Joe Celko's SQL for Smarties
Advanced SQL Programming
Free Global Shipping
No minimum orderDescription
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.
Key Features
- 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 environment
- Offers tips for working around deficiencies and gives insight into real-world challenges
Readership
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.
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
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
Product details
- No. of pages: 816
- Language: English
- Copyright: © Morgan Kaufmann 2010
- Published: October 18, 2010
- Imprint: Morgan Kaufmann
- eBook ISBN: 9780123820235
About the Authors
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 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