| |
| |
Introduction | |
| |
| |
About SQL | |
| |
| |
About This Book | |
| |
| |
What You'll Need | |
| |
| |
| |
DBMS Specifics | |
| |
| |
Running SQL Programs | |
| |
| |
Microsoft Access | |
| |
| |
Microsoft SQL Server | |
| |
| |
Oracle | |
| |
| |
IBM DB2 | |
| |
| |
MySQL | |
| |
| |
PostgreSQL | |
| |
| |
| |
The Relational Model | |
| |
| |
Tables, Columns, and Rows | |
| |
| |
Primary Keys | |
| |
| |
Foreign Keys | |
| |
| |
Relationships | |
| |
| |
Normalization | |
| |
| |
The Sample Database | |
| |
| |
Creating the Sample Database | |
| |
| |
| |
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 | |
| |
| |
| |
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 | |
| |
| |
| |
Operators and Functions | |
| |
| |
Creating Derived Columns | |
| |
| |
Performing Arithmetic Operations | |
| |
| |
Determining the Order of Evaluation | |
| |
| |
Concatenating Strings with [double vertical line] | |
| |
| |
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() | |
| |
| |
| |
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 | |
| |
| |
| |
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 | |
| |
| |
| |
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 | |
| |
| |
| |
Set Operations | |
| |
| |
Combining Rows with UNION | |
| |
| |
Finding Common Rows with INTERSECT | |
| |
| |
Finding Different Rows with EXCEPT | |
| |
| |
| |
Inserting, Updating, and Deleting Rows | |
| |
| |
Displaying Table Definitions | |
| |
| |
Inserting Rows with INSERT | |
| |
| |
Updating Rows with UPDATE | |
| |
| |
Deleting Rows with DELETE | |
| |
| |
| |
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 | |
| |
| |
| |
Indexes | |
| |
| |
Creating an Index with CREATE INDEX | |
| |
| |
Dropping an Index with DROP INDEX | |
| |
| |
| |
Views | |
| |
| |
Creating a View with CREATE VIEW | |
| |
| |
Retrieving Data Through a View | |
| |
| |
Updating Data Through a View | |
| |
| |
Dropping a View with DROP VIEW | |
| |
| |
| |
Transactions | |
| |
| |
Executing a Transaction | |
| |
| |
| |
SQL Tricks | |
| |
| |
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 | |
| |
| |
Index | |