ROUND, CEIL, FLOOR in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the ROUND function do in SQL?

ROUND rounds a numeric value to the nearest integer or to a specified number of decimal places.

beginner

How does the CEIL function behave in SQL?

CEIL returns the smallest integer greater than or equal to the given number. It always rounds up.

beginner

What is the purpose of the FLOOR function in SQL?

FLOOR returns the largest integer less than or equal to the given number. It always rounds down.

beginner

How would you round the number 3.14159 to 2 decimal places using SQL?

Use ROUND(3.14159, 2) which returns 3.14.

beginner

If you apply CEIL to 4.2 and FLOOR to 4.8, what are the results?

CEIL(4.2) returns 5, FLOOR(4.8) returns 4.

What does ROUND(5.678, 1) return?

A 6

B 5.6

C 5.7

D 5

ROUND(5.678, 1) rounds to one decimal place, so it returns 5.7.

Which function always rounds a number up to the nearest integer?

A FLOOR

B CEIL

C TRUNC

D ROUND

CEIL always rounds up to the smallest integer greater than or equal to the number.

What is FLOOR(7.9)?

A 7

B 8

C 7.9

D 0

FLOOR returns the largest integer less than or equal to 7.9, which is 7.

Which function would you use to round 2.5 to the nearest integer?

A ROUND

B FLOOR

C CEIL

D ABS

ROUND rounds to the nearest integer, so ROUND(2.5) returns 3.

What does CEIL(-3.7) return?

A -4

B 3

C -3.7

D -3

CEIL(-3.7) returns the smallest integer greater than or equal to -3.7, which is -3.

Explain the difference between ROUND, CEIL, and FLOOR functions in SQL.

Think about how each function treats decimal numbers.

How would you use these functions to handle the number 5.3 in different ways?

Consider rounding up, down, or to nearest.

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