Joe Celko's SQL for Smarties

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 | FALSE | UNKNOWN} Predicate

17.3 IS [NOT] NORMALIZED Predicate

Chapter 18 CASE Expressions

18.1 The CASE Expression

18.2 Subquery Expressions and Constants

18.3 Rozenshtein Characteristic Functions

Chapter 19 LIKE and SIMILAR TO Predicates

19.1 Tricks with Patterns

19.2 Results with NULL Values and Empty Strings

19.3 LIKE Is Not Equality

19.4 Avoiding the LIKE Predicate with a Join

19.5 CASE Expressions and LIKE Search Conditions

19.6 SIMILAR TO Predicates

19.7 Tricks with Strings

Chapter 20 BETWEEN and OVERLAPS Predicates

20.1 The BETWEEN Predicate

20.2 OVERLAPS Predicate

Chapter 21 The [NOT] IN() Predicate

21.1 Optimizing the IN() Predicate

21.2 Replacing ORs with the IN() Predicate

21.3 NULLs and the IN() Predicate

21.4 IN() Predicate and Referential Constraints

21.5 IN() Predicate and Scalar Queries

Chapter 22 EXISTS() Predicate

22.1 EXISTS and NULLs

22.2 EXISTS and INNER JOINs

22.3 NOT EXISTS and OUTER JOINs

22.4 EXISTS() and Quantifiers

22.5 EXISTS() and Referential Constraints

22.6 EXISTS and Three-Valued Logic

Chapter 23 Quantified Subquery Predicates

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

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

38.2 Personal Calendars

38.3 Time Series

38.4 Julian Dates

38.5 Other Temporal Functions

38.6 Weeks

38.7 Modeling Time in Tables

38.8 Calendar Auxiliary Table

38.9 Problems with the Year 2000

Chapter 39 Optimizing SQL

39.1 Access Methods

39.2 How to Index

39.3 Give Extra Information

39.4 Index Multiple Columns Carefully

39.5 Watch the IN Predicate

39.6 Avoid UNIONs

39.7 Prefer Joins over Nested Queries

39.8 Use Fewer Statements

39.9 Avoid Sorting

39.10 Avoid CROSS JOINs

39.11 Know Your Optimizer

39.12 Recompile Static SQL after Schema Changes

39.13 Temporary Tables Are Sometimes Handy

39.14 Update Statistics

39.15 Do Not Trust Newer Features

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

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

Miscellaneous Citations

Index

Joe Celkos SQL for Smarties: Advanced SQL Programming offers tips and techniques in advanced programming. This book is the fourth edition and it consists of 39 chapters, starting with a comparison between databases and file systems. It covers transactions and currency control, schema level objects, locating data and schema numbers, base tables, and auxiliary tables. Furthermore, procedural, semi-procedural, and declarative programming are explored in this book.

The book also presents the different normal forms in database normalization, including the first, second, third, fourth, fifth, elementary key, domain-key, and Boyce-Codd normal forms. It also offers practical hints for normalization and denormalization. The book discusses different data types, such as the numeric, temporal and character data types; the different predicates; and the simple and advanced SELECT statements. In addition, the book presents virtual tables, and it discusses data partitions in queries; grouping operations; simple aggregate functions; and descriptive statistics, matrices and graphs in SQL. The book concludes with a discussion about optimizing SQL. It will be of great value to SQL programmers.

• Expert advice from a noted SQL authority and award-winning columnist who has given ten years service to the ANSI SQL standards committee
• Teaches scores of advanced techniques that can be used with any product, in any SQL environment, whether it is an SQL 92 or SQL 2008 environment
• Offers tips for working around deficiencies and gives insight into real-world challenges

This book is intended for working SQL programmers, database administrators, database designers, database analysts, and application system developers as well as those who are developing new features for database management systems who want to know about user needs. This would include anyone working with electronic content in the relational database context but also XML. Web services, etc.