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

Preface

Since its first incarnation in the 1970s, the Structured Query Language (SQL) has evolved hand-in-hand with the information boom. As a result, it is the most widely used language to administer and query relational databases. Many software companies and developers, including those in the open source and NoSQL movements, have developed their own SQL dialects in response to specific needs. All the while, standards bodies have codified a growing list of features.

SQL in a Nutshell, 4th edition, describes the latest SQL standard—SQL:2016—version of many SQL commands, then details how different platforms implement that particular command. (Although the standards bodies have released SQL:2019, the new specifications mostly lie in the area of multidimensional arrays, which have not been implemented by the major vendors. You can read more about how SQL can be used in conjunction with these arrays on the ISO website). In this book, you will find a brief overview of the relational database model, which undergirds all relational database management systems (RDBMSs), followed by a more thorough treatment of frequently used SQL syntax and commands. New in the fourth edition are expanded explanations of programming concepts used in each vendor’s offerings, such as stored procedures and user-defined functions. And, of course, we have added popular commands that have arrived since the publication of the third edition.

SQL in a Nutshell, 4th edition, provides a concise guide to the two ...