SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 88 of 250

Easy

Find albums below average stock

Your task

Return title and stock for albums strictly below the average stock across all albums. Sort by stock, then title.

Compute the average over the whole table before comparing individual rows.

Sample data

Table:albums
idtitlegenrepricestock
1Night BusJazz187
2Static BloomIndie224
3Soft CircuitElectronic240
4Blue HourJazz163
5CoastlineIndie198
6Low TideAmbient212

Expected output

titlestock
Soft Circuit0
Low Tide2
Blue Hour3
Show a hint

Use a scalar subquery to compute AVG(stock).

Subqueries and CTEs guide

Use a query as a value

An aggregate without GROUP BY produces one row. A single-column subquery can therefore provide a scalar value for a comparison. Here each price is compared with an average calculated across the whole table.

SELECT title, price
FROM albums
WHERE price < (SELECT AVG(price) FROM albums);

Ask whether a related row exists

An EXISTS subquery only needs to produce one row to be true. It refers to a.id from the outer query, so the test is evaluated for the current album. The expression in the inner SELECT is immaterial to the existence test.

SELECT a.title
FROM albums AS a
WHERE EXISTS (
  SELECT 1
  FROM sales AS s
  WHERE s.album_id = a.id AND s.quantity > 1
);

Name intermediate results with WITH

A common table expression gives an intermediate query a name for a single statement. Here the CTE summarizes sales before joining them to albums. Read the CTE on its own first, then read the final SELECT to see how the summary is used.

WITH totals AS (
  SELECT album_id, SUM(quantity) AS copies
  FROM sales
  GROUP BY album_id
)
SELECT a.title, COALESCE(t.copies, 0) AS copies
FROM albums AS a
LEFT JOIN totals AS t ON t.album_id = a.id;
SQLiteCtrl / ⌘ + Enter

Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.

Results

Loading SQLite…