Joe Celko's SQL for Smarties

Joe Celko's SQL for Smarties

Advanced SQL Programming

3rd Edition - July 29, 2005

Write a review

  • Authors: Joe Celko, Joe Celko
  • eBook ISBN: 9780080460048

Purchase options

Purchase options
DRM-free (EPub, PDF, Mobi)
Sales tax will be calculated at check-out

Institutional Subscription

Free Global Shipping
No minimum order

Description

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 masters. These are not just tips and techniques; Joe also offers the best solutions to old and new challenges and conveys the way you need to think in order to get the most out of SQL programming efforts for both correctness and performance. In the third edition, Joe features new examples and updates to SQL-99, expanded sections of Query techniques, and a new section on schema design, with the same war-story teaching style that made the first and second editions of this book classics.

Key Features

  • Expert advice from a noted SQL authority and award-winning columnist, who has given ten years of service to the ANSI SQL standards committee and many more years of dependable help to readers of online forums.
  • Teaches scores of advanced techniques that can be used with any product, in any SQL environment, whether it is an SQL-92 or SQL-99 environment.
  • Offers tips for working around system deficiencies.
  • Continues to use war stories--updated!--that give insights into real-world SQL programming challenges.

Readership

Working SQL programmers, including those application developers, database analysts, and others who want to pick up some advanced programming tips and techniques, and also those who are developing new features for DBMSs who want to know about users' ideas and needs.

