Rounding and dividing

SQL · Unit 4: NULL and expressions

Full plan
SQL
10 exercises

What does the Keyboard cost with the 16% tax? The calculation is 45.5 * 1.16, and look at what it gives. That's not your mistake, or SQL's. Here decimals are stored in binary, and numbers like 0.1 don't fit exactly there. PostgreSQL and MySQL also have an exact decimal type, and that's the one used for storing money.

Until today the course's calculations came out clean because we picked them that way. ROUND rounds to as many decimals as you ask for, and with it, any calculation is fair game.

SELECT 45.5 * 1.16 AS messy,
  ROUND(45.5 * 1.16, 2) AS clean;

Returns

messy	clean
52.779999999999994	52.78

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

1. Predict the output
The second value in ROUND says how many decimals to keep. Leave it out and it keeps none. What does this query return?
2. Predict the output
For the products with 7 units or fewer left, the store splits what it has between its Denver shop and the online store, half and half. There are 5 Monitors, 3 Laptops, and 7 Tablets. What does this query return?
3. Multiple choice
The Monitor costs 189 and there are 5 left. price / 2 gives 94.5, decimal and all, and stock / 2 gives 2. Why does one division cut off the decimals when the other doesn't?
4. Find the bug
How much of a box of 10 does each order from Luke, customer 2, fill? His orders are for 5, 2, and 1 units, so the answer should be 0.5, 0.2, and 0.1. You get three zeros. Which line has the problem?
5. Complete the code
Devices can be paid for in three equal payments. Fill in the blank to see how much each payment is, with two decimals.
6. Put the lines in order
What percentage of its 30-unit shelf does each product in stock fill? Put the query in order, from the fullest shelf to the emptiest. One line doesn't belong.
7. Predict the output
The Mouse costs 19.9. What does this query return here?
8. Put the lines in order
Which four in-stock products earn the most points? Put the query in order. One line doesn't belong.
9. Find the case that fails
The Monitor costs 189. This query should give the points earned for buying it, one per whole unit of the price, not counting the cents. Here that's done with CAST, but whoever wrote it used ROUND. Which of these prices makes it give away an extra point?
10. Write the code
The price list the store couldn't produce until today. For each product, show three columns in this order: its name, its price, and the price with the 16% tax, rounded to two decimals.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →