Joe Celko's SQL for Smarties Advanced SQL Programming

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...

Full description

Saved in:
Bibliographic Details
Main Author: Celko, Joe
Format: eBook
Language:English
Published: Chantilly Elsevier Science & Technology 2005
Morgan Kaufmann
Edition:3
Series:The Morgan Kaufmann Series in Data Management Systems
Subjects:
SQL (Computer program language)
ISBN:9780123693792, 0123693799
Online Access:Get full text
Tags: Add Tag
No Tags, Be the first to tag this record!
Table of Contents:
  • 20.4 Grouping on Computed Columns
  • front cover -- copyright -- table of contents -- front matter -- Introduction to the Third Edition -- 1.1 What Changed in Ten Years -- 1.2 What Is New in This Edition -- 1.3 Corrections and Additions -- body -- 1 Database Design -- 1.1 Schema and Table Creation -- 1.1.1 CREATE SCHEMA Statement -- 1.1.2 Manipulating Tables -- 1.1.3 Column Constraints -- 1.1.4 UNIQUE Constraints versus UNIQUE Indexes -- 1.1.5 Nested UNIQUE Constraints -- 1.1.6 Overlapping Keys -- 1.1.7 CREATE ASSERTION Constraints -- 1.1.8 Using VIEWs for Schema Level Constraints -- 1.1.9 Using PRIMARY KEYs and ASSERTIONs for Constraints -- 1.1.10 Avoiding Attribute Splitting -- 1.1.11 Modeling Class Hierarchies in DDL -- 1.2 Generating Unique Sequential Numbers for Keys -- 1.2.1 IDENTITY Columns -- 1.2.2 ROWID and Physical Disk Addresses -- 1.2.3 Sequential Numbering in Pure SQL -- 1.2.4 GUIDs -- 1.2.5 Sequence Generator Functions -- 1.2.6 Unique Value Generators -- 1.2.7 Preallocated Values -- 1.2.8 Random Order Values -- 1.3 A Remark on Duplicate Rows -- 1.4 Other Schema Objects -- 1.4.1 Schema Tables -- 1.4.2 Temporary Tables -- 1.4.3 CREATE DOMAIN Statement -- 1.4.4 CREATE TRIGGER Statement -- 1.4.5 CREATE PROCEDURE Statement -- 1.4.6 DECLARE CURSOR Statement -- 2 Normalization -- 2.1 Functional and Multivalued Dependencies -- 2.2 First Normal Form (1NF) -- 2.2.1 Note on Repeated Groups -- 2.3 Second Normal Form (2NF) -- 2.4 Third Normal Form (3NF) -- 2.5 Elementary Key Normal Form (EKNF) -- 2.6 Boyce-Codd Normal Form (BCNF) -- 2.7 Fourth Normal Form (4NF) -- 2.8 Fifth Normal Form (5NF) -- 2.9 Domain-Key Normal Form (DKNF) -- 2.10 Practical Hints for Normalization -- 2.11 Key Types -- 2.11.1 Natural Keys -- 2.11.2 Artificial Keys -- 2.11.3 Exposed Physical Locators -- 2.11.4 Practical Hints for Denormalization -- 2.11.5 Row Sorting -- 3 Numeric Data in SQL -- 3.1 Numeric Types
  • 18 VIEWs, Derived Tables, Materialized Tables, and Temporary Tables -- 18.1 VIEWs in Queries -- 18.2 Updatable and Read-Only VIEWs -- 18.3 Types of VIEWs -- 18.3.1 Single-Table Projection and Restriction -- 18.3.2 Calculated Columns -- 18.3.3 Translated Columns -- 18.3.4 Grouped VIEWs -- 18.3.5 UNIONed VIEWs -- 18.3.6 JOINs in VIEWs -- 18.3.7 Nested VIEWs -- 18.4 How VIEWs Are Handled in the Database System -- 18.4.1 View Column List -- 18.4.2 VIEW Materialization -- 18.4.3 In-Line Text Expansion -- 18.4.4 Pointer Structures -- 18.4.5 Indexing and Views -- 18.5 WITH CHECK OPTION Clause -- 18.5.1 WITH CHECK OPTION as CHECK() Clause -- 18.6 Dropping VIEWs -- 18.7 TEMPORARY TABLE Declarations -- 18.8 Hints on Using VIEWs and TEMPORARY TABLEs -- 18.8.1 Using VIEWs -- 18.8.2 Using TEMPORARY TABLEs -- 18.8.3 Flattening a Table with a VIEW -- 18.9 Using Derived Tables -- 18.9.1 Derived Tables in the FROM clause -- 18.9.2 Derived Tables with a VALUES Constructor -- 18.10 Derived Tables in the WITH Clause -- 19 Partitioning Data in Queries -- 19.1 Coverings and Partitions -- 19.1.1 Partitioning by Ranges -- 19.1.2 Partition by Functions -- 19.1.3 Partition by Sequences -- 19.2 Relational Division -- 19.2.1 Division with a Remainder -- 19.2.2 Exact Division -- 19.2.3 Note on Performance -- 19.2.4 Todd's Division -- 19.2.5 Division with JOINs -- 19.2.6 Division with Set Operators -- 19.3 Romley's Division -- 19.4 Boolean Expressions in an RDBMS -- 19.5 FIFO and LIFO Subsets -- 20 Grouping Operations -- 20.1 GROUP BY Clause -- 20.1.1 NULLs and Groups -- 20.2 GROUP BY and HAVING -- 20.2.1 Group Characteristics and the HAVING Clause -- 20.3 Multiple Aggregation Levels -- 20.3.1 Grouped VIEWs for Multiple Aggregation Levels -- 20.3.2 Subquery Expressions for Multiple Aggregation Levels -- 20.3.3 CASE Expressions for Multiple Aggregation Levels
  • 3.1.1 BIT, BYTE, and BOOLEAN Data Types -- 3.2 Numeric Type Conversion -- 3.2.1 Rounding and Truncating -- 3.2.2 CAST() Function -- 3.3 Four-Function Arithmetic -- 3.4 Arithmetic and NULLs -- 3.5 Converting Values to and from NULL -- 3.5.1 NULLIF() Function -- 3.5.2 COALESCE() Function -- 3.6 Vendor Math Functions -- 3.6.1 Number Theory Operators -- 3.6.2 Exponential Functions -- 3.6.3 Scaling Functions -- 3.6.4 Converting Numbers to Words -- 4 Temporal Data Types in SQL -- 4.1 Notes on Calendar Standards -- 4.2 SQL Temporal Data Types -- 4.2.1 Tips for Handling Dates, Timestamps, and Times -- 4.2.2 Date Format Standards -- 4.2.3 Handling Timestamps -- 4.2.4 Handling Times -- 4.3 Queries with Date Arithmetic -- 4.4 The Nature of Temporal Data Models -- 4.4.1 Temporal Duplicates -- 4.4.2 Temporal Databases -- 4.4.3 Temporal Projection and Selection -- 4.4.4 Temporal Joins -- 4.4.5 Modifying Valid-Time State Tables -- 4.4.6 Current Modifications -- 4.4.7 Sequenced Modifications -- 4.4.8 Nonsequenced Modifications -- 4.4.9 Transaction-Time State Tables -- 4.4.10 Maintaining the Audit Log -- 4.4.11 Querying the Audit Log -- 4.4.12 Modifying the Audit Log -- 4.4.13 Bitemporal Tables -- 4.4.14 Temporal Support in Standard SQL -- 5 Character Data Types in SQL -- 5.1 Problems with SQL Strings -- 5.1.1 Problems of String Equality -- 5.1.2 Problems of String Ordering -- 5.1.3 Problems of String Grouping -- 5.2 Standard String Functions -- 5.3 Common Vendor Extensions -- 5.3.1 Phonetic Matching -- 5.4 Cutter Tables -- 6 NULLs: Missing Data in SQL -- 6.1 Empty and Missing Tables -- 6.2 Missing Values in Columns -- 6.3 Context and Missing Values -- 6.4 Comparing NULLs -- 6.5 NULLs and Logic -- 6.5.1 NULLS in Subquery Predicates -- 6.5.2 Standard SQL Solutions -- 6.6 Math and NULLs -- 6.7 Functions and NULLs -- 6.8 NULLs and Host Languages
  • 6.9 Design Advice for NULLs -- 6.9.1 Avoiding NULLs from the Host Programs -- 6.10 A Note on Multiple NULL Values -- 7 Multiple Column Data Elements -- 7.1 Distance Functions -- 7.2 Storing an IP Address in SQL -- 7.2.1 A Single VARCHAR(15) Column -- 7.2.2 One INTEGER Column -- 7.2.3 Four SMALLINT Columns -- 7.3 Currency and Other Unit Conversions -- 7.4 Social Security Numbers -- 7.5 Rational Numbers -- 8 Table Operations -- 8.1 DELETE FROM Statement -- 8.1.1 The DELETE FROM Clause -- 8.1.2 The WHERE Clause -- 8.1.3 Deleting Based on Data in a Second Table -- 8.1.4 Deleting within the Same Table -- 8.1.5 Deleting in Multiple Tables without Referential Integrity -- 8.2 INSERT INTO Statement -- 8.2.1 INSERT INTO Clause -- 8.2.2 The Nature of Inserts -- 8.2.3 Bulk Load and Unload Utilities -- 8.3 The UPDATE Statement -- 8.3.1 The UPDATE Clause -- 8.3.2 The WHERE Clause -- 8.3.3 The SET Clause -- 8.3.4 Updating with a Second Table -- 8.3.5 Using the CASE Expression in UPDATEs -- 8.4 A Note on Flaws in a Common Vendor Extension -- 8.5 MERGE Statement -- 9 Comparison or Theta Operators -- 9.1 Converting Data Types -- 9.2 Row Comparisons in SQL -- 10 Valued Predicates -- 10.1 IS NULL Predicate -- 10.1.1 Sources of NULLs -- 10.2 IS [NOT]{TRUE | FALSE | UNKNOWN} Predicate -- 10.3 IS [NOT] NORMALIZED Predicate -- 11 CASE Expressions -- 11.1 The CASE Expression -- 11.1.1 The COALESCE() and NULLIF() Functions -- 11.1.2 CASE Expressions with GROUP BY -- 11.1.3 CASE, CHECK() Clauses and Logical Implication -- 11.1.4 Subquery Expressions and Constants -- 11.2 Rozenshtein Characteristic Functions -- 12 LIKE Predicate -- 12.1 Tricks with Patterns -- 12.2 Results with NULL Values and Empty Strings -- 12.3 LIKE Is Not Equality -- 12.4 Avoiding the LIKE Predicate with a Join -- 12.5 CASE Expressions and LIKE Predicates -- 12.6 SIMILAR TO Predicates
  • 12.7 Tricks with Strings -- 12.7.1 String Character Content -- 12.7.2 Searching versus Declaring a String -- 12.7.3 Creating an Index on a String -- 13 BETWEEN and OVERLAPS Predicates -- 13.1 The BETWEEN Predicate -- 13.1.1 Results with NULL Values -- 13.1.2 Results with Empty Sets -- 13.1.3 Programming Tips -- 13.2 OVERLAPS Predicate -- 13.2.1 Time Periods and OVERLAPS Predicate -- 14 The [NOT] IN() Predicate -- 14.1 Optimizing the IN() Predicate -- 14.2 Replacing ORs with the IN() Predicate -- 14.3 NULLs and the IN() Predicate -- 14.4 IN() Predicate and Referential Constraints -- 14.5 IN() Predicate and Scalar Queries -- 15 EXISTS() Predicate -- 15.1 EXISTS and NULLs -- 15.2 EXISTS and INNER JOINs -- 15.3 NOT EXISTS and OUTER JOINs -- 15.4 EXISTS() and Quantifiers -- 15.5 EXISTS() and Referential Constraints -- 15.6 EXISTS and Three-Valued Logic -- 16 Quantified Subquery Predicates -- 16.1 Scalar Subquery Comparisons -- 16.2 Quantifiers and Missing Data -- 16.3 The ALL Predicate and Extrema Functions -- 16.4 The UNIQUE Predicate -- 17 The SELECT Statement -- 17.1 SELECT and JOINs -- 17.1.1 One-Level SELECT Statement -- 17.1.2 Correlated Subqueries in a SELECT Statement -- 17.1.3 SELECT Statement Syntax -- 17.1.4 The ORDER BY Clause -- 17.2 OUTER JOINs -- 17.2.1 Syntax for OUTER JOINs -- 17.2.2 NULLs and OUTER JOINs -- 17.2.3 NATURAL versus Searched OUTER JOINs -- 17.2.4 Self OUTER JOINs -- 17.2.5 Two or More OUTER JOINs -- 17.2.6 OUTER JOINs and Aggregate Functions -- 17.2.7 FULL OUTER JOIN -- 17.2.8 WHERE Clause OUTER JOIN Operators -- 17.3 Old versus New JOIN Syntax -- 17.4 Scope of Derived Table Names -- 17.5 JOINs by Function Calls -- 17.6 The UNION JOIN -- 17.7 Packing Joins -- 17.8 Dr. Codd's T-Join -- 17.8.1 The Croatian Solution -- 17.8.2 The Swedish Solution -- 17.8.3 The Colombian Solution