SQL : the complete reference

1045 Pages·2002·14.906 MB·English

by  James R Groff, Paul N Weinberg

SQL:
The Complete Reference,
Second Edition
James R. Groff and Paul N. Weinberg
McGraw-Hill/Osborne
New York Chicago San Francisco
Lisbon London Madrid Mexico City
Milan New Delhi San Juan
Seoul Singapore Sydney Toronto
Copyright © 2002 by The McGraw-HIll Companies, Inc. All rights reserved. Manufactured in the
United States of America. Except as permitted under the United States Copyright Act of 1976, no part
of this publication may be reproduced or distributed in any form or by any means, or stored in a data-
base or retrieval system, without the prior written permission of the publisher.
0-07-222817-2
The material in this eBook also appears in the print version of this title: 0-07-222559-9
All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after
every occurrence of a trademarked name, we use names in an editorial fashion only, and to the benefit
of the trademark owner, with no intention of infringement of the trademark. Where such designations
appear in this book, they have been printed with initial caps.
McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales pro-
motions, or for use in corporate training programs. For more information, please contact George
Hoare, Special Sales, at [email protected] or [phone].
TERMSOFUSE
This is a copyrighted work and The McGraw-Hill Companies, Inc. (“McGraw-Hill”) and its licensors
reserve all rights in and to the work. Use of this work is subject to these terms. Except as permitted
under the Copyright Act of 1976 and the right to store and retrieve one copy of the work, you may not
decompile, disassemble, reverse engineer, reproduce, modify, create derivative works based upon,
transmit, distribute, disseminate, sell, publish or sublicense the work or any part of it without
McGraw-Hill’s prior consent. You may use the work for your own noncommercial and personal use;
any other use of the work is strictly prohibited. Your right to use the work may be terminated if you
fail to comply with these terms.
THE WORK IS PROVIDED “AS IS”. McGRAW-HILLAND ITS LICENSORS MAKE NO GUAR-
ANTEES OR WARRANTIES AS TO THE ACCURACY, ADEQUACYOR COMPLETENESS OF
OR RESULTS TO BE OBTAINED FROM USING THE WORK, INCLUDING ANYINFORMA-
TION THATCAN BE ACCESSED THROUGH THE WORK VIAHYPERLINK OR OTHERWISE,
AND EXPRESSLYDISCLAIM ANYWARRANTY, EXPRESS OR IMPLIED, INCLUDING BUT
NOTLIMITED TO IMPLIED WARRANTIES OF MERCHANTABILITYOR FITNESS FOR A
PARTICULAR PURPOSE. McGraw-Hill and its licensors do not warrant or guarantee that the func-
tions contained in the work will meet your requirements or that its operation will be uninterrupted or
error free. Neither McGraw-Hill nor its licensors shall be liable to you or anyone else for any inac-
curacy, error or omission, regardless of cause, in the work or for any damages resulting therefrom.
McGraw-Hill has no responsibility for the content of any information accessed through the work.
Under no circumstances shall McGraw-Hill and/or its licensors be liable for any indirect, incidental,
special, punitive, consequential or similar damages that result from the use of or inability to use the
work, even if any of them has been advised of the possibility of such damages. This limitation of lia-
bility shall apply to any claim or cause whatsoever whether such claim or cause arises in contract, tort
or otherwise.
DOI: 10.1036/0072228172
For more information about this title, click here.
Contents
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
Part I
An Overview of SQL
1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
The SQL Language . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
The Role of SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
SQL Features and Benefits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Vendor Independence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Portability Across Computer Systems . . . . . . . . . . . . . . . . . . . . . . . 9
SQL Standards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
IBM Endorsement and Commitment (DB2) . . . . . . . . . . . . . . . . . . 9
Microsoft Commitment (SQL Server, ODBC, and ADO) . . . . . . . 9
Relational Foundation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
High-Level, English-Like Structure . . . . . . . . . . . . . . . . . . . . . . . . . 10
Interactive, Ad Hoc Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Programmatic Database Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Multiple Views of Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Complete Database Language . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Dynamic Data Definition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Client/Server Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Enterprise Application Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
v
Copyright 2002 by The McGraw-Hill Companies, Inc. Click Here for Terms of Use.
vi SQL: The Complete Reference
Extensibility and Object Technology . . . . . . . . . . . . . . . . . . . . . . . . 12
Internet Database Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Java Integration (JDBC) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Industry Infrastructure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
2 A Quick Tour of SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
A Simple Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Retrieving Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Summarizing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Adding Data to the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Deleting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Updating the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Protecting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Creating a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
3 SQL in Perspective . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
SQL and Database Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
A Brief History of SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
The Early Years . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Early Relational Products . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
IBM Products . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Commercial Acceptance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
SQL Standards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
The ANSI/ISO Standards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Other SQL Standards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
ODBC and the SQL Access Group . . . . . . . . . . . . . . . . . . . . . . . . . . 34
SQL and Portability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
SQL and Networking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Centralized Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
File Server Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Client/Server Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Multitier Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
The Proliferation of SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
SQL and IBM’s Unified Database Strategy . . . . . . . . . . . . . . . . . . . 42
SQL on Minicomputers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
SQL on UNIX-Based Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
SQL on Personal Computers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
SQL and Transaction Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
SQL and Workgroup Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
SQL and Data Warehousing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
SQL and Distributed Internet Applications . . . . . . . . . . . . . . . . . . 48
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
4 Relational Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Early Data Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
File Management Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Hierarchical Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
Network Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
Contents vii
The Relational Data Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
The Sample Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Primary Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Codd’s 12 Rules * . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
Part II
Retrieving Data
5 SQL Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Table Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Column Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Constants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Numeric Constants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
String Constants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
Date and Time Constants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
Symbolic Constants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
Built-In Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
Missing Data (NULLValues) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
6 Simple Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
TheSELECTStatement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
TheSELECTClause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
TheFROMClause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Query Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Simple Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
Calculated Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Selecting All Columns (SELECT*) . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Duplicate Rows (DISTINCT) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Row Selection (WHEREClause) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Search Conditions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
The Comparison Test (=, <>, <, <=, >, >=) . . . . . . . . . . . . . . . . . . . 111
The Range Test (BETWEEN) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
The Set Membership Test (IN) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118
The Pattern Matching Test (LIKE) . . . . . . . . . . . . . . . . . . . . . . . . . . 120
The Null Value Test (ISNULL) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
Compound Search Conditions (AND,OR,andNOT) . . . . . . . . . . . . 124
Sorting Query Results (ORDERBYClause) . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
Rules for Single-Table Query Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130
Combining Query Results (UNION) * . . . . . . . . . . . . . . . . . . . . . . . . 131
Unions and Duplicate Rows * . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
viii SQL: The Complete Reference
Unions and Sorting * . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
Multiple UNIONs* . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
7 Multitable Queries (Joins) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
A Two-Table Query Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
Simple Joins (Equi-Joins) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Parent/Child Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
Joins with Row Selection Criteria . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
Multiple Matching Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147
Queries with Three or More Tables . . . . . . . . . . . . . . . . . . . . . . . . . 148
Other Equi-Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152
Non-Equi-Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
SQL Considerations for Multitable Queries . . . . . . . . . . . . . . . . . . . . . . . . . . 154
Qualified Column Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
All-Column Selections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Self-Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Table Aliases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
Multitable Query Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
The Structure of a Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
Table Multiplication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
Rules for Multitable Query Processing . . . . . . . . . . . . . . . . . . . . . . 164
Outer Joins * . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
Left and Right Outer Joins * . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
Outer Join Notation * . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Joins and the SQL2 Standard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
Inner Joins in SQL2 * . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
Outer Joins in SQL2 * . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
Cross Joins and Union Joins in SQL2 * . . . . . . . . . . . . . . . . . . . . . . . 180
Multitable Joins in SQL2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
8 Summary Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
Column Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188
Computing a Column Total (SUM) . . . . . . . . . . . . . . . . . . . . . . . . . . 190
Computing a Column Average (AVG) . . . . . . . . . . . . . . . . . . . . . . . 191
Finding Extreme Values (MINandMAX) . . . . . . . . . . . . . . . . . . . . . 191
Counting Data Values (COUNT) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
Column Functions in the Select List . . . . . . . . . . . . . . . . . . . . . . . . . 195
NULLValues and Column Functions . . . . . . . . . . . . . . . . . . . . . . . . 197
Duplicate Row Elimination (DISTINCT) . . . . . . . . . . . . . . . . . . . . . 199
Grouped Queries (GROUPBYClause) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
Multiple Grouping Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204
Restrictions on Grouped Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . 207
NULLValues in Grouping Columns . . . . . . . . . . . . . . . . . . . . . . . . . 209
Group Search Conditions (HAVINGClause) . . . . . . . . . . . . . . . . . . . . . . . . . . 211
Restrictions on Group Search Conditions . . . . . . . . . . . . . . . . . . . . 214
NULLValues and Group Search Conditions . . . . . . . . . . . . . . . . . . 215
HAVINGWithoutGROUPBY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
Contents ix
9 Subqueries and Query Expressions . . . . . . . . . . . . . . . . . . . . . 217
Using Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218
What Is a Subquery? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
Subqueries in theWHEREClause . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220
Outer References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
Subquery Search Conditions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223
The Subquery Comparison Test (=, <>, <, <=, >, >=) . . . . . . . . . . 223
The Set Membership Test (IN) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226
The Existence Test (EXISTS) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
Quantified Tests (ANYandALL) * . . . . . . . . . . . . . . . . . . . . . . . . . . . 230
Subqueries and Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236
Nested Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
Correlated Subqueries * . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239
Subqueries in theHAVINGClause * . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
Subquery Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
Advanced Queries in SQL2 * . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
Scalar-Valued Expressions (SQL2) . . . . . . . . . . . . . . . . . . . . . . . . . . 247
Row-Valued Expressions (SQL2) . . . . . . . . . . . . . . . . . . . . . . . . . . . 253
Table-Valued Expressions (SQL2) . . . . . . . . . . . . . . . . . . . . . . . . . . 257
Query Expressions (SQL2) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261
SQL Queries: A Final Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265
Part III
Updating Data
10 Database Updates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
Adding Data to the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270
The Single-RowINSERTStatement . . . . . . . . . . . . . . . . . . . . . . . . . 271
The MultirowINSERTStatement . . . . . . . . . . . . . . . . . . . . . . . . . . . 275
Bulk Load Utilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 278
Deleting Data from the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279
TheDELETEStatement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279
Deleting All Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281
DELETEwithSubquery * . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282
Modifying Data in the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284
TheUPDATEStatement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
Updating All Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287
UPDATEwithSubquery * . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 288
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289
11 Data Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291
What Is Data Integrity? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292
Required Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
Simple Validity Checking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 294
Column Check Constraints (SQL2) . . . . . . . . . . . . . . . . . . . . . . . . . 295
Domains (SQL2) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296
Entity Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298
Other Uniqueness Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298
Uniqueness andNULLValues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298
x SQL: The Complete Reference
Referential Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299
Referential Integrity Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300
Delete and Update Rules * . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302
Cascaded Deletes and Updates * . . . . . . . . . . . . . . . . . . . . . . . . . . . 305
Referential Cycles * . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 308
Foreign Keys andNULLValues * . . . . . . . . . . . . . . . . . . . . . . . . . . . 312
Advanced Constraint Capabilities (SQL2) . . . . . . . . . . . . . . . . . . . . . . . . . . . 313
Assertions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314
SQL2 Constraint Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315
Deferred Constraint Checking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316
Business Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 319
What Is a Trigger? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 320
Triggers and Referential Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . 322
Trigger Advantages and Disadvantages . . . . . . . . . . . . . . . . . . . . . 323
Triggers and the SQL Standard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325
12 Transaction Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327
What Is a Transaction? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 328
COMMITandROLLBACK . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329
The ANSI/ISO Transaction Model . . . . . . . . . . . . . . . . . . . . . . . . . . 332
Other Transaction Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334
Transactions: Behind the Scenes * . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336
Transactions and Multiuser Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 338
The Lost Update Problem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 338
The Uncommitted Data Problem . . . . . . . . . . . . . . . . . . . . . . . . . . . 339
The Inconsistent Data Problem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341
The Phantom Insert Problem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342
Concurrent Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342
Locking * . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345
Locking Levels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345
Shared and Exclusive Locks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348
Deadlocks * . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348
Advanced Locking Techniques * . . . . . . . . . . . . . . . . . . . . . . . . . . . 351
Versioning * . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357
Versioning in Operation * . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357
Versioning Advantages and Disadvantages * . . . . . . . . . . . . . . . . 360
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361
Part IV
Database Structure
13 Creating a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365
The Data Definition Language . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 366
Creating a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367
Table Definitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369
Creating a Table (CREATE TABLE) . . . . . . . . . . . . . . . . . . . . . . . . . 369
Removing a Table (DROP TABLE) . . . . . . . . . . . . . . . . . . . . . . . . . . 379
Changing a Table Definition (ALTER TABLE) . . . . . . . . . . . . . . . . 380
Contents xi
Constraint Definitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 384
Assertions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 384
Domains . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385
Aliases and Synonyms (CREATE/DROPALIAS) . . . . . . . . . . . . . . . . . . . . . . 386
Indexes (CREATE/DROP INDEX) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 387
Managing Other Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 391
Database Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 395
Single-Database Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396
Multidatabase Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397
Multilocation Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 399
Databases on Multiple Servers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401
Database Structure and the ANSI/ISO Standard . . . . . . . . . . . . . . . . . . . . . 401
SQL2 Catalogs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 404
SQL2 Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 404
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 408
14 Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409
What Is a View? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 410
How the DBMS Handles Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . 412
Advantages of Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 412
Disadvantages of Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413
Creating a View (CREATEVIEW) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413
Horizontal Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 414
Vertical Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 416
Row/Column Subset Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 418
Grouped Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 418
Joined Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 421
Updating a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 423
View Updates and the ANSI/ISO Standard . . . . . . . . . . . . . . . . . . 424
View Updates in Commercial SQL Products . . . . . . . . . . . . . . . . . 425
Checking View Updates (CHECKOPTION) . . . . . . . . . . . . . . . . . . . 425
Dropping a View (DROPVIEW) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 428
Materialized Views * . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 429
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 432
15 SQL Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 433
SQL Security Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 434
User-Ids . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436
Security Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439
Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 440
Views and SQL Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 443
Granting Privileges (GRANT) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 446
Column Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 448
Passing Privileges (GRANT OPTION) . . . . . . . . . . . . . . . . . . . . . . . . 449
Revoking Privileges (REVOKE) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451
REVOKEand theGRANT OPTION . . . . . . . . . . . . . . . . . . . . . . . . . . . 454
REVOKEand the ANSI/ISO Standard . . . . . . . . . . . . . . . . . . . . . . . 456
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 457
xii SQL: The Complete Reference
16 The System Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459
What Is the System Catalog? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 460
The Catalog and Query Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 461
The Catalog and the ANSI/ISO Standard . . . . . . . . . . . . . . . . . . . . 461
Catalog Contents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 462
Table Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 464
Column Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 468
View Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471
Remarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 474
Relationship Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 475
User Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 478
Privileges Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 480
The SQL2 Information Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481
Other Catalog Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487
Part V
Programming with SQL
17 Embedded SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491
Programmatic SQL Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 492
DBMS Statement Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 494
Embedded SQL Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 496
Developing an Embedded SQL Program . . . . . . . . . . . . . . . . . . . . 496
Running an Embedded SQL Program . . . . . . . . . . . . . . . . . . . . . . . 500
Simple Embedded SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 502
Declaring Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 505
Error Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 506
Using Host Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 515
Data Retrieval in Embedded SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 524
Single-Row Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 524
Multirow Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 531
Cursor-Based Deletes and Updates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 539
Cursors and Transaction Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 544
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 545
18 Dynamic SQL * . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 547
Limitations of Static SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 548
Dynamic SQL Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 550
Dynamic Statement Execution (EXECUTEIMMEDIATE) . . . . . . . . . . . . . . . . 551
Two-Step Dynamic Execution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 554
ThePREPAREStatement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 558
TheEXECUTEStatement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 558
Dynamic Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 566
TheDESCRIBEStatement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 572
TheDECLARE CURSORStatement . . . . . . . . . . . . . . . . . . . . . . . . . . 574
The DynamicOPENStatement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 575
The DynamicFETCHStatement . . . . . . . . . . . . . . . . . . . . . . . . . . . . 578
The DynamicCLOSEStatement . . . . . . . . . . . . . . . . . . . . . . . . . . . . 578