Round a percentage discount
Your task
Return title and sale_price for every album after a 12 percent discount. Round sale_price to two decimal places and sort by album ID.
Apply the discount to the full price, then round the result once.
Sample data
albums| id | title | genre | price | stock |
|---|---|---|---|---|
| 1 | Night Bus | Jazz | 18 | 7 |
| 2 | Static Bloom | Indie | 22 | 4 |
| 3 | Soft Circuit | Electronic | 24 | 0 |
| 4 | Blue Hour | Jazz | 16 | 3 |
| 5 | Coastline | Indie | 19 | 8 |
| 6 | Low Tide | Ambient | 21 | 2 |
Expected output
| title | sale_price |
|---|---|
| Night Bus | 15.84 |
| Static Bloom | 19.36 |
| Soft Circuit | 21.12 |
| Blue Hour | 14.08 |
| Coastline | 16.72 |
| Low Tide | 18.48 |
Show a hint
Multiply the price by 0.88 and pass the result to ROUND.
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;Worked solution
SELECT title, ROUND(price * 0.88, 2) AS sale_price FROM albums ORDER BY id;Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.
Results
Loading SQLite…
