SQL In A Nutshell | O'Reilly Media

Content preview·Buy reprint rights for this chapter

In the 1970s, IBM developed a product called SEQUEL, or Structured English Query Language, which ultimately became SQL, the Structured Query Language.

IBM, along with other relational database vendors, wanted a standardized method for accessing and manipulating data in a relational database. Over the decades, many competing languages have allowed programmers and developers to access and manipulate data. However, few have been as easy to learn and as universally accepted as SQL. Programmers and developers now have the benefit of learning a language that, with minor adjustments, is applicable to a wide variety of database applications and products.

SQL in a Nutshell describes four implementations of the current SQL standard, SQL99 (also known as SQL3): Microsoft's SQL Server, MySQL, Oracle, and PostgreSQL. For those migrating from implementations of the earlier SQL standard, this chapter describes the current SQL standard and the ways in which it differs from the earlier standard. This chapter also provides a bit of history of the standards evolution.

Relational Database Management Systems (RDBMSs), such as SQL Server and Oracle, are the primary engines of information systems worldwide, particularly Internet/Intranet applications and distributed client/server computing systems.

An RDBMS is defined as a system whose users view data as a collection of tables related to each other through common data values. Data is stored in tables, and tables are composed of rows and columns. Tables of independent data can be linked (or related) to one another if they each have columns of data (called keys ) that represent the same data value. This concept is so common as to seem trivial; however, it was not so long ago that achieving and programming a system capable of sustaining the relational model was considered a long shot that would have limited usefulness.

Relational data theory was developed by E. F. Codd in the 1960s. Codd compiled a list of criteria a database product must meet to be considered relational. For those who are curious, Codd's list appears at the end of this chapter.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Relational Database Management Systems (RDBMSs), such as SQL Server and Oracle, are the primary engines of information systems worldwide, particularly Internet/Intranet applications and distributed client/server computing systems.

An RDBMS is defined as a system whose users view data as a collection of tables related to each other through common data values. Data is stored in tables, and tables are composed of rows and columns. Tables of independent data can be linked (or related) to one another if they each have columns of data (called keys ) that represent the same data value. This concept is so common as to seem trivial; however, it was not so long ago that achieving and programming a system capable of sustaining the relational model was considered a long shot that would have limited usefulness.

Relational data theory was developed by E. F. Codd in the 1960s. Codd compiled a list of criteria a database product must meet to be considered relational. For those who are curious, Codd's list appears at the end of this chapter.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

SQL in a Nutshell describes the SQL standard and the vendor implementations of four leading RDBMSs — two that are from leading commercial vendors (Microsoft SQL Server and Oracle) and two that are from the chief open source database projects (MySQL and PostgreSQL):

Microsoft SQL Server: Microsoft SQL Server is a popular RDBMS that runs only on the Windows platform. Its features include ease of use, low cost, and high performance. This book covers Microsoft SQL Server 2000.
MySQL: MySQL is a popular open source Database Management System (DBMS) that is known for its blistering performance. It runs on numerous operating systems, including most Linux variants. To improve performance, it has a slimmer feature set than do many other DBMSs. Its critics point out that it is not a fully relational DBMS since it does not support many key features of relational databases, particularly in how it processes transactions. This book covers MySQL 3.22.9.
Oracle: Oracle is a leading RDBMS in the commercial sector. It runs on a multitude of operating systems and hardware platforms. Its scalable and reliable architecture have made it the platform of choice for many users. Because of their highly tunable nature, Oracle RDBMSs require a well-trained database administrator (DBA). SQL in a Nutshell covers Oracle Release 8.1.
PostgreSQL: PostgreSQL is one of the most feature-rich RDBMSs of the open source world. Its compliance with SQL standards is unmatched by other open source RDBMSs. In addition to its rich set of features, PostgreSQL runs on a wide variety of operating systems and hardware platforms. This book covers PostgreSQL 6.5.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

To bring greater conformity among vendors, the American National Standards Institute (ANSI) published its first SQL standard in 1986 and a second widely adopted standard in 1989. ANSI released updates in 1992, known as SQL92 and SQL2, and again in 1999, termed both SQL99 and SQL3. Each time, ANSI added new features and incorporated new commands and capabilities into the language. Unique to the SQL99 standard is a group of capabilities that handle object-oriented datatype extensions. The International Standards Organization (ISO) has also approved SQL99. An important change from SQL92 is that SQL99 expands on SQL92's levels of conformance.

