SQL Queries for Mere Mortals

Contents

Foreword

Preface and Acknowledgments

About the Authors

Introduction

Are You a Mere Mortal?

About This Book

How to Use This Book

Reading the Diagrams Used in This Book

Sample Databases Used in This Book

"Follow the Yellow Brick Road"

Chapter 1 What Is "Relational?"

Topics Covered in This Chapter

Types of Databases

A Brief History of the Relational Model

In the Beginning . . .

Relational Database Software

Anatomy of a Relational Database

Tables

Fields

Records

Keys

Views

Relationships

What's in It for Me?

"Where Do I Go from Here?"

Summary

Chapter 2 Ensuring Your Database Structure Is Sound

Topics Covered in This Chapter

Why Is This Chapter Here?

Why Worry about Sound Structures?

Fine-tuning Fields

What's in a Name? (Part One)

Smoothing Out the Rough Edges

Resolving Multipart Fields

Resolving Multivalued Fields

Fine-tuning Tables

What's in a Name? (Part Two)

Ensuring a Sound Structure

Resolving Unnecessary Duplicate Fields

Identification Is the Key

Establishing Solid Relationships

Establishing a Deletion Rule

Setting the TYpe of Participation

Setting the Degree of Participation

Is That All?

Summary

Chapter 3 A Concise History of SQL

Topics Covered in This Chapter

The Origins of SQL

Early Vendor Implementation

". . . And Then There Was a Standard"

Evolution of the ANSI/ISO Standard

Other SQL Standards

Commercial Implementations

What the Future Holds

Why Should You Learn SQL?

Summary

Chapter 4 Creating a Simple Query

Topics Covered in This Chapter

Introducing SELECT

The SELECT Statement

Major Clauses in a SELECT Statement

A Quick Aside: Data vs. Information

Translating Your Request into SQL

Expanding the Field of Vision

Eliminating Duplicate Rows

Sorting Information

First Things First: Collating Sequences

Let's Now Come to Order

Saving Your Work

Sample Statements

Summary

Problems for You to Solve

Chapter 5 Getting More Than Simple Columns

Topics Covered in This Chapter

The SELECT Clause: Take Two

Specifying Explicit Values

Moving Beyond Basic Information

What Is an Expression?

What Are You Trying to Express?

Data Types in SQL

Types of Expressions

Concatenation

Mathematical

Date And Time Arithmetic

Using Expressions in a SELECT Clause

A Brief Digression: Value Expressions

That "Nothing" Value--NULL

Introducing Null

Sample Statements

Summary

Problems for You to Solve

Chapter 6 Filtering Your Data

Topics Covered in This Chapter

Refining What You See Using WHERE

The WHERE Clause

Using A WHERE Clause

Defining Search Conditions

Comparison

Range

Set Membership

Pattern Match

Null

Excluding Rows with NOT

Using Multiple Conditions

Introducing AND and OR

Excluding Rows: Take Two

Order of Precedence

Nulls Revisited: A Cautionary Note

Expressing Conditions in Different Ways

Sample Statements

Summary

Problems for You to Solve

CHAPTER 7 Thinking in Sets

Topics Covered in This Chapter

What Is a Set, Anyway?

Operations on Sets

Intersection

Intersection in Set Theory

Intersection between Result Sets

Problems You Can Solve with INTERSECT

Difference

Difference in Set Theory

Difference Between Result Sets

Problems You Can Solve with DIFFERENCE

Union

Union in Set Theory

Combining Result Sets Using UNION

Problems You Can Solve with UNION

SQL Set Operations

"Classical" Set Operations vs. SQL

Finding Common Values--INTERSECT

Finding Missing Values--EXCEPT (Difference)

Combining Sets--UNION

Summary

Chapter 8 INNER JOINs

Topics Covered in This Chapter

What Is a JOIN?

The INNER JOIN

What's "Legal" to JOIN?

Syntax

Check Those Relationships!

Uses for INNER JOINs

Find Related Rows

Find Matching Values

Sample Statements

Two Tables

More Than Two Tables

Looking for Matching Values

Summary

Problems for You to Solve

Chapter 9 OUTER JOINs

Topics Covered in This Chapter

What is an OUTER JOIN?

The Left/Right OUTER JOIN

Syntax

The FULL OUTER JOIN

Syntax

FULL OUTER JOIN on Non-Key Values

UNION JOIN

Uses for OUTER JOINS

Find Missing Values

Find Partially Matched Information

Sample Statements

Summary

Problems for You to Solve

Chapter 10 UNIONs

Topics Covered in This Chapter

What Is a Union?

Writing Requests with UNION

Using Simple SELECT Statements

Combining Complex SELECT Statements

Using UNION More Than Once

Sorting a UNION

Uses for UNION

Sample Statements

Summary

Problems for You to Solve

Chapter 11 Subqueries

Topics Covered in This Chapter

What Is a Subquery?

Row Subqueries

Table Subqueries

Scalar Subqueries

Subqueries as Column Expressions

Syntax

An Introduction to Aggregate Functions--COUNT and MAX

Subqueries as Filters

Syntax

Special Predicate Keywords for Subqueries

Uses for Subqueries

Column Expressions

Filters

Sample Statements

Subqueries in Expressions

Subqueries in Filters

Summary

Problems for You to Solve

Chapter 12 Summarizing Data

Topics Covered in This Chapter

Aggregate functions

Counting Rows and Values with COUNT

Computing a Total with SUM

Calculating a Mean Value with AVG

Finding the Largest Value with MAX

Finding the Smallest Value with MIN

Using More Than One Function

Using Aggregate Functions in Filters

Sample Statements

Summary

Problems for You to Solve

Chapter 13 Grouping Data

Topics Covered in This Chapter

Why Group Data?

The GROUP BY Clause

Syntax

Mixing Columns and Expressions

Using GROUP BY in a Subquery in a WHERE Clause

Simulating a SELECT DISTINCT Statement

"Some Restrictions Apply"

Column Restrictions

Grouping on Expressions

Uses for GROUP BY

Sample Statements

Summary

Problems for You to Solve

Chapter 14 Filtering Grouped Data

Topics Covered in This Chapter

A New Meaning of "Focus Groups"

When You Filter Makes A Difference

Should You Filter in WHERE or in HAVING?

Avoiding the HAVING COUNT Trap

Uses for HAVING

Sample Statements

Summary

Problems for You to Solve

In Closing

APPENDICES

A SQL Standard Diagrams

B Schema for the Sample Databases

C Recommended Reading References

Index