Joe Celko's SQL for smarties : advanced sql programming

Publisher: Elsevier
Year: 2011
Tongue: English
Leaves: 772
Series: The Morgan Kaufmann series in data management systems
Edition: 4th ed
Category: Library

No coin nor oath required. For personal study only.

✦ Synopsis

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

✦ Table of Contents

Databases versus File Systems......Page 3
Tables as Relationships......Page 6
Rows versus Records......Page 7
Columns versus Fields......Page 8
CREATE SCHEMA Statement......Page 9
Sessions......Page 13
Atomicity......Page 14
Consistency......Page 15
The Three Phenomena......Page 16
The Isolation Levels......Page 18
Pessimistic Concurrency Control......Page 20
SNAPSHOT Isolation and Optimistic Concurrency......Page 21
Deadlock and Livelocks......Page 23
CREATE SCHEMA Statement......Page 25
CREATE DOMAIN Statement......Page 26
CREATE SEQUENCE......Page 27
Using VIEWs for Schema Level Constraints......Page 28
Using PRIMARY KEYs and ASSERTIONs for Constraints......Page 32
Character Set Related Constructs......Page 33
CREATE COLLATION......Page 34
CREATE TRANSLATION......Page 35
ROWID and Physical Disk Addresses......Page 36
IDENTITY Columns......Page 37
GUIDs......Page 41
UUIDs......Page 42
Unique Value Generators......Page 43
Preallocated Values......Page 44
Special Series......Page 45
Prime Numbers......Page 46
Random Order Values......Page 49
Other Series......Page 51
Base Tables and Related Elements......Page 52
Column Constraints......Page 54
DEFAULT Clause......Page 55
NOT NULL Constraint......Page 56
CHECK( ) Constraints......Page 57
UNIQUE and PRIMARY KEY Constraints......Page 59
REFERENCES Clause......Page 61
Nested UNIQUE Constraints......Page 63
Overlapping Keys......Page 66
Single versus Multiple-Column Uniqueness......Page 69
CREATE ASSERTION Constraints......Page 77
TEMPORARY TABLE Declarations......Page 78
Manipulating Tables......Page 79
DROP TABLE

