Contents

Contents

Acknowledgments

About the Author

Introduction

Are You a Mere Mortal?

About This Book

What This Book Is Not

How to Use This Book

Reading the Diagrams Used in This Book

Sample Databases Used in This Book

“Follow the Yellow Brick Road”

PART I: RELATIONAL DATABASES AND SQL

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 Systems

Anatomy of a Relational Database

What’s in It for You?

Where Do You Go from Here?

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 Columns

What’s in a Name? (Part One)

Smoothing Out the Rough Edges

Resolving Multipart Columns

Resolving Multivalued Columns

Fine-Tuning Tables

What’s in a Name? (Part Two)

Ensuring a Sound Structure

Resolving Unnecessary Duplicate Columns

Identification Is the Key

Establishing Solid Relationships

Establishing a Deletion Rule

Setting the Type of Participation

Setting the Degree of Participation

Chapter 3: A Concise History of SQL

Topics Covered in This Chapter

The Origins of SQL

Early Vendor Implementations

“… 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?

Which Version of SQL Does this Book Cover?

PART II: SQL BASICS

Chapter 4: Creating a Simple Query

Topics Covered in This Chapter

Introducing SELECT

The SELECT Statement

A Quick Aside: Data versus Information

Translating Your Request into SQL

Expanding the Field of Vision

Using a Shortcut to Request All Columns

Eliminating Duplicate Rows

Sorting Information

First Things First: Collating Sequences

Let’s Now Come to Order

Saving Your Work

Sample Statements

Problems for You to Solve

Chapter 5: Getting More Than Simple Columns

Topics Covered in This Chapter

What Is an Expression?

What Type of Data Are You Trying to Express?

Changing Data Types: The CAST Function

Specifying Explicit Values

Character String Literals

Numeric Literals

Datetime Literals

Types of Expressions

Mathematical Expressions

Date and Time Arithmetic

Using Expressions in a SELECT Clause

Working with a Concatenation Expression

Naming the Expression

Working with a Mathematical Expression

Working with a Date Expression

A Brief Digression: Value Expressions

That “Nothing” Value: Null

Introducing Null

The Problem with Nulls

Sample Statements

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

Excluding Rows with NOT

Using Multiple Conditions

Introducing AND and OR

Excluding Rows: Take Two

Order of Precedence

Checking for Overlapping Ranges

Nulls Revisited: A Cautionary Note

Expressing Conditions in Different Ways

Sample Statements

Problems for You to Solve

PART III: WORKING WITH MULTIPLE TABLES

Chapter 7: Thinking in Sets

Topics Covered in This Chapter

What Is a Set, Anyway?

Operations on Sets

Intersection in Set Theory

Intersection between Result Sets

Problems You Can Solve with an Intersection

Difference in Set Theory

Difference between Result Sets

Problems You Can Solve with Difference

Union in Set Theory

Combining Result Sets Using a Union

Problems You Can Solve with Union

SQL Set Operations

Classic Set Operations versus SQL

Finding Common Values: INTERSECT

Finding Missing Values: EXCEPT (DIFFERENCE)

Combining Sets: UNION

Chapter 8: INNER JOINs

Topics Covered in This Chapter

What Is a JOIN?

What’s “Legal” to JOIN?

Column References

Check Those Relationships!

Uses for INNER JOINs

Find Related Rows

Find Matching Values

Sample Statements

More Than Two Tables

Looking for Matching Values

Problems for You to Solve

Chapter 9: OUTER JOINs

Topics Covered in This Chapter

What Is an OUTER JOIN?

The LEFT/RIGHT OUTER JOIN

The FULL OUTER JOIN

FULL OUTER JOIN on Non-Key Values

Uses for OUTER JOINs

Find Missing Values

Find Partially Matched Information

Sample Statements

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

Sample Statements

Problems for You to Solve

Chapter 11: Subqueries

Topics Covered in This Chapter

What Is a Subquery?

Table Subqueries

Scalar Subqueries

Subqueries as Column Expressions

An Introduction to Aggregate Functions: COUNT and MAX

Subqueries as Filters

Special Predicate Keywords for Subqueries

Uses for Subqueries

Build Subqueries as Column Expressions

Use Subqueries as Filters

Sample Statements

Subqueries in Expressions

Subqueries in Filters

Problems for You to Solve

