Melton / Simon SQL: 1999

Understanding Relational Language Components
1. Auflage 2001
ISBN: 978-0-08-051760-5
Verlag: Elsevier Science & Techn.
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 893 Seiten, Web PDF

Reihe: Morgan Kaufmann

This book is the best way to make the leap from SQL-92 to SQL:1999, but it is much more than just a simple bridge between the two. The latest from celebrated SQL experts Jim Melton and Alan Simon, SQL:1999 is a comprehensive, eminently practical account of SQL's latest incarnation and a potent distillation of the details required to put it to work. Written to accommodate both novice and experienced SQL users, SQL:1999 focuses on the language's capabilities, from the basic to the advanced, and the ways that real applications take advantage of them. Throughout, the authors illustrate features and techniques with clear and often entertaining references to their own custom database, which can be downloaded from the companion Web site.

* Gives authoritative coverage from an expert team that includes the editor of the SQL-92 and SQL:1999 standards.
* Provides a general introduction to SQL that helps you understand its constituent parts, history, and place in the realm of computer languages.
* Explains SQL:1999's more sophisticated features, including advanced value expressions, predicates, advanced SQL query expressions, and support for active databases.
* Explores key issues for programmers linking applications to SQL databases.
* Provides guidance on troubleshooting, internationalization, and changes anticipated in the next version of SQL.
* Contains appendices devoted to database design, a complete SQL:1999 example, the standardization process, and more.

Autoren/Hrsg.

Melton, Jim

Simon, Alan R.

Inhaltsverzeichnis