......Page 80
ALTER TABLE......Page 81
Table Level Attribute Splitting......Page 82
Modeling Class Hierarchies in DDL......Page 84
Auto-Incrementing Columns......Page 86
IDENTITY Columns......Page 89
Comparing IDENTITY Columns and Sequences......Page 90
Industry Standard Unique Identifiers......Page 91
Sequence Generator Functions......Page 92
Unique Value Generators......Page 93
Verification Sources......Page 94
A Remark on Duplicate Rows......Page 95
Other Schema Objects......Page 97
Temporary Tables......Page 98
CREATE DOMAIN Statement......Page 99
CREATE PROCEDURE Statement......Page 100
DECLARE CURSOR Statement......Page 101
How to Use a CURSOR......Page 103
Positioned UPDATE and DELETE Statements......Page 105
Cohesion......Page 106
Coupling......Page 107
The Big Leap......Page 108
An Improvement......Page 109
Rewriting Tricks......Page 115
Data Tables versus Generator Code......Page 116
Fibonacci Series......Page 117
Functions for Predicates......Page 119
Procedural Decomposition Solution......Page 120
Logical Decomposition Solution......Page 122
CREATE PROCEDURE......Page 124
CREATE TRIGGER......Page 125
CURSORs......Page 128
DECLARE CURSOR Statement......Page 129
The ORDER BY clause......Page 130
The ORDER BY and NULLs......Page 131
The ORDER BY and CASE Expressions......Page 135
FETCH Statement......Page 137
How to Use a CURSOR......Page 138
SEQUENCEs......Page 142
Generated Columns......Page 143
Table Functions......Page 144
The Series Table......Page 146
Enumerating a List......Page 147
Mapping a Series into a Cycle......Page 149
Replacing an Iterative Loop......Page 151
Lookup Auxiliary Tables......Page 152
Multiple Translation Auxiliary Tables......Page 154
Multiple Parameter Auxiliary Tables......Page 155
Range Auxiliary Tables......Page 156
One True Look-up Table......Page 157
Auxiliary Function Tables......Page 160
Inverse Functions with Auxiliary Tables......Page 161
Interpolation with Auxiliary Function Tables......Page 169
Global Constants Tables......Page 170
Preallocated Values......Page 171
Fibonacci Numbers......Page 172
Random Order Values......Page 173
A Note on Converting Procedural Code to Tables......Page 176
Normalization......Page 181
Functional and Multivalued Dependencies......Page 183
First Normal Form (1NF)......Page 184
Repeating Columns......Page 186
Parsing a List in a String......Page 187
Second Normal Form (2NF)......Page 188
Third Normal Form (3NF)......Page 189
Elementary Key Normal Form (EKNF)......Page 191
Boyce-Codd Normal Form (BCNF)......Page 192
Fifth Normal Form (5NF)......Page 194
Domain-Key Normal Form (DKNF)......Page 196
Practical Hints for Normalization......Page 204
Natural Keys......Page 205
Artificial Keys......Page 206
Exposed Physical Locators......Page 207
Practical Hints for Denormalization......Page 208
Row Sorting......Page 210
Numeric Types......Page 215
BIT, BYTE, and BOOLEAN Data Types......Page 218
Rounding and Truncating......Page 220
Four Function Arithmetic......Page 222
Arithmetic and NULLs......Page 224
NULLIF( ) Function......Page 225
COALESCE( ) Function......Page 226
Number Theory Operators......Page 228
Exponential Functions......Page 230
Converting Numbers to Words......Page 231
Unique Value Generators......Page 232
Sequences with Gaps......Page 233
Preallocated Values......Page 234
Binary Storage......Page 235
Separate SMALLINTs......Page 236
Notes on Calendar Standards......Page 237
SQL Temporal Data Types......Page 240
Internal Representations......Page 241
Handling Timestamps......Page 242
Time Zones and DST......Page 245
INTERVAL Data Types......Page 246
Temporal Arithmetic......Page 249
Modeling Durations......Page 250
Relationships among Durations......Page 252
Problems with SQL Strings......Page 254
Problems of String Equality......Page 255
Problems of String Grouping......Page 256
Standard String Functions......Page 257
Common Vendor Extensions......Page 258
Soundex Functions......Page 260
The Original Soundex......Page 261
Metaphone......Page 263
NYSIIS Algorithm......Page 266
Cutter Tables......Page 267
Nested Replacement......Page 268
NULLs: Missing Data in SQL......Page 269
Empty and Missing Tables......Page 270
Missing Values in Columns......Page 271
Context and Missing Values......Page 273
Comparing NULLs......Page 274
NULLs and Logic......Page 275
NULLs in Subquery Predicates......Page 277
Logical Value Predicate......Page 278
NULLs and Host Languages......Page 279
Design Advice for NULLs......Page 280
Avoiding NULLs from the Host Programs......Page 282
A Note on Multiple NULL Values......Page 283
Distance Functions......Page 286
A Single VARCHAR(15) Column......Page 288
One INTEGER Column......Page 289
Storing an IPv6 Address in SQL......Page 290
Currency and Other Unit Conversions......Page 291
Social Security Numbers......Page 292
Rational Numbers......Page 295
The DELETE FROM Clause......Page 296
The WHERE Clause......Page 297
Deleting within the Same Table......Page 300
Redundant Duplicates in a Table......Page 301
Redundant Duplicates Removal with ROWID......Page 302
INSERT INTO Statement......Page 304
INSERT INTO Clause......Page 305
Bulk Load and Unload Utilities......Page 306
The UPDATE Clause......Page 307
The SET Clause......Page 308
Updating with a Second Table......Page 309
Using the CASE Expression in UPDATEs......Page 312
A Note on Flaws in a Common Vendor Extension......Page 314
MERGE Statement......Page 316
Converting Data Types......Page 319
Other Display Formats......Page 321
Row Comparisons in SQL......Page 322
IS [NOT] DISTINCT FROM Operator......Page 324
IS NULL......Page 325
IS [NOT]{TRUE | FALSE | UNKNOWN} Predicate......Page 326
IS [NOT] NORMALIZED Predicate......Page 328
The CASE Expression......Page 329
The COALESCE() and NULLIF() Functions......Page 332
CASE Expressions with GROUP BY......Page 333
CASE, CHECK() Clauses and Logical Implication......Page 334
Subquery Expressions and Constants......Page 337
Rozenshtein Characteristic Functions......Page 338
LIKE and SIMILAR TO Predicates......Page 340
Tricks with Patterns......Page 341
Results with NULL Values and Empty Strings......Page 342
Avoiding the LIKE Predicate with a Join......Page 343
CASE Expressions and LIKE Search Conditions......Page 344
SIMILAR TO Predicates......Page 345
String Character Content......Page 347
Searching versus Declaring a String......Page 348
Creating an Index on a String......Page 349
The BETWEEN Predicate......Page 350
Results with NULL Values......Page 351
Programming Tips......Page 352
Time Periods and OVERLAPS Predicate......Page 353
The [NOT] IN() Predicate......Page 363
Optimizing the IN() Predicate......Page 364
Replacing ORs with the IN() Predicate......Page 367
NULLs and the IN() Predicate......Page 368
IN() Predicate and Referential Constraints......Page 370
IN() Predicate and Scalar Queries......Page 371
EXISTS() Predicate......Page 374
EXISTS and NULLs......Page 375
EXISTS and INNER JOINs......Page 377
EXISTS() and Quantifiers......Page 378
EXISTS() and Referential Constraints......Page 379
EXISTS and Three-Valued Logic......Page 380
Scalar Subquery Comparisons......Page 382
Quantifiers and Missing Data......Page 384
The ALL Predicate and Extrema Functions......Page 386
The UNIQUE Predicate......Page 387
The DISTINCT Predicate......Page 388
One-Level SELECT Statement......Page 389
Correlated Subqueries......Page 398
Infixed INNER JOINs......Page 402
OUTER JOINs......Page 404
A Bit of History......Page 405
NULLs and OUTER JOINs......Page 409
NATURAL versus Searched OUTER JOINs......Page 411
Self OUTER JOINs......Page 412
Two or More OUTER JOINs......Page 413
FULL OUTER JOIN......Page 415
UNION JOIN Operators......Page 416
Scalar SELECT Expressions......Page 417
Old versus New JOIN Syntax......Page 418
Constrained JOINs......Page 419
Inventory and Orders......Page 420
Stable Marriages......Page 421
Ball and Box Packing......Page 425
Dr. Codd’s T-Join......Page 428
Stobbs Solution......Page 432
Pierre’s Solution......Page 433
References......Page 434
VIEWs in Queries......Page 435
Updatable and Read-Only VIEWs......Page 436
Translated Columns......Page 438
Grouped VIEWs......Page 439
UNION-ed VIEWs......Page 440
Nested VIEWs......Page 442
VIEW Materialization......Page 443
In-Line Text Expansion......Page 444
Pointer Structures......Page 446
WITH CHECK OPTION Clause......Page 447
WITH CHECK OPTION as CHECK() clause......Page 451
Dropping VIEWs......Page 452
Using VIEWs......Page 453
Flattening a Table with a VIEW......Page 454
Using Derived Tables......Page 456
Derived Tables in the FROM Clause......Page 457
Simple Common Table Expressions......Page 458
Recursive Common Table Expressions......Page 459
Simple Incrementation......Page 460
Simple Tree Traversal......Page 461
Materialized Query Tables......Page 462
Partitioning by Ranges......Page 463
Single-Column Range Tables......Page 464
Partition by Sequences......Page 465
Relational Division......Page 468
Division with a Remainder......Page 470
Note on Performance......Page 471
Todd’s Division......Page 472
Division with JOINs......Page 474
Romley’s Division......Page 475
Boolean Expressions in an RDBMS......Page 479
FIFO and LIFO Subsets......Page 480
GROUP BY Clause......Page 483
NULLs and Groups......Page 484
GROUP BY and HAVING......Page 485
Group Characteristics and HAVING Clause......Page 486
Grouped VIEWs for Multiple Aggregation Levels......Page 488
Subquery Expressions for Multiple Aggregation Levels......Page 489
Grouping on Computed Columns......Page 491
Grouping into Pairs......Page 492
Sorting and GROUP BY......Page 494
Simple Aggregate Functions......Page 496
COUNT( ) Functions......Page 497
Optimizing Aggregates with DISTINCT......Page 499
SUM( ) Function......Page 500
AVG( ) Function......Page 501
Averages with Empty Groups......Page 503
Averages across Columns......Page 505
Simple Extrema Functions......Page 506
Generalized Extrema Functions......Page 508
Multiple Criteria Extrema Functions......Page 515
GREATEST( ) and LEAST ( ) Functions......Page 516
LIST Aggregate with Recursive CTE......Page 519
The LIST( ) Function by Crosstabs......Page 520
The PRD( ) Aggregate Function......Page 521
PRD( ) Function by Expressions......Page 522
The PRD( ) Aggregate Function by Logarithms......Page 523
Bitwise Aggregate Functions......Page 525
Bitwise OR Aggregate Functions......Page 526
Bitwise AND Aggregate Function......Page 527
Advanced Grouping, Windowed Aggregation, and OLAP in SQL......Page 528
GROUPING Operators......Page 529
GROUP BY GROUPING SET......Page 530
CUBES......Page 531
OLAP Examples of SQL......Page 532
PARTITION BY Subclause......Page 533
ORDER BY Subclause......Page 534
Window Frame Subclause......Page 535
Row Numbering......Page 536
PERCENT_RANK() and CUME_DIST......Page 537
Some Examples......Page 538
Vendor Extensions......Page 539
LEAD and LAG Functions......Page 540
FIRST and LAST Functions......Page 541
A Bit of History......Page 542
The Mode......Page 544
The AVG( ) Function......Page 545
The Median......Page 546
The Median as a Programming Problem......Page 547
Celko’s First Median......Page 548
Murchison’s Median......Page 549
Celko’s Second Median......Page 550
Median with Characteristic Function......Page 552
Celko’s Third Median......Page 555
Ken Henderson’s Median......Page 558
OLAP Medians......Page 559
Variance and Standard Deviation......Page 561
Cumulative Statistics......Page 562
Running Differences......Page 563
Cumulative Percentages......Page 564
Ordinal Functions......Page 566
Cross Tabulations......Page 571
Crosstabs by Cross Join......Page 574
Crosstabs by OUTER JOINs......Page 575
Crosstabs by Subquery......Page 576
Harmonic Mean and Geometric Mean......Page 577
Pearson’s r......Page 578
NULLs in Multivariable Descriptive Statistics......Page 579
Variance, Standard Deviation, and Descriptive Stats......Page 580
Distribution Functions......Page 581
Subsequences, Regions, Runs, Gaps, and Islands......Page 583
Finding Subregions of Size (n)......Page 584
Numbering Regions......Page 585
Finding Regions of Maximum Size......Page 586
Bound Queries......Page 590
Run and Sequence Queries......Page 591
Filling in Missing Numbers......Page 593
Summation of a Series......Page 595
Swapping and Sliding Values in a List......Page 598
Folding a List of Numbers......Page 600
Coverings......Page 601
Arrays via Named Columns......Page 605
Arrays via Subscript Columns......Page 609
Matrix Operations in SQL......Page 610
Matrix Addition......Page 611
Matrix Multiplication......Page 612
Matrix Transpose......Page 613
Other Matrix Operations......Page 614
Flattening a Table into an Array......Page 615
Comparing Arrays in Table Format......Page 616
Set Operations......Page 619
UNION and UNION ALL......Page 620
Mixed UNION and UNION ALL Operators......Page 622
INTERSECT and EXCEPT......Page 623
INTERSECT and EXCEPT without NULLs and Duplicates......Page 626
INTERSECT and EXCEPT with NULLs and Duplicates......Page 627
Equality and Proper Subsets......Page 628
Every N-th Item in a Table......Page 630
Random Rows from a Table......Page 631
Proper Subset Operators......Page 636
Table Equality......Page 637
Subset Equality......Page 640
Gaps in a Series......Page 641
Covering for Overlapping Intervals......Page 643
Picking a Representative Subset......Page 646
Trees and Hierarchies in SQL......Page 651
Adjacency List Model......Page 652
Complex Constraints......Page 653
Procedural Traversal for Queries......Page 655
The Path Enumeration Model......Page 656
Finding Subtrees and Nodes......Page 657
Integrity Constraints......Page 658
Nested Set Model of Hierarchies......Page 659
The Containment Property......Page 661
Subordinates......Page 662
Deleting Nodes and Subtrees......Page 663
Converting Adjacency List to Nested Set Model......Page 664
Other Models for Trees and Hierarchies......Page 666
Graphs in SQL......Page 667
Adjacency List Model Graphs......Page 668
SQL and the Adjacency List Model......Page 669
Paths with CTE......Page 670
Nonacyclic Graphs......Page 675
Adjacency Matrix Model......Page 677
All Nodes in the Graph......Page 679
Reachable Nodes......Page 680
Indegree and Outdegree......Page 681
Source, Sink, Isolated, and Internal Nodes......Page 682
Converting Acyclic Graphs to Nested Sets......Page 683
Points inside Polygons......Page 684
Graph Theory References......Page 686
Temporal Math......Page 687
Personal Calendars......Page 689
Time Series......Page 690
Gaps in a Time Series......Page 692
Continuous Time Periods......Page 694
Missing Times in Contiguous Events......Page 699
Locating Dates......Page 702
Starting and Ending Dates......Page 704
Julian Dates......Page 705
Other Temporal Functions......Page 708
Weeks......Page 709
Sorting by Weekday Names......Page 711
Modeling Time in Tables......Page 712
Using Duration Pairs......Page 713
Calendar Auxiliary Table......Page 715
Events and Dates......Page 716
The Zeros......Page 717
Leap Year......Page 718
The Millennium......Page 719
Weird Dates in Legacy Data......Page 720
The Aftermath......Page 721
Optimizing SQL......Page 723
Sequential Access......Page 724
Hashed Indexes......Page 725
How to Index......Page 726
Use Simple Search Conditions......Page 727
Simple String Expressions......Page 728
Simple Temporal Expressions......Page 729
Give Extra Information......Page 730
Index Multiple Columns Carefully......Page 731
Watch the IN Predicate......Page 732
Prefer Joins over Nested Queries......Page 734
Use Fewer Statements......Page 735
Avoid Sorting......Page 736
Know Your Optimizer......Page 740
Recompile Static SQL after Schema Changes......Page 742
Temporary Tables Are Sometimes Handy......Page 743
Update Statistics......Page 745
Do Not Trust Newer Features......Page 746
Random Numbers......Page 749
Scales and Measurements......Page 750
Regular Expressions......Page 751
Introductory SQL Books......Page 752
Temporal Data and the Year 2000 Problem......Page 753
Classics......Page 754
Theory, Normalization, and Advanced Database Topics......Page 755
Standards and Related Groups......Page 756
Temporal Databases......Page 757
Miscellaneous Citations......Page 758
B......Page 760
C......Page 761
D......Page 762
F......Page 763
H......Page 764
K......Page 765
N......Page 766
O......Page 767
P......Page 768
S......Page 769
U......Page 771
Z......Page 772

