Front Cover -- Series page -- Joe Celko's SQL for Smarties -- Copyright -- Dedication -- Table of Contents -- About the Author -- Introduction to the Fourth Edition -- Chapter 1. Databases versus File Systems -- 1.1 Tables as Entities -- 1.2 Tables as Relationships -- 1.3 Rows versus Records -- 1.4 Columns versus Fields -- 1.5 Schema Objects -- 1.6 CREATE SCHEMA Statement -- Chapter 2. Transactions and Concurrency Control -- 2.1 Sessions -- 2.2 Transactions and ACID -- 2.3 Concurrency Control -- 2.4 Pessimistic Concurrency Control -- 2.5 SNAPSHOT Isolation and Optimistic Concurrency -- 2.6 Logical Concurrency Control -- 2.7 Deadlock and Livelocks -- Chapter 3. Schema Level Objects -- 3.1 CREATE SCHEMA Statement -- 3.2 CREATE PROCEDURE, CREATE FUNCTION, and CREATE TRIGGER -- 3.3 CREATE DOMAIN Statement -- 3.4 CREATE SEQUENCE -- 3.5 CREATE ASSERTION -- 3.6 Character Set Related Constructs -- Chapter 4. Locating Data and Special Numbers -- 4.1 Exposed Physical Locators -- 4.2 Generated Identifiers -- 4.3 Sequence Generator Functions -- 4.4 Preallocated Values -- 4.5 Special Series -- Chapter 5. Base Tables and Related Elements -- 5.1 CREATE TABLE Statement -- 5.2 Nested UNIQUE Constraints -- 5.3 CREATE ASSERTION Constraints -- 5.4 TEMPORARY Tables -- 5.5 Manipulating Tables -- 5.6 Avoiding Attribute Splitting -- 5.7 Modeling Class Hierarchies in DDL -- 5.8 Exposed Physical Locators -- 5.9 Auto-Incrementing Columns -- 5.10 Generated Identifiers -- 5.11 A Remark on Duplicate Rows -- 5.12 Other Schema Objects -- 5.13 Temporary Tables -- 5.14 CREATE DOMAIN Statement -- 5.15 CREATE TRIGGER Statement -- 5.16 CREATE PROCEDURE Statement -- 5.17 DECLARE CURSOR Statement -- Chapter 6. Procedural, Semiprocedural, and Declarative Programming -- 6.1 Basics of Software Engineering -- 6.2 Cohesion -- 6.3 Coupling -- 6.4 The Big Leap -- 6.5 Rewriting Tricks.

6.6 Functions for Predicates -- 6.7 Procedural versus Logical Decomposition -- Chapter 7. Procedural Constructs -- 7.1 CREATE PROCEDURE -- 7.2 CREATE TRIGGER -- 7.3 CURSORs -- 7.4 SEQUENCEs -- 7.5 Generated Columns -- 7.6 Table Functions -- Chapter 8. Auxiliary Tables -- 8.1 The Series Table -- 8.2 Lookup Auxiliary Tables -- 8.3 Auxiliary Function Tables -- 8.4 Global Constants Tables -- 8.5 A Note on Converting Procedural Code to Tables -- Chapter 9. Normalization -- 9.1 Functional and Multivalued Dependencies -- 9.2 First Normal Form (1NF) -- 9.3 Second Normal Form (2NF) -- 9.4 Third Normal Form (3NF) -- 9.5 Elementary Key Normal Form (EKNF) -- 9.6 Boyce-Codd Normal Form (BCNF) -- 9.7 Fourth Normal Form (4NF) -- 9.8 Fifth Normal Form (5NF) -- 9.9 Domain-Key Normal Form (DKNF) -- 9.10 Practical Hints for Normalization -- 9.11 Key Types -- 9.12 Practical Hints for Denormalization -- Chapter 10. Numeric Data Types -- 10.1 Numeric Types -- 10.2 Numeric Type Conversion -- 10.3 Four Function Arithmetic -- 10.4 Arithmetic and NULLs -- 10.5 Converting Values to and from NULL -- 10.6 Mathematical Functions -- 10.7 Unique Value Generators -- 10.8 IP Addresses -- Chapter 11. Temporal Data Types -- 11.1 Notes on Calendar Standards -- 11.2 SQL Temporal Data Types -- 11.3 INTERVAL Data Types -- 11.4 Temporal Arithmetic -- 11.5 The Nature of Temporal Data Models -- Chapter 12. Character Data Types -- 12.1 Problems with SQL Strings -- 12.2 Standard String Functions -- 12.3 Common Vendor Extensions -- 12.4 Cutter Tables -- 12.5 Nested Replacement -- Chapter 13. NULLs: Missing Data in SQL -- 13.1 Empty and Missing Tables -- 13.2 Missing Values in Columns -- 13.3 Context and Missing Values -- 13.4 Comparing NULLs -- 13.5 NULLs and Logic -- 13.6 Math and NULLs -- 13.7 Functions and NULLs -- 13.8 NULLs and Host Languages -- 13.9 Design Advice for NULLs.

