You are previewing Practical SQL Handbook, The: Using SQL Variants, Fourth Edition.
O'Reilly logo
Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

Book Description

This latest edition of the best-selling implementation guide to the Structured Query Language teaches SQL fundamentals while providing practical solutions for critical business applications. The Practical SQL Handbook, Fourth Edition now includes expanded platform SQL coverage and extensive real-world examples based on feedback from actual SQL users.

The Practical SQL Handbook begins with a step-by-step introduction to SQL basics and examines the issues involved in designing SQL-based database applications. It fully explores SQL’s most popular implementations from industry leaders, Oracle, Microsoft, Sybase, and Informix.

Highlights include:

  • Detailed coverage of SQL commands for creating databases, tables, and indexes, and for

  • adding, changing, and deleting data

  • Using the SELECT command to retrieve specific data

  • Handling NULL values (missing information) in a relational database

  • Joining tables, including self joins and outer joins (ANSI and WHERE-clause syntax)

  • Working with nested queries (subqueries) to get data from multiple tables

  • Creating views (virtual tables) to provide customized access to data

  • Using SQL functions

  • A bonus CD-ROM contains a time-limited, full-feature version of the Sybase® Adaptive Server Anywhere™ software as well as the sample database, scripts, and examples included in the book.

    The Practical SQL Handbook is the most complete reference available for day-to-day SQL implementations.



    0201703092B05222001

    Table of Contents

    1. Copyright
      1. Dedication
    2. List of Figures
    3. Foreword to the Fourth Edition
    4. Preface
      1. Why New Editions?
      2. Changes for Recent Editions
      3. The Fourth Edition
      4. Acknowledgments
    5. Introduction
      1. The Beginnings of SQL
      2. The Commercialization of SQL
      3. Who Should Use This Book
      4. The Focus of This Book
      5. How to Learn SQL with This Book
    6. SQL and Relational Database Management
      1. Relational Database Management
      2. The Relational Model: It's All Tables
      3. Independence Forever
      4. A High-Level Language
        1. Data Manipulation
        2. Data Definition
        3. Data Administration
      5. Relational Operations
        1. Projection
        2. Selection
        3. Join
      6. Alternatives for Viewing Data
      7. NULLs
      8. Security
      9. Integrity
      10. SQL Functions
      11. Summary
    7. Designing Databases
      1. Design Considerations
        1. Do You Need an Expert?
        2. Data Definition and Customization
        3. How to Approach Database Design
        4. Introducing the Sample Database
      2. Data Entities and Relationships
        1. Entities: Things with an Independent Existence
        2. One-to-Many Relationships
        3. Many-to-Many Relationships
        4. One-to-One Relationships
        5. The Entity-Relationship Approach Summarized
      3. The Normalization Guidelines
        1. First Normal Form
        2. Second Normal Form
        3. Third Normal Form
        4. Fourth Normal Form
      4. Reviewing the Database Design
        1. Summarizing the bookbiz Database
        2. Testing Your Database Design
        3. Other Database Definition Considerations
      5. Summary
    8. Creating and Filling a Database
      1. From Theory to Practice: Installing bookbiz
        1. Distinguishing SQL Examples and Syntax
        2. Coping with Failure
      2. Working with Databases
        1. Getting Started
        2. Recognizing Roles
        3. Reserving Space for Database Objects
        4. Choosing Databases
      3. Creating Simple Tables
        1. Choosing Datatypes
        2. Assigning NULL and NOT NULL
        3. Planning Tables
        4. Defining the Tables in bookbiz
      4. Creating Indexes
        1. The CREATE INDEX Statement
        2. How, What, and Why to Index
      5. Creating Tables With SQL-92 Constraints
        1. Sketching Constraints
        2. Implementing Constraints on Individual Columns
        3. Implementing Multicolumn Constraints
      6. Changing and Deleting Databases and Objects
        1. Changing Databases
        2. Changing Table Definitions
        3. Removing Databases
        4. Removing Tables
        5. Removing Indexes
      7. Adding, Changing, and Removing Data
        1. Adding New Rows: INSERT
        2. Changing Existing Data: UPDATE
        3. Removing Data: DELETE
      8. Summary
    9. Selecting Data from the Database
      1. SELECT Overview and Syntax
        1. Basic SELECT Syntax
        2. Full SELECT Syntax
      2. Choosing Columns: The SELECT Clause
        1. Choosing All Columns: SELECT *
        2. Choosing Specific Columns
        3. More Than Column Names
      3. Specifying Tables: The FROM Clause
        1. Using Table Aliases
        2. Skipping FROM
      4. Selecting Rows: The WHERE Clause
        1. Comparison Operators
        2. Connecting Conditions with Logical Operators
        3. Ranges (BETWEEN and NOT BETWEEN)
        4. Lists (IN and NOT IN)
        5. Selecting Null Values
        6. Matching Character Strings: LIKE
        7. Like, Is IN LIKE Equals . . . ?
      5. Summary
    10. Sorting Data and Other Selection Techniques
      1. A New Batch of SELECT Statement Clauses
      2. Sorting Query Results: ORDER BY
        1. ORDER BY Syntax
        2. Character Sets and Sort Orders
        3. Sorts Within Sorts
        4. Sort Up, Sort Down
        5. What About More Complex Expressions?
        6. How Do You Sort Nulls?
      3. Eliminating Duplicate Rows: DISTINCT and ALL
        1. DISTINCT Syntax
        2. DISTINCT with Multiple SELECT List Items
      4. Aggregate Functions
        1. Aggregate Syntax
      5. Summary
    11. Grouping Data and Reporting from It
      1. Grouping and Aggregates
      2. The GROUP BY Clause
        1. GROUP BY Syntax
        2. Cautions and Restrictions
        3. NULLs and Groups
        4. GROUP BY: Aggregate Interactions
        5. GROUP BY with WHERE
        6. GROUP BY and ORDER BY
      3. The HAVING Clause
        1. Garden-Variety HAVING
        2. WHERE, GROUP BY, HAVING, ORDER BY
      4. All About NULLs
        1. NULLs and Database Design
        2. Comparisons Involving NULLs
        3. NULLs and Computations
        4. Defaults as Alternatives to NULLs
        5. Functions That Work with NULLs
        6. NULL Functions and “What-If” Calculations
      5. Summary
    12. Joining Tables for Comprehensive Data Analysis
      1. Defining Joins
      2. Why Joins Are Necessary
        1. Associating Data from Separate Tables
        2. Providing Flexibility
      3. Getting a Good Join
        1. FROM/WHERE Join Syntax
        2. SQL-92 Join Syntax
        3. Analyzing a Join
      4. Improving the Readability of Join Results
        1. Avoiding Duplication
        2. Limiting the Width of the Display
        3. Using Aliases in the FROM Clause Table/View List
      5. Specifying Join Conditions
        1. Joins Based on Equality
        2. Joins Not Based on Equality
        3. Joining More Than Two Tables
      6. Exploring Exotic Joins
        1. Joining a Table with Itself: The Self-Join
        2. Showing the Background: Outer Joins
      7. Avoiding a Common Source of Errors
        1. Understanding the Cartesian Product
        2. Using the Cartesian Product
        3. Constraining the Cartesian Product
      8. Going Beyond Joins: UNION, INTERSECT, MINUS
        1. UNION
        2. INTERSECT and MINUS
      9. Summary
    13. Structuring Queries with Subqueries
      1. What Is a Subquery?
        1. Simplified Subquery Syntax
      2. How Do Subqueries Work?
        1. Simple Subquery Processing
        2. Correlated Subquery Processing
        3. Simple-Correlated Performance Issues
      3. Joins or Subqueries?
        1. Subqueries!
        2. Joins!
        3. Subqueries vs. Self-Joins?
        4. Which Is Better?
      4. Subquery Rules
      5. Subqueries Returning Zero or More Values
        1. Subqueries Introduced with IN
        2. Subqueries Introduced with NOT IN
        3. Correlated Subqueries Introduced with IN
        4. Subqueries Introduced with Comparison Operators and ANY or ALL
      6. Subqueries Returning a Single Value
        1. Aggregate Functions Guarantee a Single Value
        2. GROUP BY and HAVING Must Return a Single Value
        3. Correlated Subqueries with Comparison Operators
      7. Subqueries Testing Existence
        1. NOT EXISTS Seeks the Empty Set
        2. Using EXISTS to Find Intersection and Difference
        3. EXISTS Alternatives
      8. Subqueries in Multiple Levels of Nesting
      9. Subqueries in UPDATE, DELETE, and INSERT Statements
      10. Subqueries in FROM and SELECT Clauses
        1. Subqueries in the FROM Clause
        2. Subqueries in the SELECT Clause
      11. Summary
    14. Creating and Using Views
      1. With a View Toward Flexibility
      2. View Commands
        1. Creating Views
        2. Displaying Data Through Views
        3. Dropping Views
      3. Advantages of Views
        1. Focus, Simplification, and Customization
        2. Security
        3. Independence
      4. How Views Work
        1. Naming View Columns
        2. Creating Views with Multiple Underlying Objects
        3. Resolving Views
      5. Data Modification Through Views
        1. The Rules According to ANSI
      6. Creating Copies of Data
      7. Summary
    15. Security, Transactions, Performance, and Integrity
      1. Database Management in the Real World
      2. Data Security
        1. User Identification and Special Users
        2. The GRANT and REVOKE Commands
        3. Views as Security Mechanisms
      3. Transactions
        1. Transactions and Concurrency
        2. Transactions and Recovery
        3. User-Defined Transactions
      4. Performance
        1. Benchmarking
        2. Design and Indexing
        3. Optimizing Queries
        4. Other Tools for Monitoring and Boosting Performance
      5. Data Integrity
        1. Domain Constraints
        2. Entity Integrity
        3. Referential Integrity
        4. Stored Procedures and Triggers
      6. Summary
    16. Solving Business Problems
      1. Using SQL on the Job
      2. Thinking Conditionally
        1. CASE/DECODE
        2. Changing Null Displays
      3. Formatting and Displaying Data
        1. Displaying One Column as Two
        2. Displaying Two Columns as One
        3. Converting from One Datatype to Another
      4. Playing with Patterns
        1. Matching Uppercase and Lowercase Letters
        2. Finding Data Within a Range When You Don't Know the Values
        3. Locating Date Data
        4. Displaying Data by Time Units
      5. Avoiding Mistakes
        1. Distinguishing DISTINCTs
        2. Removing Duplicates
        3. Finding the “First” Entry
      6. Summary
    17. Syntax Summary for the SQL Used in This Book
      1. Formatting
      2. Syntax Conventions
      3. Statement List
    18. Industry SQL Equivalents
      1. Comparisons
      2. Naming Convention Comparison
      3. Datatype Comparison
      4. Function Comparison
        1. Character Functions
        2. Conditional Functions
        3. Date and Time Functions
    19. Glossary
    20. The bookbiz Sample Database
      1. Database Details
      2. Table Charts
        1. publishers Table
        2. authors Table
        3. titles Table
        4. titleauthors Table
        5. sales Table
        6. salesdetails Table
        7. editors Table
        8. titleditors Table
        9. roysched Table
      3. CREATE Statements for the bookbiz Database
        1. Adaptive Server Anywhere CREATEs
        2. Transact-SQL CREATEs
        3. Oracle CREATEs
        4. Informix CREATEs
      4. INSERT Statements
      5. CREATE VIEW Statements
    21. Resources
      1. Books
        1. General
        2. Informix
        3. Microsoft SQL Server
        4. mSQL/MySQL
        5. Oracle
        6. Sybase
        7. Transact-SQL
      2. Web Sites
      3. Newsgroups
    22. CD ROM Warranty
    23. Using the Practical SQL Handbook CD