Mathematical functions (ROUND, CEIL, FLOOR, ABS) in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the ROUND function do in PostgreSQL?

The ROUND function rounds a numeric value to the nearest whole number or to a specified number of decimal places.

beginner

How does the CEIL function behave in PostgreSQL?

The CEIL (ceiling) function returns the smallest integer greater than or equal to the given number.

beginner

Explain the FLOOR function in PostgreSQL.

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

beginner

What is the purpose of the ABS function in PostgreSQL?

The ABS function returns the absolute value of a number, which means it removes any negative sign and returns a positive number or zero.

beginner

Which function would you use to round 3.14159 to 2 decimal places in PostgreSQL?

You would use ROUND(3.14159, 2) which returns 3.14.

What does CEIL(4.3) return in PostgreSQL?

A 5

B 3

C 4.3

D 4

CEIL returns the smallest integer greater than or equal to the number, so CEIL(4.3) is 5.

Which function returns the largest integer less than or equal to a number?

A ROUND

B CEIL

C ABS

D FLOOR

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

What is the result of ABS(-7) in PostgreSQL?

A 7

B -7

C 0

D -1

ABS returns the positive value of the number, so ABS(-7) is 7.

How does ROUND(5.678, 1) evaluate?

A 6

B 5.6

C 5.7

D 5

ROUND rounds to the specified decimal place, so 5.678 rounded to 1 decimal place is 5.7.

Which function would you use to get the integer part of 9.99 by rounding down?

A ROUND

B FLOOR

C CEIL

D ABS

FLOOR rounds down to the nearest integer, so FLOOR(9.99) returns 9.

Describe the difference between CEIL and FLOOR functions in PostgreSQL.

Think about rounding numbers up or down.

Explain how you would use the ROUND function to round a number to 3 decimal places.

ROUND(number, decimal_places)

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