Mathematical functions (ROUND, CEIL, FLOOR, ABS) in PostgreSQL - Mini Project: Build & Apply

Using Mathematical Functions in PostgreSQL

📖 Scenario: You work in a retail company database. You have a table of product prices with decimal values. You want to practice using PostgreSQL mathematical functions to round prices, find ceiling and floor values, and get absolute values for discounts.

🎯 Goal: Build SQL queries step-by-step that use the ROUND, CEIL, FLOOR, and ABS functions on a sample products table.

📋 What You'll Learn

Create a products table with columns product_id (integer) and price (numeric).

Insert exact product prices with decimal values.

Write a query using ROUND(price, 1) to round prices to 1 decimal place.

Write a query using CEIL(price) to get the smallest integer greater than or equal to the price.

Write a query using FLOOR(price) to get the largest integer less than or equal to the price.

Write a query using ABS(discount) to get the absolute value of discounts.

💡 Why This Matters

🌍 Real World

Retail and sales databases often require rounding prices for display, calculating ceilings and floors for pricing tiers, and handling discounts as absolute values.

💼 Career

Knowing how to use mathematical functions in SQL is essential for data analysts and database developers to prepare clean and accurate reports.

1

Create the products table and insert data

Create a table called products with columns product_id as integer and price as numeric. Insert these exact rows: (1, 19.95), (2, 5.49), (3, 12.30), (4, 7.99).

PostgreSQL

-- Create the products table and insert the exact rows
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Add a discount column with negative values

Alter the products table to add a column called discount of type numeric. Then update the table to set discounts as these exact values by product_id: 1 -> -2.5, 2 -> -0.99, 3 -> -1.75, 4 -> 0.

PostgreSQL

CREATE TABLE products (
    product_id INTEGER,
    price NUMERIC
);

INSERT INTO products (product_id, price) VALUES
(1, 19.95),
(2, 5.49),
(3, 12.30),
(4, 7.99);

-- Add discount column and update values
-- Your code here

Hint

Use ALTER TABLE to add a column and UPDATE ... CASE to set values.

3

Write queries using ROUND, CEIL, FLOOR, and ABS

Write four separate SELECT queries on the products table: 1) Select product_id and ROUND(price, 1) AS rounded_price. 2) Select product_id and CEIL(price) AS ceiling_price. 3) Select product_id and FLOOR(price) AS floor_price. 4) Select product_id and ABS(discount) AS absolute_discount.

PostgreSQL

CREATE TABLE products (
    product_id INTEGER,
    price NUMERIC
);

INSERT INTO products (product_id, price) VALUES
(1, 19.95),
(2, 5.49),
(3, 12.30),
(4, 7.99);

ALTER TABLE products ADD COLUMN discount NUMERIC;

UPDATE products SET discount = CASE product_id
    WHEN 1 THEN -2.5
    WHEN 2 THEN -0.99
    WHEN 3 THEN -1.75
    WHEN 4 THEN 0
END;

-- Write SELECT queries using ROUND, CEIL, FLOOR, and ABS
-- Your code here

Hint

Use SELECT with each function and alias the result with AS.

4

Combine all function results in one query

Write a single SELECT query on products that returns product_id, ROUND(price, 1) AS rounded_price, CEIL(price) AS ceiling_price, FLOOR(price) AS floor_price, and ABS(discount) AS absolute_discount all in one result set.

PostgreSQL

CREATE TABLE products (
    product_id INTEGER,
    price NUMERIC
);

INSERT INTO products (product_id, price) VALUES
(1, 19.95),
(2, 5.49),
(3, 12.30),
(4, 7.99);

ALTER TABLE products ADD COLUMN discount NUMERIC;

UPDATE products SET discount = CASE product_id
    WHEN 1 THEN -2.5
    WHEN 2 THEN -0.99
    WHEN 3 THEN -1.75
    WHEN 4 THEN 0
END;

-- Write a single SELECT query combining ROUND, CEIL, FLOOR, and ABS
-- Your code here

Hint

Use one SELECT statement with all functions separated by commas.

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