SQL exercises with solutions

SELECT name, price
FROM product
WHERE price < 2
ORDER BY price, id;

The id after price breaks ties. There are none here, but a second key costs nothing and keeps the order fixed if a new product ever shares a price.

SELECT name, town, joined
FROM customer
WHERE joined < '2024-06-01'
ORDER BY joined DESC;

"Before June" means before the first of June, so the comparison is a strict < against '2024-06-01', written in the column's own ISO shape. joined <= '2024-05-31' gives the same rows here.

SELECT id, name
FROM product
WHERE name ILIKE '%croissant%'
ORDER BY id;

LIKE '%croissant%' would miss the plain Croissant, whose capital C does not match a small c: LIKE is case-sensitive (see LIKE and ILIKE). The wildcard at the front is what finds the almond one.

SELECT name, email
FROM customer
WHERE email <> lower(trim(email))
ORDER BY id;

trim removes the spaces at both ends and lower the capitals, so a tidy email equals its own cleaned copy and an untidy one does not. A customer with no email has a NULL, and a comparison with NULL is never true, so those rows drop out without being asked: WHERE email IS NOT NULL would change nothing. Three LIKE tests (email <> lower(email) OR email LIKE ' %' OR email LIKE '% ') find the same six.

SELECT category, COUNT(*) AS products, MAX(price) AS dearest
FROM product
GROUP BY category
ORDER BY category;

Asking for the name of the dearest product is a different and harder question: name is neither grouped nor aggregated, so it cannot simply be added to this SELECT. Exercise 17 shows the pattern, with a window function.

SELECT c.town, COUNT(*) AS orders
FROM orders AS o
JOIN customer AS c ON c.id = o.customer_id
GROUP BY c.town
ORDER BY orders DESC, c.town;

The town lives in customer and the orders in orders, so the join comes first and the grouping after it. Each row of the join is one order, which is why COUNT(*) counts orders.

SELECT o.day, ROUND(SUM(oi.quantity * oi.unit_price), 2) AS takings
FROM orders AS o
JOIN order_item AS oi ON oi.order_id = o.id
WHERE o.day BETWEEN '2026-03-23' AND '2026-03-29'
GROUP BY o.day
ORDER BY o.day;

The money of a line is its quantity times unit_price, the price paid on that line, so product is not needed. Friday 27 March is missing because the bakery is closed on Fridays and nobody ordered: a group needs rows. The CTE page builds a calendar that shows such a day with zero, and exercise 19 uses one.

SELECT DISTINCT p.name, oi.unit_price AS paid, p.price AS current_price
FROM order_item AS oi
JOIN product AS p ON p.id = oi.product_id
WHERE oi.unit_price <> p.price
ORDER BY p.name;

Three products went up on Monday 16 March. product.price holds only today's price; the line keeps what the customer really paid, which is why takings are computed from unit_price: the catalogue price would overstate the first two weeks. DISTINCT folds the many lines sold at the old price into one row per product.

SELECT substr(time, 1, 2) AS hour, COUNT(*) AS orders
FROM orders
GROUP BY hour
HAVING COUNT(*) >= 25
ORDER BY orders DESC, hour;

The hour is the first two characters of the time. Keeping it as text is fine for grouping and sorting, because every hour has two digits; a CAST would only be needed for arithmetic. Two hours share a count, which is why the question says how to order them, and why hour follows orders DESC in the ORDER BY.

SELECT newcomer.name, newcomer.town, referrer.name AS referrer, referrer.town AS referrer_town
FROM customer AS newcomer
JOIN customer AS referrer ON referrer.id = newcomer.referred_by
WHERE newcomer.town <> referrer.town
  AND newcomer.joined >= '2026-01-01'
ORDER BY newcomer.id;

The same table plays two parts under two aliases. The inner join leaves out the customers who were not referred at all, which is right here: they have no referrer whose town could differ.

SELECT s.name, m.name AS manager, COUNT(o.id) AS served
FROM staff AS s
LEFT JOIN staff AS m ON m.id = s.manager_id
LEFT JOIN orders AS o ON o.staff_id = s.id
GROUP BY s.id, s.name, m.name
ORDER BY s.id;

Both joins have to be LEFT: an inner join to the manager would drop the owner, and an inner join to the orders would drop the baker who never serves. COUNT(o.id) counts only the rows where an order was found, so the baker's row counts 0, where COUNT(*) would say 1.

SELECT c.town,
       SUM(CASE WHEN o.time < '09:00' THEN 1 ELSE 0 END) AS before_nine,
       SUM(CASE WHEN o.time >= '09:00' THEN 1 ELSE 0 END) AS later
FROM customer AS c
JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.town
ORDER BY c.town;

Each CASE turns an order into a 1 or a 0, and the SUM counts the ones. Writing the second condition as >= '09:00' rather than > '09:00' matters: three orders were placed at exactly nine, and they belong in the later column, each counted once.

WITH category_average AS (
  SELECT category, AVG(price) AS average
  FROM product
  GROUP BY category
)
SELECT p.name, p.category, p.price, ROUND(a.average, 2) AS average
FROM product AS p
JOIN category_average AS a ON a.category = p.category
WHERE p.price >= a.average + 0.5
ORDER BY p.category, p.price DESC, p.id;

A correlated subquery in WHERE finds the same products, but showing the average as well would mean writing it twice. A CTE computes each category's average once and joins it like a table.

