Module 16 Web Engineering

SQL Hands-On

Most vibe coders can have an AI write them a SELECT query. Very few of them understand what a JOIN is doing at the execution level, when a subquery is more readable than a JOIN, how window functions differ from GROUP BY, or how to read a query plan to understand why their query is slow. SQL is one of those languages where basic knowledge looks almost identical to ignorance until the problem gets hard — and then the difference becomes glaringly obvious. This module takes you from blank editor to writing complex SQL confidently, with 30 queries that progressively raise the difficulty. You'll start with creating tables and inserting data, move through SELECT and WHERE, master all JOIN types and their common pitfalls, get comfortable with GROUP BY and aggregation, and finally tackle the queries that separate database practitioners from database tourists: subqueries, CTEs, window functions, and query optimization. Every lesson is interactive — you're writing real SQL against real tables, not watching someone else do it. The schema is deliberately complex, modeled on an e-commerce or analytics use case, so the queries you write here are directly applicable to the kinds of data work you'll do in professional settings. No toy examples. No trivial three-row tables.

What You'll Learn

  • 1
    Setting Up and Creating Tables — PostgreSQL data types, CREATE TABLE, constraints, ALTER TABLE
  • 2
    Inserting, Updating, and Deleting — Multi-row inserts, RETURNING, upsert, TRUNCATE vs DELETE
  • 3
    SELECT — FROM, WHERE, ORDER BY, LIMIT, OFFSET, DISTINCT, and column expressions
  • 4
    JOINs — INNER, LEFT, FULL OUTER, self-join, ON vs USING, and common join mistakes
  • 5
    GROUP BY and Aggregation — COUNT, SUM, AVG, HAVING, and NULL in aggregations
  • 6
    Subqueries and CTEs — Scalar, IN, EXISTS, correlated subqueries, WITH, and recursive CTEs
  • 7
    Window Functions — ROW_NUMBER, RANK, LAG, LEAD, running totals, and PARTITION BY
  • 8
    EXPLAIN and Query Optimization — Reading query plans, creating indexes, transactions

Capstone Project: Write 30 Queries Against a Real Schema — From Basic to Advanced

Work through 30 SQL challenges against a multi-table e-commerce schema — progressing from basic SELECT queries to multi-step CTEs, window functions computing running totals and percentile ranks, and correlated subqueries — culminating in a query optimization exercise where you use EXPLAIN ANALYZE to identify a slow query, add the appropriate index, and document the before-and-after performance improvement.

Why This Matters for Your Career

SQL is the language of data, and virtually every application that stores state uses a relational database. Being able to query that database directly — not just through an ORM — is a fundamental professional skill. When you need to debug a data issue, do a one-off analysis, or understand what an ORM is actually generating under the hood, SQL proficiency is what gets you there. Window functions are one of the most underutilized features in SQL, and one of the most powerful. Running totals, rank within a group, lag and lead comparisons — these are analytical patterns that appear constantly in reporting, analytics, and application development. Engineers who know window functions write ten-line SQL queries for problems that other engineers solve with hundreds of lines of application code. Query optimization is a direct path to application performance. An unindexed JOIN on a million-row table is frequently the bottleneck in slow API endpoints. Understanding how to read an EXPLAIN plan, identify sequential scans where index scans should be, and add the right composite index is the kind of concrete, measurable impact that makes engineers indispensable on their teams.