SQL92 first introduced levels of conformance by defining three categories: Entry, Intermediate, and Full. Vendors had to achieve Entry-level conformance to claim ANSI SQL compliance. The U.S. National Institute of Standards and Technology (NIST) later added the Transitional level between the Entry and Intermediate levels. So, NIST's levels of conformance were Entry, Transitional, Intermediate, and Full, while ANSI's were only Entry, Intermediate, and Full. Each higher level of the standard was a superset of the subordinate level, meaning that each higher level of the standard included all the features of the lower level of conformance.

SQL99 altered the base levels of conformance. Gone are the Entry, Intermediate, and Full levels of conformance. With SQL99, vendors must implement all the features of the lowest level of conformance, Core SQL:1999, in order to claim (and publish) that they are SQL99 ready. Core SQL:1999 — or Core SQL99, for short — includes the old Entry SQL92 feature set, features from other SQL92 levels, and some brand new features. This upgrade to the SQL standard enabled vendors to go quickly from the Entry SQL92 feature set to the Core SQL99 feature set.

Whereas SQL92 featured the Intermediate and Full levels of conformance, SQL99 has Enhanced SQL:1999

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The constantly evolving nature of the SQL standard has given rise to a number of SQL dialects among the various vendors and products. These dialects most commonly evolved because the user community of a given database vendor required capabilities in the database before the ANSI committee created a standard. Occasionally though, a new feature is introduced by the academic or research communities due to competitive pressures from competing technologies. For example, many database vendors are augmenting their current programmatic offerings with Java (as is the case with Oracle and Sybase) or VBScript (as Microsoft is doing). In the future, programmers and developers will use these programming languages in concert with SQL to build SQL programs.

Nonetheless, each of these dialects includes conditional processing (such as that controlled through IF . . . THEN statements), control-of-flow functions (such as WHILE loops), variables, and error handling. Because ANSI had not yet developed a standard for these important features, RDBMS developers and vendors were free to create their own commands and syntax. In fact, some of the earliest vendors from the 1980s have variances in the most elementary commands, such as SELECT, because their implementations predate the standards. (ANSI is now refining standards that address these shortcomings.)

Some of these dialects have introduced procedural commands to support the functionality of a much more complete programming language. For example, these procedural implementations contain error-handling commands, control-of-flow language, conditional commands, variable handling, arrays, and many other extensions. Although these are technically divergent procedural implementations, they are called dialects here.

Some popular dialects of SQL include:

PL /SQL: Found in Oracle. PL/SQL stands for Procedural Language/SQL and contains many similarities to the language Ada.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Following are E.F. Codd's Twelve Principles of Relational Databases. These principles continue to be the litmus test used to validate the "relational" characteristics of a database product; a database product that does not meet all of these rules is not fully relational. These rules do not apply to applications development, but they do determine whether the database engine itself can be considered truly "relational." Currently, most RDBMSs pass Codd's test, including all of the databases discussed in this book, except MySQL. (MySQL does not currently support views or atomic transactions. Therefore, it does not qualify as a true relational DBMS under Codd's rules.)

Codd's criteria provide the benchmarks for defining RDBs. Knowing and understanding these principles will help you develop and design RDBs:

Information is represented logically in tables.
Data must be logically accessible by table, primary key, and column.
Null values must be uniformly treated as "missing information," not as empty strings, blanks, or zeros.
Metadata (data about the database) must be stored in the database just as regular data is.
A single language must be able to define data, views, integrity constraints, authorization, transactions, and data manipulation.
Views must show the updates of their base tables and vice versa.
A single operation must be able to retrieve, insert, update, or delete data.
Batch and end-user operations are logically separate from physical storage and access methods.
Batch and end-user operations can change the database schema without having to recreate it or the applications built upon it.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

SQL provides an easy, intuitive way to interact with a database. The SQL99 standard does not define the concept of a "database," but it does define all the functions and concepts needed for a user to create, retrieve, update, and delete data. It is important to review a few of the concepts upon which the SQL standard is based.

Other database manipulation languages, such as Xbase or Visual Basic, perform their data operations quite differently from SQL. These languages require the programmer to tell the program exactly how to treat the data, one record at a time. Since the program cycles down through a list of records, performing its logic on one record after another, this style of programming is frequently called row processing or procedural programming.

SQL programs operate in logical sets of data. Set theory is applied when the FROM clause is used, as in the SELECT statement. In effect, data is selected from a set called a table. Unlike the row processing style, set processing allows a programmer to tell the database simply what is required, not how each individual piece of data should be handled. Sometimes set processing is referred to as declarative processing , since a programmer declares only what data is necessary, as in "Give me all employees in the southern region who earn more than $70,000 per year," rather than describes the exact procedure used to manipulate the data.

Set theory was the brainchild of Russian mathematician Georg Cantor, who developed it at the end of the nineteenth century. At the time, set theory (and his theory of the infinite) was quite controversial; today, set theory is such a common part of life that it is learned in elementary school.

Examples of set theory in conjunction with relational databases are detailed in the following section.

Effective SQL programming requires that the programmer think in terms of sets of data, rather than of individual rows. The RDBS model follows a linguistic protocol to define the hierarchy of data sets within the SQL99 standard.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Other database manipulation languages, such as Xbase or Visual Basic, perform their data operations quite differently from SQL. These languages require the programmer to tell the program exactly how to treat the data, one record at a time. Since the program cycles down through a list of records, performing its logic on one record after another, this style of programming is frequently called row processing or procedural programming.

SQL programs operate in logical sets of data. Set theory is applied when the FROM clause is used, as in the SELECT statement. In effect, data is selected from a set called a table. Unlike the row processing style, set processing allows a programmer to tell the database simply what is required, not how each individual piece of data should be handled. Sometimes set processing is referred to as declarative processing , since a programmer declares only what data is necessary, as in "Give me all employees in the southern region who earn more than $70,000 per year," rather than describes the exact procedure used to manipulate the data.

Set theory was the brainchild of Russian mathematician Georg Cantor, who developed it at the end of the nineteenth century. At the time, set theory (and his theory of the infinite) was quite controversial; today, set theory is such a common part of life that it is learned in elementary school.

Examples of set theory in conjunction with relational databases are detailed in the following section.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Effective SQL programming requires that the programmer think in terms of sets of data, rather than of individual rows. The RDBS model follows a linguistic protocol to define the hierarchy of data sets within the SQL99 standard.

Figure 2.1 is a description of the SQL99 terminology used to describe the hierarchical working sets used by a relational database—clusters contain sets of catalogs; catalogs contain sets of schemas; schemas contain sets of objects, such as tables and views; and tables and views are composed of sets of records.

Figure 2.1: SQL99 Dataset hierarchy

In the relational model, data is shown logically as a two-dimensional table that describes a single entity (for example, business expenses). Data in the table is displayed in columns and rows . Each column of the table describes a specific attribute of the entity. For example, in a Business_Expense table, a column called Expense_Date might show when the expense was incurred. Each record in the table describes a specific entity; in this case, everything that makes up a business expense (when it happened, how much it cost, who incurred the expense, what it was for, and so on). The specific values of each attribute are supposed to be atomic ; that is, they are supposed to contain one, and only one, value. If a table is constructed in which the intersection of a row and column can contain more than one distinct value, then one of SQL's primary design guidelines has been violated.

There are rules of behavior specified for column values. Foremost is that the column values must share a common domain , better known as a datatype . For example, the value `ELMER' should not be placed into the Expense_Date field. The Expense_Date field should contain only dates; therefore, this column would be defined as having a date datatype. In addition, SQL99 further controls the values of such a field through the application of

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The previous section mentioned that a table could contain one or many columns, each with a single defining datatype. In real world applications, datatypes provide some control and efficiency as to how tables are defined. Using specific datatypes enables better, more understandable queries and controls the integrity of data.

The tricky thing about SQL99 datatypes is that they do not map directly to an identical implementation in a given vendor's product. Although the vendors provide "datatypes" that correspond to the SQL99 datatypes, these vendor-specific datatypes are not true SQL99 datatypes. Nonetheless, each vendor's datatypes are close enough to the standard to be both easily understandable and job-ready.

The official SQL99 datatypes (as opposed to vendor-specific) fall into the general categories described in Table 2.1.

Table 2.1: SQL99 Datatypes
Category	Example Datatypes and Abbreviations	Description
binary	`binary large object (BLOB)`	This datatype stores binary string values in hexadecimal format.
bit string	`bit` `bit varying`	These datatypes store either binary or hexadecimal data. `BIT` has a fixed length, while `BIT VARYING` has a variable length.
boolean

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Most databases allow any of their supported datatypes to store NULL values. Inexperienced SQL programmers and developers tend to think of NULL as zero or blank. In fact, NULL is neither of these. In the relational database world, NULL literally means that the value is unknown or indeterminate. (This question alone — whether NULL should be considered unknown or indeterminate — is the subject of academic debate.) This differentiation enables a database designer to distinguish between those entries that represent a deliberately placed zero and those where either the data is not recorded in the system or where a NULL has been explicitly entered. For an example of this semantic difference, consider a system that tracks payments. A payment with a NULL amount does not mean that the product is free; instead, a NULL payment indicates that the amount is not known or perhaps not yet determined.

One side effect of the indeterminate nature of a NULL value is it cannot be used in a calculation or a comparison. Here are a few brief, but very important rules to remember about the behavior of NULL values:

A NULL value cannot be inserted into a column defined as NOT NULL.
NULL values are not equal to each other. It is a frequent mistake to compare two columns that contain NULL and expect the NULL values to match. (A NULL value can be identified in a WHERE clause or in a Boolean expression using phrases such as `value IS NULL' and `value IS NOT NULL'.)
A column containing a NULL value is ignored in the calculation of aggregate values such as AVG, SUM, or MAX.
When columns that contain NULL values in a GROUP BY clause of a query are listed, the query output contains rows for those NULL values.
Joins between tables, in which one join condition contains values and the other contains NULL, are governed by the rules for "outer joins." Joins are described completely later in this chapter.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

