Joe Celko's SQL for Smarties

Dedication

Introduction to the Third Edition

1.1 What Changed in Ten Years

1.2 What Is New in This Edition

1.3 Corrections and Additions

Chapter 1: Database Design

1.1 Schema and Table Creation

1.2 Generating Unique Sequential Numbers for Keys

1.3 A Remark on Duplicate Rows

1.4 Other Schema Objects

Chapter 2: Normalization

2.1 Functional and Multivalued Dependencies

2.2 First Normal Form (1NF)

2.3 Second Normal Form (2NF)

2.4 Third Normal Form (3NF)

2.5 Elementary Key Normal Form (EKNF)

2.6 Boyce-Codd Normal Form (BCNF)

2.7 Fourth Normal Form (4NF)

2.8 Fifth Normal Form (5NF)

2.9 Domain-Key Normal Form (DKNF)

2.10 Practical Hints for Normalization

2.11 Key Types

Chapter 3: Numeric Data in SQL

3.1 Numeric Types

3.1.1 BIT, BYTE, and BOOLEAN Data Types

3.2 Numeric Type Conversion

3.3 Four-Function Arithmetic

3.4 Arithmetic and NULLs

3.5 Converting Values to and from NULL

3.6 Vendor Math Functions

Chapter 4: Temporal Data Types in SQL

4.1 Notes on Calendar Standards

4.2 SQL Temporal Data Types

4.3 Queries with Date Arithmetic

4.4 The Nature of Temporal Data Models

Chapter 5: Character Data Types in SQL

5.1 Problems with SQL Strings

5.2 Standard String Functions

5.3 Common Vendor Extensions

5.4 Cutter Tables

Chapter 6: NULLs

6.1 Empty and Missing Tables

6.2 Missing Values in Columns

6.3 Context and Missing Values

6.5 NULLs and Logic

6.6 Math and NULLs

6.7 Functions and NULLs

6.8 NULLs and Host Languages

6.9 Design Advice for NULLs

6.10 A Note on Multiple NULL Values

Chapter 7: Multiple Column Data Elements

7.1 Distance Functions

7.2 Storing an IP Address in SQL

7.3 Currency and Other Unit Conversions

7.4 Social Security Numbers

7.5 Rational Numbers

Chapter 8: Table Operations

8.1 Delete From Statement

8.2 Insert into Statement

8.3 The Update Statement

8.4 A Note on Flaws in a Common Vendor Extension

8.5 Merge Statement

Chapter 9: Comparison or Theta Operators

9.1 Converting Data Types

9.2 Row Comparisons in SQL

Chapter 10: Valued Predicates

10.1 IS NULL Predicate

10.2 IS [NOT]{TRUE | FALSE | UNKNOWN} Predicate

10.3 IS [NOT] NORMALIZED Predicate

Chapter 11: CASE Expressions

11.1 The CASE Expression

11.2 Rozenshtein Characteristic Functions

Chapter 12: LIKE Predicate

12.1 Tricks with Patterns

12.2 Results with NULL Values and Empty Strings

12.3 LIKE Is Not Equality

12.4 Avoiding the LIKE Predicate with a Join

12.5 CASE Expressions and LIKE Predicates

12.6 SIMILAR TO Predicates

12.7 Tricks with Strings

Chapter 13: Between and Overlaps Predicates

13.1 The BETWEEN Predicate

13.2 OVERLAPS Predicate

Chapter 14: The [NOT] IN() predicate

14.1 Optimizing the IN() Predicate

14.2 Replacing ORs with the IN() Predicate

14.3 NULLs and the IN() Predicate

14.4 IN() Predicate and Referential Constraints

14.5 IN() Predicate and Scalar Queries

Chapter 15: EXISTS() predicate

15.1 EXISTS and NULLs

15.2 EXISTS and INNER JOINs

15.3 NOT EXISTS and OUTER JOINs

15.4 EXISTS() and Quantifiers

15.5 EXISTS() and Referential Constraints

15.6 EXISTS and Three-Valued Logic

Chapter 16: Quantified Subquery Predicates

16.1 Scalar Subquery Comparisons

16.2 Quantifiers and Missing Data

16.3 The ALL Predicate and Extrema Functions

16.4 The UNIQUE Predicate

Chapter 17: The SELECT Statement

17.1 SELECT and JOINs

17.2 OUTER JOINs

17.3 Old versus New JOIN Syntax

17.4 Scope of Derived Table Names

17.5 JOINs by Function Calls

17.6 The UNION JOIN

17.7 Packing Joins

17.8 Dr. Codd’sT-Join

Chapter 18: VIEWs, Derived Tables, Materialized Tables, and Temporary Tables

18.1 VlEWs in Queries

18.2 Updatable and Read-Only VIEWs

18.3 Types of VIEWs

18.4 How VIEWs Are Handled in the Database System

18.5 WITH CHECK OPTION Clause

18.6 Dropping VIEWs

18.7 TEMPORARY TABLE Declarations

18.8 Hints on Using VIEWs and TEMPORARY TABLEs

18.9 Using Derived Tables

18.10 Derived Tables in the WITH Clause

Chapter 19: Partitioning Data in Queries

19.1 Coverings and Partitions

19.2 Relational Division

19.3 Romley’s Division

19.4 Boolean Expressions in an RDBMS

19.5 FIFO and LIFO Subsets

Chapter 20: Grouping Operations

20.1 GROUP BY Clause

20.2 GROUP BY and HAVING

20.3 Multiple Aggregation Levels

20.4 Grouping on Computed Columns

20.5 Grouping into Pairs

