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
Structured Query Language (SQL)Structured Query Language (SQL)
Erick D. Slazinski,Purdue UniversityIntroduction 353
Mathematical Beginnings 353
Basic Set Theory 353
Relational Algebra 354
Null Values and Trivalue Logic 355
IBM’S Sequel Language 355
Structured Query Language 355
ANSI Standardization 356
DDL 356
DML 356
The Select Statement 356
Single Table Access and the Result Set 356
Reducing the Size of the Result Set 356
Computations 357
Arranging the Result Set 357
Segregating the Result Set 357
Reducing the Segregated Data 358
Handling Null Values 358
Joins 358
Equi-joins 358
Natural 358
Cartesian 358
Union 358
Minus 359
DDL 359Create Table 359
Keys 359
Create Index 359
Create View 359
Constraints 359
Domains 360
DCL 360
Granting Access to Data 360
DML 360
Inserting Data 360
Modifying Data 360
Removing Data 361
Transaction Control 361
Multiuser Environments 361
Concurrency Issues 361
Deadlock 361
Enhanced Versions of SQL 362
Procedural Extensions to SQL 362
Stored Modules 362
Triggers 362
Conclusion 362
Appendix: Sample Schema and Data 362
Glossary 363
Cross References 363
References 364INTRODUCTION
Applications developed today, including Internet applica-
tions, data-mining software, and other general applica-
tions, will most likely have database components on the
back end. Example applications vary from the online CD
database located at CDDB.com to a recipe database, like
the one located at FOODTV.COM. The databases used may
range from powerful enterprise-scale versions of Oracle,
DB2, and Sybase to more personal desktop version like
Microsoft Access. This chapter will focus primarily on
standard SQL, not any specific implementation. Regard-
less of the database platform, one accesses the data utiliz-
ing the provided query language. Because most popular
databases use the relational model, the query language
provided is a derivative of SQL. Currently most database
systems provide users with languages such as SQL to al-
low them to retrieve or update stored information (Chan,
Tan, & Wei, 1999).
There are several mechanisms for a user to interact
with a database. Most database vendors provide users
with line-mode interfaces where a user can enter sup-
ported statements and receive results. Other, graphical
interfaces may be available from a database vendor or
a third-party application. These graphical interfaces pro-
vide what is known as query-by-example (QBE) facilities.
QBE was developed by IBM in the 1970s to help users in
their retrieval of data (Connolly & Begg, 2002, p. 197). TheQBE facilities provided by database vendors either allow
users to graphically depict queries or provide templates
for users to fill out. These facilities then generate SQL,
which often can be viewed and modified and which then
is sent to the database engine for execution.MATHEMATICAL BEGINNINGS
Although database technology has been around for quite
some time and in various forms, the relational model
put forth in 1970 by E. F. Codd has enjoyed the great-
est success. The relational model is firmly grounded in
relational algebra, which has roots in basic set theory.
This actually makes the basics of SQL accessible to most
people.Basic Set Theory
In basic set theory, a universe of domain is specified. In
the database arena this translates to all of the data in-
cluded in a database. In our universe, items may be cat-
egorized (perhaps multiple times) into set(s) and then
relationships between these sets may be explored, using
inclusion and exclusion operations. Likewise, in the rela-
tional model, data are often retrieved as result sets. These
result sets may then be compared either inclusively or
exclusively.353