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

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