ROUND, CEIL, FLOOR in SQL - Step-by-Step Execution

Concept Flow - ROUND, CEIL, FLOOR

Start with a number

↓

ROUND

↓

Return result

Start with a number, pick one of the functions ROUND, CEIL, or FLOOR, then get the rounded result accordingly.

Execution Sample

SQL

SELECT ROUND(4.3), CEIL(4.3), FLOOR(4.7);

This query rounds 4.3 to nearest integer, rounds 4.3 up, and rounds 4.7 down.

Execution Table

Step	Function	Input	Operation	Result
1	ROUND	4.3	Round to nearest integer	4
2	CEIL	4.3	Round up to next integer	5
3	FLOOR	4.7	Round down to previous integer	4
4	END		All functions executed	Results: 4, 5, 4

💡 All functions processed, results returned.

Variable Tracker

Variable	Start	After ROUND	After CEIL	After FLOOR	Final
Input Number	4.3 / 4.3 / 4.7	4.3	4.3	4.7	unchanged
ROUND Result	N/A	4	N/A	N/A	4
CEIL Result	N/A	N/A	5	N/A	5
FLOOR Result	N/A	N/A	N/A	4	4

Key Moments - 2 Insights

Why does ROUND(4.3) give 4 but CEIL(4.3) gives 5?

ROUND rounds to the nearest integer, so 4.3 becomes 4. CEIL always rounds up to the next integer, so 4.3 becomes 5. See execution_table rows 1 and 2.

Why does FLOOR(4.7) give 4 instead of 5?

FLOOR always rounds down to the previous integer, so 4.7 becomes 4. See execution_table row 3.

Visual Quiz - 3 Questions

Look at the execution table, what is the result of CEIL(4.3)?

A 4

B 5

C 3

D 6

💡 Hint

Check execution_table row 2 under Result column.

At which step does the function round down?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Look for FLOOR function in execution_table.

If input was 4.5, what would ROUND(4.5) return?

A 6

B 4

C 5

D 3

💡 Hint

ROUND rounds to nearest integer; 4.5 rounds up to 5.

Concept Snapshot

ROUND(number): rounds to nearest integer.
CEIL(number): rounds up to next integer.
FLOOR(number): rounds down to previous integer.
Use these to control decimal rounding in SQL queries.

Full Transcript

This lesson shows how SQL functions ROUND, CEIL, and FLOOR work. Starting with a number, ROUND rounds it to the nearest whole number. CEIL always rounds up to the next whole number, even if the decimal is small. FLOOR always rounds down to the previous whole number. For example, ROUND(4.3) is 4, CEIL(4.3) is 5, and FLOOR(4.7) is 4. These functions help control how decimals are handled in your data queries.

Practice

1. What does the SQL function CEIL(4.3) return?

easy

A. 5

B. 4

C. 4.3

D. 3

Solution

1. Step 1: Understand CEIL function behavior

   The CEIL function rounds a number up to the nearest integer.

2. Step 2: Apply CEIL to 4.3

   Since 4.3 is between 4 and 5, CEIL rounds it up to 5.

3. Final Answer:

   5 -> Option A

4. Quick Check:

   CEIL(4.3) = 5 [OK]

Hint: CEIL always rounds up to next integer [OK]

Common Mistakes:

• Confusing CEIL with FLOOR
• Thinking CEIL rounds to nearest integer
• Assuming CEIL returns decimal part

2. Which of the following is the correct syntax to round a number to 2 decimal places in SQL?

easy

A. ROUND(123.456.2)

B. ROUND(123.456, 2)

C. ROUND(123.456 TO 2)

D. ROUND(123.456; 2)

Solution

1. Step 1: Recall ROUND function syntax

   ROUND(number, decimal_places) is the correct syntax in SQL.

2. Step 2: Check each option

   Only ROUND(123.456, 2) uses ROUND with a comma separating number and decimal places.

3. Final Answer:

   ROUND(123.456, 2) -> Option B

4. Quick Check:

   ROUND(number, decimals) syntax [OK]

Hint: Use comma between number and decimals in ROUND [OK]

Common Mistakes:

• Using dot instead of comma
• Using semicolon instead of comma
• Adding extra keywords like TO

3. What is the result of the SQL query:
SELECT FLOOR(7.9), CEIL(7.1), ROUND(7.5) FROM dual;

medium

A. (7, 8, 7)

B. (8, 7, 7)

C. (7, 7, 8)

D. (7, 8, 8)

Solution

1. Step 1: Evaluate FLOOR(7.9)

   FLOOR rounds down to 7.

2. Step 2: Evaluate CEIL(7.1)

   CEIL rounds up to 8.

3. Step 3: Evaluate ROUND(7.5)

   ROUND rounds to nearest integer; 7.5 rounds up to 8.

4. Final Answer:

   (7, 8, 8) -> Option D

5. Quick Check:

   FLOOR=7, CEIL=8, ROUND=8 [OK]

Hint: FLOOR down, CEIL up, ROUND nearest integer [OK]

Common Mistakes:

• Mixing FLOOR and CEIL results
• Assuming ROUND always rounds down
• Confusing ROUND behavior at .5

4. Identify the error in this SQL query:
SELECT CEIL(5.7 2) FROM dual;

medium

A. CEIL does not accept decimal numbers

B. CEIL requires two arguments

C. Missing comma between arguments

D. Syntax is correct

Solution

1. Step 1: Check CEIL function syntax

   CEIL takes only one argument, a number, with no second argument.

2. Step 2: Analyze given query

   The query has '5.7 2' without a comma, which is invalid syntax.

3. Final Answer:

   Missing comma between arguments -> Option C

4. Quick Check:

   CEIL needs one number, no extra arguments [OK]

Hint: Check commas between function arguments [OK]

Common Mistakes:

• Adding extra arguments to CEIL
• Forgetting commas between arguments
• Assuming CEIL rounds decimals differently

5. You have a table prices with a column cost containing decimal values. You want to create a new column cost_rounded that rounds cost to the nearest integer but always rounds .5 up. Which SQL expression should you use?

hard

A. FLOOR(cost + 0.5)

B. CEIL(cost - 0.5)

C. ROUND(cost)

D. CEIL(cost)

Solution

1. Step 1: Understand rounding .5 up requirement

   Standard ROUND rounds .5 up, but behavior can vary by SQL dialect.

2. Step 2: Analyze options

   FLOOR(cost + 0.5) uses FLOOR(cost + 0.5), which shifts values so .5 rounds up correctly.

3. Step 3: Verify other options

   ROUND(cost) may round .5 up but depends on implementation; B and D do not correctly round .5 up.

4. Final Answer:

   FLOOR(cost + 0.5) -> Option A

5. Quick Check:

   Use FLOOR(x + 0.5) to round .5 up reliably [OK]

Hint: Add 0.5 then FLOOR to round .5 up [OK]

Common Mistakes:

• Assuming ROUND always rounds .5 up
• Using CEIL directly without adjustment
• Using FLOOR without adding 0.5