Joe Celko's SQL for Smarties

Author： Celko, Joe

Publishers： Elsevier Science

Publishing year： 2010

ISBN： 9780123820235

Subject： [TP 自动化技术、计算机技术]

Language： ENG

Introduction

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.

  • 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

Chapter

  • Front Cover

  • Series page

  • Joe Celko’s SQL for Smarties

  • Copyright

  • Dedication

  • Table of 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

  • References

  • 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

    • 服务邮箱： whx@cnpiec.com.cn

    联系电话：010-65063046；65001305

    京ICP备09090397号-16 京公网安备 11010502049547号