Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL: Auxiliary, Temporal, and Virtual Tables in SQL
Regular price
12.000 KD
inc. VAT
Couldn't load pickup availability
Table of contents
- Cover
- Contentsvii
- Prefacexvii
- 1. SQL Is Declarative, Not Procedural1
- 1.1 Different Programming Models2
- 1.2 Different Data Models4
- 1.2.1 Columns Are Not Fields5
- 1.2.2 Rows Are Not Records7
- 1.2.3 Tables Are Not Files11
- 1.2.4 Relational Keys Are Not Record Locators13
- 1.2.5 Kinds of Keys15
- 1.2.6 Desirable Properties of Relational Keys17
- 1.2.7 Unique But Not Invariant18
- 1.3 Tables as Entities19
- 1.4 Tables as Relationships20
- 1.5 Statements Are Not Procedures20
- 1.6 Molecular, Atomic, and Subatomic Data Elements21
- 1.6.1 Table Splitting21
- 1.6.2 Column Splitting22
- 1.6.3 Temporal Splitting24
- 1.6.4 Faking Non-1NF Data24
- 1.6.5 Molecular Data Elements25
- 1.6.6 Isomer Data Elements26
- 1.6.7 Validating a Molecule27
- 2. Hardware, Data Volume, and Maintaining Databases29
- 2.1 Parallelism30
- 2.2 Cheap Main Storage31
- 2.3 Solid-State Disk32
- 2.4 Cheaper Secondary and Tertiary Storage32
- 2.5 The Data Changed33
- 2.6 The Mindset Has Not Changed33
- 3. Data Access and Records37
- 3.1 Sequential Access38
- 3.1.1 Tape-Searching Algorithms38
- 3.2 Indexes39
- 3.2.1 Single-Table Indexes40
- 3.2.2 Multiple-Table Indexes40
- 3.2.3 Type of Indexes41
- 3.3 Hashing41
- 3.3.1 Digit Selection42
- 3.3.2 Division Hashing42
- 3.3.3 Multiplication Hashing42
- 3.3.4 Folding42
- 3.3.5 Table Lookups43
- 3.3.6 Collisions43
- 3.4 Bit Vector Indexes44
- 3.5 Parallel Access44
- 3.6 Row and Column Storage44
- 3.6.1 Row-Based Storage44
- 3.6.2 Column-Based Storage45
- 3.7 JOIN Algorithms46
- 3.7.1 Nested-Loop Join Algorithm47
- 3.7.2 Sort-Merge Join Method47
- 3.7.3 Hash Join Method48
- 3.7.4 Shin’s Algorithm48
- 4. Lookup Tables51
- 4.1 Data Element Names52
- 4.2 Multiparameter Lookup Tables55
- 4.3 Constants Table56
- 4.4 OTLT or MUCK Table Problems59
- 4.5 Definition of a Proper Table62
- 5. Auxiliary Tables65
- 5.1 Sequence Table65
- 5.1.1 Creating a Sequence Table67
- 5.1.2 Sequence Constructor68
- 5.1.3 Replacing an Iterative Loop69
- 5.2 Permutations72
- 5.2.1 Permutations via Recursion72
- 5.2.2 Permutations via CROSS JOIN73
- 5.3 Functions75
- 5.3.1 Functions without a Simple Formula76
- 5.4 Encryption via Tables78
- 5.5 Random Numbers79
- 5.6 Interpolation83
- 6. Views87
- 6.1 Mullins VIEW Usage Rules88
- 6.1.1 Effi cient Access and Computations88
- 6.1.2 Column Renaming89
- 6.1.3 Proliferation Avoidance90
- 6.1.4 The VIEW Synchronization Rule90
- 6.2 Updatable and Read-Only VIEWs91
- 6.3 Types of VIEWs93
- 6.3.1 Single-Table Projection and Restriction93
- 6.3.2 Calculated Columns93
- 6.3.3 Translated Columns94
- 6.3.4 Grouped VIEWs95
- 6.3.5 UNIONed VIEWs96
- 6.3.6 JOINs in VIEWs98
- 6.3.7 Nested VIEWs98
- 6.4 Modeling Classes with Tables100
- 6.4.1 Class Hierarchies in SQL100
- 6.4.2 Subclasses via ASSERTIONs and TRIGGERs103
- 6.5 How VIEWs Are Handled in the Database System103
- 6.5.1 VIEW Column List104
- 6.5.2 VIEW Materialization104
- 6.6 In-Line Text Expansion105
- 6.7 WITH CHECK OPTION Clause106
- 6.7.1 WITH CHECK OPTION as CHECK( ) Clause110
- 6.8 Dropping VIEWs112
- 6.9 Outdated Uses for VIEWs113
- 6.9.1 Domain Support113
- 6.9.2 Table Expression VIEWs114
- 6.9.3 VIEWs for Table Level CHECK( ) Constraints114
- 6.9.4 One VIEW per Base Table115
- 7. Virtual Tables117
- 7.1 Derived Tables118
- 7.1.1 Column Naming Rules118
- 7.1.2 Scoping Rules119
- 7.1.3 Exposed Table Names121
- 7.1.4 LATERAL() Clause122
- 7.2 Common Table Expressions124
- 7.2.1 Nonrecursive CTEs124
- 7.2.2 Recursive CTEs126
- 7.3 Temporary Tables128
- 7.3.1 ANSI/ISO Standards128
- 7.3.2 Vendors Models128
- 7.4 The Information Schema129
- 7.4.1 The INFORMATION_SCHEMA Declarations130
- 7.4.2 A Quick List of VIEWS and Their Purposes130
- 7.4.3 DOMAIN Declarations132
- 7.4.4 Defi nition Schema132
- 7.4.5 INFORMATION_SCHEMA Assertions135
- 8. Complicated Functions via Tables137
- 8.1 Functions without a Simple Formula137
- 8.1.1 Encryption via Tables138
- 8.2 Check Digits via Tables139
- 8.2.1 Check Digits Defi ned139
- 8.2.2 Error Detection versus Error Correction140
- 8.3 Classes of Algorithms141
- 8.3.1 Weighted-Sum Algorithms141
- 8.3.2 Power-Sum Check Digits144
- 8.3.3 Luhn Algorithm145
- 8.3.4 Dihedral Five Check Digit146
- 8.4 Declarations, Not Functions, Not Procedures148
- 8.5 Data Mining for Auxiliary Tables152
- 9. Temporal Tables155
- 9.1 The Nature of Time155
- 9.1.1 Durations, Not Chronons156
- 9.1.2 Granularity158
- 9.2 The ISO Half-Open Interval Model159
- 9.2.1 Use of NULL for EternityŽ161
- 9.2.2 Single Timestamp Tables162
- 9.2.3 Overlapping Intervals164
- 9.3 State Transition Tables174
- 9.4 Consolidating Intervals178
- 9.4.1 Cursors and Triggers180
- 9.4.2 OLAP Function Solution181
- 9.4.3 CTE Solution181
- 9.5 Calendar Tables182
- 9.5.1 Day of Week via Tables183
- 9.5.2 Holiday Lists184
- 9.5.3 Report Periods186
- 9.5.4 Self-Updating Views186
- 9.6 History Tables188
- 9.6.1 Audit Trails189
- 10. Scrubbing Data with Non-1NF Tables191
- 10.1 Repeated Groups192
- 10.1.1 Sorting within a Repeated Group195
- 10.2 Designing Scrubbing Tables198
- 10.3 Scrubbing Constraints201
- 10.4 Calendar Scrubs202
- 10.4.1 Special Dates203
- 10.5 String Scrubbing204
- 10.6 Sharing SQL Data205
- 10.6.1 A Look at Data Evolution206
- 10.6.2 Databases207
- 10.7 Extract, Transform, and Load Products208
- 10.7.1 Loading Data Warehouses209
- 10.7.2 Doing It All in SQL211
- 10.7.3 Extract, Load, and then Transform212
- 11. Thinking in SQL215
- 11.1 Warm-up Exercises216
- 11.1.1 The Whole and Not the Parts216
- 11.1.2 Characteristic Functions217
- 11.1.3 Locking into a Solution Early219
- 11.2 Heuristics220
- 11.2.1 Put the Specifi cation into a Clear Statement220
- 11.2.2 Add the Words Set of AllƒŽ in Front of the Nouns220
- 11.2.3 Remove Active Verbs from the Problem Statement221
- 11.2.4 You Can Still Use Stubs221
- 11.2.5 Do Not Worry about Displaying the Data222
- 11.2.6 Your First Attempts Need Special Handling223
- 11.2.7 Do Not Be Afraid to Throw Away Your First Attempts at DDL223
- 11.2.8 Save Your First Attempts at DML224
- 11.2.9 Do Not Think with Boxes and Arrows225
- 11.2.10 Draw Circles and Set Diagrams225
- 11.2.11 Learn Your Dialect226
- 11.2.12 Imagine that Your WHERE Clause Is Super AmoebaŽ227
- 11.2.13 Use the Newsgroups, Blogs, and Internet228
- 11.3 Do Not Use BIT or BOOLEAN Flags in SQL228
- 11.3.1 Flags Are at the Wrong Level229
- 11.3.2 Flags Confuse Proper Attributes230
- 12. Group Characteristics235
- 12.1 Grouping Is Not Equality237
- 12.2 Using Groups without Looking Inside238
- 12.2.1 Semiset-Oriented Approach239
- 12.2.2 Grouped Solutions240
- 12.2.3 Aggregated Solutions241
- 12.3 Grouping over Time242
- 12.3.1 Piece-by-Piece Solution243
- 12.3.2 Data as a Whole Solution244
- 12.4 Other Tricks with HAVING Clauses245
- 12.5 Groupings, Rollups, and Cubes247
- 12.5.1 GROUPING SET Clause247
- 12.5.2 The ROLLUP Clause249
- 12.5.3 The CUBE Clause249
- 12.5.4 A Footnote about Super Grouping250
- 12.6 The WINDOW Clause250
- 12.6.1 The PARTITION BY Clause251
- 12.6.2 The ORDER BY Clause252
- 12.6.3 The RANGE Clause253
- 12.6.4 Programming Tricks253
- 13. Turning Specifications into Code255
- 13.1 Signs of Bad SQL255
- 13.1.1 Is the Code Formatted Like Another Language?256
- 13.1.2 Assuming Sequential Access256
- 13.1.3 Cursors256
- 13.1.4 Poor Cohesion257
- 13.1.5 Table-Valued Functions257
- 13.1.6 Multiple Names for the Same Data Element258
- 13.1.7 Formatting in the Database258
- 13.1.8 Keeping Dates in Strings259
- 13.1.9 BIT Flags, BOOLEAN, and Other Computed Columns259
- 13.1.10 Attribute Splitting Across Columns259
- 13.1.11 Attribute Splitting Across Rows259
- 13.1.12 Attribute Splitting Across Tables260
- 13.2 Methods of Attack260
- 13.2.1 Cursor-Based Solution261
- 13.2.2 Semiset-Oriented Approach262
- 13.2.3 Pure Set-Oriented Approach264
- 13.2.4 Advantages of Set-Oriented Code264
- 13.3 Translating Vague Specifications265
- 13.3.1 Go Back to the DDL266
- 13.3.2 Changing Specifi cations269
- 14. Using Procedure and Function Calls273
- 14.1 Clearing out Spaces in a String273
- 14.1.1 Procedural Solution #1274
- 14.1.2 Functional Solution #1275
- 14.1.3 Functional Solution #2278
- 14.2 The PRD() Aggregate Function280
- 14.3 Long Parameter Lists in Procedures and Functions282
- 14.3.1 The IN( ) Predicate Parameter Lists283
- 15. Numbering Rows287
- 15.1 Procedural Solutions287
- 15.1.1 Reordering on a Numbering Column289
- 15.2 OLAP Functions291
- 15.2.1 Simple Row Numbering291
- 15.2.2 RANK( ) and DENSE_RANK( )292
- 15.3 Sections293
- 16. Keeping Computed Data297
- 16.1 Procedural Solution297
- 16.2 Relational Solution299
- 16.3 Other Kinds of Computed Data299
- 17. Triggers for Constraints301
- 17.1 Triggers for Computations301
- 17.2 Complex Constraints via CHECK( ) and CASE Constraints302
- 17.3 Complex Constraints via VIEWs305
- 17.3.1 Set-Oriented Solutions306
- 17.4 Operations on VIEWs as Constraints308
- 17.4.1 The Basic Three Operations308
- 17.4.2 WITH CHECK OPTION Clause308
- 17.4.3 WITH CHECK OPTION as CHECK( ) clause313
- 17.4.4 How VIEWs Behave314
- 17.4.5 UNIONed VIEWs316
- 17.4.6 Simple INSTEAD OF Triggers317
- 17.4.7 Warnings about INSTEAD OF Triggers321
- 18. Procedural and Data-Driven Solutions323
- 18.1 Removing Letters in a String323
- 18.1.1 The Procedural Solution324
- 18.1.2 Pure SQL Solution325
- 18.1.3 Impure SQL Solution326
- 18.2 Two Approaches to Sudoku326
- 18.2.1 Procedural Approach327
- 18.2.2 Data-Driven Approach327
- 18.2.3 Handling the Given Digits329
- 18.3 Data Constraint Approach331
- 18.4 Bin Packing Problems335
- 18.4.1 The Procedural Approach336
- 18.4.2 The SQL Approach336
- 18.5 Inventory Costs over Time339
- 18.5.1 Inventory UPDATE Statements343
- 18.5.2 Bin Packing Returns344
- Index349
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.
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.