Joe Celko's SQL for Smarties, 4th Edition

Joe Celko's SQL for Smarties, 4th Edition

by Joe Celko
Released November 2010
Publisher(s): Morgan Kaufmann
ISBN: 9780123820235

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

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

Table of contents

  1. Cover image
  2. Title page
  3. Table of Contents
  4. Copyright
  5. Dedication
  6. About the Author
  7. Introduction to the Fourth Edition
    1. What Changed in Ten Years
    2. New in This Edition
    3. Corrections and Additions
  8. 1. Databases versus File Systems
    1. 1.1 Tables as Entities
    2. 1.2 Tables as Relationships
    3. 1.3 Rows versus Records
    4. 1.4 Columns versus Fields
    5. 1.5 Schema Objects
    6. 1.6 CREATE SCHEMA Statement
  9. 2. Transactions and Concurrency Control
    1. 2.1 Sessions
    2. 2.2 Transactions and ACID
    3. 2.3 Concurrency Control
    4. 2.4 Pessimistic Concurrency Control
    5. 2.5 SNAPSHOT Isolation and Optimistic Concurrency
    6. 2.6 Logical Concurrency Control
    7. 2.7 Deadlock and Livelocks
  10. 3. Schema Level Objects
    1. 3.1 CREATE SCHEMA Statement
    2. 3.2 CREATE PROCEDURE, CREATE FUNCTION, and CREATE TRIGGER
    3. 3.3 CREATE DOMAIN Statement
    4. 3.4 CREATE SEQUENCE
    5. 3.5 CREATE ASSERTION
    6. 3.6 Character Set Related Constructs
  11. 4. Locating Data and Special Numbers
    1. 4.1 Exposed Physical Locators
    2. 4.2 Generated Identifiers
    3. 4.3 Sequence Generator Functions
    4. 4.4 Preallocated Values
    5. 4.5 Special Series
  12. 5. Base Tables and Related Elements
    1. 5.1 CREATE TABLE Statement
    2. 5.2 Nested UNIQUE Constraints
    3. 5.3 CREATE ASSERTION Constraints
    4. 5.4 TEMPORARY Tables
    5. 5.5 Manipulating Tables
    6. 5.6 Avoiding Attribute Splitting
    7. 5.7 Modeling Class Hierarchies in DDL
    8. 5.8 Exposed Physical Locators
    9. 5.9 Auto-Incrementing Columns
    10. 5.10 Generated Identifiers
    11. 5.11 A Remark on Duplicate Rows
    12. 5.12 Other Schema Objects
    13. 5.13 Temporary Tables
    14. 5.14 CREATE DOMAIN Statement
    15. 5.15 CREATE TRIGGER Statement
    16. 5.16 CREATE PROCEDURE Statement
    17. 5.17 DECLARE CURSOR Statement
  13. 6. Procedural, Semiprocedural, and Declarative Programming
    1. 6.1 Basics of Software Engineering
    2. 6.2 Cohesion
    3. 6.3 Coupling
    4. 6.4 The Big Leap
    5. 6.5 Rewriting Tricks
    6. 6.6 Functions for Predicates
    7. 6.7 Procedural versus Logical Decomposition
  14. 7. Procedural Constructs
    1. 7.1 CREATE PROCEDURE
    2. 7.2 CREATE TRIGGER
    3. 7.3 CURSORs
    4. 7.4 SEQUENCEs
    5. 7.5 Generated Columns
    6. 7.6 Table Functions
  15. 8. Auxiliary Tables
    1. 8.1 The Series Table
    2. 8.2 Lookup Auxiliary Tables
    3. 8.3 Auxiliary Function Tables
    4. 8.4 Global Constants Tables
    5. 8.5 A Note on Converting Procedural Code to Tables
  16. 9. Normalization
    1. 9.1 Functional and Multivalued Dependencies
    2. 9.2 First Normal Form (1NF)
    3. 9.3 Second Normal Form (2NF)
    4. 9.4 Third Normal Form (3NF)
    5. 9.5 Elementary Key Normal Form (EKNF)
    6. 9.6 Boyce-Codd Normal Form (BCNF)
    7. 9.7 Fourth Normal Form (4NF)
    8. 9.8 Fifth Normal Form (5NF)
    9. 9.9 Domain-Key Normal Form (DKNF)
    10. 9.10 Practical Hints for Normalization
    11. 9.11 Key Types
    12. 9.12 Practical Hints for Denormalization
  17. 10. Numeric Data Types
    1. 10.1 Numeric Types
    2. 10.2 Numeric Type Conversion
    3. 10.3 Four Function Arithmetic
    4. 10.4 Arithmetic and NULLs
    5. 10.5 Converting Values to and from NULL
    6. 10.6 Mathematical Functions
    7. 10.7 Unique Value Generators
    8. 10.8 IP Addresses
  18. 11. Temporal Data Types
    1. 11.1 Notes on Calendar Standards
    2. 11.2 SQL Temporal Data Types
    3. 11.3 INTERVAL Data Types
    4. 11.4 Temporal Arithmetic
    5. 11.5 The Nature of Temporal Data Models
  19. 12. Character Data Types
    1. 12.1 Problems with SQL Strings
    2. 12.2 Standard String Functions
    3. 12.3 Common Vendor Extensions
    4. 12.4 Cutter Tables
    5. 12.5 Nested Replacement
  20. 13. NULLs: Missing Data in SQL
    1. 13.1 Empty and Missing Tables
    2. 13.2 Missing Values in Columns
    3. 13.3 Context and Missing Values
    4. 13.4 Comparing NULLs
    5. 13.5 NULLs and Logic
    6. 13.6 Math and NULLs
    7. 13.7 Functions and NULLs
    8. 13.8 NULLs and Host Languages
    9. 13.9 Design Advice for NULLs
    10. 13.10 A Note on Multiple NULL Values
  21. 14. Multiple Column Data Elements
    1. 14.1 Distance Functions
    2. 14.2 Storing an IPv4 Address in SQL
    3. 14.3 Storing an IPv6 Address in SQL
    4. 14.4 Currency and Other Unit Conversions
    5. 14.5 Social Security Numbers
    6. 14.6 Rational Numbers
  22. 15. Table Operations
    1. 15.1 DELETE FROM Statement
    2. 15.2 INSERT INTO Statement
    3. 15.3 The UPDATE Statement
    4. 15.4 A Note on Flaws in a Common Vendor Extension
    5. 15.5 MERGE Statement
  23. 16. Comparison or Theta Operators
    1. 16.1 Converting Data Types
    2. 16.2 Row Comparisons in SQL
    3. 16.3 IS [NOT] DISTINCT FROM Operator
  24. 17. Valued Predicates
    1. 17.1 IS NULL
    2. 17.2 IS [NOT]{TRUE | FALSE | UNKNOWN} Predicate
    3. 17.3 IS [NOT] NORMALIZED Predicate
  25. 18. CASE Expressions
    1. 18.1 The CASE Expression
    2. 18.2 Subquery Expressions and Constants
    3. 18.3 Rozenshtein Characteristic Functions
  26. 19. LIKE and SIMILAR TO Predicates
    1. 19.1 Tricks with Patterns
    2. 19.2 Results with NULL Values and Empty Strings
    3. 19.3 LIKE Is Not Equality
    4. 19.4 Avoiding the LIKE Predicate with a Join
    5. 19.5 CASE Expressions and LIKE Search Conditions
    6. 19.6 SIMILAR TO Predicates
    7. 19.7 Tricks with Strings
  27. 20. BETWEEN and OVERLAPS Predicates
    1. 20.1 The BETWEEN Predicate
    2. 20.2 OVERLAPS Predicate
  28. 21. The [NOT] IN() Predicate
    1. 21.1 Optimizing the IN() Predicate
    2. 21.2 Replacing ORs with the IN() Predicate
    3. 21.3 NULLs and the IN() Predicate
    4. 21.4 IN() Predicate and Referential Constraints
    5. 21.5 IN() Predicate and Scalar Queries
  29. 22. EXISTS() Predicate
    1. 22.1 EXISTS and NULLs
    2. 22.2 EXISTS and INNER JOINs
    3. 22.3 NOT EXISTS and OUTER JOINs
    4. 22.4 EXISTS() and Quantifiers
    5. 22.5 EXISTS() and Referential Constraints
    6. 22.6 EXISTS and Three-Valued Logic
  30. 23. Quantified Subquery Predicates
    1. 23.1 Scalar Subquery Comparisons
    2. 23.2 Quantifiers and Missing Data
    3. 23.3 The ALL Predicate and Extrema Functions
    4. 23.4 The UNIQUE Predicate
    5. 23.5 The DISTINCT Predicate
  31. 24. The Simple SELECT Statement
    1. 24.1 SELECT Statement Execution Order
    2. 24.2 One-Level SELECT Statement
  32. 25. Advanced SELECT Statements
    1. 25.1 Correlated Subqueries
    2. 25.2 Infixed INNER JOINs
    3. 25.3 OUTER JOINs
    4. 25.4 UNION JOIN Operators
    5. 25.5 Scalar SELECT Expressions
    6. 25.6 Old versus New JOIN Syntax
    7. 25.7 Constrained JOINs
    8. 25.8 Dr. Codd’s T-Join
    9. References
  33. 26. Virtual Tables: VIEWs, Derived Tables, CTEs, and MQTs
    1. 26.1 VIEWs in Queries
    2. 26.2 Updatable and Read-Only VIEWs
    3. 26.3 Types of VIEWs
    4. 26.4 How VIEWs Are Handled in the Database Engine
    5. 26.5 WITH CHECK OPTION Clause
    6. 26.6 Dropping VIEWs
    7. 26.7 Hints on Using VIEWs versus TEMPORARY TABLEs
    8. 26.8 Using Derived Tables
    9. 26.9 Common Table Expressions
    10. 26.10 Recursive Common Table Expressions
    11. 26.11 Materialized Query Tables
  34. 27. Partitioning Data in Queries
    1. 27.1 Coverings and Partitions
    2. 27.2 Relational Division
    3. 27.3 Romley’s Division
    4. 27.4 Boolean Expressions in an RDBMS
    5. 27.5 FIFO and LIFO Subsets
  35. 28. Grouping Operations
    1. 28.1 GROUP BY Clause
    2. 28.2 GROUP BY and HAVING
    3. 28.3 Multiple Aggregation Levels
    4. 28.4 Grouping on Computed Columns
    5. 28.5 Grouping into Pairs
    6. 28.6 Sorting and GROUP BY
  36. 29. Simple Aggregate Functions
    1. 29.1 COUNT() Functions
    2. 29.2 SUM() Function
    3. 29.3 AVG() Function
    4. 29.4 Extrema Functions
    5. 29.5 The LIST() Aggregate Function
    6. 29.6 The PRD() Aggregate Function
    7. 29.7 Bitwise Aggregate Functions
  37. 30. Advanced Grouping, Windowed Aggregation, and OLAP in SQL
    1. 30.1 Star Schema
    2. 30.2 GROUPING Operators
    3. 30.3 The Window Clause
    4. 30.4 Windowed Aggregate Functions
    5. 30.5 Ordinal Functions
    6. 30.6 Vendor Extensions
    7. 30.7 A Bit of History
  38. 31. Descriptive Statistics in SQL
    1. 31.1 The Mode
    2. 31.2 The AVG() Function
    3. 31.3 The Median
    4. 31.4 Variance and Standard Deviation
    5. 31.5 Average Deviation
    6. 31.6 Cumulative Statistics
    7. 31.7 Cross Tabulations
    8. 31.8 Harmonic Mean and Geometric Mean
    9. 31.9 Multivariable Descriptive Statistics in SQL
    10. 31.10 Statistical Functions in SQL:2006
  39. 32. Subsequences, Regions, Runs, Gaps, and Islands
    1. 32.1 Finding Subregions of Size (n)
    2. 32.2 Numbering Regions
    3. 32.3 Finding Regions of Maximum Size
    4. 32.4 Bound Queries
    5. 32.5 Run and Sequence Queries
    6. 32.6 Summation of a Series
    7. 32.7 Swapping and Sliding Values in a List
    8. 32.8 Condensing a List of Numbers
    9. 32.9 Folding a List of Numbers
    10. 32.10 Coverings
  40. 33. Matrices in SQL
    1. 33.1 Arrays via Named Columns
    2. 33.2 Arrays via Subscript Columns
    3. 33.3 Matrix Operations in SQL
    4. 33.4 Flattening a Table into an Array
    5. 33.5 Comparing Arrays in Table Format
  41. 34. Set Operations
    1. 34.1 UNION and UNION ALL
    2. 34.2 INTERSECT and EXCEPT
    3. 34.3 A Note on ALL and SELECT DISTINCT
    4. 34.4 Equality and Proper Subsets
  42. 35. Subsets
    1. 35.1 Every N-th Item in a Table
    2. 35.2 Random Rows from a Table
    3. 35.3 The CONTAINS Operators
    4. 35.4 Gaps in a Series
    5. 35.5 Covering for Overlapping Intervals
    6. 35.6 Picking a Representative Subset
  43. 36. Trees and Hierarchies in SQL
    1. 36.1 Adjacency List Model
    2. 36.2 The Path Enumeration Model
    3. 36.3 Nested Set Model of Hierarchies
    4. 36.4 Other Models for Trees and Hierarchies
  44. 37. Graphs in SQL
    1. 37.1 Adjacency List Model Graphs
    2. 37.2 Split Node Nested Set Models for Graphs
    3. 37.3 Points inside Polygons
    4. 37.4 Graph Theory References
  45. 38. Temporal Queries
    1. 38.1 Temporal Math
    2. 38.2 Personal Calendars
    3. 38.3 Time Series
    4. 38.4 Julian Dates
    5. 38.5 Other Temporal Functions
    6. 38.6 Weeks
    7. 38.7 Modeling Time in Tables
    8. 38.8 Calendar Auxiliary Table
    9. 38.9 Problems with the Year 2000
  46. 39. Optimizing SQL
    1. 39.1 Access Methods
    2. 39.2 How to Index
    3. 39.3 Give Extra Information
    4. 39.4 Index Multiple Columns Carefully
    5. 39.5 Watch the IN Predicate
    6. 39.6 Avoid UNIONs
    7. 39.7 Prefer Joins over Nested Queries
    8. 39.8 Use Fewer Statements
    9. 39.9 Avoid Sorting
    10. 39.10 Avoid CROSS JOINs
    11. 39.11 Know Your Optimizer
    12. 39.12 Recompile Static SQL after Schema Changes
    13. 39.13 Temporary Tables Are Sometimes Handy
    14. 39.14 Update Statistics
    15. 39.15 Do Not Trust Newer Features
  47. Index

Product information

  • Title: Joe Celko's SQL for Smarties, 4th Edition
  • Author(s): Joe Celko
  • Release date: November 2010
  • Publisher(s): Morgan Kaufmann
  • ISBN: 9780123820235