Joe Celko's SQL for Smarties
3rd Edition
Advanced SQL Programming
Table of Contents
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
Description
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.
Key Features
- 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.
Readership
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.
Details
- No. of pages:
- 840
- Language:
- English
- Copyright:
- © Morgan Kaufmann 2005
- Published:
- 29th July 2005
- Imprint:
- Morgan Kaufmann
- eBook ISBN:
- 9780080460048
- Paperback ISBN:
- 9780123693792
Reviews
"This book is a classic, and this revision will merely solidify its position." --Rudy Limeback SQL for Smarties is a well-known and highly regarded text in the industry and a new edition of the book will be sought by database practitioners regardless of the DBMS they use. --Craig Mullins, BMC Software
Ratings and Reviews
About the Authors

Joe Celko Author
Joe Celko served 10 years on ANSI/ISO SQL Standards Committee and contributed to the SQL-89 and SQL-92 Standards.
Mr. Celko is author a series of books on SQL and RDBMS for Elsevier/MKP. He is an independent consultant based in Austin, Texas.
He has written over 1200 columns in the computer trade and academic press, mostly dealing with data and databases.
Affiliations and Expertise
Independent Consultant, Austin, Texas

Joe Celko Author
Joe Celko served 10 years on ANSI/ISO SQL Standards Committee and contributed to the SQL-89 and SQL-92 Standards.
Mr. Celko is author a series of books on SQL and RDBMS for Elsevier/MKP. He is an independent consultant based in Austin, Texas.
He has written over 1200 columns in the computer trade and academic press, mostly dealing with data and databases.
Affiliations and Expertise
Independent Consultant, Austin, Texas