Joe Celko's SQL for Smarties: Advanced SQL Programming

by Joe Celko

Overview

Get the most from your database applications through clever SQL programming
Joe Celko shares his most useful tips and tricks for advanced SQL programming to help the working programmer gain performance and work around system deficiencies. Addressing real problems that people building real applications face, Joe provides new and creative solutions.

A noted consultant, lecturer, and columnist for DBMS and Database Programming & Design magazines, Joe uses entertaining anecdotes and "war stories" to provide real-world insight into SQL programming and warn about potential problems.

Become an expert programmer
This is the first advanced SQL book available that provides a comprehensive presentation of the techniques necessary to help you progress from a casual user of SQL to an expert programmer. Joe uses an informal, conversational writing style to explain why a particular trick works, so the working programmer can apply it directly to his or her own programming needs. Relying primarily on the SQL-89 features that are now supported by commercial RDBMSs, he also includes an extensive discussion of SQL-92, with information on how it's being implemented in current and future database products. The tips and techniques presented here are not product specific, so they allow programmers to maximize any system's performance.

Product Details

ISBN-13:	9780123820235
Publisher:	Elsevier Science
Publication date:	11/22/2010
Series:	The Morgan Kaufmann Series in Data Management Systems
Sold by:	Barnes & Noble
Format:	eBook
Pages:	816
Sales rank:	836,269
File size:	4 MB

About the Author

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.

Table of Contents

Joe Celko's SQL for Smarties: Advanced SQL Programming
by Joe Celko

Preface: README:JOE
1. Introduction
1.1 The Nature of the SQL Language
1.2 Programming Tips

2 Database Design
2.1 Schema and Table Creation
2.1.1 Schemas
2.1.2 Manipulating Tables
DROP TABLE
ALTER TABLE
CREATE TABLE
2.1.3 Column Definitions
The DEFAULTClause
Column Constraints
NOT NULL Constraint
CHECK() Constraint
UNIQUE and PRIMARY KEY Constraints
REFERENCES Clause
2.2 Normalization
2.2.1 Functional and Multivalued Dependencies
2.2.2 First Normal Form (INF)
Note on Repeated Groups
2.2.3 Second Normal Form (2NF)
2.2.4 Third Normal Form (3NF)
Boyce-Codd Normal Form
2.2.5 CASE Tools for Normalization
2.2.6 Boyce-Codd Normal Form (BCNF)
2.2.7 Fourth Normal Form (4NF)
2.2.8 Fifth Normal Form (5NF)
2.2.9 Domain-Key Normal Form (DKNF)
2.2.10 Practical Hints for Normalization
2.2.11 Practical Hints for Denormalization

3 Numeric Data in SQL
3.1 Numeric Types
3.2 Numeric type Conversion
3.2.1 Rounding and Truncating
3.2.2 CAST() Function
3.3 Four-Function Math
3.4 Arithmetic and NULLs
3.5 Converting Values to and from NULL
3.5.1 NULLIF() Function
3.5.2 COALESCE()Function
3.6 Vendor Math Functions

4 Temporal Datatypes in SQL
4.1 Tips for Handling Dates, Timestamps, and Times
4.1.1 Handling Dates
4.1.2 Handling Timestamps
4.1.3 Handling Times
4.2 Queries with Dates
4.2.1 Calendars
4.2.2 Gaps in a time Series
4.2.3 Locating Dates
4.3 Julian Dates
4.4 DATE and TIME Functions

5 Character Data in SQL
5.1 Problems of String Equality
5.2 Problems of String Ordering
5.3 Common Vendor Extensions
5.4 Phonetic Matching
5.4.1 Soundex Functions
5.4.2 The Original Soundex
5.4.3 An Improved Soundex
5.4.4 Metaphone
5.5 Cutter Tables
5.6 Standard String Functions
5.7 Other Pattern-Matching Predicates
5.8 Other String Functions

6 NULLs-Missing Data in SQL
6.1 Empty and Missing Tables
6.2 Missing Values in Columns
6.3 Context and Missing Values
6.4 NULLs and Logic
6.5 Comparing NULLs
6.6 Math and NULLs
6.7 Functions and NULLs
6.8 NULLs and Host Languages
6.9 Design Advice

7 Other Schema Objects
7.1 Schema Creation
7.1.1 Schema Tables
7.2 Temporary Tables
7.3 CREATE ASSERTION
7.4 CREATE DOMAIN
7.5 TRIGGERs
7.6 CREATE PROCEDURE

8 Table Operations
8.1 DELETE FROM Statement
8.1.1 The DELETE FROM Clause
8.1.2 The WHERE Clause
8.1.3 Deleting Based on Data in a Second Table
8.1.4 Deleting Within the Same Table
8.1.5 Deleting in Multiple Tables Without Referential Integrity
8.2 INSERT INTO Statement
8.2.1 INSERT INTO Clause
8.2.2 The Nature of Inserts
8.2.3 Bulk Load and Unload Utilities
8.3 The UPDATE Statement
8.3.1 The UPDATE Clause
8.3.2 The WHERE Clause
8.3.3 The SET Clause
8.3.4 Updating with a Second Table
8.3.5 Updating Within the Same Table
8.3.6 Updating a Primary Key

9 Comparison or Theta Operators
9.1 Converting Datatypes
9.2 Row Comparisons in SQL-92

10 Valued Predicates
10.1 IS NULL Predicate
10.1.1 Sources of NULLS
10.2 IS [NOT] {TRUE}

What People are Saying About This

From the Publisher

Bestselling author and SQL guru Joe Celko ensures programming success through his new and expanded edition of SQL for Smarties which is packed with tips, tricks and other indispensable information for any and all SQL database practitioners and programmers who need to know how to get their job done.

Editorial Reviews

The Barnes & Noble Review
Joe Celko’s SQL for Smarties turns experienced SQL programmers into great SQL programmers. Now he’s updated this classic to reflect today’s state-of-the-art in SQL coding. That means new examples, many drawn from SQL-99 features that were still on the drawing board last time. It also means more advanced query techniques, plus a whole new discussion of schema design. What hasn’t changed, though, is just as important: Celko’s relentlessly informed and practical approach, and the breadth of his coverage.

Do you want to design or normalize databases more effectively? Make better use of data types, tables, CASE expressions, predicates, SELECT, VIEW, partitioning data, grouping operations, aggregate functions? Handle gaps, sequences, series? OLAP, transactions, currency control, optimization? Celko offers advanced techniques for all that, and plenty more. Get this book, and get a stack of Post-it Notes: you’ll use ’em. Bill Camarda, from the November 2005 Read Only

bn.com

A compendium of tips and tricks for advanced SQL programming, with coverage of database and data design, data types, and operations on tables, in an informal, conversational style. The bulk of the book covers predicates and logical operators of SQL, and complex queries. SQL-89 features now supported by commercial RDBMSs are discussed, as well as SQL-92 and how it's being implemented in current and future database products. An appendix offers source code for six check-digit programs. Annotation c. Book News, Inc., Portland, OR (booknews.com)

Booknews

"...a must-read book for any SQL programmer, covering advanced topics in an understandable way...aimed at taking experienced SQL programmers and giving them the extras to make them experts." —I Programmer, 2015

From the Publisher