Cover image for SQL in a Nutshell, 3rd Edition

Book description

For programmers, analysts, and database administrators, SQL in a Nutshell is the essential reference for the SQL language used in today's most popular database products. This new edition clearly documents every SQL command according to the latest ANSI standard, and details how those commands are implemented in Microsoft SQL Server 2008, Oracle 11g, and the MySQL 5.1 and PostgreSQL 8.3 open source database products. You'll also get a concise overview of the Relational Database Management System (RDBMS) model, and a clear-cut explanation of foundational RDBMS concepts -- all packed into a succinct, comprehensive, and easy-to-use format. This book provides:

  • Background on the Relational Database Model, including current and previous SQL standards

  • Fundamental concepts necessary for understanding relational databases and SQL commands

  • An alphabetical command reference to SQL statements, according to the SQL2003 ANSI standard

  • The implementation of each command by MySQL, Oracle, PostgreSQL, and SQL Server

  • An alphabetical reference of the ANSI SQL2003 functions, as well as the vendor implementations

  • Platform-specific functions unique to each implementation

Beginning where vendor documentation ends, SQL in a Nutshell distills the experiences of professional database administrators and developers who have used SQL variants to support complex enterprise applications. Whether SQL is new to you, or you've been using SQL since its earliest days, you'll get lots of new tips and techniques in this book.

