Amba's Tools
Home › Learn › SQL

SQL

26 lessons, 5 modules, SQL running in your browser.

Start the course

SQL is the language of databases, and nearly every job that touches data uses it. This course runs a real database engine (SQLite, compiled to WebAssembly) inside your browser tab with a small shop loaded into it: customers, products, orders, and employees. You write a query, press Run, see the rows, and the checker compares them with the expected result.

The lessons go from reading rows to shaping results, summarizing with aggregates and GROUP BY, combining tables with joins and subqueries, and changing data with INSERT, UPDATE, DELETE, and CREATE TABLE. The course ends in a project, the order report a shop manager reads, that is not started from nothing: one query in each module is saved as a view, and the report joins your views. The database is rebuilt before every run, so experiment freely. Twenty-six lessons, about eight minutes each; progress stays in this browser.

What you will learn

The final project

The course ends with Project: the order report: the order report, assembled from views you wrote along the way. 5 of the lessons build a part of it, saved in this browser as you go, so the project is assembled from your own work rather than started from nothing.

  1. top_products (ORDER BY and LIMIT) is the view of the three most expensive products, which the report flags as featured.
  2. product_values (Expressions and aliases) is the view of each product with the value of its remaining stock.
  3. customer_orders (GROUP BY) is the view of how many orders each customer has placed, and how many items.
  4. order_lines (Joining three tables) is the view of every order with its customer and product spelled out.
  5. availability (CASE expressions) is the view that labels each product out, low, or ok.

Lessons

Reading rows

  1. SELECT everythingRead every row and column of a table.
  2. Choosing columnsAsk for only the columns you need, in the order you want them.
  3. WHEREKeep only the rows that match a condition.
  4. AND, OR, NOTCombine conditions.
  5. ORDER BY and LIMITSort the result and take the first few rows.part: top_products

Shaping results

  1. DISTINCTEach value once.
  2. LIKEMatch text patterns.
  3. IN and BETWEENMatch a list of values, or a range.
  4. NULLMissing values and how to test for them.
  5. Expressions and aliasesCalculate new columns and name them.part: product_values

Aggregates

  1. COUNTHow many rows?
  2. SUM, AVG, MIN, MAXTotals, averages, and extremes.
  3. GROUP BYOne row per group, with an aggregate for each.part: customer_orders
  4. HAVINGFilter groups after aggregating.
  5. ROUND and other functionsTidy numbers and text in the result.

Joins

  1. INNER JOINCombine rows from two tables that belong together.
  2. Joining three tablesChain joins to follow several relationships.part: order_lines
  3. LEFT JOINKeep rows that have no match.
  4. Self joinJoin a table to itself for hierarchies.
  5. SubqueriesUse one query's answer inside another.

Changing data, and the report

  1. INSERTAdd rows.
  2. UPDATEChange existing rows.
  3. DELETERemove rows.
  4. CREATE TABLEDefine a new table and put a row in it.
  5. CASE expressionsCompute a label per row with conditions.part: availability
  6. Project: the order reportAssemble the views you built along the way into the report the shop manager reads.final project