ROUND, CEIL, FLOOR in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to round the value 3.7 to the nearest integer.

SQL

SELECT [1](3.7);

Drag options to blanks, or click blank then click option'

ATRUNC

BCEIL

CFLOOR

DROUND

Attempts:

3 left

💡 Hint

Common Mistakes

Using CEIL or FLOOR instead of ROUND.

Confusing ROUND with TRUNC.

The ROUND function rounds a number to the nearest integer.

2fill in blank

medium

Complete the code to get the smallest integer greater than or equal to 5.3.

SQL

SELECT [1](5.3);

Drag options to blanks, or click blank then click option'

AROUND

BFLOOR

CCEIL

DABS

Attempts:

3 left

💡 Hint

Common Mistakes

Using FLOOR which rounds down.

Using ROUND which rounds to nearest.

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

3fill in blank

hard

Fix the error in the code to get the largest integer less than or equal to 7.9.

SQL

SELECT [1](7.9);

Drag options to blanks, or click blank then click option'

AFLOOR

BROUND

CCEIL

DSQRT

Attempts:

3 left

💡 Hint

Common Mistakes

Using CEIL which rounds up.

Using ROUND which rounds to nearest.

The FLOOR function returns the largest integer less than or equal to the number.

4fill in blank

hard

Fill both blanks to round 4.567 to 2 decimal places and then get the ceiling of the result.

SQL

SELECT [1]([2](4.567, 2));

Drag options to blanks, or click blank then click option'

ACEIL

BROUND

CFLOOR

DABS

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping CEIL and ROUND order.

Using FLOOR instead of CEIL.

First, ROUND rounds 4.567 to 4.57. Then CEIL returns the smallest integer greater than or equal to 4.57, which is 5.

5fill in blank

hard

Fill all three blanks to get the floor of 9.99, then round it, and finally get the ceiling of the rounded value.

SQL

SELECT [1]([2]([3](9.99)));

Drag options to blanks, or click blank then click option'

ACEIL

BROUND

CFLOOR

DABS

Attempts:

3 left

💡 Hint

Common Mistakes

Changing the order of functions.

Using ABS which is unrelated.

First, FLOOR(9.99) gives 9. Then ROUND(9) is 9. Finally, CEIL(9) is 9.

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