Table of Contents

  1. SQL in a Nutshell
  2. Preface
    1. Why This Book?
    2. Who Should Read This Book?
    3. How This Book Is Organized
    4. How to Use This Book
    5. Resources
    6. Changes in the Third Edition
    7. Conventions Used in This Book
    8. Using Code Examples
    9. How to Contact Us
    10. Safari® Books Online
    11. Acknowledgments
      1. Kevin E. Kline's Acknowledgments
      2. Daniel Kline's Acknowledgments
      3. Brand Hunt's Acknowledgments
  3. 1. SQL History and Implementations
    1. The Relational Model and ANSI SQL
      1. Codd's Rules for Relational Database Systems
        1. Data structures (rules 1, 2, and 8)
        2. NULLs (rule 3)
        3. Metadata (rules 4 and 10)
        4. The language (rules 5 and 11)
        5. Views (rule 6)
        6. Set operations (rules 7 and 12)
      2. Codd's Rules in Action: Simple SELECT Examples
    2. History of the SQL Standard
      1. What's New in SQL2006
      2. What's New in SQL2003 (SQL3)
      3. Levels of Conformance
      4. Supplemental Features Packages in the SQL3 Standard
      5. SQL3 Statement Classes
    3. SQL Dialects
  4. 2. Foundational Concepts
    1. Database Platforms Described in This Book
      1. Categories of Syntax
        1. Identifiers
        2. Naming conventions
        3. Identifier rules
      2. Literals
      3. Operators
        1. Arithmetic operators
        2. Assignment operators
        3. Bitwise operators
        4. Comparison operators
        5. Logical operators
        6. Unary operators
        7. Operator precedence
        8. System delimiters and operators
      4. Keywords and Reserved Words
    2. SQL2003 and Platform-Specific Datatypes
      1. MySQL Datatypes
      2. Oracle Datatypes
      3. PostgreSQL Datatypes
      4. SQL Server Datatypes
    3. Constraints
      1. Scope
      2. Syntax
      3. PRIMARY KEY Constraints
      4. FOREIGN KEY Constraints
      5. UNIQUE Constraints
      6. CHECK Constraints
  5. 3. SQL Statement Command Reference
    1. How to Use This Chapter
    2. SQL Platform Support
    3. SQL Command Reference
      1. ALL/ANY/SOME Operators
      2. BETWEEN Operator
      3. CALL Statement
      4. CLOSE CURSOR Statement
      5. COMMIT Statement
      6. CONNECT Statement
      7. CREATE/ALTER DATABASE Statement
      8. CREATE/ALTER FUNCTION/PROCEDURE Statements
      9. CREATE/ALTER INDEX Statement
      10. CREATE/ALTER METHOD Statement
      11. CREATE ROLE Statement
      12. CREATE SCHEMA Statement
      13. CREATE/ALTER TABLE Statement
      14. CREATE/ALTER TRIGGER Statement
      15. CREATE/ALTER TYPE Statement
      16. CREATE/ALTER VIEW Statement
      17. DECLARE CURSOR Command
      18. DELETE Statement
      19. DISCONNECT Statement
      20. DROP Statements
      21. EXCEPT Set Operator
      22. EXISTS Operator
      23. FETCH Statement
      24. GRANT Statement
      25. IN Operator
      26. INSERT Statement
      27. INTERSECT Set Operator
      28. IS Operator
      29. JOIN Subclause
      30. LIKE Operator
      31. MERGE Statement
      32. OPEN Statement
      33. ORDER BY Clause
      34. RELEASE SAVEPOINT Statement
      35. RETURN Statement
      36. REVOKE Statement
      37. ROLLBACK Statement
      38. SAVEPOINT Statement
      39. SELECT Statement
      40. SET Statement
      41. SET CONNECTION Statement
      42. SET CONSTRAINT Statement
      43. SET PATH Statement
      44. SET ROLE Statement
      45. SET SCHEMA Statement
      46. SET SESSION AUTHORIZATION Statement
      47. SET TIME ZONE Statement
      48. SET TRANSACTION Statement
      49. START TRANSACTION Statement
      50. SUBQUERY Substatement
      51. TRUNCATE TABLE Statement
      52. UNION Set Operator
      53. UPDATE Statement
      54. WHERE Clause
  6. 4. SQL Functions
    1. Types of Functions
      1. Deterministic and Nondeterministic Functions
      2. Aggregate and Scalar Functions
      3. Window Functions
    2. ANSI SQL Aggregate Functions
      1. AVG and SUM
      2. CORR
      3. COUNT
      4. COVAR_POP
      5. COVAR_SAMP
      6. CUME_DIST
      7. DENSE_RANK
      8. MIN and MAX
      9. PERCENT_RANK
      10. PERCENTILE_CONT
      11. PERCENTILE_DISC
      12. RANK
      13. The REGR Family of Functions
      14. STDDEV_POP
      15. STDDEV_SAMP
      16. VAR_POP
      17. VAR_SAMP
    3. ANSI SQL Window Functions
      1. ANSI SQL2003's Window Syntax
      2. Oracle's Window Syntax
      3. SQL Server's Window Syntax
      4. Partitioning
      5. Ordering
      6. Grouping or Windowing
      7. List of Window Functions
        1. CUME_DIST
        2. DENSE_RANK
        3. PERCENT_RANK
        4. RANK
        5. ROW_NUMBER
    4. ANSI SQL Scalar Functions
      1. Built-in Scalar Functions
      2. CASE and CAST Functions
        1. CASE
        2. CAST
      3. Numeric Scalar Functions
        1. ABS
        2. BIT_LENGTH, CHAR_LENGTH, and OCTET_LENGTH
        3. CEIL
        4. EXP
        5. EXTRACT
        6. FLOOR
        7. LN
        8. MOD
        9. POSITION
        10. POWER
        11. SQRT
        12. WIDTH_BUCKET
      4. String Functions and Operators
        1. Concatenation Operator
        2. CONVERT and TRANSLATE
        3. LOWER and UPPER
        4. OVERLAY
        5. SUBSTRING
        6. TRIM
    5. Platform-Specific Extensions
      1. MySQL-Supported Functions
      2. Oracle-Supported Functions
      3. PostgreSQL-Supported Functions
      4. SQL Server-Supported Functions
  7. A. Shared and Platform-Specific Keywords
  8. Index
  9. About the Author
  10. Colophon
  11. Copyright