Joe Celko's SQL for Smarties : Advanced SQL Programming

by Celko

• ISBN13: 9780123693792
• ISBN10: 0123693799
• eBook ISBN(s): 9780080460048
• Edition: 3rd
• Format: Paperback
• Copyright: 2005-07-29
• Publisher: Elsevier Science

Summary

SQL for Smarties was hailed as the first book devoted explicitly to the advanced techniques needed to transform an experienced SQL programmer into an expert. Now, 10 years later and in the third edition, this classic still reigns supreme as the book written by an SQL master that teaches future SQL masters. These are not just tips and techniques; Joe also offers the best solutions to old and new challenges and conveys the way you need to think in order to get the most out of SQL programming efforts for both correctness and performance. In the third edition, Joe features new examples and updates to SQL-99, expanded sections of Query techniques, and a new section on schema design, with the same war-story teaching style that made the first and second editions of this book classics. * Expert advice from a noted SQL authority and award-winning columnist, who has given ten years of service to the ANSI SQL standards committee and many more years of dependable help to readers of online forums. * Teaches scores of advanced techniques that can be used with any product, in any SQL environment, whether it is an SQL-92 or SQL-99 environment. * Offers tips for working around system deficiencies. * Continues to use war stories--updated!--that give insights into real-world SQL programming challenges.

Author Biography

Joe Celko is a noted consultant, lecturer, teacher, and one of the most-read SQL authors in the world

Table of Contents