📜 SIMILAR VOLUMES

Joe Celko's SQL for Smarties - Advanced

📁 Joe Celko's SQL for Smarties - Advanced SQL Programming

✍ Joe Celko 📂 Library 📅 2005 🏛 Morgan Kaufmann 🌐 English

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 m

Joe Celko's SQL for Smarties Advanced SQ

📁 Joe Celko's SQL for Smarties Advanced SQL Programming

✍ Joe Celko 📂 Library 📅 2005 🏛 Morgan Kaufmann 🌐 English

Joe Celko's SQL for smarties : advanced

📁 Joe Celko's SQL for smarties : advanced SQL programming

✍ Joe Celko 📂 Library 📅 2005 🏛 Morgan Kaufmann 🌐 English

Joe Celko's SQL for Smarties, Fourth Edi

📁 Joe Celko's SQL for Smarties, Fourth Edition: Advanced SQL Programming

✍ Joe Celko 📂 Library 📅 2011 🏛 Elsevier 🌐 English

Joe Celko's SQL for Smarties: Advanced S

📁 Joe Celko's SQL for Smarties: Advanced SQL Programming Third Edition

✍ Joe Celko 📂 Library 📅 2005 🏛 Morgan Kaufmann 🌐 English

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 futur

Joe Celko's SQL for Smarties, Fifth Edit

📁 Joe Celko's SQL for Smarties, Fifth Edition: Advanced SQL Programming

✍ Joe Celko 📂 Library 📅 2014 🏛 Morgan Kaufmann 🌐 English

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, 20 years later and in its fifth edition, this classic reference still reigns supreme as the only book written by a SQL master t