13.10 A Note on Multiple NULL Values -- Chapter 14. Multiple Column Data Elements -- 14.1 Distance Functions -- 14.2 Storing an IPv4 Address in SQL -- 14.3 Storing an IPv6 Address in SQL -- 14.4 Currency and Other Unit Conversions -- 14.5 Social Security Numbers -- 14.6 Rational Numbers -- Chapter 15. Table Operations -- 15.1 DELETE FROM Statement -- 15.2 INSERT INTO Statement -- 15.3 The UPDATE Statement -- 15.4 A Note on Flaws in a Common Vendor Extension -- 15.5 MERGE Statement -- Chapter 16. Comparison or Theta Operators -- 16.1 Converting Data Types -- 16.2 Row Comparisons in SQL -- 16.3 IS [NOT] DISTINCT FROM Operator -- Chapter 17. Valued Predicates -- 17.1 IS NULL -- 17.2 IS [NOT]{TRUE

23.1 Scalar Subquery Comparisons -- 23.2 Quantifiers and Missing Data -- 23.3 The ALL Predicate and Extrema Functions -- 23.4 The UNIQUE Predicate -- 23.5 The DISTINCT Predicate -- Chapter 24. The Simple SELECT Statement -- 24.1 SELECT Statement Execution Order -- 24.2 One-Level SELECT Statement -- Chapter 25. Advanced SELECT Statements -- 25.1 Correlated Subqueries -- 25.2 Infixed INNER JOINs -- 25.3 OUTER JOINs -- 25.4 UNION JOIN Operators -- 25.5 Scalar SELECT Expressions -- 25.6 Old versus New JOIN Syntax -- 25.7 Constrained JOINs -- 25.8 Dr. Codd's T-Join -- References -- Chapter 26. Virtual Tables: VIEWs, Derived Tables, CTEs, and MQTs -- 26.1 VIEWs in Queries -- 26.2 Updatable and Read-Only VIEWs -- 26.3 Types of VIEWs -- 26.4 How VIEWs Are Handled in the Database Engine -- 26.5 WITH CHECK OPTION Clause -- 26.6 Dropping VIEWs -- 26.7 Hints on Using VIEWs versus TEMPORARY TABLEs -- 26.8 Using Derived Tables -- 26.9 Common Table Expressions -- 26.10 Recursive Common Table Expressions -- 26.11 Materialized Query Tables -- Chapter 27. Partitioning Data in Queries -- 27.1 Coverings and Partitions -- 27.2 Relational Division -- 27.3 Romley's Division -- 27.4 Boolean Expressions in an RDBMS -- 27.5 FIFO and LIFO Subsets -- Chapter 28. Grouping Operations -- 28.1 GROUP BY Clause -- 28.2 GROUP BY and HAVING -- 28.3 Multiple Aggregation Levels -- 28.4 Grouping on Computed Columns -- 28.5 Grouping into Pairs -- 28.6 Sorting and GROUP BY -- Chapter 29. Simple Aggregate Functions -- 29.1 COUNT() Functions -- 29.2 SUM() Function -- 29.3 AVG() Function -- 29.4 Extrema Functions -- 29.5 The LIST() Aggregate Function -- 29.6 The PRD() Aggregate Function -- 29.7 Bitwise Aggregate Functions -- Chapter 30. Advanced Grouping, Windowed Aggregation, and OLAP in SQL -- 30.1 Star Schema -- 30.2 GROUPING Operators -- 30.3 The Window Clause.

30.4 Windowed Aggregate Functions -- 30.5 Ordinal Functions -- 30.6 Vendor Extensions -- 30.7 A Bit of History -- Chapter 31. Descriptive Statistics in SQL -- 31.1 The Mode -- 31.2 The AVG() Function -- 31.3 The Median -- 31.4 Variance and Standard Deviation -- 31.5 Average Deviation -- 31.6 Cumulative Statistics -- 31.7 Cross Tabulations -- 31.8 Harmonic Mean and Geometric Mean -- 31.9 Multivariable Descriptive Statistics in SQL -- 31.10 Statistical Functions in SQL:2006 -- Chapter 32. Subsequences, Regions, Runs, Gaps, and Islands -- 32.1 Finding Subregions of Size (n) -- 32.2 Numbering Regions -- 32.3 Finding Regions of Maximum Size -- 32.4 Bound Queries -- 32.5 Run and Sequence Queries -- 32.6 Summation of a Series -- 32.7 Swapping and Sliding Values in a List -- 32.8 Condensing a List of Numbers -- 32.9 Folding a List of Numbers -- 32.10 Coverings -- Chapter 33. Matrices in SQL -- 33.1 Arrays via Named Columns -- 33.2 Arrays via Subscript Columns -- 33.3 Matrix Operations in SQL -- 33.4 Flattening a Table into an Array -- 33.5 Comparing Arrays in Table Format -- Chapter 34. Set Operations -- 34.1 UNION and UNION ALL -- 34.2 INTERSECT and EXCEPT -- 34.3 A Note on ALL and SELECT DISTINCT -- 34.4 Equality and Proper Subsets -- Chapter 35. Subsets -- 35.1 Every N-th Item in a Table -- 35.2 Random Rows from a Table -- 35.3 The CONTAINS Operators -- 35.4 Gaps in a Series -- 35.5 Covering for Overlapping Intervals -- 35.6 Picking a Representative Subset -- Chapter 36. Trees and Hierarchies in SQL -- 36.1 Adjacency List Model -- 36.2 The Path Enumeration Model -- 36.3 Nested Set Model of Hierarchies -- 36.4 Other Models for Trees and Hierarchies -- Chapter 37. Graphs in SQL -- 37.1 Adjacency List Model Graphs -- 37.2 Split Node Nested Set Models for Graphs -- 37.3 Points inside Polygons -- 37.4 Graph Theory References -- Chapter 38. Temporal Queries.

38.1 Temporal Math.