Mathematical functions (ROUND, CEIL, FLOOR, ABS) in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to round the number 3.14159 to the nearest integer.

PostgreSQL

SELECT [1](3.14159);

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

AFLOOR

BROUND

CCEIL

DABS

Attempts:

3 left

💡 Hint

Common Mistakes

Using CEIL or FLOOR instead of ROUND will always round up or down, not to the nearest integer.

Using ABS returns the absolute value, not rounding.

The ROUND function rounds a number to the nearest integer or specified decimal places.

2fill in blank

medium

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

PostgreSQL

SELECT [1](4.3);

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

AABS

BROUND

CFLOOR

DCEIL

Attempts:

3 left

💡 Hint

Common Mistakes

Using FLOOR will round down instead of up.

Using ROUND may round down if decimal part is less than 0.5.

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

3fill in blank

hard

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

PostgreSQL

SELECT [1](7.8);

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

AROUND

BABS

CFLOOR

DCEIL

Attempts:

3 left

💡 Hint

Common Mistakes

Using CEIL will round up instead of down.

Using ROUND may round up or down depending on decimal part.

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

4fill in blank

hard

Fill both blanks to calculate the absolute value of -15 and then round it.

PostgreSQL

SELECT [1]([2](-15));

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

AROUND

BCEIL

CABS

DFLOOR

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the order of functions will cause errors or wrong results.

Using CEIL or FLOOR instead of ABS will not remove the negative sign.

First, ABS returns the absolute value of -15, which is 15. Then, ROUND rounds the number (though 15 is already an integer).

5fill in blank

hard

Fill all three blanks to round the absolute value of -3.7 up to the nearest integer.

PostgreSQL

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

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

AROUND

BCEIL

CABS

D-3.7

Attempts:

3 left

💡 Hint

Common Mistakes

Using ROUND instead of CEIL will round 3.7 to 4 but not always up.

Not using ABS will keep the negative sign and affect rounding.

The innermost value is -3.7. The ABS function makes it positive (3.7). Then CEIL rounds it up to 4.

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