Joe Celko's SQL for smarties: advanced SQL programming

Front Cover; Joe Celko's sql for Smarties: Advanced sql Programming; Copyright; Dedication; Contents; Introduction to the Fifth Edition; What is New in this Edition; Corrections and Additions; Part 1: Data Declaration Features; Chapter 1: Databases Versus File Systems; 1.1. The Schema Statement; 1.2. Tables as Entities; 1.3. Tables as Relationships; 1.3.1. E-R Diagrams; 1.4. Rows Versus Records; 1.5. Columns Versus Fields; Chapter 2: Transactions and Concurrency Control; 2.1. Sessions; 2.2. Transactions and ACID ; 2.3. Concurrency Control; 2.3.1. The Transaction Phenomena.

2.4. The Isolation Levels2.5. Pessimistic Concurrency Control; 2.6. Snapshot Isolation Optimistic Concurrency; 2.7. Logical Concurrency Control; 2.8. Cap Theorem; 2.9. Base; 2.10. Server-Side Consistency; 2.11. Error Handling; 2.12. Deadlock and Livelocks; Chapter 3: Tables; 3.1. Create Table Statements; 3.1.1. Base Tables; 3.1.2. [Global Local] Temporary Tables; 3.2. Column Definitions; 3.2.1. DEFAULT Clause; 3.2.2. NOT NULL Constraint; 3.2.3. CHECK () Constraint; 3.2.4. UNIQUE and PRIMARY KEY Constraints; 3.2.5. REFERENCES Clause; 3.2.6. Referential Actions.

3.2.6.1. Nested UNIQUE Constraints3.2.6.2. Overlapping Keys; 3.3. Computed Columns; 3.4. [NOT] DEFERRABLE Constraints; 3.5. CREATE DOMAIN and CREATE SEQUENCE ; 3.5.1. CREATE DOMAIN ; 3.5.2. CREATE SEQUENCE ; 3.5.2.1. Using the SEQUENCE ; 3.6. Character Set Related Constructs; 3.6.1. CREATE CHARACTER SET ; 3.6.2. CREATE COLLATION ; 3.6.3. CREATE TRANSLATION ; Chapter 4: Keys, Locators, and Generated Values; 4.1. Key Types; 4.1.1. Natural Keys; 4.1.2. Artificial Keys; 4.1.3. Exposed Physical Locators; 4.2. Practical Hints for Denormalization; 4.2.1. Row Sorting; Chapter 5: Normalization.

5.1. Functional and Multivalued Dependencies5.2. First Normal Form (1NF); 5.2.1. Note on Repeating Groups; 5.2.1.1. Repeating Columns; 5.2.1.2. Parsing a List in a String; 5.3. Second Normal Form (2NF); 5.4. Third Normal Form (3NF); 5.5. Elementary Key Normal Form (EKNF); 5.6. Boyce-Codd Normal Form (BCNF); 5.7. Fourth Normal Form (4NF); 5.8. Fifth Normal Form (5NF); 5.9. Domain-Key Normal Form (DKNF); 5.10. Practical Hints for Normalization; 5.11. Non-Normal Form Redundancy; 5.11.1. Aggregation Level Redundancy; 5.11.2. Entire Table Redundancy; 5.11.3. Access Path Redundancy.

5.11.4. Attribute SplittingChapter 6: VIEWs, Derived, and Other Virtual Tables; 6.1. VIEWs in Queries; 6.2. Updatable and Read-Only VIEWs; 6.3. Types of VIEWs; 6.3.1. Single-Table Projection and Restriction; 6.3.2. Calculated Columns; 6.3.3. Translated Columns; 6.3.4. Grouped VIEWs; 6.3.5. UNION-ed VIEWs; 6.3.6. JOINs in VIEWs; 6.3.7. Nested VIEWs; 6.4. How VIEWs are Handled in the Database Engine; 6.4.1. View Column List; 6.4.2. VIEW Materialization; 6.4.3. In-Line Text Expansion; 6.4.4. Pointer Structures; 6.4.5. Indexing and Views; 6.5. WITH CHECK OPTION Clause; 6.5.1. WITH CHECK OPTION as CHECK() clause.

Introduction

Chapter 1: Databases versus File Systems

Chapter 2: Transactions and Concurrency Control

Chapter 3: Schema Level Objects

Chapter 4: Locating Data and Special Numbers

Chapter 5: Base Tables and Related Elements

Chapter 6: Procedural, Semi-Procedural and Declarative Programming

Chapter 7: Procedural Constructs

Chapter 8: Auxiliary Tables

Chapter 9: Normalization

Chapter 10: Numeric Data Types

Chapter 11: Temporal Data Types

Chapter 12: Character Data Types

Chapter 13: NULLs

Missing Data in SQL

Chapter 14: Multiple Column Data Elements

Chapter 15: Table Operations

Chapter 16: Comparison or Theta Operators

Chapter 17: Valued Predicates

Chapter 18: CASE Expressions

Chapter 19: LIKE and SIMILAR TO Predicates

Chapter 20: BETWEEN and OVERLAPS Predicates

Chapter 21: The [NOT] IN() Predicate

Chapter 22: EXISTS() Predicate

Chapter 23: Quantified Subquery Predicates

Chapter 24: The Simple SELECT Statement

Chapter 25: Advanced SELECT Statements

Chapter 26: Virtual Tables: VIEWs, Derived Tables, CTEs and MQTs

Chapter 27: Partitioning Data in Queries

Chapter 28: Grouping Operations

Chapter 29: Simple Aggregate Functions

Chapter 30: Advanced Grouping, Windowed Aggregation and OLAP in SQL

Chapter 31: Descriptive Statistics in SQL

Chapter 32: Sub-Sequences, Regions, Runs, Gaps, and Islands

Chapter 33: Matrices in SQL

Chapter 34: Set Operations

Chapter 35: Subsets

Chapter 36: Trees and Hierarchies in SQL

Chapter 37: Graphs in SQL

Chapter 38: Temporal Queries

Chapter 39: Optimizing SQL.

Database design

Normalization

Numeric data in SQL

Temporal data types in SQL

Character data types in SQL

NULLs : missing data in SQL

Multiple column data elements

Table operations

Comparison or theta operators

Valued predicates

CASE expressions

LIKE predicate

Between and overlaps predicates

The [NOT] IN() predicate

EXISTS() predicate

Quantified subquery predicates

The SELECT statement

VIEWs, derived tables, materialized tables, and temporary tables

Partitioning data in queries

Grouping operations

Aggregate functions

Auxiliary tables

Statistics in SQL

Regions, runs, gaps, sequences, and series

Arrays in SQL

Set operations

Subsets

Trees and hierarchies in SQL

Temporal queries

Graphs in SQL

OLAP in SQL

Transactions and concurrency control

Optimizing SQL.