SQL in a Nutshell

The SQL Standard

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.

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 . Any DBMS that supports the Core SQL99 benchmarks, plus one or more of nine additional feature packages, is now said to meet Enhanced SQL:1999 standards defined in SQL99 (also called Enhanced SQL99).

Supplemental Features Packages

The SQL99 standard represents the ideal, but very few vendors immediately meet or exceed the Core SQL99 requirements. The Core SQL99 standard is like the interstate speed limit: some drivers go above, others go below, but few go exactly the speed limit. Similarly, vendor implementations can vary greatly.

Two committees — one within ANSI and the other within ISO — composed of representatives from virtually every RDBMS vendor drafted these definitions. In this collaborative and somewhat political environment, vendors must compromise on exactly which proposed feature and implementation will be incorporated into the new standard. Many times, a new feature in the ANSI standard is derived from an existing product or is the outgrowth of new research and development from the academic community. Consequently, many vendors adopt some features in the standard, and later add still more.

The nine supplemental features packages, representing different subsets of commands, are vendor-optional. Some SQL99 features might show up in multiple packages, while others do not appear in any of the packages. These packages and their features are described in Table 1.1.

Table 1-1. SQL99 Supplemental Features Packages

ID	Name	Features
PKG001	Enhanced datetime facilities	Interval datatype Time zone specification Full datetime Optional interval qualifier
PKG002	Enhanced integrity management	Assertions Referential delete actions Referential update actions Constraint management Subqueries in CHECK constraint Triggers FOR EACH STATEMENT triggers Referential action RESTRICT
PKG003	OLAP capabilities	CUBE and ROLLUP INTERSECT operator Row and table constructs FULL OUTER JOIN Scalar subquery values
PKG004	SQL Persistent Stored Modules (PSM)	A programmatic extension to SQL that makes it suitable for developing more functionally complete applications The commands CASE, IF, WHILE, REPEAT, LOOP, and FOR Stored Modules Computational completeness INFORMATION_SCHEMA views
PKG005	SQL Call-level Interface (CLI)	SQL Call-level Interface support: an Application Programming Interface (API) that enables SQL operations to be called that is very similar to the Open Database Connectivity (ODBC) standard
PKG006	Basic object support	Overloading SQL-invoked functions and procedures User-defined types with single inheritance; basic SQL routines on user-defined types (including dynamic dispatch) Reference types CREATE TABLE Array support: basic array support, array expressions, array locators, user-datatype (UDT ) array support, reference-type array support, SQL routine on arrays Attribute and field reference Reference and dereference operations
PKG007	Enhanced object support	ALTER TABLE, ADD Enhanced user-defined types (including constructor options, attribute defaults, multiple inheritance, and ordering clause) SQL functions and type-name resolution Subtables ONLY in queries Type predicate Subtype treatment User-defined CAST functions UDT locators SQL routines on user-defined types such as identity functions and generalized expressions
PKG008	Active database features	Triggers
PKG009	SQL Multimedia (MM) support	Handling for streaming multimedia data and for large and complex audio and video data

Be aware that a DBMS vendor may claim Enhanced SQL99 compliance by meeting Core SQL99 standards plus only one of nine added packages; so read the vendor’s fine print for a full description of its program features. By understanding what features comprise the nine packages, programmers and developers gain a clear idea of the capabilities of a particular DBMS, and how the various features behave when SQL code is transported to other database products.

The ANSI standards — which cover retrieval, manipulation, and management of data in commands, such as SELECT, JOIN, ALTER TABLE, and DROP — formalized many SQL behaviors and syntax structures across a variety of products. These standards become even more important as open source database products, such as MySQL, miniSQL, and PostgreSQL, grow in popularity and are developed by virtual teams rather than large corporations.

SQL in a Nutshell explains the SQL implementation of four popular RDBMSs. These vendors do not meet all the SQL99 standards; in fact, all RDBMS vendors play a constant game of tag with the standards bodies. Many times, as soon as vendors close in on the standard, the standards bodies update, refine, or otherwise change the benchmark.

SQL99 Statement Classes

Comparing statement classes further delineates SQL92 and SQL99. In SQL92, SQL statements are grouped into three broad categories: the Data Manipulation Language (DML), the Data Definition Language (DDL), and the Data Control Language (DCL). The DML provides specific data-manipulation commands such as SELECT, INSERT, UPDATE, and DELETE. The DDL contains commands that handle the accessibility and manipulation of database objects, including CREATE and DROP, while the DCL contains the permission-related commands GRANT and REVOKE.

In contrast, SQL99 supplies seven Core categories that provide a general framework for the types of commands available in SQL. These statement “classes” are slightly different than the SQL92 statement classes, since they attempt to identify the statements within each class more accurately and logically. Furthermore, because SQL is constantly under development, new features and commands enter the standard and may necessitate new statement classes. So, to accommodate future growth, SQL99 developed new sets of statement classes, making them somewhat more comprehensible and logical. Additionally, the new statement classes now allow some “orphaned” statements—which did not fit well into any of the old categories—to be properly classified.

Table 1.2 identifies the SQL99 statement classes and lists a few commands in each class, each of which is fully discussed later. At this point, the key is to remember the statement class title.

Table 1-2. SQL Statement Classes

Class	Description	Example Commands
SQL Connection Statements	Start and end a client connection	CONNECT, DISCONNECT
SQL Control Statements	Control the execution of a set of SQL statements	CALL, RETURN
SQL Data Statements	Have a persistent and enduring effect upon data	SELECT, INSERT, UPDATE, DELETE
SQL Diagnostic Statements	Provide diagnostic information and raise exceptions and errors	GET DIAGNOSTICS
SQL Schema Statements	Have a persistent and enduring effect on a database schema and objects within that schema	ALTER, CREATE, DROP
SQL Session Statements	Control default behavior and other parameters for a session	SET
SQL Transaction Statements	Set the starting and ending point of a transaction	COMMIT, ROLLBACK

Those who work with SQL regularly should become familiar with both the old (SQL92) and the new (SQL99) statement classes, since many programmers and developers still use the old nomenclature to refer to current SQL features.