SQL for Developers (Not DBAs)
Master SQL the way developers actually use it — writing queries, modeling data, and optimizing performance inside real applications. You will build a complete e-commerce reporting backend using raw SQL across six progressive modules.
Course Content
6 modules · 30 lessonsFoundations: How Databases Think
Students can explain the relational model, write basic SELECT queries, and filter rows with WHERE conditions.
What is a relational database and why should developers care
relational model, tables, rows, columns, primary key, schema, RDBMS options
Writing your first SELECT query — columns, aliases, and literals
SELECT syntax, column selection, star selector, column aliases, literal values, query execution order
Filtering rows with WHERE — operators and conditions
WHERE clause, comparison operators, BETWEEN, IN, LIKE, NULL handling, IS NULL, IS NOT NULL
Sorting and limiting results with ORDER BY and LIMIT
ORDER BY, ASC, DESC, multiple sort columns, LIMIT, OFFSET, pagination pattern, NULLS FIRST LAST
SQL data types every developer must know
integer types, varchar vs text, numeric vs float, boolean, date and timestamp, UUID, type casting, choosing the right type
Aggregations and Grouping
Students can aggregate data with GROUP BY, filter groups with HAVING, and use window functions for running totals and rankings.
COUNT, SUM, AVG, MIN, MAX — aggregate functions explained
aggregate functions, COUNT(*) vs COUNT(col), SUM, AVG, MIN, MAX, NULLs in aggregation, DISTINCT inside aggregates
GROUP BY — aggregate data by category or value
GROUP BY syntax, grouping multiple columns, SELECT rules with GROUP BY, non-aggregated columns, common mistakes
HAVING vs WHERE — filtering groups after aggregation
HAVING clause, WHERE vs HAVING execution order, filtering on aggregate results, combining WHERE and HAVING
Window functions — ROW_NUMBER, RANK, and running totals
OVER clause, PARTITION BY, ORDER BY in window, ROW_NUMBER, RANK, DENSE_RANK, SUM as running total, window vs GROUP BY
LAG and LEAD — comparing rows to previous or next values
LAG function, LEAD function, offset parameter, default value, practical use cases, period-over-period comparison
Joins — Combining Tables
Students can join multiple tables using INNER, LEFT, RIGHT, and FULL OUTER joins, and understand when each join type is appropriate.
How SQL joins work — the mental model developers need
join concept, foreign keys, join condition, cartesian product, ON clause, table aliases in joins
INNER JOIN vs LEFT JOIN — which rows survive
INNER JOIN behavior, LEFT JOIN behavior, NULL columns from outer join, missing data detection, JOIN keyword shorthand
RIGHT JOIN and FULL OUTER JOIN — when and why to use them
RIGHT JOIN, FULL OUTER JOIN, symmetric equivalence with LEFT JOIN, NULLs in outer joins, practical use cases, COALESCE with joins
Joining three or more tables without losing your mind
multi-table joins, join order, intermediate result sets, join to aggregated subquery, readability patterns, aliasing strategy
Self joins and non-equi joins — advanced join patterns
self join pattern, employee hierarchy example, non-equi join, range join, join on inequality, practical cases
Schema Design and Constraints
Students can design normalized schemas, define constraints, and model relationships that prevent bad data from entering the database.
Database normalization — 1NF, 2NF, 3NF without the textbook jargon
1NF atomic values, 2NF partial dependency, 3NF transitive dependency, denormalization tradeoff, when to normalize
Primary keys, foreign keys, and referential integrity
PRIMARY KEY, FOREIGN KEY, referential integrity, ON DELETE CASCADE, ON DELETE RESTRICT, ON DELETE SET NULL, surrogate vs natural keys
UNIQUE, NOT NULL, CHECK — enforcing data rules in the schema
NOT NULL constraint, UNIQUE constraint, composite UNIQUE, CHECK constraint, DEFAULT values, constraint naming, deferred constraints
Indexes — what they are and when to add them
B-tree index, how indexes speed queries, index on foreign key, multi-column index, index selectivity, when not to index, EXPLAIN basics
Many-to-many relationships and junction tables
many-to-many pattern, junction table, composite primary key vs surrogate, extra columns on junction, querying many-to-many, tag pattern
Subqueries and CTEs
Students can write subqueries, correlated subqueries, and CTEs to express complex multi-step logic clearly in a single SQL statement.
Scalar subqueries and subqueries in WHERE — inline queries explained
subquery definition, scalar subquery, subquery in WHERE, IN with subquery, NOT IN gotcha with NULLs, subquery vs JOIN
Correlated subqueries — what they are and when they are slow
correlated subquery, outer query reference, EXISTS, NOT EXISTS, performance implications, when to use vs rewrite
CTEs with WITH — making complex queries readable
CTE syntax, WITH keyword, multiple CTEs, CTE vs subquery readability, CTE scope, chaining CTEs
Recursive CTEs — querying hierarchical data
recursive CTE syntax, anchor member, recursive member, UNION ALL, depth limit, org chart traversal, bill of materials
Subqueries in FROM — derived tables and their uses
subquery in FROM clause, derived table, aliasing requirement, filtering aggregated results, composing with JOINs
Transactions, Performance, and Production SQL
Students can write safe transactional SQL, read query execution plans, and optimize slow queries using indexes, rewrites, and schema changes.
Transactions and ACID — what developers need to know
ACID properties, BEGIN, COMMIT, ROLLBACK, transaction isolation, savepoints, implicit transactions
Reading EXPLAIN output — understanding query execution plans
EXPLAIN, EXPLAIN ANALYZE, sequential scan, index scan, cost estimates, actual vs estimated rows, node types, startup cost vs total cost
N+1 queries and how to fix them with SQL
N+1 problem definition, ORM context, batch loading, JOIN solution, IN clause batching, eager loading concept
Common query rewrites that make SQL faster
SELECT star vs specific columns, DISTINCT vs GROUP BY, subquery vs JOIN performance, function on indexed column, partial indexes, covering indexes
UPSERT, soft deletes, and common application SQL patterns
INSERT ON CONFLICT, UPSERT pattern, soft delete with deleted_at, partial index for soft deletes, UPDATE returning, bulk insert