User-defined functions in SQL - Mini Project: Build & Apply

Create and Use a User-Defined Function in SQL

📖 Scenario: You work at a retail company that wants to calculate the discounted price for products based on a fixed discount rate.They want to reuse this calculation easily in their database queries.

🎯 Goal: Create a user-defined function in SQL that calculates the discounted price given an original price and a discount rate.Then use this function to find the discounted prices of products in a sample table.

📋 What You'll Learn

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

Insert three products with prices: 100.00, 200.00, and 300.00.

Create a user-defined function called calculate_discount that takes original_price and discount_rate as inputs and returns the discounted price.

Use the calculate_discount function in a SELECT query to show product IDs and their discounted prices with a discount rate of 0.1 (10%).

💡 Why This Matters

🌍 Real World

Retail companies often need to apply discounts to product prices dynamically in their databases for sales and promotions.

💼 Career

Knowing how to create and use user-defined functions in SQL helps database developers and analysts write reusable, clear, and maintainable code.

1

Create the products table and insert data

Write SQL statements to create a table called products with columns product_id as INTEGER and price as DECIMAL(10,2). Then insert three rows with product_id values 1, 2, 3 and price values 100.00, 200.00, and 300.00 respectively.

SQL

-- Create the products table
-- Insert three products with prices

Need a hint?

Use CREATE TABLE products (product_id INTEGER, price DECIMAL(10,2)); to create the table.

Use INSERT INTO products (product_id, price) VALUES (1, 100.00), (2, 200.00), (3, 300.00); to add the rows.

2

Define the calculate_discount function

Write a SQL statement to create a user-defined function called calculate_discount that takes two parameters: original_price DECIMAL(10,2) and discount_rate DECIMAL(3,2). The function should return the discounted price calculated as original_price * (1 - discount_rate).

SQL

CREATE TABLE products (
    product_id INTEGER,
    price DECIMAL(10,2)
);

INSERT INTO products (product_id, price) VALUES
(1, 100.00),
(2, 200.00),
(3, 300.00);

-- Create the calculate_discount function

Need a hint?

Use CREATE FUNCTION calculate_discount(original_price DECIMAL(10,2), discount_rate DECIMAL(3,2)) RETURNS DECIMAL(10,2) to start.

Inside the function, return original_price * (1 - discount_rate).

3

Use the calculate_discount function in a query

Write a SELECT query that retrieves product_id and the discounted price using the calculate_discount function with a discount rate of 0.1 for each product in the products table.

SQL

CREATE TABLE products (
    product_id INTEGER,
    price DECIMAL(10,2)
);

INSERT INTO products (product_id, price) VALUES
(1, 100.00),
(2, 200.00),
(3, 300.00);

CREATE FUNCTION calculate_discount(original_price DECIMAL(10,2), discount_rate DECIMAL(3,2))
RETURNS DECIMAL(10,2) AS $$
BEGIN
    RETURN original_price * (1 - discount_rate);
END;
$$ LANGUAGE plpgsql;

-- Select product_id and discounted price using calculate_discount with discount_rate 0.1

Need a hint?

Use SELECT product_id, calculate_discount(price, 0.1) AS discounted_price FROM products; to get the discounted prices.

4

Complete the function with a comment and commit

Add a comment above the calculate_discount function explaining it calculates discounted price. Then add a COMMIT statement at the end.

SQL

CREATE TABLE products (
    product_id INTEGER,
    price DECIMAL(10,2)
);

INSERT INTO products (product_id, price) VALUES
(1, 100.00),
(2, 200.00),
(3, 300.00);

-- This function calculates the discounted price given original price and discount rate
CREATE FUNCTION calculate_discount(original_price DECIMAL(10,2), discount_rate DECIMAL(3,2))
RETURNS DECIMAL(10,2) AS $$
BEGIN
    RETURN original_price * (1 - discount_rate);
END;
$$ LANGUAGE plpgsql;

SELECT product_id, calculate_discount(price, 0.1) AS discounted_price FROM products;

-- Commit the transaction

Need a hint?

Add a comment line starting with -- above the function.

End with COMMIT; to save changes.