4. Retrieving Data from a Table
Retrieving Columns with SELECT and FROM
Creating Column Aliases with AS
Eliminating Duplicate Rows with DISTINCT
Combining and Negating Conditions with AND, OR, and NOT
Testing for Nulls with IS NULL
Performing Arithmetic Operations
Determining the Order of Evaluation
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
Converting Data Types with CAST()
Evaluating Conditional Values with CASE
Checking for Nulls with COALESCE()
Comparing Expressions with NULLIF()
6. Summarizing and Grouping Data
Creating Aggregate Expressions
Calculating an Average with AVG()
Aggregating Distinct Values with DISTINCT
Creating Table Aliases with AS
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
Simple and Correlated Subqueries
Qualifying Column Names 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
Finding Common Rows with INTERSECT
Finding Different Rows with EXCEPT
10. Inserting, Updating, and Deleting Rows
11. Creating, Altering, and Dropping Tables
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
Creating an Index with CREATE INDEX
Dropping an Index with DROP INDEX
Creating a View with CREATE VIEW
Retrieving Data Through a View
Dropping a View with DROP VIEW
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