SQL in a Nutshell: A Desktop Quick Reference

✍ Scribed by Kevin Kline, Regina Obe, Leo Hsu

• Publisher: O'Reilly Media
• Year: 2022
• Tongue: English
• Leaves: 841
• Edition: 4
• Category: Library

✦ Synopsis

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

✦ Table of Contents

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

✦ Subjects

SQL; Relational Databases; MySQL; PostgreSQL; Microsoft SQL Server