Joe Celko's SQL for Smarties (eBook)

Front Cover 1
Series page 3
Joe Celko’s SQL for Smarties 4
Copyright 5
Dedication 6
Table of Contents 8
About the Author 20
Introduction to the Fourth Edition 22
Chapter 1. Databases versus File Systems 26
1.1 Tables as Entities 29
1.2 Tables as Relationships 29
1.3 Rows versus Records 30
1.4 Columns versus Fields 31
1.5 Schema Objects 32
1.6 CREATE SCHEMA Statement 32
Chapter 2. Transactions and Concurrency Control 36
2.1 Sessions 36
2.2 Transactions and ACID 37
2.3 Concurrency Control 39
2.4 Pessimistic Concurrency Control 43
2.5 SNAPSHOT Isolation and Optimistic Concurrency 44
2.6 Logical Concurrency Control 46
2.7 Deadlock and Livelocks 46
Chapter 3. Schema Level Objects 48
3.1 CREATE SCHEMA Statement 48
3.2 CREATE PROCEDURE, CREATE FUNCTION, and CREATE TRIGGER 49
3.3 CREATE DOMAIN Statement 49
3.4 CREATE SEQUENCE 50
3.5 CREATE ASSERTION 51
3.6 Character Set Related Constructs 56
Chapter 4. Locating Data and Special Numbers 60
4.1 Exposed Physical Locators 60
4.2 Generated Identifiers 65
4.3 Sequence Generator Functions 67
4.4 Preallocated Values 68
4.5 Special Series 69
Chapter 5. Base Tables and Related Elements 76
5.1 CREATE TABLE Statement 78
5.2 Nested UNIQUE Constraints 87
5.3 CREATE ASSERTION Constraints 101
5.4 TEMPORARY Tables 102
5.5 Manipulating Tables 103
5.6 Avoiding Attribute Splitting 106
5.7 Modeling Class Hierarchies in DDL 108
5.8 Exposed Physical Locators 110
5.9 Auto-Incrementing Columns 110
5.10 Generated Identifiers 115
5.11 A Remark on Duplicate Rows 119
5.12 Other Schema Objects 121
5.13 Temporary Tables 122
5.14 CREATE DOMAIN Statement 123
5.15 CREATE TRIGGER Statement 124
5.16 CREATE PROCEDURE Statement 124
5.17 DECLARE CURSOR Statement 125
Chapter 6. Procedural, Semiprocedural, and Declarative Programming 130
6.1 Basics of Software Engineering 130
6.2 Cohesion 130
6.3 Coupling 131
6.4 The Big Leap 132
6.5 Rewriting Tricks 139
6.6 Functions for Predicates 143
6.7 Procedural versus Logical Decomposition 144
Chapter 7. Procedural Constructs 148
7.1 CREATE PROCEDURE 148
7.2 CREATE TRIGGER 149
7.3 CURSORs 152
7.4 SEQUENCEs 166
7.5 Generated Columns 167
7.6 Table Functions 168
Chapter 8. Auxiliary Tables 170
8.1 The Series Table 170
8.2 Lookup Auxiliary Tables 176
8.3 Auxiliary Function Tables 184
8.4 Global Constants Tables 194
8.5 A Note on Converting Procedural Code to Tables 200
Chapter 9. Normalization 206
9.1 Functional and Multivalued Dependencies 208
9.2 First Normal Form (1NF) 209
9.3 Second Normal Form (2NF) 213
9.4 Third Normal Form (3NF) 214
9.5 Elementary Key Normal Form (EKNF) 216
9.6 Boyce-Codd Normal Form (BCNF) 217
9.7 Fourth Normal Form (4NF) 219
9.8 Fifth Normal Form (5NF) 219
9.9 Domain-Key Normal Form (DKNF) 221
9.10 Practical Hints for Normalization 229
9.11 Key Types 230
9.12 Practical Hints for Denormalization 233
Chapter 10. Numeric Data Types 240
10.1 Numeric Types 240
10.2 Numeric Type Conversion 245
10.3 Four Function Arithmetic 247
10.4 Arithmetic and NULLs 249
10.5 Converting Values to and from NULL 250
10.6 Mathematical Functions 253
10.7 Unique Value Generators 257
10.8 IP Addresses 260
Chapter 11. Temporal Data Types 262
11.1 Notes on Calendar Standards 262
11.2 SQL Temporal Data Types 265
11.3 INTERVAL Data Types 271
11.4 Temporal Arithmetic 274
11.5 The Nature of Temporal Data Models 275
Chapter 12. Character Data Types 280
12.1 Problems with SQL Strings 280
12.2 Standard String Functions 283
12.3 Common Vendor Extensions 284
12.4 Cutter Tables 293
12.5 Nested Replacement 294
Chapter 13. NULLs: Missing Data in SQL 296
13.1 Empty and Missing Tables 297
13.2 Missing Values in Columns 298
13.3 Context and Missing Values 300
13.4 Comparing NULLs 301
13.5 NULLs and Logic 302
13.6 Math and NULLs 306
13.7 Functions and NULLs 306
13.8 NULLs and Host Languages 306
13.9 Design Advice for NULLs 307
13.10 A Note on Multiple NULL Values 310
Chapter 14. Multiple Column Data Elements 314
14.1 Distance Functions 314
14.2 Storing an IPv4 Address in SQL 316
14.3 Storing an IPv6 Address in SQL 318
14.4 Currency and Other Unit Conversions 319
14.5 Social Security Numbers 320
14.6 Rational Numbers 323
Chapter 15. Table Operations 324
15.1 DELETE FROM Statement 324
15.2 INSERT INTO Statement 332
15.3 The UPDATE Statement 335
15.4 A Note on Flaws in a Common Vendor Extension 342
15.5 MERGE Statement 344
Chapter 16. Comparison or Theta Operators 348
16.1 Converting Data Types 348
16.2 Row Comparisons in SQL 351
16.3 IS [NOT] DISTINCT FROM Operator 353
Chapter 17. Valued Predicates 354
17.1 IS NULL 354
17.2 IS [NOT]{TRUE | FALSE | UNKNOWN} Predicate 355
17.3 IS [NOT] NORMALIZED Predicate 357
Chapter 18. CASE Expressions 358
18.1 The CASE Expression 358
18.2 Subquery Expressions and Constants 366
18.3 Rozenshtein Characteristic Functions 367
Chapter 19. LIKE and SIMILAR TO Predicates 370
19.1 Tricks with Patterns 371
19.2 Results with NULL Values and Empty Strings 372
19.3 LIKE Is Not Equality 373
19.4 Avoiding the LIKE Predicate with a Join 373
19.5 CASE Expressions and LIKE Search Conditions 374
19.6 SIMILAR TO Predicates 375
19.7 Tricks with Strings 377
Chapter 20. BETWEEN and OVERLAPS Predicates 380
20.1 The BETWEEN Predicate 380
20.2 OVERLAPS Predicate 383
Chapter 21. The [NOT] IN() Predicate 394
21.1 Optimizing the IN() Predicate 395
21.2 Replacing ORs with the IN() Predicate 398
21.3 NULLs and the IN() Predicate 399
21.4 IN() Predicate and Referential Constraints 401
21.5 IN() Predicate and Scalar Queries 402
Chapter 22. EXISTS() Predicate 406
22.1 EXISTS and NULLs 407
22.2 EXISTS and INNER JOINs 409
22.3 NOT EXISTS and OUTER JOINs 410
22.4 EXISTS() and Quantifiers 410
22.5 EXISTS() and Referential Constraints 411
22.6 EXISTS and Three-Valued Logic 412
Chapter 23. Quantified Subquery Predicates 414
23.1 Scalar Subquery Comparisons 414
23.2 Quantifiers and Missing Data 416
23.3 The ALL Predicate and Extrema Functions 418
23.4 The UNIQUE Predicate 419
23.5 The DISTINCT Predicate 420
Chapter 24. The Simple SELECT Statement 422
24.1 SELECT Statement Execution Order 422
24.2 One-Level SELECT Statement 422
Chapter 25. Advanced SELECT Statements 432
25.1 Correlated Subqueries 432
25.2 Infixed INNER JOINs 436
25.3 OUTER JOINs 438
25.4 UNION JOIN Operators 450
25.5 Scalar SELECT Expressions 451
25.6 Old versus New JOIN Syntax 452
25.7 Constrained JOINs 453
25.8 Dr. Codd’s T-Join 462
References 468
Chapter 26. Virtual Tables: VIEWs, Derived Tables, CTEs, and MQTs 470
26.1 VIEWs in Queries 470
26.2 Updatable and Read-Only VIEWs 471
26.3 Types of VIEWs 473
26.4 How VIEWs Are Handled in the Database Engine 478
26.5 WITH CHECK OPTION Clause 482
26.6 Dropping VIEWs 487
26.7 Hints on Using VIEWs versus TEMPORARY TABLEs 488
26.8 Using Derived Tables 491
26.9 Common Table Expressions 493
26.10 Recursive Common Table Expressions 494
26.11 Materialized Query Tables 497
Chapter 27. Partitioning Data in Queries 498
27.1 Coverings and Partitions 498
27.2 Relational Division 503
27.3 Romley’s Division 510
27.4 Boolean Expressions in an RDBMS 514
27.5 FIFO and LIFO Subsets 515
Chapter 28. Grouping Operations 518
28.1 GROUP BY Clause 518
28.2 GROUP BY and HAVING 520
28.3 Multiple Aggregation Levels 523
28.4 Grouping on Computed Columns 526
28.5 Grouping into Pairs 527
28.6 Sorting and GROUP BY 529
Chapter 29. Simple Aggregate Functions 532
29.1 COUNT() Functions 533
29.2 SUM() Function 536
29.3 AVG() Function 537
29.4 Extrema Functions 542
29.5 The LIST() Aggregate Function 555
29.6 The PRD() Aggregate Function 557
29.7 Bitwise Aggregate Functions 561
Chapter 30. Advanced Grouping, Windowed Aggregation, and OLAP in SQL 564
30.1 Star Schema 565
30.2 GROUPING Operators 565
30.3 The Window Clause 569
30.4 Windowed Aggregate Functions 572
30.5 Ordinal Functions 572
30.6 Vendor Extensions 575
30.7 A Bit of History 578
Chapter 31. Descriptive Statistics in SQL 580
31.1 The Mode 580
31.2 The AVG() Function 581
31.3 The Median 582
31.4 Variance and Standard Deviation 597
31.5 Average Deviation 598
31.6 Cumulative Statistics 598
31.7 Cross Tabulations 607
31.8 Harmonic Mean and Geometric Mean 613
31.9 Multivariable Descriptive Statistics in SQL 614
31.10 Statistical Functions in SQL:2006 616
Chapter 32. Subsequences, Regions, Runs, Gaps, and Islands 620
32.1 Finding Subregions of Size (n) 621
32.2 Numbering Regions 622
32.3 Finding Regions of Maximum Size 623
32.4 Bound Queries 627
32.5 Run and Sequence Queries 628
32.6 Summation of a Series 632
32.7 Swapping and Sliding Values in a List 635
32.8 Condensing a List of Numbers 637
32.9 Folding a List of Numbers 637
32.10 Coverings 638
Chapter 33. Matrices in SQL 642
33.1 Arrays via Named Columns 642
33.2 Arrays via Subscript Columns 646
33.3 Matrix Operations in SQL 647
33.4 Flattening a Table into an Array 652
33.5 Comparing Arrays in Table Format 653
Chapter 34. Set Operations 656
34.1 UNION and UNION ALL 657
34.2 INTERSECT and EXCEPT 660
34.3 A Note on ALL and SELECT DISTINCT 665
34.4 Equality and Proper Subsets 665
Chapter 35. Subsets 668
35.1 Every N-th Item in a Table 668
35.2 Random Rows from a Table 669
35.3 The CONTAINS Operators 674
35.4 Gaps in a Series 679
35.5 Covering for Overlapping Intervals 681
35.6 Picking a Representative Subset 684
Chapter 36. Trees and Hierarchies in SQL 690
36.1 Adjacency List Model 691
36.2 The Path Enumeration Model 695
36.3 Nested Set Model of Hierarchies 698
36.4 Other Models for Trees and Hierarchies 705
Chapter 37. Graphs in SQL 706
37.1 Adjacency List Model Graphs 707
37.2 Split Node Nested Set Models for Graphs 718
37.3 Points inside Polygons 723
37.4 Graph Theory References 725
Chapter 38. Temporal Queries 726
38.1 Temporal Math 726
38.2 Personal Calendars 728
38.3 Time Series 729
38.4 Julian Dates 744
38.5 Other Temporal Functions 747
38.6 Weeks 748
38.7 Modeling Time in Tables 751
38.8 Calendar Auxiliary Table 754
38.9 Problems with the Year 2000 756
Chapter 39. Optimizing SQL 762
39.1 Access Methods 763
39.2 How to Index 765
39.3 Give Extra Information 769
39.4 Index Multiple Columns Carefully 770
39.5 Watch the IN Predicate 771
39.6 Avoid UNIONs 773
39.7 Prefer Joins over Nested Queries 773
39.8 Use Fewer Statements 774
39.9 Avoid Sorting 775
39.10 Avoid CROSS JOINs 779
39.11 Know Your Optimizer 779
39.12 Recompile Static SQL after Schema Changes 781
39.13 Temporary Tables Are Sometimes Handy 782
39.14 Update Statistics 784
39.15 Do Not Trust Newer Features 785
References 788
General References 788
Logic 788
Mathematical Techniques 788
Random Numbers 788
Scales and Measurements 789
Missing Values 790
Regular Expressions 790
Graph Theory 791
Introductory SQL Books 791
Optimizing Queries 792
Temporal Data and the Year 2000 Problem 792
SQL Programming Techniques 793
Classics 793
Updatable Views 794
Theory, Normalization, and Advanced Database Topics 794
Books on SQL-92 and SQL-99 795
Standards and Related Groups 795
Web Sites Related to SQL 796
Statistics 796
Temporal Databases 796
Miscellaneous Citations 797
Index 800