Ten levels of real, working SQL — starting with a single table and ending
with five tables joined together, subqueries, CTEs and window functions. Every query on this
page runs as-is against the classicmodels sample database, in
DBeaver or the browser SQL Editor.
The best way to learn SQL is to read a lot of it. Each level below adds exactly one new idea
and shows the statement that uses it — no toy examples, no foo and bar,
just the kind of query you'd actually write against this data.
Same data, same answers — but on SQL Server the tables live in a schema that's also
called classicmodels, so you must write classicmodels.products rather
than bare products. And row limits differ: MySQL puts LIMIT 10 at the
end, SQL Server puts SELECT TOP 10 at the start. Flip the switch above and
every snippet adjusts.
Every SQL statement starts here: pick columns, pick a table, put them in order. Read it top-to-bottom and it's almost English.
The 10 most expensive products to buy.
Products running low on stock.
Same single table, but instead of listing rows you collapse them into numbers. This is where SQL starts doing work a spreadsheet would struggle with.
Three numbers describing the whole catalogue.
One row per product line.
Only countries with 5+ customers.
New to aggregates? There's a full explainer with accounting examples →
The first real join. Each product belongs to a product line, so we follow that foreign key to bring both tables together.
How many products in each product line?
Now a genuine business question: which products actually made money on shipped orders? No single table can answer that — the money is in one table, the product names in another, the order status in a third.
Top 10 products by revenue, shipped orders only.
The full chain, and the payoff. Revenue lives on the order line; the sales rep is four hops away. In a spreadsheet this is a morning of VLOOKUPs. In SQL it's one statement.
Revenue by sales rep, with their office.
An INNER JOIN only keeps rows that match. A LEFT JOIN keeps every row on the left even when there's no match — which lets you ask the opposite question: which customers have never ordered?
Customers who have never placed an order.
Managers are employees too, so employees.reportsTo points back at the same table. Join it to itself with two different aliases and you get an org chart.
Every employee and the manager they report to.
e, m) stop being a convenience and become essential — they're the only thing telling the two copies apart.Sometimes you need an answer before you can ask the real question — like the average price, or the list of customers who cancelled. Subqueries let you nest one inside the other.
Products priced above the catalogue average.
Customers who have cancelled an order.
Subqueries get unreadable fast when they nest. A CTE lifts the inner query out, gives it a name, and lets you use it like a table. Same result as a subquery — far kinder to the next person who reads it.
Top 10 products by revenue — same answer, readable.
GROUP BY collapses rows. A window function does the maths without collapsing anything — so you keep every row AND get the ranking or running total beside it. This is the point where SQL stops being a spreadsheet and starts being a language.
Rank products by price *within* each product line.
Daily revenue and a running cumulative total.