Joe Celko's SQL for Smarties, 5th Edition

Joe Celko's SQL for Smarties, 5th Edition

by Joe Celko
Released December 2014
Publisher(s): Morgan Kaufmann
ISBN: 9780128008300

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book 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, 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
    • Extended coverage of descriptive statistic aggregate functions
    • New chapter covers flaws in DDL
    • Examination of traditional acid versus base transaction models
    • Reorganized to help you navigate related topics with ease
  • 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

  1. Cover image
  2. Title page
  3. Table of Contents
  4. Copyright
  5. Dedication
  6. Introduction to the Fifth Edition
    1. What is New in this Edition
    2. Corrections and Additions
  7. Part 1: Data Declaration Features
    1. Chapter 1: Databases Versus File Systems
      1. Abstract
      2. 1.1 The Schema Statement
      3. 1.2 Tables as Entities
      4. 1.3 Tables as Relationships
      5. 1.4 Rows Versus Records
      6. 1.5 Columns Versus Fields
    2. Chapter 2: Transactions and Concurrency Control
      1. Abstract
      2. 2.1 Sessions
      3. 2.2 Transactions and ACID
      4. 2.3 Concurrency Control
      5. 2.4 The Isolation Levels
      6. 2.5 Pessimistic Concurrency Control
      7. 2.6 SNAPSHOT Isolation Optimistic Concurrency
      8. 2.7 Logical Concurrency Control
      9. 2.8 CAP Theorem
      10. 2.9 BASE
      11. 2.10 Server-Side Consistency
      12. 2.11 Error Handling
      13. 2.12 Deadlock and Livelocks
    3. Chapter 3: Tables
      1. Abstract
      2. 3.1 CREATE TABLE Statements
      3. 3.2 Column Definitions
      4. 3.3 Computed Columns
      5. 3.4 [NOT] DEFERRABLE Constraints
      6. 3.5 CREATE DOMAIN and CREATE SEQUENCE
      7. 3.6 Character Set Related Constructs
    4. Chapter 4: Keys, Locators, and Generated Values
      1. Abstract
      2. 4.1 Key Types
      3. 4.2 Practical Hints for Denormalization
    5. Chapter 5: Normalization
      1. Abstract
      2. 5.1 Functional and Multivalued Dependencies
      3. 5.2 First Normal Form (1NF)
      4. 5.3 Second Normal Form (2NF)
      5. 5.4 Third Normal Form (3NF)
      6. 5.5 Elementary Key Normal Form (EKNF)
      7. 5.6 Boyce-Codd Normal Form (BCNF)
      8. 5.7 Fourth Normal Form (4NF)
      9. 5.8 Fifth Normal Form (5NF)
      10. 5.9 Domain-Key Normal Form (DKNF)
      11. 5.10 Practical Hints for Normalization
      12. 5.11 Non-Normal Form Redundancy
    6. Chapter 6: VIEWs, Derived, and Other Virtual Tables
      1. Abstract
      2. 6.1 VIEWs in Queries
      3. 6.2 Updatable and Read-Only VIEWs
      4. 6.3 Types of VIEWs
      5. 6.4 How VIEWs are Handled in the Database Engine
      6. 6.5 WITH CHECK OPTION Clause
      7. 6.6 Dropping VIEWs
      8. 6.7 Materialized Query Tables
    7. Chapter 7: Auxiliary Tables
      1. Abstract
      2. 7.1 Series Table
      3. 7.2 Look-up Auxiliary Tables
      4. 7.3 Advance Auxiliary Function Tables
      5. 7.4 Global Constants Tables
      6. 7.5 A Note on Converting Procedural Code to Tables
    8. Chapter 8: Other Schema Objects
      1. Abstract
      2. 8.1 CREATE SCHEMA Statement
      3. 8.2 Schema Tables
      4. 8.3 Temporary Tables
      5. 8.4 CREATE ASSERTION Statement
      6. 8.5 CREATE DOMAIN Statement
      7. 8.6 CREATE COLLATION Statement
      8. 8.7 CREATE TRANSLATION Statement
      9. 8.8 CREATE PROCEDURE Statement
      10. 8.9 TRIGGERs
      11. 8.10 The TRIGGER Model
    9. Chapter 9: DDL Flaws to Avoid
      1. Abstract
      2. 9.1 Tibbling and Related Errors
      3. 9.2 Attribute Splitting
      4. 9.3 Overloading Design Flaws
      5. 9.4 Non-Normal Form Redundancy
  8. Part 2: Data Types
    1. Chapter 10: Numeric Data in SQL
      1. Abstract
      2. 10.1 Exact Numeric Data Types
      3. 10.2 Approximate Numeric Data Types
      4. 10.3 Numeric Type Conversions
      5. 10.4 Four Function Arithmetic
      6. 10.5 Converting Values to and from NULL
      7. 10.6 Mathematical Functions
      8. 10.7 IP Addresses
    2. Chapter 11: Character Data Types in SQL
      1. Abstract
      2. 11.1 Problems with SQL Strings
      3. 11.2 Standard String Functions
      4. 11.3 Common Vendor Extensions
      5. 11.4 Cutter Tables
    3. Chapter 12: Temporal Data Types in SQL
      1. Abstract
      2. 12.1 Notes on Calendar Standards
      3. 12.2 The Nature of Temporal Data Models
      4. 12.3 SQL Temporal Data Types
      5. 12.4 INTERVAL Data Types
      6. 12.5 Queries with Date Arithmetic
      7. 12.6 Use of NULL for “Eternity”
      8. 12.7 The OVERLAPS() Predicate
      9. 12.8 State-Transition Constraints
      10. 12.9 Calendar Tables
    4. Chapter 13: Multiple Column Data Elements
      1. Abstract
      2. 13.1 Vector and Coordinate Data Elements
      3. 13.2 Hierarchical Data Elements
    5. Chapter 14: NULLs—Missing Data in SQL
      1. Abstract
      2. 14.1 Empty and Missing Tables
      3. 14.2 Missing Values in Columns
      4. 14.3 Context and Missing Values
      5. 14.4 Comparing NULLs
      6. 14.5 NULLs and Logic
      7. 14.6 Math and NULLs
      8. 14.7 Functions for NULLs
      9. 14.8 NULLs and Host Languages
      10. 14.9 Design Advice for NULLs
      11. 14.10 A Note on Multiple NULL Values
    6. Chapter 15: Table Operations
      1. Abstract
      2. 15.1 DELETE FROM Statement
      3. 15.2 INSERT INTO Statement
      4. 15.3 The UPDATE Statement
      5. 15.4 A Note on Flaws in a Common Vendor Extension
      6. 15.5 MERGE Statement
    7. Chapter 16: Set Operations
      1. Abstract
      2. 16.1 UNION and UNION ALL
      3. 16.2 INTERSECT and EXCEPT
      4. 16.3 A Note on ALL and SELECT DISTINCT
      5. 16.4 Equality and Proper Subsets
  9. Part 3: Row and Column Level Features
    1. Chapter 17: Comparison or Theta Operators
      1. Abstract
      2. 17.1 Converting Data Types
      3. 17.2 Row Comparisons in SQL
      4. 17.3 IS [NOT] DISTINCT FROM Operator
      5. 17.4 Monadic Operators
    2. Chapter 18: Subquery Predicates
      1. Abstract
      2. 18.1 The UNIQUE Predicate
      3. 18.2 The [NOT] IN() Predicate
      4. 18.3 [NOT] EXISTS() Predicate
      5. 18.4 < theta > [SOME | ANY] < subquery >
      6. 18.5 < theta > ALL < subquery >
    3. Chapter 19: BETWEEN and OVERLAPS Predicates
      1. Abstract
      2. 19.1 The BETWEEN Predicate
      3. 19.2 OVERLAPS Predicate
    4. Chapter 20: CASE Expression Family
      1. Abstract
      2. 20.1 CASE Expression
      3. 20.2 Subquery Expressions and Constants
    5. Chapter 21: LIKE and SIMILAR TO Predicates
      1. Abstract
      2. 21.1 Tricks with Patterns
      3. 21.2 Results with NULL Values and Empty Strings
      4. 21.3 LIKE is Not Equality
      5. 21.4 Extending the LIKE Predicate with a Join
      6. 21.5 CASE Expressions and LIKE Predicates
      7. 21.6 SIMILAR TO Predicates
      8. 21.7 Tricks with Strings
    6. Chapter 22: Basic SELECT Statement
      1. Abstract
      2. 22.1 CTEs
      3. 22.2 FROM Clause
      4. 22.3 WHERE Clause
      5. 22.4 GROUP BY Clause
      6. 22.5 HAVING Clause
      7. 22.6 SELECT Clause
      8. 22.7 ORDER BY Clause
      9. 22.8 Nested Query Expressions and Orthogonality
    7. Chapter 23: Basic Aggregate Functions
      1. Abstract
      2. 23.1 COUNT() Functions
      3. 23.2 SUM() Function
      4. 23.3 AVG() Function
      5. 23.4 Extrema Functions
      6. 23.5 The LIST() Aggregate Function
      7. 23.6 The Mode Aggregate Function
      8. 23.7 The Median Aggregate Function
      9. 23.8 The PRD() Aggregate Function
    8. Chapter 24: Advance Descriptive Statistics
      1. Abstract
      2. 24.1 Binary Table Functions
      3. 24.2 Correlation
    9. Chapter 25: OLAP Aggregation in SQL
      1. Abstract
      2. 25.1 Querying Versus Reporting
      3. 25.2 GROUPING Operators
      4. 25.3 The Window Clause
      5. 25.4 Windowed Aggregate Functions
      6. 25.5 Ordinal Functions
      7. 25.6 Vendor Extensions
      8. 25.7 A Bit of History
    10. Chapter 26: Advanced SELECT Statements
      1. Abstract
      2. 26.1 Correlated Subqueries
      3. 26.2 Infixed INNER JOINs
      4. 26.3 OUTER JOINs
      5. 26.4 UNION JOIN Operators
      6. 26.5 Scalar SELECT Expressions
      7. 26.6 Old Versus New JOIN Syntax
      8. 26.7 Constrained Joins
      9. 26.8 Dr. Codd's T-Join
      10. 26.9 Missing Values in Data
      11. 26.10 Missing and Mixed Data in Rows
  10. Part 4: Data Structures in SQL
    1. Chapter 27: Graphs in SQL
      1. Abstract
      2. 27.1 Basic Graph Characteristics
      3. 27.2 Paths in a Graph
      4. 27.3 Acyclic Graphs as Nested Sets
      5. 27.4 Adjacency Matrix Model
      6. 27.5 Points Inside Polygons
      7. 27.6 Taxicab Geometry
      8. 27.7 Equivalence Classes and Cliques
      9. 27.8 Conclusion
    2. Chapter 28: Trees and Hierarchies in SQL
      1. Abstract
      2. 28.1 Adjacency List Model
      3. 28.2 Finding the Root Node
      4. 28.3 Finding Leaf Nodes
      5. 28.4 Finding Levels in a Tree
      6. 28.5 Tree Operations
      7. 28.6 Nested Sets Model
      8. 28.7 Finding Root and Leaf Nodes
      9. 28.8 Finding Subtrees
      10. 28.9 Finding Levels and Paths in a Tree
      11. 28.10 Functions in the Nested Sets Model
      12. 28.11 Deleting Nodes and Subtrees
      13. 28.12 Summary Functions on Trees
      14. 28.13 Inserting and Updating Trees
      15. 28.14 Converting Adjacency List to Nested Sets Model
      16. 28.15 Converting Nested Sets to Adjacency List Model
      17. 28.16 Comparing Nodes and Structure
    3. Chapter 29: Queues
      1. Abstract
      2. 29.1 Basic DDL
      3. 29.2 Enqueue, Dequeue, and Empty Procedures
      4. 29.3 Rearrangement
      5. 29.4 Queues and Math
      6. 29.5 Priority Queues
      7. 29.6 FIFO and LIFO Queues
    4. Chapter 30: Matrices in SQL
      1. Abstract
      2. 30.1 Arrays via Named Columns
      3. 30.2 Arrays via Subscript Columns
      4. 30.3 Matrix Operations in SQL
      5. 30.4 Flattening a Table into an Array
      6. 30.5 Comparing Arrays in Table Format
      7. 30.6 Other Matrix Operations
  11. Part 5: Typical Queries
    1. Chapter 31: Partitioning and Aggregating Data in Queries
      1. Abstract
      2. 31.1 Coverings and Partitions
      3. 31.2 Advanced Grouping, Windowed Aggregation, and OLAP in SQL
    2. Chapter 32: Sub-sequences, Regions, Runs, Gaps, and Islands
      1. Abstract
      2. 32.1 Finding Subregions of Size (n)
      3. 32.2 Numbering Regions
      4. 32.3 Finding Regions of Maximum Size
      5. 32.4 Bound Queries
      6. 32.5 Run and Sequence Queries
      7. 32.6 Summation of a Handmade Series
      8. 32.7 Swapping and Sliding Values in a List
      9. 32.8 Condensing a List of Numbers
      10. 32.9 Folding a List of Numbers
      11. 32.10 Coverings
      12. 32.11 Equivalence Classes and Cliques
    3. Chapter 33: Auctions
      1. Abstract
      2. 33.1 General Types of Bidding
      3. 33.2 Types of Auctions
      4. 33.3 LIFO and FIFO Inventory
      5. 33.4 Bin Packing
    4. Chapter 34: Relational Division
      1. Abstract
      2. 34.1 Division with a Remainder
      3. 34.2 Exact Division
      4. 34.3 Note on Performance
      5. 34.4 Todd’s Division
      6. 34.5 Division with JOINs
      7. 34.6 Division with Set Operators
      8. 34.7 Romley’s Division
      9. 34.8 Boolean Expressions in Relational Division
    5. Chapter 35: Temporal Queries
      1. Abstract
      2. 35.1 Temporal Math
      3. 35.2 Calendars
      4. 35.3 Time Series
      5. 35.4 Julian Dates
      6. 35.5 Other Temporal Functions
      7. 35.6 Multi-day Periods
      8. 35.7 Modeling Time in Tables
      9. 35.8 LEAD() and LAG() Functions
      10. 35.9 Problems with the Year 2000: A Historical Overview
  12. Part 6: Implementation and Coding Issues
    1. Chapter 36: Procedural Semi-Procedural and Declarative Programming in SQL
      1. Abstract
      2. 36.1 Words Matter
      3. 36.2 Cleaning Code
    2. Chapter 37: Nesting Levels in SQL
      1. Abstract
      2. 37.1 Derived Tables
      3. 37.2 Column Naming Rules
      4. 37.3 Scoping Rules
      5. 37.4 Exposed Table Names
      6. 37.5 Common Table Expressions (CTEs)
      7. 37.6 LATERAL Tables
      8. 37.7 Programming Tips
    3. Chapter 38: Embedded SQL, CLI Dynamic SQL, and SQL/PSM
      1. Abstract
      2. 38.1 Embedded SQL
      3. 38.2 SQL/CLI
      4. 38.3 Dynamic SQL
      5. 38.4 SQL/PSM History
      6. 38.5 CSV Parameters
  13. Index

Product information

  • Title: Joe Celko's SQL for Smarties, 5th Edition
  • Author(s): Joe Celko
  • Release date: December 2014
  • Publisher(s): Morgan Kaufmann
  • ISBN: 9780128008300