SQL-99 Complete, Really

Note

You are reading a digital copy of SQL-99 Complete, Really, a book that documents the SQL-99 standard.

For more information specific to CrateDB, check out the CrateDB Reference.

Table of Contents

• Preface
  • Who Should Read this Book?
  • What’s In It?
    • World’s Longest SQL Poem (for obvious reasons)
    • Why Read It?
    • Further Information
• Chapters
  • Chapter 1 – Introduction
    • How To Read This Book
    • How to Read SQL Syntax
    • What is SQL?
    • SQL Conformance
    • SQL Statement Classes
    • SQL Rule Evaluation Order
  • Chapter 2 – General Concepts
    • Set Theory
    • Recap: the Relational Model
    • The SQL-Environment
    • SQL Objects
    • SQL Data Types
    • SQL Language Elements
  • Chapter 3 – Numbers
    • Numeric <literal>s
    • Numeric <data type>s
    • IEEE Binary Floats
    • Numeric Operations
  • Chapter 4 – Bit strings
    • <bit string literal>s
    • Bit string <data type>s
    • Bit Operations
  • Chapter 5 – Binary strings
    • <BLOB literal>s
    • BLOB <data type>s
    • BLOB Operations
    • Comparison
  • Chapter 6 – Characters
    • What is a Character?
    • What’s in it for Me?
    • Predefined Character sets
  • Chapter 7 – Character strings
    • Character string <literal>s
    • Character string <data type>s
    • Character String Operations
    • Character Strings and Character Sets
    • Character Strings and Collations
    • Dialects
  • Chapter 8 – Temporal values
    • Some Preliminaries
    • Time Zone Offset Arithmetic
    • Temporal <literal>s
    • Temporal <data type>s
    • Dialects
    • The SQL Library
  • Chapter 9 – Boolean values
    • <Boolean literal>s
    • Boolean <data type>s
    • Boolean Operations
  • Chapter 10 – Collection types
    • Collection <data type>s
    • Collection Operations
    • Comprehensive Example
  • Chapter 11 – Row types
    • Row <data type>s
    • Row Operations
    • Comprehensive Example
  • Chapter 12 – Reference types
    • Reference <data type>s
    • Reference Operations
  • Chapter 13 – NULLs
    • Representing Missing Data with NULL
    • The Meaning of NULL
    • Three-Valued Logic
    • The Duplicate Loophole
    • Fun with NULLs
    • Problems For Optimizers
    • Nulloclasts vs. Nullodules
  • Chapter 14 – SQL Cluster
    • Cluster
  • Chapter 15 – SQL Authorizationid
    • <AuthorizationID>
    • CREATE ROLE Statement
    • Privilege
    • GRANT statement
    • Data Control
    • REVOKE Statement
    • DROP ROLE Statement
    • What privileges do I have?
    • Violating the security system
    • User Functions
    • Dialects
  • Chapter 16 – SQL Catalogs
    • Catalog
    • Catalog Names:
    • The Information Schema
  • Chapter 17 – SQL Schema
    • Schema
    • CREATE SCHEMA Statement
    • DROP SCHEMA Statement
  • Chapter 18 – SQL Table and View
    • Base Table
    • Subtables and Supertables
    • Table names
    • Column
    • CREATE TABLE Statement
    • <Column definition>
    • <default clause>
    • ALTER TABLE Statement
    • DROP TABLE statement
    • CREATE VIEW Statement
    • Getting More Out Of Views
    • DROP VIEW Statement
    • DECLARE TABLE Statement
    • Dialects
  • Chapter 19 – SQL Domain
    • Domain
    • CREATE DOMAIN Statement
    • ALTER DOMAIN Statement
    • DROP DOMAIN Statement
    • Frequently-used numeric Domains
  • Chapter 20 – SQL Constraint and Assertion
    • Constraint
    • Constraint Descriptors
    • Constraint Definition
    • Constraint_type – UNIQUE Constraint
    • Constraint_type – PRIMARY KEY Constraint
    • Constraint_type – FOREIGN KEY Constraint
    • Constraint_type – NOT NULL Constraint
    • Constraint_type – CHECK Constraint
    • CREATE ASSERTION Statement
    • Interlocking References
    • Dropping Constraints
    • DROP ASSERTION Statement
    • Dialects
  • Chapter 21 – SQL Character Set
    • Character Set
    • CREATE CHARACTER SET Statement
    • DROP CHARACTER SET Statement
  • Chapter 22 – SQL Collation
    • Collation
    • CREATE COLLATION Statement
    • DROP COLLATION Statement
  • Chapter 23 – SQL Translation
    • Translation
    • CREATE TRANSLATION Statement
    • DROP TRANSLATION Statement
  • Chapter 24 – SQL Trigger
    • Trigger
    • CREATE TRIGGER Statement
    • Activation of Triggers
    • Trigger Examples
    • Triggers versus Constraints
    • DROP TRIGGER Statement
    • Dialects
  • Chapter 25 – SQL-invoked Routine
    • Routine
    • CREATE PROCEDURE/FUNCTION Statement
    • Routine Parameters
    • Invoking Routines
    • Routine Examples
    • RETURN Statement
    • External Routines
    • ALTER ROUTINE/PROCEDURE/FUNCTION/METHOD Statement
    • DROP ROUTINE/PROCEDURE/FUNCTION Statement
    • Dialects
  • Chapter 26 – PSM: Not just Persistent Stored Modules
    • Persistent Stored Modules
    • CREATE MODULE Statement
    • ALTER MODULE Statement
    • DROP MODULE Statement
    • BEGIN … END: compound Statement
    • SIGNAL Statement
    • RESIGNAL Statement
    • Should everything be in SQL?
    • Dialects
  • Chapter 27 – User-defined Types
    • UDTs
    • UDT Example
    • Columns Based on UDTs
    • Defining a Typed Table Based on a UDT
    • CREATE TYPE Statement
    • CREATE TABLE Statement
    • CREATE CAST Statement
    • CREATE ORDERING Statement
    • Other Processes for Object/Relational Users
    • Is object/relational really object-oriented?
    • Dialects
  • Chapter 28 – Introduction to SQL-data operations
    • <value specification>
    • <value expression>
    • <row value constructor>
    • <target specification>
  • Chapter 29 – Simple Search Conditions
    • Truth Values
    • SELECT Statement
    • Predicates
    • Search Conditions
    • SQL’s <case expression>
    • Dialects
  • Chapter 30 – Searching with Joins
    • Joined Tables
    • Syntax Rules
    • Dialects
  • Chapter 31 – Searching with Subqueries
    • Subquery Syntax
    • Scalar Subqueries
    • Row Subqueries
    • Table Subqueries
    • Quantified Comparisons
    • Predicates
    • Joins versus Subqueries
    • Subquery Examples
    • Subquery Tips
    • Dialects
  • Chapter 32 – Searching with Set Operators
    • <query expression>
    • Set Operation Syntax
    • Result Names and ORDER BY
    • Result <data type>s and Compatibility
    • Set Operation Examples
    • Updatability
    • Recursive Unions
    • Dialects
  • Chapter 33 – Searching with Groups
    • GROUP BY Clause
    • Set Functions
    • HAVING Clause
    • Views of Groups
    • Dialects
  • Chapter 34 – Sorting Search Results
    • ORDER BY Clause
    • Dialects
  • Chapter 35 – Changing SQL-data
    • The SQL-data Change Statements
    • INSERT Statement
    • UPDATE Statement
    • DELETE Statement
    • Data Change Operations
    • Dialects
  • Chapter 36 – SQL Transactions
    • Initiating Transactions
    • Terminating Transactions
    • Using Savepoints
    • Transaction Tips
    • See Also
    • Dialects
  • Chapter 37 – SQL Transaction Concurrency
    • Isolation Phenomena
    • Pessimistic Concurrency: LOCKING
    • Optimistic Concurrency: TIMESTAMPING
    • SET TRANSACTION statement
    • START TRANSACTION Statement
    • Special Problems
    • Transactions and Constraint Checking
    • Dialects
    • Goodies
  • Chapter 38 – SQL Sessions
    • SQL-Connections
    • SQL-Session Management
  • Chapter 39 – Embedded SQL Binding Style
    • Binding Styles
    • What is embedded SQL?
    • Precompilers
    • SQL Prefixes and Terminators
    • Host Variables
    • Cursors
    • Embedded SQL Examples
    • Diagnostics
    • Dynamic SQL
    • Summary
    • Dialects
  • Chapter 40 – SQL/CLI Binding Style
    • EXAMPLE1.C
    • SQLCHAR, SQLINTEGER and Other Typedefs
    • SQLRETURN
    • Handle Relationships
    • How to Run Example Programs
    • “Standard SQL CLI” Equals “Core ODBC API”
    • How Each CLI Function Will Be Described
    • CharacterStringRetrieval
  • Chapter 41 – SQL/CLI: env Functions
    • Null Termination
    • SQLAllocHandle(SQL_HANDLE_ENV,…)
    • SQLAllocEnv
    • SQLGetEnvAttr
    • SQLSetEnvAttr
    • SQLFreeHandle(SQL_HANDLE_ENV,…)
    • SQLFreeEnv
  • Chapter 42 – SQL/CLI: dbc Functions
    • SQLAllocHandle(SQL_HANDLE_DBC,…)
    • SQLAllocConnect
    • SQLConnect
    • SQLDisconnect
    • SQLGetConnectAttr
    • SQLSetConnectAttr
    • SQLFreeHandle(SQL_HANDLE_DBC,…)
    • SQLFreeConnect
  • Chapter 43 – SQL/CLI: stmt Functions
    • SQLAllocHandle(SQL_HANDLE_STMT,…)
    • SQLAllocStmt
    • SQLGetStmtAttr
    • SQLSetStmtAttr
    • SQLFreeHandle(SQL_HANDLE_STMT,…)
    • SQLFreeStmt
  • Chapter 44 – SQL/CLI Statement Functions
    • Preparable SQL Statements
    • SQLPrepare
    • SQLExecute
    • SQLExecDirect
    • SQLEndTran
  • Chapter 45 – SQL/CLI: Cursor Functions
    • SQLFetch
    • SQLFetchScroll
    • SQLCloseCursor
    • SQLGetCursorName
    • SQLSetCursorName
    • Embedded SQL versus CLI
    • SQLMoreResults
  • Chapter 46 – SQL/CLI: desc Functions
    • Descriptor Areas
    • The desc Fields
    • The desc Functions
  • Chapter 47 – SQL/CLI: Diagnostic Functions
    • SQLGetDiagField
    • SQLGetDiagRec
    • SQLError
    • SQLRowCount
    • SQLSTATE Codes
  • Chapter 48 – SQL/CLI: General Functions
    • SQLDataSources
    • SQLGetFunctions
    • SQLGetInfo
  • Chapter 49 – SQL/CLI: Deferred Parameter Functions
    • How to Pass Deferred Parameters
    • SQLParamData
    • SQLPutData
    • SQLCancel
  • Chapter 50 – SQL/CLI: Locator Functions
    • What is a Locator?
    • SQLGetLength
    • SQLGetPosition
    • SQLGetSubstring
  • Chapter 51 – SQL/CLI: Catalog Functions
    • Some necessary preliminaries
    • SQLColumnPrivileges
    • SQLColumns
    • SQLForeignKeys
    • SQLGetTypeInfo
    • SQLParameters
    • SQLPrimaryKeys
    • SQLRoutinePrivileges
    • SQLRoutines
    • SQLSpecialColumns
    • SQLTablePrivileges
    • SQLTables
    • THE END
  • Chapter 52 – Module Binding Style
    • SQL-client Modules
    • MODULE Statement
    • PROCEDURE Statement
  • Chapter 53 – Style
    • Authority
    • Layout Rules
    • Naming Rules
    • Examples of Statements in Formal Style
    • Host Language Programs
    • Summary
• Appendices
  • Appendix A – Remote Database Access
    • ISO/IEC 9759
    • TCP/IP
    • The Client/Server Model
    • A Short Session
    • Making it hard
    • What good is RDA?
  • Appendix B – SQL Taxonomy
    • Core SQL Notes
    • Taxonomy Tables
  • Appendix C – Non-portable SQL Features
    • Implementation-Defined Features
    • Implementation-Dependent Features
  • Appendix D – Incompatibilities with SQL-92
  • Appendix E – SQL Web Sites
  • Appendix F – Glossary
    • Glossary Entries
  • Appendix G – Errata
    • SQL Foundation
    • SQL/CLI