Joe Celko s SQL for Smarties - Advanced SQL Programming P3. In the SQL database community, Joe Celko is a well-known columnist and purveyor of valuable insights. In Joe Celko's 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. | xx CONTENTS 25 Arrays in SQL 575 Arrays via Named Columns 576 Arrays via Subscript Columns 580 Matrix Operations in SQL 581 Matrix Equality 582 Matrix Addition 582 Matrix Multiplication 583 Other Matrix Operations 585 Flattening a Table into an Array 585 Comparing Arrays in Table Format 587 26 Set Operations 591 UNION and UNION ALL 592 Order of Execution 594 Mixed UNION and UNION ALL Operators 595 UNION of Columns from the Same Table 595 INTERSECT and EXCEPT 596 INTERSECT and EXCEPT without NULLs and Duplicates 599 INTERSECT and EXCEPT with NULLs and Duplicates 600 A Note on ALL and SELECT DISTINCT 601 Equality and Proper Subsets 602 27 Subsets 605 Every nth Item in a Table 605 Picking Random Rows from a Table 607 The CONTAINS Operators 612 Proper Subset Operators 612 Table Equality 613 Picking a Representative Subset 618 28 Trees and Hierarchies in SQL 623 Adjacency List Model 624 CONTENTS xxi Complex Constraints 625 Procedural Traversal for Queries 627 Altering the Table 628 The Path Enumeration Model 628 Finding Subtrees and Nodes 629 Finding Levels and Subordinates 630 Deleting Nodes and Subtrees 630 Integrity Constraints 631 Nested Set Model of Hierarchies 631 The Counting Property 633 The Containment Property 634 Subordinates 635 Hierarchical Aggregations 636 Deleting Nodes and Subtrees 636 Converting Adjacency List to Nested Set Model 637 Other Models for Trees and Hierarchies 639 29 Temporal Queries 641 Temporal Math 642 Personal Calendars 643 Time Series 645 Gaps in a Time Series 645 Continuous Time Periods 648 Missing Times in Contiguous Events 652 Locating Dates 656 Temporal Starting and Ending Points 658 Average Wait Times 660 Julian Dates 661
