SQL and databases: a reading path from first query to solid data modeling
This curriculum takes a complete beginner from writing their first SQL query all the way to understanding how databases work under the hood at scale. Each stage builds directly on the last: you first learn the language, then master query craft and schema design, then explore the internals that make databases fast and reliable, and finally tackle the architectural and performance challenges of production systems.
Foundations: Learning the SQL Language
BeginnerRead and write core SQL confidently — SELECT, filter, sort, aggregate, and join tables across a real relational database.
▸ Study plan for this stage
Pace: 8–10 weeks, ~25–30 pages/day (alternating between both books; ~2 weeks per major topic)
- Relational database structure: tables, rows, columns, keys, and relationships
- SELECT statement fundamentals: retrieving data with column selection and WHERE filtering
- Filtering and comparison operators: WHERE clauses, AND/OR logic, IN, BETWEEN, LIKE, NULL handling
- Sorting and limiting results: ORDER BY, LIMIT, and controlling result set size
- Aggregate functions: COUNT, SUM, AVG, MIN, MAX, and GROUP BY with HAVING
- Joining tables: INNER JOIN, LEFT JOIN, RIGHT JOIN, and understanding foreign key relationships
- Subqueries and derived tables: nesting queries and using results as data sources
- Data types and basic schema understanding: recognizing VARCHAR, INT, DATE, and how they affect queries
- What is a primary key and a foreign key, and why are they essential in a relational database?
- How do you write a SELECT statement to retrieve specific columns from a table with multiple filter conditions?
- What is the difference between INNER JOIN, LEFT JOIN, and RIGHT JOIN, and when would you use each?
- How do you use GROUP BY and aggregate functions (like SUM, COUNT, AVG) to summarize data?
- What is a subquery, and how can you use it to solve problems that require multiple steps?
- How do you sort results in descending order and limit the number of rows returned?
- Set up a local database (MySQL, PostgreSQL, or SQLite) with sample data and write 10+ SELECT queries retrieving specific columns and rows
- Write WHERE clauses combining AND/OR logic, IN operators, BETWEEN ranges, and LIKE pattern matching on real tables
- Create queries that join two or more tables together and verify the results match your expectations
- Write GROUP BY queries with aggregate functions and HAVING clauses to summarize sales, user counts, or similar metrics
- Build a multi-step problem (e.g., 'find the top 5 customers by total purchase amount') using both joins and aggregation
- Write 5–10 subqueries that solve problems you couldn't easily solve with a single SELECT statement
Next up: Mastering these core SQL fundamentals—SELECT, filtering, joining, and aggregating—equips you to tackle intermediate topics like window functions, CTEs, and optimization, which unlock more sophisticated data analysis and reporting.
The single best beginner-friendly introduction to SQL: clear prose, progressive examples, and full coverage of joins, subqueries, and set operations. Start here to build solid query-writing fundamentals.
Reinforces and extends SQL fundamentals with a strong emphasis on thinking through problems before writing queries. Reading it second cements the mental model built in the first book.
Relational Theory & Schema Design
BeginnerUnderstand the relational model deeply, design normalized schemas, and know why good data structure prevents entire classes of bugs.
▸ Study plan for this stage
Pace: 6–8 weeks, ~40–50 pages/day (alternating between both books; start with Hernandez for practical grounding, then deepen with Date)
- The relational model as a mathematical foundation: relations, tuples, attributes, and domains
- Functional dependencies and their role in identifying data anomalies and design flaws
- Normalization forms (1NF through 3NF, BCNF) and why each eliminates specific classes of update/insertion/deletion anomalies
- Entity-relationship modeling (ER diagrams) as a bridge from business requirements to relational schemas
- Primary keys, foreign keys, and referential integrity as structural constraints that prevent logical inconsistencies
- The difference between logical schema design and physical implementation; why good design survives schema changes
- Denormalization trade-offs: when and why you might intentionally violate normalization rules for performance
- How proper schema design prevents entire categories of bugs (data duplication, orphaned records, inconsistent updates)
- What is a functional dependency, and how do you identify one from a business requirement? Why does recognizing FDs prevent anomalies?
- Walk through a poorly normalized table and explain which normal form it violates and what anomalies it suffers from (insertion, update, deletion)
- Design a normalized schema (3NF or BCNF) for a real-world scenario (e.g., a library, e-commerce site, or school system) and justify your choices
- How do primary keys and foreign keys enforce data integrity, and what happens when you violate referential integrity?
- When would you intentionally denormalize a schema, and what trade-offs are you making?
- Explain the difference between the logical relational model (as Date describes it) and how it maps to physical SQL table structures
- Work through Hernandez's case studies (Chapters 4–6) and redraw the ER diagrams, then normalize each to 3NF step-by-step, documenting which anomalies you eliminate at each stage
- Take a real messy dataset or poorly designed schema you've encountered and normalize it; document the functional dependencies you discover and the normal form violations
- Design a complete schema for a moderately complex domain (restaurant reservations, project management, or inventory system) using ER modeling, then normalize to BCNF and create the SQL DDL
- Read Date's chapters on functional dependencies and practice identifying FDs from written business rules; create dependency diagrams for at least 3 different scenarios
- Implement a schema in an actual database (PostgreSQL, SQLite, or MySQL), create sample data, and deliberately violate a foreign key constraint to observe the error; then fix it
- Write a short document (1–2 pages) explaining why a specific design choice (e.g., using a junction table for many-to-many relationships) prevents a class of bugs, with concrete examples
Next up: Mastering relational theory and normalization gives you the conceptual foundation to write efficient, maintainable queries and understand how to optimize schemas without introducing bugs—preparing you to move into SQL query writing and performance tuning with confidence.
A practical, step-by-step guide to designing relational schemas from scratch — entities, relationships, normalization through 3NF. Bridges the gap between writing queries and structuring the data they run against.
The canonical text on relational theory by one of its founding thinkers. Reading it after hands-on practice gives rigorous grounding in keys, constraints, normalization, and why the relational model is designed the way it is.
Advanced SQL & Query Mastery
IntermediateWrite sophisticated, expressive SQL — window functions, CTEs, advanced aggregations — and understand how query structure affects correctness and performance.
▸ Study plan for this stage
Pace: 8–10 weeks, ~40–50 pages/day (mix of reading and hands-on practice). Week 1–3: SQL Cookbook (recipes & patterns); Week 4–5: T-SQL Window Functions (foundational window concepts); Week 6–8: T-SQL Window Functions (advanced window techniques); Week 8–10: Integration project combining both books.
- Window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, aggregate window functions) and their use cases for ranking, sequencing, and running calculations
- Common Table Expressions (CTEs) and recursive CTEs for breaking down complex queries into readable, maintainable steps
- Advanced aggregation patterns: GROUP BY variations, HAVING clauses, and how to aggregate across different logical groupings within a single query
- Partitioning logic: understanding PARTITION BY and ORDER BY within window functions to control scope and sequence of calculations
- Query optimization fundamentals: how execution plans work, index usage, and why certain query structures perform better than others
- Set-based thinking: writing queries that operate on entire result sets rather than row-by-row logic, and recognizing when window functions replace procedural approaches
- Real-world SQL patterns from the Cookbook: handling gaps, running totals, row comparisons, and multi-step transformations
- T-SQL specifics: syntax, functions, and performance considerations unique to SQL Server (as covered in Ben-Gan's book)
- When would you use ROW_NUMBER vs. RANK vs. DENSE_RANK, and what is the practical difference in output for data with ties?
- How do you write a recursive CTE, and what real-world scenarios (hierarchies, sequences, date ranges) make recursion the right choice?
- Explain the difference between a window function aggregate (e.g., SUM() OVER (...)) and a traditional GROUP BY aggregate. When is each appropriate?
- How does the PARTITION BY clause change the scope of a window function, and how do you use ORDER BY within a window to control sequence?
- Write a query that calculates a running total and the previous row's value in a single pass—what window functions enable this?
- Given a poorly performing query, how would you identify whether the issue is missing indexes, inefficient joins, or suboptimal window function usage?
- Work through 10–15 recipes from SQL Cookbook that use window functions or CTEs (e.g., ranking rows, calculating running totals, finding gaps in sequences). Implement each in your database environment and verify output.
- Refactor 3–5 queries you've written previously that used subqueries or self-joins to use CTEs or window functions instead. Compare readability and performance.
- Build a recursive CTE that generates a date range or organizational hierarchy (e.g., manager–employee tree). Test with real or realistic data.
- Create a query that uses multiple window functions with different PARTITION BY and ORDER BY clauses in the same SELECT. Document what each window function calculates and why.
- Write a query that calculates year-over-year growth, month-over-month change, or similar comparative metrics using LAG/LEAD window functions.
- Analyze execution plans for 3–4 queries: one using window functions, one using GROUP BY, one using a CTE, and one using a subquery. Document which is fastest and why.
Next up: Mastery of window functions, CTEs, and advanced aggregation patterns equips you to write expressive, performant queries that form the foundation for the next stage—whether that's query tuning, database design, or building data pipelines where complex transformations are routine.
A problem-solution format covering dozens of real-world query challenges. Reading it here transforms a competent SQL writer into a fluent one by exposing patterns that textbooks rarely cover.
The definitive deep-dive on window functions — one of the most powerful and underused features of modern SQL. Mastering this unlocks elegant solutions to ranking, running totals, and time-series problems.
Database Internals: How Engines Really Work
IntermediateUnderstand how a database engine stores data on disk, builds and uses indexes, executes query plans, and guarantees ACID transactions.
▸ Study plan for this stage
Pace: 8–10 weeks, ~40–50 pages/day (with 2–3 days/week for exercises and review)
- Storage engines and data structures: B-trees, LSM trees, and how they optimize disk I/O patterns
- Page management and buffer pools: how databases keep hot data in memory and manage eviction policies
- Index structures and query optimization: how indexes are built, maintained, and used to accelerate lookups and range scans
- Query execution and cost-based optimization: how query planners estimate costs and choose execution strategies
- ACID properties and transaction isolation: how databases implement atomicity, consistency, isolation, and durability guarantees
- Concurrency control mechanisms: locking, MVCC, and timestamp-based protocols for safe concurrent access
- Recovery and write-ahead logging: how WAL ensures durability and enables crash recovery
- Distributed transaction coordination: two-phase commit and consensus protocols for multi-node consistency
- What are the key differences between B-tree and LSM tree storage engines, and when would you choose one over the other?
- How does a buffer pool work, and what eviction policies (LRU, clock, etc.) does it use to manage limited memory?
- Explain how a clustered index differs from a secondary index and how query planners decide which index to use.
- What is a query execution plan, and how do cost-based optimizers estimate the cost of different plan alternatives?
- How do write-ahead logging (WAL) and checkpointing work together to guarantee durability and enable recovery?
- Compare isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) and the anomalies they prevent.
- How do locking and MVCC (multi-version concurrency control) differ as concurrency control strategies, and what are their trade-offs?
- What is the two-phase commit protocol, and what problems does it solve in distributed transactions?
- Trace through a B-tree insertion and deletion by hand; then implement a simplified B-tree insert operation in your language of choice.
- Analyze a real query execution plan from PostgreSQL or MySQL (using EXPLAIN); identify the index used, estimated vs. actual rows, and bottlenecks.
- Write a simple buffer pool simulator in code: implement a page cache with LRU eviction and measure hit/miss rates under different access patterns.
- Create a test scenario in SQLite or PostgreSQL that demonstrates different isolation levels (dirty reads, phantom reads, lost updates) and observe which anomalies occur at each level.
- Implement a basic write-ahead log (WAL) for a toy key-value store: write operations to a log before applying them, then simulate a crash and recovery.
- Profile a database workload (read-heavy vs. write-heavy) and predict which storage engine (B-tree vs. LSM) would perform better; verify with benchmarks.
- Design and implement a simple two-phase commit protocol for coordinating transactions across two in-memory data stores; test failure scenarios.
- Reverse-engineer the locking behavior of a real database by running concurrent transactions and observing lock waits using system tables (e.g., pg_locks in PostgreSQL).
Next up: This stage builds the foundational knowledge of how databases work internally; the next stage will apply these concepts to practical optimization, schema design, and real-world performance tuning in production systems.
A modern, accessible tour of storage engines, B-trees, LSM trees, and distributed consensus. Reading it first in this stage gives a concrete mental model of the machinery beneath every query.
The foundational text on ACID, concurrency control, locking, and recovery — written by a Turing Award winner who helped invent these concepts. Essential for understanding why databases are reliable.
Performance, Scaling & Production Systems
ExpertDiagnose and fix slow queries, design indexes strategically, and understand the trade-offs of scaling relational databases in real production environments.
▸ Study plan for this stage
Pace: 8–10 weeks, ~40–50 pages/day. Start with "High Performance MySQL" (Chapters 1–8, ~2 weeks), then "Designing Data-Intensive Applications" (Chapters 1–9, ~6–8 weeks), allowing time for hands-on labs between sections.
- Query optimization: EXPLAIN output analysis, index selection, and identifying bottlenecks in slow queries
- Index design trade-offs: B-tree indexes, composite indexes, covering indexes, and the cost of writes vs. read performance
- Storage engines and their performance characteristics: InnoDB vs. MyISAM, transaction isolation, and locking mechanisms
- Replication and scaling strategies: master-slave replication, read replicas, and consistency challenges in distributed systems
- Data consistency models: ACID properties, eventual consistency, and CAP theorem trade-offs in production systems
- Partitioning and sharding: horizontal scaling strategies, partition key selection, and cross-shard query complexity
- Monitoring and profiling: identifying slow logs, understanding query execution plans, and production observability
- Distributed systems challenges: network partitions, consensus algorithms, and handling failures in multi-node databases
- How do you interpret EXPLAIN output to diagnose a slow query, and what index changes would you recommend?
- What are the trade-offs between using a composite index vs. multiple single-column indexes for a given query pattern?
- When would you choose replication over sharding for scaling a database, and what consistency guarantees does each provide?
- How does the CAP theorem apply to your production database architecture, and which property are you willing to sacrifice?
- What monitoring metrics and slow query logs would you set up to identify performance problems before they impact users?
- How would you partition a large table across multiple servers, and what challenges arise when querying across partitions?
- Set up a local MySQL instance and run EXPLAIN on 5–10 slow queries from a real or sample workload; identify missing indexes and rewrite queries to improve performance
- Create a composite index on a multi-column WHERE clause and measure query execution time before/after; compare against separate single-column indexes
- Configure MySQL replication (master-slave) locally or in Docker; write a test script that reads from the replica and observe replication lag under load
- Profile a workload using MySQL slow query log and performance_schema; identify the top 3 bottlenecks and implement fixes (indexing, query rewrite, or schema changes)
- Design a sharding strategy for a sample dataset (e.g., user table with millions of rows); write code to route queries to the correct shard and handle cross-shard aggregations
- Implement a simple distributed transaction scenario (e.g., transferring funds between accounts on different shards) and document consistency guarantees and failure modes
Next up: This stage equips you with the diagnostic tools and architectural patterns to run databases reliably at scale; the next stage will likely deepen your expertise in specific advanced topics (e.g., NoSQL trade-offs, real-time analytics, or operational excellence) or move you toward designing complete data systems end-to-end.
The industry-standard reference for MySQL performance tuning — indexing strategy, query optimization, schema design for scale, and replication. Applies broadly beyond MySQL to relational databases in general.
The capstone of the curriculum: a masterful synthesis of how relational and non-relational systems store, replicate, and distribute data reliably at scale. Puts everything learned so far into the broader context of modern data engineering.
Discussion
Keep reading
Paths that share books, cover the same subject, or open a related topic.
