Learning SQL

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

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

3. 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
Exercise 3-2
Exercise 3-3
Exercise 3-4

4. Filtering
Condition Evaluation
Using Parentheses
Using the not Operator
Building a Condition
Condition Types
Equality Conditions
Range Conditions
Membership Conditions
Matching Conditions
Null: That Four-Letter Word
Test Your Knowledge
Exercise 4-1
Exercise 4-2
Exercise 4-3
Exercise 4-4

5. 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
Exercise 5-2
Exercise 5-3

6. 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
Exercise 6-2
Exercise 6-3

7. 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
Exercise 7-2
Exercise 7-3

8. 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
Exercise 8-2
Exercise 8-3

9. 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
Exercise 9-2
Exercise 9-3

10. Joins Revisited
Outer Joins
Left Versus Right Outer Joins
Three-Way Outer Joins
Cross Joins
Natural Joins
Test Your Knowledge
Exercise 10-1
Exercise 10-2
Exercise 10-3 (Extra Credit)

11. Conditional Logic Using Case
What Is Conditional Logic?
The Case Expression
Searched Case Expressions
Simple Case Expressions
Case Expression Examples
Result Set Transformations
Checking for Existence
Division-by-Zero Errors
Conditional Updates
Handling Null Values
Test Your Knowledge
Exercise 11-1
Exercise 11-2

12. Transactions
Multiuser Databases
Locking
Lock Granularities
What Is a Transaction?
Starting a Transaction
Ending a Transaction
Transaction Savepoints
Test Your Knowledge
Exercise 12-1

13. 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
Exercise 13-2

14. 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
Exercise 14-2

15. Metadata
Data About Data
Information_Schema
Working with Metadata
Schema Generation Scripts
Deployment Verification
Dynamic SQL Generation
Test Your Knowledge
Exercise 15-1
Exercise 15-2

16. 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
Exercise 16-2
Exercise 16-3

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

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

A. ER Diagram for Example Database
B. Solutions to Exercises