SQL: 1999 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.
- 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.
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 betwee
n 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 SavepointsEstablishment 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 Li
mitations 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 Metad
ata, 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