SQL The Complete Reference, 3rd Edition | Complete Reference

SQL The Complete Reference, 3rd Edition

Authors: James Groff, Paul Weinberg and Andy Oppel

Published: August 2009

eISBN: 9780071592567 0071592563 | ISBN: 9780071592550

Contents

Acknowledgments

Introduction

Part I: An Overview of SQL

1 Introduction

The SQL Language

The Role of SQL

SQL Success Factors

2 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

3 SQL in Perspective

SQL and the Evolution of Database Management

A Brief History of SQL

SQL Standards

SQL and Networking

The Proliferation of SQL

Summary

4 Relational Databases

Early Data Models

The Relational Data Model

Codd's 12 Rules for Relational Databases*

Summary

Part II: Retrieving Data

5 SQL Basics

Statements

Names

Data Types

Constants

Expressions

Built-In Functions

Missing Data (NULL Values)

Summary

6 Simple Queries

The SELECT Statement

Query Results

Simple Queries

Selecting All Columns (SELECT *)

Duplicate Rows (DISTINCT)

Row Selection (WHERE Clause)

Search Conditions

The Comparison Test (=, <>, <, <=, >, >=)

Sorting Query Results (ORDER BY Clause)

Rules for Single-Table Query Processing

Summary

7 Multitable Queries (Joins)

A Two-Table Query Example

Simple Joins (Equi-Joins)

Non-Equi-Joins

SQL Considerations for Multitable Queries

Multitable Query Performance

The Structure of a Join

Outer Joins

Joins and the SQL Standard

Summary

8 Summary Queries

Column Functions

Grouped Queries (GROUP BY Clause)

Group Search Conditions (HAVING Clause)

Summary

9 Subqueries and Query Expressions

Using Subqueries

Subquery Search Conditions

Subqueries and Joins

Nested Subqueries

Correlated Subqueries*

Subqueries in the HAVING Clause*

Subquery Summary

Advanced Queries*

SQL Queries: A Final Summary

Part III: Updating Data

10 Database Updates

Adding Data to the Database

Deleting Data from the Database

Modifying Data in the Database

Summary

11 Data Integrity

What Is Data Integrity?

Required Data

Simple Validity Checking

Entity Integrity

Referential Integrity

Advanced Constraint Capabilities

Business Rules

Summary

12 Transaction Processing

What Is a Transaction?

The ANSI/ISO SQL Transaction Model

Transactions: Behind the Scenes*

Transactions and Multiuser Processing

Locking*

Versioning*

Summary

Part IV: Database Structure

13 Creating a Database

The Data Definition Language

Creating a Database

Table Definitions

Constraint Definitions

Aliases and Synonyms (CREATE/DROP ALIAS)

Indexes (CREATE/DROP INDEX)

Managing Other Database Objects

Database Structure

Database Structure and the ANSI/ISO Standard

Summary

14 View

Creating a View (CREATE VIEW)

Updating a View

Dropping a View (DROP VIEW)

Materialized Views*

Summary

15 SQL Security

SQL Security Concepts

Views and SQL Security

Granting Privileges (GRANT)

Revoking Privileges (REVOKE)

Role-Based Security

Summary

16 The System Catalog

What Is the System Catalog?

Table Information

Column Information

View Information

Remarks

Relationship Information

User Information

Privileges Information

The SQL Information Schema

Other Catalog Information

Summary

Part V: Programming with SQL

17 Embedded SQL

Programmatic SQL Techniques

Simple Embedded SQL Statements

Data Retrieval in Embedded SQL

Cursor-Based Deletes and Updates

Cursors and Transaction Processing

Summary

18 Dynamic SQL*

Limitations of Static SQL

Dynamic SQL Concepts

Dynamic Statement Execution (EXECUTE IMMEDIATE)

Two-Step Dynamic Execution

Dynamic Queries

Dynamic SQL Dialects

Dynamic SQL and the SQL Standard

Summary

19 SQL APIs

API Concepts

The dblib API (SQL Server)

ODBC and the SQL/CLI Standard

The ODBC API

The Oracle Call Interface (OCI)

Java Database Connectivity (JDBC)

Summary

Part VI: SQL Today and Tomorrow

20 Database Processing and Stored Procedural SQL

Procedural SQL Concepts

A Basic Example

Using Stored Procedures

Advantages of Stored Procedures

Stored Procedure Performance

System-Defined Stored Procedures

External Stored Procedures

Triggers

Stored Procedures, Functions, Triggers, and the SQL Standard

Summary

21 SQL and Data Warehousing

Data Warehousing Concepts

Database Architecture for Warehousing

Warehouse Performance

Summary

22 SQL and Application Servers

SQL and Web Sites: Early Implementations

Application Servers and Three-Tier Web Site Architectures

Database Access from Application Servers

Application Server Caching

Summary

23 SQL Networking and Distributed Databases

The Challenge of Distributed Data Management

Distributing Data: Practical Approaches

Distributed Database Access

The Two-Phase Commit Protocol*

Network Applications and Database Architecture

Summary

24 SQL and Objects

Object-Oriented Databases

Object-Relational Databases

Abstract (Structured) Data Types

Inheritance

Sets, Arrays, and Collections

User-Defined Data Types

Methods and Stored Procedures

Object Support in the SQL Standard

Summary

25 SQL and XML

What Is XML?

XML Basics

XML for Data

Using XML with Databases

XML and Metadata

XML and Queries

XML Databases

Summary

26 Specialty Databases

Very Low Latency and In-Memory Databases

In-Memory Database Implementations

Complex Event-Processing and Stream Databases

Embedded Databases

Mobile Databases

Summary

27 The Future of SQL

Database Market Trends

SQL in the Next Decade

Summary

Part VII: Appendixes

A: The Sample Database

B: DBMS Vendor Profiles

C: SQL Syntax Reference

Data Definition Statements

Access Control Statements

Basic Data Manipulation Statements

Transaction-Processing Statements

Cursor-Based Statements

Query Expressions

Search Conditions

Expressions

Statement Elements

Simple Elements

Index