SQL
From your first SELECT to window functions and interview problems. Free, step by step: every lesson is complete, with an animation, a worked example and exercises with answers.
Unit 0: Start here: databases, tables and SQL
Know what a database is, what a table, a row and a key are, and the six practice tables you will use in every lesson.
- What a database, a table and SQL are: A relational database stores data in tables of rows and columns, and SQL (Structured Query Language) is the language you use to ask it questions and to change what it holds.
- Meet the practice database: Six small tables — departments, employees, customers, orders, products and order_items — that every example and exercise in this course uses.
- Keys and relationships: primary keys, foreign keys, one-to-many, many-to-many: A primary key is a column whose value identifies exactly one row. A foreign key is a column that holds another table's primary key, and these links are how tables relate: one-to-many, many-to-many or one-to-one.
- Data types: numbers, text, dates and true/false: Every column has a data type, such as INTEGER, a decimal number, TEXT, a date or a boolean. The type decides what values the column can hold, and how they compare, sort and calculate.
Unit 1: Reading data: SELECT, WHERE and sorting
Pick the columns you want, keep only the rows you want, sort them, and handle the missing values that trip everyone up.
- SELECT and FROM: choosing columns: SELECT lists the columns (or calculations) you want in the result, and FROM names the table they come from.
- WHERE: keeping only the rows you want: WHERE keeps only the rows for which a condition is true, using comparisons such as =, <>, <, > and the words AND, OR and NOT.
- IN, BETWEEN and LIKE: shorter conditions: IN checks a value against a list, BETWEEN checks a range (both ends included), and LIKE matches text against a pattern with % (any number of characters) and _ (exactly one).
- NULL: the missing value: NULL means "no value" or "unknown". Any comparison with it, even `NULL = NULL`, is unknown rather than true. So you test for it with IS NULL, and replace it with COALESCE.
- ORDER BY, LIMIT and OFFSET: sorting and top N: ORDER BY sorts the result by one or more columns (ASC for smallest first, DESC for largest first), and LIMIT keeps only the first n rows, with OFFSET to skip some first.
- DISTINCT: removing duplicate rows: SELECT DISTINCT removes duplicate rows from the result, where two rows are duplicates only if every selected column is the same.
- CASE WHEN: if-then logic inside a query: CASE WHEN condition THEN value … ELSE value END returns a value chosen by the first condition that is true, so a query can label, group or tidy values row by row.
- Built-in functions for text, numbers and dates: Functions such as UPPER, LENGTH, SUBSTR, ROUND and the date functions transform a value inside a query — one value in, one value out, for each row.
Unit 2: Summarising data: aggregates, GROUP BY and HAVING
Turn many rows into answers — totals, averages, counts per group — and know exactly which rows each number is built from.
- Aggregate functions: COUNT, SUM, AVG, MIN and MAX: An aggregate function turns many rows into one value — COUNT counts, SUM adds, AVG averages, MIN and MAX find the smallest and largest — and every one except `COUNT(*)` skips NULLs.
- GROUP BY: one result row per group: GROUP BY splits the rows into groups that share the same value in the listed columns, and the aggregates then return one row per group.
- HAVING: filtering groups, and how it differs from WHERE: HAVING filters groups after GROUP BY, using aggregates such as `COUNT(*) > 1`, while WHERE filters single rows before any grouping happens.
- Conditional aggregation: counting several things at once: Conditional aggregation puts CASE inside an aggregate, such as `SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END)`, so that it counts or sums only the rows that match. One query can then produce several different totals, or turn rows into columns (a pivot).
- The order a query really runs in: You write a query as SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. The database works it out in a different order: FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT. That order explains most "why doesn't this work?" errors.
Unit 3: Joins: combining tables
Put rows from two or more tables side by side, keep or drop the rows that have no match on purpose, and spot the joins that quietly double your totals.
- Joins, and INNER JOIN: A JOIN combines rows from two tables wherever a condition (usually matching keys) is true, and INNER JOIN keeps only the rows that found a match on both sides.
- LEFT JOIN: keep every row from one side: A LEFT JOIN keeps every row from the left (first) table, joins matching rows from the right table, and fills the right-hand columns with NULL where there is no match.
- Finding what is missing: anti-joins: An anti-join returns rows from one table that have no match in another — written as `LEFT JOIN … WHERE right.key IS NULL`, or as `WHERE NOT EXISTS (…)`.
- Self joins: a table joined to itself: A self join joins a table to itself, under two different aliases. That lets a row be compared with another row of the same table, such as an employee with their manager.
- Joining three or more tables: You join more than two tables by adding one JOIN … ON for each table, following the keys: orders to customers, orders to order_items, order_items to products. Each result row can then hold columns from all of them.
- FULL OUTER JOIN and CROSS JOIN: A FULL OUTER JOIN keeps every row from both tables, with NULLs wherever either side has no match. A CROSS JOIN pairs every row of one table with every row of the other.
- Join traps: double counting, and ON versus WHERE: There are two classic join bugs. Fan-out: joining a "many" table repeats the "one" side, so its numbers get summed several times. And a right-table filter in WHERE, which silently turns a LEFT JOIN into an INNER JOIN.
Unit 4: Subqueries, CTEs and set operations
Use the result of one query inside another, break hard questions into named steps with WITH, walk trees with recursion, and stack results with UNION.
- Subqueries: a query inside a query: A subquery is a SELECT inside another query, in brackets: it can return one value (to compare with), a list (for IN), or a whole table (to select FROM).
- Correlated subqueries, EXISTS, and the NOT IN trap: A correlated subquery refers to a column of the outer row, so it is worked out once for each outer row. EXISTS asks "does the subquery return any row at all?", which makes it the safe way to test for a match.
- CTEs: naming each step with WITH: A CTE (common table expression) is a named, temporary result, written at the top of a query with WITH name AS (…). Later parts of the query can use it like a table.
- Recursive CTEs: walking trees and filling in dates: A recursive CTE refers to itself. An anchor query gives the starting rows. Then a recursive part, joined with UNION ALL, keeps adding rows from the previous round until no new rows appear.
- UNION, UNION ALL, INTERSECT and EXCEPT: Set operators stack the results of two queries. UNION ALL keeps every row, and UNION removes duplicates. INTERSECT keeps rows found in both, and EXCEPT keeps rows in the first but not the second.
Unit 5: Window functions: ranking, running totals and comparing rows
Calculate across related rows — rank them, compare each with the one before, keep running totals — without losing the individual rows the way GROUP BY does.
- What a window function is: OVER and PARTITION BY: A window function calculates a value for each row from a set of related rows, its "window", which is defined by OVER (PARTITION BY … ORDER BY …). Unlike GROUP BY, it keeps every row in the result.
- Ranking: ROW_NUMBER, RANK and DENSE_RANK: ROW_NUMBER numbers the rows 1, 2, 3, 4 with no ties. RANK gives ties the same number and then skips (1, 2, 2, 4). DENSE_RANK gives ties the same number without skipping (1, 2, 2, 3).
- LAG and LEAD: comparing with the previous or next row: `LAG(column)` returns the value from the previous row and `LEAD(column)` the value from the next row, in the order set by OVER (… ORDER BY …). That lets a row be compared with its neighbour.
- Running totals and moving averages: window frames: With ORDER BY inside OVER, SUM and AVG become running totals; a frame such as `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW` limits the window to nearby rows, giving a moving average.
- Share of total, NTILE and FIRST_VALUE: `x / SUM(x) OVER ()` gives each row's share of the total. `NTILE(n)` splits the ordered rows into n roughly equal buckets. FIRST_VALUE and LAST_VALUE pull a value from the first or last row of the window.
Unit 6: Building tables and changing data
Create tables with the right rules, add, change and remove rows safely, group changes into transactions, and design tables that don't contradict themselves.
- CREATE TABLE and ALTER TABLE: CREATE TABLE defines a new table — its name, its columns, their types and rules — and ALTER TABLE changes an existing table, for example by adding a column.
- Constraints: the rules that keep bad data out: Constraints are rules the database enforces on every write — PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK and DEFAULT — so that rows which break them are refused instead of stored.
- INSERT, UPDATE and DELETE: INSERT adds new rows, UPDATE changes values in existing rows, and DELETE removes rows. UPDATE and DELETE act on every row their WHERE matches, and on every row of the table if there is no WHERE.
- Transactions and ACID: A transaction groups several statements into one all-or-nothing unit: BEGIN, then the changes, then COMMIT to keep them or ROLLBACK to undo them. ACID names the four guarantees it gives: Atomic, Consistent, Isolated, Durable.
- Normalisation: designing tables that don't contradict themselves: Normalisation is designing tables so that each fact is stored in exactly one place. The usual target is third normal form (3NF): one value per cell, and every column depends on the key, the whole key, and nothing but the key.
- Views: saved queries you can use like tables: A view is a named, saved SELECT that you query like a table. It stores no data of its own, so it always shows the current data. A materialised view stores the result and must be refreshed.
- DDL, DML, DCL, TCL — and DELETE versus TRUNCATE versus DROP: SQL commands fall into four families: DDL changes structure (CREATE, ALTER, DROP), DML changes data (INSERT, UPDATE, DELETE), DCL controls access (GRANT, REVOKE) and TCL controls transactions (COMMIT, ROLLBACK). DELETE removes rows, TRUNCATE removes all rows, and DROP removes the whole table.
Unit 7: Performance: indexes and query plans
Know why a query is slow, read the plan the database chose, and write queries and indexes that let it find rows without reading the whole table.
- Indexes: how a B-tree makes lookups fast: An index is a separate, sorted copy of one or more columns, usually a B-tree, with pointers back to the rows. The database can then jump to the matching rows instead of scanning the whole table.
- Reading a query plan with EXPLAIN: EXPLAIN shows the plan the database chose for a query — which tables it scans, which indexes it searches, how it joins and sorts — without (or, with ANALYZE, while) running it.
- Index-friendly queries: sargable filters and composite indexes: A filter is sargable when the database can use an index for it: the indexed column stands alone on one side of the comparison. A composite index on (a, b) helps queries that filter on a, or on a and b, but not on b alone.
- How a database runs a join: nested loop, hash join, merge join: A database has three main ways to run a join. A nested loop looks up matches in the other table for each row of one. A hash join builds a hash table from one side and probes it with the other. A merge join walks two sorted inputs together. The optimiser picks one for each join.
Unit 8: SQL interview problems, from easy to hard
Solve the classic SQL interview questions from a blank page, explain your approach while you write, and name the edge cases before the interviewer does.
- How to answer any SQL interview question: A repeatable method: restate the question, look at the tables and their grain, ask about edge cases, and plan the steps out loud. Then write the query as small CTEs, and check the result against a few rows you can work out by hand.
- Quick-fire SQL theory questions: Short "what is the difference between…" questions that open many SQL interviews; each is best answered with a one-line definition of both sides, the key difference, and when you would use each.
- The second highest salary (and the Nth): Return the second highest salary from employees. If there is no second highest salary, return NULL.
- Employees who earn more than their manager: Find every employee who earns more than their own manager. Show the employee's name and salary, and the manager's name and salary.
- Customers who have never had an order delivered: List every customer who has never had an order delivered — including customers who have never ordered at all.
- Find duplicate emails, then remove the duplicates: Find every email that belongs to more than one customer, with how many sign-ups it has. Then delete the duplicates, keeping the row with the lowest customer_id.
- The highest-paid employee in each department: For each department, return the employee (or employees, if tied) with the highest salary. Show the department name, the employee's name and their salary.
- Employees paid above their department's average: List employees whose salary is above the average salary of their own department, showing that average next to them.
- Month-over-month revenue growth: For each month, show the revenue and the percentage change from the previous month, rounded to one decimal place.
- Each customer's first order: For each customer who has ordered, show their first order — its id, date and amount — and how many days after signing up they placed it.
- Streaks: orders on consecutive days (gaps and islands): Find every streak where a customer ordered on two or more consecutive days. Show the customer, the first and last day of the streak, and its length in days.
- Products bought together: Find every pair of different products that appear in the same order, and count how many orders contain each pair. List each pair once.
- Which products make up 80% of revenue? (share of total): Show each product's revenue, its percentage of total revenue, and the running (cumulative) percentage from the best seller down. Then list the products that bring in the first 80% of revenue.
- Retention: who came back within 30 days?: Of the customers who placed an order, what percentage placed another order within 30 days of their first one?
- The median salary: Find the median salary of all employees, and then the median salary in each department.
- Customers who bought every product in a category: Find the customers who have bought every product in the Stationery category.