Fourth Edition
A Guide to
the SQL Standard
A user's guide to
i
the standard database language SQL
Fachbereichsbibliothek Informatik
TU Darmstadt
C J. Date
with
Hugh Darwen
Technische Universitat Darmstadt
FACHBEREICH INFORMATIK
B 1 BL 1 0 T H E K
n ^ - co>| a"
kiventar-Nr.:
Sachgebiete:.
Standort:
%*\
i
re
ADDISON-WESLEY
An imprint of Addison-Wesiey Longman, Inc.
Reading, Massachusetts Harlow, England ^Menlo Park, California Berkeley, California
Don Mills, Ontario Sydney Bonn Amsterdam Tokyo Mexico City
Contents
PART I
INTRODUCTION
CHAPTER l / Why SQL Is Important
1.1 Background
3
1.2 Is a standard desirable?
CHAPTER 2 / An Overview of SQL
2.1
2.2
2.3
2.4
2.5
2.6
2.7
2.8
2.9
Introduction
-9
Data definition
11
Data manipulation
13
Cursor operations
16
Views
18
Security and integrity
20
Recovery and concurrency
22
Schemas and catalogs
23
Sessions and connections
25
xvi
Contents
PART II
SOME PRELIMINARIES
CHAPTER 3 / Basic Language Elements
3.1
3.2
3.3
3.4
3.5
3.6
SQL language characters
29
Tokens and separators
30
Key words
31
Identifiers and names
33
Scope of names
35
Notation
37
CHAPTER 4 / Catalogs and Schemas
4.1 The SQL-environment
4.2 Catalogs
42
4.3 Schemas
43
49
SQL-agents
49
SQL-connections
51
SQL-sessions
54
SQL-transactions
56
CHAPTER
6.1
6.2
6.3
6.4
41
41
CHAPTER 5 / Connections, Sessions, and Transactions
5.1
5.2
5.3
5.4
29
6 / Binding Styles
Introduction
63
The module language
Embedded SQL
71
Direct SQL
74
63
63
PART III DATA DEFINITION AND MANIPULATION
CHAPTER 7 / Scalar Objects, Operators, and Expressions
7.1 Introduction
7.2 Datatypes
79
81
79
Contents
7.3
7.4
7.5
7.6
Literals
84
Scalar operators and functions
Aggregate functions
92
Assignments and comparisons
85
95
CHAPTER 8 / Data Definition: Domains and Base Tables
99
8.1 Introduction
99
8.2 Domains
100
8.3 Base tables
103
CHAPTER 9 / Data Manipulation: Noncursor Operations
9.1
9.2
9.3
9.4
9.5
109
Introduction
109
Single-row SELECT
110
INSERT
111
Searched UPDATE
113
Searched DELETE
114
CHAPTER io / Data Manipulation: Cursor Operations
10.1
10.2
10.3
10.4
10.5
Introduction
115
Cursors
116
Cursor-based manipulation statements
121
A comprehensive example (module version)
124
A comprehensive example (embedded SQL version)
CHAPTER l l / Table Expressions
11.1
11.2
11.3
11.4
11.5
11.6
11.7
Introduction
133
Join expressions
135
Table references
137
Unions, differences, and intersections
Table primaries
148
Select expressions
150
Derived table column names
161
115
128
133
144
Contents
CHAPTER 12 / Conditional Expressions
12.1
12.2
12.3
12.4
12.5
12.6
12.7
12.8
12.9
Introduction
165
General remarks
167
Comparison conditions
168
BETWEEN and LIKE conditions
169
IN and MATCH conditions
171
ALL-or-ANY conditions
176
EXISTS conditions
176
UNIQUE conditions
177
A final remark
178
CHAPTER 13 / Views
13.1
13.2
13.3
13.4
165
179
Introduction
179
Data definition operations
}82
Data manipulation operations
184
The check option
187
PART IV DATA CONTROL
CHAPTER 14 / Integrity
14.1
14.2
14.3
14.4
14.5
14.6
Introduction
197
Domain constraints
200
General constraints
202
Base table and column constraints
204
Foreign keys
210
Deferred constraint checking
216
CHAPTER 15 / Security
i
15.1
15.2
15.3
15.4
15.5
15.6
197
Introduction
219
Authorization identifiers
220
Privileges and access rules
222
GRANT and REVOKE
224
The grant option
227
RESTRICT vs. CASCADE
228
219
Contents
PART V ADVANCED TOPICS
CHAPTER
16.1
16.2
16.3
16.4
16.5
16.6
16 / Missing Information and Nulls
233
Introduction
233
Effect of nulls on scalar expressions
235
Effect of nulls on conditional expressions
239
Effect of nulls on table expressions
248
Effect of nulls on integrity constraints
253
A recommendation
257
CHAPTER 17 / Dates and Times
17.1
17.2
17.3
17.4
17.5
17.6
17.7
Introduction
259
Datatypes
259
Literals
263
Time zones
265
Data conversion
270
Scalar operators and functions
Assignments and comparisons
259
272
276
CHAPTER 18 / Temporary Tables
18.1 Introduction
281
18.2 Type 1: "Declared" temporary tables
282
18.3 Types 2 and 3: "Created" temporary tables
CHAPTER 19 / Character Data
19.1
19.2
19.3
19.4
19.5
19.6
19.7
19.8
Introduction
285
Properties of character strings
287
Character sets and collations
288
Datatypes
292
Literals
293
Scalar operators and functions
295
Assignments and comparisons
298
Character sets for identifiers
305
281
283
285
xx
Contents
CHAPTER 20 / Dynamic SQL
20.1
20.2
20.3
20.4
20.5
20.6
309
Introduction
309
What is the problem?
312
Statement preparation and execution
SQL descriptor areas
322
Cursor operations
331
Session defaults
337
314
CHAPTER 21 / Information Schemas
21.1 Introduction
339
21.2 Information Schema tables
CHAPTER
339
341
22 / Exception Handling
22.1 Status codes
347
22.2 The diagnostics area
347
350
CHAPTER 23 / Call-Level Interface
23.1
23.2
23.3
23.4
23.5
23.6
23.7
23.8
23.9
23.10
23.11
353
Introduction
353
What is the problem?
355
CLI routines and parameters
357
CLI resources and related matters
361
Statement preparation and execution
365
CLI descriptor areas
366
Placeholders
369
Retrieval operations
372
Exception handling
376
A sample "SQLCLI.H" file
377
A sample CLI application
389
APPENDIXES
APPENDIX A / An SQL Grammar
A.I Introduction
395
A.2 Sessions, connections, and transactions
395
397
Contents
A.3
A.4
A.5
A.6
A.7
A.8
A.9
A.10
[Link]
A.12
Data definition
398
Modules
400
Data manipulation
401
Table expressions
402
Conditional expressions
403
Constraints
404
Dynamic SQL
405
Scalar expressions
406
GET DIAGNOSTICS
407
Miscellaneous
410
APPENDIX B / Language Levels and Conformance
B.I
B.2
B.3
B.4
411
Introduction
411
Intermediate SQL
412
Entry SQL
414
SQLflagger
416
APPENDIX C / SQL/92 vs. SQL/89
C.I
C.2
C.3
C.4
xxi
419
Introduction
419
Extensions
419
Incompatibilities
422
Deprecated features
425
APPENDIX D / Some Outstanding Issues
427
D.I Introduction
427
D.2 The issues
429 /
APPENDIX E
E.I
E.2
E.3
E.4
E.5
E.6
E.7
E.8
E.9
/ Persistent Stored Modules
Introduction
453
Overview
456
Routines, modules, and schemas
467
Type precedence
472
Subject routine determination
474
SQL control statements
476
Exception handling
483
External routines
487
Miscellaneous topics
491
453
xxii
Contents
APPENDIX F / An Overview of SQL3
F.I
F.2
F.3
F.4
F.5
F.6
Introduction
495
Data definition and manipulation
Functions and operators
502
Integrity
506
Security
507
Transactions
507
495
497
APPENDIX G / References and Bibliography
509
Index
513