SELECT name
FROM product
EXCEPT
SELECT p.name
FROM product AS p
JOIN order_item AS oi ON oi.product_id = p.id
JOIN orders AS o ON o.id = oi.order_id
WHERE o.day IN ('2026-03-21', '2026-03-22')
ORDER BY name;

Every product, minus the ones sold that weekend. The first half reads product alone, so the pistachio financier, which nobody bought all month, is in the answer too; a join on its own could never show it. Both days are named: o.day >= '2026-03-21' would also take in the last weekend, and the coffee eclair, sold then, would drop out. Comparing names works because no two products share one; with a column that can repeat, compare the ids. Exercise 15 asks the same kind of question with NOT EXISTS.

SELECT c.name
FROM customer AS c
WHERE NOT EXISTS (SELECT 1
                  FROM orders AS o
                  JOIN order_item AS oi ON oi.order_id = o.id
                  JOIN product AS p ON p.id = oi.product_id
                  WHERE o.customer_id = c.id
                    AND p.category = 'bread')
ORDER BY c.id;

The tempting answer joins everything and filters WHERE p.category <> 'bread', but that lists the customers who bought something that is not bread, which includes bread buyers who also took a croissant, and drops Hugo Silva and the others who bought nothing. The question is about the absence of a row, which is what NOT EXISTS asks.

WITH sold AS (
  SELECT o.day, SUM(oi.quantity * oi.unit_price) AS sold
  FROM orders AS o
  JOIN order_item AS oi ON oi.order_id = o.id
  GROUP BY o.day
),
refunded AS (
  SELECT day, SUM(amount) AS refunded
  FROM refund
  GROUP BY day
)
SELECT s.day,
       ROUND(s.sold, 2) AS sold,
       ROUND(COALESCE(r.refunded, 0), 2) AS refunded,
       ROUND(s.sold - COALESCE(r.refunded, 0), 2) AS kept
FROM sold AS s
LEFT JOIN refunded AS r ON r.day = s.day
WHERE s.day BETWEEN '2026-03-16' AND '2026-03-22'
ORDER BY s.day;

Joining the lines and the refunds in one query multiplies them: every refund of a day is repeated on every line of that day, and its sum comes out many times too big. Adding up each table on its own first, one row per day in each CTE, and only then joining the two keeps both totals right. The join is a LEFT JOIN because Sunday 22 March had no refund, and COALESCE turns its missing total into 0, so the subtraction does not give NULL.

WITH sold AS (
  SELECT p.category, p.name, SUM(oi.quantity) AS pieces
  FROM product AS p
  JOIN order_item AS oi ON oi.product_id = p.id
  GROUP BY p.id, p.category, p.name
),
ranked AS (
  SELECT category, name, pieces,
         RANK() OVER (PARTITION BY category ORDER BY pieces DESC) AS rk
  FROM sold
)
SELECT category, name, pieces
FROM ranked
WHERE rk = 1
ORDER BY category, name;

Three steps: add up the pieces per product, rank the products within each category, keep the first. RANK gives tied products the same rank 1, so the two pastries that sold the same number both show; ROW_NUMBER would keep just one, chosen arbitrarily. The filter has to sit outside the CTE, because a window function cannot appear in WHERE.

WITH visits AS (
  SELECT id, customer_id, day, time,
         LAG(day) OVER (PARTITION BY customer_id ORDER BY day, time) AS previous_day
  FROM orders
)
SELECT id, customer_id, previous_day, day
FROM visits
WHERE day = '2026-03-04'
  AND previous_day IS NOT NULL
ORDER BY customer_id, time;

A customer's first order has no previous row, so LAG returns NULL for it, and IS NOT NULL drops exactly those orders. The day is chosen outside the CTE, after LAG has seen every order: filtered inside it, the window would see only that Wednesday, and an order whose previous visit was on the Monday would look like a first. Ordering the window by day, time rather than day alone keeps two orders on the same day in their real order.

WITH RECURSIVE calendar(n, day) AS (
  SELECT 2, '2026-03-02'
  UNION ALL
  SELECT n + 1, '2026-03-' || RIGHT('0' || (n + 1), 2)
  FROM calendar
  WHERE n < 29
)
SELECT day
FROM calendar
WHERE day NOT IN (
  SELECT o.day
  FROM orders AS o
  JOIN customer AS c ON c.id = o.customer_id
  WHERE c.town = 'Hillcrest'
)
ORDER BY day;

A missing day has no row anywhere, so no query on orders alone can list it. The recursive CTE writes out every date of the period, one row per round, and NOT IN keeps those that no Hillcrest order carries: the four Fridays, when the shop is closed, and two more days, Tuesday 3 and Saturday 7 March, that only the calendar reveals. NOT IN is safe here because orders.day is never NULL; a LEFT JOIN with WHERE o.id IS NULL gives the same rows.

WITH RECURSIVE chain(root_id, id) AS (
  SELECT id, id FROM customer WHERE referred_by IS NULL
  UNION ALL
  SELECT ch.root_id, c.id
  FROM chain AS ch
  JOIN customer AS c ON c.referred_by = ch.id
)
SELECT r.name, COUNT(*) - 1 AS brought
FROM chain AS ch
JOIN customer AS r ON r.id = ch.root_id
GROUP BY r.id, r.name
ORDER BY brought DESC, r.id;

The anchor starts one chain per walk-in, carrying the walk-in's id as root_id; each round adds the customers referred by the newest members and passes the same root_id down. Every chain contains its walk-in too, hence the - 1. Because the walk-ins start their own chains, a walk-in who brought nobody still appears, with 0.