Understanding the New SQL - 1st Edition - ISBN: 9781558602458, 9780080520056

Understanding the New SQL

1st Edition

A Complete Guide

Authors: Jim Melton Alan Simon
Paperback ISBN: 9781558602458
eBook ISBN: 9780080520056
Imprint: Morgan Kaufmann
Published Date: 1st October 1992
Page Count: 536
Tax/VAT will be calculated at check-out

Institutional Access

Table of Contents

Understanging the New SQL: A Complete Guide
by Jim Melton and Alan R. Simon
    Part I SQL-92 Basics
    1 Introduction to SQL-92
      1.1 Introduction
      1.2 What is SQL?
        1.2.1 Data Sublanguages Versus Applications Languages
        1.2.2 Procedural Versus Nonprocedural Languages
      1.3 Why Do You Want This Book
      1.4 The Relational Model
        1.4.1 History and Basics
        1.4.2 Mathematical Foundations
        1.4.3 Other Database Models
      1.5 The History of the SQL Language
      1.6 SQL-92, The New Revision
        1.6.1 New Features
        1.6.2 Size of SQL-92
        1.6.3 Levels of SQL-92
        1.6.4 Conforming to SQL-92
      1.7 Chapter Summary

    2 Getting Started with SQL-92
      2.1 Introduction
      2.2 Database Management System Concepts
        2.2.1 What is a Database?
        2.2.2 What is a Database Management System?
      2.3 Data and Metadata
      2.4 Data Models
      2.5 The Relational Model
        2.5.1 Tables, Columns, and Rows (or Relations, Attributes, and Tuples)
        2.5.2 Primitive Data Types
        2.5.3 Relational Operations
        2.5.4 Closure of the Relational Model
      2.6 SQL Concepts
        2.6.2 Users and Privileges
        2.6.3 Schemas and Catalogs
      2.7 SQL Data Types
        2.7.1 Exact Numerics
        2.7.2 Approximate Numerics
        2.7.3 Character Strings
        2.7.4 Datetimes
        2.7.6 Intervals
      2.8 Logic and Null Values
      2.9 Data Conversions
      2.10 SQL Statements
      2.11 Static Versus Dynamic Execution
      2.12 The Transaction Model
      2.13 Our Example
      2.14 Chapter Summary

    3 Basic Table Creation and Data Manipulation
      3.1 Introduction
      3.2 Set Orientation
      3.3 Basic Table Creation
      3.4 Basic Data Manipulation
        3.4.1 DISTINCT
        3.4.2 Inside the SELECT Statement
        3.4.3 ORDER BY
        3.4.4 WHERE and (
        3.4.5 Search Conditions Using AND
        3.4.6 More SELECT Statements
      3.5 Updating Information
      3.6 Inserting Information
      3.7 Deleting Information
      3.8 Chapter Summary

    4 Basic Data Definition Language (DDL)
      4.1 Introduction
      4.2 Data Definition Fundamentals
        4.2.1 Schema Concepts
        4.2.2 Catalog Concepts
        4.2.3 Tables
        4.2.4 Columns
      4.3 Basic DDL Statements
        4.3.1 SQL Syntax for Tables and Columns
        4.3.2 Character Sets
        4.3.3 Domains
        4.3.4 Temporary Tables
        4.3.5 Modification of Table Structures: The ALTER and DROP Statements
        4.3.6 Schemas
      4.4 Chapter Summary

    5 Values, Basic Functions, and Expressions
      5.1 Introduction
      5.2 Types of SQL Values
      5.3 Literals
      5.4 Parameters
        5.4.1 Types of Parameters
      5.5 Special Values
      5.6 Column References
      5.7 Some Terminology
      5.8 Set Functions
        5.8.1 COUNT
        5.8.2 MAX
        5.8.3 MIN
        5.8.4 SUM
        5.8.5 AVG
      5.9 Value Functions
        5.9.1 Numeric Value Functions
        5.9.2 String Value Functions
        5.9.3 Datetime Value Functions
      5.10 Value Expressions
        5.10.1 Numeric Value Expressions
        5.10.2 String Value Expressions
        5.10.3 Datetime Value Expressions
        5.10.4 Interval Value Expressions
      5.11 Chapter Summary

    Part II Advanced Features of SQL-92
    6 Advanced Value Expressions: CASE, CAST, and Row Value Expressions
      6.1 Introduction
      6.2 The CASE Expression
        6.2.1 CASE and Search Conditions
        6.2.2 CASE and Values
        6.2.3 NULLIF
        6.2.4 COALESCE
      6.3 The CAST Expression
      6.4 Row Value Constructors
      6.5 Chapter Summary

    7 Predicates
      7.1 Introduction
      7.2 What is a Predicate?
      7.3 Subqueries
      7.4 Comparison Predicate
        7.4.1 BETWEEN
        7.4.2 NOT BETWEEN
      7.5 NULL Predicate
      7.6 IN Predicate
      7.7 Like Predicate
      7.8 EXISTS and UNIQUE Predicates
      7.9 OVERLAPS Predicate
      7.10 SOME, ANY, and ALL
      7.11 MATCH Predicate
      7.12 Search Conditions
      7.13 Chapter Summary

    8 Working with Multiple Tables: The Relational Operators
      8.1 Introduction
      8.2 Join Operators: An Overview
      8.3 Types of Join Operators
        8.3.1 Old-Style Joins
        8.3.2 The CROSS JOIN
        8.3.3 The NATURAL JOIN
        8.3.4 Condition JOIN
        8.3.5 Column Name JOIN
        8.3.6 Joins So Far
        8.3.7 The INNER JOIN
        8.3.8 The OUTER JOIN
      8.4 The UNION Operator
        8.4.1 Alternative Syntax
      8.5 UNION JOIN
      8.6 The INTERSECT and EXCEPT Operators
      8.7 Another Example
      8.8 Chapter Summary

    9 Advanced SQL Query Expressions
      9.1 Introduction
      9.2 Query Specifications
        9.2.1 Table Expressions
        9.2.2 GROUP BY
        9.2.3 Updatable Query Specifications
      9.3 Query Expressions
        9.3.1 CORRESPONDING
        9.3.2 Results of Query Expressions
        9.3.3 Examples of Query Expressions
      9.4 Joined Table
      9.5 Grouped Tables
        9.5.1 Grouped Views
      9.6 Table Value Constructor
      9.7 Subqueries
      9.8 Chapter Summary

    10 Constraints, Assertions, and Referential Integrity
      10.1 Introduction
      10.2 Column Constraints and Table Constraints
        10.2.1 NOT NULL
        10.2.2 UNIQUE
        10.2.3 CHECK
        10.2.4 Constraint Names
      10.3 Assertions
      10.4 Primary Key
      10.5 Referential Integrity
        10.5.1 FOREIGN KEY
        10.5.2 Referential Constraint Actions
      10.6 Multiple Cascades
      10.7 More About Referential Integrity Constraints
      10.8 More About Constraints and Assertions
      10.9 Chapter Summary

    Part III SQL and Applications
    11 Accessing SQL from the Real World
      11.1 Introduction
      11.2 Data Access Methods and Data Types
      11.3 Applications Interface Mechanisms for SQL
      11.4 Direct Invocation
      11.5 Embedded SQL
        11.5.1 Introduction
        11.5.2 Embedded Exception Declarations
        11.5.3 Embedded SQL Declarations
        11.5.4 Embedded SQL Statements
      11.6 Module Language
        11.6.1 Some Additional Information About Privileges
        11.6.2 An Implementation Note
      11.7 Other Binding Styles
      11.8 Package SQL_STANDARD
      11.9 Chapter Summary

    12 Cursors
      12.1 Introduction
      12.2 Cursors: The Basics
        12.2.1 Syntax
        12.2.2 Ordering and Column Naming
        12.2.3 Updatability
        12.2.4 Sensitivity
        12.2.5 Scrollable Cursors
      12.3 OPEN and CLOSE
      12.4 FETCH
      12.5 Cursor Positioning
        12.5.1 Positioned DELETE and UPDATE Statements
      12.6 Chapter Summary

    13 Privileges, Users, and Security
      13.1 Introduction
      13.2 GRANT
        13.2.1 Basic Viewing Privileges
        13.2.2 Deletion Privileges with Further GRANT Permission
        13.2.3 Update Privileges on a Specific Column
        13.2.4 Insertion Privileges
        13.2.5 PUBLIC Access and Privileges on VIEWS
        13.2.6 REFERENCES
        13.2.7 Constraints and Privileges
        13.2.8 USAGE
        13.2.9 ALL PRIVILEGES
      13.3 Other Rules
      13.4 Revoking Privileges
      13.5 Additional Details of REVOKE
      13.6 Chapter Summary

    14 Transaction Management
      14.1 Introduction
      14.2 SQL-92 Transaction Syntax
      14.3 SQL-92 Isolation Levels
      14.4 Mixed DML and DDL
      14.5 Transaction Termination
      14.6 Transactions and Constraints
      14.7 Additional Transaction Termination Actions
      14.8 Chapter Summary

    15 Connections and Remote Database Access
      15.1 Introduction
      15.2 Establishing Connections
      15.3 Connection Statements
        15.3.1 CONNECT
        15.3.2 SET CONNECTION
        15.3.3 DISCONNECT
      15.4 RDA (Remote Database Access)
      15.5 Termination Statements and Connections
      15.6 Chapter Summary

      16.1 Introduction
      16.2 What is Dynamic SQL?
      16.3 The Philosophy of Dynamic SQL
        16.3.1 Parameters
        16.3.2 Normal and Extended Dynamic
      16.4 Outline of Dynamic SQL Processing
        16.4.1 Parameters and Codes
      16.5 The EXECUTE IMMEDIATE Statement
      16.6 PREPARE AND EXECUTE Statement
      16.7 The DESCRIBE Statement
      16.8 The EXECUTE Statement Revisited
      16.9 Dynamic SQL and Cursors
      16.9.10 Dynamic SQL Example
      16.11 Chapter Summary

    Part IV The Finishing Touches
    17 Diagnostics and Error Management
      17.1 Introduction
      17.2 SQLCODE and SQLSTATE
      17.3 SQL Statement Codes
      17.4 Chapter Summary

    18 Internationalization Aspects of SQL-92
      18.1 Introduction
      18.2 Character Sets and Collations
        18.2.1 Coercibility
        18.2.2 Coercibility Attributes
        18.2.3 ORDER BY
        18.2.4 GROUP BY
      18.3 Translations and Conversions
      18.4 Chapter Summary

    19 Information Schema
      19.1 Introduction
      19.2 Metadata, Repositories, and The INFORMATION _SCHEMA
      19.3 DEFINITION_SCHEMA and Base Tables
      19.4 Self-Description
      19.5 INFORMATION_SCHEMA and Privileges
      19.6 INFORMATION_SCHEMA Extensions
      19.7 Identifier Representation
        19.8.1 Schema Definition
        19.8.2 The Base Tables
        19.8.3 Assertions on the Base Tables
        19.9.1 Schema Definition
        19.9.2 Assertion
        19.9.3 Domains
      19.10 Chapter Summary

    20 A Look to the Future
      20.1 Introduction
      20.2 SQL3 Overview
        20.2.1 Enhanced Relational Support
        20.2.2 Triggers
        20.2.3 Recursive Operations
        20.2.4 New Data Types
        20.2.5 Other Relational Features
      20.3 Support for the Object Paradigm
      20.4 Chapter Summary

    Part V Appendices
    A Designing SQL-92 Databases
      A.1 Introduction
      A.2 Overview of Database Design
      A.3 Levels of Database Design
      A.4 Conceptual Database Design
        A.4.1 Conceptual Objects
        A.4.2 Performing Conceptual Design
      A.5 Logical Database Design
        A.5.1 Model-to-Model Design Transformation
        A.5.2 Normalization
      A.6 Physical Database Design
      A.7 Use of CASE Tools
      A.8 Appendix Summary

    B A Complete SQL-92 Example
      B.1 Introduction
      B.2 The Schema Definition
      B.3 Application Code
        B.3.1 Data Input
        B.3.2 Data Modification
        B.3.3. Table Structure Modification
        B.3.4 Data Removal
        B.3.5 Data Access and Management

    C The SQL-92 Annexes: Differences, Implementation-Defined and Implementation-Dependent Features, Deprecated Features, and Leveling
      C.1 Introduction
      C.2 Differences Between SQL-89 and SQL-92
        C.2.1 Incompatible Differences
        C.2.2 Compatible Differences
      C.3 Implementation-Defined and Implementation-Dependent
        C.3.1 Implementation-Defined
        C.3.2 Implementation-Dependent
      C.4 Deprecated Features
      C.5 Leveling of SQL-92
      C.6 Appendix Summary

    D Relevant Standards Bodies
      D.1 Introduction
      D.2 List of Standards Bodies

    E Status Codes
      E.1 Values of SQLSTATE and SQLCODE

    F The SQL Standardization Process
      F.1 Introduction
      F.2 The Various Standards Bodies
        F.2.1 National Standards
        F.2.2 International Standards
        F.2.3 Standards Development in ISO
      F.3 History of the SQL Standard
        F.3.1 SQL2
      F.4 NIST and the FIPS
      F.6 Other SQL-Related Organizations
      F.7 Appendix Summary

    G The Complete SQL-92 Language


An effective introduction to SQL, and a comprehensive reference for years to come. As the editor of the 1992 standard, Jim Melton is an authority on the language and its new features. Using a highly readable, conversational style, he and Alan Simon clearly present the power of SQL. They describe practical methods of using SQL to solve problems, advanced SQL query expressions, dynamic SQL, transaction models, and database design.

Key Features

  • A tutorial on basic relational database concepts.

  • A clear explanation of the differences between SQL-89 and SQL-92.

  • A retail video and music store as an example that develops with the SQL presentation.

  • Coverage of international character sets.


No. of pages:
© Morgan Kaufmann 1993
Morgan Kaufmann
eBook ISBN:
Paperback ISBN:

About the Authors

Jim Melton Author

Jim Melton is editor of all parts of ISO/IEC 9075 (SQL) and is a representative for database standards at Oracle Corporation. Since 1986, he has been his company's representative to ANSI INCITS Technical Committee H2 for Database and a US representative to ISO/IEC JTC1/SC32/WG3 (Database Languages). In addition, Jim has participated in the W3C's XML Query Working Group since 1998 and is currently co-Chair of that Working Group. He is also Chair of the WG's Full-Text Task Force, co-Chair of the Update Language Task Force, and co-editor of two XQuery-related specifications. He is the author of several SQL books.

Affiliations and Expertise

Oracle Corporation, Sandy, Utah.

Alan Simon Author

Alan Simon is a leading authority on data warehousing and database technology. He is the author of 26 books, including the previous edition of this book and the forthcoming Data Warehousing and Business Intelligence for e-Commerce, available from Morgan Kaufmann Publishers in early 2001. He currently provides data warehousing-related consulting services to clients.