O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL

Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL

by Joe Celko
Publisher: Morgan Kaufmann
Release Date: January 2008
ISBN: 9780080557526
View table of contents
Start reading

Book Description

Perfectly intelligent programmers often struggle when forced to work with SQL. Why? Joe Celko believes the problem lies with their procedural programming mindset, which keeps them from taking full advantage of the power of declarative languages. The result is overly complex and inefficient code, not to mention lost productivity.

This book will change the way you think about the problems you solve with SQL programs.. Focusing on three key table-based techniques, Celko reveals their power through detailed examples and clear explanations. As you master these techniques, you’ll find you are able to conceptualize problems as rooted in sets and solvable through declarative programming. Before long, you’ll be coding more quickly, writing more efficient code, and applying the full power of SQL

• Filled with the insights of one of the world’s leading SQL authorities - noted for his knowledge and his ability to teach what he knows.

• Focuses on auxiliary tables (for computing functions and other values by joins), temporal tables (for temporal queries, historical data, and audit information), and virtual tables (for improved performance).

• Presents clear guidance for selecting and correctly applying the right table technique.

Table of Contents

  1. Copyright
    1. Dedication
  2. The Morgan Kaufmann Series in Data Management Systems
  3. Preface
    1. Auxiliary, Temporal, and Virtual Tables
    2. Corrections and Additions
  4. SQL Is Declarative, Not Procedural
    1. Different Programming Models
    2. Different Data Models
    3. Tables as Entities
    4. Tables as Relationships
    5. Statements Are Not Procedures
    6. Molecular, Atomic, and Subatomic Data Elements
  5. Hardware, Data Volume, and Maintaining Databases
    1. Parallelism
    2. Cheap Main Storage
    3. Solid-State Disk
    4. Cheaper Secondary and Tertiary Storage
    5. The Data Changed
    6. The Mindset Has Not Changed
  6. Data Access and Records
    1. Sequential Access
    2. Indexes
    3. Hashing
    4. Bit Vector Indexes
    5. Parallel Access
    6. Row and Column Storage
    7. JOIN Algorithms
  7. Lookup Tables
    1. Data Element Names
    2. Multiparameter Lookup Tables
    3. Constants Table
    4. OTLT or MUCK Table Problems
    5. Definition of a Proper Table
  8. Auxiliary Tables
    1. Sequence Table
    2. Permutations
    3. Functions
    4. Encryption via Tables
    5. Random Numbers
    6. Interpolation
  9. Views
    1. Mullins VIEW Usage Rules
    2. Updatable and Read-Only VIEWs
    3. Types of VIEWs
    4. Modeling Classes with Tables
    5. How VIEWs Are Handled in the Database System
    6. In-Line Text Expansion
    7. WITH CHECK OPTION Clause
    8. Dropping VIEWs
    9. Outdated Uses for VIEWs
  10. Virtual Tables
    1. Derived Tables
    2. Common Table Expressions
    3. Temporary Tables
    4. The Information Schema
  11. Complicated Functions via Tables
    1. Functions without a Simple Formula
    2. Check Digits via Tables
    3. Classes of Algorithms
    4. Declarations, Not Functions, Not Procedures
    5. Data Mining for Auxiliary Tables
  12. Temporal Tables
    1. The Nature of Time
    2. The ISO Half-Open Interval Model
    3. State Transition Tables
    4. Consolidating Intervals
    5. Calendar Tables
    6. History Tables
  13. Scrubbing Data with Non-1 N F Tables
    1. Repeated Groups
    2. Designing Scrubbing Tables
    3. Scrubbing Constraints
    4. Calendar Scrubs
    5. String Scrubbing
    6. Sharing SQL Data
    7. Extract, Transform, and Load Products
  14. Thinking in SQL
    1. Warm-up Exercises
    2. Heuristics
    3. Do Not Use Bit Or Boolean Flags In Sql
  15. Group Characteristics
    1. Grouping Is Not Equality
    2. Grouping over Time
    3. Other Tricks with HAVING Clauses
    4. Groupings, Rollups, and Cubes
    5. The WINDOW Clause
  16. Turning Specifications into Code
    1. Signs of Bad SQL
    2. Methods of Attack
    3. Translating Vague Specifications
  17. Using Procedure and Function Calls
    1. Clearing out Spaces in a String
    2. The PRD() Aggregate Function
    3. Long Parameter Lists in Procedures and Functions
  18. Numbering Rows
    1. Procedural Solutions
    2. OLAP Functions
    3. Sections
  19. Keeping Computed Data
    1. Procedural Solution
    2. Relational Solution
    3. Other Kinds of Computed Data
  20. Triggers for Constraints
    1. Triggers for Computations
    2. Complex Constraints via CHECK() and CASE Constraints
    3. Complex Constraints via VIEWs
    4. Operations on VIEWs as Constraints
  21. Procedural and Data-Driven Solutions
    1. Removing Letters in a String
    2. Two Approaches to Sudoku
    3. Data Constraint Approach
    4. Bin Packing Problems
    5. Inventory Costs over Time