SQL, lesson 8
A subquery is a complete SELECT in parentheses, used inside another statement. SQLite runs
it and uses its answer as a value, a list, or a table. It lets you ask questions in two steps
without copying numbers by hand.
When a subquery returns one row with one column, you can use it anywhere a value fits:
You could run the inner query, note the number, and type it in, but then your query breaks as soon as the prices change. The subquery always uses the current data.
With IN, a subquery that returns one column becomes a list to check against:
NOT IN flips it, but be careful: if the list contains a NULL, NOT IN matches nothing.
A subquery in FROM acts as a temporary table. It must have an alias:
2 exercises to try what you just read.
CASE picks a value based on conditions, checked from top to bottom. The first true WHEN
wins, and ELSE covers everything else:
You can group by a CASE result too, which turns raw numbers into neat categories.
|| joins text, and printf formats numbers. printf('%.2f', 3.5) gives 3.50, which is how
money usually looks on a report:
A report query often uses everything from this course at once: a join to bring tables together,
WHERE to pick rows, GROUP BY and aggregates to summarise, CASE to label, and ORDER BY to
present. Build it one clause at a time and run it after each step. When something looks wrong,
remove the last clause you added and look at the rows again.
The table menu has the columns dish and price:
Print the dish and price of every dish that costs more than the average price, from
most to least expensive. Use a subquery so your query would still work if prices changed.
Expected output:
A gym keeps two tables. members has the columns id, name and plan; visits has
member_id and minutes, one row per visit:
Write a report with one line per member, sorted by total minutes from most to least (break ties by name). Each line shows:
name,regular for 3 or more visits, occasional for 1 or 2 visits, inactive for none.Expected output:
-- Dishes that cost more than the average dish
SELECT dish, price
FROM menu
WHERE price > (SELECT avg(price) FROM menu);-- Customers who placed at least one order over 50
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 50);SELECT avg(n) FROM (SELECT count(*) AS n FROM orders GROUP BY customer_id) AS per_customer;SELECT dish,
CASE
WHEN price < 5 THEN 'budget'
WHEN price < 10 THEN 'standard'
ELSE 'premium'
END AS band
FROM menu;SELECT dish || ': ' || printf('%.2f', price) FROM menu;dish | price
Soup | 4.5
Burger | 9.0
Salad | 6.5
Steak | 18.0
Pasta | 8.5
Fries | 3.5Steak|18.0
Burger|9.0
Pasta|8.5members visits
id | name | plan member_id | minutes
1 | Aisha | gold 1 | 60
2 | Bruno | basic 1 | 45
3 | Chen | gold 2 | 30
4 | Dana | basic 3 | 90
1 | 50
3 | 40Aisha|3|155|regular
Chen|2|130|occasional
Bruno|1|30|occasional
Dana|0|0|inactive