SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 38 of 250

Hard

Give root categories readable labels

Your task

Return category ID and display_name. A child’s label should be its parent’s name, then ' / ', then its own name. A root’s label should be its own name. Sort by category ID.

When concatenation meets NULL, the whole result can become NULL. Choose a fallback for the missing parent.

Sample data

Table:categories
idnameparent_id
1MusicNULL
2Jazz1
3Indie1
4Bebop2
5Cool Jazz2
6MerchNULL
7Shirts6

Expected output

iddisplay_name
1Music
2Music / Jazz
3Music / Indie
4Jazz / Bebop
5Jazz / Cool Jazz
6Merch
7Merch / Shirts
Show a hint

Join categories to themselves with a LEFT JOIN and use COALESCE on the full concatenation.

Expressions and missing values guide

Calculate a value for each row

Expressions can combine columns, constants, and functions. This query estimates how much stock is worth at the listed price. AS gives the calculation a name in the result.

SELECT title, price * stock AS stock_value
FROM albums;

Build a category from conditions

CASE tests branches from top to bottom. Put the most specific test first: zero would also satisfy stock < 5. ELSE catches the remaining rows.

SELECT title,
       CASE
         WHEN stock = 0 THEN 'out of stock'
         WHEN stock < 5 THEN 'low stock'
         ELSE 'stocked'
       END AS availability
FROM albums;

Account for missing values

NULL represents an unknown or missing value. Compare it with IS NULL rather than equality. NULLIF can also turn a known sentinel into NULL. AVG ignores NULL, so these two averages use different sets of albums.

SELECT AVG(stock) AS all_albums,
       AVG(NULLIF(stock, 0)) AS in_stock_only
FROM albums;
SQLiteCtrl / ⌘ + Enter

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

Results

Loading SQLite…