SQL commands contains three main categories of syntax: identifiers, literals, and reserved and key words. Identifiers name objects that a user or a system process has created, such as a database, a table, the columns in a table, or a view. Literals are non-NULL values supplied to the system. Reserved and key words are words that have special meaning to the database SQL parser, such as SELECT, GRANT, DELETE, or CREATE.

Keep in mind that RDBMSs are built upon set theory: clusters contain sets of catalogs, catalogs contain sets of schemas, schemas contain sets of objects, and so on. At each level of this structure, each item requires a unique name or identifier.

This means that each object (whether a database, table, view, column, index, key, trigger, stored procedure, or constraint) in a RDBMS must be named. When issuing the command that creates a server object, a name for that new object must be specified.

There are two important sets of rules that experienced programmers keep in mind when choosing an identifier for a given item:

The first set of rules include logical rules of thumb or conventions that ultimately create better database structures and data tracking. These are not so much required by SQL as they are the distilled experience of practiced programmers.
The second set of rules are those set by the SQL standard and implemented by the vendors. The conventions for each vendor are covered later in this chapter.

Section 2.5.1.1: Naming conventions

The naming conventions suggested for identifiers in the following list are based on long years of experience by many in the industry, although they aren't necessarily required by SQL:

Select a name that is meaningful, relevant, and descriptive

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Up to this point, the chapter has been about the individual aspects of a SQL statement. Following is a high-level overview of the most important SQL command, SELECT, and some of its most salient points — namely, the relational operations known as projections, selections, and joins.

Although at first glance it might appear that the SELECT statement deals only with the selection operation; in actuality, SELECT embodies all three operations. (The SELECT statement is treated in detail in Chapter 3.) Projection operations retrieve specific columns of data. Selection operations retrieve specific rows of data. And join operations bring together the data from two or more different tables.

This overly simplified example of a SELECT statement focuses more on the underlying concepts than on difficult syntax:

SELECT select_list
FROM   table_list
WHERE  search_criteria

The following statement actually embodies two of the three relational operations, selection and projection:

SELECT expense_date, expense_amount, expense_description
FROM   expenses
WHERE  employee_last_name  = 'Fudd' 
  AND  employee_first_name = 'Elmer'

Projection is the relational operation of retrieving specific columns of data. As illustrated in the prior generic example, and the more realistic example that follows, the select_list is the component of a SELECT statement that allows the programmer to perform a projection. Here, we select the first and last names of an author, plus his home state, in the authors table:

SELECT au_fname, au_lname, state
FROM   authors

The results from any such SELECT statement are presented as another table of data:

au_fname             au_lname                                 state 
-------------------- ---------------------------------------- ----- 
Johnson              White                                    CA    
Marjorie             Green                                    CA    
Cheryl               Carson                                   CA    
Michael              O'Leary                                  CA

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

