Joe Celko's SQL for Smarties

Advanced SQL Programming

Joe Celko

• Publisher: Morgan Kaufmann
• Year: 2014
• eBook ISBN: 9780128008300
• Edition: 5
• Topic: Computer Science
• Subtopic: Databases
• Index: Computer Science

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, 20 years later and in its fifth edition, this classic reference still reigns supreme as the only book written by a SQL master that teaches programmers and practitioners to become SQL masters themselves! These are not just tips and techniques; also offered are the best solutions to old and new challenges. Joe Celko conveys the way you need to think in order to get the most out of SQL programming efforts for both correctness and performance.New to the fifth edition, Joe features new examples to reflect the ANSI/ISO Standards so anyone can use it. He also updates data element names to meet new ISO-11179 rules with the same experience-based teaching style that made the previous editions the classics they are today. You will learn new ways to write common queries, such as finding coverings, partitions, runs in data, auctions and inventory, relational divisions and so forth.SQL for Smarties explains some of the principles of SQL programming as well as the code. A new chapter discusses design flaws in DDL, such as attribute splitting, non-normal forum redundancies and tibbling. There is a look at the traditional acid versus base transaction models, now popular in NoSQL products. You'll learn about computed columns and the DEFERRABLE options in constraints. An overview of the bi-temporal model is new to this edition and there is a longer discussion about descriptive statistic aggregate functions. The book finishes with an overview of SQL/PSM that is applicable to proprietary 4GL vendor extensions. - New to the 5th Edition: - Overview of the bitemporal model - Extended coverage of descriptive statistic aggregate functions - New chapter covers flaws in DDL - Examination of traditional acid versus base transaction models - Reorganized to help you navigate related topics with ease - Expert advice from a noted SQL authority and award-winning columnist Joe Celko, who served on the ANSI SQL standards committee for over a decade - Teaches scores of advanced techniques that can be used with any product, in any SQL environment, whether it is SQL 92 or SQL 2011 - Offers tips for working around deficiencies and gives insight into real-world challenges

Part 1

Data Declaration Features

Chapter 1

Databases Versus File Systems

Abstract

A comparison of relational databases versus traditional file systems is discussed. Rows are not records; columns are not fields, and tables are not files.

Keywords

Database

File system

US standard railroad gauge

COBOL

FORTRAN

C

BASIC

PL/I

Java

Procedural programming language

OO programming language

E-R diagrams

Peter Chen

Data Declaration Language (DDL)

Data Control Language (DCL)

It ain’t so much the things we don’t know that get us in trouble. It’s the things we know that ain’t so.

—Artemus Ward (William Graham Sumner), American Writer and Humorist, 1834-1867

Perfecting oneself is as much unlearning as it is learning

—Edsgar Dijkstra

If you already have a background in data processing with traditional file systems, the first things to unlearn are

(0) Databases are not file sets.

(1) Tables are not files.

(2) Rows are not records.

(3) Columns are not fields.

(4) Values in RDBMS are scalar, not structured (arrays, lists, meta-data).

Do not feel ashamed of getting stuck in a conceptual rut; every new technology has this problem.

The US standard railroad gauge (distance between the rails) is 4 ft, 8.5 in. This gauge is used because the English built railroads to that gauge and US railroads were built by English expatriates.

Why did the English build railroads to that gauge? Because the first rail lines were built by the same people who built the pre-railroad tramways, and that’s the gauge they used. Why did those wheelwrights use that gauge then? Because the people who built the horse-drawn trams used the same jigs and tools that they used for building wagons, which used that wheel spacing.

Why did the wagons use that odd wheel spacing? For the practical reason that any other spacing would break an axle on some of the old, long distance roads, because this is the measure of the old wheel ruts.

So who built these old rutted roads? The first long distance roads in Europe were built by Imperial Rome for their legions and used ever since. The initial ruts were first made by Roman war chariots, which were of uniform military issue. The Imperial Roman chariots were made to be just wide enough to accommodate the back-ends of two war horses (this example is originally due to Professor Tom O’Hare, Germanic Languages, University of Texas at Austin; email: [email protected]).

This story does not end there, however. Look at a NASA Space Shuttle and the two big booster rockets attached to the sides of the main fuel tank. These are solid rocket boosters or SRBs. The SRBs are made by Thiokol at their factory at Utah. The engineers who designed the SRBs might have preferred to make them a bit fatter, but the SRBs had to be shipped by train from the factory to the launch site in Florida. The railroad line from the factory runs through a tunnel in the mountains and the SRBs have to fit through that tunnel. The tunnel is slightly wider than the railroad track. So, the major design feature of what is arguably the world’s most advanced transportation system was determined by the width of a horse’s ass.

In a similar fashion, modern data processing began with punch cards (Hollerith cards if you are really old) used by the Bureau of the Census. Their original size was that of a US dollar bill. This was set by their inventor, Herman Hollerith, because he could get furniture to store the cards from the US Treasury Department, just across the street. Likewise, physical constraints limited each card to 80 columns of holes in which to record a symbol.

The influence of the punch card lingered on long after the invention of magnetic tapes and disk for data storage. This is why early video display terminals were 80 columns across. Even today, files which were migrated from cards to magnetic tape files or disk storage still use 80 column physical records.

But the influence was not just on the physical side of data processing. The methods for handling data from the prior media were imitated in the new media.

Data processing first consisted of sorting and merging decks of punch cards (later, sequential magnetic tape files) in a series of distinct steps. The result of each step feed into the next step in the process. Think of the assembly line in a factory.

Databases and RDBMS in particular are nothing like the file systems that came with COBOL, FORTRAN, C, BASIC, PL/I, Java, or any of the procedural and OO programming languages. We used to say that SQL means “Scarcely Qualifies as a Language” because it has no I/O of its own. SQL depends on a host language to get and receive data to and from end users.

1.1 The Schema Statement

Programming languages are usually based on some underlying model; if you understand the model, the language makes much more sense. For example, FORTRAN is based on algebra. This does not mean that FORTRAN is exactly like algebra. But if you know algebra, FORTRAN does not look all that strange to you the way that LISP or APL would. You can write an expression in an assignment statement or make a good guess as to the names of library functions you have never seen before.

Likewise, COBOL is based on English narratives of business processes. The design of COBOL files (and almost every other early programming language) was derived from paper forms. The most primitive form of a file is a sequence of records that are ordered within the file and referenced by physical position.

You open a file (think file folder or in-basket on your desk) and then read a first record (think of the first paper form on the stack), followed by a series of next records (process the stack of paperwork, one paper form at a time) until you come to the last record to raise the end-of-file condition (put the file folder in the out-basket). Notice the work flow:

1. The records (paper forms) have to physically exist to be processed. Files are not virtual by nature. In fact, this mindset is best expressed by a quote from Samuel Goldwyn “a verbal contract ain’t worth the paper it is written on!”

2. You navigate among these records and perform actions, one record at a time. You ca...

Table of contents

• Cover image
• Title page
• Table of Contents
• Copyright
• Dedication
• Introduction to the Fifth Edition
• Part 1: Data Declaration Features
• Part 2: Data Types
• Part 3: Row and Column Level Features
• Part 4: Data Structures in SQL
• Part 5: Typical Queries
• Part 6: Implementation and Coding Issues
• Index