SQL in a Nutshell, 4th Edition

SQL in a Nutshell, 4th Edition

by Kevin Kline, Regina O. Obe, Leo S. Hsu
Released June 2022
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781492088868

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 Buy on ebooks.com
Start your free trial

Book description

For programmers, analysts, and database administrators, this Nutshell guide is the essential reference for the SQL language used in today's most popular database products. This new fourth edition clearly documents SQL commands according to the latest ANSI/ISO standard and details how those commands are implemented in Microsoft SQL Server 2019 and Oracle 19c, as well as in the MySQL 8, MariaDB 10.5, and PostgreSQL 14 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.

Sections include:

  • 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 SQL:2016 ANSI standard
  • The implementation of each command by MySQL, Oracle, PostgreSQL, and SQL Server
  • An alphabetical reference of the ANSI SQL:2016 functions and constructs as well as the vendor implementations
  • Platform-specific functions unique to each implementation

Publisher resources

View/Submit Errata

Table of contents

  1. 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 Fourth Edition
    7. Conventions Used in This Book
    8. O’Reilly Online Learning
    9. How to Contact Us
    10. Acknowledgments
      1. Kevin E. Kline’s Acknowledgments
      2. Regina Obe and Leo Hsu’s Acknowledgments
  2. 1. SQL History and Implementations
    1. The Relational Model and ANSI SQL
      1. Codd’s Rules for Relational Database Systems
      2. Codd’s Rules in Action: Simple SELECT Examples
    2. History of the SQL Standard
      1. Levels of Conformance
      2. Parts of the SQL Standard
      3. SQL Statement Classes
    3. SQL Dialects
    4. NoSQL
  3. 2. Foundational Concepts
    1. Database Platforms Described in This Book
    2. Categories of Syntax
      1. Identifiers
      2. Literals
      3. Operators
      4. Keywords and Reserved Words
    3. SQL and Platform-Specific Data Types
      1. MySQL Data Types
      2. Oracle Data Types
      3. PostgreSQL Data Types
      4. SQL Server Data Types
    4. Constraints
      1. Scope
      2. Syntax
      3. PRIMARY KEY Constraints
      4. FOREIGN KEY Constraints
      5. UNIQUE Constraints
      6. CHECK Constraints
  4. 3. Structuring Your Data
    1. How to Use This Chapter
    2. SQL Platform Support
    3. SQL Command Reference
      1. CREATE/ALTER DATABASE Statement
      2. CREATE DOMAIN Statement
      3. CREATE/ALTER INDEX Statement
      4. CREATE/ALTER SCHEMA Statement
      5. CREATE/ALTER TABLE Statement
      6. CREATE/ALTER TYPE Statement
      7. CREATE/ALTER VIEW Statement
      8. DROP Statements
  5. 4. Reading Your Data
    1. How to Use This Chapter
    2. SQL Platform Support
    3. SQL Command Reference
      1. ALL/ANY/SOME Operators
      2. BETWEEN Operator
      3. EXCEPT Set Operator
      4. EXISTS Operator
      5. FILTER Clause
      6. GROUP BY Clause
      7. IN Operator
      8. INTERSECT Set Operator
      9. IS Operator
      10. JOIN Subclause
      11. LIKE Operator
      12. ORDER BY Clause
      13. OVER Clause
      14. SELECT Statement
      15. SUBQUERY Substatement
      16. UNION Set Operator
      17. VALUES Clause
      18. WHERE Clause
      19. WITH Clause
      20. WITH ORDINALITY Clause
  6. 5. Manipulating Your Data
    1. How to Use This Chapter
    2. SQL Platform Support
    3. SQL Command Reference
      1. COMMIT Statement
      2. DELETE Statement
      3. INSERT Statement
      4. MERGE Statement
      5. RELEASE SAVEPOINT Statement
      6. RETURNING Clause
      7. ROLLBACK Statement
      8. SAVEPOINT Statement
      9. SET TRANSACTION Statement
      10. START TRANSACTION Statement
      11. TRUNCATE TABLE Statement
      12. UPDATE Statement
  7. 6. Securing Your Data
    1. How to Use This Chapter
    2. SQL Platform Support
    3. SQL Command Reference
      1. CONNECT Statement
      2. CREATE ROLE Statement
      3. GRANT Statement
      4. REVOKE Statement
      5. SET CONNECTION Statement
      6. SET CONSTRAINTS Statement
      7. SET PATH Statement
      8. SET ROLE Statement
      9. SET SCHEMA Statement
      10. SET SESSION AUTHORIZATION Statement
      11. SET TIME ZONE Statement
  8. 7. SQL Built-in Functions
    1. How to Use This Chapter
    2. Types of Functions
      1. Deterministic and Nondeterministic Functions
      2. Aggregate Functions
      3. Window Functions
    3. SQL Functions
      1. Variable Functions
      2. General-Purpose Functions
      3. Numeric Functions
      4. String Functions and Operators
      5. Collection Functions
    4. Platform-Specific Extensions
      1. MySQL-Supported Functions
      2. Oracle-Supported Functions
      3. PostgreSQL-Supported Functions
      4. SQL Server–Supported Functions
  9. 8. SQL Built-in Aggregate and Window Functions
    1. How to Use This Chapter
    2. SQL Aggregate Functions
      1. SQL Aggregate Syntax
      2. ARRAY_AGG
      3. AVG and SUM
      4. COLLECT
      5. CORR
      6. COUNT
      7. COVAR_POP
      8. COVAR_SAMP
      9. CUME_DIST
      10. DENSE_RANK
      11. EVERY
      12. LISTAGG
      13. MIN and MAX
      14. PERCENT_RANK
      15. PERCENTILE_CONT
      16. PERCENTILE_DISC
      17. RANK
      18. The REGR Family of Functions
      19. STDDEV_POP
      20. STDDEV_SAMP
      21. VAR_POP
      22. VAR_SAMP
    3. Complementary Functions
      1. GROUPING
      2. MATCH_RECOGNIZE
    4. SQL Window Functions
      1. SQL Standard Window Syntax
      2. MySQL Window Syntax
      3. Oracle Window Syntax
      4. PostgreSQL Window Syntax
      5. SQL Server Window Syntax
      6. Partitioning
      7. Ordering
      8. Grouping or Windowing
      9. List of Window Functions
    5. Platform-Specific Extensions
      1. MySQL-Supported Functions
      2. Oracle-Supported Functions
      3. PostgreSQL-Supported Functions
      4. SQL Server–Supported Functions
  10. 9. Storing Logic in the Database
    1. How to Use This Chapter
    2. SQL Platform Support
    3. SQL Command Reference
      1. CALL Statement
      2. CLOSE Statement (Cursors)
      3. CREATE/ALTER AGGREGATE Statement
      4. CREATE CAST Statement
      5. CREATE/ALTER FUNCTION/PROCEDURE Statement
      6. CREATE/ALTER METHOD Statement
      7. CREATE/ALTER/DROP TRIGGER Statement
      8. DECLARE CURSOR Statement
      9. FETCH Statement (Cursors)
      10. OPEN Statement (Cursors)
      11. RETURN Statement
      12. Platform-Specific Extensions
  11. 10. Flexible and Schemaless
    1. Why JSON?
      1. Exporting Relational Data as JSON
    2. JSON Support
      1. JSON Data Types
      2. SQL/JSON Path
      3. SQL Standard JSON Functions
      4. JSON Platform-Specific Extensions
    3. Why XML?
      1. XML Data Types
      2. XPath
      3. SQL XML Functions
      4. XML Platform-Specific Extensions
  12. A. Shared and Platform-Specific Keywords
    1. SQL Standard Keywords
      1. A
      2. B
      3. C
      4. D
      5. E
      6. F
      7. G–H
      8. I
      9. J
      10. K–L
      11. M
      12. N
      13. O
      14. P–Q
      15. R
      16. S
      17. T
      18. U
      19. V–Z
    2. Keywords Shared Across Multiple Platforms
  13. Index
  14. About the Authors

Product information

  • Title: SQL in a Nutshell, 4th Edition
  • Author(s): Kevin Kline, Regina O. Obe, Leo S. Hsu
  • Release date: June 2022
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781492088868