Table of Contents

  • Dedication

    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

    Chapter 1: Database Design

    1.1 Schema and Table Creation

    1.2 Generating Unique Sequential Numbers for Keys

    1.3 A Remark on Duplicate Rows

    1.4 Other Schema Objects

    Chapter 2: Normalization

    2.1 Functional and Multivalued Dependencies

    2.2 First Normal Form (1NF)

    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

    Chapter 3: Numeric Data in SQL

    3.1 Numeric Types

    3.1.1 BIT, BYTE, and BOOLEAN Data Types

    3.2 Numeric Type Conversion

    3.3 Four-Function Arithmetic

    3.4 Arithmetic and NULLs

    3.5 Converting Values to and from NULL

    3.6 Vendor Math Functions

    Chapter 4: Temporal Data Types in SQL

    4.1 Notes on Calendar Standards

    4.2 SQL Temporal Data Types

    4.3 Queries with Date Arithmetic

    4.4 The Nature of Temporal Data Models

    Chapter 5: Character Data Types in SQL

    5.1 Problems with SQL Strings

    5.2 Standard String Functions

    5.3 Common Vendor Extensions

    5.4 Cutter Tables

    Chapter 6: NULLs

    6.1 Empty and Missing Tables

    6.2 Missing Values in Columns

    6.3 Context and Missing Values

    6.5 NULLs and Logic

    6.6 Math and NULLs

    6.7 Functions and NULLs

    6.8 NULLs and Host Languages

    6.9 Design Advice for NULLs

    6.10 A Note on Multiple NULL Values

    Chapter 7: Multiple Column Data Elements

    7.1 Distance Functions

    7.2 Storing an IP Address in SQL

    7.3 Currency and Other Unit Conversions

    7.4 Social Security Numbers

    7.5 Rational Numbers

    Chapter 8: Table Operations

    8.1 Delete From Statement

    8.2 Insert into Statement

    8.3 The Update Statement

    8.4 A Note on Flaws in a Common Vendor Extension

    8.5 Merge Statement

    Chapter 9: Comparison or Theta Operators

    9.1 Converting Data Types

    9.2 Row Comparisons in SQL

    Chapter 10: Valued Predicates

    10.1 IS NULL Predicate

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

    10.3 IS [NOT] NORMALIZED Predicate

    Chapter 11: CASE Expressions

    11.1 The CASE Expression

    11.2 Rozenshtein Characteristic Functions

    Chapter 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

    Chapter 13: Between and Overlaps Predicates

    13.1 The BETWEEN Predicate

    13.2 OVERLAPS Predicate

    Chapter 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

    Chapter 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

    Chapter 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

    Chapter 17: The SELECT Statement

    17.1 SELECT and JOINs

    17.2 OUTER JOINs

    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’sT-Join

    Chapter 18: VIEWs, Derived Tables, Materialized Tables, and Temporary Tables

    18.1 VlEWs in Queries

    18.2 Updatable and Read-Only VIEWs

    18.3 Types of VIEWs

    18.4 How VIEWs Are Handled in the Database System

    18.5 WITH CHECK OPTION Clause

    18.6 Dropping VIEWs

    18.7 TEMPORARY TABLE Declarations

    18.8 Hints on Using VIEWs and TEMPORARY TABLEs

    18.9 Using Derived Tables

    18.10 Derived Tables in the WITH Clause

    Chapter 19: Partitioning Data in Queries

    19.1 Coverings and Partitions

    19.2 Relational Division

    19.3 Romley’s Division

    19.4 Boolean Expressions in an RDBMS

    19.5 FIFO and LIFO Subsets

    Chapter 20: Grouping Operations

    20.1 GROUP BY Clause

    20.2 GROUP BY and HAVING

    20.3 Multiple Aggregation Levels

    20.4 Grouping on Computed Columns

    20.5 Grouping into Pairs

    20.6 Sorting and GROUP BY

    Chapter 21: Aggregate Functions

    21.1 COUNT() Functions

    21.2 SUM() Functions

    21.3 AVG() Functions

    21.4 Extrema Functions

    21.5 The LIST() Aggregate Function

    21.6 The PRD() Aggregate Function

    21.7 Bitwise Aggregate Functions

    Chapter 22: Auxiliary Tables

    22.1 The Sequence Table

    22.2 Lookup Auxiliary Tables

    22.3 Auxiliary Function Tables

    22.4 Global Constants Tables

    Chapter 23: Statistics in SQL

    23.1 The Mode

    23.2 The AVG() Function

    23.3 The Median

    23.4 Variance and Standard Deviation

    23.5 Average Deviation

    23.6 Cumulative Statistics

    23.7 Cross Tabulations

    23.8 Harmonic Mean and Geometric Mean

    23.9 Multivariate Descriptive Statistics in SQL

    Chapter 24: Regions, Runs, Gaps, Sequences, and Series

    24.2 Numbering Regions

    24.3 Finding Regions of Maximum Size

    24.4 Bound Queries

    24.5 Run and Sequence Queries

    24.6 Summation of a Series

    24.7 Swapping and Sliding Values in a List

    24.8 Condensing a List of Numbers

    24.9 Folding a List of Numbers

    24.10 Coverings

    Chapter 25: Arrays in SQL

    25.1 Arrays via Named Columns

    25.2 Arrays via Subscript Columns

    25.3 Matrix Operations in SQL

    25.4 Flattening a Table into an Array

    25.5 Comparing Arrays in Table Format

    Chapter 26: Set Operations

    26.1 UNION and UNION ALL

    26.2 INTERSECT and EXCEPT

    26.3 A Note on ALL and SELECT DISTINCT

    26.4 Equality and Proper Subsets

    Chapter 27: Subsets

    27.1 Every nth Item in a Table

    27.2 Picking Random Rows from a Table

    27.3 The CONTAINS Operators

    27.4 Picking a Representative Subset

    Chapter 28: Trees and Hierarchies in SQL

    28.1 Adjacency List Model

    28.2 The Path Enumeration Model

    28.3 Nested Set Model of Hierarchies

    28.4 Other Models for Trees and Hierarchies

    Chapter 29: Temporal Queries

    29.1 Temporal Math

    29.2 Personal Calendars

    29.3 Time Series

    29.4 Julian Dates

    29.5 Date and Time Extraction Functions

    29.6 Other Temporal Functions

    29.7 Weeks

    29.8 Modeling Time in Tables

    29.9 Calendar Auxiliary Table

    29.10 Problems with the Year 2000

    Chapter 30: Graphs in SQL

    30.1 Basic Graph Characteristics

    30.2 Paths in a Graph

    30.3 Acyclic Graphs as Nested Sets

    30.4 Paths with CTE

    30.5 Adjacency Matrix Model

    30.6 Points inside Polygons

    Chapter 31: OLAP in SQL

    31.1 Star Schema

    31.2 OLAP Functionality

    31.3 A Bit of History

    Chapter 32: Transactions and Concurrency Control

    32.1 Sessions

    32.2 Transactions and ACID

    32.3 Concurrency Control

    32.5 SNAPSHOT Isolation: Optimistic Concurrency

    32.6 Logical Concurrency Control

    32.7 Deadlock and Livelocks

    Chapter 33: Optimizing SQL

    33.1 Access Methods

    33.2 Expressions and Unnested Queries

    33.3 Give Extra join Information in Queries

    33.4 Index Tables Carefully

    33.5 Watch the IN Predicate

    33.6 Avoid UNIONS

    33.7 Prefer Joins over Nested Queries

    33.8 Avoid Expressions on Indexed Columns

    33.9 Avoid Sorting

    33.10 Avoid CROSS JOINs

    33.11 Learn to Use Indexes Carefully

    33.12 Order Indexes Carefully

    33.13 Know Your Optimizer

    33.14 Recompile Static SQL after Schema Changes

    33.15 Temporary Tables Are Sometimes Handy

    33.16 Update Statistics

    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

    Forum

    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

    Index

    About the Author

Product details

  • No. of pages: 840
  • Language: English
  • Copyright: © Morgan Kaufmann 2005
  • Published: July 29, 2005
  • Imprint: Morgan Kaufmann
  • eBook ISBN: 9780080460048

About the Authors

Joe Celko

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

Affiliations and Expertise

Independent Consultant, Austin, Texas

Joe Celko

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

Affiliations and Expertise

Independent Consultant, Austin, Texas

Ratings and Reviews

Write a review

There are currently no reviews for "Joe Celko's SQL for Smarties"