PART IV: SUMMARIZING AND GROUPING DATA

Chapter 12: Simple Totals

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

Problems for You to Solve

Chapter 13: Grouping Data

Topics Covered in This Chapter

Why Group Data?

The GROUP BY Clause

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

Problems for You to Solve

Chapter 14: Filtering Grouped Data

Topics Covered in This Chapter

A New Meaning for “Focus Groups”

Where You Filter Makes a Difference

Should You Filter in WHERE or in HAVING?

Avoiding the HAVING COUNT Trap

Uses for HAVING

Sample Statements

Problems for You to Solve

PART V: MODIFYING SETS OF DATA

Chapter 15: Updating Sets of Data

Topics Covered in This Chapter

What Is an UPDATE?

The UPDATE Statement

Using a Simple UPDATE Expression

A Brief Aside: Transactions

Updating Multiple Columns

Using a Subquery to Filter Rows

Some Database Systems Allow a JOIN in the UPDATE Clause

Using a Subquery UPDATE Expression

Uses for UPDATE

Sample Statements

Problems for You to Solve

Chapter 16: Inserting Sets of Data

Topics Covered in This Chapter

What Is an INSERT?

The INSERT Statement

Inserting Values

Generating the Next Primary Key Value

Inserting Data by Using SELECT

Uses for INSERT

Sample Statements

Problems for You to Solve

Chapter 17: Deleting Sets of Data

Topics Covered in This Chapter

What Is a DELETE?

The DELETE Statement

Deleting All Rows

Deleting Some Rows

Uses for DELETE

Sample Statements

Problems for You to Solve

PART VI: INTRODUCTION TO SOLVING TOUGH PROBLEMS

Chapter 18: “NOT” and “AND” Problems

Topics Covered in This Chapter

A Short Review of Sets

Sets with Multiple AND Criteria

Sets with Multiple NOT Criteria

Sets Including Some Criteria but Excluding Others

Finding Out the “Not” Case

Using OUTER JOIN

Using NOT EXISTS

Using GROUP BY/HAVING

Finding Multiple Matches in the Same Table

Using INNER JOIN

Using GROUP BY/HAVING

Sample Statements

Problems for You to Solve

Chapter 19: Condition Testing

Topics Covered in This Chapter

Conditional Expressions (CASE)

Solving Problems with CASE

Solving Problems with Simple CASE

Solving Problems with Searched CASE

Using CASE in a WHERE Clause

Sample Statements

Problems for You to Solve

Chapter 20: Using Unlinked Data and “Driver” Tables

Topics Covered in This Chapter

What Is Unlinked Data?

Deciding When to Use a CROSS JOIN

Solving Problems with Unlinked Data

Solving Problems Using “Driver” Tables

Setting Up a Driver Table

Using a Driver Table

Sample Statements

Examples Using Unlinked Tables

Examples Using Driver Tables

Problems for You to Solve

Chapter 21: Performing Complex Calculations on Groups

Topics in this Chapter

Grouping in Sub-Groups

Extending the GROUP BY Clause

Getting Totals in a Hierarchy Using Rollup

Calculating Totals on Combinations Using CUBE

Creating a Union of Totals with GROUPING SETS

Variations on Grouping Techniques

Sample Statements

Examples using ROLLUP

Examples using CUBE

Examples using GROUPING SETS

Problems for You to Solve

Chapter 22: Partitioning Data into Windows

Topics in this Chapter

What You Can Do With a “Window” into Your Data

Calculating a Row Number

Splitting Data into Quintiles

Using Windows with Aggregate Functions

Sample Statements

Examples Using ROW_NUMBER

Examples Using RANK, DENSE_RANK, and PERCENT_RANK

Examples Using NTILE

Examples Using Aggregate Functions

Problems for You to Solve

PART VII: APPENDICES

Appendix A: SQL Standard Diagrams

Appendix B: Schema for the Sample Databases

Sales Orders Example Database

Sales Orders Modify Database

Entertainment Agency Example Database

Entertainment Agency Modify Database

School Scheduling Example Database

School Scheduling Modify Database

Bowling League Example Database

Bowling League Modify Database

Recipes Database

“Driver” Tables

Appendix C: Date and Time Types, Operations, and Functions

Microsoft Access

Microsoft SQL Server

Appendix D: Suggested Reading