SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 21 of 250

Easy

Apply a discount

Your task

Return each title and its price after a 10 percent discount, rounded to two decimal places.

Arithmetic in a result leaves the stored price intact. ROUND can control the number of decimal places.

Sample data

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

Expected output

titleROUND(price * 0.90, 2)
Night Bus16.2
Static Bloom19.8
Soft Circuit21.6
Blue Hour14.4
Coastline17.1
Low Tide18.9
Show a hint

Use ROUND(price * 0.90, 2).

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…