-
A Little Background
- Introduction to Databases
- Nonrelational Database Systems
- The Relational Model
- Some Terminology
- What Is SQL?
- SQL Statement Classes
- SQL: A Nonprocedural Language
- SQL Examples
- What Is MySQL?
- SQL Unplugged
- What’s in Store
-
Creating and Populating a Database
- Creating a MySQL Database
- Using the mysql Command-Line Tool
- MySQL Data Types
- Character Data
- Numeric Data
- Temporal Data
- Table Creation
- Step 1: Design
- Step 2: Refinement
- Step 3: Building SQL Schema Statements
- Populating and Modifying Tables
- Inserting Data
- Updating Data
- Deleting Data
- When Good Statements Go Bad
- Nonunique Primary Key
- Nonexistent Foreign Key
- Column Value Violations
- Invalid Date Conversions
- The Sakila Database
-
Query Primer
- Query Mechanics
- Query Clauses
- The select Clause
- Column Aliases
- Removing Duplicates
- The from Clause
- Tables
- Table Links
- Defining Table Aliases
- The where Clause
- The group by and having Clauses
- The order by Clause
- Ascending Versus Descending Sort Order
- Sorting via Numeric Placeholders
- Test Your Knowledge
- Exercise 3-1, 3-2, 3-3, 3-4
-
Filtering
- Condition Evaluation
- Using Parentheses
- Using the not Operator
- Building a Condition
- Condition Types (Equality, Range, Membership, Matching, Null)
- Test Your Knowledge
- Exercise 4-1, 4-2, 4-3, 4-4
-
Querying Multiple Tables
- What Is a Join?
- Cartesian Product
- Inner Joins
- The ANSI Join Syntax
- Joining Three or More Tables
- Using Subqueries as Tables
- Using the Same Table Twice (Self-Joins)
- Test Your Knowledge
- Exercise 5-1, 5-2, 5-3
-
Working with Sets
- Set Theory Primer
- Set Theory in Practice
- Set Operators
- The union Operator
- The intersect Operator
- The except Operator
- Set Operation Rules
- Sorting Compound Query Results
- Set Operation Precedence
- Test Your Knowledge
- Exercise 6-1, 6-2, 6-3
-
Data Generation, Manipulation, and Conversion
- Working with String Data
- String Generation
- String Manipulation
- Working with Numeric Data
- Performing Arithmetic Functions
- Controlling Number Precision
- Handling Signed Data
- Working with Temporal Data
- Dealing with Time Zones
- Generating Temporal Data
- Manipulating Temporal Data
- Conversion Functions
- Test Your Knowledge
- Exercise 7-1, 7-2, 7-3
- Working with String Data
-
Grouping and Aggregates
- Grouping Concepts
- Aggregate Functions
- Implicit Versus Explicit Groups
- Counting Distinct Values
- Using Expressions
- How Nulls Are Handled
- Generating Groups
- Single-Column Grouping
- Multicolumn Grouping
- Grouping via Expressions
- Generating Rollups
- Group Filter Conditions
- Test Your Knowledge
- Exercise 8-1, 8-2, 8-3
-
Subqueries
- What Is a Subquery?
- Subquery Types
- Noncorrelated Subqueries
- Multiple-Row, Single-Column Subqueries
- Multicolumn Subqueries
- Correlated Subqueries
- The exists Operator
- Data Manipulation Using Correlated Subqueries
- When to Use Subqueries
- Subqueries as Data Sources
- Subqueries as Expression Generators
- Subquery Wrap-Up
- Test Your Knowledge
- Exercise 9-1, 9-2, 9-3
-
Joins Revisited
- Outer Joins
- Left Versus Right Outer Joins
- Three-Way Outer Joins
- Cross Joins
- Natural Joins
- Test Your Knowledge
- Exercise 10-1, 10-2, 10-3 (Extra Credit)
- Outer Joins
-
Conditional Logic
- What Is Conditional Logic?
- The case Expression
- Searched case Expressions
- Simple case Expressions
- Examples of case Expressions
- Result Set Transformations
- Checking for Existence
- Division-by-Zero Errors
- Conditional Updates
- Handling Null Values
- Test Your Knowledge
- Exercise 11-1, 11-2
-
Transactions
- Multiuser Databases
- Locking
- Lock Granularities
- What Is a Transaction?
- Starting a Transaction
- Ending a Transaction
- Transaction Savepoints
- Test Your Knowledge
- Exercise 12-1
-
Indexes and Constraints
- Indexes
- Index Creation
- Types of Indexes
- How Indexes Are Used
- The Downside of Indexes
- Constraints
- Constraint Creation
- Test Your Knowledge
- Exercise 13-1, 13-2
- Indexes
-
Views
- What Are Views?
- Why Use Views?
- Data Security
- Data Aggregation
- Hiding Complexity
- Joining Partitioned Data
- Updatable Views
- Updating Simple Views
- Updating Complex Views
- Test Your Knowledge
- Exercise 14-1, 14-2
-
Metadata
- Data About Data
- information_schema
- Working with Metadata
- Schema Generation Scripts
- Deployment Verification
- Dynamic SQL Generation
- Test Your Knowledge
- Exercise 15-1, 15-2
-
Analytic Functions
- Analytic Function Concepts
- Data Windows
- Localized Sorting
- Ranking
- Ranking Functions
- Generating Multiple Rankings
- Reporting Functions
- Window Frames
- Lag and Lead
- Column Value Concatenation
- Test Your Knowledge
- Exercise 16-1, 16-2, 16-3
-
Working with Large Databases
- Partitioning
- Partitioning Concepts
- Table Partitioning
- Index Partitioning
- Partitioning Methods
- Partitioning Benefits
- Clustering
- Sharding
- Big Data
- Hadoop
- NoSQL and Document Databases
- Cloud Computing
- Conclusion
- Partitioning
-
SQL and Big Data
- Introduction to Apache Drill
- Querying Files Using Drill
- Querying MySQL Using Drill
- Querying MongoDB Using Drill
- Drill with Multiple Data Sources
- Future of SQL
Appendices: A. ER Diagram for Example Database
B. Solutions to Exercises
- Chapter 3 (Exercises 3-1, 3-2, 3-3, 3-4)
- Chapter 4 (Exercises 4-1, 4-2, 4-3, 4-4)
- Chapter 5 (Exercises 5-1, 5-2, 5-3)
- Chapter 6 (Exercises 6-1, 6-2, 6-3)
- Chapter 7 (Exercises 7-1, 7-2, 7-3)
- Chapter 8 (Exercises 8-1, 8-2, 8-3)
- Chapter 9 (Exercises 9-1, 9-2, 9-3)
- Chapter 10 (Exercises 10-1, 10-2, 10-3 Extra Credit)
- Chapter 11 (Exercises 11-1, 11-2)
- Chapter 12 (Exercise 12-1)
- Chapter 13 (Exercises 13-1, 13-2)
- Chapter 14 (Exercises 14-1, 14-2)
- Chapter 15 (Exercises 15-1, 15-2)
- Chapter 16 (Exercises 16-1, 16-2, 16-3)
Index
About the Author
