Skip to content

SQL The Complete Reference

ISBN-10: 0072118458

ISBN-13: 9780072118452

Edition: 1999

Authors: James R. Groff, Paul N. Weinberg

List price: $49.99
Blue ribbon 30 day, 100% satisfaction guarantee!
what's this?
Rush Rewards U
Members Receive:
Carrot Coin icon
XP icon
You have reached 400 XP and carrot coins. That is the daily max!

Description:

This reference guide to SQL delivers the conceptual framework and insight into relational database technology and its trends. The book covers the core SQL language and also describes the differences between popular SQL products (DB2, Oracle, etc.).
Customers also bought

Book details

List price: $49.99
Copyright year: 1999
Publisher: McGraw-Hill Osborne
Binding: Mixed Media
Pages: 1008
Size: 7.40" wide x 9.09" long x 2.32" tall
Weight: 4.378
Language: English

James R. Groff is senior vice president of business strategy at Oracle Corporation. He is a SQL expert whose SQL-oriented software company, TimesTen Performance Software, was acquired by Oracle in 2005. Paul N. Weinberg is senior vice president of NetWeaver MDM at SAP. He is a SQL expert whose SQL-oriented software company, A2i, Inc., was acquired by SAP in 2004. Weinberg is the bestselling author, with James Groff, of the previous editions of this book.

