O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

SQL in a Nutshell, 2nd Edition

Book Description

SQL in a Nutshell applies the eminently useful "Nutshell" format to Structured Query Language (SQL), the elegant--but complex--descriptive language that is used to create and manipulate large stores of data. For SQL programmers, analysts, and database administrators, the new second edition of SQL in a Nutshell is the essential date language reference for the world's top SQL database products. SQL in a Nutshell is a lean, focused, and thoroughly comprehensive reference for those who live in a deadline-driven world.This invaluable desktop quick reference drills down and documents every SQL command and how to use it in both commercial (Oracle, DB2, and Microsoft SQL Server) and open source implementations (PostgreSQL, and MySQL). It describes every command and reference and includes the command syntax (by vendor, if the syntax differs across implementations), a clear description, and practical examples that illustrate important concepts and uses. And it also explains how the leading commercial and open sources database product implement SQL. This wealth of information is packed into a succinct, comprehensive, and extraordinarily easy-to-use format that covers the SQL syntax of no less than 4 different databases.When you need fast, accurate, detailed, and up-to-date SQL information, SQL in a Nutshell, Second Edition will be the quick reference you'll reach for every time. SQL in a Nutshell is small enough to keep by your keyboard, and concise (as well as clearly organized) enough that you can look up the syntax you need quickly without having to wade through a lot of useless fluff. You won't want to work on a project involving SQL without it.

