SQL Bible, Second Edition

SQL Bible, Second Edition

by Alex Kriegel, Boris M. Trukhnov
Released April 2008
Publisher(s): Wiley
ISBN: 9780470229064

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Combining theory with everyday practicality, this definitive volume is packed with the up-to-date information, new features, and explanations you need to get the very most out of SQL and its latest standard. The book is unique in that every chapter highlights how the new SQL standard applies to the three major databases, Oracle 11g, IBM DB2 9.5, and Microsoft SQL Server 2008. The result is a comprehensive, useful, and real-world reference for all SQL users, from beginners to experienced developers.

Table of contents

  1. Copyright
  2. About the Authors
    1. Contributing authors
  3. Credits
  4. Acknowledgments
  5. Introduction
    1. Whom This Book Is For
    2. How This Book Is Organized
      1. Part I: SQL Basic Concepts and Principles
      2. Part II: Creating and Modifying Database Objects
      3. Part III: Data Manipulation and Transaction Control
      4. Part IV: Retrieving and Transferring Data
      5. Part V: Implementing Security Using the System Catalog
      6. Part VI: Beyond SQL: Procedural Programming and Database Access Mechanisms
      7. Appendixes
    3. Conventions Used in This Book
  6. I. SQL Basic Concepts and Principles
    1. 1. SQL and Relational Database Management Systems
      1. 1.1. Desirable Database Characteristics
        1. 1.1.1. Sufficient capacity
        2. 1.1.2. Adequate security and auditing
        3. 1.1.3. Multiuser environment
        4. 1.1.4. Effectiveness and searchability
        5. 1.1.5. Scalability
        6. 1.1.6. User friendliness
      2. 1.2. Selecting Your Database Software
        1. 1.2.1. Market share
        2. 1.2.2. Total cost of ownership
        3. 1.2.3. Support and persistence
      3. 1.3. Major DBMS Implementations
      4. 1.4. Real-Life Database Examples
        1. 1.4.1. Order management system database
        2. 1.4.2. Health care provider database
        3. 1.4.3. Video sharing and editing database
        4. 1.4.4. Scientific database
        5. 1.4.5. Nonprofit organization database
      5. 1.5. Database Legacy
        1. 1.5.1. Flat file databases
        2. 1.5.2. Hierarchical databases
        3. 1.5.3. Network databases
      6. 1.6. Relational Databases
        1. 1.6.1. Tables
        2. 1.6.2. Relationships
        3. 1.6.3. Primary key
        4. 1.6.4. Foreign key
        5. 1.6.5. Invasion of RDBMS
      7. 1.7. Other DBMS Models
      8. 1.8. Brief History of SQL and SQL Standards
        1. 1.8.1. Humble beginnings: RDBMS and SQL evolution
        2. 1.8.2. A brief history of SQL standards
          1. 1.8.2.1. SQL-86/87 (SQL1)
          2. 1.8.2.2. SQL-89 (SQL1.1)
          3. 1.8.2.3. SQL-92 (SQL2)
          4. 1.8.2.4. SQL:1999 (SQL3)
          5. 1.8.2.5. SQL:2003
          6. 1.8.2.6. SQL:2008
      9. 1.9. Summary
    2. 2. Fundamental SQL Concepts and Principles
      1. 2.1. Promises and Deliverables
      2. 2.2. SQL: The First Look
        1. 2.2.1. Database example
        2. 2.2.2. Getting the data in and out
        3. 2.2.3. Slice and dice: Same data, different angle
        4. 2.2.4. Aggregation
        5. 2.2.5. Data security
          1. 2.2.5.1. Privileges
          2. 2.2.5.2. Views
        6. 2.2.6. Accessing data from a client application
        7. 2.2.7. New developments
      3. 2.3. Any Platform, Any Time
      4. 2.4. Summary
    3. 3. SQL Data Types
      1. 3.1. No Strings Attached
        1. 3.1.1. Character strings
          1. 3.1.1.1. Fixed-length character strings
          2. 3.1.1.2. Character strings of varying length
          3. 3.1.1.3. National character strings
          4. 3.1.1.4. SQL:2003
          5. 3.1.1.5. Oracle 11g
          6. 3.1.1.6. DB2 9.5
          7. 3.1.1.7. Microsoft SQL Server 2008
          8. 3.1.1.8. Character string literals
        2. 3.1.2. Binary strings
          1. 3.1.2.1. SQL:2003
          2. 3.1.2.2. Oracle 11g
          3. 3.1.2.3. DB2 9.5
          4. 3.1.2.4. Microsoft SQL Server 2008
          5. 3.1.2.5. Binary string literals
      2. 3.2. In Numbers Strength
        1. 3.2.1. Exact numbers
          1. 3.2.1.1. SQL:2003
          2. 3.2.1.2. Oracle 11g
          3. 3.2.1.3. DB2 9.5
          4. 3.2.1.4. Microsoft SQL Server 2008
          5. 3.2.1.5. Literals for exact numbers
        2. 3.2.2. Approximate numbers
          1. 3.2.2.1. SQL:2003
          2. 3.2.2.2. Oracle 11g
          3. 3.2.2.3. DB2 9.5
          4. 3.2.2.4. Microsoft SQL Server 2008
          5. 3.2.2.5. Literals for approximate numbers
      3. 3.3. Once Upon a Time: Date and Time Data Types
        1. 3.3.1. Introduction to complex data types
        2. 3.3.2. Date and time implementations
          1. 3.3.2.1. SQL:2003
          2. 3.3.2.2. Oracle 11g
          3. 3.3.2.3. DB2 9.5
          4. 3.3.2.4. Microsoft SQL Server 2008
          5. 3.3.2.5. Date and time literals
          6. 3.3.2.6. Oracle 11g
          7. 3.3.2.7. DB2 9.5
          8. 3.3.2.8. Microsoft SQL Server
      4. 3.4. XML Data Type
        1. 3.4.1. XML data type implementations
          1. 3.4.1.1. SQL:2003 and SQL/XML:2006
          2. 3.4.1.2. Oracle 11g
          3. 3.4.1.3. DB2 9.5
          4. 3.4.1.4. Microsoft SQL Server 2008
      5. 3.5. Constructed and User-Defined Data Types
        1. 3.5.1. SQL:2003
          1. 3.5.1.1. Object-oriented data types
            1. 3.5.1.1.1. Reference type
            2. 3.5.1.1.2. Row type
          2. 3.5.1.2. Collections
            1. 3.5.1.2.1. ARRAY
            2. 3.5.1.2.2. MULTISET
        2. 3.5.2. Oracle 11g
          1. 3.5.2.1. Object-oriented data types
          2. 3.5.2.2. Collections
            1. 3.5.2.2.1. Nested tables
            2. 3.5.2.2.2. Varying arrays
        3. 3.5.3. DB2 9.5
          1. 3.5.3.1. Structured types
          2. 3.5.3.2. Reference types
          3. 3.5.3.3. Distinct types
        4. 3.5.4. Microsoft SQL Server 2008
          1. 3.5.4.1. Table type
          2. 3.5.4.2. Alias type
      6. 3.6. Other Data Types
        1. 3.6.1. BOOLEAN
        2. 3.6.2. ROWID
        3. 3.6.3. UROWID
        4. 3.6.4. BFILE
        5. 3.6.5. DATALINK
        6. 3.6.6. BIT
        7. 3.6.7. TIMESTAMP
        8. 3.6.8. SQL_VARIANT
      7. 3.7. NULL
      8. 3.8. Summary
  7. II. Creating and Modifying Database Objects
    1. 4. Creating RDBMS Objects
      1. 4.1. Tables
        1. 4.1.1. CREATE TABLE Statement
          1. 4.1.1.1. SQL:2003 syntax
          2. 4.1.1.2. Oracle 11 g syntax
          3. 4.1.1.3. DB2 9.5 syntax
          4. 4.1.1.4. Microsoft SQL 2008 syntax
          5. 4.1.1.5. Permanent and temporary tables
            1. 4.1.1.5.1. Temporary tables in SQL:2003
            2. 4.1.1.5.2. Temporary tables in Oracle 11 g
            3. 4.1.1.5.3. Temporary tables in DB2 9.5
            4. 4.1.1.5.4. Temporary tables in Microsoft SQL Server 2008
          6. 4.1.1.6. Column definitions
            1. 4.1.1.6.1. SQL:2003
          7. 4.1.1.7. Column constraints
          8. 4.1.1.8. Column default values
          9. 4.1.1.9. Column collating sequence
          10. 4.1.1.10. Table constraints
            1. 4.1.1.10.1. Referential integrity constraints
            2. 4.1.1.10.2. Deferrable constraints
          11. 4.1.1.11. ON COMMIT clause
          12. 4.1.1.12. Physical properties clause
            1. 4.1.1.12.1. Oracle 11 g
            2. 4.1.1.12.2. DB2 9.5
            3. 4.1.1.12.3. Microsoft SQL Server 2008
          13. 4.1.1.13. Identity clause
            1. 4.1.1.13.1. SQL:2003
            2. 4.1.1.13.2. Oracle 11 g
            3. 4.1.1.13.3. DB2 9.5
            4. 4.1.1.13.4. Microsoft SQL Server 2008
          14. 4.1.1.14. Creating a new table as a copy of another table
            1. 4.1.1.14.1. SQL:2003
            2. 4.1.1.14.2. Oracle 11 g
            3. 4.1.1.14.3. DB2 9.5
            4. 4.1.1.14.4. Microsoft SQL Server 2008
      2. 4.2. Indexes
        1. 4.2.1. CREATE INDEX statement
          1. 4.2.1.1. Oracle 11 g
            1. 4.2.1.1.1. Function-based indexes
            2. 4.2.1.1.2. Bitmap indexes
            3. 4.2.1.1.3. Physical storage clause
          2. 4.2.1.2. DB2 9.5
          3. 4.2.1.3. Microsoft SQL Server 2008
      3. 4.3. Views
        1. 4.3.1. CREATE VIEW statement
          1. 4.3.1.1. SQL:2003
            1. 4.3.1.1.1. Column names
            2. 4.3.1.1.2. SELECT statement and updatable views
            3. 4.3.1.1.3. View constraints
          2. 4.3.1.2. Oracle 11 g
          3. 4.3.1.3. DB2 9.5
          4. 4.3.1.4. Microsoft SQL Server 2008
        2. 4.3.2. Creating complex views
          1. 4.3.2.1. Join view with GROUP BY clause and aggregate function
          2. 4.3.2.2. View based on another view example
          3. 4.3.2.3. View with UNION example
          4. 4.3.2.4. View with subquery
      4. 4.4. Aliases and Synonyms
        1. 4.4.1. SQL:2003
        2. 4.4.2. Oracle 11 g CREATE SYNONYM statement
          1. 4.4.2.1. Public versus private synonyms
          2. 4.4.2.2. Types of objects for which you can create synonyms
          3. 4.4.2.3. Creating synonyms for remote database objects
          4. 4.4.2.4. CREATE SYNONYM examples
        3. 4.4.3. DB2 9.5 CREATE ALIAS statement
        4. 4.4.4. Microsoft SQL Server 2008 CREATE SYNONYM statement
      5. 4.5. Schemas
        1. 4.5.1. CREATE SCHEMA statement
          1. 4.5.1.1. SQL:2003
          2. 4.5.1.2. Oracle 11 g
          3. 4.5.1.3. DB2 9.5
          4. 4.5.1.4. Microsoft SQL Server 2008
      6. 4.6. Sequences
        1. 4.6.1. External sequence generators in SQL:2003
        2. 4.6.2. Sequences in Oracle 11 g
          1. 4.6.2.1. Creating ascending and descending sequences
          2. 4.6.2.2. Creating cycling sequences
          3. 4.6.2.3. Caching sequence values
          4. 4.6.2.4. Guaranteeing the order of sequence values
          5. 4.6.2.5. Accessing sequences in Oracle
        3. 4.6.3. DB2 9.5
          1. 4.6.3.1. Accessing sequences in DB2
      7. 4.7. Other SQL:2003 and Implementation-Specific Objects
        1. 4.7.1. Domains (SQL:2003)
        2. 4.7.2. Character sets (SQL:2003)
        3. 4.7.3. Collations (SQL:2003)
        4. 4.7.4. Tablespaces and filegroups
          1. 4.7.4.1. Oracle 11 g
          2. 4.7.4.2. DB2 9.5
          3. 4.7.4.3. Microsoft SQL Server 2008
        5. 4.7.5. Materialized views (Oracle 11 g)
          1. 4.7.5.1. Materialized view refreshed on demand
          2. 4.7.5.2. Materialized view refreshed periodically with no user interaction
        6. 4.7.6. Database links (Oracle 11 g)
          1. 4.7.6.1. CREATE DATABASE LINK statement
        7. 4.7.7. Directories (Oracle 11 g)
      8. 4.8. CREATE Statement Cross-Reference
      9. 4.9. Summary
    2. 5. Altering and Destroying RDBMS Objects
      1. 5.1. Tables
        1. 5.1.1. ALTER TABLE statement
          1. 5.1.1.1. SQL:2003
          2. 5.1.1.2. Oracle 11 g
            1. 5.1.1.2.1. Changing physical attributes
            2. 5.1.1.2.2. Adding columns
            3. 5.1.1.2.3. Modifying existing columns
            4. 5.1.1.2.4. Removing table columns
            5. 5.1.1.2.5. Modifying constraints
            6. 5.1.1.2.6. Creating new constraints
            7. 5.1.1.2.7. Removing constraints
            8. 5.1.1.2.8. Disabling and enabling constraints
            9. 5.1.1.2.9. Renaming a table
          3. 5.1.1.3. DB2 9.5
            1. 5.1.1.3.1. Adding new columns to a table
            2. 5.1.1.3.2. Modifying existing columns
            3. 5.1.1.3.3. Modifying identity column options
            4. 5.1.1.3.4. Creating new constraints on an existing table
            5. 5.1.1.3.5. Removing constraints
            6. 5.1.1.3.6. Altering summary table options
          4. 5.1.1.4. Microsoft SQL Server 2008
            1. 5.1.1.4.1. Adding new columns to a table
            2. 5.1.1.4.2. Modifying existing columns
            3. 5.1.1.4.3. Removing table columns
            4. 5.1.1.4.4. Creating and removing constraints
            5. 5.1.1.4.5. Disabling and enabling constraints
        2. 5.1.2. DROP TABLE statement
          1. 5.1.2.1. SQL:2003
          2. 5.1.2.2. Oracle 11 g
          3. 5.1.2.3. DB2 9.5
          4. 5.1.2.4. Microsoft SQL Server 2008
      2. 5.2. Indexes
        1. 5.2.1. ALTER INDEX statement
          1. 5.2.1.1. Oracle 11 g
          2. 5.2.1.2. Renaming indexes
          3. 5.2.1.3. Rebuilding indexes into a different tablespace
          4. 5.2.1.4. DB2 9.5
          5. 5.2.1.5. Microsoft SQL Server 2008
        2. 5.2.2. DROP INDEX statement
          1. 5.2.2.1. Oracle 11 g
          2. 5.2.2.2. DB2 9.5
          3. 5.2.2.3. Microsoft SQL Server 2008
      3. 5.3. Views
        1. 5.3.1. ALTER VIEW statement
          1. 5.3.1.1. Oracle 11 g
          2. 5.3.1.2. DB2 9.5
          3. 5.3.1.3. Microsoft SQL Server 2008
        2. 5.3.2. DROP VIEW statement
          1. 5.3.2.1. SQL:2003
          2. 5.3.2.2. Oracle 11 g
          3. 5.3.2.3. DB2 9.5
          4. 5.3.2.4. Microsoft SQL Server 2008
      4. 5.4. Aliases and Synonyms
        1. 5.4.1. Oracle 11 g
        2. 5.4.2. DB2 9.5
        3. 5.4.3. Microsoft SQL Server 2008
      5. 5.5. Schemas
        1. 5.5.1. SQL:2003
        2. 5.5.2. DB2 9.5
        3. 5.5.3. Microsoft SQL Server 2008
      6. 5.6. Sequences
        1. 5.6.1. ALTER SEQUENCE statement
          1. 5.6.1.1. SQL:2003
          2. 5.6.1.2. Oracle 11 g
          3. 5.6.1.3. DB2 9.5
        2. 5.6.2. DROP SEQUENCE statement
          1. 5.6.2.1. SQL:2003
          2. 5.6.2.2. Oracle 11 g
          3. 5.6.2.3. DB2 9.5
      7. 5.7. Other Implementation-Specific Objects
        1. 5.7.1. Domains (SQL:2003)
        2. 5.7.2. Character sets (SQL:2003)
        3. 5.7.3. Collations (SQL:2003)
        4. 5.7.4. Tablespaces
          1. 5.7.4.1. ALTER TABLESPACE statement
            1. 5.7.4.1.1. Oracle 11 g
            2. 5.7.4.1.2. DB2 9.5
          2. 5.7.4.2. DROP TABLESPACE statement
            1. 5.7.4.2.1. Oracle 11 g
            2. 5.7.4.2.2. DB2 9.5
        5. 5.7.5. Materialized views (Oracle 11 g)
        6. 5.7.6. Database Links (Oracle 11 g)
        7. 5.7.7. Directories (Oracle 11 g)
      8. 5.8. ALTER and DROP Statements Cross-Reference
      9. 5.9. Summary
  8. III. Data Manipulation and Transaction Control
    1. 6. Data Manipulation Language (DML)
      1. 6.1. INSERT: Populating Tables with Data
        1. 6.1.1. Common INSERT statement clauses
          1. 6.1.1.1. Inserting values for specified columns
          2. 6.1.1.2. Inserting values for all columns
          3. 6.1.1.3. Inserting NULL and default values explicitly
          4. 6.1.1.4. Inserting values selected from other tables
          5. 6.1.1.5. INSERT statement and integrity constraints
        2. 6.1.2. INSERT statement vendor-related specifics
          1. 6.1.2.1. SQL:2003
          2. 6.1.2.2. Oracle 11g
          3. 6.1.2.3. DB2 9.5
          4. 6.1.2.4. Microsoft SQL Server 2008
      2. 6.2. UPDATE: Modifying Table Data
        1. 6.2.1. Common UPDATE statement clauses
          1. 6.2.1.1. Updating a single column of a single row
          2. 6.2.1.2. Updating multiple columns
          3. 6.2.1.3. Updating a column in all rows
          4. 6.2.1.4. Updating columns using a single-row subquery
            1. 6.2.1.4.1. Deriving the assignment value from another value
            2. 6.2.1.4.2. Update with correlated subquery
          5. 6.2.1.5. UPDATE statement and integrity constraints
        2. 6.2.2. Vendor-specific UPDATE statement details
          1. 6.2.2.1. SQL:2003
          2. 6.2.2.2. Oracle 11g
          3. 6.2.2.3. DB2 9.5
          4. 6.2.2.4. Microsoft SQL Server 2008
      3. 6.3. DELETE: Removing Data from Tables
        1. 6.3.1. Common DELETE statement clauses
          1. 6.3.1.1. DELETE statement and integrity constraints
          2. 6.3.1.2. Using subqueries in a DELETE statement WHERE clause
        2. 6.3.2. Vendor-specific DELETE statement clauses
          1. 6.3.2.1. Oracle 11g
          2. 6.3.2.2. Microsoft SQL Server 2008
      4. 6.4. MERGE: Combining INSERT, UPDATE, and DELETE in One Statement
        1. 6.4.1. Common MERGE statement clauses
        2. 6.4.2. Vendor-specific MERGE statement clauses
          1. 6.4.2.1. Oracle 11g
          2. 6.4.2.2. DB2 9.5
          3. 6.4.2.3. Microsoft SQL Server 2008
      5. 6.5. TRUNCATE Statement
        1. 6.5.1. Differences between Oracle and Microsoft SQL Server TRUNCATE statements
      6. 6.6. Summary
    2. 7. Sessions, Transactions, and Locks
      1. 7.1. Sessions
      2. 7.2. Transactions
        1. 7.2.1. What is a transaction?
        2. 7.2.2. Transactions COMMIT and ROLLBACK
        3. 7.2.3. Transaction isolation levels
      3. 7.3. Understanding Locks
        1. 7.3.1. Locking modes
        2. 7.3.2. Dealing with deadlocks
      4. 7.4. Summary
  9. IV. Retrieving and Transferring Data
    1. 8. Understanding the SELECT Statement
      1. 8.1. Single Table SELECT Statement Syntax
      2. 8.2. SELECT Clause: What Do You Select?
        1. 8.2.1. Single-column select
        2. 8.2.2. Multicolumn SELECT
          1. 8.2.2.1. Selecting several columns
          2. 8.2.2.2. Selecting all columns
          3. 8.2.2.3. Selecting all columns plus an extra column
          4. 8.2.2.4. Selecting distinct values
        3. 8.2.3. Using literals, functions, and calculated columns
          1. 8.2.3.1. Dummy tables
            1. 8.2.3.1.1. SQL:2003
            2. 8.2.3.1.2. Oracle 11g
            3. 8.2.3.1.3. DB2 9.5
            4. 8.2.3.1.4. Microsoft SQL Server 2008
          2. 8.2.3.2. Column aliases
          3. 8.2.3.3. Literals, functions, and expressions
        4. 8.2.4. Using subqueries in a SELECT clause
      3. 8.3. FROM Clause: Select from What?
        1. 8.3.1. Selecting from tables and views
        2. 8.3.2. Using aliases in a FROM clause
        3. 8.3.3. Using subqueries in a FROM clause (inline views)
      4. 8.4. WHERE Clause: Setting Horizontal Limits
        1. 8.4.1. Using comparison operators
        2. 8.4.2. Compound operators: Using AND and OR
        3. 8.4.3. Using the BETWEEN operator
        4. 8.4.4. Using the IN operator: Set membership test
        5. 8.4.5. The NOT operator
        6. 8.4.6. Using the IS NULL operator: Special test for NULLS
        7. 8.4.7. Using subqueries in a WHERE clause
          1. 8.4.7.1. Nested subqueries
      5. 8.5. GROUP BY and HAVING Clauses: Summarizing Results
      6. 8.6. ORDER BY Clause: Sorting Query Output
      7. 8.7. Combining the Results of Multiple Queries
        1. 8.7.1. UNION
        2. 8.7.2. INTERSECT
        3. 8.7.3. EXCEPT (MINUS)
      8. 8.8. SQL Analytic Functions and Top N Queries
        1. 8.8.1. Analytic functions and the SQL:2003 standard
        2. 8.8.2. Designing top N queries
        3. 8.8.3. Limit N queries
      9. 8.9. Summary
    2. 9. Multitable Queries
      1. 9.1. Inner Joins
        1. 9.1.1. Two syntaxes for inner joins
          1. 9.1.1.1. Inner join options
          2. 9.1.1.2. ON and USING clauses
          3. 9.1.1.3. Old syntax
        2. 9.1.2. Equijoin
          1. 9.1.2.1. SQL:2003 standard syntax
          2. 9.1.2.2. Natural join
          3. 9.1.2.3. Old syntax
        3. 9.1.3. Nonequijoin
        4. 9.1.4. Self-join
          1. 9.1.4.1. SQL:2003 standard syntax
          2. 9.1.4.2. Old syntax
        5. 9.1.5. Cross join (Cartesian product)
          1. 9.1.5.1. SQL:2003 standard syntax
          2. 9.1.5.2. Old syntax
        6. 9.1.6. Joining more than two tables
          1. 9.1.6.1. Number of joins
          2. 9.1.6.2. SQL:2003 standard syntax
          3. 9.1.6.3. Old syntax
      2. 9.2. Outer Joins: Joining Tables on Columns Containing NULL Values
        1. 9.2.1. Two syntaxes for outer joins
          1. 9.2.1.1. SQL:2003
          2. 9.2.1.2. Old syntax
            1. 9.2.1.2.1. Oracle 11 g
            2. 9.2.1.2.2. Microsoft SQL Server 2008
        2. 9.2.2. Left outer join
          1. 9.2.2.1. SQL:2003 standard syntax
          2. 9.2.2.2. Old syntax
            1. 9.2.2.2.1. Oracle 11 g
            2. 9.2.2.2.2. DB2 9.5
            3. 9.2.2.2.3. Microsoft SQL Server 2008
        3. 9.2.3. Right outer join
          1. 9.2.3.1. SQL:2003 standard syntax
          2. 9.2.3.2. Old syntax
            1. 9.2.3.2.1. Oracle 11 g
        4. 9.2.4. Full outer join
        5. 9.2.5. Union join
      3. 9.3. Joins Involving Inline Views
      4. 9.4. Multitable Joins with Correlated Queries
      5. 9.5. Improving Efficiency of Multitable Queries
      6. 9.6. Summary
    3. 10. SQL Functions
      1. 10.1.
        1. 10.1.1. Numeric functions
          1. 10.1.1.1. CEIL
          2. 10.1.1.2. ROUND
          3. 10.1.1.3. TRUNC
          4. 10.1.1.4. RAND
          5. 10.1.1.5. SIGN
        2. 10.1.2. String functions
          1. 10.1.2.1. CONCAT
          2. 10.1.2.2. CHARINDEX, INSTR, LOCATE, and POSSTR
          3. 10.1.2.3. SUBSTR and SUBSTRING
          4. 10.1.2.4. LENGTH
          5. 10.1.2.5. LOWER and UPPER
          6. 10.1.2.6. TO_CHAR, CHAR, and STR
          7. 10.1.2.7. REPLACE
          8. 10.1.2.8. REPLICATE and REPEAT
          9. 10.1.2.9. TRANSLATE
          10. 10.1.2.10. TRIM, LTRIM, and RTRIM
        3. 10.1.3. Date and time functions
          1. 10.1.3.1. GETDATE, SYSDATE, and CURRENT DATE
            1. 10.1.3.1.1. Time zone functions
          2. 10.1.3.2. ADD_MONTHS, DATEADD, and date arithmetic
          3. 10.1.3.3. EXTRACT and DATEPART
          4. 10.1.3.4. DAYNAME, MONTHNAME, and DATENAME
          5. 10.1.3.5. MONTHS_BETWEEN and DATEDIFF
        4. 10.1.4. Aggregate functions
          1. 10.1.4.1. SUM
          2. 10.1.4.2. COUNT
          3. 10.1.4.3. AVG
          4. 10.1.4.4. MIN and MAX
        5. 10.1.5. Conversion functions
          1. 10.1.5.1. Oracle 11 g CONVERT, TRANSLATE...USING, and UNISTR
            1. 10.1.5.1.1. Data type–specific conversion functions
        6. 10.1.6. System Functions
        7. 10.1.7. Miscellaneous functions
          1. 10.1.7.1. DECODE and CASE
          2. 10.1.7.2. COALESCE and NULLIF
          3. 10.1.7.3. NVL, NVL2, and ISNULL
        8. 10.1.8. User-defined functions
      2. 10.2. Summary
    4. 11. SQL Operators
      1. 11.1. Arithmetic and String Concatenation Operators
      2. 11.2. Logical Operators
        1. 11.2.1. ALL
        2. 11.2.2. ANY | SOME
        3. 11.2.3. BETWEEN Inexpression> AND <expression>
        4. 11.2.4. IN
        5. 11.2.5. EXISTS
        6. 11.2.6. LIKE
        7. 11.2.7. AND
        8. 11.2.8. NOT
        9. 11.2.9. OR
      3. 11.3. Operator Precedence
      4. 11.4. Assignment Operator
      5. 11.5. Comparison Operators
      6. 11.6. Bitwise Operators
      7. 11.7. Summary
  10. V. Implementing Security Using the System Catalog
    1. 12. SQL and RDBMS Security
      1. 12.1. Basic security mechanisms
        1. 12.1.1. Identification and authentication
        2. 12.1.2. Authorization and access control
        3. 12.1.3. Encryption
        4. 12.1.4. Integrity and consistency
        5. 12.1.5. Auditing
      2. 12.2. Defining a Database User
      3. 12.3. Managing Security with Privileges
        1. 12.3.1. GRANT statement
          1. 12.3.1.1. Granting System-Level Privileges
          2. 12.3.1.2. Granting Object-Level Privileges
        2. 12.3.2. REVOKE privileges
          1. 12.3.2.1. Revoking System-Level Privileges
          2. 12.3.2.2. Revoking Object-Level Privileges
      4. 12.4. Managing Security with Roles
      5. 12.5. Using Views for Security
      6. 12.6. Using Constraints for Security
      7. 12.7. Using Stored Procedures and Triggers for Security
        1. 12.7.1. Data encryption
      8. 12.8. Database Auditing
      9. 12.9. Security Standards
        1. 12.9.1. International security standards
        2. 12.9.2. Regulatory compliance
      10. 12.10. Summary
    2. 13. The System Catalog and INFORMATION_SCHEMA
      1. 13.1. SQL System Catalogs
      2. 13.2. Oracle 11 g Data Dictionary
        1. 13.2.1. Oracle data dictionary structure
        2. 13.2.2. Oracle data dictionary and SQL:2007 standards
        3. 13.2.3. One level deeper: Data about metadata
      3. 13.3. IBM DB2 9.5 System Catalogs
        1. 13.3.1. The INFORMATION_SCHEMA objects in DB2
        2. 13.3.2. Obtaining information about INFORMATION_SCHEMA objects
      4. 13.4. Microsoft SQL Server 2008 System Catalog
        1. 13.4.1. MS SQL Server 2008 INFORMATION_SCHEMA Views
        2. 13.4.2. Microsoft SQL Server system stored procedures
          1. 13.4.2.1. sp_tables
          2. 13.4.2.2. sp_columns
          3. 13.4.2.3. sp_server_info
          4. 13.4.2.4. sp_databases
        3. 13.4.3. Microsoft SQL Server 2008 system functions
          1. 13.4.3.1. Configuration functions
          2. 13.4.3.2. Metadata functions
          3. 13.4.3.3. System functions
      5. 13.5. Summary
  11. VI. Beyond SQL: Procedural Programming and Database Access Mechanisms
    1. 14. Stored Procedures, Triggers, and User-Defined Functions
      1. 14.1. Procedural Extension Uses and Benefits
        1. 14.1.1. Performance and network traffic
        2. 14.1.2. Database security
        3. 14.1.3. Code reusability
      2. 14.2. Key Elements of a SQL Procedural Language
        1. 14.2.1. Variables and assignment
          1. 14.2.1.1. PL/SQL (Oracle)
          2. 14.2.1.2. IBM DB2 9.5
            1. 14.2.1.2.1. Transact-SQL (Microsoft SQL Server)
        2. 14.2.2. Modularity, subprograms, and block structure
        3. 14.2.3. Passing parameters
          1. 14.2.3.1. PL/SQL
            1. 14.2.3.1.1. Transact-SQL
          2. 14.2.3.2. IBM DB2 SQL PL
      3. 14.3. Oracle 11 g
      4. 14.4. DB2 9.5
      5. 14.5. Microsoft SQL Server 2008
        1. 14.5.1. Conditional execution
          1. 14.5.1.1. PL/SQL
          2. 14.5.1.2. IBM DB2 9.5
            1. 14.5.1.2.1. Transact-SQL
        2. 14.5.2. Repeated execution
        3. 14.5.3. Cursors
          1. 14.5.3.1. DECLARE
          2. 14.5.3.2. OPEN
          3. 14.5.3.3. FETCH
          4. 14.5.3.4. CLOSE
            1. 14.5.3.4.1. Cursor examples
        4. 14.5.4. Error handling
          1. 14.5.4.1. PL/SQL
          2. 14.5.4.2. IBM DB2 9.5
            1. 14.5.4.2.1. Transact-SQL
      6. 14.6. Stored Procedures
        1. 14.6.1. CREATE PROCEDURE syntax
        2. 14.6.2. Creating a simple stored procedure
          1. 14.6.2.1. IBM DB2 9.5
            1. 14.6.2.1.1. Microsoft SQL Server 2008
        3. 14.6.3. Removing a stored procedure
      7. 14.7. User-Defined Functions
        1. 14.7.1. CREATE FUNCTION syntax
        2. 14.7.2. Creating a simple function
          1. 14.7.2.1. PL/SQL
          2. 14.7.2.2. IBM DB2 SQL PL
            1. 14.7.2.2.1. Transact-SQL
        3. 14.7.3. Removing a user-defined function
      8. 14.8. Triggers
        1. 14.8.1. CREATE TRIGGER syntax
          1. 14.8.1.1. SQL:2003
          2. 14.8.1.2. PL/SQL
          3. 14.8.1.3. IBM DB2 SQL PL
            1. 14.8.1.3.1. Transact-SQL
        2. 14.8.2. Removing a trigger
      9. 14.9. .NET Stored Procedures and Functions
      10. 14.10. Summary
    2. 15. SQL and XML
      1. 15.1.
        1. 15.1.1. Introduction
        2. 15.1.2. The Structure of XML
          1. 15.1.2.1. Formatted XML
          2. 15.1.2.2. DTD and Schema
            1. 15.1.2.2.1. Namespaces
        3. 15.1.3. XML as a data source
          1. 15.1.3.1. Accessing XML documents in an application
          2. 15.1.3.2. XPath
          3. 15.1.3.3. XQuery
        4. 15.1.4. Encoding XML
          1. 15.1.4.1. Entities
          2. 15.1.4.2. CDATA
        5. 15.1.5. Presenting XML documents
          1. 15.1.5.1. XSL and XSLT
        6. 15.1.6. XML and RDBMS
        7. 15.1.7. Oracle 11 g XML DB
        8. 15.1.8. IBM DB 9.5 pureXML
        9. 15.1.9. Microsoft SQL Server 2008
          1. 15.1.9.1. SQL Server 2000
          2. 15.1.9.2. XML DML
      2. 15.2. Summary
    3. 16. SQL and Procedural Programming
      1. 16.1. SQL Statement Processing Steps
      2. 16.2. Embedded and Dynamic SQL
        1. 16.2.1. Embedded SQL
          1. 16.2.1.1. Embedded SQL basic elements
            1. 16.2.1.1.1. Hoast variables
            2. 16.2.1.1.2. Database connection
            3. 16.2.1.1.3. Data retrieval and SELECT INTO statement
            4. 16.2.1.1.4. Handling errors
          2. 16.2.1.2. Common embedded SQL statements
          3. 16.2.1.3. A typical embedded SQL program flow
        2. 16.2.2. Dynamic SQL techniques
          1. 16.2.2.1. Two varieties of dynamic SQL
          2. 16.2.2.2. Dynamic SQL and SQL:2003 standards
          3. 16.2.2.3. Dynamic SQL basic elements
            1. 16.2.2.3.1. Dynamic DML and DDL
            2. 16.2.2.3.2. Dynamic queries
        3. 16.2.3. The future of Embedded and Dynamic SQL
      3. 16.3. SQL/CLI Standard
      4. 16.4. Open Database Connectivity and Object Linking and Embedding, Database
        1. 16.4.1. ODBC
        2. 16.4.2. OLEDB
      5. 16.5. SQL and Java
        1. 16.5.1. Java Database Connectivity (JDBC)
          1. 16.5.1.1. JDBC-ODBC drivers (Type 1)
          2. 16.5.1.2. Native API Partly Java technology-enabled drivers (Type 2)
          3. 16.5.1.3. Net-Protocol Fully Java technology-enabled drivers (Type 3)
          4. 16.5.1.4. Native-Protocol drivers (Type 4)
        2. 16.5.2. SQLJ
      6. 16.6. Oracle API options
        1. 16.6.1. Oracle Call Interface
        2. 16.6.2. Oracle Objects for OLE
        3. 16.6.3. Oracle Data Provider for .NET
      7. 16.7. IBM DB2 Call-Level Interface
      8. 16.8. Microsoft Data Access Interfaces
        1. 16.8.1. ActiveX Data Objects
        2. 16.8.2. ADO.NET
      9. 16.9. Summary
    4. 17. The Future of SQL
      1. 17.1. OLAP and Business Intelligence
        1. 17.1.1. Oracle 11 g
        2. 17.1.2. IBM DB2 9.5
        3. 17.1.3. Microsoft SQL Server 2008
      2. 17.2. LINQ to SQL
      3. 17.3. Objects
        1. 17.3.1. OOP Paradigm
          1. 17.3.1.1. Objects and classes
          2. 17.3.1.2. Encapsulation
          3. 17.3.1.3. Inheritance
          4. 17.3.1.4. Polymorphism
          5. 17.3.1.5. Identity
        2. 17.3.2. Object Language Bindings (SQL/OLB) in SQL:2003 Standard
        3. 17.3.3. Oracle 11 g support
        4. 17.3.4. IBM DB2 9.5 support
        5. 17.3.5. Microsoft SQL Server 2008
        6. 17.3.6. Abstract data types
        7. 17.3.7. Object-oriented databases
      4. 17.4. Summary
    5. A. Accompanying Website
    6. B. The ACME Sample Database
      1. B.1. General Information and Business Rules
      2. B.2. Naming Conventions
      3. B.3. Relationships between Tables
      4. B.4. Column Constraints and Defaults
      5. B.5. Indexes
      6. B.6. Views
      7. B.7. SQL Scripts to Create ACME Database Objects
    7. C. Basics of Relational Database Design
      1. C.1. Identifying Entities and Attributes
      2. C.2. Normalization
        1. C.2.1. First normal form
        2. C.2.2. Second normal form
        3. C.2.3. Third normal form
      3. C.3. Specifying Constraints
      4. C.4. OLTP versus OLAP Designs
        1. C.4.1. Data warehouses and data marts
        2. C.4.2. Star and snowflake schemas
      5. C.5. Pitfalls of Relational Database Design
    8. D. Installing RDBMS Software
      1. D.1. Installing Oracle 11g
        1. D.1.1. Installing Oracle 11g release 1 (11.1.0.6.0) Enterprise Edition on Linux
          1. D.1.1.1. Pre-installation tasks to perform as root
          2. D.1.1.2. Pre-installation tasks to perform as user oracle
          3. D.1.1.3. Installation instructions
          4. D.1.1.4. Testing Oracle 11g installation
          5. D.1.1.5. Uninstalling Oracle 11g
        2. D.1.2. Installing Oracle 11g release 1 (11.1.0.6.0) Personal Edition on Windows
          1. D.1.2.1. Installation instructions
          2. D.1.2.2. Testing Oracle 11g Installation
          3. D.1.2.3. Starting and stopping Oracle 11g
      2. D.2. Installing IBM DB2 9.5
        1. D.2.1. Installing DB2 9.5 software on Windows
          1. D.2.1.1. System requirements
          2. D.2.1.2. Pre-installation tasks
          3. D.2.1.3. Installation instructions
          4. D.2.1.4. Postinstallation tasks
          5. D.2.1.5. Uninstalling DB2 9.5
          6. D.2.1.6. Starting and stopping DB2 9.5
      3. D.3. Installing Microsoft SQL Server 2008
        1. D.3.1. Prerequisites for the Microsoft SQL Server 2008 (CTP, November 2007 release)
          1. D.3.1.1. Minimum system requirements
        2. D.3.2. Installing on Windows Vista Enterprise Edition
          1. D.3.2.1. Installation instructions
          2. D.3.2.2. Postinstallation tasks
          3. D.3.2.3. Starting and Stopping Microsoft SQL Server 2008
    9. E. Accessing RDBMS
      1. E.1. Using ORACLE 11g Utilities to Access RDBMS
        1. E.1.1. SQL*Plus
        2. E.1.2. SQL Developer
          1. E.1.2.1. Starting SQL Developer
          2. E.1.2.2. Creating a database connection
          3. E.1.2.3. Working with SQL Worksheet
        3. E.1.3. Oracle Enterprise Manager (SQL Worksheet)
      2. E.2. Using IBM DB2 9.5 Utilities to Access the RDBMS
        1. E.2.1. Command-Line Processor (CLP)
          1. E.2.1.1. Interactive mode
          2. E.2.1.2. Command mode
          3. E.2.1.3. Batch mode
        2. E.2.2. Command Editor
      3. E.3. Using Microsoft SQL Server 2008 Utilities to Access the RDBMS
        1. E.3.1. Using SQLCMD
        2. E.3.2. Using SQL Server Management Studio
    10. F. Installing the ACME Database
      1. F.1. Installing the ACME Database on Oracle 11g Using SQL*Plus
        1. F.1.1. Linux / Unix
        2. F.1.2. Microsoft Windows
      2. F.2. Installing the ACME Database on DB2 9.5 (Windows) Using CLP
      3. F.3. Installing the ACME Database on Microsoft SQL Server 2008 Using the SQLCMD Utility
    11. G. SQL Functions
    12. H. SQL Syntax Reference
      1. H.1. DDL Statements
        1. H.1.1. Tables
        2. H.1.2. Indexes
        3. H.1.3. Views
        4. H.1.4. Schemas
        5. H.1.5. Stored procedures
        6. H.1.6. User-defined functions
        7. H.1.7. Triggers
      2. H.2. DCL Statements
      3. H.3. DML Statements
      4. H.4. DQL Statements
        1. H.4.1. Single-table SELECT
        2. H.4.2. Multitable SELECT
      5. H.5. Transactional Control Statements
      6. H.6. Predicates
    13. I. SQL-Reserved Keywords
      1. I.1. Future Keywords
      2. I.2. ODBC Reserved Keywords
    14. J. The Other RDBMSs
    15. K. A Brief Introduction to Number Systems, Boolean Algebra, and Set Theory
      1. K.1. The Number Systems
        1. K.1.1. The RDBMS connection
        2. K.1.2. Converting numbers
          1. K.1.2.1. Binary to decimal conversion
          2. K.1.2.2. Decimal to binary conversion
      2. K.2. Logic Elements of Boolean Algebra
        1. K.2.1. NOT (complement or inverter)
        2. K.2.2. AND (Boolean product)
        3. K.2.3. OR (Boolean sum)
        4. K.2.4. XOR (exclusive OR)
        5. K.2.5. NAND (inversed AND)
        6. K.2.6. NOR (inversed OR)
        7. K.2.7. Rules of precedence
      3. K.3. Set Theory
        1. K.3.1. The listing of sets
        2. K.3.2. Subsets
        3. K.3.3. Set equality
        4. K.3.4. Operations on sets
          1. K.3.4.1. UNION
          2. K.3.4.2. INTERSECTION
          3. K.3.4.3. COMPLEMENT
          4. K.3.4.4. DIFFERENCE
          5. K.3.4.5. CARTESIAN PRODUCT
          6. K.3.4.6. Multiple operations
          7. K.3.4.7. Set cardinality
        5. K.3.5. Identities of set algebra

Product information

  • Title: SQL Bible, Second Edition
  • Author(s): Alex Kriegel, Boris M. Trukhnov
  • Release date: April 2008
  • Publisher(s): Wiley
  • ISBN: 9780470229064