SQL for Smarties

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, 20 years later and in its fifth edition, this classic reference still reigns supreme as the only book written by a SQL master that teaches programmers and practitioners to become SQL masters themselves! These are not just tips and techniques; also offered are the best solutions to old and new challenges. Joe Celko conveys the way you need to think in order to get the most out of SQL programming efforts for both correctness and performance. New to the fifth edition, Joe features new examples to reflect the ANSI/ISO Standards so anyone can use it. He also updates data element names to meet new ISO-11179 rules with the same experience-based teaching style that made the previous editions the classics they are today. You will learn new ways to write common queries, such as finding coverings, partitions, runs in data, auctions and inventory, relational divisions and so forth. SQL for Smarties explains some of the principles of SQL programming as well as the code. A new chapter discusses design flaws in DDL, such as attribute splitting, non-normal forum redundancies and tibbling. There is a look at the traditional acid versus base transaction models, now popular in NoSQL products. You’ll learn about computed columns and the DEFERRABLE options in constraints. An overview of the bi-temporal model is new to this edition and there is a longer discussion about descriptive statistic aggregate functions. The book finishes with an overview of SQL/PSM that is applicable to proprietary 4GL vendor extensions.

• New to the 5th Edition:
• Overview of the bitemporal model
• Expert advice from a noted SQL authority and award-winning columnist Joe Celko, who served on the ANSI SQL standards committee for over a decade
• Teaches scores of advanced techniques that can be used with any product, in any SQL environment, whether it is SQL 92 or SQL 2011
• Offers tips for working around deficiencies and gives insight into real-world challenges

Table of Contents

Part 1: Data Declaration Features

Chapter 1: Databases Versus File Systems

Chapter 2: Transactions and Concurrency Control

Chapter 3: Tables

Chapter 4: Keys, Locators, and Generated Values

Chapter 5: Normalization

Chapter 6: VIEWs, Derived, and Other Virtual Tables

Chapter 7: Auxiliary Tables

Chapter 8: Other Schema Objects

Chapter 9: DDL Flaws to Avoid

Part 2: Data Types

Chapter 10: Numeric Data in SQL

Chapter 11: Character Data Types in SQL

Chapter 12: Temporal Data Types in SQL

Chapter 13: Multiple Column Data Elements

Chapter 14: NULL s- Missing Data in SQL

Chapter 15: Table Operations

Chapter 16: Set Operations

Part 3: Row and Column Level Features

Chapter 17: Comparison or Theta Operators

Chapter 18: Subquery Predicates

Chapter 19: BETWEEN and OVERLAPS Predicates

Chapter 20: CASE Expression Family

Chapter 21: LIKE and SIMILAR TO Predicates

Chapter 22: Basic SELECT Statement

Chapter 23: Basic Aggregate Functions

Chapter 24: Advance Descriptive Statistics

Chapter 25: OLAP Aggregation in SQL

Chapter 26: Advanced SELECT Statements

Part 4: Data Structures in SQL

Chapter 27: Graphs in SQL

Chapter 28: Trees and Hierarchies in SQL

Chapter 29: Queues

Chapter 30: Matrices in SQL

Part 5: Typical Queries

Chapter 31: Partitioning and Aggregating Data in Queries

Chapter 32: Sub-sequences, Regions, Runs, Gaps, and Islands

Chapter 33: Auctions

Chapter 34: Relational Division

Chapter 35: Temporal Queries

Part 6: Implementation and Coding Issues

Chapter 36: Procedural Semi-Procedural and Declarative Programming in SQL

Chapter 37: Nesting Levels in SQL

Chapter 38: Embedded SQL, CLI Dynamic SQL, and SQL/PSM

About the Author

Joe Celko

I was a member of the ANSI X3H2 Database Standards Committee from 1987 to 1997 and helped write the ANSI/ISO SQL-89 and SQL-92 Standards. I have nine books and have written over 1200 columns in the computer trade and academic press, mostly dealing with data and databases.

I live in Austin,TX. When I am not writing, I am consulting, speaking at conferences, teaching SQL training classes anywhere on Earth or beating up newbies in SQL Newsgroups.

The rumor that I own only one black suit that I have worn for 30 years is false; I own six identical black suits that I have worn for 30 years.