SQL Pocket Guide, 2nd Edition

SQL Pocket Guide, 2nd Edition

by Jonathan Gennick
Released April 2006
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596526887

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.

Buy on Amazon
Start your free trial

Book description

SQL is the language of databases. It's used to create and maintain database objects, place data into those objects, query the data, modify the data, and, finally, delete data that is no longer needed. Databases lie at the heart of many, if not most business applications. Chances are very good that if you're involved with software development, you're using SQL to some degree. And if you're using SQL, you should own a good reference or two.

Now available in an updated second edition, our very popular SQL Pocket Guide is a major help to programmers, database administrators, and everyone who uses SQL in their day-to-day work. The SQL Pocket Guide is a concise reference to frequently used SQL statements and commonly used SQL functions. Not just an endless collection of syntax diagrams, this portable guide addresses the language's complexity head on and leads by example. The information in this edition has been updated to reflect the latest versions of the most commonly used SQL variants including:

  • Oracle Database 10g, Release 2 (includingthe free Oracle Database 10g Express Edition (XE))
  • Microsoft SQL Server 2005
  • MySQL 5
  • IBM DB2 8.2
  • PostreSQL 8.1 database

Table of contents

  1. SQL Pocket Guide, 2nd Edition
  2. A Note Regarding Supplemental Files
  3. Praise for SQL Pocket Guide
  4. 1. SQL Pocket Guide
    1. 1.1. Introduction
      1. 1.1.1. Organization of This Book
      2. 1.1.2. Feedback Needed!
      3. 1.1.3. Conventions
      4. 1.1.4. Acknowledgments
      5. 1.1.5. Example Data
    2. 1.2. Analytic Functions
    3. 1.3. CASE Expressions
      1. 1.3.1. Simple CASE Expressions
      2. 1.3.2. Searched CASE Expressions
    4. 1.4. Datatypes
      1. 1.4.1. Character String Types
      2. 1.4.2. Decimal Types
      3. 1.4.3. Binary Integer Types
      4. 1.4.4. Datetime Types
        1. 1.4.4.1. Oracle
        2. 1.4.4.2. DB2
        3. 1.4.4.3. SQL Server
        4. 1.4.4.4. MySQL
        5. 1.4.4.5. PostgreSQL
    5. 1.5. Dataype Conversion
      1. 1.5.1. Standard CAST Function
      2. 1.5.2. Standard EXTRACT Function
      3. 1.5.3. Datetime Conversions (Oracle)
      4. 1.5.4. Numeric Conversions (Oracle)
      5. 1.5.5. Datetime Conversions (DB2)
      6. 1.5.6. Numeric Conversions (DB2)
      7. 1.5.7. Datetime Conversions (SQL Server)
        1. 1.5.7.1. CAST and SET DATEFORMAT (SQL Server)
        2. 1.5.7.2. CONVERT (SQL Server)
        3. 1.5.7.3. DATENAME and DATEPART (SQL Server)
        4. 1.5.7.4. DAY, MONTH, and YEAR (SQL Server)
      8. 1.5.8. Numeric Conversions (SQL Server)
      9. 1.5.9. Datetime Conversions (MySQL)
        1. 1.5.9.1. Date and time elements (MySQL)
        2. 1.5.9.2. TO_DAYS and FROM_DAYS (MySQL)
        3. 1.5.9.3. Unix timestamp support (MySQL)
        4. 1.5.9.4. Seconds in the day (MySQL)
        5. 1.5.9.5. DATE_FORMAT and TIME_FORMAT (MySQL)
      10. 1.5.10. Numeric Conversions (MySQL)
      11. 1.5.11. Datetime Conversions (PostgreSQL)
      12. 1.5.12. Numeric Conversions (PostgreSQL)
    6. 1.6. Deleting Data
      1. 1.6.1. Deleting in Order (MySQL)
      2. 1.6.2. Deleting All Rows
      3. 1.6.3. Deleting from Views and Subqueries
      4. 1.6.4. Returning Deleted Data (Oracle, DB2)
      5. 1.6.5. Double-FROM (SQL Server)
    7. 1.7. Functions
      1. 1.7.1. Datetime Functions (Oracle)
        1. 1.7.1.1. Getting the current date and time in Oracle
        2. 1.7.1.2. Rounding and truncating DATEs in Oracle
        3. 1.7.1.3. Other useful Oracle datetime functions
      2. 1.7.2. Datetime Functions (DB2)
      3. 1.7.3. Datetime Functions (SQL Server)
      4. 1.7.4. Datetime Functions (MySQL)
      5. 1.7.5. Datetime Functions (PostgreSQL)
        1. 1.7.5.1. Getting the current date and time in PostgreSQL
        2. 1.7.5.2. Rounding and truncating in PostgreSQL
        3. 1.7.5.3. Other useful PostgreSQL datetime functions
      6. 1.7.6. Numeric and Math Functions (All Platforms)
      7. 1.7.7. Trigonometric Functions (All Platforms)
      8. 1.7.8. String Functions
        1. 1.7.8.1. Searching a string
        2. 1.7.8.2. Replacing text in a string
        3. 1.7.8.3. Extracting a substring
        4. 1.7.8.4. Finding the length of a string
        5. 1.7.8.5. Concatenating strings
        6. 1.7.8.6. Trimming unwanted characters
        7. 1.7.8.7. Changing the case of a string
      9. 1.7.9. GREATEST and LEAST (Oracle, PostgreSQL)
    8. 1.8. Grouping and Summarizing
      1. 1.8.1. Aggregate Functions
      2. 1.8.2. GROUP BY
      3. 1.8.3. Useful GROUP BY Techniques
        1. 1.8.3.1. Reducing the GROUP BY list
        2. 1.8.3.2. Grouping before the join
      4. 1.8.4. HAVING
      5. 1.8.5. GROUP BY Extensions (Oracle, DB2)
        1. 1.8.5.1. ROLLUP (Oracle, DB2)
        2. 1.8.5.2. CUBE (Oracle, DB2)
        3. 1.8.5.3. GROUPING SETS (Oracle)
        4. 1.8.5.4. Related functions (Oracle, DB2)
      6. 1.8.6. GROUP BY Extensions (SQL Server)
        1. 1.8.6.1. ROLLUP (SQL Server)
        2. 1.8.6.2. CUBE (SQL Server)
        3. 1.8.6.3. GROUPING (SQL Server)
    9. 1.9. Hierarchical Queries
      1. 1.9.1. Recursive WITH (SQL Server, DB2)
      2. 1.9.2. CONNECT BY (Oracle)
        1. 1.9.2.1. CONNECT BY, START WITH, and PRIOR (Oracle)
        2. 1.9.2.2. Creative CONNECT BY (Oracle)
        3. 1.9.2.3. WHERE clauses with CONNECT BY (Oracle)
        4. 1.9.2.4. Joins with CONNECT BY (Oracle)
        5. 1.9.2.5. Sorting CONNECT BY results (Oracle)
        6. 1.9.2.6. Loops in hierarchical data (Oracle)
        7. 1.9.2.7. CONNECT BY functions and operators (Oracle)
    10. 1.10. Indexes, Creating
    11. 1.11. Indexes, Removing
    12. 1.12. Inserting Data
      1. 1.12.1. Single-Row Inserts
      2. 1.12.2. Multi-Row Inserts (DB2, MySQL)
      3. 1.12.3. Insert Targets
      4. 1.12.4. Subquery Inserts
      5. 1.12.5. Returning Inserted Values (Oracle, DB2)
      6. 1.12.6. Multi-Table Inserts (Oracle)
        1. 1.12.6.1. Unconditional multi-table insert (Oracle)
        2. 1.12.6.2. Conditional multi-table insert (Oracle)
        3. 1.12.6.3. ALL versus FIRST (Oracle)
    13. 1.13. Joining Tables
      1. 1.13.1. The Concept of a Join
      2. 1.13.2. Cross Joins
      3. 1.13.3. Inner Joins
      4. 1.13.4. The USING Clause (Oracle, MySQL, PostgreSQL)
      5. 1.13.5. Natural Joins (Oracle, MySQL, PostgreSQL)
      6. 1.13.6. Non-Equi-Joins
      7. 1.13.7. Outer Joins
        1. 1.13.7.1. Left outer joins
        2. 1.13.7.2. Interpreting nulls in an outer join
        3. 1.13.7.3. Right outer joins
        4. 1.13.7.4. Full outer joins
        5. 1.13.7.5. Vendor-specific outer join syntax
    14. 1.14. Literals
      1. 1.14.1. Text Literals
      2. 1.14.2. Numeric Literals
      3. 1.14.3. Datetime Literals (Oracle, MySQL, PostgreSQL)
      4. 1.14.4. Datetime Interval Literals
    15. 1.15. Merging Data (Oracle, DB2)
    16. 1.16. Nulls
      1. 1.16.1. Predicates for Nulls
      2. 1.16.2. Using CASE with Nulls
      3. 1.16.3. Using the COALESCE Function
      4. 1.16.4. Functions for Nulls (Oracle)
      5. 1.16.5. Functions for Nulls (DB2)
      6. 1.16.6. Functions for Nulls (SQL Server)
      7. 1.16.7. Functions for Nulls (MySQL)
      8. 1.16.8. Functions for Nulls (PostgreSQL)
    17. 1.17. OLAP Functions
    18. 1.18. Predicates
      1. 1.18.1. EXISTS Predicates
      2. 1.18.2. IN Predicates
      3. 1.18.3. BETWEEN Predicates
      4. 1.18.4. LIKE Predicates
    19. 1.19. Recursive Queries
    20. 1.20. Regular Expressions
      1. 1.20.1. Regular Expressions (Oracle)
      2. 1.20.2. Regular Expressions (SQL Server)
      3. 1.20.3. Regular Expressions (MySQL)
      4. 1.20.4. Regular Expressions (PostgreSQL)
    21. 1.21. Selecting Data
      1. 1.21.1. The SELECT Clause
        1. 1.21.1.1. Listing the columns to retrieve
        2. 1.21.1.2. Taking shortcuts with the asterisk
        3. 1.21.1.3. Writing expressions
        4. 1.21.1.4. Specifying result-set column names
        5. 1.21.1.5. Dealing with case and punctuation in names
        6. 1.21.1.6. Using subqueries in a SELECT list
        7. 1.21.1.7. Qualifying column names
      2. 1.21.2. ALL and DISTINCT
      3. 1.21.3. The FROM Clause
        1. 1.21.3.1. Table aliases in the FROM clause
        2. 1.21.3.2. Subqueries in the FROM clause
        3. 1.21.3.3. Selecting newly inserted, updated, or deleted rows (DB2)
        4. 1.21.3.4. Generating tables through the VALUES clause (DB2)
      4. 1.21.4. The WHERE Clause
      5. 1.21.5. The GROUP BY Clause
      6. 1.21.6. The HAVING Clause
      7. 1.21.7. The ORDER BY Clause
    22. 1.22. Subqueries
      1. 1.22.1. The WITH Clause (Oracle, SQL Server, DB2)
    23. 1.23. Tables, Creating
      1. 1.23.1. Creating a Table (Oracle)
      2. 1.23.2. Creating a Table (DB2)
      3. 1.23.3. Creating a Table (SQL Server)
      4. 1.23.4. Creating a Table (MySQL)
      5. 1.23.5. Creating a Table (PostgreSQL)
    24. 1.24. Tables, Modifying
      1. 1.24.1. Modifying a Table (Oracle)
      2. 1.24.2. Modifying a Table (DB2)
      3. 1.24.3. Modifying a Table (SQL Server)
      4. 1.24.4. Modifying a Table (MySQL)
      5. 1.24.5. Modifying a Table (PostgreSQL)
    25. 1.25. Tables, Removing
    26. 1.26. Transaction Management
      1. 1.26.1. Autocommit Mode (SQL Server, MySQL, PostgreSQL)
      2. 1.26.2. Starting a Transaction
        1. 1.26.2.1. Starting a transaction (Oracle)
        2. 1.26.2.2. Starting a transaction (SQL Server)
        3. 1.26.2.3. Starting a transaction (MySQL)
        4. 1.26.2.4. Starting a transaction (PostgreSQL)
      3. 1.26.3. Ending a Transaction
      4. 1.26.4. Aborting a Transaction
      5. 1.26.5. Aborting to a Transaction Savepoint
    27. 1.27. Union Queries
      1. 1.27.1. UNION and UNION ALL
        1. 1.27.1.1. UNION
        2. 1.27.1.2. UNION ALL
      2. 1.27.2. ORDER BY in Union Queries
      3. 1.27.3. Names and Datatypes in a Union
      4. 1.27.4. Order of Evaluation
      5. 1.27.5. EXCEPT (or MINUS)
        1. 1.27.5.1. EXCEPT (or MINUS in Oracle)
        2. 1.27.5.2. EXCEPT ALL (DB2, PostgreSQL)
      6. 1.27.6. INTERSECT
      7. 1.27.7. INTERSECT
        1. 1.27.7.1. INTERSECT ALL (DB2, PostgreSQL)
    28. 1.28. Updating Data
      1. 1.28.1. Simple Updates
      2. 1.28.2. New Values from a Subquery
      3. 1.28.3. Updating Views and Subqueries
      4. 1.28.4. UPDATE FROM Clause (SQL Server, PostgreSQL)
      5. 1.28.5. Returning Updated Data (Oracle, DB2)
    29. 1.29. Window Functions
      1. 1.29.1. Defining a Summary Window (Oracle, DB2, SQL Server)
      2. 1.29.2. Ordering and Ranking Within a Window (Oracle, DB2, SQL Server)
      3. 1.29.3. Comparing Values Across Rows (Oracle)
      4. 1.29.4. Summarizing over a Moving Window (Oracle, DB2)
      5. 1.29.5. Window Function Syntax (Oracle)
      6. 1.29.6. Window Function Syntax (DB2)
      7. 1.29.7. Window Function Evaluation and Placement
  5. About the Author
  6. Copyright

Product information

  • Title: SQL Pocket Guide, 2nd Edition
  • Author(s): Jonathan Gennick
  • Release date: April 2006
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596526887