Celko Joe Celko's SQL for Smarties

Advanced SQL Programming
4. Auflage 2010
ISBN: 978-0-12-382023-5
Verlag: Elsevier Science & Techn.
Format: EPUB
Kopierschutz: 6 - ePub Watermark

E-Book, Englisch, 816 Seiten

Reihe: The Morgan Kaufmann Series in Data Management Systems

Joe Celkos SQL for Smarties: Advanced SQL Programming offers tips and techniques in advanced programming. This book is the fourth edition and it consists of 39 chapters, starting with a comparison between databases and file systems. It covers transactions and currency control, schema level objects, locating data and schema numbers, base tables, and auxiliary tables. Furthermore, procedural, semi-procedural, and declarative programming are explored in this book. The book also presents the different normal forms in database normalization, including the first, second, third, fourth, fifth, elementary key, domain-key, and Boyce-Codd normal forms. It also offers practical hints for normalization and denormalization. The book discusses different data types, such as the numeric, temporal and character data types; the different predicates; and the simple and advanced SELECT statements. In addition, the book presents virtual tables, and it discusses data partitions in queries; grouping operations; simple aggregate functions; and descriptive statistics, matrices and graphs in SQL. The book concludes with a discussion about optimizing SQL. It will be of great value to SQL programmers. - 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

Joe Celko served 10 years on ANSI/ISO SQL Standards Committee and contributed to the SQL-89 and SQL-92 Standards. Mr. Celko is author a series of books on SQL and RDBMS for Elsevier/MKP. He is an independent consultant based in Austin, Texas. He has written over 1200 columns in the computer trade and academic press, mostly dealing with data and databases.

Autoren/Hrsg.

Celko, Joe

Inhaltsverzeichnis

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