Contents

Introduction

About SQL

About This Book

What You’ll Need

1. Running SQL Programs

DBMSs and SQL Tools

Microsoft Access

Microsoft SQL Server

Oracle Database

IBM Db2 Database

MySQL

PostgreSQL

2. The Relational Model

Tables, Columns, and Rows

Primary Keys

Foreign Keys

Relationships

Normalization

The Sample Database

Creating the Sample Database

3. SQL Basics

SQL Syntax

SQL Standards and Conformance

Identifiers

Data Types

Character String Types

Binary Large Object Type

Exact Numeric Types

Approximate Numeric Types

Boolean Type

Datetime Types

Interval Types

Unique Identifiers

Other Data Types

Nulls

4. Retrieving Data from a Table

Retrieving Columns with SELECT and FROM

Creating Column Aliases with AS

Eliminating Duplicate Rows with DISTINCT

Sorting Rows with ORDER BY

Filtering Rows with WHERE

Combining and Negating Conditions with AND, OR, and NOT

Matching Patterns with LIKE

Range Filtering with BETWEEN

List Filtering with IN

Testing for Nulls with IS NULL

5. Operators and Functions

Creating Derived Columns

Performing Arithmetic Operations

Determining the Order of Evaluation

Concatenating Strings with ||

Extracting a Substring with SUBSTRING()

Changing String Case with UPPER() and LOWER()

Trimming Characters with TRIM()

Finding the Length of a String with CHARACTER_LENGTH()

Finding Substrings with POSITION()

Performing Datetime and Interval Arithmetic

Getting the Current Date and Time

Getting User Information

Converting Data Types with CAST()

Evaluating Conditional Values with CASE

Checking for Nulls with COALESCE()

Comparing Expressions with NULLIF()

6. Summarizing and Grouping Data

Using Aggregate Functions

Creating Aggregate Expressions

Finding a Minimum with MIN()

Finding a Maximum with MAX()

Calculating a Sum with SUM()

Calculating an Average with AVG()

Counting Rows with COUNT()

Aggregating Distinct Values with DISTINCT

Grouping Rows with GROUP BY

Filtering Groups with HAVING

7. Joins

Qualifying Column Names

Creating Table Aliases with AS

Using Joins

Creating Joins with JOIN or WHERE

Creating a Cross Join with CROSS JOIN

Creating a Natural Join with NATURAL JOIN

Creating an Inner Join with INNER JOIN

Creating Outer Joins with OUTER JOIN

Creating a Self-Join

8. Subqueries

Understanding Subqueries

Subquery Syntax

Subqueries vs. Joins

Simple and Correlated Subqueries

Qualifying Column Names in Subqueries

Nulls in Subqueries

Using Subqueries as Column Expressions

Comparing a Subquery Value by Using a Comparison Operator

Testing Set Membership with IN

Comparing All Subquery Values with ALL

Comparing Some Subquery Values with ANY

Testing Existence with EXISTS

Comparing Equivalent Queries

9. Set Operations

Combining Rows with UNION

Finding Common Rows with INTERSECT

Finding Different Rows with EXCEPT

10. Inserting, Updating, and Deleting Rows

Displaying Table Definitions

Inserting Rows with INSERT

Updating Rows with UPDATE

Deleting Rows with DELETE

11. Creating, Altering, and Dropping Tables

Creating Tables

Understanding Constraints

Creating a New Table with CREATE TABLE

Forbidding Nulls with NOT NULL

Specifying a Default Value with DEFAULT

Specifying a Primary Key with PRIMARY KEY

Specifying a Foreign Key with FOREIGN KEY

Forcing Unique Values with UNIQUE

Adding a Check Constraint with CHECK

Creating a Temporary Table with CREATE TEMPORARY TABLE

Creating a New Table from an Existing One with CREATE TABLE AS

Altering a Table with ALTER TABLE

Dropping a Table with DROP TABLE

12. Indexes

Creating an Index with CREATE INDEX

Dropping an Index with DROP INDEX

13. Views

Creating a View with CREATE VIEW

Retrieving Data Through a View

Updating Data Through a View

Dropping a View with DROP VIEW

14. Transactions

Executing a Transaction

15. Advanced SQL (bonus chapter—print and ebook only)

Calculating Running Statistics

Generating Sequences

Finding Sequences, Runs, and Regions

Limiting the Number of Rows Returned

Assigning Ranks

Calculating a Trimmed Mean

Picking Random Rows

Handling Duplicates

Creating a Telephone List

Retrieving Metadata

Working with Dates

Calculating a Median

Finding Extreme Values

Changing Running Statistics Midstream

Pivoting Results

Working with Hierarchies

Download and Create the Sample Database

Microsoft Access

Microsoft SQL Server

Oracle Database

IBM Db2 Database

MySQL

PostgreSQL

Other DBMSs