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

Need a 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

Need a 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

Need a 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

Need a hint?

Use one SELECT statement with all functions separated by commas.