← Back to SQL overview

Hand-written tutorials, ordered as a recommended learning path.

1. 21 EXPLAIN Plans Learn to read EXPLAIN and EXPLAIN ANALYZE output to diagnose slow queries, spot sequential scans, and optimize SQL performance. 6 min read →
2. 22 Isolation Levels Understand SQL isolation levels, their concurrency trade-offs, and how to choose the right level for your application. 6 min read →
3. 23 Window Functions Guide Master SQL window functions with practical examples covering ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and frame clauses. 5 min read →
4. 24 CTEs in SQL A practical guide to SQL Common Table Expressions: the WITH clause, recursive CTEs, readability improvements, materialization hints, and when CTEs outperform subqueries. 10 min read →
5. 25 Query Optimization Practical SQL query optimization: reading EXPLAIN plans, choosing the right indexes, avoiding N+1 queries, understanding query planner decisions, and knowing when denormalization makes sense. 9 min read →
6. 26 Transactions & Isolation Understanding SQL transactions and isolation levels: ACID properties, read phenomena, read committed, repeatable read, serializable isolation, deadlocks, and practical concurrency patterns. 10 min read →
7. 27 PG Indexes A practical tour of Postgres indexing: btree, hash, GIN, BRIN, partial and expression indexes, plus how to choose, measure, and maintain them in production. 5 min read →
8. 28 ACID vs BASE What ACID and BASE actually mean, where each shines, and how to reason about consistency, availability, and durability when you design data systems. 4 min read →
9. 29 GROUP BY How aggregate functions interact with GROUP BY, HAVING, and window functions, with practical patterns and pitfalls every backend engineer should know. 4 min read →
10. 30 CTE vs Subquery Compare common table expressions, subqueries, and temporary tables in SQL. Learn when each shines, the performance trade-offs, and concrete examples. 5 min read →
11. 31 EXPLAIN ANALYZE How to read EXPLAIN ANALYZE output, interpret cost estimates, spot bad plans, and use it to drive real performance improvements in production databases. 4 min read →
12. 32 EXPLAIN Learn how to read EXPLAIN output, what scan and join types mean, and how to spot the indexes and rewrites that make slow queries fast. 5 min read →
13. 33 FK Cascades How ON DELETE and ON UPDATE cascade options work, when to use CASCADE, SET NULL, RESTRICT, or NO ACTION, and how to avoid surprise mass deletes. 5 min read →
14. 34 Index Types A practical comparison of B-Tree, Hash, and GIN indexes in PostgreSQL, when each one shines, and how to pick the right structure for your query patterns. 4 min read →
15. 35 SQL Joins A thorough tour of SQL joins with diagrams, sample queries, and the gotchas that bite real systems: NULLs, duplicates, and join order. 4 min read →
16. 36 SQL NULLs Learn how NULL behaves in SQL, why three-valued logic trips up queries, and the patterns that keep your data consistent and your queries correct. 4 min read →
17. 37 PIVOT / UNPIVOT How to reshape rows into columns and back using PIVOT, UNPIVOT, CASE aggregations, and lateral unions across Postgres, MySQL, and SQL Server. 4 min read →
18. 38 Postgres vs MySQL A grounded comparison of Postgres and MySQL across data types, transactions, replication, JSON, and ecosystem - so you can pick the right one for your project. 4 min read →
19. 39 Prepared Statements What prepared statements actually do, why they prevent SQL injection, how they affect performance, and how to use them correctly from any language. 4 min read →
20. 40 Recursive CTEs Learn how recursive CTEs work in SQL, how to traverse hierarchies and graphs in pure SQL, and how to avoid the common termination and performance pitfalls. 4 min read →