SQL in a Nutshell, 2nd Edition [Electronic resources] نسخه متنی

1.2 History of the SQL Standard

Because SQL dialects began to proliferate, the ANSI published its
first SQL standard in 1986 to bring greater conformity among vendors,
and a second, widely-adopted standard in 1989. The International
Standards Organization (ISO) also approves the SQL standard. ANSI
released updates in 1992, known as SQL92 and SQL2, and again in 1999,
termed both SQL99 and SQL3. Now that the ANSI standard has advanced,
we generally use the industry standard term SQL2003. Each time it
revises the SQL standard, ANSI adds new features and incorporates new
commands and capabilities into the language. For example, the SQL99
standard added a group of capabilities that handled object-oriented
datatype extensions.

1.2.1 What's New in SQL2003

SQL99 had two main
parts, Foundation:1999 and Bindings:1999. The
SQL2003 Foundation section includes
all of the Foundation and Bindings standards from SQL99, but a new
section called Schemata was created.

The Core requirements of SQL2003 did not change from Core SQL99. So
the database platforms that conformed to Core SQL99 automatically
conform to SQL2003. Although the Core of SQL2003 has no additions
(except for a few new reserved words), a number of individual
statements and behaviors have been updated or modified. Because these
updates are reflected in the individual syntax descriptions of each
statement in Chapter 3, we
won't spend time on them here.

A few elements of the Core in
SQL99 have been deleted from SQL2003,
including:

The BIT and BIT VARYING
datatypes

The UNION JOIN clause

The UPDATE...SET ROW statement

In addition, a number of features, most of which were rather obscure,
have been added, deleted, or renamed. At this point in time, many of
the new features of SQL2003 standard are interesting mostly from an
academic standpoint because none of the database platforms support
the feature yet. However, there are a few new features that hold more
than passing interest:

Elementary OLAP functions

Adds an Online Analytical Processing (OLAP) amendment, including a
number of
windowing functions to support widely
used calculations such as moving averages and cumulative sums.
Windowing functions are aggregates computed over a window of data:
ROW_NUMBER, RANK,
DENSE_RANK, PERCENT_RANK,
and CUME_DIST. OLAP functions are fully
described in T611 of the standard. Some database platforms are
starting to support the OLAP functions. Refer to Chapter 4 for details.

Sampling

Adds the
TABLESAMPLE clause to the
FROM clause. This is useful for statistical
queries on large databases, such as a data warehouse.

Enhanced numeric functions

Adds a large number of numeric functions. In this case, the standard
was mostly catching up with the trend in the industry, since the new
functions are already supported by one or more of the database
platforms. Refer to Chapter 4 for details.

Another important change from SQL92 is that SQL99 is built upon
SQL92's levels of conformance.

1.2.2 Levels of Conformance

SQL92 first introduced
levels of conformance by defining three categories:
Entry, Intermediate, and
Full. Vendors had to achieve at least
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.

Later, SQL99 altered the base levels of conformance. Gone were the
Entry, Intermediate, and Full levels of conformance. With SQL99,
vendors must implement all the features of the lowest level of
conformance, Core SQL99, in order to claim (and publish) that they
are SQL99 ready. Core SQL99 includes the old Entry SQL92 feature set,
features from other SQL92 levels, and some brand new features. A
vendor may also choose to implement additional feature packages
described in the SQL99 standard.

1.2.3 Supplemental Features Packages in the SQL2003 Standard

The SQL2003 standard represents
the ideal, but very few vendors currently meet or exceed the Core
SQL2003 requirements. The Core 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.

The nine supplemental features packages, representing different
subsets of commands, are platform-optional. Some 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 the list
that follows.

Two committeesone within ANSI, the other within ISO, and both
composed of representatives from virtually every RDBMS
vendordrafted the definitions shown in the list. In this
collaborative and somewhat political environment, vendors compromised
on exactly which proposed features and implementations were
incorporated into a 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
in the academic community. Consequently, vendor adoption of specific
ANSI standards can be spotty at times. A relatively new addition to
the SQL2003 standard is
SQL/XML. The other
parts of the SQL99 standard are persisted in SQL2003, though their
names may have changed or they may have been slightly rearranged.

