Learn/SQL/8. Subqueries and a small report
Lesson 8 of 8
Sign in

SQL, lesson 8

Subqueries and a small report

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.

A single value

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.

A list of values

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 table

A subquery in FROM acts as a temporary table. It must have an alias:

2 exercises to try what you just read.

Exercise 1

Above average

Press Ctrl Enter or Check to test your code.

Exercise 2

Gym membership report

Press Ctrl Enter or Check to test your code.

Labels with CASE

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.

Building text

|| joins text, and printf formats numbers. printf('%.2f', 3.5) gives 3.50, which is how money usually looks on a report:

Putting it together

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.

Further reading

  • SQLite: SELECT and subqueries
  • SQLite: expressions, CASE and IN
  • SQLite: printf() and other core functions

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:

  • the member's name,
  • their number of visits,
  • their total minutes (0 when they never came),
  • a label: 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.5
Steak|18.0
Burger|9.0
Pasta|8.5
members                   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         | 40
Aisha|3|155|regular
Chen|2|130|occasional
Bruno|1|30|occasional
Dana|0|0|inactive