| |
| |
| 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 | |