Intro
Table of Contents
About the Author
About the Technical Reviewer
Acknowledgments
Introduction
Chapter 1: Getting Ready
About the Sample Database
Setting Up
Database Management Software
Database Client
The Sample Database
What You Probably Know Already
Some Philosophical Concepts
Data vs. Information vs. Values
Database Tables
Writing SQL
Basic SQL
Data Types
SQL Clauses
Filtering Data with the WHERE Clause
Multiple Assertions
Wildcard Matches
Sorting with the ORDER BY Clause
Limiting Results
Sorting Strings

Calculating Columns
Calculating with NULLs
Aliases
Subqueries
The CASE Expression
Casting a Value
Views
Joins
The ON Clause
Join Types
Aggregates
Working with Tables
Data Types
Constraints
Foreign Keys
Indexes
Manipulating Data
Set Operations
Coming Up
Chapter 2: Working with Table Design
Understanding Normalized Tables
Columns Should Be Independent
Adding the Towns Table
Adding a Foreign Key to the Town
Update the Customers Table
Remove the Old Address Columns
Create a customerdetails View
Drop the Address Columns

Changing the Town
Adding the Country
Additional Comments
Improving Database Integrity
Fixing Issues with a Nullable Column
Replacing NULL Quantities
Setting the NOT NULL Constraint for Quantity
Setting a DEFAULT for Quantity
Adding a Positive CHECK Constraint for Quantity
Combining the Changes
Making the Changes in SQLite
Other Adjustments
Ensuring the Prices Are Not Negative
Ensuring That an Author Is Born Before Dying
Adding Indexes
Adding an Index to the Books and Authors Tables
Creating a Unique Index
Review
Normal Form
Multiple Values

Altering Tables
Views
Indexes
The Final Product
Summary
Coming Up
Chapter 3: Table Relationships and Joins
An Overview of Relationships
One-to-Many Relationship
Counting One-to-Many Joins
The NOT IN Quirk
Creating a Books and Authors View
One-to-One Relationships
One-to-Maybe Relationships
Multiple Values
Many-to-Many Relationships
Joining Many-to-Many Tables
Summarizing Multiple Values
Combining the Joins
Many-to-Many Relationships Happen All the Time
Another Many-to-Many Example
Inserting into Related Tables

Adding a Book and an Author
Adding an Author
Adding a Book
Adding a New Sale
Adding a New Sale in the Sales Table
Adding the Sale Items and Getting the Prices
Completing the Sale
Review
Types of Relationships
Joining Tables
Views
Inserting into Related Tables
Summary
Coming Up
Chapter 4: Working with Calculated Data
Calculation Basics
Using Aliases
Alias Names
AS Is Optional
Aliases Are Not Available in the Rest of the Query
Dealing with NULLs
Coalesce
Fixing the Author Names
Using Calculations in Other Clauses