Q1Which of the following is the correct result of SELECT ROUND(3.84);?
Run a query to see results
Functions ③ — Math Functions (ROUND / FLOOR / CEILING) and COALESCE
The third of three SQL function articles. Cover the math functions ROUND / FLOOR / CEILING / POWER and COALESCE — which returns the first non-NULL value — using CSV-loaded staff and test-score datasets.
Data used in this article — staff and test_score
The last function article covers math functions and COALESCE. With the math functions, we'll handle rounding, flooring, ceiling, and exponentiation on numeric values like the salary column. With COALESCE, we'll cover replacing NULL with another value.
This article uses two CSVs. The first half (math functions) uses the same staff table as before (10 employees). The second half (COALESCE) brings in a new test_score table (8 students × 3 test attempts, with NULLs). In test_score, the score_1 / score_2 / score_3 columns have NULLs where a student was absent and didn't take that test — perfect for a realistic scenario like "use the score from the first test the student was able to take" to learn how COALESCE works.
Before diving into the exercises, let's check the column definitions and sample data of both tables — staff and test_score.
① Run PRAGMA table_info(staff); and PRAGMA table_info(test_score); to see the columns of both.
② Run SELECT * FROM staff LIMIT 5; and SELECT * FROM test_score LIMIT 5; to preview the first 5 rows.
Math functions — ROUND / FLOOR / CEILING / POWER
Here are the 4 main functions for shaping numeric values:
- ROUND(x, n): rounds to n decimal places. Omit n and it rounds to an integer
- FLOOR(x): floor (rounds toward negative infinity)
- CEILING(x) or CEIL(x): ceiling (rounds toward positive infinity)
- POWER(x, y): exponentiation (x to the power of y)
-- 1) ROUND: rounding
SELECT ROUND(3.14); -- 3 (integer)
SELECT ROUND(3.14, 1); -- 3.1 (1 decimal place)
-- 2) FLOOR: floor
SELECT FLOOR(3.84); -- 3
-- Note: CEILING runs on MySQL / PostgreSQL / Oracle / SQL Server — SELECT CEILING(3.14); → 4
-- 3) POWER: exponentiation
SELECT POWER(3, 4); -- 81 (3 to the 4th)
SELECT POWER(2, 10); -- 1024
-- 4) On a column — round monthly salary
SELECT name, salary, ROUND(salary / 12.0) AS monthly
FROM staff;
| Input | ROUND (rounding) | FLOOR (floor) | CEILING (ceiling / not available in this course's console) |
|---|---|---|---|
| 3.14 | 3 | 3 | 4 |
| 3.84 | 4 | 3 | 4 |
| 3.50 | 4 | 3 | 4 |
| -3.14 | -3 | -4 (toward negative) | -3 |
Imagine a request: "divide annual salary by 12 to get monthly salary, displayed as a rounded integer." (If you run it correctly, an explanation will appear.)
① From the staff table, pull name, salary, and `ROUND(salary / 12.0)` aliased as `monthly` — 3 columns total.
② Order by monthly descending.
③ Verify that the result has 10 rows and starts with Frank Tanaka 7,200,000 / 600,000.
Run a query to see results
Imagine a request: "estimate the annual salary 3 years from now, assuming a 10% raise every year." Multiplying by the same factor multiple times can be written in one expression with `POWER(x, y)` (x to the power of y). Three consecutive 1.1× raises become salary * POWER(1.1, 3), which is the same as salary * 1.331.
① From the staff table, pull name, salary, and *`ROUND(salary POWER(1.1, 3)) aliased as salary_after_3y`** — 3 columns total (ROUND keeps the result as an integer).
② Order by salary_after_3y descending and keep only the top 5 rows.
③ Verify that the result is 5 rows, starting with Frank Tanaka 7,200,000 / 9,583,200 and David Sato 6,800,000 / 9,050,800 as row 2.
Run a query to see results
Imagine a request: "in the monthly salary display, check how rounding vs. flooring change the result." Place the 2 rounding functions side by side on the same expression to see the behavior difference at a glance.
① From the staff table, pull name, salary, `ROUND(salary / 12.0)` as `round_monthly`, and `FLOOR(salary / 12.0)` as `floor_monthly` — 4 columns total.
② Order by salary ascending and keep only the top 5 rows.
③ Verify that the result is 5 rows in the order Emi / Carol / Iris / Alice / Bob. For Alice (whose salary divides evenly by 12), round_monthly and floor_monthly should be equal; for the others they should differ (the decimal portion changes the behavior).
Run a query to see results
COALESCE — return the first non-NULL value
`COALESCE(value1, value2, value3, ...)` evaluates its arguments left to right and returns the first one that isn't NULL. If they're all NULL, it returns NULL. Use it whenever you want a NULL fallback: "use the main column if it has a value, otherwise the backup column, otherwise a default."
This article uses the test_score table. For each student_id, score_1 / score_2 / score_3 hold the 1st, 2nd, and 3rd test scores, with NULL where the student was absent. Passing (score_1, score_2, score_3, 0) to COALESCE gives you the score from the first test the student was able to take, treated as the "final score." Adding 0 at the end means "if they missed all three, treat it as 0."
-- 1) Check with literal values
SELECT COALESCE(NULL, NULL, 'C'); -- 'C'
SELECT COALESCE(NULL, NULL, NULL); -- NULL
-- 2) Pull the final score from the test_score table
SELECT student_id, name, score_1, score_2, score_3,
COALESCE(score_1, score_2, score_3) AS first_score
FROM test_score;
-- 3) Use a default — "absent across the board = 0 points"
SELECT student_id, name,
COALESCE(score_1, score_2, score_3, 0) AS final_score
FROM test_score;
The 2-argument version can be written as IFNULL
For the simpler 2-argument case — "if NULL, use a default; otherwise, the original value" — you can also write IFNULL(column, default) (supported in both this course's console and MySQL). COALESCE(email, 'not registered') and IFNULL(email, 'not registered') are exactly the same.
When you need 3 or more arguments, use COALESCE. Since COALESCE is the SQL standard, the same form works on PostgreSQL, Oracle, SQL Server, etc. — it's the more portable choice.
Imagine a request: "we ran 3 tests, but there were absences — use the score from the first test each student was able to take as the final score."
① From the test_score table, pull student_id, name, score_1, score_2, score_3, and `COALESCE(score_1, score_2, score_3)` aliased as `first_score` — 6 columns total.
② Verify that the result is 8 rows: Alice 85 (score_1 is non-NULL), Bob 78 (score_1 is NULL so score_2 is picked), Carol 95 (score_1 and score_2 are NULL so score_3), Dave NULL (all NULL).
Run a query to see results
Imagine a request: "give every student a final score. Treat consistently absent students as 0, and award the top 3 by final score."
① From the test_score table, pull student_id, name, and `COALESCE(score_1, score_2, score_3, 0)` aliased as `final_score` — 3 columns total.
② Order by final_score descending and keep only the top 3 rows.
③ Verify that the result is 3 rows: Carol 95 / Frank 90 / Grace 88.
Run a query to see results
QUIZ
Knowledge Check
Answer each question one by one.
Q2What does SELECT FLOOR(3.84), CEILING(3.14); return?
Q3What does SELECT COALESCE(NULL, NULL, 'C', 'D'); return?