Understanding the New SQL book cover

Understanding the New SQL

A Complete Guide

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.

Paperback, 536 Pages

Published: October 1992

Imprint: Morgan Kaufmann

ISBN: 978-1-55860-245-8

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.1 Views
        • 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.3.7 Views
      • 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 DYNAMIC SQL
      • 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.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 The DEFINITION_SCHEMA
        • 19.8.1 Schema Definition
        • 19.8.2 The Base Tables
        • 19.8.3 Assertions on the Base Tables
      • 19.9 The INFORMATION_SCHEMA
        • 19.9.1 Schema Definition
        • 19.9.2 Assertion
        • 19.9.3 Domains
        • 19.9.4 Views
      • 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

Advertisement

advert image