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.
Inhaltsverzeichnis
1;front cover;1 2;copyright;7 3;table of contents;10 4;front matter;26 5;Introduction to the Third Edition;26 5.1;1.1 What Changed in Ten Years;26 5.2;1.2 What Is New in This Edition;28 5.3;1.3 Corrections and Additions;29 6;body;30 7;1 Database Design;30 7.1;1.1 Schema and Table Creation;31 7.1.1;1.1.1 CREATE SCHEMA Statement;32 7.1.2;1.1.2 Manipulating Tables;34 7.1.3;1.1.3 Column Constraints;39 7.1.4;1.1.4 UNIQUE Constraints versus UNIQUE Indexes;46 7.1.5;1.1.5 Nested UNIQUE Constraints;47 7.1.6;1.1.6 Overlapping Keys;51 7.1.7;1.1.7 CREATE ASSERTION Constraints;54 7.1.8;1.1.8 Using VIEWs for Schema Level Constraints;54 7.1.9;1.1.9 Using PRIMARY KEYs and ASSERTIONs for Constraints;58 7.1.10;1.1.10 Avoiding Attribute Splitting;60 7.1.11;1.1.11 Modeling Class Hierarchies in DDL;63 7.2;1.2 Generating Unique Sequential Numbers for Keys;65 7.2.1;1.2.1 IDENTITY Columns;66 7.2.2;1.2.2 ROWID and Physical Disk Addresses;68 7.2.3;1.2.3 Sequential Numbering in Pure SQL;68 7.2.4;1.2.4 GUIDs;70 7.2.5;1.2.5 Sequence Generator Functions;71 7.2.6;1.2.6 Unique Value Generators;71 7.2.7;1.2.7 Preallocated Values;73 7.2.8;1.2.8 Random Order Values;74 7.3;1.3 A Remark on Duplicate Rows;77 7.4;1.4 Other Schema Objects;79 7.4.1;1.4.1 Schema Tables;79 7.4.2;1.4.2 Temporary Tables;80 7.4.3;1.4.3 CREATE DOMAIN Statement;80 7.4.4;1.4.4 CREATE TRIGGER Statement;81 7.4.5;1.4.5 CREATE PROCEDURE Statement;82 7.4.6;1.4.6 DECLARE CURSOR Statement;82 8;2 Normalization;90 8.1;2.1 Functional and Multivalued Dependencies;93 8.2;2.2 First Normal Form (1NF);93 8.2.1;2.2.1 Note on Repeated Groups;95 8.3;2.3 Second Normal Form (2NF);99 8.4;2.4 Third Normal Form (3NF);100 8.5;2.5 Elementary Key Normal Form (EKNF);101 8.6;2.6 Boyce-Codd Normal Form (BCNF);102 8.7;2.7 Fourth Normal Form (4NF);104 8.8;2.8 Fifth Normal Form (5NF);105 8.9;2.9 Domain-Key Normal Form (DKNF);107 8.10;2.10 Practical Hints for Normalization;116 8.11;2.11 Key Types;117 8.11.1;2.11.1 Natural Keys;118 8.11.2;2.11.2 Artificial Keys;11
8 8.11.3;2.11.3 Exposed Physical Locators;118 8.11.4;2.11.4 Practical Hints for Denormalization;120 8.11.5;2.11.5 Row Sorting;122 9;3 Numeric Data in SQL;130 9.1;3.1 Numeric Types;130 9.1.1;3.1.1 BIT, BYTE, and BOOLEAN Data Types;133 9.2;3.2 Numeric Type Conversion;134 9.2.1;3.2.1 Rounding and Truncating;134 9.2.2;3.2.2 CAST() Function;136 9.3;3.3 Four-Function Arithmetic;137 9.4;3.4 Arithmetic and NULLs;138 9.5;3.5 Converting Values to and from NULL;139 9.5.1;3.5.1 NULLIF() Function;139 9.5.2;3.5.2 COALESCE() Function;140 9.6;3.6 Vendor Math Functions;142 9.6.1;3.6.1 Number Theory Operators;142 9.6.2;3.6.2 Exponential Functions;145 9.6.3;3.6.3 Scaling Functions;145 9.6.4;3.6.4 Converting Numbers to Words;146 10;4 Temporal Data Types in SQL;148 10.1;4.1 Notes on Calendar Standards;148 10.2;4.2 SQL Temporal Data Types;152 10.2.1;4.2.1 Tips for Handling Dates, Timestamps, and Times;153 10.2.2;4.2.2 Date Format Standards;153 10.2.3;4.2.3 Handling Timestamps;154 10.2.4;4.2.4 Handling Times;156 10.3;4.3 Queries with Date Arithmetic;157 10.4;4.4 The Nature of Temporal Data Models;158 10.4.1;4.4.1 Temporal Duplicates;158 10.4.2;4.4.2 Temporal Databases;164 10.4.3;4.4.3 Temporal Projection and Selection;166 10.4.4;4.4.4 Temporal Joins;168 10.4.5;4.4.5 Modifying Valid-Time State Tables;174 10.4.6;4.4.6 Current Modifications;175 10.4.7;4.4.7 Sequenced Modifications;179 10.4.8;4.4.8 Nonsequenced Modifications;184 10.4.9;4.4.9 Transaction-Time State Tables;185 10.4.10;4.4.10 Maintaining the Audit Log;187 10.4.11;4.4.11 Querying the Audit Log;189 10.4.12;4.4.12 Modifying the Audit Log;193 10.4.13;4.4.13 Bitemporal Tables;193 10.4.14;4.4.14 Temporal Support in Standard SQL;196 11;5 Character Data Types in SQL;198 11.1;5.1 Problems with SQL Strings;199 11.1.1;5.1.1 Problems of String Equality;199 11.1.2;5.1.2 Problems of String Ordering;200 11.1.3;5.1.3 Problems of String Grouping;201 11.2;5.2 Standard String Functions;201 11.3;5.3 Common Vendor Extensions;203 11.3.1;5.3.1 Phoneti
c Matching;204 11.4;5.4 Cutter Tables;211 12;6 NULLs: Missing Data in SQL;214 12.1;6.1 Empty and Missing Tables;216 12.2;6.2 Missing Values in Columns;216 12.3;6.3 Context and Missing Values;218 12.4;6.4 Comparing NULLs;219 12.5;6.5 NULLs and Logic;219 12.5.1;6.5.1 NULLS in Subquery Predicates;220 12.5.2;6.5.2 Standard SQL Solutions;222 12.6;6.6 Math and NULLs;222 12.7;6.7 Functions and NULLs;222 12.8;6.8 NULLs and Host Languages;223 12.9;6.9 Design Advice for NULLs;224 12.9.1;6.9.1 Avoiding NULLs from the Host Programs;226 12.10;6.10 A Note on Multiple NULL Values;227 13;7 Multiple Column Data Elements;230 13.1;7.1 Distance Functions;230 13.2;7.2 Storing an IP Address in SQL;231 13.2.1;7.2.1 A Single VARCHAR(15) Column;232 13.2.2;7.2.2 One INTEGER Column;232 13.2.3;7.2.3 Four SMALLINT Columns;234 13.3;7.3 Currency and Other Unit Conversions;234 13.4;7.4 Social Security Numbers;235 13.5;7.5 Rational Numbers;238 14;8 Table Operations;240 14.1;8.1 DELETE FROM Statement;240 14.1.1;8.1.1 The DELETE FROM Clause;241 14.1.2;8.1.2 The WHERE Clause;241 14.1.3;8.1.3 Deleting Based on Data in a Second Table;245 14.1.4;8.1.4 Deleting within the Same Table;245 14.1.5;8.1.5 Deleting in Multiple Tables without Referential Integrity;249 14.2;8.2 INSERT INTO Statement;250 14.2.1;8.2.1 INSERT INTO Clause;250 14.2.2;8.2.2 The Nature of Inserts;251 14.2.3;8.2.3 Bulk Load and Unload Utilities;252 14.3;8.3 The UPDATE Statement;252 14.3.1;8.3.1 The UPDATE Clause;252 14.3.2;8.3.2 The WHERE Clause;253 14.3.3;8.3.3 The SET Clause;254 14.3.4;8.3.4 Updating with a Second Table;255 14.3.5;8.3.5 Using the CASE Expression in UPDATEs;257 14.4;8.4 A Note on Flaws in a Common Vendor Extension;260 14.5;8.5 MERGE Statement;261 15;9 Comparison or Theta Operators;264 15.1;9.1 Converting Data Types;265 15.2;9.2 Row Comparisons in SQL;267 16;10 Valued Predicates;270 16.1;10.1 IS NULL Predicate;270 16.1.1;10.1.1 Sources of NULLs;271 16.2;10.2 IS [NOT]{TRUE | FALSE | UNKNOWN} Predicate;271 16.3;10.3 IS [NOT
] NORMALIZED Predicate;273 17;11 CASE Expressions;276 17.1;11.1 The CASE Expression;276 17.1.1;11.1.1 The COALESCE() and NULLIF() Functions;280 17.1.2;11.1.2 CASE Expressions with GROUP BY;281 17.1.3;11.1.3 CASE, CHECK() Clauses and Logical Implication;282 17.1.4;11.1.4 Subquery Expressions and Constants;286 17.2;11.2 Rozenshtein Characteristic Functions;287 18;12 LIKE Predicate;290 18.1;12.1 Tricks with Patterns;291 18.2;12.2 Results with NULL Values and Empty Strings;293 18.3;12.3 LIKE Is Not Equality;293 18.4;12.4 Avoiding the LIKE Predicate with a Join;293 18.5;12.5 CASE Expressions and LIKE Predicates;295 18.6;12.6 SIMILAR TO Predicates;296 18.7;12.7 Tricks with Strings;298 18.7.1;12.7.1 String Character Content;298 18.7.2;12.7.2 Searching versus Declaring a String;299 18.7.3;12.7.3 Creating an Index on a String;299 19;13 BETWEEN and OVERLAPS Predicates;302 19.1;13.1 The BETWEEN Predicate;302 19.1.1;13.1.1 Results with NULL Values;303 19.1.2;13.1.2 Results with Empty Sets;303 19.1.3;13.1.3 Programming Tips;303 19.2;13.2 OVERLAPS Predicate;304 19.2.1;13.2.1 Time Periods and OVERLAPS Predicate;304 20;14 The [NOT] IN() Predicate;316 20.1;14.1 Optimizing the IN() Predicate;317 20.2;14.2 Replacing ORs with the IN() Predicate;321 20.3;14.3 NULLs and the IN() Predicate;322 20.4;14.4 IN() Predicate and Referential Constraints;324 20.5;14.5 IN() Predicate and Scalar Queries;326 21;15 EXISTS() Predicate;328 21.1;15.1 EXISTS and NULLs;329 21.2;15.2 EXISTS and INNER JOINs;331 21.3;15.3 NOT EXISTS and OUTER JOINs;332 21.4;15.4 EXISTS() and Quantifiers;333 21.5;15.5 EXISTS() and Referential Constraints;334 21.6;15.6 EXISTS and Three-Valued Logic;335 22;16 Quantified Subquery Predicates;338 22.1;16.1 Scalar Subquery Comparisons;339 22.2;16.2 Quantifiers and Missing Data;340 22.3;16.3 The ALL Predicate and Extrema Functions;342 22.4;16.4 The UNIQUE Predicate;343 23;17 The SELECT Statement;346 23.1;17.1 SELECT and JOINs;346 23.1.1;17.1.1 One-Level SELECT Statement;346 23.1.2;17
.1.2 Correlated Subqueries in a SELECT Statement;353 23.1.3;17.1.3 SELECT Statement Syntax;355 23.1.4;17.1.4 The ORDER BY Clause;357 23.2;17.2 OUTER JOINs;365 23.2.1;17.2.1 Syntax for OUTER JOINs;366 23.2.2;17.2.2 NULLs and OUTER JOINs;371 23.2.3;17.2.3 NATURAL versus Searched OUTER JOINs;373 23.2.4;17.2.4 Self OUTER JOINs;374 23.2.5;17.2.5 Two or More OUTER JOINs;375 23.2.6;17.2.6 OUTER JOINs and Aggregate Functions;377 23.2.7;17.2.7 FULL OUTER JOIN;378 23.2.8;17.2.8 WHERE Clause OUTER JOIN Operators;379 23.3;17.3 Old versus New JOIN Syntax;380 23.4;17.4 Scope of Derived Table Names;382 23.5;17.5 JOINs by Function Calls;383 23.6;17.6 The UNION JOIN;385 23.7;17.7 Packing Joins;387 23.8;17.8 Dr. Codd's T-Join;388 23.8.1;17.8.1 The Croatian Solution;392 23.8.2;17.8.2 The Swedish Solution;393 23.8.3;17.8.3 The Colombian Solution;393 24;18 VIEWs, Derived Tables, Materialized Tables, and Temporary Tables;398 24.1;18.1 VIEWs in Queries;399 24.2;18.2 Updatable and Read-Only VIEWs;400 24.3;18.3 Types of VIEWs;402 24.3.1;18.3.1 Single-Table Projection and Restriction;402 24.3.2;18.3.2 Calculated Columns;402 24.3.3;18.3.3 Translated Columns;402 24.3.4;18.3.4 Grouped VIEWs;403 24.3.5;18.3.5 UNIONed VIEWs;404 24.3.6;18.3.6 JOINs in VIEWs;406 24.3.7;18.3.7 Nested VIEWs;406 24.4;18.4 How VIEWs Are Handled in the Database System;408 24.4.1;18.4.1 View Column List;408 24.4.2;18.4.2 VIEW Materialization;408 24.4.3;18.4.3 In-Line Text Expansion;409 24.4.4;18.4.4 Pointer Structures;411 24.4.5;18.4.5 Indexing and Views;412 24.5;18.5 WITH CHECK OPTION Clause;412 24.5.1;18.5.1 WITH CHECK OPTION as CHECK() Clause;417 24.6;18.6 Dropping VIEWs;418 24.7;18.7 TEMPORARY TABLE Declarations;419 24.8;18.8 Hints on Using VIEWs and TEMPORARY TABLEs;420 24.8.1;18.8.1 Using VIEWs;421 24.8.2;18.8.2 Using TEMPORARY TABLEs;421 24.8.3;18.8.3 Flattening a Table with a VIEW;422 24.9;18.9 Using Derived Tables;424 24.9.1;18.9.1 Derived Tables in the FROM clause;424 24.9.2;18.9.2 Derived Tables with a VALUES
Constructor;426 24.10;18.10 Derived Tables in the WITH Clause;426 25;19 Partitioning Data in Queries;430 25.1;19.1 Coverings and Partitions;430 25.1.1;19.1.1 Partitioning by Ranges;431 25.1.2;19.1.2 Partition by Functions;432 25.1.3;19.1.3 Partition by Sequences;433 25.2;19.2 Relational Division;435 25.2.1;19.2.1 Division with a Remainder;437 25.2.2;19.2.2 Exact Division;438 25.2.3;19.2.3 Note on Performance;439 25.2.4;19.2.4 Todd's Division;439 25.2.5;19.2.5 Division with JOINs;442 25.2.6;19.2.6 Division with Set Operators;442 25.3;19.3 Romley's Division;443 25.4;19.4 Boolean Expressions in an RDBMS;447 25.5;19.5 FIFO and LIFO Subsets;449 26;20 Grouping Operations;454 26.1;20.1 GROUP BY Clause;454 26.1.1;20.1.1 NULLs and Groups;456 26.2;20.2 GROUP BY and HAVING;456 26.2.1;20.2.1 Group Characteristics and the HAVING Clause;458 26.3;20.3 Multiple Aggregation Levels;460 26.3.1;20.3.1 Grouped VIEWs for Multiple Aggregation Levels;461 26.3.2;20.3.2 Subquery Expressions for Multiple Aggregation Levels;462 26.3.3;20.3.3 CASE Expressions for Multiple Aggregation Levels;463 26.4;20.4 Grouping on Computed Columns;464 26.5;20.5 Grouping into Pairs;465 26.6;20.6 Sorting and GROUP BY;466 27;21 Aggregate Functions;468 27.1;21.1 COUNT() Functions;469 27.2;21.2 SUM() Functions;472 27.3;21.3 AVG() Functions;473 27.3.1;21.3.1 Averages with Empty Groups;475 27.3.2;21.3.2 Averages across Columns;477 27.4;21.4 Extrema Functions;478 27.4.1;21.4.1 Simple Extrema Functions;478 27.4.2;21.4.2 Generalized Extrema Functions;480 27.4.3;21.4.3 Multiple Criteria Extrema Functions;489 27.4.4;21.4.4 GREATEST() and LEAST() Functions;491 27.5;21.5 The LIST() Aggregate Function;494 27.5.1;21.5.1 The LIST() Function with a Procedure;495 27.5.2;21.5.2 The LIST() Function by Crosstabs;496 27.6;21.6 The PRD() Aggregate Function;497 27.6.1;21.6.1 PRD() Function by Expressions;498 27.6.2;21.6.2 The PRD() Aggregate Function by Logarithms;499 27.7;21.7 Bitwise Aggregate Functions;502 27.7.1;21.7.1 Bitwise OR
Aggregate Function;503 27.7.2;21.7.2 Bitwise AND Aggregate Function;504 28;22 Auxiliary Tables;506 28.1;22.1 The Sequence Table;506 28.1.1;22.1.1 Enumerating a List;508 28.1.2;22.1.2 Mapping a Sequence into a Cycle;510 28.1.3;22.1.3 Replacing an Iterative Loop;512 28.2;22.2 Lookup Auxiliary Tables;514 28.2.1;22.2.1 Simple Translation Auxiliary Tables;516 28.2.2;22.2.2 Multiple Translation Auxiliary Tables;516 28.2.3;22.2.3 Multiple Parameter Auxiliary Tables;517 28.2.4;22.2.4 Range Auxiliary Tables;518 28.2.5;22.2.5 Hierarchical Auxiliary Tables;519 28.2.6;22.2.6 One True Lookup Table;520 28.3;22.3 Auxiliary Function Tables;522 28.3.1;22.3.1 Inverse Functions with Auxiliary Tables;524 28.3.2;22.3.2 Interpolation with Auxiliary Function Tables;533 28.4;22.4 Global Constants Tables;535 29;23 Statistics in SQL;538 29.1;23.1 The Mode;539 29.2;23.2 The AVG() Function;541 29.3;23.3 The Median;541 29.3.1;23.3.1 Date's First Median;542 29.3.2;23.3.2 Celko's First Median;543 29.3.3;23.3.3 Date's Second Median;545 29.3.4;23.3.4 Murchison's Median;545 29.3.5;23.3.5 Celko's Second Median;546 29.3.6;23.3.6 Vaughan's Median with VIEWs;548 29.3.7;23.3.7 Median with Characteristic Function;549 29.3.8;23.3.8 Celko's Third Median;551 29.3.9;23.3.9 Ken Henderson's Median;555 29.4;23.4 Variance and Standard Deviation;556 29.5;23.5 Average Deviation;557 29.6;23.6 Cumulative Statistics;557 29.6.1;23.6.1 Running Totals;558 29.6.2;23.6.2 Running Differences;559 29.6.3;23.6.3 Cumulative Percentages;560 29.6.4;23.6.4 Rankings and Related Statistics;562 29.6.5;23.6.5 Quintiles and Related Statistics;566 29.7;23.7 Cross Tabulations;567 29.7.1;23.7.1 Crosstabs by Cross Join;571 29.7.2;23.7.2 Crosstabs by Outer Joins;572 29.7.3;23.7.3 Crosstabs by Subquery;573 29.7.4;23.7.4 Crosstabs by CASE Expression;574 29.8;23.8 Harmonic Mean and Geometric Mean;574 29.9;23.9 Multivariable Descriptive Statistics in SQL;575 29.9.1;23.9.1 Covariance;575 29.9.2;23.9.2 Pearson's r;576 29.9.3;23.9.3 NULLs in Mult
ivariable Descriptive Statistics;577 30;24 Regions, Runs, Gaps, Sequences, and Series;578 30.1;24.1 Finding Subregions of Size (n);579 30.2;24.2 Numbering Regions;580 30.3;24.3 Finding Regions of Maximum Size;581 30.4;24.4 Bound Queries;586 30.5;24.5 Run and Sequence Queries;586 30.5.1;24.5.1 Filling in Sequence Numbers;589 30.6;24.6 Summation of a Series;591 30.7;24.7 Swapping and Sliding Values in a List;594 30.8;24.8 Condensing a List of Numbers;596 30.9;24.9 Folding a List of Numbers;596 30.10;24.10 Coverings;597 31;25 Arrays in SQL;604 31.1;25.1 Arrays via Named Columns;605 31.2;25.2 Arrays via Subscript Columns;609 31.3;25.3 Matrix Operations in SQL;610 31.3.1;25.3.1 Matrix Equality;611 31.3.2;25.3.2 Matrix Addition;611 31.3.3;25.3.3 Matrix Multiplication;612 31.3.4;25.3.4 Other Matrix Operations;614 31.4;25.4 Flattening a Table into an Array;614 31.5;25.5 Comparing Arrays in Table Format;616 32;26 Set Operations;620 32.1;26.1 UNION and UNION ALL;621 32.1.1;26.1.1 Order of Execution;623 32.1.2;26.1.2 Mixed UNION and UNION ALL Operators;624 32.1.3;26.1.3 UNION of Columns from the Same Table;624 32.2;26.2 INTERSECT and EXCEPT;625 32.2.1;26.2.1 INTERSECT and EXCEPT without NULLs and Duplicates;628 32.2.2;26.2.2 INTERSECT and EXCEPT with NULLs and Duplicates;629 32.3;26.3 A Note on ALL and SELECT DISTINCT;630 32.4;26.4 Equality and Proper Subsets;631 33;27 Subsets;634 33.1;27.1 Every nth Item in a Table;634 33.2;27.2 Picking Random Rows from a Table;636 33.3;27.3 The CONTAINS Operators;641 33.3.1;27.3.1 Proper Subset Operators;641 33.3.2;27.3.2 Table Equality;642 33.4;27.4 Picking a Representative Subset;647 34;28 Trees and Hierarchies in SQL;652 34.1;28.1 Adjacency List Model;653 34.1.1;28.1.1 Complex Constraints;654 34.1.2;28.1.2 Procedural Traversal for Queries;656 34.1.3;28.1.3 Altering the Table;657 34.2;28.2 The Path Enumeration Model;657 34.2.1;28.2.1 Finding Subtrees and Nodes;658 34.2.2;28.2.2 Finding Levels and Subordinates;659 34.2.3;28.2.3 Deleting Nod
es and Subtrees;659 34.2.4;28.2.4 Integrity Constraints;660 34.3;28.3 Nested Set Model of Hierarchies;660 34.3.1;28.3.1 The Counting Property;662 34.3.2;28.3.2 The Containment Property;663 34.3.3;28.3.3 Subordinates;664 34.3.4;28.3.4 Hierarchical Aggregations;665 34.3.5;28.3.5 Deleting Nodes and Subtrees;665 34.3.6;28.3.6 Converting Adjacency List to Nested Set Model;666 34.4;28.4 Other Models for Trees and Hierarchies;668 35;29 Temporal Queries;670 35.1;29.1 Temporal Math;671 35.2;29.2 Personal Calendars;672 35.3;29.3 Time Series;674 35.3.1;29.3.1 Gaps in a Time Series;674 35.3.2;29.3.2 Continuous Time Periods;677 35.3.3;29.3.3 Missing Times in Contiguous Events;681 35.3.4;29.3.4 Locating Dates;685 35.3.5;29.3.5 Temporal Starting and Ending Points;687 35.3.6;29.3.6 Average Wait Times;689 35.4;29.4 Julian Dates;690 35.5;29.5 Date and Time Extraction Functions;694 35.6;29.6 Other Temporal Functions;695 35.7;29.7 Weeks;696 35.7.1;29.7.1 Sorting by Weekday Names;698 35.8;29.8 Modeling Time in Tables;699 35.8.1;29.8.1 Using Duration Pairs;701 35.9;29.9 Calendar Auxiliary Table;702 35.10;29.10 Problems with the Year 2000;704 35.10.1;29.10.1 The Zeros;704 35.10.2;29.10.2 Leap Year;705 35.10.3;29.10.3 The Millennium;706 35.10.4;29.10.4 Weird Dates in Legacy Data;708 35.10.5;29.10.5 The Aftermath;709 36;30 Graphs in SQL;710 36.1;30.1 Basic Graph Characteristics;711 36.1.1;30.1.1 All Nodes in the Graph;711 36.1.2;30.1.2 Path Endpoints;712 36.1.3;30.1.3 Reachable Nodes;712 36.1.4;30.1.4 Edges;713 36.1.5;30.1.5 Indegree and Outdegree;713 36.1.6;30.1.6 Source, Sink, Isolated, and Internal Nodes;714 36.2;30.2 Paths in a Graph;715 36.2.1;30.2.1 Length of Paths;716 36.2.2;30.2.2 Shortest Path;716 36.2.3;30.2.3 Paths by Iteration;717 36.2.4;30.2.4 Listing the Paths;720 36.3;30.3 Acyclic Graphs as Nested Sets;724 36.4;30.4 Paths with CTE;726 36.4.1;30.4.1 Nonacyclic Graphs;732 36.5;30.5 Adjacency Matrix Model;734 36.6;30.6 Points inside Polygons;735 37;31 OLAP in SQL;738 37.1;31.1 S
tar Schema;739 37.2;31.2 OLAP Functionality;740 37.2.1;31.2.1 RANK and DENSE_RANK;740 37.2.2;31.2.2 Row Numbering;740 37.2.3;31.2.3 GROUPING Operators;741 37.2.4;31.2.4 The Window Clause;743 37.2.5;31.2.5 OLAP Examples of SQL;745 37.2.6;31.2.6 Enterprise-Wide Dimensional Layer;746 37.3;31.3 A Bit of History;747 38;32 Transactions and Concurrency Control;748 38.1;32.1 Sessions;748 38.2;32.2 Transactions and ACID;749 38.2.1;32.2.1 Atomicity;749 38.2.2;32.2.2 Consistency;750 38.2.3;32.2.3 Isolation;750 38.2.4;32.2.4 Durability;751 38.3;32.3 Concurrency Control;751 38.3.1;32.3.1 The Five Phenomena;751 38.3.2;32.3.2 The Isolation Levels;753 38.3.3;32.3.3 CURSOR STABILITY Isolation Level;755 38.4;32.4 Pessimistic Concurrency Control;755 38.5;32.5 SNAPSHOT Isolation: Optimistic Concurrency;756 38.6;32.6 Logical Concurrency Control;758 38.7;32.7 Deadlock and Livelocks;759 39;33 Optimizing SQL;760 39.1;33.1 Access Methods;761 39.1.1;33.1.1 Sequential Access;761 39.1.2;33.1.2 Indexed Access;761 39.1.3;33.1.3 Hashed Indexes;762 39.1.4;33.1.4 Bit Vector Indexes;762 39.2;33.2 Expressions and Unnested Queries;762 39.2.1;33.2.1 Use Simple Expressions;763 39.2.2;33.2.2 String Expressions;767 39.3;33.3 Give Extra Join Information in Queries;767 39.4;33.4 Index Tables Carefully;769 39.5;33.5 Watch the IN Predicate;771 39.6;33.6 Avoid UNIONs;773 39.7;33.7 Prefer Joins over Nested Queries;774 39.8;33.8 Avoid Expressions on Indexed Columns;775 39.9;33.9 Avoid Sorting;775 39.10;33.10 Avoid CROSS JOINs;779 39.11;33.11 Learn to Use Indexes Carefully;780 39.12;33.12 Order Indexes Carefully;781 39.13;33.13 Know Your Optimizer;783 39.14;33.14 Recompile Static SQL after Schema Changes;785 39.15;33.15 Temporary Tables Are Sometimes Handy;786 39.16;33.16 Update Statistics;789 40;back matter;790 41;Appendix: References;790 41.1;General References;790 41.2;Logic;790 41.3;Mathematical Techniques;790 41.4;Random Numbers;791 41.5;Scales and Measurements;792 41.6;Missing Values;792 41.7;Regular Expres
sions;793 41.8;Graph Theory;794 41.9;Introductory SQL Books;794 41.10;Optimizing Queries;795 41.11;Temporal Data and the Year 2000 Problem;795 41.12;SQL Programming Techniques;797 41.13;Classics;797 41.14;Forum;798 41.15;Updatable Views;798 41.16;Theory, Normalization, and Advanced Database Topics;799 41.17;Books on SQL-92 and SQL-99;800 41.18;Standards and Related Groups;800 41.19;Web Sites Related to SQL;801 41.20;Statistics;801 41.21;Temporal Databases;802 41.22;New Citations;803 42;index;806 43;About the Author;839