1;Front Cover;1
2;SQL:1999 Understanding Relational Language Components;4
3;Copyright Page;5
4;Contents;10
5;Preface;26
6;Chapter 1. Database and Technology Foundations;34
6.1;1.1 Introduction;34
6.2;1.2 Introduction to Database Technology;35
6.3;1.3 Database Languages;49
6.4;1.4 The Transaction Model;52
6.5;1.5 Chapter Summary;55
7;Chapter 2. Introduction to SQL:1999;56
7.1;2.1 Introduction;56
7.2;2.2 SQL History and Background;56
7.3;2.3 SQL Concepts;59
7.4;2.4 SQL Data Types;65
7.5;2.5 Logic and Null Values;82
7.6;2.6 Data Conversions;85
7.7;2.7 SQL Statements;87
7.8;2.8 Static Versus Dynamic Execution;87
7.9;2.9 Conforming to SQL:1999;88
7.10;2.10 Relationship to Products;89
7.11;2.11 Identifiers;90
7.12;2.12 Chapter Summary;91
8;Chapter 3. Basic Table Creation and Data Manipulation;92
8.1;3.1 Introduction;92
8.2;3.2 Our Example;93
8.3;3.3 Basic Table Creation;94
8.4;3.4 Set Orientation;98
8.5;3.5 Basic Data Retrieval;99
8.6;3.6 Updating Information;108
8.7;3.7 Inserting Information;112
8.8;3.8 Deleting Information;114
8.9;3.9 Chapter Summary;115
9;Chapter 4. Basic Data Definition Language (DDL);116
9.1;4.1 Introduction;116
9.2;4.2 Data Definition Fundamentals;116
9.3;4.3 Basic DDL Statements;125
9.4;4.4 WITH CHECK OPTION;144
9.5;4.5 Chapter Summary;145
10;Chapter 5. Values, Basic Functions, and Expressions;146
10.1;5.1 Introduction;146
10.2;5.2 Types of SQL Values;146
10.3;5.3 Literals;147
10.4;5.4 Parameters;152
10.5;5.5 Special Values;158
10.6;5.6 Column References and Field References;159
10.7;5.7 Some Terminology;165
10.8;5.8 Set Functions;165
10.9;5.9 Value Functions;176
10.10;5.10 Value Expressions;186
10.11;5.11 Chapter Summary;195
11;Chapter 6. Advanced Value Expressions: CASE, CAST, and Row Value Expressions;198
11.1;6.1 Introduction;198
11.2;6.2 The CASE Expression;199
11.3;6.3 The CAST Expression;204
11.4;6.4 Row Value Constructors;207
11.5;6.5 Table Value Constructors;209
11.6;6.6 Array Value Constructors;210
11.7;6.7 Chapter Summary;211
12;Chapter 7. Predicates;212
12.1;7.1 Introduction;212
12.2;7.2 What Is a Predicate?;213
12.3;7.3 Subqueries;213
12.4;7.4 Comparison Predicate;214
12.5;7.5 IS NULL Predicate;220
12.6;7.6 IN Predicate;222
12.7;7.7 LIKE and SIMILAR Predicates;223
12.8;7.8 EXISTS and UNIQUE Predicates;238
12.9;7.9 OVERLAPS Predicate;240
12.10;7.10 SOME, ANY, and ALL;242
12.11;7.11 MATCH Predicate;245
12.12;7.12 DISTINCT Predicate;248
12.13;7.13 Type Predicate;250
12.14;7.14 Search Conditions;250
12.15;7.15 Chapter Summary;254
13;Chapter 8. Working with Multiple Tables: The Relational Operators;256
13.1;8.1 Introduction;256
13.2;8.2 Join Operations: An Overview;256
13.3;8.3 Types of Join Operations;258
13.4;8.4 The UNION Operator;282
13.5;8.5 UNION JOIN;287
13.6;8.6 The INTERSECT and EXCEPT Operators;289
13.7;8.7 Another Example;293
13.8;8.8 Chapter Summary;296
14;Chapter 9. Advanced SQL Query Expressions;298
14.1;9.1 Introduction;298
14.2;9.2 Query Specifications;299
14.3;9.3 Functional Dependencies: Rules for Updatability;318
14.4;9.4 Query Expressions;326
14.5;9.5 Joined Table;334
14.6;9.6 Grouped Tables;342
14.7;9.7 Result Data Types of Columns of UNION, EXCEPT, and INTERSECT;345
14.8;9.8 Sequence of Evaluation within Query Expression;347
14.9;9.9 Table Value Constructor;347
14.10;9.10 Subqueries;350
14.11;9.11 Table References;354
14.12;9.12 CUBE and ROLLUP;359
14.13;9.13 Recursive Queries;371
14.14;9.14 Chapter Summary;386
15;Chapter 10. Constraints, Assertions, and Referential Integrity;388
15.1;10.1 Introduction;388
15.2;10.2 Column Constraints and Table Constraints;391
15.3;10.3 Assertions;406
15.4;10.4 PRIMARY KEY;409
15.5;10.5 Referential Integrity;412
15.6;10.6 Multiple Cascades;423
15.7;10.7 More about Referential Integrity Constraints;425
15.8;10.8 More about Constraints and Assertions;426
15.9;10.9 Chapter Summary;427
16;Chapter 11. Active Databases and Triggers;428
16.1;11.1 Introduction;428
16.2;11.2 Referential Actions Redux;429
16.3;11.3 Triggers;429
16.4;11.4 Interaction between Referential Actions and Triggers;442
16.5;11.5 Chapter Summary;443
17;Chapter 12. Accessing SQL from the Real World;444
17.1;12.1 Introduction;444
17.2;12.2 Data Access Methods and Data Types;445
17.3;12.3 Applications Interface Mechanisms for SQL;447
17.4;12.4 Direct Invocation;447
17.5;12.5 Embedded SQL;448
17.6;12.6 Module Language;464
17.7;12.7 The Impedance Mismatch;470
17.8;12.8 Other Binding Styles;471
17.9;12.9 Package Interfaces.SQL;473
17.10;12.10 Chapter Summary;479
18;Chapter 13. Cursors;480
18.1;13.1 Introduction;480
18.2;13.2 Cursors: The Basics;480
18.3;13.3 OPEN and CLOSE;496
18.4;13.4 FETCH;497
18.5;13.5 Cursor Positioning;498
18.6;13.6 Chapter Summary;500
19;Chapter 14. Privileges, Users, and Security;502
19.1;14.1 Introduction;502
19.2;14.2 Users and Roles;505
19.3;14.3 GRANT;515
19.4;14.4 WITH GRANT OPTION and WITH ADMIN OPTION;525
19.5;14.5 Changing User Identifiers and Role Names;526
19.6;14.6 Other Rules;528
19.7;14.7 REVOKE;529
19.8;14.8 Additional Details of REVOKE;533
19.9;14.9 Chapter Summary;541
20;Chapter 15. Transaction Management;544
20.1;15.1 Introduction;544
20.2;15.2 SQL:1999 Transaction Syntax;545
20.3;15.3 SQL:1999 Isolation Levels;549
20.4;15.4 Mixed DML and DDL;556
20.5;15.5 Transaction Termination;556
20.6;15.6 Transactions and Constraints;557
20.7;15.7 Savepoints—Establishment and Removal;559
20.8;15.8 Additional Transaction Termination Actions;561
20.9;15.9 Chapter Summary;564
21;Chapter 16. Connections and Remote Database Access;566
21.1;16.1 Introduction;566
21.2;16.2 Establishing Connections;567
21.3;16.3 Connection Statements;569
21.4;16.4 RDA (Remote Database Access);572
21.5;16.5 Termination Statements and Connections;573
21.6;16.6 Chapter Summary;573
22;Chapter 17. Routines and Routine Invocation (Functions and Procedures);574
22.1;17.1 Introduction;574
22.2;17.2 Relationship of SQL:1999's Routines to SQL/PSM;575
22.3;17.3 SQL-Invoked Routines;576
22.4;17.4 External Routines;590
22.5;17.5 SQL Routines;595
22.6;17.6 Limitations of SQL for Stored Routines;596
22.7;17.7 Polymorphism;597
22.8;17.8 Routine Resolution Outline;597
22.9;17.9 Chapter Summary;600
23;Chapter 18. Dynamic SQL;602
23.1;18.1 Introduction;602
23.2;18.2 What Is Dynamic SQL?;602
23.3;18.3 The Philosophy of Dynamic SQL;604
23.4;18.4 Outline of Dynamic SQL Processing;607
23.5;18.5 The EXECUTE IMMEDIATE Statement;622
23.6;18.6 PREPARE and EXECUTE Statements;623
23.7;18.7 The DESCRIBE Statement;627
23.8;18.8 Rows, Collections, and the DESCRIBE Statement;635
23.9;18.9 The EXECUTE Statement Revisited;640
23.10;18.10 Dynamic SQL and Cursors;643
23.11;18.11 A Dynamic SQL Example;649
23.12;18.12 Chapter Summary;656
24;Chapter 19. Call-Level Interface (CLI);658
24.1;19.1 Introduction;658
24.2;19.2 A Brief History of SQL/CLI and ODBC;659
24.3;19.3 Brief Comparison of SQL/CLI and Dynamic SQL;660
24.4;19.4 The SQL/CLI Model;662
24.5;19.5 Handles;663
24.6;19.6 Null Pointers;667
24.7;19.7 Database Connections and Implicit Connections;667
24.8;19.8 CLI Descriptor Areas Versus Dynamic Descriptor Areas;670
24.9;19.9 Statement Preparation and Execution;676
24.10;19.10 Deferred Parameters;683
24.11;19.11 Transaction Management;690
24.12;19.12 Diagnostics Facilities;691
24.13;19.13 Metadata Access and Catalog Routines;695
24.14;19.14 CLI Routines Summary;696
24.15;19.15 Annotated CLI Example;710
24.16;19.16 Chapter Summary;714
25;Chapter 20. Diagnostics and Error Management;716
25.1;20.1 Introduction;716
25.2;20.2 SQLSTATE;716
25.3;20.3 Whatever Happened to SQLCODE?;728
25.4;20.4 SQL Statement Codes;729
25.5;20.5 Chapter Summary;732
26;Chapter 21. Internationalization Aspects of SQL:1999;734
26.1;21.1 Introduction;734
26.2;21.2 Character Sets and Collations;735
26.3;21.3 Translations and Conversions;744
26.4;21.4 Mapping Host Language Capabilities to SQL's Character Set Facilities;745
26.5;21.5 Equal, Distinct, Identical;746
26.6;21.6 Chapter Summary;747
27;Chapter 22. Information Schema;748
27.1;22.1 Introduction;748
27.2;22.2 Metadata, Repositories, and the INFORMATION_SCHEMA;748
27.3;22.3 DEFINITION_SCHEMA and Base Tables;749
27.4;22.4 Self-Description;750
27.5;22.5 INFORMATION_SCHEMAand Privileges;751
27.6;22.6 INFORMATION_SCHEMA Extensions;751
27.7;22.7 Identifier Representation;752
27.8;22.8 The DEFINITION_SCHEMA;755
27.9;22.9 The INFORMATION_SCHEMA;765
27.10;22.10 Short-Name Views;770
27.11;22.11 Chapter Summary;772
28;Chapter 23. A Look to the Future;774
28.1;23.1 Introduction;774
28.2;23.2 SQL:200n Overview;775
28.3;23.3 XML and SQL;778
28.4;23.4 Chapter Summary;779
29;Appendix A: Designing SQL:1999 Databases;780
29.1;A.1 Introduction;780
29.2;A.2 Overview of Database Design;781
29.3;A.3 Impact of Data Warehousing on Designing SQL:1999 Databases;784
29.4;A.4 Physical Database Design;787
29.5;A.5 Use of CASE and Data Modeling Tools;788
29.6;A.6 Appendix Summary;789
30;Appendix B: An SQL:1999 Application Example;790
30.1;B.1 Introduction;790
30.2;B.2 The Schema Definition;790
30.3;B.3 Application Code;797
31;Appendix C: The SQL:1999 Annexes;808
31.1;C.1 Introduction;808
31.2;C.2 Differences between SQL-92 and SQL:1999;808
31.3;C.3 Implementation-Defined and Implementation-Dependent;813
31.4;C.4 Deprecated Features;833
31.5;C.5 SQL:1999 Conformance Claims;834
31.6;C.6 Appendix Summary;849
32;Appendix D: Relevant Standards Bodies;850
32.1;D.1 Introduction;850
32.2;D.2 Contacting ISO;852
32.3;D.3 Selected National Standards Bodies;852
33;Appendix E: Status Codes;858
33.1;E.1 Values of SQLSTATE;858
34;Appendix F: The SQL Standardization Process;866
34.1;F.1 Introduction;866
34.2;F.2 The Various Standards Bodies;866
34.3;F.3 History of the SQL Standard;871
34.4;R4 NIST and the FIPS;874
34.5;F.5 Other SQL-Related Organizations;875
34.6;F.6 Appendix Summary;877
35;Index;878
36;About the Authors;928