SQL: 1999 - Understanding Relational Language Components (The Morgan Kaufmann Series in Data Management Systems)

Front Cover......Page 1
SQL:1999 Understanding Relational Language Components......Page 4
Copyright Page......Page 5
Contents......Page 10
Preface......Page 26
1.1 Introduction......Page 34
1.2 Introduction to Database Technology......Page 35
1.3 Database Languages......Page 49
1.4 The Transaction Model......Page 52
1.5 Chapter Summary......Page 55
2.2 SQL History and Background......Page 56
2.3 SQL Concepts......Page 59
2.4 SQL Data Types......Page 65
2.5 Logic and Null Values......Page 82
2.6 Data Conversions......Page 85
2.8 Static Versus Dynamic Execution......Page 87
2.9 Conforming to SQL:1999......Page 88
2.10 Relationship to Products......Page 89
2.11 Identifiers......Page 90
2.12 Chapter Summary......Page 91
3.1 Introduction......Page 92
3.2 Our Example......Page 93
3.3 Basic Table Creation......Page 94
3.4 Set Orientation......Page 98
3.5 Basic Data Retrieval......Page 99
3.6 Updating Information......Page 108
3.7 Inserting Information......Page 112
3.8 Deleting Information......Page 114
3.9 Chapter Summary......Page 115
4.2 Data Definition Fundamentals......Page 116
4.3 Basic DDL Statements......Page 125
4.4 WITH CHECK OPTION......Page 144
4.5 Chapter Summary......Page 145
5.2 Types of SQL Values......Page 146
5.3 Literals......Page 147
5.4 Parameters......Page 152
5.5 Special Values......Page 158
5.6 Column References and Field References......Page 159
5.8 Set Functions......Page 165
5.9 Value Functions......Page 176
5.10 Value Expressions......Page 186
5.11 Chapter Summary......Page 195
6.1 Introduction......Page 198
6.2 The CASE Expression......Page 199
6.3 The CAST Expression......Page 204
6.4 Row Value Constructors......Page 207
6.5 Table Value Constructors......Page 209
6.6 Array Value Constructors......Page 210
6.7 Chapter Summary......Page 211
7.1 Introduction......Page 212
7.3 Subqueries......Page 213
7.4 Comparison Predicate......Page 214
7.5 IS NULL Predicate......Page 220
7.6 IN Predicate......Page 222
7.7 LIKE and SIMILAR Predicates......Page 223
7.8 EXISTS and UNIQUE Predicates......Page 238
7.9 OVERLAPS Predicate......Page 240
7.10 SOME, ANY, and ALL......Page 242
7.11 MATCH Predicate......Page 245
7.12 DISTINCT Predicate......Page 248
7.14 Search Conditions......Page 250
7.15 Chapter Summary......Page 254
8.2 Join Operations: An Overview......Page 256
8.3 Types of Join Operations......Page 258
8.4 The UNION Operator......Page 282
8.5 UNION JOIN......Page 287
8.6 The INTERSECT and EXCEPT Operators......Page 289
8.7 Another Example......Page 293
8.8 Chapter Summary......Page 296
9.1 Introduction......Page 298
9.2 Query Specifications......Page 299
9.3 Functional Dependencies: Rules for Updatability......Page 318
9.4 Query Expressions......Page 326
9.5 Joined Table......Page 334
9.6 Grouped Tables......Page 342
9.7 Result Data Types of Columns of UNION, EXCEPT, and INTERSECT......Page 345
9.9 Table Value Constructor......Page 347
9.10 Subqueries......Page 350
9.11 Table References......Page 354
9.12 CUBE and ROLLUP......Page 359
9.13 Recursive Queries......Page 371
9.14 Chapter Summary......Page 386
10.1 Introduction......Page 388
10.2 Column Constraints and Table Constraints......Page 391
10.3 Assertions......Page 406
10.4 PRIMARY KEY......Page 409
10.5 Referential Integrity......Page 412
10.6 Multiple Cascades......Page 423
10.7 More about Referential Integrity Constraints......Page 425
10.8 More about Constraints and Assertions......Page 426
10.9 Chapter Summary......Page 427
11.1 Introduction......Page 428
11.3 Triggers......Page 429
11.4 Interaction between Referential Actions and Triggers......Page 442
11.5 Chapter Summary......Page 443
12.1 Introduction......Page 444
12.2 Data Access Methods and Data Types......Page 445
12.4 Direct Invocation......Page 447
12.5 Embedded SQL......Page 448
12.6 Module Language......Page 464
12.7 The Impedance Mismatch......Page 470
12.8 Other Binding Styles......Page 471
12.9 Package Interfaces.SQL......Page 473
12.10 Chapter Summary......Page 479
13.2 Cursors: The Basics......Page 480
13.3 OPEN and CLOSE......Page 496
13.4 FETCH......Page 497
13.5 Cursor Positioning......Page 498
13.6 Chapter Summary......Page 500
14.1 Introduction......Page 502
14.2 Users and Roles......Page 505
14.3 GRANT......Page 515
14.4 WITH GRANT OPTION and WITH ADMIN OPTION......Page 525
14.5 Changing User Identifiers and Role Names......Page 526
14.6 Other Rules......Page 528
14.7 REVOKE......Page 529
14.8 Additional Details of REVOKE......Page 533
14.9 Chapter Summary......Page 541
15.1 Introduction......Page 544
15.2 SQL:1999 Transaction Syntax......Page 545
15.3 SQL:1999 Isolation Levels......Page 549
15.5 Transaction Termination......Page 556
15.6 Transactions and Constraints......Page 557
15.7 Savepoints—Establishment and Removal......Page 559
15.8 Additional Transaction Termination Actions......Page 561
15.9 Chapter Summary......Page 564
16.1 Introduction......Page 566
16.2 Establishing Connections......Page 567
16.3 Connection Statements......Page 569
16.4 RDA (Remote Database Access)......Page 572
16.6 Chapter Summary......Page 573
17.1 Introduction......Page 574
17.2 Relationship of SQL:1999's Routines to SQL/PSM......Page 575
17.3 SQL-Invoked Routines......Page 576
17.4 External Routines......Page 590
17.5 SQL Routines......Page 595
17.6 Limitations of SQL for Stored Routines......Page 596
17.8 Routine Resolution Outline......Page 597
17.9 Chapter Summary......Page 600
18.2 What Is Dynamic SQL?......Page 602
18.3 The Philosophy of Dynamic SQL......Page 604
18.4 Outline of Dynamic SQL Processing......Page 607
18.5 The EXECUTE IMMEDIATE Statement......Page 622
18.6 PREPARE and EXECUTE Statements......Page 623
18.7 The DESCRIBE Statement......Page 627
18.8 Rows, Collections, and the DESCRIBE Statement......Page 635
18.9 The EXECUTE Statement Revisited......Page 640
18.10 Dynamic SQL and Cursors......Page 643
18.11 A Dynamic SQL Example......Page 649
18.12 Chapter Summary......Page 656
19.1 Introduction......Page 658
19.2 A Brief History of SQL/CLI and ODBC......Page 659
19.3 Brief Comparison of SQL/CLI and Dynamic SQL......Page 660
19.4 The SQL/CLI Model......Page 662
19.5 Handles......Page 663
19.7 Database Connections and Implicit Connections......Page 667
19.8 CLI Descriptor Areas Versus Dynamic Descriptor Areas......Page 670
19.9 Statement Preparation and Execution......Page 676
19.10 Deferred Parameters......Page 683
19.11 Transaction Management......Page 690
19.12 Diagnostics Facilities......Page 691
19.13 Metadata Access and Catalog Routines......Page 695
19.14 CLI Routines Summary......Page 696
19.15 Annotated CLI Example......Page 710
19.16 Chapter Summary......Page 714
20.2 SQLSTATE......Page 716
20.3 Whatever Happened to SQLCODE?......Page 728
20.4 SQL Statement Codes......Page 729
20.5 Chapter Summary......Page 732
21.1 Introduction......Page 734
21.2 Character Sets and Collations......Page 735
21.3 Translations and Conversions......Page 744
21.4 Mapping Host Language Capabilities to SQL's Character Set Facilities......Page 745
21.5 Equal, Distinct, Identical......Page 746
21.6 Chapter Summary......Page 747
22.2 Metadata, Repositories, and the INFORMATION_SCHEMA......Page 748
22.3 DEFINITION_SCHEMA and Base Tables......Page 749
22.4 Self-Description......Page 750
22.6 INFORMATION_SCHEMA Extensions......Page 751
22.7 Identifier Representation......Page 752
22.8 The DEFINITION_SCHEMA......Page 755
22.9 The INFORMATION_SCHEMA......Page 765
22.10 Short-Name Views......Page 770
22.11 Chapter Summary......Page 772
23.1 Introduction......Page 774
23.2 SQL:200n Overview......Page 775
23.3 XML and SQL......Page 778
23.4 Chapter Summary......Page 779
A.1 Introduction......Page 780
A.2 Overview of Database Design......Page 781
A.3 Impact of Data Warehousing on Designing SQL:1999 Databases......Page 784
A.4 Physical Database Design......Page 787
A.5 Use of CASE and Data Modeling Tools......Page 788
A.6 Appendix Summary......Page 789
B.2 The Schema Definition......Page 790
B.3 Application Code......Page 797
C.2 Differences between SQL-92 and SQL:1999......Page 808
C.3 Implementation-Defined and Implementation-Dependent......Page 813
C.4 Deprecated Features......Page 833
C.5 SQL:1999 Conformance Claims......Page 834
C.6 Appendix Summary......Page 849
D.1 Introduction......Page 850
D.3 Selected National Standards Bodies......Page 852
E.1 Values of SQLSTATE......Page 858
F.2 The Various Standards Bodies......Page 866
F.3 History of the SQL Standard......Page 871
R4 NIST and the FIPS......Page 874
F.5 Other SQL-Related Organizations......Page 875
F.6 Appendix Summary......Page 877
Index......Page 878
About the Authors......Page 928