SQL: 1999

SQL: 1999

by Jim Melton, Alan R. Simon
Released May 2001
Publisher(s): Morgan Kaufmann
ISBN: 9780080517605

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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.

Table of contents

  1. Front Cover
  2. SQL:1999 Understanding Relational Language Components
  3. Copyright Page
  4. Contents (1/4)
  5. Contents (2/4)
  6. Contents (3/4)
  7. Contents (4/4)
  8. Preface (1/2)
  9. Preface (2/2)
  10. Chapter 1. Database and Technology Foundations
    1. 1.1 Introduction
    2. 1.2 Introduction to Database Technology (1/3)
    3. 1.2 Introduction to Database Technology (2/3)
    4. 1.2 Introduction to Database Technology (3/3)
    5. 1.3 Database Languages
    6. 1.4 The Transaction Model
    7. 1.5 Chapter Summary
  11. Chapter 2. Introduction to SQL:1999
    1. 2.1 Introduction
    2. 2.2 SQL History and Background
    3. 2.3 SQL Concepts (1/2)
    4. 2.3 SQL Concepts (2/2)
    5. 2.4 SQL Data Types (1/4)
    6. 2.4 SQL Data Types (2/4)
    7. 2.4 SQL Data Types (3/4)
    8. 2.4 SQL Data Types (4/4)
    9. 2.5 Logic and Null Values
    10. 2.6 Data Conversions
    11. 2.7 SQL Statements
    12. 2.8 Static Versus Dynamic Execution
    13. 2.9 Conforming to SQL:1999
    14. 2.10 Relationship to Products
    15. 2.11 Identifiers
    16. 2.12 Chapter Summary
  12. Chapter 3. Basic Table Creation and Data Manipulation
    1. 3.1 Introduction
    2. 3.2 Our Example
    3. 3.3 Basic Table Creation
    4. 3.4 Set Orientation
    5. 3.5 Basic Data Retrieval (1/2)
    6. 3.5 Basic Data Retrieval (2/2)
    7. 3.6 Updating Information
    8. 3.7 Inserting Information
    9. 3.8 Deleting Information
    10. 3.9 Chapter Summary
  13. Chapter 4. Basic Data Definition Language (DDL)
    1. 4.1 Introduction
    2. 4.2 Data Definition Fundamentals
    3. 4.3 Basic DDL Statements (1/4)
    4. 4.3 Basic DDL Statements (2/4)
    5. 4.3 Basic DDL Statements (3/4)
    6. 4.3 Basic DDL Statements (4/4)
    7. 4.4 WITH CHECK OPTION
    8. 4.5 Chapter Summary
  14. Chapter 5. Values, Basic Functions, and Expressions
    1. 5.1 Introduction
    2. 5.2 Types of SQL Values
    3. 5.3 Literals
    4. 5.4 Parameters (1/2)
    5. 5.4 Parameters (2/2)
    6. 5.5 Special Values
    7. 5.6 Column References and Field References (1/2)
    8. 5.6 Column References and Field References (2/2)
    9. 5.7 Some Terminology
    10. 5.8 Set Functions
    11. 5.9 Value Functions (1/2)
    12. 5.9 Value Functions (2/2)
    13. 5.10 Value Expressions (1/2)
    14. 5.10 Value Expressions (2/2)
    15. 5.11 Chapter Summary
  15. Chapter 6. Advanced Value Expressions: CASE, CAST, and Row Value Expressions
    1. 6.1 Introduction
    2. 6.2 The CASE Expression
    3. 6.3 The CAST Expression
    4. 6.4 Row Value Constructors
    5. 6.5 Table Value Constructors
    6. 6.6 Array Value Constructors
    7. 6.7 Chapter Summary
  16. Chapter 7. Predicates
    1. 7.1 Introduction
    2. 7.2 What Is a Predicate?
    3. 7.3 Subqueries
    4. 7.4 Comparison Predicate (1/2)
    5. 7.4 Comparison Predicate (2/2)
    6. 7.5 IS NULL Predicate
    7. 7.6 IN Predicate
    8. 7.7 LIKE and SIMILAR Predicates (1/3)
    9. 7.7 LIKE and SIMILAR Predicates (2/3)
    10. 7.7 LIKE and SIMILAR Predicates (3/3)
    11. 7.8 EXISTS and UNIQUE Predicates
    12. 7.9 OVERLAPS Predicate
    13. 7.10 SOME, ANY, and ALL
    14. 7.11 MATCH Predicate
    15. 7.12 DISTINCT Predicate
    16. 7.13 Type Predicate
    17. 7.14 Search Conditions
    18. 7.15 Chapter Summary
  17. Chapter 8. Working with Multiple Tables: The Relational Operators
    1. 8.1 Introduction
    2. 8.2 Join Operations: An Overview
    3. 8.3 Types of Join Operations (1/5)
    4. 8.3 Types of Join Operations (2/5)
    5. 8.3 Types of Join Operations (3/5)
    6. 8.3 Types of Join Operations (4/5)
    7. 8.3 Types of Join Operations (5/5)
    8. 8.4 The UNION Operator
    9. 8.5 UNION JOIN
    10. 8.6 The INTERSECT and EXCEPT Operators
    11. 8.7 Another Example
    12. 8.8 Chapter Summary
  18. Chapter 9. Advanced SQL Query Expressions
    1. 9.1 Introduction
    2. 9.2 Query Specifications (1/4)
    3. 9.2 Query Specifications (2/4)
    4. 9.2 Query Specifications (3/4)
    5. 9.2 Query Specifications (4/4)
    6. 9.3 Functional Dependencies: Rules for Updatability (1/2)
    7. 9.3 Functional Dependencies: Rules for Updatability (2/2)
    8. 9.4 Query Expressions (1/2)
    9. 9.4 Query Expressions (2/2)
    10. 9.5 Joined Table (1/2)
    11. 9.5 Joined Table (2/2)
    12. 9.6 Grouped Tables
    13. 9.7 Result Data Types of Columns of UNION, EXCEPT, and INTERSECT
    14. 9.8 Sequence of Evaluation within Query Expression
    15. 9.9 Table Value Constructor
    16. 9.10 Subqueries
    17. 9.11 Table References
    18. 9.12 CUBE and ROLLUP (1/3)
    19. 9.12 CUBE and ROLLUP (2/3)
    20. 9.12 CUBE and ROLLUP (3/3)
    21. 9.13 Recursive Queries (1/3)
    22. 9.13 Recursive Queries (2/3)
    23. 9.13 Recursive Queries (3/3)
    24. 9.14 Chapter Summary
  19. Chapter 10. Constraints, Assertions, and Referential Integrity
    1. 10.1 Introduction
    2. 10.2 Column Constraints and Table Constraints (1/3)
    3. 10.2 Column Constraints and Table Constraints (2/3)
    4. 10.2 Column Constraints and Table Constraints (3/3)
    5. 10.3 Assertions
    6. 10.4 PRIMARY KEY
    7. 10.5 Referential Integrity (1/3)
    8. 10.5 Referential Integrity (2/3)
    9. 10.5 Referential Integrity (3/3)
    10. 10.6 Multiple Cascades
    11. 10.7 More about Referential Integrity Constraints
    12. 10.8 More about Constraints and Assertions
    13. 10.9 Chapter Summary
  20. Chapter 11. Active Databases and Triggers
    1. 11.1 Introduction
    2. 11.2 Referential Actions Redux
    3. 11.3 Triggers
    4. 11.4 Interaction between Referential Actions and Triggers
    5. 11.5 Chapter Summary
  21. Chapter 12. Accessing SQL from the Real World
    1. 12.1 Introduction
    2. 12.2 Data Access Methods and Data Types
    3. 12.3 Applications Interface Mechanisms for SQL
    4. 12.4 Direct Invocation
    5. 12.5 Embedded SQL (1/4)
    6. 12.5 Embedded SQL (2/4)
    7. 12.5 Embedded SQL (3/4)
    8. 12.5 Embedded SQL (4/4)
    9. 12.6 Module Language (1/2)
    10. 12.6 Module Language (2/2)
    11. 12.7 The Impedance Mismatch
    12. 12.8 Other Binding Styles
    13. 12.9 Package Interfaces.SQL (1/2)
    14. 12.9 Package Interfaces.SQL (2/2)
    15. 12.10 Chapter Summary
  22. Chapter 13. Cursors
    1. 13.1 Introduction
    2. 13.2 Cursors: The Basics
    3. 13.3 OPEN and CLOSE
    4. 13.4 FETCH
    5. 13.5 Cursor Positioning
    6. 13.6 Chapter Summary
  23. Chapter 14. Privileges, Users, and Security
    1. 14.1 Introduction
    2. 14.2 Users and Roles (1/2)
    3. 14.2 Users and Roles (2/2)
    4. 14.3 GRANT (1/2)
    5. 14.3 GRANT (2/2)
    6. 14.4 WITH GRANT OPTION and WITH ADMIN OPTION
    7. 14.5 Changing User Identifiers and Role Names
    8. 14.6 Other Rules
    9. 14.7 REVOKE
    10. 14.8 Additional Details of REVOKE (1/2)
    11. 14.8 Additional Details of REVOKE (2/2)
    12. 14.9 Chapter Summary
  24. Chapter 15. Transaction Management
    1. 15.1 Introduction
    2. 15.2 SQL:1999 Transaction Syntax
    3. 15.3 SQL:1999 Isolation Levels (1/2)
    4. 15.3 SQL:1999 Isolation Levels (2/2)
    5. 15.4 Mixed DML and DDL
    6. 15.5 Transaction Termination
    7. 15.6 Transactions and Constraints
    8. 15.7 Savepoints—Establishment and Removal
    9. 15.8 Additional Transaction Termination Actions
    10. 15.9 Chapter Summary
  25. Chapter 16. Connections and Remote Database Access
    1. 16.1 Introduction
    2. 16.2 Establishing Connections
    3. 16.3 Connection Statements
    4. 16.4 RDA (Remote Database Access)
    5. 16.5 Termination Statements and Connections
    6. 16.6 Chapter Summary
  26. Chapter 17. Routines and Routine Invocation (Functions and Procedures)
    1. 17.1 Introduction
    2. 17.2 Relationship of SQL:1999's Routines to SQL/PSM
    3. 17.3 SQL-Invoked Routines (1/3)
    4. 17.3 SQL-Invoked Routines (2/3)
    5. 17.3 SQL-Invoked Routines (3/3)
    6. 17.4 External Routines
    7. 17.5 SQL Routines
    8. 17.6 Limitations of SQL for Stored Routines
    9. 17.7 Polymorphism
    10. 17.8 Routine Resolution Outline
    11. 17.9 Chapter Summary
  27. Chapter 18. Dynamic SQL
    1. 18.1 Introduction
    2. 18.2 What Is Dynamic SQL?
    3. 18.3 The Philosophy of Dynamic SQL
    4. 18.4 Outline of Dynamic SQL Processing (1/3)
    5. 18.4 Outline of Dynamic SQL Processing (2/3)
    6. 18.4 Outline of Dynamic SQL Processing (3/3)
    7. 18.5 The EXECUTE IMMEDIATE Statement
    8. 18.6 PREPARE and EXECUTE Statements
    9. 18.7 The DESCRIBE Statement (1/2)
    10. 18.7 The DESCRIBE Statement (2/2)
    11. 18.8 Rows, Collections, and the DESCRIBE Statement
    12. 18.9 The EXECUTE Statement Revisited
    13. 18.10 Dynamic SQL and Cursors (1/2)
    14. 18.10 Dynamic SQL and Cursors (2/2)
    15. 18.11 A Dynamic SQL Example (1/2)
    16. 18.11 A Dynamic SQL Example (2/2)
    17. 18.12 Chapter Summary
  28. Chapter 19. Call-Level Interface (CLI)
    1. 19.1 Introduction
    2. 19.2 A Brief History of SQL/CLI and ODBC
    3. 19.3 Brief Comparison of SQL/CLI and Dynamic SQL
    4. 19.4 The SQL/CLI Model
    5. 19.5 Handles
    6. 19.6 Null Pointers
    7. 19.7 Database Connections and Implicit Connections
    8. 19.8 CLI Descriptor Areas Versus Dynamic Descriptor Areas (1/2)
    9. 19.8 CLI Descriptor Areas Versus Dynamic Descriptor Areas (2/2)
    10. 19.9 Statement Preparation and Execution (1/2)
    11. 19.9 Statement Preparation and Execution (2/2)
    12. 19.10 Deferred Parameters (1/2)
    13. 19.10 Deferred Parameters (2/2)
    14. 19.11 Transaction Management
    15. 19.12 Diagnostics Facilities
    16. 19.13 Metadata Access and Catalog Routines
    17. 19.14 CLI Routines Summary (1/3)
    18. 19.14 CLI Routines Summary (2/3)
    19. 19.14 CLI Routines Summary (3/3)
    20. 19.15 Annotated CLI Example
    21. 19.16 Chapter Summary
  29. Chapter 20. Diagnostics and Error Management
    1. 20.1 Introduction
    2. 20.2 SQLSTATE
    3. 20.3 Whatever Happened to SQLCODE?
    4. 20.4 SQL Statement Codes
    5. 20.5 Chapter Summary
  30. Chapter 21. Internationalization Aspects of SQL:1999
    1. 21.1 Introduction
    2. 21.2 Character Sets and Collations (1/2)
    3. 21.2 Character Sets and Collations (2/2)
    4. 21.3 Translations and Conversions
    5. 21.4 Mapping Host Language Capabilities to SQL's Character Set Facilities
    6. 21.5 Equal, Distinct, Identical
    7. 21.6 Chapter Summary
  31. Chapter 22. Information Schema
    1. 22.1 Introduction
    2. 22.2 Metadata, Repositories, and the INFORMATION_SCHEMA
    3. 22.3 DEFINITION_SCHEMA and Base Tables
    4. 22.4 Self-Description
    5. 22.5 INFORMATION_SCHEMAand Privileges
    6. 22.6 INFORMATION_SCHEMA Extensions
    7. 22.7 Identifier Representation
    8. 22.8 The DEFINITION_SCHEMA (1/2)
    9. 22.8 The DEFINITION_SCHEMA (2/2)
    10. 22.9 The INFORMATION_SCHEMA
    11. 22.10 Short-Name Views
    12. 22.11 Chapter Summary
  32. Chapter 23. A Look to the Future
    1. 23.1 Introduction
    2. 23.2 SQL:200n Overview
    3. 23.3 XML and SQL
    4. 23.4 Chapter Summary
  33. Appendix A: Designing SQL:1999 Databases
    1. A.1 Introduction
    2. A.2 Overview of Database Design
    3. A.3 Impact of Data Warehousing on Designing SQL:1999 Databases
    4. A.4 Physical Database Design
    5. A.5 Use of CASE and Data Modeling Tools
    6. A.6 Appendix Summary
  34. Appendix B: An SQL:1999 Application Example
    1. B.1 Introduction
    2. B.2 The Schema Definition
    3. B.3 Application Code (1/3)
    4. B.3 Application Code (2/3)
    5. B.3 Application Code (3/3)
  35. Appendix C: The SQL:1999 Annexes
    1. C.1 Introduction
    2. C.2 Differences between SQL-92 and SQL:1999
    3. C.3 Implementation-Defined and Implementation-Dependent (1/4)
    4. C.3 Implementation-Defined and Implementation-Dependent (2/4)
    5. C.3 Implementation-Defined and Implementation-Dependent (3/4)
    6. C.3 Implementation-Defined and Implementation-Dependent (4/4)
    7. C.4 Deprecated Features
    8. C.5 SQL:1999 Conformance Claims (1/3)
    9. C.5 SQL:1999 Conformance Claims (2/3)
    10. C.5 SQL:1999 Conformance Claims (3/3)
    11. C.6 Appendix Summary
  36. Appendix D: Relevant Standards Bodies
    1. D.1 Introduction
    2. D.2 Contacting ISO
    3. D.3 Selected National Standards Bodies
  37. Appendix E: Status Codes
    1. E.1 Values of SQLSTATE
  38. Appendix F: The SQL Standardization Process
    1. F.1 Introduction
    2. F.2 The Various Standards Bodies
    3. F.3 History of the SQL Standard
    4. R4 NIST and the FIPS
    5. F.5 Other SQL-Related Organizations
    6. F.6 Appendix Summary
  39. Index (1/10)
  40. Index (2/10)
  41. Index (3/10)
  42. Index (4/10)
  43. Index (5/10)
  44. Index (6/10)
  45. Index (7/10)
  46. Index (8/10)
  47. Index (9/10)
  48. Index (10/10)
  49. About the Authors

Product information

  • Title: SQL: 1999
  • Author(s): Jim Melton, Alan R. Simon
  • Release date: May 2001
  • Publisher(s): Morgan Kaufmann
  • ISBN: 9780080517605