Cover image for SQL, the complete reference
Title:
SQL, the complete reference
Author:
Groff, James R.
Personal Author:
Publication Information:
Berkeley, Calif. : Osborne/McGraw-Hill, [1999]

©1999
Physical Description:
xxviii, 994 pages : illustrations ; 24 cm + 1 computer laser optical disc (4 3/4 in.)
General Note:
Includes index.
Language:
English
Added Author:
ISBN:
9780072118452
Format :
Book

Available:*

Library
Call Number
Material Type
Home Location
Status
Item Holds
Searching...
QA76.73.S67 G758 1999 Book and Software Set Central Closed Stacks
Searching...

On Order

Summary

Summary

Providing the conceptual framework and insight into relational database technology, this how-to guide includes comprehensive coverage of every SQL feature and topic as well as current enhancements and extensions.


Author Notes

James R. Groff and Paul N. Weinberg were the cofounders of Network Innovations Corporation, an early developer of SQL-based networking software that linked personal computers to corporate databases. Groff is currently CEO of TimesTen Performance Software, developer of an ultra-high-performance main-memory SQL database for communications and Internet applications. Weinberg is Vice President of A2i, Inc., developer of a database-driven, cross-media catalog publishing system that supports printed and electronic output from a single data source.


Table of Contents

Acknowledgmentsp. xxiii
Prefacep. xxv
Part I An Overview of SQL
1 Introductionp. 3
The SQL Languagep. 4
The Role of SQLp. 6
SQL Features and Benefitsp. 8
Vendor Independencep. 8
Portability Across Computer Systemsp. 9
SQL Standardsp. 9
IBM Endorsement (DB2)p. 9
Microsoft Commitment (ODBC and ADO)p. 9
Relational Foundationp. 10
High-Level, English-Like Structurep. 10
Interactive, Ad Hoc Queriesp. 10
Programmatic Database Accessp. 10
Multiple Views of Datap. 10
Complete Database Languagep. 11
Dynamic Data Definitionp. 11
Client/Server Architecturep. 11
Extensibility and Object Technologyp. 11
Internet Database Accessp. 11
Java Integration (JDBC)p. 12
2 A Quick Tour of SQLp. 13
A Simple Databasep. 14
Retrieving Datap. 14
Summarizing Datap. 17
Adding Data to the Databasep. 18
Deleting Datap. 19
Updating the Databasep. 19
Protecting Datap. 20
Creating a Databasep. 21
Summaryp. 22
3 SQL In Perspectivep. 23
SQL and Database Managementp. 24
A Brief History of SQLp. 25
The Early Yearsp. 26
Early Relational Productsp. 26
IBM Productsp. 27
Commercial Acceptancep. 27
SQL Standardsp. 29
The ANSI/ISO Standardsp. 29
Other SQL Standardsp. 31
ODBC and the SQL Access Groupp. 31
The Portability Mythp. 32
SQL and Networkingp. 34
Centralized Architecturep. 34
File Server Architecturep. 35
Client/Server Architecturep. 36
Multi-Tier Architecturep. 37
The Proliferation of SQLp. 38
SQL and IBM's Unified Database Strategyp. 39
SQL on Minicomputersp. 39
SQL on Unix-Based Systemsp. 40
SQL on Personal Computersp. 40
SQL and Transaction Processingp. 41
SQL and Workgroup Databasesp. 42
SQL and Data Warehousingp. 43
Summaryp. 45
4 Relational Databasesp. 47
Early Data Modelsp. 48
File Management Systemsp. 48
Hierarchical Databasesp. 49
Network Databasesp. 51
The Relational Data Modelp. 53
The Sample Databasep. 54
Tablesp. 56
Primary Keysp. 57
Relationshipsp. 59
Foreign Keysp. 59
Codd's Twelve Rules*p. 61
Summaryp. 64
Part II Retrieving Data
5 SQL Basicsp. 67
Statementsp. 68
Namesp. 72
Table Namesp. 74
Column Namesp. 74
Data Typesp. 75
Constantsp. 80
Numeric Constantsp. 81
String Constantsp. 81
Date and Time Constantsp. 82
Symbolic Constantsp. 83
Expressionsp. 84
Built-in Functionsp. 85
Missing Data (NULL Values)p. 86
Summaryp. 88
6 Simple Queriesp. 91
The SELECT Statementp. 92
The SELECT Clausep. 94
The FROM Clausep. 94
Query Resultsp. 94
Simple Queriesp. 98
Calculated Columnsp. 100
Selecting All Columns (SELECT *)p. 103
Duplicate Rows (DISTINCT)p. 104
Row Selection (WHERE Clause)p. 105
Search Conditionsp. 107
Comparison Test (=, [], [, [=, ], ]=)p. 108
Range Test (BETWEEN)p. 111
Set Membership Test (IN)p. 114
Pattern Matching Test (LIKE)p. 116
Null Value Test (IS NULL)p. 119
Compound Search Conditions (AND,OR, and NOT)p. 121
Sorting Query Results (ORDER BY Clause)p. 124
Rules for Single-Table Query Processingp. 127
Combining Query Results (UNION)*p. 128
Unions and Duplicate Rows*p. 130
Unions and Sorting*p. 132
Multiple UNIONS*p. 132
Summaryp. 135
7 Multi-Table Queries (Joins)p. 137
A Two-Table Query Examplep. 138
Simple Joins (Equi-Joins)p. 140
Parent/Child Queriesp. 142
Joins with Row Selection Criteriap. 145
Multiple Matching Columnsp. 145
Queries with Three or More Tablesp. 146
Other Equi-Joinsp. 149
Non-Equi Joinsp. 152
SQL Considerations for Multi-Table Queriesp. 153
Qualified Column Namesp. 153
All-Column Selectionsp. 155
Self-Joinsp. 155
Table Aliasesp. 159
Multi-Table Query Performancep. 160
The Structure of a Joinp. 161
Table Multiplicationp. 161
Rules for Multi-Table Query Processingp. 162
Outer Joins*p. 164
Left and Right Outer Joins*p. 168
Outer Join Notation*p. 172
Joins and the SQL2 Standardp. 174
Inner Joins in SQL2*p. 174
Outer Joins in SQL2*p. 177
Cross Joins and Union Joins in SQL2*p. 179
Multi-Table Joins in SQL2p. 181
Summaryp. 184
8 Summary Queriesp. 187
Column Functionsp. 188
Computing a Column Total (SUM)p. 191
Computing a Column Average (AVG)p. 191
Finding Extreme Values (MIN and MAX)p. 192
Counting Data Values (COUNT)p. 194
Column Functions in the Select Listp. 195
NULL Values and Column Functionsp. 197
Duplicate Row Elimination (DISTINCT)p. 199
Grouped Queries (GROUP BY Clause)p. 201
Multiple Grouping Columnsp. 204
Restrictions on Grouped Queriesp. 208
NULL Values in Grouping Columnsp. 210
Group Search Conditions (HAVING Clause)p. 211
Restrictions on Group Search Conditionsp. 215
NULL Values and Group Search Conditionsp. 215
HAVING Without GROUP BYp. 216
Summaryp. 216
9 Subqueries and Query Expressionsp. 217
Using Subqueriesp. 218
What Is a Subquery?p. 219
Subqueries in the WHERE Clausep. 221
Outer Referencesp. 222
Subquery Search Conditionsp. 223
Subquery Comparison Test (=, [], [, [=, ], ]=)p. 224
Set Membership Test (IN)p. 226
Existence Test (EXISTS)p. 228
Quantified Tests (ANY and ALL)*p. 231
Subqueries and Joinsp. 237
Nested Subqueriesp. 239
Correlated Subqueries*p. 240
Subqueries in the HAVING Clause*p. 243
Subquery Summaryp. 245
Advanced Queries in SQL2*p. 246
Scalar-Valued Expressions (SQL2)p. 248
Row Value Expressions (SQL2)p. 254
Table Value Expressions (SQL2)p. 258
Query Expressions (SQL2)p. 261
SQL Queries--A Final Summaryp. 267
Part III Updating Data
10 Database Updatesp. 271
Adding Data to the Databasep. 272
The Single-Row INSERT Statementp. 273
The Multi-Row INSERT Statementp. 277
Bulk Load Utilitiesp. 280
Deleting Data from the Databasep. 280
The DELETE Statementp. 280
Deleting All Rowsp. 282
DELETE with Subquery*p. 283
Modifying Data in the Databasep. 285
The UPDATE Statementp. 286
Updating All Rowsp. 288
UPDATE with Subquery*p. 289
Summaryp. 290
11 Data Integrityp. 291
What Is Data Integrity?p. 292
Required Datap. 294
Simple Validity Checkingp. 294
Column Check Constraints (SQL2)p. 295
Domains (SQL2)p. 296
Entity Integrityp. 297
Other Uniqueness Constraintsp. 298
Uniqueness and NULL Valuesp. 298
Referential Integrityp. 299
Referential Integrity Problemsp. 300
Delete and Update Rules*p. 302
Cascaded Deletes and Updates*p. 305
Referential Cycles*p. 308
Foreign Keys and NULL Values*p. 312
Advanced Constraint Capabilities (SQL2)p. 313
Assertionsp. 314
SQL2 Constraint Typesp. 315
Deferred Constraint Checkingp. 316
Business Rulesp. 319
What Is a Trigger?p. 320
Triggers and Referential Integrityp. 323
Trigger Advantages and Disadvantagesp. 324
Triggers and the SQL Standardp. 324
Summaryp. 325
12 Transaction Processingp. 327
What Is a Transaction?p. 328
COMMIT and ROLLBACKp. 329
The ANSI/ISO Transaction Modelp. 332
Other Transaction Modelsp. 334
Transactions: Behind the Scenes*p. 336
Transactions and Multi-User Processingp. 338
The Lost Update Problemp. 338
The Uncommitted Data Problemp. 339
The Inconsistent Data Problemp. 341
The Phantom Insert Problemp. 342
Concurrent Transactionsp. 342
Locking*p. 345
Locking Levelsp. 345
Shared and Exclusive Locksp. 347
Deadlocks*p. 348
Advanced Locking Techniques*p. 351
Summaryp. 357
Part IV Database Structure
13 Creating a Databasep. 361
The Data Definition Languagep. 362
Creating a Databasep. 364
Table Definitionsp. 365
Creating a Table (CREATE TABLE)p. 365
Removing a Table (DROP TABLE)p. 376
Changing a Table Definition (ALTER TABLE)p. 377
Constraint Definitionsp. 381
Assertionsp. 382
Domainsp. 382
Aliases and Synonyms (CREATE/DROP ALIAS)p. 383
Indexes (CREATE/DROP INDEX)p. 385
Managing Other Database Objectsp. 388
Database Structurep. 392
Single-Database Architecturep. 393
Multi-Database Architecturep. 394
Multi-Location Architecturep. 396
Database Structure and the ANSI/ISO Standardp. 398
SQL2 Catalogsp. 401
SQL2 Schemasp. 402
Summaryp. 406
14 Viewsp. 407
What Is a View?p. 408
How the DBMS Handles Viewsp. 410
Advantages of Viewsp. 410
Disadvantages of Viewsp. 411
Creating a View (CREATE VIEW)p. 411
Horizontal Viewsp. 412
Vertical Viewsp. 414
Row/Column Subset Viewsp. 416
Grouped Viewsp. 416
Joined Viewsp. 419
Updating a Viewp. 421
View Updates and the ANSI/ISO Standardp. 422
View Updates in Commercial SQL Productsp. 423
Checking View Updates (CHECK OPTION)p. 424
Dropping a View (DROP VIEW)p. 426
Summaryp. 428
15 SQL Securityp. 429
SQL Security Conceptsp. 430
User-Idsp. 432
Security Objectsp. 436
Privilegesp. 436
Views and SQL Securityp. 439
Granting Privileges (GRANT)p. 442
Column Privilegesp. 444
Passing Privileges (GRANT OPTION)p. 445
Revoking Privileges (REVOKE)p. 447
REVOKE and the GRANT OPTIONp. 450
REVOKE and the ANSI/ISO Standardp. 452
Summaryp. 454
16 The System Catalogp. 455
What Is the System Catalog?p. 456
The Catalog and Query Toolsp. 457
The Catalog and the ANSI/ISO Standardp. 457
Catalog Contentsp. 458
Table Informationp. 459
Column Informationp. 464
View Informationp. 466
Remarksp. 470
Relationship Informationp. 470
User Informationp. 474
Privileges Informationp. 476
The SQL2 Information Schemap. 477
Other Catalog Informationp. 483
Summaryp. 483
Part V Programming with SQL
17 Embedded SQLp. 487
Programmatic SQL Techniquesp. 488
DBMS Statement Processingp. 490
Embedded SQL Conceptsp. 491
Developing an Embedded SQL Programp. 492
Running an Embedded SQL Programp. 492
Simple Embedded SQL Statementsp. 498
Declaring Tablesp. 501
Error Handlingp. 502
Using Host Variablesp. 511
Data Retrieval in Embedded SQLp. 520
Single-Row Queriesp. 520
Multi-Row Queriesp. 527
Cursor-Based Deletes and Updatesp. 535
Cursors and Transaction Processingp. 540
Summaryp. 541
18 Dynamic SQL*p. 543
Limitations of Static SQLp. 544
Dynamic SQL Conceptsp. 546
Dynamic Statement Execution (EXECUTE IMMEDIATE)p. 547
Two-Step Dynamic Executionp. 550
The PREPARE Statementp. 554
The EXECUTE Statementp. 555
Dynamic Queriesp. 564
The DESCRIBE Statementp. 569
The DECLARE CURSOR Statementp. 572
The Dynamic OPEN Statementp. 572
The Dynamic FETCH Statementp. 575
The Dynamic CLOSE Statementp. 576
Dynamic SQL Dialectsp. 576
Dynamic SQL in SQL/DSp. 577
Dynamic SQL in Oracle*p. 580
Dynamic SQL and the SQL2 Standardp. 584
Basic Dynamic SQL2 Statementsp. 585
SQL2 and the SQLDAp. 587
SQL2 and Dynamic SQL Queriesp. 594
Summaryp. 598
19 SQL APIsp. 601
API Conceptsp. 602
The dblib API (SQL Server)p. 604
Basic SQL Server Techniquesp. 606
SQL Server Queriesp. 613
Positioned Updatesp. 619
Dynamic Queriesp. 620
ODBC and the SQL/CLI Standardp. 625
Call-Level Interface Standardizationp. 625
CLI Structuresp. 631
CLI Statement Processingp. 635
CLI Errors and Diagnostic Informationp. 654
CLI Attributesp. 656
CLI Information Callsp. 658
The ODBC APIp. 659
The Structure of ODBCp. 659
ODBC and DBMS Independencep. 661
ODBC Catalog Functionsp. 661
Extended ODBC Capabilitiesp. 663
The Oracle Call Interface (OCI)p. 667
Legacy OCIp. 667
OCI and Oracle8p. 670
Summaryp. 677
Part VI SQL Today and Tomorrow
20 Database Processing and Stored Proceduresp. 681
Stored Procedure Conceptsp. 682
A Basic Examplep. 683
Using Stored Proceduresp. 684
Creating a Stored Procedurep. 686
Calling a Stored Procedurep. 689
Stored Procedure Variablesp. 690
Statement Blocksp. 692
Returning a Valuep. 695
Returning Values via Parametersp. 697
Conditional Executionp. 701
Repeated Executionp. 703
Other Flow-of-Control Constructsp. 705
Cursor-Based Repetitionp. 706
Handling Error Conditionsp. 710
Advantages of Stored Proceduresp. 713
Stored Procedure Performancep. 713
System-Defined Stored Proceduresp. 714
External Stored Proceduresp. 715
Triggersp. 716
Advantages and Disadvantages of Triggersp. 716
Triggers in Transact-SQLp. 717
Triggers in Informix SPLp. 719
Triggers in Oracle PL/SQLp. 722
Other Trigger Considerationsp. 723
Stored Procedures and the SQL Standardp. 724
Core Capabilitiesp. 724
Creating a SQL Routinep. 725
Flow-of-Control Statementsp. 725
Cursor Operationsp. 726
Block Structurep. 727
Error Handlingp. 730
Routine Name Overloadingp. 731
Other Stored Procedure Considerationsp. 732
Summaryp. 733
21 SQL and Data Warehousingp. 735
Data Warehousing Conceptsp. 736
Components of a Data Warehousep. 738
The Evolution of Data Warehousingp. 739
Database Architecture for Warehousingp. 740
Fact Cubesp. 740
Star Schemasp. 742
Multi-Level Dimensionsp. 745
SQL Extensions for Data Warehousingp. 746
Warehouse Performancep. 747
Load Performancep. 747
Query Performancep. 749
Summaryp. 750
22 SQL Networking and Distributed Databasesp. 751
The Challenge of Distributed Data Managementp. 752
Distributing Data--Practical Approachesp. 758
Remote Database Accessp. 758
Remote Data Transparencyp. 762
Table Extractsp. 764
Table Replicationp. 766
Updateable Replicasp. 769
Replication Tradeoffsp. 770
Typical Replication Architecturesp. 771
Distributed Database Accessp. 776
Remote Requestsp. 777
Remote Transactionsp. 778
Distributed Transactionsp. 779
Distributed Requestsp. 780
The Two-Phase Commit Protocol*p. 782
Network Applications and Database Architecturep. 785
Client/Server Applications and Database Architecturep. 786
Client/Server Applications with Stored Proceduresp. 787
Enterprise Applications and Data Cachingp. 789
High-Volume Internet Data Managementp. 790
Summaryp. 792
23 SQL and Objectsp. 793
Object-Oriented Databasesp. 794
Object-Oriented Database Characteristicsp. 794
Pros and Cons of Object-Oriented Databasesp. 796
Objects and the Database Marketp. 797
Object-Relational Databasesp. 798
Large Object Supportp. 799
BLOBs in the Relational Modelp. 799
Specialized BLOB Processingp. 801
Abstract (Structured) Data Typesp. 803
Defining Abstract Data Typesp. 805
Manipulating Abstract Data Typesp. 808
Inheritancep. 809
Table Inheritance--Implementing Object Classesp. 812
Sets, Arrays, and Collectionsp. 815
Defining Collectionsp. 817
Querying Collection Datap. 820
Manipulating Collection Datap. 822
Collections and Stored Proceduresp. 823
User-Defined Data Typesp. 825
Methods and Stored Proceduresp. 826
Summaryp. 830
24 The Future of SQLp. 831
Database Market Trendsp. 832
Enterprise Database Market Maturityp. 833
Market Diversity and Segmentationp. 834
Packaged Enterprise Applicationsp. 834
Hardware Performance Gainsp. 835
Benchmark Warsp. 837
SQL Standardizationp. 839
SQL in the Next Decadep. 840
Distributed Databasesp. 841
Massive Data Warehousingp. 841
Ultra-High-Performance Databasesp. 842
Internet and Network Services Integrationp. 843
Embedded Databasesp. 843
Object Integrationp. 844
Part VII Appendices
A The Sample Databasep. 849
B Database Vendor Profilesp. 857
A2i, Inc. (www.a2i.com)p. 859
Angara Database Systems (www.angara.com)p. 859
Arbor Software (www.hyperion.com)p. 859
Ardent Software (www.ardentsoftware.com)p. 860
Centura Software (www.centurasoft.com)p. 860
Cloudscape, Inc. (www.cloudscape.com)p. 861
Computer Associates (www.cai.com)p. 861
Computer Corporation of America (www.cca-int.com)p. 862
Empress Software (www.empress.com)p. 863
IBM Corporation (www.ibm.com)p. 863
Informix Software (www.informix.com)p. 864
Microsoft Corporation (www.microsoft.com)p. 865
Object Design (www.odi.com)p. 866
Objectivity (www.objectivity.com)p. 867
Oracle Corporation (www.oracle.com)p. 867
Persistence Software (www.persistence.com)p. 868
Pervasive Software (www.pervasive.com)p. 869
Quadbase Systems (www.quadbase.com)p. 869
Raima Corporation (www.raima.com)p. 870
Red Brick Systems (www.redbrick.com)p. 870
Rogue Wave Software (www.roguewave.com)p. 871
Sybase, Inc. (www.sybase.com)p. 871
Tache Group (www.tachegroup.com)p. 873
Tandem Computers (www.tandem.com)p. 873
TimesTen Performance Software (www.timesten.com)p. 874
Versant Corporation (www.versant.com)p. 874
C Company and Product Listp. 877
D SQL Syntax Referencep. 885
Data Definition Statementsp. 886
Basic Data Manipulation Statementsp. 888
Transaction Processing Statementsp. 889
Cursor-Based Statementsp. 889
Query Expressionsp. 890
Search Conditionsp. 892
Expressionsp. 892
Statement Elementsp. 893
Simple Elementsp. 893
E SQL Call Level Interfacep. 895
CLI Return Valuesp. 898
General Handle Management Routinesp. 898
SQL Environment Management Routinesp. 898
SQL Connection Management Routinesp. 899
SQL Statement Management Routinesp. 901
SQL Statement Execution Routinesp. 902
Query Results Processing Routinesp. 902
Query Results Description Routinesp. 904
Query Results Descriptor Management Routinesp. 904
Deferred Dynamic Parameter Processing Routinesp. 906
Error, Status, and Diagnostic Routinesp. 906
CLI Implementation Information Routinesp. 907
CLI Parameter Value Codesp. 908
F SQL Information Schema Standardp. 915
SCHEMATA Viewp. 917
TABLES Viewp. 918
COLUMNS Viewp. 919
VIEWS Viewp. 920
VIEW_TABLE_USAGE Viewp. 921
VIEW_COLUMN_USAGE Viewp. 922
TABLE_CONSTRAINTS Viewp. 922
REFERENTIAL_CONSTRAINTS Viewp. 923
CHECK_CONSTRAINTS Viewp. 924
KEY_COLUMN_USAGE Viewp. 925
ASSERTIONS Viewp. 925
CONSTRAINT_TABLE_USAGE Viewp. 926
CONSTRAINT_COLUMN_USAGE Viewp. 926
TABLE_PRIVILEGES Viewp. 927
COLUMN_PRIVILEGES Viewp. 928
USAGE_PRIVILEGES Viewp. 929
DOMAINS Viewp. 930
DOMAIN_CONSTRAINTS Viewp. 931
DOMAIN_COLUMN_USAGE Viewp. 932
CHARACTER_SETS Viewp. 932
COLLATIONS Viewp. 933
TRANSLATIONS Viewp. 934
SQL_LANGUAGES Viewp. 935
G CD-ROM Installation Guidep. 937
Installing the SQL DBMS Softwarep. 939
Microsoft SQL Server 7p. 939
Hardware and Software Requirementsp. 940
SQL Server Services User Accountsp. 940
SQL Server 7.0 Installationp. 941
Starting SQL Server 7.0p. 942
Stopping SQL Server 7.0p. 943
Uninstalling SQL Server 7.0p. 943
Oracle8p. 944
Products Included on the CDp. 944
Hardware and Software Requirementsp. 946
Products Available for Installationp. 947
Oracle8 Personal Edition Installationp. 948
Starting Oracle8 Personal Editionp. 950
Stopping Oracle8 Personal Editionp. 950
Uninstalling Oracle8 Personal Editionp. 950
Informixp. 951
Hardware and Software Requirementsp. 952
Informix User Accountsp. 952
Informix Personal Edition Installationp. 952
Starting Informix Personal Editionp. 954
Stopping Informix Personal Editionp. 954
Troubleshooting Installation Problemsp. 955
Uninstalling Informix Personal Editionp. 955
Sybasep. 956
Hardware and Software Requirementsp. 956
Sybase Adaptive Server Anywhere Installationp. 957
Starting Sybase Adaptive Server Anywherep. 958
Stopping Sybase Adaptive Server Anywherep. 958
Uninstalling Sybase Adaptive Server Anywherep. 958
IBM DB2p. 959
Hardware and Software Requirementsp. 959
DB2 User Accountsp. 960
DB2 Personal Edition Installationp. 961
Starting DB2 Personal Editionp. 962
Stopping DB2 Personal Editionp. 962
Verifying the Installationp. 963
Troubleshooting Installation Problemsp. 963
Uninstalling DB2 Personal Editionp. 963
Indexp. 965