Table of Contents

  1. Preface
    1. Why This Book?
    2. Who Should Read This Book?
    3. How This Book Is Organized
    4. Conventions Used in This Book
    5. How to Use This Book
    6. How to Contact Us
    7. Safari Enabled
    8. Resources
    9. Changes in the Second Edition
    10. Acknowledgments
      1. Brand Hunt’s Acknowledgments
      2. Daniel Kline’s Acknowledgments
      3. Kevin E. Kline’s Acknowledgments
  2. 1. SQL History and Implementations
    1. 1.1. The Relational Model and ANSI SQL
      1. 1.1.1. Codd’s Rules for Relational Database Systems
        1. 1.1.1.1. Data structures (rules 1, 2, and 8)
        2. 1.1.1.2. NULLS (rule 3)
        3. 1.1.1.3. Metadata (rules 4 and 10)
        4. 1.1.1.4. The language (rules 5 and 11)
        5. 1.1.1.5. Views (rule 6)
        6. 1.1.1.6. Set operations (rules 7 and 12)
      2. 1.1.2. Codd’s Rules in Action: Simple SELECT Examples
    2. 1.2. History of the SQL Standard
      1. 1.2.1. What’s New in SQL2003
      2. 1.2.2. Levels of Conformance
      3. 1.2.3. Supplemental Features Packages in the SQL2003 Standard
      4. 1.2.4. SQL2003 Statement Classes
    3. 1.3. SQL Dialects
  3. 2. Foundational Concepts
    1. 2.1. Database Platforms Described in This Book
    2. 2.2. Categories of Syntax
      1. 2.2.1. Identifiers
        1. 2.2.1.1. Naming conventions
        2. 2.2.1.2. Identifier rules
      2. 2.2.2. Literals
      3. 2.2.3. Operators
        1. 2.2.3.1. Arithmetic operators
        2. 2.2.3.2. Assignment operators
        3. 2.2.3.3. Bitwise operators
        4. 2.2.3.4. Comparison operators
        5. 2.2.3.5. Logical operators
        6. 2.2.3.6. Unary operators
        7. 2.2.3.7. Operator precedence
        8. 2.2.3.8. System delimiters and operators
      4. 2.2.4. Keywords and Reserved Words
    3. 2.3. SQL2003 and Platform-Specific Datatypes
      1. 2.3.1. DB2 Datatypes
      2. 2.3.2. MySQL Datatypes
      3. 2.3.3. Oracle Datatypes
      4. 2.3.4. PostgreSQL Datatypes
      5. 2.3.5. SQL Server Datatypes
    4. 2.4. Constraints
      1. 2.4.1. Scope
      2. 2.4.2. Syntax
      3. 2.4.3. PRIMARY KEY Constraints
      4. 2.4.4. FOREIGN KEY Constraints
      5. 2.4.5. UNIQUE Constraints
      6. 2.4.6. CHECK Constraints
  4. 3. SQL Statement Command Reference
    1. 3.1. How to Use This Chapter
    2. 3.2. SQL Platform Support
    3. 3.3. SQL Command Reference
  5. 4. SQL Functions
    1. 4.1. Types of Functions
      1. 4.1.1. Deterministic and Nondeterministic Functions
      2. 4.1.2. Aggregate and Scalar Functions
      3. 4.1.3. Window Functions
    2. 4.2. ANSI SQL Aggregate Functions
    3. 4.3. ANSI SQL Window Functions
      1. 4.3.1. SQL2003’s Window Syntax
      2. 4.3.2. Oracle’s Window Syntax
      3. 4.3.3. DB2’s Window Syntax
      4. 4.3.4. Partitioning
      5. 4.3.5. Ordering
      6. 4.3.6. Grouping or Windowing
      7. 4.3.7. List of Window Functions
    4. 4.4. ANSI SQL Scalar Functions
      1. 4.4.1. Built-in Scalar Functions
      2. 4.4.2. CASE and CAST Functions
      3. 4.4.3. Numeric Scalar Functions
      4. 4.4.4. String Functions and Operators
      5. Reference Section
      6. Reference Section
      7. Reference Section
      8. Reference Section
      9. Reference Section
      10. Reference Section
    5. 4.5. Platform-Specific Extensions
      1. 4.5.1. DB2-Supported Extensions
      2. 4.5.2. MySQL-Supported Functions
      3. 4.5.3. Oracle-Supported Functions
      4. 4.5.4. PostgreSQL-Supported Functions
      5. 4.5.5. SQL Server-Supported Functions
  6. 5. Database Programming
    1. 5.1. Database Programming Overview
    2. 5.2. Opening a Database Connection
      1. 5.2.1. Opening an ADO.NET Database Connection
      2. 5.2.2. Opening a JDBC Database Connection
        1. 5.2.2.1. DB2
        2. 5.2.2.2. MySQL
        3. 5.2.2.3. PostgreSQL
        4. 5.2.2.4. Oracle
        5. 5.2.2.5. SQL Server
    3. 5.3. Closing a Database Connection
      1. 5.3.1. Closing an ADO.NET Database Connection
      2. 5.3.2. Closing a JDBC Database Connection
    4. 5.4. Managing Transactions
      1. 5.4.1. Beginning a Transaction
        1. 5.4.1.1. Beginning an ADO.NET transaction
        2. 5.4.1.2. Beginning a JDBC transaction
      2. 5.4.2. Committing a Transaction
        1. 5.4.2.1. Committing an ADO.NET transaction
        2. 5.4.2.2. Committing a JDBC transaction
      3. 5.4.3. Rolling Back a Transaction
        1. 5.4.3.1. Rolling back an ADO.NET transaction
        2. 5.4.3.2. Rolling back a JDBC transaction
    5. 5.5. Executing Statements
      1. 5.5.1. Executing an ADO.NET Statement
      2. 5.5.2. Executing a JDBC Statement
    6. 5.6. Retrieving Data
      1. 5.6.1. Retrieving Data Using ADO.NET
      2. 5.6.2. Retrieving Data Using JDBC
        1. 5.6.2.1. Use the following steps to execute query statements in JDBC:
    7. 5.7. Bound Parameters
      1. 5.7.1. ADO.NET Bound Parameters
        1. 5.7.1.1. Use the following steps to execute statements with bound parameters in ADONET:
      2. 5.7.2. Binding Parameters with JDBC
        1. 5.7.2.1. Use the following steps to execute statements with bound parameters in JDBC:
    8. 5.8. Error Handling
      1. 5.8.1. Error Handling in ADO.NET
      2. 5.8.2. Error Handling in JDBC
    9. 5.9. Examples
      1. 5.9.1. ADO.NET Example
      2. 5.9.2. JDBC Example
  7. A. Sybase Adaptive Server
    1. A.1. Sybase Adaptive Server Datatypes
    2. A.2. Sybase Adaptive Server SQL Statements
    3. A.3. Sybase Adaptive Server SQL Functions
    4. A.4. Sybase Adaptive Server Keywords
  8. B. Shared and Platform-Specific Keywords
  9. About the Author
  10. Colophon
  11. Copyright