Part 1 - SQL/Framework

Includes common definitions and concepts used throughout the
standard. Defines the way the standard is structured and how the
various parts relate to one another. Describes the conformance
requirements set out by the standards committee.

Part 2 - SQL/Foundation

Includes the Core, an augmentation of the SQL99 Core, and is the
largest and most important part of the standard.

Part 3 - SQL/CLI (Call-Level Interface)

Defines the call-level interface for dynamically invoking SQL
statements from external application programs. SQL/CLI also includes
over 60 routine specifications to facilitate the development of truly
portable shrink-wrapped software.

Part 4 - SQL/PSM (Persistent Stored Modules)

Standardizes procedural language constructs similar to those found in
database platform-specific SQL dialects like PL/SQL and Transact-SQL.

Part 9 - SQL/MED (Management of External Data)

Defines the management of data located outside of the database
platform using datalinks and a wrapper interface.

Part 10 - SQL/OBJ (Object Language Binding)

Describes how to embed SQL statements in Java programs. It is closely
related to JDBC, but offers a few advantages over JDBC. It is also
very different from the traditional host language binding possible in
early versions of the standard.

Part 11 - SQL/Schemata

Defines over 85 views (three more than in SQL99) used to describe the
metadata of each database and stored in a special schema called
INFORMATION_SCHEMA. A number of views that
existed in SQL99 have been updated.

Part 12 - SQL/JRT (Java Routines and Types)

Defines a number of SQL routines and types using the Java programming
language. Features of Java, such as Java static methods and Java
classes, are now supported.

Part 14 - SQL/XML

Adds a new type, called XML, four new operators
(XMLPARSE, XMLSERIALIZE,
XMLROOT, and XMLCONCAT),
several new functions (described in Chapter 4),
and the new IS DOCUMENT predicate. It also
includes rules for mapping SQL-related elements (like identifiers,
schemas, and objects) to XML-related elements.

Note that parts 5, 6, 7, and 8 do not exist by design.

Be aware that an RDBMS platform may claim SQL2003 compliance by
meeting Core SQL99 standards, so read the vendor's
fine print for a full description of its ANSI conformity features. By
understanding what features comprise the nine packages, you gain a
clear idea of both the capabilities of a particular RDBMS and how the
various features behave when SQL code is transported to other
database products.

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

SQL in a Nutshell, Second Edition, explains the
SQL implementation of five popular RDBMSs. These vendors do not meet
all the SQL2003 standards; in fact, all RDBMS platforms 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. Conversely, the vendors
often implement new features that are not yet a part of the standard.

1.2.4 SQL2003 Statement Classes

Comparing statement classes further delineates
SQL2003 from SQL92. However, you will
still hear these terms bantered about. So you need to know them. In
SQL92, SQL statements are grouped into three broad categories:

Data Manipulation Language (DML)

Provides specific data-manipulation commands such as
SELECT, INSERT,
UPDATE, and DELETE.

Data Definition Language (DDL)

Contains commands that handle the accessibility and manipulation of
database objects, including CREATE and
DROP.

Data Control Language (DCL)

Contains the permission-related commands
GRANT and
REVOKE.

In contrast, SQL2003 supplies seven core categories, now called
classes, that provide a general framework for
the types of commands available in SQL. These statement
"classes" are slightly different
than the SQL92 statement categories, 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, SQL2003 has a new sets of statement classes,
originally added in the SQL99 standard, making them somewhat more
comprehensible and logical. Additionally, the new statement classes
now allow some "orphaned"
statementswhich did not fit well into any of the old
categoriesto be properly classified.

Table 1-1 identifies the SQL2003 statement classes
and lists some commands in each class, each of which is fully
discussed later. At this point, the key is to remember the statement
class title.

Those who work with SQL regularly should become familiar with both
the old (SQL92) and the new (SQL2003) statement classes, since both
nomenclatures are still used to refer to SQL features and statements.