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 7. SQL Built-in Functions

A function is a special type of command word in the SQL command set, and each SQL dialect varies in its implementation of that command set. The value of a function can be determined by input parameters, as with a function that averages a list of database values. However, many functions do not use any type of input parameter. The function that returns the current system time, CURRENT_TIME, is an example of such a function. Functions may also return tables or sets of rows. These types of functions are often referred to as set-returning functions.

The SQL standard supports a number of useful functions. This chapter and the next cover those functions, providing detailed descriptions and examples for each platform. We do not cover every function under the sun, but will focus on the most commonly used functions. In addition, each database supports a large set of its own internal functions that are outside the scope of the SQL standard; listings of these functions for each vendor are provided at the end of each chapter.

In the case of MariaDB and MySQL, we will refer only to MySQL except in cases where MariaDB deviates from MySQL behavior.

Most database platforms support the ability to create user-defined functions (UDFs). For more information on UDFs, refer to Chapter 9. JSON and XML functions are covered in Chapter 10 and will not be discussed in this or the next chapter.