ROUND, CEIL, FLOOR in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of ROUND function with decimal places

What is the output of the following SQL query?

SELECT ROUND(123.4567, 2) AS rounded_value;

SQL

SELECT ROUND(123.4567, 2) AS rounded_value;

A 123.45

B 123.46

C 123.5

D 124

💡 Hint

ROUND rounds the number to the specified decimal places.

ROUND(123.4567, 2) rounds the number to 2 decimal places, so 123.4567 becomes 123.46.

❓ query_result

intermediate

Output of CEIL function on a negative decimal

What is the output of this SQL query?

SELECT CEIL(-3.7) AS ceil_value;

SQL

SELECT CEIL(-3.7) AS ceil_value;

A -3

B -4

C -3.7

D -2

💡 Hint

CEIL returns the smallest integer greater than or equal to the number.

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

❓ query_result

advanced

Combining FLOOR and ROUND functions

What is the output of this SQL query?

SELECT FLOOR(ROUND(5.67, 0)) AS result_value;

SQL

SELECT FLOOR(ROUND(5.67, 0)) AS result_value;

A 6

B 5

C 7

D 5.67

💡 Hint

ROUND first rounds 5.67 to 6, then FLOOR returns the largest integer less than or equal to 6.

ROUND(5.67, 0) rounds to 6, and FLOOR(6) returns 6.

📝 Syntax

advanced

Identify the syntax error in CEIL usage

Which option contains a syntax error when using the CEIL function in SQL?

A SELECT CEIL(4.2) FROM dual;

B SELECT CEIL(-1.5);

C SELECT CEIL(4.2) AS ceil_val;

D SELECT CEIL 4.2 FROM dual;

💡 Hint

Functions require parentheses around their arguments.

Option D is missing parentheses around the argument, causing a syntax error.

🧠 Conceptual

expert

Understanding FLOOR behavior with negative numbers

Which statement best describes the behavior of the FLOOR function when applied to negative decimal numbers?

A FLOOR rounds the number towards zero, so FLOOR(-2.3) returns -2.

B FLOOR returns the largest integer less than or equal to the number, so FLOOR(-2.3) returns -2.

C FLOOR returns the largest integer less than or equal to the number, so FLOOR(-2.3) returns -3.

D FLOOR always returns the absolute value of the number rounded down, so FLOOR(-2.3) returns 2.

💡 Hint

Think about the number line and what 'less than or equal' means for negative numbers.

FLOOR returns the largest integer less than or equal to the input. For -2.3, the largest integer less than or equal is -3.

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