From first query to production PostgreSQL
Master SQL through practical, step-by-step lessons. Start with SQLite for zero-friction learning, then graduate to PostgreSQL via Docker. Query a realistic e-commerce database from your first SELECT to window functions, CTEs, and production DevOps workflows.
Download the SQL setup files from GitHub before Lesson 1. This prevents the first-run setup confusion and gives you the exact files the lesson expects.
Modules 1-3 use SQLite (zero setup on Mac/Linux). Module 4 introduces PostgreSQL via Docker.
Progress through these modules to master SQL. Click any module to expand and view lessons.
5 modules • 1 open
Get hands-on with SQL by querying a real e-commerce database. You'll learn to retrieve data, pick specific columns, remove duplicates, and control your output — all using actual SQLite commands in your terminal.
Learn to find exactly the data you need with WHERE clauses, comparison operators, pattern matching, and NULL handling. Then organize your results with ORDER BY and paginate with LIMIT and OFFSET.
Move beyond reading data to changing it. This SQLite-focused lesson covers inserting new rows, updating existing records, deleting safely, understanding SQLite's flexible type system, and working with ISO 8601 dates.
Design and build your own database tables from scratch. This SQLite-focused lesson teaches columns, constraints (PRIMARY KEY, NOT NULL, UNIQUE, CHECK, FOREIGN KEY), ALTER TABLE changes, and safe table removal with DROP TABLE.
Learn to summarize data with aggregate functions and GROUP BY. Transform thousands of rows into meaningful totals, averages, and counts — the foundation of every business report.
Connect related tables to build complete pictures of your data. Joins are the most powerful tool in SQL — they let you answer questions that span multiple tables, like 'what did each customer order and how much did they spend?'
Nest one query inside another to answer complex questions in a single statement. Subqueries let you filter, compare, and derive data dynamically — no hardcoded values needed.
Master CASE WHEN for conditional logic, handle NULLs gracefully with COALESCE, manipulate strings and dates, and combine result sets with UNION and friends. This lesson uses SQLite-flavored functions and emphasizes how result grain changes when you group or stack results.
Put all your Module 2 skills to the test by building a comprehensive sales report from scratch. No new concepts — just pure practice combining JOINs, aggregation, subqueries, CASE WHEN, and everything else you've learned, with explicit attention to result grain.
Learn to write cleaner, more readable SQL using Common Table Expressions. Refactor tangled subqueries into named building blocks, chain multiple CTEs together, and explore recursive CTEs to traverse hierarchical data like category trees.
Master SQL window functions to perform calculations across sets of rows without collapsing them. Learn ROW_NUMBER, RANK, LAG, LEAD, running totals, moving averages, and frame clauses to build powerful analytics queries.
Learn how database indexes work, when to create them, and how to use SQLite's EXPLAIN QUERY PLAN to measure their impact. Optimize real queries step by step and understand the tradeoffs of indexing without over-trusting any single plan output.
Learn to protect data integrity with transactions, simplify complex queries with views, and automate database actions with triggers. These three features are the foundation of reliable, maintainable database systems.
Learn to design relational databases from scratch in a separate SQLite design lab. Understand normalization forms (1NF, 2NF, 3NF), entity relationships, junction tables, and when to break the rules and denormalize for performance.
Launch a production-grade PostgreSQL 16 database in Docker, connect with psql, and verify that everything you learned in Modules 1-3 transfers directly. This lesson bridges your SQLite knowledge to the database used in real-world DevOps. Module 4 uses a compact teaching schema; the larger Lesson 501 capstone later introduces a separate analytics schema on a separate container.
Unlock the power tools that set PostgreSQL apart: JSONB for semi-structured data, arrays, UUID generation, schemas for organization, import/export with COPY, and identity columns. This lesson is fully PostgreSQL-specific and continues the compact Module 4 schema in the Docker container.
Apply SQL skills to real DevOps operations in PostgreSQL: write database migrations, monitor health, analyze query performance, back up and restore databases, and build operational dashboards. These are the queries you'll run at 3 AM during incidents.
Master the two pillars of production databases: making queries fast with EXPLAIN ANALYZE, query plans, and indexing strategies, and keeping data safe with SQL injection prevention, parameterized queries, role-based access control, and row-level security. This lesson continues the compact PostgreSQL schema you created in Lessons 402-404 inside the `pg-sql-course` container.
Work through these lessons at your own pace. Each step includes commands, explanations, and expected outcomes.