Mathematical functions (ROUND, CEIL, FLOOR, ABS) in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of ROUND function with decimal places

What is the output of this SQL query?

SELECT ROUND(12.3456, 2);

PostgreSQL

SELECT ROUND(12.3456, 2);

A 12

B 12.34

C 12.35

D 13

💡 Hint

ROUND rounds the number to the specified decimal places.

ROUND(12.3456, 2) rounds the number to 2 decimal places, resulting in 12.35.

❓ query_result

intermediate

Output of CEIL function on negative decimal

What is the output of this SQL query?

SELECT CEIL(-3.7);

PostgreSQL

SELECT CEIL(-3.7);

A -3

B -4

C -3.7

D 3

💡 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

Output of FLOOR function with positive decimal

What is the output of this SQL query?

SELECT FLOOR(7.999);

PostgreSQL

SELECT FLOOR(7.999);

A 8

B 7

C 7.999

D 0

💡 Hint

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

FLOOR(7.999) returns 7 because 7 is the largest integer less than or equal to 7.999.

❓ query_result

advanced

Output of ABS function on negative integer

What is the output of this SQL query?

SELECT ABS(-15);

PostgreSQL

SELECT ABS(-15);

A 15

B 0

C -15

D NULL

💡 Hint

ABS returns the absolute value of the number.

ABS(-15) returns 15 because absolute value removes the negative sign.

🧠 Conceptual

expert

Understanding combined use of ROUND, CEIL, FLOOR, and ABS

Given the query:

SELECT ROUND(CEIL(-2.3) + FLOOR(3.7) + ABS(-4.5), 0);

What is the output?

PostgreSQL

SELECT ROUND(CEIL(-2.3) + FLOOR(3.7) + ABS(-4.5), 0);

A 7

B 5

C -6

D 6

💡 Hint

Calculate each function step-by-step before adding and rounding.

CEIL(-2.3) = -2, FLOOR(3.7) = 3, ABS(-4.5) = 4.5; sum = -2 + 3 + 4.5 = 5.5; ROUND(5.5,0) = 6.

Practice

1. What does the ABS function do in PostgreSQL?

easy

A. Rounds a number to the nearest integer

B. Finds the largest integer less than or equal to a number

C. Finds the smallest integer greater than or equal to a number

D. Returns the positive value of a number

Solution

1. Step 1: Understand the ABS function

   The ABS function returns the absolute value, which means it converts any number to its positive form.

2. Step 2: Compare with other functions

   ROUND changes decimals, CEIL and FLOOR deal with integers, so they do not return absolute values.

3. Final Answer:

   Returns the positive value of a number -> Option D

4. Quick Check:

   ABS(-5) = 5 [OK]

Hint: ABS always returns a positive number or zero [OK]

Common Mistakes:

• Confusing ABS with ROUND
• Thinking ABS changes decimal places
• Mixing ABS with CEIL or FLOOR

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

easy

A. ROUND(3.14159, 2)

B. ROUND(3.14159.2)

C. ROUND(3.14159 TO 2)

D. ROUND(3.14159, '2')

Solution

1. Step 1: Recall ROUND function syntax

   ROUND takes two arguments: the number and the number of decimal places as an integer.

2. Step 2: Check each option

   ROUND(3.14159, 2) uses correct syntax ROUND(number, decimals). Others have syntax errors or wrong argument types.

3. Final Answer:

   ROUND(3.14159, 2) -> Option A

4. Quick Check:

   ROUND(3.14159, 2) = 3.14 [OK]

Hint: ROUND(number, decimals) uses comma and integer decimals [OK]

Common Mistakes:

• Using dot instead of comma between arguments
• Passing decimals as string
• Using 'TO' keyword incorrectly

3. What is the result of this PostgreSQL query?
SELECT CEIL(-3.7), FLOOR(-3.7), ROUND(-3.7), ABS(-3.7);

medium

A. (-3, -4, -3, 3.7)

B. (-4, -3, -4, 3.7)

C. (-3, -4, -4, 3.7)

D. (-3, -4, -4, -3.7)

Solution

1. Step 1: Calculate CEIL and FLOOR for -3.7

   CEIL(-3.7) is the smallest integer >= -3.7, which is -3. FLOOR(-3.7) is the largest integer <= -3.7, which is -4.

2. Step 2: Calculate ROUND and ABS

   ROUND(-3.7) rounds to nearest integer, which is -4. ABS(-3.7) returns positive value 3.7.

3. Final Answer:

   (-3, -4, -4, 3.7) -> Option C

4. Quick Check:

   CEIL(-3.7) = -3, FLOOR(-3.7) = -4, ROUND(-3.7) = -4, ABS(-3.7) = 3.7 [OK]

Hint: CEIL rounds up, FLOOR rounds down, ABS makes positive [OK]

Common Mistakes:

• Mixing CEIL and FLOOR results for negative numbers
• Assuming ROUND always truncates
• Forgetting ABS returns positive

4. Identify the error in this PostgreSQL query:
SELECT ROUND(5.678, '1');

medium

A. ROUND requires only one argument

B. The second argument should be an integer, not a string

C. Missing FROM clause in the query

D. ROUND cannot be used with decimal numbers

Solution

1. Step 1: Check ROUND function arguments

   ROUND expects the second argument to be an integer specifying decimal places, not a string.

2. Step 2: Validate other options

   ROUND works with decimals, FROM clause is optional in SELECT without tables, and ROUND can take two arguments.

3. Final Answer:

   The second argument should be an integer, not a string -> Option B

4. Quick Check:

   ROUND(5.678, 1) is correct, ROUND(5.678, '1') causes error [OK]

Hint: Use integer for decimals, not quoted strings [OK]

Common Mistakes:

• Passing decimals as quoted strings
• Thinking FROM is mandatory for simple SELECT
• Assuming ROUND only takes one argument

5. You want to calculate the total price after applying a 7.5% tax on a price of 123.4567 and round the result to 2 decimal places. Which PostgreSQL expression correctly does this?

hard

A. ROUND(123.4567 * 1.075, 2)

B. CEIL(123.4567 * 1.075, 2)

C. FLOOR(123.4567 * 1.075, 2)

D. ABS(123.4567 * 1.075, 2)

Solution

1. Step 1: Calculate price with tax

   Multiply price by 1.075 (100% + 7.5%) to get total price including tax.

2. Step 2: Round to 2 decimal places

   Use ROUND function with second argument 2 to round to two decimals.

3. Step 3: Check other functions

   CEIL and FLOOR do not accept decimals argument, ABS is for absolute value, so they are incorrect here.

4. Final Answer:

   ROUND(123.4567 * 1.075, 2) -> Option A

5. Quick Check:

   ROUND(123.4567 * 1.075, 2) = 132.72 [OK]

Hint: Use ROUND for decimals, CEIL/FLOOR only for integers [OK]

Common Mistakes:

• Using CEIL or FLOOR with decimal places argument
• Using ABS instead of ROUND for rounding
• Forgetting to multiply by 1.075 for tax