20.6 Sorting and GROUP BY

Chapter 21: Aggregate Functions

21.1 COUNT() Functions

21.2 SUM() Functions

21.3 AVG() Functions

21.4 Extrema Functions

21.5 The LIST() Aggregate Function

21.6 The PRD() Aggregate Function

21.7 Bitwise Aggregate Functions

Chapter 22: Auxiliary Tables

22.1 The Sequence Table

22.2 Lookup Auxiliary Tables

22.3 Auxiliary Function Tables

22.4 Global Constants Tables

Chapter 23: Statistics in SQL

23.1 The Mode

23.2 The AVG() Function

23.3 The Median

23.4 Variance and Standard Deviation

23.5 Average Deviation

23.6 Cumulative Statistics

23.7 Cross Tabulations

23.8 Harmonic Mean and Geometric Mean

23.9 Multivariate Descriptive Statistics in SQL

Chapter 24: Regions, Runs, Gaps, Sequences, and Series

24.2 Numbering Regions

24.3 Finding Regions of Maximum Size

24.4 Bound Queries

24.5 Run and Sequence Queries

24.6 Summation of a Series

24.7 Swapping and Sliding Values in a List

24.8 Condensing a List of Numbers

24.9 Folding a List of Numbers

24.10 Coverings

Chapter 25: Arrays in SQL

25.1 Arrays via Named Columns

25.2 Arrays via Subscript Columns

25.3 Matrix Operations in SQL

25.4 Flattening a Table into an Array

25.5 Comparing Arrays in Table Format

Chapter 26: Set Operations

26.1 UNION and UNION ALL

26.2 INTERSECT and EXCEPT

26.3 A Note on ALL and SELECT DISTINCT

26.4 Equality and Proper Subsets

Chapter 27: Subsets

27.1 Every nth Item in a Table

27.2 Picking Random Rows from a Table

27.3 The CONTAINS Operators

27.4 Picking a Representative Subset

Chapter 28: Trees and Hierarchies in SQL

28.1 Adjacency List Model

28.2 The Path Enumeration Model

28.3 Nested Set Model of Hierarchies

28.4 Other Models for Trees and Hierarchies

Chapter 29: Temporal Queries

29.1 Temporal Math

29.2 Personal Calendars

29.3 Time Series

29.4 Julian Dates

29.5 Date and Time Extraction Functions

29.6 Other Temporal Functions

29.7 Weeks

29.8 Modeling Time in Tables

29.9 Calendar Auxiliary Table

29.10 Problems with the Year 2000

Chapter 30: Graphs in SQL

30.1 Basic Graph Characteristics

30.2 Paths in a Graph

30.3 Acyclic Graphs as Nested Sets

30.4 Paths with CTE

30.5 Adjacency Matrix Model

30.6 Points inside Polygons

Chapter 31: OLAP in SQL

31.1 Star Schema

31.2 OLAP Functionality

31.3 A Bit of History

Chapter 32: Transactions and Concurrency Control

32.1 Sessions

32.2 Transactions and ACID

32.3 Concurrency Control

32.5 SNAPSHOT Isolation: Optimistic Concurrency

32.6 Logical Concurrency Control

32.7 Deadlock and Livelocks

Chapter 33: Optimizing SQL

33.1 Access Methods

33.2 Expressions and Unnested Queries

33.3 Give Extra join Information in Queries

33.4 Index Tables Carefully

33.5 Watch the IN Predicate

33.6 Avoid UNIONS

33.7 Prefer Joins over Nested Queries

33.8 Avoid Expressions on Indexed Columns

33.9 Avoid Sorting

33.10 Avoid CROSS JOINs

33.11 Learn to Use Indexes Carefully

33.12 Order Indexes Carefully

33.13 Know Your Optimizer

33.14 Recompile Static SQL after Schema Changes

33.15 Temporary Tables Are Sometimes Handy

33.16 Update Statistics

References

General References

Logic

Mathematical Techniques

Random Numbers

Scales and Measurements

Missing Values

Regular Expressions

Graph Theory

Introductory SQL Books

Optimizing Queries

Temporal Data and the Year 2000 Problem

SQL Programming Techniques

Classics

Forum

Updatable VIEWs

Theory, Normalization, and Advanced Database Topics

Books on SQL-92 and SQL-99

Standards and Related Groups

Web Sites Related to SQL

Statistics

Temporal Databases

Index

About the Author

SQL for Smarties was hailed as the first book devoted explicitly to the advanced techniques needed to transform an experienced SQL programmer into an expert. Now, 10 years later and in the third edition, this classic still reigns supreme as the book written by an SQL master that teaches future SQL masters. These are not just tips and techniques; Joe also offers the best solutions to old and new challenges and conveys the way you need to think in order to get the most out of SQL programming efforts for both correctness and performance.

In the third edition, Joe features new examples and updates to SQL-99, expanded sections of Query techniques, and a new section on schema design, with the same war-story teaching style that made the first and second editions of this book classics.

• Expert advice from a noted SQL authority and award-winning columnist, who has given ten years of service to the ANSI SQL standards committee and many more years of dependable help to readers of online forums.
• Teaches scores of advanced techniques that can be used with any product, in any SQL environment, whether it is an SQL-92 or SQL-99 environment.
• Offers tips for working around system deficiencies.
• Continues to use war stories--updated!--that give insights into real-world SQL programming challenges.

Working SQL programmers, including those application developers, database analysts, and others who want to pick up some advanced programming tips and techniques, and also those who are developing new features for DBMSs who want to know about users' ideas and needs.