Joe Celko's SQL for Smarties, 3rd Edition

Joe Celko's SQL for Smarties, 3rd Edition

by Joe Celko
Released July 2010
Publisher(s): Morgan Kaufmann
ISBN: 9780080460048

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, 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.

Table of contents

  1. Copyright
    1. Dedication
  2. The Morgan Kaufmann Series in Data Management Systems
  3. Introduction to the Third Edition
    1. What Changed in Ten Years
    2. What Is New in This Edition
    3. Corrections and Additions
  4. Database Design
    1. Schema and Table Creation
    2. Generating Unique Sequential Numbers for Keys
    3. A Remark on Duplicate Rows
    4. Other Schema Objects
  5. Normalization
    1. Functional and Multivalued Dependencies
    2. First Normal Form (1NF)
    3. Second Normal Form (2NF)
    4. Third Normal Form (3NF)
    5. Elementary Key Normal Form (EKNF)
    6. Boyce-Codd Normal Form (BCNF)
    7. Fourth Normal Form (4NF)
    8. Fifth Normal Form (5NF)
    9. Domain-Key Normal Form (DKNF)
    10. Practical Hints for Normalization
    11. Key Types
  6. Numeric Data in SQL
    1. Numeric Types
    2. Numeric Type Conversion
    3. Four-Function Arithmetic
    4. Arithmetic and NULLs
    5. Converting Values to and from NULL
    6. Vendor Math Functions
  7. Temporal Data Types in SQL
    1. Notes on Calendar Standards
    2. SQL Temporal Data Types
    3. Queries with Date Arithmetic
    4. The Nature of Temporal Data Models
  8. Character Data Types in SQL
    1. Problems with SQL Strings
    2. Standard String Functions
    3. Common Vendor Extensions
    4. Cutter Tables
  9. NULLs: Missing Data in SQL
    1. Empty and Missing Tables
    2. Missing Values in Columns
    3. Context and Missing Values
    4. Comparing NULLs
    5. NULLs and Logic
    6. Math and NULLs
    7. Functions and NULLs
    8. NULLs and Host Languages
    9. Design Advice for NULLs
    10. A Note on Multiple NULL Values
  10. Multiple Column Data Elements
    1. Distance Functions
    2. Storing an IP Address in SQL
    3. Currency and Other Unit Conversions
    4. Social Security Numbers
    5. Rational Numbers
  11. Table Operations
    1. DELETE FROM Statement
    2. INSERT INTO Statement
    3. The UPDATE Statement
    4. A Note on Flaws in a Common Vendor Extension
    5. MERGE Statement
  12. Comparison or Theta Operators
    1. Converting Data Types
    2. Row Comparisons in SQL
  13. Valued Predicates
    1. IS NULL Predicate
    2. IS [NOT]{TRUE | FALSE | UNKNOWN} Predicate
    3. IS [NOT] NORMALIZED Predicate
  14. CASE Expressions
    1. The CASE Expression
    2. Rozenshtein Characteristic Functions
  15. LIKE Predicate
    1. Tricks with Patterns
    2. Results with NULL Values and Empty Strings
    3. LIKE Is Not Equality
    4. Avoiding the LIKE Predicate with a Join
    5. CASE Expressions and LIKE Predicates
    6. SIMILAR TO Predicates
    7. Tricks with Strings
  16. BETWEEN and OVERLAPS Predicates
    1. The BETWEEN Predicate
    2. OVERLAPS Predicate
  17. The [NOT] IN() Predicate
    1. Optimizing the IN() Predicate
    2. Replacing ORs with the IN() Predicate
    3. NULLs and the IN() Predicate
    4. IN() Predicate and Referential Constraints
    5. IN() Predicate and Scalar Queries
  18. EXISTS() Predicate
    1. EXISTS and NULLs
    2. EXISTS and INNER JOINs
    3. NOT EXISTS and OUTER JOINs
    4. EXISTS() and Quantifiers
    5. EXISTS() and Referential Constraints
    6. EXISTS and Three-Valued Logic
  19. Quantified Subquery Predicates
    1. Scalar Subquery Comparisons
    2. Quantifiers and Missing Data
    3. The ALL Predicate and Extrema Functions
    4. The UNIQUE Predicate
  20. The SELECT Statement
    1. SELECT and JOINs
    2. OUTER JOINs
    3. Old versus New JOIN Syntax
    4. Scope of Derived Table Names
    5. JOINs by Function Calls
    6. The UNION JOIN
    7. Packing Joins
    8. Dr. Codd’s T-Join
  21. VIEWs, Derived Tables, Materialized Tables, and Temporary Tables
    1. VIEWs in Queries
    2. Updatable and Read-Only VIEWs
    3. Types of VIEWs
    4. How VIEWs Are Handled in the Database System
    5. WITH CHECK OPTION Clause
    6. Dropping VIEWs
    7. TEMPORARY TABLE Declarations
    8. Hints on Using VIEWs and TEMPORARY TABLEs
    9. Using Derived Tables
    10. Derived Tables in the WITH Clause
  22. Partitioning Data in Queries
    1. Coverings and Partitions
    2. Relational Division
    3. Romley’s Division
    4. Boolean Expressions in an RDBMS
    5. FIFO and LIFO Subsets
  23. Grouping Operations
    1. GROUP BY Clause
    2. GROUP BY and HAVING
    3. Multiple Aggregation Levels
    4. Grouping on Computed Columns
    5. Grouping into Pairs
    6. Sorting and GROUP BY
  24. Aggregate Functions
    1. COUNT() Functions
    2. SUM() Functions
    3. AVG() Functions
    4. Extrema Functions
    5. The LIST() Aggregate Function
    6. The PRD() Aggregate Function
    7. Bitwise Aggregate Functions
  25. Auxiliary Tables
    1. The Sequence Table
    2. Lookup Auxiliary Tables
    3. Auxiliary Function Tables
    4. Global Constants Tables
  26. Statistics in SQL
    1. The Mode
    2. The AVG() Function
    3. The Median
    4. Variance and Standard Deviation
    5. Average Deviation
    6. Cumulative Statistics
    7. Cross Tabulations
    8. Harmonic Mean and Geometric Mean
    9. Multivariable Descriptive Statistics in SQL
  27. Regions, Runs, Gaps, Sequences, and Series
    1. Finding Subregions of Size (n)
    2. Numbering Regions
    3. Finding Regions of Maximum Size
    4. Bound Queries
    5. Run and Sequence Queries
    6. Summation of a Series
    7. Swapping and Sliding Values in a List
    8. Condensing a List of Numbers
    9. Folding a List of Numbers
    10. Coverings
  28. Arrays in SQL
    1. Arrays via Named Columns
    2. Arrays via Subscript Columns
    3. Matrix Operations in SQL
    4. Flattening a Table into an Array
    5. Comparing Arrays in Table Format
  29. Set Operations
    1. UNION and UNION ALL
    2. INTERSECT and EXCEPT
    3. A Note on ALL and SELECT DISTINCT
    4. Equality and Proper Subsets
  30. Subsets
    1. Every nth Item in a Table
    2. Picking Random Rows from a Table
    3. The CONTAINS Operators
    4. Picking a Representative Subset
  31. Trees and Hierarchies in SQL
    1. Adjacency List Model
    2. The Path Enumeration Model
    3. Nested Set Model of Hierarchies
    4. Other Models for Trees and Hierarchies
  32. Temporal Queries
    1. Temporal Math
    2. Personal Calendars
    3. Time Series
    4. Julian Dates
    5. Date and Time Extraction Functions
    6. Other Temporal Functions
    7. Weeks
    8. Modeling Time in Tables
    9. Calendar Auxiliary Table
    10. Problems with the Year 2000
  33. Graphs in SQL
    1. Basic Graph Characteristics
    2. Paths in a Graph
    3. Acyclic Graphs as Nested Sets
    4. Paths with CTE
    5. Adjacency Matrix Model
    6. Points inside Polygons
  34. OLAP in SQL
    1. Star Schema
    2. OLAP Functionality
    3. A Bit of History
  35. Transactions and Concurrency Control
    1. Sessions
    2. Transactions and ACID
    3. Concurrency Control
    4. Pessimistic Concurrency Control
    5. SNAPSHOT Isolation: Optimistic Concurrency
    6. Logical Concurrency Control
    7. Deadlock and Livelocks
  36. Optimizing SQL
    1. Access Methods
    2. Expressions and Unnested Queries
    3. Give Extra Join Information in Queries
    4. Index Tables Carefully
    5. Watch the IN Predicate
    6. Avoid UNIONs
    7. Prefer Joins over Nested Queries
    8. Avoid Expressions on Indexed Columns
    9. Avoid Sorting
    10. Avoid CROSS JOINs
    11. Learn to Use Indexes Carefully
    12. Order Indexes Carefully
    13. Know Your Optimizer
    14. Recompile Static SQL after Schema Changes
    15. Temporary Tables Are Sometimes Handy
    16. Update Statistics
  37. References
    1. General References
    2. Logic
    3. Mathematical Techniques
    4. Random Numbers
    5. Scales and Measurements
    6. Missing Values
    7. Regular Expressions
    8. Graph Theory
    9. Introductory SQL Books
    10. Optimizing Queries
    11. Temporal Data and the Year 2000 Problem
    12. SQL Programming Techniques
    13. Classics
    14. Forum
    15. Updatable Views
    16. Theory, Normalization, and Advanced Database Topics
    17. Books on SQL-92 and SQL-99
    18. Standards and Related Groups
    19. Web Sites Related to SQL
    20. Statistics
    21. Temporal Databases
    22. New Citations
  38. About the Author
  39. Index

Product information

  • Title: Joe Celko's SQL for Smarties, 3rd Edition
  • Author(s): Joe Celko
  • Release date: July 2010
  • Publisher(s): Morgan Kaufmann
  • ISBN: 9780080460048