This chapter discussed the concepts that serve as the foundation for learning the Structured Query Language. The differences between row processing applications and set processing applications (such as relational databases) were discussed. The basic structure of the relational model, both in terms of the ANSI standard and typical database implementations, was illustrated. A quick overview of the datatypes provided by the SQL99 standard, as well as those provided by each database vendor, was also provided. The categories of SQL syntax, including identifiers, literals, system delimiters and operators, and keywords were described. Examples of SQL and an explanation of SQL projections, selections, and joins were included.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

This chapter is the heart of SQL in a Nutshell: it is an alphabetical listing of SQL commands with detailed explanations and examples. Each command and function is identified in a master table as being "supported," "supported with variations," "supported with limitations," or "not supported," for each of the four SQL dialects covered in this book: SQL Server, MySQL, Oracle, and PostgreSQL. After a brief description of the SQL99 standard, each vendor application is discussed briefly but thoroughly, with supporting examples and sample coding.

When researching a command in this chapter, first read the introductory paragraph, vendor support table, and the section on SQL99 syntax and description. The reason for this is that any common features between all the implementations of the command are discussed once under the SQL99 topic. Thus, reading directly about a vendor's implementation of a particular command may not describe every aspect of that command, since some of its details may be covered in the opening comments.

The following list offers useful tips for reading Table 3.1, as well as what each abbreviation stands for. The sections that follow describe the table's commands in detail:

The first column contains the alphabetized SQL commands.
The SQL statement class for each command is indicated in the second lefthand column.
The command's implementation in SQL99 is indicated in the next column.
The subsequent columns list the vendor's level of support:

Supported (S)

The vendor supports the SQL99 standard for the particular command.

Supported, with variations (SWV)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

When researching a command in this chapter, first read the introductory paragraph, vendor support table, and the section on SQL99 syntax and description. The reason for this is that any common features between all the implementations of the command are discussed once under the SQL99 topic. Thus, reading directly about a vendor's implementation of a particular command may not describe every aspect of that command, since some of its details may be covered in the opening comments.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The following list offers useful tips for reading Table 3.1, as well as what each abbreviation stands for. The sections that follow describe the table's commands in detail:

The first column contains the alphabetized SQL commands.
The SQL statement class for each command is indicated in the second lefthand column.
The command's implementation in SQL99 is indicated in the next column.
The subsequent columns list the vendor's level of support:

Supported (S)

The vendor supports the SQL99 standard for the particular command.

Supported, with variations (SWV)

The vendor supports the SQL99 standard for the particular command, using vendor-specific code or syntax.

Supported, with limitations (SWL)

The vendor supports some but not all of the functions specified by the SQL99 standard for the particular command.

Not supported (NS)

The vendor does not support the particular command according to the SQL99 standard.
Remember that even if a specific SQL99 command is listed as "Not Supported," the vendor usually has alternative coding or syntax to enact the same command or function. Therefore, be sure read the discussion and examples for each command later in this chapter.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

ALTER PROCEDURE

The ALTER PROCEDURE statement allows changes to be made to an existing stored procedure. Depending on the vendor, the kind and degree of change varies widely.

In SQL Server, this statement alters a previously created procedure (using the CREATE PROCEDURE statement) but doesn't change permissions or affect dependent stored procedures or triggers.

In Oracle, this command simply recompiles a PL/SQL stored procedure, but does not allow the code to be changed. Instead, use the Oracle command CREATE OR REPLACE PROCEDURE to achieve the same functionality.

Vendor	Command
SQL Server	Supported, with variations
MySQL	Not supported
Oracle	Supported, with variations
PostgreSQL	Not supported

SQL99 Syntax and Description

