Joe Celko's SQL for Smarties: Advanced SQL Programming (4th edition)

In the SQL database community, Joe Celko is a well-known columnist and purveyor of valuable insights. In Joe Celkos SQL for Smarties: Advanced SQL Programming, he picks up where basic SQL training and experience leaves many database professionals and offers tips, techniques, and explanations that help readers extend their capabilities to top-tier SQL programming. Although Celko denies that the book is about database theory, he nevertheless alludes to theory often to buttress his practical points. This title is not for novices, as the author points out. Instead, its intended audience is SQL programmers with at least a years experience. The book maintains a fine balance between technical discussion and practical explanation--picking hot topics and offering advice on a wide range of issues. The book uses ANSI SQL-89 as its baseline standard, with some mention of SQL-92 features. It does not, however, focus on any commercial product this guide zeroes in on the SQL language. Celko covers all aspects of database design, optimization, and manipulation, with easy-to-understand explanations of key issues such as why not to use too many nulls, how to use practical normalization, and how to optimize queries. This insightful text is manna for all the day-to-day SQL coders banging their heads over the languages subtle challenges. --Stephen W. Plain Topics covered: Database design and normalization, SQL data types, querying, grouping, set operations, optimization, data scaling, and encoding. Copyright......Page 5 Dedication......Page 6 About the Author......Page 7 What Changed in Ten Years......Page 9 New in This Edition......Page 10 Corrections and Additions......Page 11 Databases versus File Systems......Page 12 Tables as Relationships......Page 15 Rows versus Records......Page 16 Columns versus Fields......Page 17 CREATE SCHEMA Statement......Page 18 Sessions......Page 22 Atomicity......Page 23 Consistency......Page 24 The Three Phenomena......Page 25 The Isolation Levels......Page 27 Pessimistic Concurrency Control......Page 29 SNAPSHOT Isolation and Optimistic Concurrency......Page 30 Deadlock and Livelocks......Page 32 CREATE SCHEMA Statement......Page 34 CREATE DOMAIN Statement......Page 35 CREATE SEQUENCE......Page 36 Using VIEWs for Schema Level Constraints......Page 37 Using PRIMARY KEYs and ASSERTIONs for Constraints......Page 41 Character Set Related Constructs......Page 42 CREATE COLLATION......Page 43 CREATE TRANSLATION......Page 44 ROWID and Physical Disk Addresses......Page 46 IDENTITY Columns......Page 47 GUIDs......Page 51 UUIDs......Page 52 Unique Value Generators......Page 53 Preallocated Values......Page 54 Special Series......Page 55 Prime Numbers......Page 56 Random Order Values......Page 59 Other Series......Page 61 Base Tables and Related Elements......Page 62 Column Constraints......Page 64 DEFAULT Clause......Page 65 NOT NULL Constraint......Page 66 CHECK( ) Constraints......Page 67 UNIQUE and PRIMARY KEY Constraints......Page 69 REFERENCES Clause......Page 71 Nested UNIQUE Constraints......Page 73 Overlapping Keys......Page 76 Single versus Multiple-Column Uniqueness......Page 79 CREATE ASSERTION Constraints......Page 87 TEMPORARY TABLE Declarations......Page 88 Manipulating Tables......Page 89 DROP TABLE ......Page 90 ALTER TABLE......Page 91 Table Level Attribute Splitting......Page 92 Modeling Class Hierarchies in DDL......Page 94 Auto-Incrementing Columns......Page 96 IDENTITY Columns......Page 99 Comparing IDENTITY Columns and Sequences......Page 100 Industry Standard Unique Identifiers......Page 101 Sequence Generator Functions......Page 102 Unique Value Generators......Page 103 Verification Sources......Page 104 A Remark on Duplicate Rows......Page 105 Other Schema Objects......Page 107 Temporary Tables......Page 108 CREATE DOMAIN Statement......Page 109 CREATE PROCEDURE Statement......Page 110 DECLARE CURSOR Statement......Page 111 How to Use a CURSOR......Page 113 Positioned UPDATE and DELETE Statements......Page 115 Cohesion......Page 116 Coupling......Page 117 The Big Leap......Page 118 An Improvement......Page 119 Rewriting Tricks......Page 125 Data Tables versus Generator Code......Page 126 Fibonacci Series......Page 127 Functions for Predicates......Page 129 Procedural Decomposition Solution......Page 130 Logical Decomposition Solution......Page 132 CREATE PROCEDURE......Page 134 CREATE TRIGGER......Page 135 CURSORs......Page 138 DECLARE CURSOR Statement......Page 139 The ORDER BY clause......Page 140 The ORDER BY and NULLs......Page 141 The ORDER BY and CASE Expressions......Page 145 FETCH Statement......Page 147 How to Use a CURSOR......Page 148 SEQUENCEs......Page 152 Generated Columns......Page 153 Table Functions......Page 154 The Series Table......Page 156 Enumerating a List......Page 157 Mapping a Series into a Cycle......Page 159 Replacing an Iterative Loop......Page 161 Lookup Auxiliary Tables......Page 162 Multiple Translation Auxiliary Tables......Page 164 Multiple Parameter Auxiliary Tables......Page 165 Range Auxiliary Tables......Page 166 One True Look-up Table......Page 167 Auxiliary Function Tables......Page 170 Inverse Functions with Auxiliary Tables......Page 171 Interpolation with Auxiliary Function Tables......Page 179 Global Constants Tables......Page 180 Preallocated Values......Page 181 Fibonacci Numbers......Page 182 Random Order Values......Page 183 A Note on Converting Procedural Code to Tables......Page 186 Normalization......Page 192 Functional and Multivalued Dependencies......Page 194 First Normal Form (1NF)......Page 195 Repeating Columns......Page 197 Parsing a List in a String......Page 198 Second Normal Form (2NF)......Page 199 Third Normal Form (3NF)......Page 200 Elementary Key Normal Form (EKNF)......Page 202 Boyce-Codd Normal Form (BCNF)......Page 203 Fifth Normal Form (5NF)......Page 205 Domain-Key Normal Form (DKNF)......Page 207 Practical Hints for Normalization......Page 215 Natural Keys......Page 216 Artificial Keys......Page 217 Exposed Physical Locators......Page 218 Practical Hints for Denormalization......Page 219 Row Sorting......Page 221 Numeric Types......Page 226 BIT, BYTE, and BOOLEAN Data Types......Page 229 Rounding and Truncating......Page 231 Four Function Arithmetic......Page 233 Arithmetic and NULLs......Page 235 NULLIF( ) Function......Page 236 COALESCE( ) Function......Page 237 Number Theory Operators......Page 239 Exponential Functions......Page 241 Converting Numbers to Words......Page 242 Unique Value Generators......Page 243 Sequences with Gaps......Page 244 Preallocated Values......Page 245 Binary Storage......Page 246 Separate SMALLINTs......Page 247 Notes on Calendar Standards......Page 248 SQL Temporal Data Types......Page 251 Internal Representations......Page 252 Handling Timestamps......Page 253 Time Zones and DST......Page 256 INTERVAL Data Types......Page 257 Temporal Arithmetic......Page 260 Modeling Durations......Page 261 Relationships among Durations......Page 263 Problems with SQL Strings......Page 266 Problems of String Equality......Page 267 Problems of String Grouping......Page 268 Standard String Functions......Page 269 Common Vendor Extensions......Page 270 Soundex Functions......Page 272 The Original Soundex......Page 273 Metaphone......Page 275 NYSIIS Algorithm......Page 278 Cutter Tables......Page 279 Nested Replacement......Page 280 NULLs: Missing Data in SQL......Page 282 Empty and Missing Tables......Page 283 Missing Values in Columns......Page 284 Context and Missing Values......Page 286 Comparing NULLs......Page 287 NULLs and Logic......Page 288 NULLs in Subquery Predicates......Page 290 Logical Value Predicate......Page 291 NULLs and Host Languages......Page 292 Design Advice for NULLs......Page 293 Avoiding NULLs from the Host Programs......Page 295 A Note on Multiple NULL Values......Page 296 Distance Functions......Page 300 A Single VARCHAR(15) Column......Page 302 One INTEGER Column......Page 303 Storing an IPv6 Address in SQL......Page 304 Currency and Other Unit Conversions......Page 305 Social Security Numbers......Page 306 Rational Numbers......Page 309 The DELETE FROM Clause......Page 310 The WHERE Clause......Page 311 Deleting within the Same Table......Page 314 Redundant Duplicates in a Table......Page 315 Redundant Duplicates Removal with ROWID......Page 316 INSERT INTO Statement......Page 318 INSERT INTO Clause......Page 319 Bulk Load and Unload Utilities......Page 320 The UPDATE Clause......Page 321 The SET Clause......Page 322 Updating with a Second Table......Page 323 Using the CASE Expression in UPDATEs......Page 326 A Note on Flaws in a Common Vendor Extension......Page 328 MERGE Statement......Page 330 Converting Data Types......Page 334 Other Display Formats......Page 336 Row Comparisons in SQL......Page 337 IS [NOT] DISTINCT FROM Operator......Page 339 IS NULL......Page 340 IS [NOT]{TRUE | FALSE | UNKNOWN} Predicate......Page 341 IS [NOT] NORMALIZED Predicate......Page 343 The CASE Expression......Page 344 The COALESCE() and NULLIF() Functions......Page 347 CASE Expressions with GROUP BY......Page 348 CASE, CHECK() Clauses and Logical Implication......Page 349 Subquery Expressions and Constants......Page 352 Rozenshtein Characteristic Functions......Page 353 LIKE and SIMILAR TO Predicates......Page 356 Tricks with Patterns......Page 357 Results with NULL Values and Empty Strings......Page 358 Avoiding the LIKE Predicate with a Join......Page 359 CASE Expressions and LIKE Search Conditions......Page 360 SIMILAR TO Predicates......Page 361 String Character Content......Page 363 Searching versus Declaring a String......Page 364 Creating an Index on a String......Page 365 The BETWEEN Predicate......Page 366 Results with NULL Values......Page 367 Programming Tips......Page 368 Time Periods and OVERLAPS Predicate......Page 369 The [NOT] IN() Predicate......Page 380 Optimizing the IN() Predicate......Page 381 Replacing ORs with the IN() Predicate......Page 384 NULLs and the IN() Predicate......Page 385 IN() Predicate and Referential Constraints......Page 387 IN() Predicate and Scalar Queries......Page 388 EXISTS() Predicate......Page 392 EXISTS and NULLs......Page 393 EXISTS and INNER JOINs......Page 395 EXISTS() and Quantifiers......Page 396 EXISTS() and Referential Constraints......Page 397 EXISTS and Three-Valued Logic......Page 398 Scalar Subquery Comparisons......Page 400 Quantifiers and Missing Data......Page 402 The ALL Predicate and Extrema Functions......Page 404 The UNIQUE Predicate......Page 405 The DISTINCT Predicate......Page 406 One-Level SELECT Statement......Page 408 Correlated Subqueries......Page 418 Infixed INNER JOINs......Page 422 OUTER JOINs......Page 424 A Bit of History......Page 425 NULLs and OUTER JOINs......Page 429 NATURAL versus Searched OUTER JOINs......Page 431 Self OUTER JOINs......Page 432 Two or More OUTER JOINs......Page 433 FULL OUTER JOIN......Page 435 UNION JOIN Operators......Page 436 Scalar SELECT Expressions......Page 437 Old versus New JOIN Syntax......Page 438 Constrained JOINs......Page 439 Inventory and Orders......Page 440 Stable Marriages......Page 441 Ball and Box Packing......Page 445 Dr. Codd’s T-Join......Page 448 Stobbs Solution......Page 452 Pierre’s Solution......Page 453 References......Page 454 VIEWs in Queries......Page 456 Updatable and Read-Only VIEWs......Page 457 Translated Columns......Page 459 Grouped VIEWs......Page 460 UNION-ed VIEWs......Page 461 Nested VIEWs......Page 463 VIEW Materialization......Page 464 In-Line Text Expansion......Page 465 Pointer Structures......Page 467 WITH CHECK OPTION Clause......Page 468 WITH CHECK OPTION as CHECK() clause......Page 472 Dropping VIEWs......Page 473 Using VIEWs......Page 474 Flattening a Table with a VIEW......Page 475 Using Derived Tables......Page 477 Derived Tables in the FROM Clause......Page 478 Simple Common Table Expressions......Page 479 Recursive Common Table Expressions......Page 480 Simple Incrementation......Page 481 Simple Tree Traversal......Page 482 Materialized Query Tables......Page 483 Partitioning by Ranges......Page 484 Single-Column Range Tables......Page 485 Partition by Sequences......Page 486 Relational Division......Page 489 Division with a Remainder......Page 491 Note on Performance......Page 492 Todd’s Division......Page 493 Division with JOINs......Page 495 Romley’s Division......Page 496 Boolean Expressions in an RDBMS......Page 500 FIFO and LIFO Subsets......Page 501 GROUP BY Clause......Page 504 NULLs and Groups......Page 505 GROUP BY and HAVING......Page 506 Group Characteristics and HAVING Clause......Page 507 Grouped VIEWs for Multiple Aggregation Levels......Page 509 Subquery Expressions for Multiple Aggregation Levels......Page 510 Grouping on Computed Columns......Page 512 Grouping into Pairs......Page 513 Sorting and GROUP BY......Page 515 Simple Aggregate Functions......Page 518 COUNT( ) Functions......Page 519 Optimizing Aggregates with DISTINCT......Page 521 SUM( ) Function......Page 522 AVG( ) Function......Page 523 Averages with Empty Groups......Page 525 Averages across Columns......Page 527 Simple Extrema Functions......Page 528 Generalized Extrema Functions......Page 530 Multiple Criteria Extrema Functions......Page 537 GREATEST( ) and LEAST ( ) Functions......Page 538 LIST Aggregate with Recursive CTE......Page 541 The LIST( ) Function by Crosstabs......Page 542 The PRD( ) Aggregate Function......Page 543 PRD( ) Function by Expressions......Page 544 The PRD( ) Aggregate Function by Logarithms......Page 545 Bitwise Aggregate Functions......Page 547 Bitwise OR Aggregate Functions......Page 548 Bitwise AND Aggregate Function......Page 549 Advanced Grouping, Windowed Aggregation, and OLAP in SQL......Page 550 GROUPING Operators......Page 551 GROUP BY GROUPING SET......Page 552 CUBES......Page 553 OLAP Examples of SQL......Page 554 PARTITION BY Subclause......Page 555 ORDER BY Subclause......Page 556 Window Frame Subclause......Page 557 Row Numbering......Page 558 PERCENT_RANK() and CUME_DIST......Page 559 Some Examples......Page 560 Vendor Extensions......Page 561 LEAD and LAG Functions......Page 562 FIRST and LAST Functions......Page 563 A Bit of History......Page 564 The Mode......Page 566 The AVG( ) Function......Page 567 The Median......Page 568 The Median as a Programming Problem......Page 569 Celko’s First Median......Page 570 Murchison’s Median......Page 571 Celko’s Second Median......Page 572 Median with Characteristic Function......Page 574 Celko’s Third Median......Page 577 Ken Henderson’s Median......Page 580 OLAP Medians......Page 581 Variance and Standard Deviation......Page 583 Cumulative Statistics......Page 584 Running Differences......Page 585 Cumulative Percentages......Page 586 Ordinal Functions......Page 588 Cross Tabulations......Page 593 Crosstabs by Cross Join......Page 596 Crosstabs by OUTER JOINs......Page 597 Crosstabs by Subquery......Page 598 Harmonic Mean and Geometric Mean......Page 599 Pearson’s r......Page 600 NULLs in Multivariable Descriptive Statistics......Page 601 Variance, Standard Deviation, and Descriptive Stats......Page 602 Distribution Functions......Page 603 Subsequences, Regions, Runs, Gaps, and Islands......Page 606 Finding Subregions of Size (n)......Page 607 Numbering Regions......Page 608 Finding Regions of Maximum Size......Page 609 Bound Queries......Page 613 Run and Sequence Queries......Page 614 Filling in Missing Numbers......Page 616 Summation of a Series......Page 618 Swapping and Sliding Values in a List......Page 621 Folding a List of Numbers......Page 623 Coverings......Page 624 Arrays via Named Columns......Page 628 Arrays via Subscript Columns......Page 632 Matrix Operations in SQL......Page 633 Matrix Addition......Page 634 Matrix Multiplication......Page 635 Matrix Transpose......Page 636 Other Matrix Operations......Page 637 Flattening a Table into an Array......Page 638 Comparing Arrays in Table Format......Page 639 Set Operations......Page 642 UNION and UNION ALL......Page 643 Mixed UNION and UNION ALL Operators......Page 645 INTERSECT and EXCEPT......Page 646 INTERSECT and EXCEPT without NULLs and Duplicates......Page 649 INTERSECT and EXCEPT with NULLs and Duplicates......Page 650 Equality and Proper Subsets......Page 651 Every N-th Item in a Table......Page 654 Random Rows from a Table......Page 655 Proper Subset Operators......Page 660 Table Equality......Page 661 Subset Equality......Page 664 Gaps in a Series......Page 665 Covering for Overlapping Intervals......Page 667 Picking a Representative Subset......Page 670 Trees and Hierarchies in SQL......Page 676 Adjacency List Model......Page 677 Complex Constraints......Page 678 Procedural Traversal for Queries......Page 680 The Path Enumeration Model......Page 681 Finding Subtrees and Nodes......Page 682 Integrity Constraints......Page 683 Nested Set Model of Hierarchies......Page 684 The Containment Property......Page 686 Subordinates......Page 687 Deleting Nodes and Subtrees......Page 688 Converting Adjacency List to Nested Set Model......Page 689 Other Models for Trees and Hierarchies......Page 691 Graphs in SQL......Page 692 Adjacency List Model Graphs......Page 693 SQL and the Adjacency List Model......Page 694 Paths with CTE......Page 695 Nonacyclic Graphs......Page 700 Adjacency Matrix Model......Page 702 All Nodes in the Graph......Page 704 Reachable Nodes......Page 705 Indegree and Outdegree......Page 706 Source, Sink, Isolated, and Internal Nodes......Page 707 Converting Acyclic Graphs to Nested Sets......Page 708 Points inside Polygons......Page 709 Graph Theory References......Page 711 Temporal Math......Page 712 Personal Calendars......Page 714 Time Series......Page 715 Gaps in a Time Series......Page 717 Continuous Time Periods......Page 719 Missing Times in Contiguous Events......Page 724 Locating Dates......Page 727 Starting and Ending Dates......Page 729 Julian Dates......Page 730 Other Temporal Functions......Page 733 Weeks......Page 734 Sorting by Weekday Names......Page 736 Modeling Time in Tables......Page 737 Using Duration Pairs......Page 738 Calendar Auxiliary Table......Page 740 Events and Dates......Page 741 The Zeros......Page 742 Leap Year......Page 743 The Millennium......Page 744 Weird Dates in Legacy Data......Page 745 The Aftermath......Page 746 Optimizing SQL......Page 748 Sequential Access......Page 749 Hashed Indexes......Page 750 How to Index......Page 751 Use Simple Search Conditions......Page 752 Simple String Expressions......Page 753 Simple Temporal Expressions......Page 754 Give Extra Information......Page 755 Index Multiple Columns Carefully......Page 756 Watch the IN Predicate......Page 757 Prefer Joins over Nested Queries......Page 759 Use Fewer Statements......Page 760 Avoid Sorting......Page 761 Know Your Optimizer......Page 765 Recompile Static SQL after Schema Changes......Page 767 Temporary Tables Are Sometimes Handy......Page 768 Update Statistics......Page 770 Do Not Trust Newer Features......Page 771 Random Numbers......Page 774 Scales and Measurements......Page 775 Regular Expressions......Page 776 Introductory SQL Books......Page 777 Temporal Data and the Year 2000 Problem......Page 778 Classics......Page 779 Theory, Normalization, and Advanced Database Topics......Page 780 Standards and Related Groups......Page 781 Temporal Databases......Page 782 Miscellaneous Citations......Page 783 B......Page 786 C......Page 787 D......Page 788 F......Page 789 H......Page 790 K......Page 791 N......Page 792 O......Page 793 P......Page 794 S......Page 795 U......Page 797 Z......Page 798 Introduction Chapter 1: Databases versus File Systems Chapter 2: Transactions and Concurrency Control Chapter 3: Schema Level Objects Chapter 4: Locating Data and Special Numbers Chapter 5: Base Tables and Related Elements Chapter 6: Procedural, Semi-Procedural and Declarative Programming Chapter 7: Procedural Constructs Chapter 8: Auxiliary Tables Chapter 9: Normalization Chapter 10: Numeric Data Types Chapter 11: Temporal Data Types Chapter 12: Character Data Types Chapter 13: NULLs Missing Data in SQL Chapter 14: Multiple Column Data Elements Chapter 15: Table Operations Chapter 16: Comparison or Theta Operators Chapter 17: Valued Predicates Chapter 18: CASE Expressions Chapter 19: LIKE and SIMILAR TO Predicates Chapter 20: BETWEEN and OVERLAPS Predicates Chapter 21: The [NOT] IN() Predicate Chapter 22: EXISTS() Predicate Chapter 23: Quantified Subquery Predicates Chapter 24: The Simple SELECT Statement Chapter 25: Advanced SELECT Statements Chapter 26: Virtual Tables: VIEWs, Derived Tables, CTEs and MQTs Chapter 27: Partitioning Data in Queries Chapter 28: Grouping Operations Chapter 29: Simple Aggregate Functions Chapter 30: Advanced Grouping, Windowed Aggregation and OLAP in SQL Chapter 31: Descriptive Statistics in SQL Chapter 32: Sub-Sequences, Regions, Runs, Gaps, and Islands Chapter 33: Matrices in SQL Chapter 34: Set Operations Chapter 35: Subsets Chapter 36: Trees and Hierarchies in SQL Chapter 37: Graphs in SQL Chapter 38: Temporal Queries Chapter 39: Optimizing SQL. 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, 15 years later and in its fourth edition, this classic reference still reigns supreme as the only book written by a SQL master that teaches programmers and practitioners to become SQL masters themselves! These are not just tips and techniques; also offered are the best solutions to old and new challenges. Joe Celko conveys the way you need to think in order to get the most out of SQL programming efforts for both correctness and performance. New to the fourth edition, Joe features new examples to reflect the ANSI/ISO Standards so anyone can use it. He also updates data element names to meet new ISO-11179 rules and he expands coverage of SSD, parallel processors and how new hardware will change how SQL works, all with the same experience-based teaching style that made the previous editions the classics they are today. KEY FEATURES Expert advice from a noted SQL authority and award-winning columnist who has given ten years service to the ANSI SQL standards committee Teaches scores of advanced techniques that can be used with any product, in any SQL environment, whether it is an SQL 92 or SQL 2008 environment Offers tips for working around deficiencies and gives insight into real-world challenges.

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, 15 years later and in its fourth edition, this classic reference still reigns supreme as the only book written by a SQL master that teaches programmers and practitioners to become SQL masters themselves! These are not just tips and techniques; also offered are the best solutions to old and new challenges. Joe Celko conveys the way you need to think in order to get the most out of SQL programming efforts for both correctness and performance. New to the fourth edition, Joe features new examples to reflect the ANSI/ISO Standards so anyone can use it. He also updates data element names to meet new ISO—11179 rules with the same experience-based teaching style that made the previous editions the classics they are today.

KEY FEATURES

• Expert advice from a noted SQL authority and award-winning columnist who has given ten years service to the ANSI SQL standards committee
• Teaches scores of advanced techniques that can be used with any product, in any SQL environment, whether it is an SQL 92 or SQL 2008 environment
• Offers tips for working around deficiencies and gives insight into real-world challenges