SQL : 1999 - Understanding Relational Language Components

by Melton; Simon

• ISBN13: 9781558604568
• ISBN10: 1558604561
• eBook ISBN(s): 9780080517605, 9781558604568
• Edition: 2nd
• Format: Paperback
• Copyright: 2001-05-18
• Publisher: Elsevier Science

Summary

This book 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, which can be downloaded from the companion Web site.

Table of Contents

Preface	xxv
Database and Technology Foundations	1	(22)
Introduction	1	(1)
Introduction to Database Technology	2	(14)
Database Concepts	2	(1)
Database Management Systems (DBMSs)	3	(1)
The Relational Model	4	(9)
Object-Oriented Databases	13	(1)
Extending the Relational Model with Object-Oriented Capabilities	14	(1)
Other Database Models	15	(1)
Database Languages	16	(3)
Data Sublanguages Versus Applications Languages	16	(2)
Procedural Versus Nonprocedural Languages	18	(1)
The Transaction Model	19	(3)
Chapter Summary	22	(1)
Introduction to SQL:1999	23	(36)
Introduction	23	(1)
SQL History and Background	23	(3)
SQL Versus Competing Relational Languages	23	(2)
CLI-95, PSM-96, and OLB-98	25	(1)
SQL:1999, The Latest Revision	25	(1)
SQL Concepts	26	(6)
Views	27	(1)
Users and Privileges	28	(1)
Schemas and Catalogs	29	(1)
Connections, Sessions, and Transactions	30	(1)
Routines and Paths	31	(1)
SQL Data Types	32	(17)
Exact Numerics	32	(1)
Approximate Numerics	33	(1)
Character Strings (Including CLOBs)	34	(2)
Bit Strings and Binary Strings (Including BLOBs)	36	(1)
Datetimes	37	(3)
Intervals	40	(1)
Booleans	41	(1)
Collection (or Array) Types	42	(1)
Anonymous Row Types	43	(2)
User-Defined Types	45	(1)
Reference Types	46	(1)
Locators	47	(1)
Sites	48	(1)
Logic and Null Values	49	(3)
Data Conversions	52	(2)
SQL Statements	54	(1)
Static Versus Dynamic Execution	54	(1)
Conforming to SQL:1999	55	(1)
Core SQL	55	(1)
Packages	56	(1)
Relationship to Products	56	(1)
Identifiers	57	(1)
Chapter Summary	58	(1)
Basic Table Creation and Data Manipulation	59	(24)
Introduction	59	(1)
Our Example	60	(1)
Basic Table Creation	61	(4)
Set Orientation	65	(1)
Basic Data Retrieval	66	(9)
Distinct	67	(1)
Inside the Select Statement	68	(1)
Order By	69	(1)
Where and *	70	(1)
Search Conditions Using and, or	71	(1)
More Select Statements	72	(3)
Updating Information	75	(4)
Inserting Information	79	(2)
Deleting Information	81	(1)
Chapter Summary	82	(1)
Basic Data Definition Language (DDL)	83	(30)
Introduction	83	(1)
Data Definition Fundamentals	83	(9)
Schema Concepts	84	(1)
Catalog Concepts	85	(1)
Tables	86	(5)
Columns	91	(1)
Basic DDL Statements	92	(19)
SQL Syntax for Tables and Columns	93	(5)
Domains	98	(1)
Character Sets	99	(1)
Distinct Types	99	(1)
Temporary Tables	100	(2)
Modification of Table Structures: The Alter and Drop Statements	102	(4)
Schemas	106	(1)
Views	107	(4)
With Check Option	111	(1)
Chapter Summary	112	(1)
Values, Basic Functions, and Expressions	113	(52)
Introduction	113	(1)
Types of SQL Values	113	(1)
Literals	114	(5)
Parameters	119	(6)
Types of Parameters	121	(3)
Relationship to Host Variables	124	(1)
Special Values	125	(1)
Column References and Field References	126	(6)
Column References	127	(3)
Field References	130	(2)
Some Terminology	132	(1)
Set Functions	132	(11)
Count	133	(3)
Max	136	(3)
Min	139	(1)
Sum	140	(1)
Avg	141	(1)
The Rest of the Set Functions: Every, Any, and Some	142	(1)
Value Functions	143	(10)
Numeric Value Functions	143	(4)
String Value Functions	147	(4)
Datetime Value Functions	151	(1)
Interval Value Functions	152	(1)
Value Expressions	153	(9)
Numeric Value Expressions	153	(2)
String Value Expressions	155	(1)
Datetime Value Expressions	155	(2)
Interval Value Expressions	157	(2)
Boolean Value Expressions	159	(1)
Array Value Expressions	160	(2)
Row Value Expressions	162	(1)
Chapter Summary	162	(3)
Advanced Value Expressions: Case, Cast, and Row Value Expressions	165	(14)
Introduction	165	(1)
The Case Expression	166	(5)
Case and Search Conditions	166	(2)
Case and Values	168	(1)
Nullif	169	(1)
Coalesce	170	(1)
The Cast Expression	171	(3)
Row Value Constructors	174	(2)
Table Value Constructors	176	(1)
Array Value Constructors	177	(1)
Chapter Summary	178	(1)
Predicates	179	(44)
Introduction	179	(1)
What Is a Predicate?	180	(1)
Subqueries	180	(1)
Comparison Predicate	181	(6)
The Big Six Comparison Types	181	(3)
Between	184	(2)
Not Between	186	(1)
Is Null Predicate	187	(2)
In Predicate	189	(1)
Like and Similar Predicates	190	(15)
Like Predicate	191	(6)
Similar Predicate	197	(8)
Exists and Unique Predicates	205	(2)
Overlaps Predicate	207	(2)
Some, Any, and All	209	(3)
Match Predicate	212	(3)
Distinct Predicate	215	(2)
Type Predicate	217	(1)
Search Conditions	217	(4)
Chapter Summary	221	(2)
Working with Multiple Tables: The Relational Operators	223	(42)
Introduction	223	(1)
Join Operations: An Overview	223	(2)
Types of Join Operations	225	(24)
Classic, Comma-Separated Joins	230	(6)
The Cross Join	236	(1)
The Natural Join	237	(2)
Condition Join	239	(1)
Column Name Join	240	(2)
Joins So Far	242	(1)
The Inner Join	242	(1)
The Outer Join	243	(6)
The Union Operator	249	(5)
Alternative Syntax	253	(1)
Union Join	254	(2)
The Intersect and Except Operators	256	(4)
Another Example	260	(3)
Chapter Summary	263	(2)
Advanced SQL Query Expressions	265	(90)
Introduction	265	(1)
Query Specifications	266	(19)
Qualified Asterisk	270	(4)
Table Expressions	274	(4)
Group by and Having	278	(5)
Updatable Query Specifications	283	(2)
Functional Dependencies: Rules for Updatability	285	(8)
Query Expressions	293	(8)
Corresponding	294	(1)
Results of Query Expressions	295	(1)
Examples of Query Expressions	295	(1)
With Clause	296	(5)
Joined Table	301	(8)
Review of Joined Table	301	(8)
Grouped Tables	309	(3)
Grouped Virtual Tables	310	(1)
Grouped Views	311	(1)
Result Data Types of Columns of Union, Except, and Intersect	312	(2)
Sequence of Evaluation within Query Expression	314	(1)
Table Value Constructor	314	(3)
Subqueries	317	(4)
Table References	321	(5)
Cube and Rollup	326	(12)
The Basics	327	(7)
Grouping Operations	334	(4)
Recursive Queries	338	(15)
Parts Is Parts Is Parts, or Who Works for Whom	338	(2)
Concepts of Recursion	340	(4)
With Recursive	344	(9)
Chapter Summary	353	(2)
Constraints, Assertions, and Referential Integrity	355	(40)
Introduction	355	(3)
Column Constraints and Table Constraints	358	(15)
Constraint Deferral	359	(1)
Not Null	360	(1)
Unique	361	(3)
Check	364	(8)
Constraint Names	372	(1)
Assertions	373	(3)
Primary Key	376	(3)
Referential Integrity	379	(11)
Foreign Key	381	(3)
Referential Constraint Actions	384	(5)
Restrict	389	(1)
Multiple Cascades	390	(2)
More about Referential Integrity Constraints	392	(1)
More about Constraints and Assertions	393	(1)
Chapter Summary	394	(1)
Active Databases and Triggers	395	(16)
Introduction	395	(1)
Referential Actions Redux	396	(1)
Triggers	396	(13)
Types of Triggers	401	(3)
Execution of Triggers	404	(4)
Triggers and Products	408	(1)
Interaction between Referential Actions and Triggers	409	(1)
Chapter Summary	410	(1)
Accessing SQL from the Real World	411	(36)
Introduction	411	(1)
Data Access Methods and Data Types	412	(2)
Applications Interface Mechanisms for SQL	414	(1)
Direct Invocation	414	(1)
Embedded SQL	415	(16)
Introduction	415	(3)
Embedded Exception Declarations	418	(3)
Embedded SQL Declarations	421	(9)
Embedded SQL Statements	430	(1)
Module Language	431	(6)
Some Additional Information about Privileges	436	(1)
An Implementation Note	437	(1)
The Impedance Mismatch	437	(1)
Other Binding Styles	438	(2)
Package Interfaces.SQL	440	(6)
Chapter Summary	446	(1)
Cursors	447	(22)
Introduction	447	(1)
Cursors: The Basics	447	(16)
Syntax	450	(2)
Ordering and Column Naming	452	(3)
Updatability	455	(2)
Sensitivity	457	(3)
Scrollable Cursors	460	(1)
Holdable Cursors	461	(1)
Returning Result Sets from Cursors	462	(1)
Open and Close	463	(1)
Fetch	464	(1)
Cursor Positioning	465	(2)
Positioned Delete	466	(1)
Positioned Update	466	(1)
Chapter Summary	467	(2)
Privileges, Users, and Security	469	(42)
Introduction	469	(3)
Users and Roles	472	(10)
The SQL Security Model	472	(10)
Grant	482	(10)
Granting Privileges	482	(3)
Granting Roles	485	(1)
Basic Viewing Privileges	486	(1)
Deletion Privileges with Further Grant Permission	486	(1)
Update Privileges on a Specific Column	486	(1)
Insertion Privileges	487	(1)
Public Access and Privileges on Views	487	(1)
References	488	(1)
Constraints and Privileges	489	(1)
Trigger	490	(1)
Usage	490	(1)
Execute	491	(1)
Under	491	(1)
All Privileges	492	(1)
With Grant Option and With Admin Option	492	(1)
Changing User Identifiers and Role Names	493	(2)
Set Session Authorization Statement	493	(1)
Set Roles Statement	494	(1)
Other Rules	495	(1)
Revoke	496	(4)
Revoking Privileges	496	(3)
Revoking Roles	499	(1)
Additional Details of Revoke	500	(8)
Chapter Summary	508	(3)
Transaction Management	511	(22)
Introduction	511	(1)
SQL:1999 Transaction Syntax	512	(4)
Set Transaction Statement	512	(3)
Start Transaction Statement	515	(1)
SQL:1999 Isolation Levels	516	(7)
Transaction Phenomena and Isolation Levels	516	(5)
Other Phenomena and Possible Isolation Levels	521	(1)
Intra-Transaction and Inter-Transaction Interactions	522	(1)
Mixed DML and DDL	523	(1)
Transaction Termination	523	(1)
Transactions and Constraints	524	(2)
Savepoints---Establishment and Removal	526	(2)
Additional Transaction Termination Actions	528	(3)
Holdable Cursors and Holdable Locators	529	(1)
Chained Transactions	530	(1)
Chapter Summary	531	(2)
Connections and Remote Database Access	533	(8)
Introduction	533	(1)
Establishing Connections	534	(2)
Connection Statements	536	(3)
Connect	536	(2)
Set Connection	538	(1)
Disconnect	538	(1)
RDA (Remote Database Access)	539	(1)
Termination Statements and Connections	540	(1)
Chapter Summary	540	(1)
Routines and Routine Invocation (Functions and Procedures)	541	(28)
Introduction	541	(1)
Relationship of SQL:1999's Routines to SQL/PSM	542	(1)
SQL-Invoked Routines	543	(14)
SQL Parameters	543	(1)
Distinction between Procedures and Functions	544	(1)
How---and Why---to Store SQL-Invoked Routines	545	(1)
SQL Statements to Store, Modify, and Delete Stored Routines	546	(11)
External Routines	557	(5)
Use and Need for External Routines	557	(1)
Array-Returning External Functions	558	(1)
Where Are External Routines Actually Stored?	559	(1)
External Routine Execution Context	560	(1)
External Routine Advantages and Disadvantages	561	(1)
SQL Routines	562	(1)
Use and Need for SQL Routines	562	(1)
Storing and Managing SQL Routines	563	(1)
Limitations of SQL for Stored Routines	563	(1)
Polymorphism	564	(1)
Routine Resolution Outline	564	(3)
Chapter Summary	567	(2)
Dynamic SQL	569	(56)
Introduction	569	(1)
What Is Dynamic SQL?	569	(2)
The Philosophy of Dynamic SQL	571	(3)
Parameters	573	(1)
Normal and Extended Dynamic	574	(1)
Outline of Dynamic SQL Processing	574	(15)
Parameters and Codes	575	(14)
The Execute Immediate Statement	589	(1)
Prepare and Execute Statements	590	(4)
The Describe Statement	594	(8)
Describe Output Statement Execution	595	(3)
Describe Input Statement Execution	598	(1)
Describe Dynamic Result Set Statement Execution	599	(3)
Rows, Collections, and the Describe Statement	602	(5)
The Execute Statement Revisited	607	(3)
Dynamic SQL and Cursors	610	(6)
Dynamic Cursors	611	(3)
Dynamic Positioned Statements	614	(2)
A Dynamic SQL Example	616	(7)
Chapter Summary	623	(2)
Call-Level Interface (CLI)	625	(58)
Introduction	625	(1)
A Brief History of SQL/CLI and ODBC	626	(1)
Brief Comparison of SQL/CLI and Dynamic SQL	627	(2)
The SQL/CLI Model	629	(1)
Handles	630	(4)
Environment Handles	631	(1)
Connection Handles	631	(1)
Statement Handles	632	(1)
Descriptor Handles	633	(1)
Null Pointers	634	(1)
Database Connections and Implicit Connections	634	(3)
CLI Descriptor Areas Versus Dynamic Descriptor Areas	637	(6)
Types of CLI Descriptor Area	638	(2)
CLI Descriptor Area Fields	640	(3)
Statement Preparation and Execution	643	(7)
Simple Statements That Do Not Retrieve Data	643	(4)
Implicit Cursors	647	(2)
Implicit Dynamic Operations	649	(1)
Deferred Parameters	650	(7)
Transaction Management	657	(1)
Diagnostics Facilities	658	(4)
Metadata Access and Catalog Routines	662	(1)
CLI Routines Summary	663	(14)
Annotated CLI Example	677	(4)
Chapter Summary	681	(2)
Diagnostics and Error Management	683	(18)
Introduction	683	(1)
Sqlstate	683	(12)
Whatever Happened to Sqlcode?	695	(1)
SQL Statement Codes	696	(3)
Chapter Summary	699	(2)
Internationalization Aspects of SQL:1999	701	(14)
Introduction	701	(1)
Character Sets and Collations	702	(9)
Unicode and UCS	704	(2)
Character Set Use in SQL	706	(1)
Coercibility	707	(2)
Coercibility Attributes	709	(1)
Order by	710	(1)
Group by	711	(1)
Translations and Conversions	711	(1)
Mapping Host Language Capabilities to SQL's Character Set Facilities	712	(1)
Equal, Distinct, Identical	713	(1)
Chapter Summary	714	(1)
Information Schema	715	(26)
Introduction	715	(1)
Metadata, Repositories, and the Information_Schema	715	(1)
Definition_Schema and Base Tables	716	(1)
Self-Description	717	(1)
Information_Schema and Privileges	718	(1)
Information_Schema Extensions	718	(1)
Identifier Representation	719	(3)
The Definition_Schema	722	(10)
Schema Definition	723	(1)
The Base Tables	723	(8)
Assertions on the Base Tables	731	(1)
The Information_Schema	732	(5)
Schema Definition	732	(1)
Domains	732	(1)
Views	733	(4)
Short-Name Views	737	(2)
Chapter Summary	739	(2)
A Look to the Future	741	(6)
Introduction	741	(1)
SQL:200n Overview	742	(3)
New Data Types	742	(1)
Enhancements to Triggers	743	(1)
SQL-Invoked Routines	743	(1)
Improved Security Features	743	(1)
Object Capabilities	744	(1)
XML and SQL	745	(1)
Chapter Summary	746	(1)
Appendix A Designing SQL:1999 Databases	747	(10)
A.1 Introduction	747	(1)
A.2 Overview of Database Design	748	(3)
A.3 Impact of Data Warehousing on Designing SQL:1999 Databases	751	(3)
A.4 Physical Database Design	754	(1)
A.5 Use of Case and Data Modeling Tools	755	(1)
A.6 Appendix Summary	756	(1)
Appendix B An SQL:1999 Application Example	757	(18)
B.1 Introduction	757	(1)
B.2 The Schema Definition	757	(7)
B.3 Application Code	764	(11)
B.3.1 Data Input	765	(3)
B.3.2 Data Modification	768	(1)
B.3.3 Table Structure Modification	769	(1)
B.3.4 Data Removal	769	(1)
B.3.5 Data Access and Management	770	(5)
Appendix C The SQL:1999 Annexes	775	(42)
C.1 Introduction	775	(1)
C.2 Differences between SQL-92 and SQL:1999	775	(5)
C.2.1 Incompatible Differences	776	(3)
C.2.2 Compatible Differences	779	(1)
C.3 Implementation-Defined and Implementation-Dependent	780	(20)
C.3.1 Implementation-Defined	781	(12)
C.3.2 Implementation-Dependent	793	(7)
C.4 Deprecated Features	800	(1)
C.5 SQL:1999 Conformance Claims	801	(15)
C.5.1 The Features of Core SQL:1999	802	(6)
C.5.2 The Features of SQL:1999 Not in Core SQL	808	(6)
C.5.3 Content of SQL:1999 Packages	814	(2)
C.6 Appendix Summary	816	(1)
Appendix D Relevant Standards Bodies	817	(8)
D.1 Introduction	817	(2)
D.2 Contacting ISO	819	(1)
D.3 Selected National Standards Bodies	819	(6)
Appendix E Status Codes	825	(8)
E.1 Values of Sqlstate	825	(8)
Appendix F The SQL Standardization Process	833	(12)
F.1 Introduction	833	(1)
F.2 The Various Standards Bodies	833	(5)
F.2.1 National Standards	833	(2)
F.2.2 International Standards	835	(1)
F.2.3 Standards Development in ISO	836	(2)
F.3 History of the SQL Standard	838	(3)
F.3.1 SQL2	840	(1)
F.3.2 SQL3	841	(1)
F.4 Nist and the Fips	841	(1)
F.5 Other SQL-Related Organizations	842	(2)
F.6 Appendix Summary	844	(1)
Index	845	(50)
About the Authors	895