Understanding the New SQL
A Complete Guide
- 1st Edition - October 1, 1992
- Latest edition
- Authors: Jim Melton, Alan R. Simon
- Language: English
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 fe… Read more
Purchase options
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.
* 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.
* 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.
Understanging the New SQL: A Complete Guide
by Jim Melton and Alan R. Simon
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.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 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
- Edition: 1
- Latest edition
- Published: October 1, 1992
- Language: English
JM
Jim Melton
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, USAAS
Alan R. Simon
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.