SQL lessons
Querying, joins, aggregation and window functions, one topic at a time. 34 lessons across 7 tracks, each ending with a short self-check.
34lessons
183mtotal
7tracks
68checks
Foundations & querying
Relational concepts, SELECT, filtering, sorting, nulls and duplicates.
Databases and relational conceptsTables, rows, columns, and the keys that connect them.The SELECT statementChoosing columns, renaming them, and the difference between * and naming them.Filtering with WHEREComparison operators, BETWEEN, IN and LIKE.Combining conditions with AND, OR and NOTBoolean logic and the precedence rule that causes wrong results.Sorting and limiting resultsORDER BY, ASC/DESC, and the dialect differences in LIMIT.Working with NULLWhy NULL is not a value, and the comparisons that silently fail.Removing duplicates with DISTINCTHow DISTINCT works across one column and several, and what it costs.
Aggregation & summary
Aggregate functions, GROUP BY, HAVING and conditional aggregation.
Aggregate functionsCOUNT, SUM, AVG, MIN and MAX, and the COUNT(*) versus COUNT(col) distinction.GROUP BYGrouping rows, the SELECT rule, and grouping by several columns.HAVING versus WHEREFiltering groups after aggregation, and why the order matters.Conditional aggregation with CASEPivoting inside a single query with CASE inside an aggregate.
Combining data
Every join type, multi-table joins, and set operations.
Join conceptsWhat a join does, matching keys, and why row counts change.INNER JOINKeeping only matching rows, and what silently disappears.LEFT and RIGHT JOINPreserving unmatched rows, finding missing data, and the WHERE trap.FULL OUTER and CROSS JOINKeeping everything from both sides, and deliberate Cartesian products.Joining three or more tablesChaining joins, keeping them readable, and where the row count goes wrong.UNION and UNION ALLStacking result sets, and why UNION ALL is usually the right choice.
Functions & transformation
String, numeric, date and null-handling functions.
String functionsCONCAT, SUBSTRING, LENGTH, UPPER, LOWER, REPLACE and TRIM.Numeric functionsROUND, CEIL, FLOOR, ABS, and integer division.Date and time functionsDATEADD, DATEDIFF, EXTRACT, truncation and date ranges that do not leak.COALESCE and NULLIFSubstituting values for nulls, and generating them deliberately.
Subqueries & CTEs
Scalar subqueries, derived tables, WITH, and EXISTS versus IN.
Window functions
OVER, partitioning, ranking, LAG/LEAD and running totals.
Introduction to window functionsOVER() versus GROUP BY, and keeping detail while aggregating.PARTITION BY and ORDER BYDefining the window, and how ORDER BY changes the frame.ROW_NUMBER, RANK and DENSE_RANKThe three ranking functions, how they treat ties, and top-N per group.LAG and LEADComparing a row with the one before or after it.Running totals and moving averagesCumulative sums, moving windows, and getting the frame right.
Creating & managing tables
Data types, CREATE TABLE, INSERT/UPDATE/DELETE and views.
SQL data typesChoosing column types, and the ones that cause trouble later.CREATE TABLE and constraintsDefining a table with the constraints that keep data valid.INSERT, UPDATE and DELETEModifying data, and the WHERE clause that saves your job.ViewsSaving a query as a reusable object, and where views help or hurt.