Introduction to the Third Edition	p. xxv
What Changed in Ten Years	p. xxv
What Is New in This Edition	p. xxvii
Corrections and Additions	p. xxviii
Database Design	p. 1
Schema and Table Creation	p. 2
CREATE SCHEMA Statement	p. 3
Manipulating Tables	p. 5
Column Constraints	p. 10
UNIQUE Constraints versus UNIQUE Indexes	p. 17
Nested UNIQUE Constraints	p. 18
Overlapping Keys	p. 22
CREATE ASSERTION Constraints	p. 25
Using VIEWs for Schema Level Constraints	p. 25
Using PRIMARY KEYs and ASSERTIONs for Constraints	p. 29
Avoiding Attribute Splitting	p. 31
Modeling Class Hierarchies in DDL	p. 34
Generating Unique Sequential Numbers for Keys	p. 36
IDENTITY Columns	p. 37
ROWID and Physical Disk Addresses	p. 39
Sequential Numbering in Pure SQL	p. 39
GUIDs	p. 41
Sequence Generator Functions	p. 42
Unique Value Generators	p. 42
Preallocated Values	p. 44
Random Order Values	p. 45
A Remark on Duplicate Rows	p. 48
Other Schema Objects	p. 50
Schema Tables	p. 50
Temporary Tables	p. 51
CREATE DOMAIN Statement	p. 51
CREATE TRIGGER Statement	p. 52
CREATE PROCEDURE Statement	p. 53
DECLARE CURSOR Statement	p. 53
Normalization	p. 61
Functional and Multivalued Dependencies	p. 64
First Normal Form (1NF)	p. 64
Note on Repeated Groups	p. 66
Second Normal Form (2NF)	p. 70
Third Normal Form (3NF)	p. 71
Elementary Key Normal Form (EKNF)	p. 72
Boyce-Codd Normal Form (BCNF)	p. 73
Fourth Normal Form (4NF)	p. 75
Fifth Normal Form (5NF)	p. 76
Domain-Key Normal Form (DKNF)	p. 78
Practical Hints for Normalization	p. 87
Key Types	p. 88
Natural Keys	p. 89
Artificial Keys	p. 89
Exposed Physical Locators	p. 89
Practical Hints for Denormalization	p. 91
Row Sorting	p. 93
Numeric Data in SQL	p. 101
Numeric Types	p. 101
BIT, BYTE, and BOOLEAN Data Types	p. 104
Numeric Type Conversion	p. 105
Rounding and Truncating	p. 105
CAST() Function	p. 107
Four-Function Arithmetic	p. 108
Arithmetic and NULLs	p. 109
Converting Values to and from NULL	p. 110
NULLIF() Function	p. 110
COALESCE() Function	p. 111
Vendor Math Functions	p. 113
Number Theory Operators	p. 113
Exponential Functions	p. 116
Scaling Functions	p. 116
Converting Numbers to Words	p. 117
Temporal Data Types in SQL	p. 119
Notes on Calendar Standards	p. 119
SQL Temporal Data Types	p. 123
Tips for Handling Dates, Timestamps, and Times	p. 124
Date Format Standards	p. 124
Handling Timestamps	p. 125
Handling Times	p. 127
Queries with Date Arithmetic	p. 128
The Nature of Temporal Data Models	p. 129
Temporal Duplicates	p. 129
Temporal Databases	p. 135
Temporal Projection and Selection	p. 137
Temporal Joins	p. 139
Modifying Valid-Time State Tables	p. 145
Current Modifications	p. 146
Sequenced Modifications	p. 150
Nonsequenced Modifications	p. 155
Transaction-Time State Tables	p. 156
Maintaining the Audit Log	p. 158
Querying the Audit Log	p. 160
Modifying the Audit Log	p. 164
Bitemporal Tables	p. 164
Temporal Support in Standard SQL	p. 167
Character Data Types in SQL	p. 169
Problems with SQL Strings	p. 170
Problems of String Equality	p. 170
Problems of String Ordering	p. 171
Problems of String Grouping	p. 172
Standard String Functions	p. 172
Common Vendor Extensions	p. 174
Phonetic Matching	p. 175
Cutter Tables	p. 182
NULLs: Missing Data in SQL	p. 185
Empty and Missing Tables	p. 187
Missing Values in Columns	p. 187
Context and Missing Values	p. 189
Comparing NULLs	p. 190
NULLs and Logic	p. 190
NULLS in Subquery Predicates	p. 191
Standard SQL Solutions	p. 193
Math and NULLs	p. 193
Functions and NULLs	p. 193
NULLs and Host Languages	p. 194
Design Advice for NULLs	p. 195
Avoiding NULLs from the Host Programs	p. 197
A Note on Multiple NULL Values	p. 198
Multiple Column Data Elements	p. 201
Distance Functions	p. 201
Storing an IP Address in SQL	p. 202
A Single VARCHAR(15) Column	p. 203
One INTEGER Column	p. 203
Four SMALLINT Columns	p. 205
Currency and Other Unit Conversions	p. 205
Social Security Numbers	p. 206
Rational Numbers	p. 209
Table Operations	p. 211
DELETE FROM Statement	p. 211
The DELETE FROM Clause	p. 212
The WHERE Clause	p. 212
Deleting Based on Data in a Second Table	p. 216
Deleting within the Same Table	p. 216
Deleting in Multiple Tables without Referential Integrity	p. 220
INSERT INTO Statement	p. 221
INSERT INTO Clause	p. 221
The Nature of Inserts	p. 222
Bulk Load and Unload Utilities	p. 223
The UPDATE Statement	p. 223
The UPDATE Clause	p. 223
The WHERE Clause	p. 224
The SET Clause	p. 225
Updating with a Second Table	p. 226
Using the CASE Expression in UPDATEs	p. 228
A Note on Flaws in a Common Vendor Extension	p. 231
MERGE Statement	p. 232
Comparison or Theta Operators	p. 235
Converting Data Types	p. 236
Row Comparisons in SQL	p. 238
Valued Predicates	p. 241
IS NULL Predicate	p. 241
Sources of NULLs	p. 242
IS [NOT] {TRUE [vertical bar] FALSE [vertical bar] UNKNOWN} Predicate	p. 242
IS [NOT] NORMALIZED Predicate	p. 244
CASE Expressions	p. 247
The CASE Expression	p. 247
The COALESCE() and NULLIF() Functions	p. 251
CASE Expressions with GROUP BY	p. 252
CASE, CHECK() Clauses and Logical Implication	p. 253
Subquery Expressions and Constants	p. 257
Rozenshtein Characteristic Functions	p. 258
LIKE Predicate	p. 261
Tricks with Patterns	p. 262
Results with NULL Values and Empty Strings	p. 264
LIKE Is Not Equality	p. 264
Avoiding the LIKE Predicate with a Join	p. 264
CASE Expressions and LIKE Predicates	p. 266
SIMILAR TO Predicates	p. 267
Tricks with Strings	p. 269
String Character Content	p. 269
Searching versus Declaring a String	p. 270
Creating an Index on a String	p. 270
BETWEEN and OVERLAPS Predicates	p. 273
The BETWEEN Predicate	p. 273
Results with NULL Values	p. 274
Results with Empty Sets	p. 274
Programming Tips	p. 274
OVERLAPS Predicate	p. 275
Time Periods and OVERLAPS Predicate	p. 275
The [NOT] IN() Predicate	p. 287
Optimizing the IN() Predicate	p. 288
Replacing ORs with the IN() Predicate	p. 292
NULLs and the IN() Predicate	p. 293
IN() Predicate and Referential Constraints	p. 295
IN() Predicate and Scalar Queries	p. 297
EXISTS() Predicate	p. 299
EXISTS and NULLs	p. 300
EXISTS and INNER JOINs	p. 302
NOT EXISTS and OUTER JOINs	p. 303
EXISTS() and Quantifiers	p. 304
EXISTS() and Referential Constraints	p. 305
EXISTS and Three-Valued Logic	p. 306
Quantified Subquery Predicates	p. 309
Scalar Subquery Comparisons	p. 310
Quantifiers and Missing Data	p. 311
The ALL Predicate and Extrema Functions	p. 313
The UNIQUE Predicate	p. 314
The SELECT Statement	p. 317
SELECT and JOINs	p. 317
One-Level SELECT Statement	p. 317
Correlated Subqueries in a SELECT Statement	p. 324
SELECT Statement Syntax	p. 326
The ORDER BY Clause	p. 328
OUTER JOINs	p. 336
Syntax for OUTER JOINs	p. 337
NULLs and OUTER JOINs	p. 342
NATURAL versus Searched OUTER JOINs	p. 344
Self OUTER JOINs	p. 345
Two or More OUTER JOINs	p. 346
OUTER JOINs and Aggregate Functions	p. 348
FULL OUTER JOIN	p. 349
WHERE Clause OUTER JOIN Operators	p. 350
Old versus New JOIN Syntax	p. 351
Scope of Derived Table Names	p. 353
JOINs by Function Calls	p. 354
The UNION JOIN	p. 356
Packing Joins	p. 358
Dr. Codd's T-Join	p. 359
The Croatian Solution	p. 363
The Swedish Solution	p. 364
The Colombian Solution	p. 364
VIEWs, Derived Tables, Materialized Tables, and Temporary Tables	p. 369
VIEWs in Queries	p. 370
Updatable and Read-Only VIEWs	p. 371
Types of VIEWs	p. 373
Single-Table Projection and Restriction	p. 373
Calculated Columns	p. 373
Translated Columns	p. 373
Grouped VIEWs	p. 374
UNIONed VIEWs	p. 375
JOINs in VIEWs	p. 377
Nested VIEWs	p. 377
How VIEWs Are Handled in the Database System	p. 379
View Column List	p. 379
VIEW Materialization	p. 379
In-Line Text Expansion	p. 380
Pointer Structures	p. 382
Indexing and Views	p. 383
WITH CHECK OPTION Clause	p. 383
WITH CHECK OPTION as CHECK() Clause	p. 388
Dropping VIEWs	p. 389
TEMPORARY TABLE Declarations	p. 390
Hints on Using VIEWs and TEMPORARY TABLEs	p. 391
Using VIEWs	p. 392
Using TEMPORARY TABLEs	p. 392
Flattening a Table with a VIEW	p. 393
Using Derived Tables	p. 395
Derived Tables in the FROM clause	p. 395
Derived Tables with a VALUES Constructor	p. 397
Derived Tables in the WITH Clause	p. 397
Partitioning Data in Queries	p. 401
Coverings and Partitions	p. 401
Partitioning by Ranges	p. 402
Partition by Functions	p. 403
Partition by Sequences	p. 404
Relational Division	p. 406
Division with a Remainder	p. 408
Exact Division	p. 409
Note on Performance	p. 410
Todd's Division	p. 410
Division with JOINs	p. 413
Division with Set Operators	p. 413
Romley's Division	p. 414
Boolean Expressions in an RDBMS	p. 418
FIFO and LIFO Subsets	p. 420
Grouping Operations	p. 425
GROUP BY Clause	p. 425
NULLs and Groups	p. 427
GROUP BY and HAVING	p. 427
Group Characteristics and the HAVING Clause	p. 429
Multiple Aggregation Levels	p. 431
Grouped VIEWs for Multiple Aggregation Levels	p. 432
Subquery Expressions for Multiple Aggregation Levels	p. 433
CASE Expressions for Multiple Aggregation Levels	p. 434
Grouping on Computed Columns	p. 435
Grouping into Pairs	p. 436
Sorting and GROUP BY	p. 437
Aggregate Functions	p. 439
COUNT() Functions	p. 440
SUM() Functions	p. 443
AVG() Functions	p. 444
Averages with Empty Groups	p. 446
Averages across Columns	p. 448
Extrema Functions	p. 449
Simple Extrema Functions	p. 449
Generalized Extrema Functions	p. 451
Multiple Criteria Extrema Functions	p. 460
GREATEST() and LEAST() Functions	p. 462
The LIST() Aggregate Function	p. 465
The LIST() Function with a Procedure	p. 466
The LIST() Function by Crosstabs	p. 467
The PRD() Aggregate Function	p. 468
PRD() Function by Expressions	p. 469
The PRD() Aggregate Function by Logarithms	p. 470
Bitwise Aggregate Functions	p. 473
Bitwise OR Aggregate Function	p. 474
Bitwise AND Aggregate Function	p. 475
Auxiliary Tables	p. 477
The Sequence Table	p. 477
Enumerating a List	p. 479
Mapping a Sequence into a Cycle	p. 481
Replacing an Iterative Loop	p. 483
Lookup Auxiliary Tables	p. 485
Simple Translation Auxiliary Tables	p. 487
Multiple Translation Auxiliary Tables	p. 487
Multiple Parameter Auxiliary Tables	p. 488
Range Auxiliary Tables	p. 489
Hierarchical Auxiliary Tables	p. 490
One True Lookup Table	p. 491
Auxiliary Function Tables	p. 493
Inverse Functions with Auxiliary Tables	p. 495
Interpolation with Auxiliary Function Tables	p. 504
Global Constants Tables	p. 506
Statistics in SQL	p. 509
The Mode	p. 510
The AVG() Function	p. 512
The Median	p. 512
Date's First Median	p. 513
Celko's First Median	p. 514
Date's Second Median	p. 516
Murchison's Median	p. 516
Celko's Second Median	p. 517
Vaughan's Median with VIEWs	p. 519
Median with Characteristic Function	p. 520
Celko's Third Median	p. 522
Ken Henderson's Median	p. 526
Variance and Standard Deviation	p. 527
Average Deviation	p. 528
Cumulative Statistics	p. 528
Running Totals	p. 529
Running Differences	p. 530
Cumulative Percentages	p. 531
Rankings and Related Statistics	p. 533
Quintiles and Related Statistics	p. 537
Cross Tabulations	p. 538
Crosstabs by Cross Join	p. 542
Crosstabs by Outer Joins	p. 543
Crosstabs by Subquery	p. 544
Crosstabs by CASE Expression	p. 545
Harmonic Mean and Geometric Mean	p. 545
Multivariable Descriptive Statistics in SQL	p. 546
Covariance	p. 546
Pearson's r	p. 547
NULLs in Multivariable Descriptive Statistics	p. 548
Regions, Runs, Gaps, Sequences, and Series	p. 549
Finding Subregions of Size (n)	p. 550
Numbering Regions	p. 551
Finding Regions of Maximum Size	p. 552
Bound Queries	p. 557
Run and Sequence Queries	p. 557
Filling in Sequence Numbers	p. 560
Summation of a Series	p. 562
Swapping and Sliding Values in a List	p. 565
Condensing a List of Numbers	p. 567
Folding a List of Numbers	p. 567
Coverings	p. 568
Arrays in SQL	p. 575
Arrays via Named Columns	p. 576
Arrays via Subscript Columns	p. 580
Matrix Operations in SQL	p. 581
Matrix Equality	p. 582
Matrix Addition	p. 582
Matrix Multiplication	p. 583
Other Matrix Operations	p. 585
Flattening a Table into an Array	p. 585
Comparing Arrays in Table Format	p. 587
Set Operations	p. 591
UNION and UNION ALL	p. 592
Order of Execution	p. 594
Mixed UNION and UNION ALL Operators	p. 595
UNION of Columns from the Same Table	p. 595
INTERSECT and EXCEPT	p. 596
INTERSECT and EXCEPT without NULLs and Duplicates	p. 599
INTERSECT and EXCEPT with NULLs and Duplicates	p. 600
A Note on ALL and SELECT DISTINCT	p. 601
Equality and Proper Subsets	p. 602
Subsets	p. 605
Every nth Item in a Table	p. 605
Picking Random Rows from a Table	p. 607
The CONTAINS Operators	p. 612
Proper Subset Operators	p. 612
Table Equality	p. 613
Picking a Representative Subset	p. 618
Trees and Hierarchies in SQL	p. 623
Adjacency List Model	p. 624
Complex Constraints	p. 625
Procedural Traversal for Queries	p. 627
Altering the Table	p. 628
The Path Enumeration Model	p. 628
Finding Subtrees and Nodes	p. 629
Finding Levels and Subordinates	p. 630
Deleting Nodes and Subtrees	p. 630
Integrity Constraints	p. 631
Nested Set Model of Hierarchies	p. 631
The Counting Property	p. 633
The Containment Property	p. 634
Subordinates	p. 635
Hierarchical Aggregations	p. 636
Deleting Nodes and Subtrees	p. 636
Converting Adjacency List to Nested Set Model	p. 637
Other Models for Trees and Hierarchies	p. 639
Temporal Queries	p. 641
Temporal Math	p. 642
Personal Calendars	p. 643
Time Series	p. 645
Gaps in a Time Series	p. 645
Continuous Time Periods	p. 648
Missing Times in Contiguous Events	p. 652
Locating Dates	p. 656
Temporal Starting and Ending Points	p. 658
Average Wait Times	p. 660
Julian Dates	p. 661
Date and Time Extraction Functions	p. 665
Other Temporal Functions	p. 666
Weeks	p. 667
Sorting by Weekday Names	p. 669
Modeling Time in Tables	p. 670
Using Duration Pairs	p. 672
Calendar Auxiliary Table	p. 673
Problems with the Year 2000	p. 675
The Zeros	p. 675
Leap Year	p. 676
The Millennium	p. 677
Weird Dates in Legacy Data	p. 679
The Aftermath	p. 680
Graphs in SQL	p. 681
Basic Graph Characteristics	p. 682
All Nodes in the Graph	p. 682
Path Endpoints	p. 683
Reachable Nodes	p. 683
Edges	p. 684
Indegree and Outdegree	p. 684
Source, Sink, Isolated, and Internal Nodes	p. 685
Paths in a Graph	p. 686
Length of Paths	p. 687
Shortest Path	p. 687
Paths by Iteration	p. 688
Listing the Paths	p. 691
Acyclic Graphs as Nested Sets	p. 695
Paths with CTE	p. 697
Nonacyclic Graphs	p. 703
Adjacency Matrix Model	p. 705
Points inside Polygons	p. 706
OLAP in SQL	p. 709
Star Schema	p. 710
OLAP Functionality	p. 711
RANK and DENSE_RANK	p. 711
Row Numbering	p. 711
GROUPING Operators	p. 712
The Window Clause	p. 714
OLAP Examples of SQL	p. 716
Enterprise-Wide Dimensional Layer	p. 717
A Bit of History	p. 718
Transactions and Concurrency Control	p. 719
Sessions	p. 719
Transactions and ACID	p. 720
Atomicity	p. 720
Consistency	p. 721
Isolation	p. 721
Durability	p. 722
Concurrency Control	p. 722
The Five Phenomena	p. 722
The Isolation Levels	p. 724
CURSOR STABILITY Isolation Level	p. 726
Pessimistic Concurrency Control	p. 726
SNAPSHOT Isolation: Optimistic Concurrency	p. 727
Logical Concurrency Control	p. 729
Deadlock and Livelocks	p. 730
Optimizing SQL	p. 731
Access Methods	p. 732
Sequential Access	p. 732
Indexed Access	p. 732
Hashed Indexes	p. 733
Bit Vector Indexes	p. 733
Expressions and Unnested Queries	p. 733
Use Simple Expressions	p. 734
String Expressions	p. 738
Give Extra Join Information in Queries	p. 738
Index Tables Carefully	p. 740
Watch the IN Predicate	p. 742
Avoid UNIONs	p. 744
Prefer Joins over Nested Queries	p. 745
Avoid Expressions on Indexed Columns	p. 746
Avoid Sorting	p. 746
Avoid CROSS JOINs	p. 750
Learn to Use Indexes Carefully	p. 751
Order Indexes Carefully	p. 752
Know Your Optimizer	p. 754
Recompile Static SQL after Schema Changes	p. 756
Temporary Tables Are Sometimes Handy	p. 757
Update Statistics	p. 760
References	p. 761
General References	p. 761
Logic	p. 761
Mathematical Techniques	p. 761
Random Numbers	p. 762
Scales and Measurements	p. 763
Missing Values	p. 763
Regular Expressions	p. 764
Graph Theory	p. 765
Introductory SQL Books	p. 765
Optimizing Queries	p. 766
Temporal Data and the Year 2000 Problem	p. 766
SQL Programming Techniques	p. 768
Classics	p. 768
Forum	p. 769
Updatable Views	p. 769
Theory, Normalization, and Advanced Database Topics	p. 770
Books on SQL-92 and SQL-99	p. 771
Standards and Related Groups	p. 771
Web Sites Related to SQL	p. 772
Statistics	p. 772
Temporal Databases	p. 773
New Citations	p. 774
Index	p. 777
About the Author	p. 810
Table of Contents provided by Ingram. All Rights Reserved.