SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 9 of 250

Moderate

Keep unmatched rows

Your task

Return the titles of albums that have never been sold.

A LEFT JOIN keeps every row from the table on the left. When there is no match, the columns from the right table contain NULL. Use IS NULL to find those rows.

Sample data

Choose a table
idtitlegenrepricestock
1Night BusJazz187
2Static BloomIndie224
3Soft CircuitElectronic240
4Blue HourJazz163
5CoastlineIndie198
6Low TideAmbient212

Expected output

title
Soft Circuit
Low Tide
Show a hint

LEFT JOIN sales to albums, then use WHERE sales.id IS NULL.

SQL fundamentals guide

Read a table

A table has named columns and one row per record. In albums, id identifies a row, title describes it, and stock is how many copies the shop has. Start by selecting just the columns you need. The result below shows titles beside their current stock.

SELECT title, stock
FROM albums;

Filter, then sort

WHERE keeps rows that satisfy its condition. The next query only considers albums in stock. ORDER BY applies to those matches, and LIMIT returns the first three. The second sort key makes the result predictable if prices tie.

SELECT title, price
FROM albums
WHERE stock > 0
ORDER BY price DESC, id
LIMIT 3;

Think about what one row represents

One album can have several sale records. Joining albums to sales makes each result row represent one sale, so an album title may repeat. Grouping by album ID brings the result back to one row per album that has sold.

SELECT a.title, SUM(s.quantity) AS copies_sold
FROM albums AS a
JOIN sales AS s ON s.album_id = a.id
GROUP BY 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…