ROUND, CEIL, FLOOR in SQL

Introduction

These functions help you change numbers to simpler forms. You can round numbers, or always go up or down to the nearest whole number.

When you want to show prices without many decimals.

When you need to count whole items, not parts.

When you want to find the smallest whole number bigger than a value.

When you want to find the biggest whole number smaller than a value.

When you want to prepare numbers for reports or summaries.

Syntax

SQL

ROUND(number, decimals)
CEIL(number)
FLOOR(number)

ROUND changes a number to a set number of decimal places.

CEIL always rounds a number up to the next whole number.

FLOOR always rounds a number down to the previous whole number.

Examples

Rounds 3.14159 to 2 decimal places, result is 3.14.

SQL

SELECT ROUND(3.14159, 2);

Rounds 4.2 up to 5.

SQL

SELECT CEIL(4.2);

Rounds 4.8 down to 4.

SQL

SELECT FLOOR(4.8);

Rounds 123.456 to 0 decimals, result is 123.

SQL

SELECT ROUND(123.456, 0);

Sample Program

This query shows how ROUND, CEIL, and FLOOR work on the number 7.856.

SQL

SELECT ROUND(7.856, 1) AS rounded_value,
       CEIL(7.856) AS ceiling_value,
       FLOOR(7.856) AS floor_value;

Important Notes

ROUND can take a second number to decide how many decimals to keep.

CEIL and FLOOR always return whole numbers.

These functions are useful for cleaning up numbers before showing them.

Summary

ROUND changes numbers to a set decimal place.

CEIL always rounds numbers up.

FLOOR always rounds numbers down.

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