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

Informationen zum Autor Joe Celko served 10 years on ANSI/ISO SQL Standards Committee and contributed to the SQL-89 and SQL-92 Standards. Mr. Celko is author a series of books on SQL and RDBMS for Elsevier/MKP. He is an independent consultant based in Austin, Texas. He has written over 1200 columns in the computer trade and academic press, mostly dealing with data and databases. Through detailed examples and clear explanations, Celko guides programmers to conceptualize database problems as rooted in sets. The book focuses on three table-based SQL techniques that not only demonstrate this set characteristic of SQL, but also dramatically simplify application development. Zusammenfassung Perfectly intelligent programmers often struggle when forced to work with SQL. Why? This title intends to change the way you think about the problems you solve with SQL programs. Focusing on three key table-based techniques, it reveals their power through detailed examples and explanations.

Autorentext
Joe Celko served 10 years on ANSI/ISO SQL Standards Committee and contributed to the SQL-89 and SQL-92 Standards. Mr. Celko is author a series of books on SQL and RDBMS for Elsevier/MKP. He is an independent consultant based in Austin, Texas. He has written over 1200 columns in the computer trade and academic press, mostly dealing with data and databases.

Klappentext
Through detailed examples and clear explanations, Celko guides programmers to conceptualize database problems as rooted in sets. The book focuses on three table-based SQL techniques that not only demonstrate this set characteristic of SQL, but also dramatically simplify application development.

Zusammenfassung
Perfectly intelligent programmers often struggle when forced to work with SQL. Why? This title intends to change the way you think about the problems you solve with SQL programs. Focusing on three key table-based techniques, it reveals their power through detailed examples and explanations.

Inhalt

0. Introduction 1. SQL is Declarative, Not Procedural 1.1. Different Programming Model 1.2. Different Data Model 1.2.1. Tables are not Files 1.2.2. Rows are not Records 1.2.3. Columns are not Fields 1.2.4. Statements are not Procedures 1.3. Hardware Now Makes Handling Volume Data Easier 1.3.1. Parallelism - why it is best for sets 1.3.2. Cheap Main Storage 1.3.3. Cheaper Secondary Storage 2. Auxiliary Tables - define 2.1. Sequence Table 2.1.1. As Loop Replacement 2.1.2. From Recursive CTE 2.1.3. Display Functions 2.2. Basic Look-Up Tables - Display & Decode 2.2.1. Constant Tables 2.2.2. OTLT or MUCK Table Problems 2.3. Functions 2.3.1. Not All Functions are Computable (see calendar) 2.3.1.1. Encryption via Tables 2.3.1.2. Random Numbers 2.3.2. Join Parallelism can be faster than Computations 2.4. Interpolation for Unknown Values 2.4.1. Linear Interpolation 2.4.2. Non-Linear Interpolation (First and Second Deltas) 2.5. Check Digits via Tables 2.5.1. Weighted Summation Algorithm 2.5.2. Diherdal-5 Algorithm 2.6. Data Mining for Auxiliary Tables 2.6.1. Corky's BBQ Story 3. Temporal Tables 3.1. The Nature of Time - 3.1.1. Durations & Continuum, not Chronons 3.1.2. Granularity, not a True Continuum 3.2. State Transition Tables 3.3. ISO Half-Open Interval Model 3.3.1. Use of NULL for "Eternity" or "Now" 3.3.2. Temporal Table Constraints 3.3.2.1. Primary Key + timestamp 3.3.2.2. Preventing Overlapping Intervals 3.4. Consolidating Overlapping Intervals 3.4.1. Cursors 3.4.2. Triggers 3.4.3. OLAP and Recursive CTE Code 3.5. Calendar Tables 3.5.1. Basic Calendar Table 3.5.2. Other Temporal Data Table Tricks 3.6. Holiday Lists 3.7. Report Periods 3.7.1. Overlapping Periods 3.7.2. Self-Updating Views 3.8. History Tables 3.8.1. Basic History Table 3.8.2. Audit Trails are Kept outside of the Schema 4. Virtual Tables 4.1. Derived Tables 4.1.1. Column Naming Rules 4.1.2. Scoping Rules 4.2. CTE - Common Table Expressions 4.2.1. Recursive 4.2.2. Non-Recursive 4.3. Views 4.3.1. Basic Concepts 4.3.2. Nesting Levels 4.3.3. WITH CHECK OPTION 4.4. Temporary Tables - avoid them 4.4.1. ANSI/ISO Standards 4.4.2. Vendors Models 4.5. Schema Information Tables 4.5.1. ANSI/ISO Standards versus Vendors 4.5.2. Use in Code Generation 4.6 Materialized Views