Joe Celko

Joe Celko's SQL for Smarties

Advanced SQL Programming

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.

Audience

This book is intended for working SQL programmers, database administrators, database designers, database analysts, and application system developers as well as those who are developing new features for database management systems who want to know about user needs. This would include anyone working with electronic content in the relational database context but also XML. Web services, etc.

Paperback, 816 Pages

Published: October 2010

Imprint: Morgan Kaufmann

ISBN: 978-0-12-382022-8

Reviews

  • "If you work with SQL in any way, shape, or form, the most recent edition of Joe Celko’s SQL for Smarties needs to be on your bookshelf!"--Data Technology Today Blog


Contents


  • About the Author

    Introduction to the Fourth Edition

    Chapter 1 Databases versus File Systems

        1.1 Tables as Entities

        1.2 Tables as Relationships

        1.3 Rows versus Records

        1.4 Columns versus Fields

        1.5 Schema Objects

        1.6 CREATE SCHEMA Statement

    Chapter 2 Transactions and Concurrency Control

        2.1 Sessions

        2.2 Transactions and ACID

        2.3 Concurrency Control

        2.4 Pessimistic Concurrency Control

        2.5 SNAPSHOT Isolation and Optimistic Concurrency

        2.6 Logical Concurrency Control

        2.7 Deadlock and Livelocks

    Chapter 3 Schema Level Objects

        3.1 CREATE SCHEMA Statement

        3.2 CREATE PROCEDURE, CREATE FUNCTION, and CREATE TRIGGER

        3.3 CREATE DOMAIN Statement

        3.4 CREATE SEQUENCE

        3.5 CREATE ASSERTION

        3.6 Character Set Related Constructs

    Chapter 4 Locating Data and Special Numbers

        4.1 Exposed Physical Locators

        4.2 Generated Identifiers

        4.3 Sequence Generator Functions

        4.4 Preallocated Values

        4.5 Special Series

    Chapter 5 Base Tables and Related Elements

        5.1 CREATE TABLE Statement

        5.2 Nested UNIQUE Constraints

        5.3 CREATE ASSERTION Constraints

        5.4 TEMPORARY Tables

        5.5 Manipulating Tables

        5.6 Avoiding Attribute Splitting

        5.7 Modeling Class Hierarchies in DDL

        5.8 Exposed Physical Locators

        5.9 Auto-Incrementing Columns

        5.10 Generated Identifiers

        5.11 A Remark on Duplicate Rows

        5.12 Other Schema Objects

        5.13 Temporary Tables

        5.14 CREATE DOMAIN Statement

        5.15 CREATE TRIGGER Statement

        5.16 CREATE PROCEDURE Statement

        5.17 DECLARE CURSOR Statement

    Chapter 6 Procedural, Semiprocedural, and Declarative Programming

        6.1 Basics of Software Engineering

        6.2 Cohesion

        6.3 Coupling

        6.4 The Big Leap

        6.5 Rewriting Tricks

        6.6 Functions for Predicates

        6.7 Procedural versus Logical Decomposition

    Chapter 7 Procedural Constructs

        7.1 CREATE PROCEDURE

        7.2 CREATE TRIGGER

        7.3 CURSORs

        7.4 SEQUENCEs

        7.5 Generated Columns

        7.6 Table Functions

    Chapter 8 Auxiliary Tables

        8.1 The Series Table

        8.2 Lookup Auxiliary Tables

        8.3 Auxiliary Function Tables

        8.4 Global Constants Tables

        8.5 A Note on Converting Procedural Code to Tables

    Chapter 9 Normalization

        9.1 Functional and Multivalued Dependencies

        9.2 First Normal Form (1NF)

        9.3 Second Normal Form (2NF)

        9.4 Third Normal Form (3NF)

        9.5 Elementary Key Normal Form (EKNF)

        9.6 Boyce-Codd Normal Form (BCNF)

        9.7 Fourth Normal Form (4NF)

        9.8 Fifth Normal Form (5NF)

        9.9 Domain-Key Normal Form (DKNF)

        9.10 Practical Hints for Normalization

        9.11 Key Types

        9.12 Practical Hints for Denormalization

    Chapter 10 Numeric Data Types

        10.1 Numeric Types

        10.2 Numeric Type Conversion

        10.3 Four Function Arithmetic

        10.4 Arithmetic and NULLs

        10.5 Converting Values to and from NULL

        10.6 Mathematical Functions

        10.7 Unique Value Generators

        10.8 IP Addresses

    Chapter 11 Temporal Data Types

        11.1 Notes on Calendar Standards

        11.2 SQL Temporal Data Types

        11.3 INTERVAL Data Types

        11.4 Temporal Arithmetic

        11.5 The Nature of Temporal Data Models

    Chapter 12 Character Data Types

        12.1 Problems with SQL Strings

        12.2 Standard String Functions

        12.3 Common Vendor Extensions

        12.4 Cutter Tables

        12.5 Nested Replacement

    Chapter 13 NULLs: Missing Data in SQL

        13.1 Empty and Missing Tables

        13.2 Missing Values in Columns

        13.3 Context and Missing Values

        13.4 Comparing NULLs

        13.5 NULLs and Logic

        13.6 Math and NULLs

        13.7 Functions and NULLs

        13.8 NULLs and Host Languages

        13.9 Design Advice for NULLs

        13.10 A Note on Multiple NULL Values

    Chapter 14 Multiple Column Data Elements

        14.1 Distance Functions

        14.2 Storing an IPv4 Address in SQL

        14.3 Storing an IPv6 Address in SQL

        14.4 Currency and Other Unit Conversions

        14.5 Social Security Numbers

        14.6 Rational Numbers

    Chapter 15 Table Operations

        15.1 DELETE FROM Statement

        15.2 INSERT INTO Statement

        15.3 The UPDATE Statement

        15.4 A Note on Flaws in a Common Vendor Extension

        15.5 MERGE Statement

    Chapter 16 Comparison or Theta Operators

        16.1 Converting Data Types

        16.2 Row Comparisons in SQL

        16.3 IS [NOT] DISTINCT FROM Operator

    Chapter 17 Valued Predicates

        17.1 IS NULL

        17.2 IS [NOT]{TRUE | FALSE | UNKNOWN} Predicate

        17.3 IS [NOT] NORMALIZED Predicate

    Chapter 18 CASE Expressions

        18.1 The CASE Expression

        18.2 Subquery Expressions and Constants

        18.3 Rozenshtein Characteristic Functions

    Chapter 19 LIKE and SIMILAR TO Predicates

        19.1 Tricks with Patterns

        19.2 Results with NULL Values and Empty Strings

        19.3 LIKE Is Not Equality

        19.4 Avoiding the LIKE Predicate with a Join

        19.5 CASE Expressions and LIKE Search Conditions

        19.6 SIMILAR TO Predicates

        19.7 Tricks with Strings

    Chapter 20 BETWEEN and OVERLAPS Predicates

        20.1 The BETWEEN Predicate

        20.2 OVERLAPS Predicate

    Chapter 21 The [NOT] IN() Predicate

        21.1 Optimizing the IN() Predicate

        21.2 Replacing ORs with the IN() Predicate

        21.3 NULLs and the IN() Predicate

        21.4 IN() Predicate and Referential Constraints

        21.5 IN() Predicate and Scalar Queries

    Chapter 22 EXISTS() Predicate

        22.1 EXISTS and NULLs

        22.2 EXISTS and INNER JOINs

        22.3 NOT EXISTS and OUTER JOINs

        22.4 EXISTS() and Quantifiers

        22.5 EXISTS() and Referential Constraints

        22.6 EXISTS and Three-Valued Logic

    Chapter 23 Quantified Subquery Predicates

        23.1 Scalar Subquery Comparisons

        23.2 Quantifiers and Missing Data

        23.3 The ALL Predicate and Extrema Functions

        23.4 The UNIQUE Predicate

        23.5 The DISTINCT Predicate

    Chapter 24 The Simple SELECT Statement

        24.1 SELECT Statement Execution Order

        24.2 One-Level SELECT Statement

    Chapter 25 Advanced SELECT Statements

        25.1 Correlated Subqueries

        25.2 Infixed INNER JOINs

        25.3 OUTER JOINs

        25.4 UNION JOIN Operators

        25.5 Scalar SELECT Expressions

        25.6 Old versus New JOIN Syntax

        25.7 Constrained JOINs

        25.8 Dr. Codd’s T-Join

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

        26.1 VIEWs in Queries

        26.2 Updatable and Read-Only VIEWs

        26.3 Types of VIEWs

        26.4 How VIEWs Are Handled in the Database Engine

        26.5 WITH CHECK OPTION Clause

        26.6 Dropping VIEWs

        26.7 Hints on Using VIEWs versus TEMPORARY TABLEs

        26.8 Using Derived Tables

        26.9 Common Table Expressions

        26.10 Recursive Common Table Expressions

        26.11 Materialized Query Tables

    Chapter 27 Partitioning Data in Queries

        27.1 Coverings and Partitions

        27.2 Relational Division

        27.3 Romley’s Division

        27.4 Boolean Expressions in an RDBMS

        27.5 FIFO and LIFO Subsets

    Chapter 28 Grouping Operations

        28.1 GROUP BY Clause

        28.2 GROUP BY and HAVING

        28.3 Multiple Aggregation Levels

        28.4 Grouping on Computed Columns

        28.5 Grouping into Pairs

        28.6 Sorting and GROUP BY

    Chapter 29 Simple Aggregate Functions

        29.1 COUNT() Functions

        29.2 SUM() Function

        29.3 AVG() Function

        29.4 Extrema Functions

        29.5 The LIST() Aggregate Function

        29.6 The PRD() Aggregate Function

        29.7 Bitwise Aggregate Functions

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

        30.1 Star Schema

        30.2 GROUPING Operators

        30.3 The Window Clause

        30.4 Windowed Aggregate Functions

        30.5 Ordinal Functions

        30.6 Vendor Extensions

        30.7 A Bit of History

    Chapter 31 Descriptive Statistics in SQL

        31.1 The Mode

        31.2 The AVG() Function

        31.3 The Median

        31.4 Variance and Standard Deviation

        31.5 Average Deviation

        31.6 Cumulative Statistics

        31.7 Cross Tabulations

        31.8 Harmonic Mean and Geometric Mean

        31.9 Multivariable Descriptive Statistics in SQL

        31.10 Statistical Functions in SQL:2006

    Chapter 32 Subsequences, Regions, Runs, Gaps, and Islands

        32.1 Finding Subregions of Size (n)

        32.2 Numbering Regions

        32.3 Finding Regions of Maximum Size

        32.4 Bound Queries

        32.5 Run and Sequence Queries

        32.6 Summation of a Series

        32.7 Swapping and Sliding Values in a List

        32.8 Condensing a List of Numbers

        32.9 Folding a List of Numbers

        32.10 Coverings

    Chapter 33 Matrices in SQL

        33.1 Arrays via Named Columns

        33.2 Arrays via Subscript Columns

        33.3 Matrix Operations in SQL

        33.4 Flattening a Table into an Array

        33.5 Comparing Arrays in Table Format

    Chapter 34 Set Operations

        34.1 UNION and UNION ALL

        34.2 INTERSECT and EXCEPT

        34.3 A Note on ALL and SELECT DISTINCT

        34.4 Equality and Proper Subsets

    Chapter 35 Subsets

        35.1 Every N-th Item in a Table

        35.2 Random Rows from a Table

        35.3 The CONTAINS Operators

        35.4 Gaps in a Series

        35.5 Covering for Overlapping Intervals

        35.6 Picking a Representative Subset

    Chapter 36 Trees and Hierarchies in SQL

        36.1 Adjacency List Model

        36.2 The Path Enumeration Model

        36.3 Nested Set Model of Hierarchies

        36.4 Other Models for Trees and Hierarchies

    Chapter 37 Graphs in SQL

        37.1 Adjacency List Model Graphs

        37.2 Split Node Nested Set Models for Graphs

        37.3 Points inside Polygons

        37.4 Graph Theory References

    Chapter 38 Temporal Queries

        38.1 Temporal Math

        38.2 Personal Calendars

        38.3 Time Series

        38.4 Julian Dates

        38.5 Other Temporal Functions

        38.6 Weeks

        38.7 Modeling Time in Tables

        38.8 Calendar Auxiliary Table

        38.9 Problems with the Year 2000

    Chapter 39 Optimizing SQL

        39.1 Access Methods

        39.2 How to Index

        39.3 Give Extra Information

        39.4 Index Multiple Columns Carefully

        39.5 Watch the IN Predicate

        39.6 Avoid UNIONs

        39.7 Prefer Joins over Nested Queries

        39.8 Use Fewer Statements

        39.9 Avoid Sorting

        39.10 Avoid CROSS JOINs

        39.11 Know Your Optimizer

        39.12 Recompile Static SQL after Schema Changes

        39.13 Temporary Tables Are Sometimes Handy

        39.14 Update Statistics

        39.15 Do Not Trust Newer Features

    References

        General References

        Logic

        Mathematical Techniques

        Random Numbers

        Scales and Measurements

        Missing Values

        Regular Expressions

        Graph Theory

        Introductory SQL Books

        Optimizing Queries

        Temporal Data and the Year 2000 Problem

        SQL Programming Techniques

        Classics

        Updatable Views

        Theory, Normalization, and Advanced Database Topics

        Books on SQL-92 and SQL-99

        Standards and Related Groups

        Web Sites Related to SQL

        Statistics

        Temporal Databases

        Miscellaneous Citations

        Index








Advertisement

advert image