Contents
Preface
Introduction: Haters Gonna Hate, Or Why You Still Need SQL
I  Basic Skills
1 Exercise 0: The Setup
 1.1 Additional Tools You'll Need
 1.2 Extra Credit
2 Exercise 1: Creating Tables
 2.1 What You Should See
 2.2 Extra Credit
 2.3 Portability Notes
3 Exercise 2: Creating A Multi-Table Database
 3.1 What You Should See
 3.2 Extra Credit
 3.3 Portability Notes
4 Exercise 3: Inserting Data
 4.1 What You Should See
 4.2 Extra Credit
 4.3 Portability Notes
5 Exercise 4: Insert Referential Data
 5.1 What You Should See
 5.2 Extra Credit
6 Exercise 5: Selecting Data
 6.1 What You Should See
 6.2 Extra Credit
 6.3 Portability Notes
7 Exercise 6: Select Across Many Tables
 7.1 What You Should See
 7.2 Extra Credit
 7.3 Portability Notes
8 Exercise 7: Deleting Data
 8.1 What You Should See
 8.2 Extra Credit
9 Exercise 8: Deleting Using Other Tables
 9.1 What You Should See
 9.2 Extra Credit
 9.3 Portability Notes
10 Exercise 9: Updating Data
 10.1 What You Should See
 10.2 Extra Credit
11 Exercise 10: Updating Complex Data
 11.1 What You Should See
 11.2 Extra Credit
12 Exercise 11: Replacing Data
 12.1 What You Should See
 12.2 Extra Credit
 12.3 Portability Notes
13 Exercise 12: Destroying And Altering Tables
 13.1 What You Should See
 13.2 Extra Credit
 13.3 Portability Notes
14 Exercise 13: Migrating And Evolving Data
 14.1 The Assignment
15 Exercise 14: Basic Transactions
 15.1 Extra Credit
 15.2 Portability Notes
16 Exercise 15: Data Modeling
 16.1 Aesthetic Guidelines
 16.2 Designing The Data Model
 16.3 Implementing The SQL
 16.4 Implementing With Object-Relational Mappers
 16.5 Final Steps
 16.6 Extra Credit
17 Exercise 16: Making Indices
 17.1 What You Should See
 17.2 Extra Credit
 17.3 Portability Notes
18 Exercise 17: Optimizing A Database
 18.1 What You Should See
 18.2 Extra Credit
 18.3 Portability Notes
19 Exercise 18: Using Triggers
 19.1 What You Should See
 19.2 Extra Credit
 19.3 Portability Notes
20 Exercise 19: Updating One Table From Another
 20.1 What You Should See
 20.2 Extra Credit
 20.3 Portability Notes
21 Exercise 20: Working With Views
 21.1 What You Should See
 21.2 Extra Credit
 21.3 Portability Notes
22 Exercise 21: Simple Data Analysis With Views
 22.1 What You Should See
 22.2 Extra Credit
 22.3 Portability Notes
23 Exercise 22: GROUP BY And Counts
 23.1 What You Should See
 23.2 Extra Credit
 23.3 Portability Notes
24 Exercise 23: Date And Time Functions
 24.1 What You Should See
 24.2 Extra Credit
 24.3 Portability Notes
25 Exercise 24: Range Queries By Dates
 25.1 What You Should See
 25.2 Extra Credit
 25.3 Portability Notes
26 Exercise 25: Getting Averages
 26.1 What You Should See
 26.2 Extra Credit
 26.3 Portability Notes
27 Exercise 26: Maximums And Minimums
 27.1 What You Should See
 27.2 Extra Credit
 27.3 Portability Notes
II  Advanced Topics
28 Exercise 27: Safe Data Practices With Transactions
 28.1 What You Should See
 28.2 Extra Credit
 28.3 Portability Notes
29 Exercise 28: Advanced Transactions And Savepoints
 29.1 What You Should See
 29.2 Extra Credit
 29.3 Portability Notes
30 Exercise 29: Sums And Totals
 30.1 What You Should See
 30.2 Extra Credit
 30.3 Portability Notes
31 Exercise 30: Logic And Math Expressions
 31.1 What You Should See
 31.2 Extra Credit
 31.3 Portability Notes
32 Exercise 31: More Statistics And Math
 32.1 What You Should See
 32.2 Extra Credit
 32.3 Portability Notes
33 Exercise 32: String Manipulation
 33.1 What You Should See
 33.2 Extra Credit
 33.3 Portability Notes
34 Exercise 33: Fixing Databases With Bad Input
 34.1 What You Should See
 34.2 Extra Credit
 34.3 Portability Notes
35 Exercise 34: Math Functions
 35.1 What You Should See
 35.2 Extra Credit
 35.3 Portability Notes
36 Exercise 35: More Data Analysis
 36.1 What You Should See
 36.2 Extra Credit
 36.3 Portability Notes
37 Exercise 36: Complex Decision Expressions
 37.1 What You Should See
 37.2 Extra Credit
 37.3 Portability Notes
38 Exercise 37: Attaching And Detaching Databases
 38.1 What You Should See
 38.2 Extra Credit
 38.3 Portability Notes
39 Exercise 38: Analyzing Tables
 39.1 What You Should See
 39.2 Extra Credit
 39.3 Portability Notes
40 Exercise 39: Explaining Queries
 40.1 What You Should See
 40.2 Extra Credit
 40.3 Portability Notes
41 Exercise 40: Optimizing More With EXPLAIN
 41.1 What You Should See
 41.2 Extra Credit
 41.3 Portability Notes
42 Exercise 41: Forcing Reindexing
 42.1 What You Should See
 42.2 Extra Credit
 42.3 Portability Notes
43 Exercise 42: Vacuuming Junk
 43.1 What You Should See
 43.2 Extra Credit
 43.3 Portability Notes
44 Exercise 43: Setting Options With PRAGMA
 44.1 What You Should See
 44.2 Extra Credit
 44.3 Portability Notes
45 Exercise 44: Handy Pragmas
 45.1 What You Should See
 45.2 Extra Credit
 45.3 Portability Notes
46 Exercise 45: Using Reserved Words In Queries
 46.1 What You Should See
 46.2 Extra Credit
 46.3 Portability Notes
47 Exercise 46: Designing A Real Database
 47.1 What You Should See
 47.2 Extra Credit
 47.3 Portability Notes
48 Exercise 47: Mapping OOP Concepts To SQL
 48.1 What You Should See
 48.2 Extra Credit
 48.3 Portability Notes
49 Exercise 48: Using The Database From Python
 49.1 What You Should See
 49.2 Extra Credit
 49.3 Portability Notes
50 Exercise 49: Using The Database From C
 50.1 What You Should See
 50.2 Extra Credit
 50.3 Portability Notes
51 Exercise 50: Using The Database From R
 51.1 What You Should See
 51.2 Extra Credit
 51.3 Portability Notes
52 Exercise 51: Using The Database From Objective-C
 52.1 What You Should See
 52.2 Extra Credit
 52.3 Portability Notes
53 Exercise 52: Final Exam Extend And Optimize The Database
 53.1 What You Should See
 53.2 Extra Credit
 53.3 Portability Notes
54 Next Steps