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
STRUCTUREDQUERYLANGUAGE 355ASare domain-compatible attributes from relations R and
S, respectively, andθis one of the following comparison
operators:{=,<,≤,>,≥,=}. JOIN CONDITIONS may be
connected with the Boolean AND operator. For example,
to retrieve all of the data about a candidate the statement
CANDIDATEξ(candidate.name=candidate-body.name)CANDIDATE-
BODY would yield the following relation:height
name sex address name (in) age
Smith M Lexington Park Smith 75 50
Jones F Washington DC Jones 77 26
Taylor M Detroit Taylor 65 36
Rupert F Hollywood Rupert 62 30
Burr M Lusby Burr 71 25
Zimmerman F Pittsburgh Zimmerman 61 38
Roberts F Cherry Valley Roberts 68 40
Malcolm M Akron Malcolm 72 20
Tefft M Ridge Tefft 70 29
Carpenter M Lexington Park Carpenter 74 30
Day F Hollywood Day 64 25
Lucas M Hollywood Lucas 70 19Null Values and Trivalue Logic
One concept that was added to basic set theory was the
concept of null value. An attribute that has a value of null
is said to be an attribute that is not applicable, unknown,
or undefined for the data row. One must be careful to prop-
erly account for the possibility of null values appearing in
a database.
Null values, when used in numeric computations, act
like infinity; anything we do in conjunction with a null
value ends up as null. For example, if we add 3+null,
the result is null. When used in character operations
(such as concatenation) the null typically acts like a
blank.
The two places where one must handle the null are in
theselectstatement and thewhereclause. In the select
statement, we must decide what to display to the final re-
cipient of the SQL statement that we are inputting instead
of the null, which typically displays as a blank character.
In thewhereclause, the results are more profound. In try-
ing to evaluate awhereclause where portions of the clause
may null out (return a value of null), the desired result set
may not be returned. This is due to the fact that SQL uses
a trivalue logic set as shown in the table below:p q p AND q p OR q NOT p
True True True True False
True False False True False
True Null Null True False
False True False True True
False False False False True
False Null False Null True
Null True Null True Null
Null False False Null Null
Null Null Null Null NullSo if one portion of awhereclause nulls out, depend-
ing on the operation, the entirewhereclause may null out,leaving the empty set as our result set. For the “and” oper-
ation to return a value of “True” both predicates (“p” and
“q” above) must evaluate to “True.” Because the value of
null is unknown (it could be “True”), the evaluation can-
not be complete and therefore returns a null value. For the
“or” operation, only one of the predicates must evaluate to
“True.” The not operation simply changes the predicate’s
value: a “True” value become a “False” value, and so on.IBM’S SEQUEL LANGUAGE
Even though Codd proposed relational databases in 1970,
it was not until 1974 that Chanberlin and Boyce pub-
lished an article proposing the form of a structured query
language, named SEQUEL (Hursch & Hursch, 1988,
pp. 1–2). This name is often used (incorrectly) today to
reference the present-day SQL language.STRUCTURED QUERY LANGUAGE
All of the basic concepts of relational algebra are
present in today’s relational database management sys-
tems (RDBMs). However, the concept of a domain is
only implemented for primitive data types, such as in-
teger, float, date, versus any data type, as defined in the
model (Date, 1994, p. 66). SQL is the American Na-
tional Standards Institute (ANSI) standardized version
of IBM’s SEQUEL “data sublanguage,” for use in a re-
lational database (Hursch & Hursch, 1988, p. 1). SQL
(pronounced “ess, queue, ell”) is more than a language
for retrieving information out of tables; it also “includes
features for defining the structure of the data, for mod-
ifying the data in the database, and specifying security
constraints. Each feature has its own set of statements
that are expressed in, respectively, Data Definition Lan-
guage (DDL), Data Manipulation Language (DML), and
Data Control Language (DCL)” (Hursch & Hursch, 1988,
p. 1). DML implements the relational operators and will
be addressed later.
Sets of data, relations, are stored in a RDBMS in the
form of tables. Each table consists of rows and columns.
Each row of data is a specific and unique instance (entity)
of a member of the table (i.e., a tuple). Each column is
said to be an attribute of the table. To create the database
objects required, SQL’s DDL provides the following data
definition statements (DDS): CREATE TABLE, CREATE
INDEX, ALTER TABLE, DROP TABLE, DROP VIEW,
and DROP INDEX. The table names created with these
statements are the identifiers used by the DML to resolve
SQL statements.
SQL’s DML supports the following operations on ta-
bles: INSERT, UPDATE, DELETE, SELECT, and CREATE
VIEW. INSERT, UPDATE, and DELETE operations are
data maintenance functions and will not be addressed.
The SQL SELECT is the data retrieval mechanism. The
CREATE VIEW statement is an alternative way of look-
ing at the data in one or more tables called a derived table
and is based upon the SELECT statement.
SQL’s DCL is used to control authorization for data ac-
cess and auditing database use. DCL statements include
the GRANT and REVOKE statements. Database adminis-
trators and developers primarily use these operations.