SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 35 of 250

Moderate

Find prices closest to a target

Your task

Return title, price, and the absolute distance from price 20 as distance. Sort by distance ascending, then title.

An absolute difference measures distance even when a price is below the target.

Sample data

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

Expected output

titlepricedistance
Coastline191
Low Tide211
Night Bus182
Static Bloom222
Blue Hour164
Soft Circuit244
Show a hint

Apply ABS to price minus 20.

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…