Acknowledgments
Preface
An Overview of SQL
Introduction
The SQL Language
The Role of SQL
SQL Features and Benefits
Vendor Independence
Portability Across Computer Systems
SQL Standards
IBM Endorsement (DB2)
Microsoft Commitment (ODBC and ADO)
Relational Foundation
High-Level, English-Like Structure
Interactive, Ad Hoc Queries
Programmatic Database Access
Multiple Views of Data
Complete Database Language
Dynamic Data Definition
Client/Server Architecture
Extensibility and Object Technology
Internet Database Access
Java Integration (JDBC)
A Quick Tour of SQL
A Simple Database
Retrieving Data
Summarizing Data
Adding Data to the Database
Deleting Data
Updating the Database
Protecting Data
Creating a Database
Summary
SQL In Perspective
SQL and Database Management
A Brief History of SQL
The Early Years
Early Relational Products
IBM Products
Commercial Acceptance
SQL Standards
The ANSI/ISO Standards
Other SQL Standards
ODBC and the SQL Access Group
The Portability Myth
SQL and Networking
Centralized Architecture
File Server Architecture
Client/Server Architecture
Multi-Tier Architecture
The Proliferation of SQL
SQL and IBM's Unified Database Strategy
SQL on Minicomputers
SQL on Unix-Based Systems
SQL on Personal Computers
SQL and Transaction Processing
SQL and Workgroup Databases
SQL and Data Warehousing
Summary
Relational Databases
Early Data Models
File Management Systems
Hierarchical Databases
Network Databases
The Relational Data Model
The Sample Database
Tables
Primary Keys
Relationships
Foreign Keys
Codd's Twelve Rules*
Summary
Retrieving Data
SQL Basics
Statements
Names
Table Names
Column Names
Data Types
Constants
Numeric Constants
String Constants
Date and Time Constants
Symbolic Constants
Expressions
Built-in Functions
Missing Data (NULL Values)
Summary
Simple Queries
The SELECT Statement
The SELECT Clause
The FROM Clause
Query Results
Simple Queries
Calculated Columns
Selecting All Columns (SELECT *)
Duplicate Rows (DISTINCT)
Row Selection (WHERE Clause)
Search Conditions
Comparison Test (=, [], [, [=, ], ]=)
Range Test (BETWEEN)
Set Membership Test (IN)
Pattern Matching Test (LIKE)
Null Value Test (IS NULL)
Compound Search Conditions (AND,OR, and NOT)
Sorting Query Results (ORDER BY Clause)
Rules for Single-Table Query Processing
Combining Query Results (UNION)*
Unions and Duplicate Rows*
Unions and Sorting*
Multiple UNIONS*
Summary
Multi-Table Queries (Joins)
A Two-Table Query Example
Simple Joins (Equi-Joins)
Parent/Child Queries
Joins with Row Selection Criteria
Multiple Matching Columns
Queries with Three or More Tables
Other Equi-Joins
Non-Equi Joins
SQL Considerations for Multi-Table Queries
Qualified Column Names
All-Column Selections
Self-Joins
Table Aliases
Multi-Table Query Performance
The Structure of a Join
Table Multiplication
Rules for Multi-Table Query Processing
Outer Joins*
Left and Right Outer Joins*
Outer Join Notation*
Joins and the SQL2 Standard
Inner Joins in SQL2*
Outer Joins in SQL2*
Cross Joins and Union Joins in SQL2*
Multi-Table Joins in SQL2
Summary
Summary Queries
Column Functions
Computing a Column Total (SUM)
Computing a Column Average (AVG)
Finding Extreme Values (MIN and MAX)
Counting Data Values (COUNT)
Column Functions in the Select List
NULL Values and Column Functions
Duplicate Row Elimination (DISTINCT)
Grouped Queries (GROUP BY Clause)
Multiple Grouping Columns
Restrictions on Grouped Queries
NULL Values in Grouping Columns
Group Search Conditions (HAVING Clause)
Restrictions on Group Search Conditions
NULL Values and Group Search Conditions
HAVING Without GROUP BY
Summary
Subqueries and Query Expressions
Using Subqueries
What Is a Subquery?
Subqueries in the WHERE Clause
Outer References
Subquery Search Conditions
Subquery Comparison Test (=, [], [, [=, ], ]=)
Set Membership Test (IN)
Existence Test (EXISTS)
Quantified Tests (ANY and ALL)*
Subqueries and Joins
Nested Subqueries
Correlated Subqueries*
Subqueries in the HAVING Clause*
Subquery Summary
Advanced Queries in SQL2*
Scalar-Valued Expressions (SQL2)
Row Value Expressions (SQL2)
Table Value Expressions (SQL2)
Query Expressions (SQL2)
SQL Queries--A Final Summary
Updating Data
Database Updates
Adding Data to the Database
The Single-Row INSERT Statement
The Multi-Row INSERT Statement
Bulk Load Utilities
Deleting Data from the Database
The DELETE Statement
Deleting All Rows
DELETE with Subquery*
Modifying Data in the Database
The UPDATE Statement
Updating All Rows
UPDATE with Subquery*
Summary
Data Integrity
What Is Data Integrity?
Required Data
Simple Validity Checking
Column Check Constraints (SQL2)
Domains (SQL2)
Entity Integrity
Other Uniqueness Constraints
Uniqueness and NULL Values
Referential Integrity
Referential Integrity Problems
Delete and Update Rules*
Cascaded Deletes and Updates*
Referential Cycles*
Foreign Keys and NULL Values*
Advanced Constraint Capabilities (SQL2)
Assertions
SQL2 Constraint Types
Deferred Constraint Checking
Business Rules
What Is a Trigger?
Triggers and Referential Integrity
Trigger Advantages and Disadvantages
Triggers and the SQL Standard
Summary
Transaction Processing
What Is a Transaction?
COMMIT and ROLLBACK
The ANSI/ISO Transaction Model
Other Transaction Models
Transactions: Behind the Scenes*
Transactions and Multi-User Processing
The Lost Update Problem
The Uncommitted Data Problem
The Inconsistent Data Problem
The Phantom Insert Problem
Concurrent Transactions
Locking*
Locking Levels
Shared and Exclusive Locks
Deadlocks*
Advanced Locking Techniques*
Summary
Database Structure
Creating a Database
The Data Definition Language
Creating a Database
Table Definitions
Creating a Table (CREATE TABLE)
Removing a Table (DROP TABLE)
Changing a Table Definition (ALTER TABLE)
Constraint Definitions
Assertions
Domains
Aliases and Synonyms (CREATE/DROP ALIAS)
Indexes (CREATE/DROP INDEX)
Managing Other Database Objects
Database Structure
Single-Database Architecture
Multi-Database Architecture
Multi-Location Architecture
Database Structure and the ANSI/ISO Standard
SQL2 Catalogs
SQL2 Schemas
Summary
Views
What Is a View?
How the DBMS Handles Views
Advantages of Views
Disadvantages of Views
Creating a View (CREATE VIEW)
Horizontal Views
Vertical Views
Row/Column Subset Views
Grouped Views
Joined Views
Updating a View
View Updates and the ANSI/ISO Standard
View Updates in Commercial SQL Products
Checking View Updates (CHECK OPTION)
Dropping a View (DROP VIEW)
Summary
SQL Security
SQL Security Concepts
User-Ids
Security Objects
Privileges
Views and SQL Security
Granting Privileges (GRANT)
Column Privileges
Passing Privileges (GRANT OPTION)
Revoking Privileges (REVOKE)
REVOKE and the GRANT OPTION
REVOKE and the ANSI/ISO Standard
Summary
The System Catalog
What Is the System Catalog?
The Catalog and Query Tools
The Catalog and the ANSI/ISO Standard
Catalog Contents
Table Information
Column Information
View Information
Remarks
Relationship Information
User Information
Privileges Information
The SQL2 Information Schema
Other Catalog Information
Summary
Programming with SQL
Embedded SQL
Programmatic SQL Techniques
DBMS Statement Processing
Embedded SQL Concepts
Developing an Embedded SQL Program
Running an Embedded SQL Program
Simple Embedded SQL Statements
Declaring Tables
Error Handling
Using Host Variables
Data Retrieval in Embedded SQL
Single-Row Queries
Multi-Row Queries
Cursor-Based Deletes and Updates
Cursors and Transaction Processing
Summary
Dynamic SQL*
Limitations of Static SQL
Dynamic SQL Concepts
Dynamic Statement Execution (EXECUTE IMMEDIATE)
Two-Step Dynamic Execution
The PREPARE Statement
The EXECUTE Statement
Dynamic Queries
The DESCRIBE Statement
The DECLARE CURSOR Statement
The Dynamic OPEN Statement
The Dynamic FETCH Statement
The Dynamic CLOSE Statement
Dynamic SQL Dialects
Dynamic SQL in SQL/DS
Dynamic SQL in Oracle*
Dynamic SQL and the SQL2 Standard
Basic Dynamic SQL2 Statements
SQL2 and the SQLDA
SQL2 and Dynamic SQL Queries
Summary
SQL APIs
API Concepts
The dblib API (SQL Server)
Basic SQL Server Techniques
SQL Server Queries
Positioned Updates
Dynamic Queries
ODBC and the SQL/CLI Standard
Call-Level Interface Standardization
CLI Structures
CLI Statement Processing
CLI Errors and Diagnostic Information
CLI Attributes
CLI Information Calls
The ODBC API
The Structure of ODBC
ODBC and DBMS Independence
ODBC Catalog Functions
Extended ODBC Capabilities
The Oracle Call Interface (OCI)
Legacy OCI
OCI and Oracle8
Summary
SQL Today and Tomorrow
Database Processing and Stored Procedures
Stored Procedure Concepts
A Basic Example
Using Stored Procedures
Creating a Stored Procedure
Calling a Stored Procedure
Stored Procedure Variables
Statement Blocks
Returning a Value
Returning Values via Parameters
Conditional Execution
Repeated Execution
Other Flow-of-Control Constructs
Cursor-Based Repetition
Handling Error Conditions
Advantages of Stored Procedures
Stored Procedure Performance
System-Defined Stored Procedures
External Stored Procedures
Triggers
Advantages and Disadvantages of Triggers
Triggers in Transact-SQL
Triggers in Informix SPL
Triggers in Oracle PL/SQL
Other Trigger Considerations
Stored Procedures and the SQL Standard
Core Capabilities
Creating a SQL Routine
Flow-of-Control Statements
Cursor Operations
Block Structure
Error Handling
Routine Name Overloading
Other Stored Procedure Considerations
Summary
SQL and Data Warehousing
Data Warehousing Concepts
Components of a Data Warehouse
The Evolution of Data Warehousing
Database Architecture for Warehousing
Fact Cubes
Star Schemas
Multi-Level Dimensions
SQL Extensions for Data Warehousing
Warehouse Performance
Load Performance
Query Performance
Summary
SQL Networking and Distributed Databases
The Challenge of Distributed Data Management
Distributing Data--Practical Approaches
Remote Database Access
Remote Data Transparency
Table Extracts
Table Replication
Updateable Replicas
Replication Tradeoffs
Typical Replication Architectures
Distributed Database Access
Remote Requests
Remote Transactions
Distributed Transactions
Distributed Requests
The Two-Phase Commit Protocol*
Network Applications and Database Architecture
Client/Server Applications and Database Architecture
Client/Server Applications with Stored Procedures
Enterprise Applications and Data Caching
High-Volume Internet Data Management
Summary
SQL and Objects
Object-Oriented Databases
Object-Oriented Database Characteristics
Pros and Cons of Object-Oriented Databases
Objects and the Database Market
Object-Relational Databases
Large Object Support
BLOBs in the Relational Model
Specialized BLOB Processing
Abstract (Structured) Data Types
Defining Abstract Data Types
Manipulating Abstract Data Types
Inheritance
Table Inheritance--Implementing Object Classes
Sets, Arrays, and Collections
Defining Collections
Querying Collection Data
Manipulating Collection Data
Collections and Stored Procedures
User-Defined Data Types
Methods and Stored Procedures
Summary
The Future of SQL
Database Market Trends
Enterprise Database Market Maturity
Market Diversity and Segmentation
Packaged Enterprise Applications
Hardware Performance Gains
Benchmark Wars
SQL Standardization
SQL in the Next Decade
Distributed Databases
Massive Data Warehousing
Ultra-High-Performance Databases
Internet and Network Services Integration
Embedded Databases
Object Integration
Appendices
The Sample Database
Database Vendor Profiles
A2i, Inc. (www.a2i.com)
Angara Database Systems (www.angara.com)
Arbor Software (www.hyperion.com)
Ardent Software (www.ardentsoftware.com)
Centura Software (www.centurasoft.com)
Cloudscape, Inc. (www.cloudscape.com)
Computer Associates (www.cai.com)
Computer Corporation of America (www.cca-int.com)
Empress Software (www.empress.com)
IBM Corporation (www.ibm.com)
Informix Software (www.informix.com)
Microsoft Corporation (www.microsoft.com)
Object Design (www.odi.com)
Objectivity (www.objectivity.com)
Oracle Corporation (www.oracle.com)
Persistence Software (www.persistence.com)
Pervasive Software (www.pervasive.com)
Quadbase Systems (www.quadbase.com)
Raima Corporation (www.raima.com)
Red Brick Systems (www.redbrick.com)
Rogue Wave Software (www.roguewave.com)
Sybase, Inc. (www.sybase.com)
Tache Group (www.tachegroup.com)
Tandem Computers (www.tandem.com)
TimesTen Performance Software (www.timesten.com)
Versant Corporation (www.versant.com)
Company and Product List
SQL Syntax Reference
Data Definition Statements
Basic Data Manipulation Statements
Transaction Processing Statements
Cursor-Based Statements
Query Expressions
Search Conditions
Expressions
Statement Elements
Simple Elements
SQL Call Level Interface
CLI Return Values
General Handle Management Routines
SQL Environment Management Routines
SQL Connection Management Routines
SQL Statement Management Routines
SQL Statement Execution Routines
Query Results Processing Routines
Query Results Description Routines
Query Results Descriptor Management Routines
Deferred Dynamic Parameter Processing Routines
Error, Status, and Diagnostic Routines
CLI Implementation Information Routines
CLI Parameter Value Codes
SQL Information Schema Standard
SCHEMATA View
TABLES View
COLUMNS View
VIEWS View
VIEW_TABLE_USAGE View
VIEW_COLUMN_USAGE View
TABLE_CONSTRAINTS View
REFERENTIAL_CONSTRAINTS View
CHECK_CONSTRAINTS View
KEY_COLUMN_USAGE View
ASSERTIONS View
CONSTRAINT_TABLE_USAGE View
CONSTRAINT_COLUMN_USAGE View
TABLE_PRIVILEGES View
COLUMN_PRIVILEGES View
USAGE_PRIVILEGES View
DOMAINS View
DOMAIN_CONSTRAINTS View
DOMAIN_COLUMN_USAGE View
CHARACTER_SETS View
COLLATIONS View
TRANSLATIONS View
SQL_LANGUAGES View
CD-ROM Installation Guide
Installing the SQL DBMS Software
Microsoft SQL Server 7
Hardware and Software Requirements
SQL Server Services User Accounts
SQL Server 7.0 Installation
Starting SQL Server 7.0
Stopping SQL Server 7.0
Uninstalling SQL Server 7.0
Oracle8
Products Included on the CD
Hardware and Software Requirements
Products Available for Installation
Oracle8 Personal Edition Installation
Starting Oracle8 Personal Edition
Stopping Oracle8 Personal Edition
Uninstalling Oracle8 Personal Edition
Informix
Hardware and Software Requirements
Informix User Accounts
Informix Personal Edition Installation
Starting Informix Personal Edition
Stopping Informix Personal Edition
Troubleshooting Installation Problems
Uninstalling Informix Personal Edition
Sybase
Hardware and Software Requirements
Sybase Adaptive Server Anywhere Installation
Starting Sybase Adaptive Server Anywhere
Stopping Sybase Adaptive Server Anywhere
Uninstalling Sybase Adaptive Server Anywhere
IBM DB2
Hardware and Software Requirements
DB2 User Accounts
DB2 Personal Edition Installation
Starting DB2 Personal Edition
Stopping DB2 Personal Edition
Verifying the Installation
Troubleshooting Installation Problems
Uninstalling DB2 Personal Edition
Index