book

SQL in a Nutshell, 4th Edition

by Kevin Kline, Regina O. Obe, Leo S. Hsu

June 2022

Intermediate to advanced

838 pages

21h 1m

English

O'Reilly Media, Inc.

Book available

Read now

Unlock full access

Why This Book?Who Should Read This Book?How This Book Is OrganizedHow to Use This BookResourcesChanges in the Fourth EditionConventions Used in This BookO’Reilly Online LearningHow to Contact UsAcknowledgmentsKevin E. Kline’s AcknowledgmentsRegina Obe and Leo Hsu’s Acknowledgments
The Relational Model and ANSI SQLCodd’s Rules for Relational Database SystemsCodd’s Rules in Action: Simple SELECT ExamplesHistory of the SQL StandardLevels of ConformanceParts of the SQL StandardSQL Statement ClassesSQL DialectsNoSQL
Database Platforms Described in This BookCategories of SyntaxIdentifiersLiteralsOperatorsKeywords and Reserved WordsSQL and Platform-Specific Data TypesMySQL Data TypesOracle Data TypesPostgreSQL Data TypesSQL Server Data TypesConstraintsScopeSyntaxPRIMARY KEY ConstraintsFOREIGN KEY ConstraintsUNIQUE ConstraintsCHECK Constraints
How to Use This ChapterSQL Platform SupportSQL Command ReferenceCREATE/ALTER DATABASE StatementCREATE DOMAIN StatementCREATE/ALTER INDEX StatementCREATE/ALTER SCHEMA StatementCREATE/ALTER TABLE StatementCREATE/ALTER TYPE StatementCREATE/ALTER VIEW StatementDROP Statements
How to Use This ChapterSQL Platform SupportSQL Command ReferenceALL/ANY/SOME OperatorsBETWEEN OperatorEXCEPT Set OperatorEXISTS OperatorFILTER ClauseGROUP BY ClauseIN OperatorINTERSECT Set OperatorIS OperatorJOIN SubclauseLIKE OperatorORDER BY ClauseOVER ClauseSELECT StatementSUBQUERY SubstatementUNION Set OperatorVALUES ClauseWHERE ClauseWITH ClauseWITH ORDINALITY Clause
How to Use This ChapterSQL Platform SupportSQL Command ReferenceCOMMIT StatementDELETE StatementINSERT StatementMERGE StatementRELEASE SAVEPOINT StatementRETURNING ClauseROLLBACK StatementSAVEPOINT StatementSET TRANSACTION StatementSTART TRANSACTION StatementTRUNCATE TABLE StatementUPDATE Statement
How to Use This ChapterSQL Platform SupportSQL Command ReferenceCONNECT StatementCREATE ROLE StatementGRANT StatementREVOKE StatementSET CONNECTION StatementSET CONSTRAINTS StatementSET PATH StatementSET ROLE StatementSET SCHEMA StatementSET SESSION AUTHORIZATION StatementSET TIME ZONE Statement
How to Use This ChapterTypes of FunctionsDeterministic and Nondeterministic FunctionsAggregate FunctionsWindow FunctionsSQL FunctionsVariable FunctionsGeneral-Purpose FunctionsNumeric FunctionsString Functions and OperatorsCollection FunctionsPlatform-Specific ExtensionsMySQL-Supported FunctionsOracle-Supported FunctionsPostgreSQL-Supported FunctionsSQL Server–Supported Functions
How to Use This ChapterSQL Aggregate FunctionsSQL Aggregate SyntaxARRAY_AGGAVG and SUMCOLLECTCORRCOUNTCOVAR_POPCOVAR_SAMPCUME_DISTDENSE_RANKEVERYLISTAGGMIN and MAXPERCENT_RANKPERCENTILE_CONTPERCENTILE_DISCRANKThe REGR Family of FunctionsSTDDEV_POPSTDDEV_SAMPVAR_POPVAR_SAMPComplementary FunctionsGROUPINGMATCH_RECOGNIZESQL Window FunctionsSQL Standard Window SyntaxMySQL Window SyntaxOracle Window SyntaxPostgreSQL Window SyntaxSQL Server Window SyntaxPartitioningOrderingGrouping or WindowingList of Window FunctionsPlatform-Specific ExtensionsMySQL-Supported FunctionsOracle-Supported FunctionsPostgreSQL-Supported FunctionsSQL Server–Supported Functions
How to Use This ChapterSQL Platform SupportSQL Command ReferenceCALL StatementCLOSE Statement (Cursors)CREATE/ALTER AGGREGATE StatementCREATE CAST StatementCREATE/ALTER FUNCTION/PROCEDURE StatementCREATE/ALTER METHOD StatementCREATE/ALTER/DROP TRIGGER StatementDECLARE CURSOR StatementFETCH Statement (Cursors)OPEN Statement (Cursors)RETURN StatementPlatform-Specific Extensions

Why JSON?Exporting Relational Data as JSONJSON SupportJSON Data TypesSQL/JSON PathSQL Standard JSON FunctionsJSON Platform-Specific ExtensionsWhy XML?XML Data TypesXPathSQL XML FunctionsXML Platform-Specific Extensions
SQL Standard KeywordsABCDEFG–HIJK–LMNOP–QRSTUV–ZKeywords Shared Across Multiple Platforms

Content preview from SQL in a Nutshell, 4th Edition

Chapter 4. Reading Your Data

In this chapter, we will explore the key SQL statements and clauses needed to extract information from your database. You will learn about the fundamental SELECT statement and the various subclauses you can use within it to select data and aggregate data into subtotals. By the end of this chapter, you will understand the concepts of JOIN operations, SET operations, aggregation, window operations, common table expressions, and subqueries. You will also know how to combine these effectively to query any data in the core database platforms.

How to Use This Chapter

When researching a command in this chapter:

Read “SQL Platform Support”.
Check Table 4-1.
Look up the specific SQL statement, check the syntax, and read the “Keywords,” “Rules at a glance,” and “Programming tips and gotchas” sections and read the section on the standard for SQL syntax and description. Do this even if you are looking for a specific platform implementation.
Finally, read the platform-specific implementation information.

You will note that the entry for a given platform implementation does not duplicate information on any clauses that do not differ from the standard. So, it is possible that you will need to flip between the descriptions for a vendor variation and the SQL standard to cover all possible details of that command.

In our discussions of MySQL, we will also include MariaDB, a fork of MySQL. For the most part, MySQL and MariaDB provide fully code-compatible syntax. ...