ALTER PROCEDURE procedure_name {CASCADE | RESTRICT}
[LANGUAGE | PARAMETER STYLE | <SQL data access> | <null clause behavior> | DYNAMIC RESULT SETS | NAME]
[parameter datatype [,...n]

As discussed under CREATE PROCEDURE, the LANGUAGE, PARAMETER STYLE, SQL data access method (i.e.,

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

ALTER TABLE

The ALTER TABLE statement allows an existing table to be modified without dropping the table or altering existing permissions on the table. In this way, certain incremental changes are performed easily on an existing table.

Both Oracle and Microsoft SQL Server support this command with a number of variations to service their differing physical file-allocation methods.

Vendor	Command
SQL Server	Supported, with variations
MySQL	Supported, with limitations
Oracle	Supported, with variations
PostgreSQL	Supported, with variations

SQL99 Syntax and Description

ALTER TABLE table_name
[ADD [COLUMN] column_name datatype attributes]
| [ALTER [COLUMN] column_name SET DEFAULT default_value]
| [ALTER [COLUMN] column_name DROP DEFAULT]
| [ALTER [COLUMN] column_name ADD SCOPE table_name
| [ALTER [COLUMN] column_name DROP SCOPE {RESTRICT | CASCADE}]
| [DROP [COLUMN] column_name {RESTRICT | CASCADE}]
| [ADD table_constraint_name]
| [DROP CONSTRAINT table_constraint_name {RESTRICT | CASCADE}]

The SQL99 ALTER TABLE statement allows many useful modifications to be made to an existing table. This versatile command allows users to

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

ALTER TRIGGER

The ALTER TRIGGER statement modifies a preexisting trigger definition without altering permissions or dependencies.

Vendor	Command
SQL Server	Supported, with variations
MySQL	Not supported
Oracle	Supported, with variations
PostgreSQL	Not supported

SQL99 Syntax and Description

Currently, there is no SQL99 standard for this command.

Microsoft SQL Server Syntax and Variations

ALTER TRIGGER trigger_name
ON {table_name | view_name}
[WITH ENCRYPTION]
{FOR | AFTER | INSTEAD OF} {[DELETE] [,] [INSERT] [,] [UPDATE]}
[WITH APPEND]
[NOT FOR REPLICATION]
AS
  T-SQL_block
| [FOR { [INSERT] [,] [UPDATE] }
[NOT FOR REPLICATION]
AS
 
  { IF UPDATE(column) [{AND | OR} UPDATE(column)] [...n]
    | 
    IF (COLUMNS_UPDATED(  ) {bitwise_operator} updated_bitmask) 
    { comparison_operator} column_bitmask [...n] }
    T-SQL_block ] } ]

Microsoft SQL Server allows the specification of FOR | AFTER | INSTEAD OF { [DELETE] [,] [UPDATE] [,][INSERT] } | { [INSERT] [,] [UPDATE] } to describe which data-modification statement trigger is affected by the command. At least one of these is required, but any combination is allowed with extra options separated by commas. The options

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

ALTER VIEW

While there is currently no SQL99 standard for the ALTER VIEW , it is important to note that this command behaves differently in each major vendor application that supports it. Oracle uses this command to recompile a view; Microsoft SQL Server uses this command to allow modifications to a view without also updating any dependent stored procedures, triggers, or permissions.

Vendor	Command
SQL Server	Supported, with variations
MySQL	Not supported
Oracle	Supported, with variations
PostgreSQL	Not supported

SQL99 Syntax and Description

Currently, there is no SQL99 standard for this command.

Microsoft SQL Server Syntax and Variations

ALTER VIEW view_name [(column [,...n])]
[WITH {ENCRYPTION | SCHEMABINDING | VIEW_METADATA]
AS
select_statement 
[WITH CHECK OPTION]

As with the CREATE VIEW statement, ALTER VIEW allows a programmer to specify the column aliases that the view uses to name the columns, as well as the entire SELECT statement that is the core component of the view.

The other clauses of the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

CALL

The CALL statement invokes a stored procedure.

Vendor	Command
SQL Server	Not supported
MySQL	Not supported
Oracle	Supported
PostgreSQL	Supported

SQL99 Syntax and Description

CALL procedure_name [(parameter [,...n] )]

The CALL statement makes it easy to invoke a stored procedure. Simply provide the name of the stored procedure and include any parameters used by the stored procedure, enclosing them within parentheses. If the stored procedure has only OUT parameters, or has no parameters, empty parentheses may be included.

Microsoft SQL Server does not support the CALL statement. However, nearly identical functionality can be achieved using the EXECUTE statement. Refer to the vendor documentation for a full explanation of this SQL Server extension.

Oracle Syntax and Variations

CALL [schema.][{type_name | package_name}.]procedure_name@dblink 
[(parameter [,...n] )]
[INTO :variable_name [INDICATOR :indicator_name] ]

Oracle allows the CALL statement to invoke standalone stored procedures, functions, methods, as well as stored procedures and functions contained within a type or package. If the procedure or function resides in another database, simply declare the database via a dblink statement, stating where the object resides, as part of the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

CASE

The CASE function provides IF-THEN-ELSE functionality within a SELECT or UPDATE statement. It evaluates a list of conditions and returns one value out of several possible values.

Vendor	Command
SQL Server	Supported
MySQL	Supported
Oracle	Not Supported (refer to the `DECODE` function in vendor documentation for similar functionality)
PostgreSQL	Supported

CASE has two usages: simple and searched. Simple CASE expressions compare one value, the input_value, with a list of other values, and return a result associated with the first matching value. Searched CASE expressions allow the analysis of several logical conditions and return a result associated with the first one that is true.

SQL99 Syntax and Description

-- Simple comparison operation
CASE input_value
WHEN when_condition THEN resulting_value
[...n]
[ELSE else_result_value]
END

-- Boolean searched operation
CASE
WHEN Boolean_condition THEN resulting_value
[...n]
[ELSE else_result_expression]
END

In the simple CASE function, the input_value is evaluated against each WHEN clause. The

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

CAST

The CAST command explicitly converts an expression of one datatype to another.

Vendor	Command
SQL Server	Supported
MySQL	Not supported
Oracle	Not supported
PostgreSQL	Supported

SQL99 Syntax and Description

CAST(expression AS data_type[(length)])

The CAST function converts any expression, such as a column value or variable, into another defined datatype. The length of the datatype may be supplied optionally for those datatypes (such as CHAR or VARCHAR) that support lengths.

Be aware that some conversions, such as DECIMAL values to INTEGER, result in rounding operations. Also, some conversion operations may result in an error if the new datatype does not have sufficient space to display the converted value.

Example

This example retrieves the year-to-date sales as a CHAR and concatenates it with a literal string and a portion of the book title. It converts ytd_sales to CHAR(5), plus it shortens the length of the title to make the results more readable:

SELECT CAST(ytd_sales AS CHAR(5)) + "Copies sold of " + CAST(title AS 
VARCHAR(30))
FROM titles
WHERE ytd_sales IS NOT NULL
  AND ytd_sales > 10000
ORDER BY ytd_sales DESC

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

CLOSE CURSOR

The CLOSE CURSOR command closes a server-side cursor created with a DECLARE CURSOR statement. MySQL does not support server-side cursors, but does support extensive C programming extensions.

Vendor	Command
SQL Server	Supported
MySQL	Not supported
Oracle	Supported
PostgreSQL	Supported

SQL99 Syntax and Description

CLOSE { cursor_name }

The cursor_name is the name of the cursor created with the DECLARE CURSOR command.

Example

This example from Microsoft SQL Server opens a cursor and fetches all the rows:

DECLARE employee_cursor CURSOR FOR
  SELECT lname, fname
  FROM pubs.dbo.authors
  WHERE lname LIKE 'K%'

OPEN employee_cursor

FETCH NEXT FROM employee_cursor

WHILE @@FETCH_STATUS = 0
BEGIN
  FETCH NEXT FROM Employee_Cursor
END

CLOSE employee_cursor

DEALLOCATE employee_cursor

The DEALLOCATE statement in Microsoft SQL Server releases the resources and data structures used by the cursor, but Oracle, PostgreSQL, and MySQL do not use it.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

COMMIT TRANSACTION

The COMMIT TRANSATION statement explicitly ends an open transaction, whether explicitly opened with BEGIN, or implicitly opened as part of an INSERT, UPDATE, or DELETE statement. This command allows the manual and permanent end to a data-manipulation operation.

Vendor	Command
SQL Server	Supported, with variations
MySQL	Not supported
Oracle	Supported
PostgreSQL	Supported

SQL99 Syntax and Description

COMMIT [WORK]

In addition to finalizing a single or group of data-manipulation operation(s), COMMIT has some interesting effects on other aspects of a transaction. First, it closes any associated open cursors. Second, any temporary table(s) specified with ON COMMIT DELETE ROWS are cleared of data. Third, all locks opened by the transaction are released. Finally, all deferred constraints are checked. If the deferred constraints are violated, the transaction is rolled back.

Please note that SQL99 dictates that transactions are implicitly opened when one of these statements is executed:

ALTER

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Table of Contents

Section 2.5.1.1: Naming conventions