P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IML
SQL ̇OLE WL040/Bidgolio-Vol I WL040-Sample.cls June 20, 2003 13:16 Char Count= 0
356 STRUCTUREDQUERYLANGUAGE(SQL)ANSI Standardization
SQL is in its third revision as an ANSI standard. The first
revision was released in 1986, the second version (SQL2 or
SQL-92) was released in 1992, and the latest release (SQL3
or SQL-99) occurred in 1999. Each release added major
functionality to the standard and, in theory, increasing
compatibility for those willing to program in “pure” SQL
(Eisenberg & Melton, 2002).IBM developed SQL in the 1970s for use in Sys-
tem R. It is the de facto standard as well as being
an ISO and ANSI standard. It is often embedded
in general-purpose programming languages.The first SQL standard, in 1986, provided ba-
sic language constructs for defining and ma-
nipulating tables of data; a [minor] revision in
1989 added language extensions for referential
integrity and generalized integrity constraints.
Another revision in 1992 provided facilities for
schema manipulation and data administration,
as well as substantial enhancements for data def-
inition and data manipulation.Development is currently underway to enhance
SQL into a computationally complete language
for the definition and management of persistent,
complex objects. This includes: generalization
and specialization hierarchies, multiple inheri-
tance, user defined data types, triggers and as-
sertions, support for knowledge based systems,
recursive query expressions, and additional data
administration tools. It also includes the specifi-
cation of abstract data types (ADTs), object iden-
tifiers, methods, inheritance, polymorphism,
encapsulation, and all of the other facilities nor-
mally associated with object data management.
(FOLDOC, 2002)DDL
SQL’s DDL provides the following data definition
statements (DDS): CREATE TABLE, CREATE INDEX,
CREATE VIEW, ALTER TABLE, DROP TABLE, DROP
VIEW, and DROP INDEX. The create statements create
the named structures within the database (tables,
indexes, etc). The drop statements remove the corre-
sponding structures. The create view statement will be
handled in a later section.DML
SQL’s DML supports the following operations on ta-
bles: INSERT, UPDATE, DELETE, SELECT, and CREATE
VIEW. The SQL SELECT is the data retrieval mechanism.
The operations are self-explaining; the insert operation
adds data into a named table, the update operation mod-
ifies data in a named table, and the delete operation re-
moves data from the named table. All of these operations
are permanent modifications to the database if and only
if they are followed by commit statements—refer to the
section on transaction control.The Select Statement
The select statement is one of the most flexible and often
used in SQL. The simplest form of the SELECT statement,
“select * from<table name>,” returns all of the rows
from the indicated table. By specifying awhereclause, the
resultant set is restricted by a set of conditions. Thegroup
byclause allows data grouping (ordered by the attributes
listed). Thehavingclause can further restrict the results
returned by thewhereclause. Theorder byclause allows a
final ordering to occur before results are returned to the
requester. The SQL SELECT statement has no restrictions
on the number of tables that may be accessed to fulfill the
user’s request. Besides being the query construct, the se-
lect statement can be used in conjunction with every DML
statement. The syntax of the select statement isselect * | <attribute list>
from <table list>
{where <condition>}
{group by <grouping attributes>}
{having <group condition> }
{order by <attribute list>}SINGLE TABLE ACCESS AND
THE RESULT SET
Because SQL was designed using set theory, one impor-
tant carry-over was the result set. The result of any query
is a set, named the result set. This makes for a very pow-
erful and flexible design. Because we have a set, we can
then apply another query to this result set (in the case of
refining as we go).
The most basic of queries returns the contents of a
specified table:select * from TICKET_PRICE;The result set is the entire ticketprice table:TICKETTYPE PRICE
KIDS 4.50
SENIORS 5.00
ADULT MAT 5.00
ADULTS 7.00The “select *” statement is often used to inspect the
data that are stored in a table (that is not too large) and
to verify that DML operations were successful (especially
inserts and deletes).Reducing the Size of the Result Set
Often the “select *” statement returns too many rows for
useful analysis. To trim down the size of the result set,
we can perform some combination of column and row
reduction. To reduce the number of columns, we simply
replace the “*” with the column names that we want to in-
vestigate. To reduce the number of rows, we must include
awhereclause. Thewhereclause provides a list of criteria
that can be joined using trivalue Boolean logic.