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

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

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

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

Hint

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

End with COMMIT; to save changes.

Practice

1. What is the main purpose of a user-defined function in SQL?

easy

A. To create reusable code that returns a single value

B. To store large amounts of data

C. To create tables automatically

D. To delete records from a table

Solution

1. Step 1: Understand what user-defined functions do

   User-defined functions are designed to perform specific tasks and return a single value.

2. Step 2: Compare options with the function's purpose

   Only To create reusable code that returns a single value describes creating reusable code that returns a single value, which matches the function's role.

3. Final Answer:

   To create reusable code that returns a single value -> Option A

4. Quick Check:

   User-defined function = reusable single-value code [OK]

Hint: Functions return one value and reuse code easily [OK]

Common Mistakes:

• Confusing functions with tables or data storage
• Thinking functions delete or modify data directly
• Assuming functions return multiple rows or tables

2. Which of the following is the correct way to start creating a scalar user-defined function in SQL?

easy

A. DROP FUNCTION function_name();

B. CREATE FUNCTION function_name() RETURNS INT AS BEGIN RETURN 1; END;

C. SELECT FUNCTION function_name();

D. CREATE TABLE function_name();

Solution

1. Step 1: Identify the correct syntax for creating a function

   The correct syntax starts with CREATE FUNCTION, specifies the return type, and includes a BEGIN...END block.

2. Step 2: Check each option's validity

   CREATE FUNCTION function_name() RETURNS INT AS BEGIN RETURN 1; END; matches the correct syntax. Options A, B, and C are for other SQL commands (dropping, table creation, and selection).

3. Final Answer:

   CREATE FUNCTION function_name() RETURNS INT AS BEGIN RETURN 1; END; -> Option B

4. Quick Check:

   CREATE FUNCTION + RETURNS + BEGIN...END = correct syntax [OK]

Hint: Functions start with CREATE FUNCTION and specify RETURNS [OK]

Common Mistakes:

• Using CREATE TABLE instead of CREATE FUNCTION
• Trying to SELECT a function directly without calling it
• Confusing DROP FUNCTION with creation syntax

3. Given the function below, what will be the output of SELECT dbo.AddFive(10);?

CREATE FUNCTION dbo.AddFive(@num INT) RETURNS INT AS BEGIN RETURN @num + 5; END;

medium

A. 5

B. 10

C. 15

D. Syntax Error

Solution

1. Step 1: Understand the function logic

   The function takes an integer input and returns the input plus 5.

2. Step 2: Calculate the output for input 10

   10 + 5 = 15, so the function returns 15.

3. Final Answer:

   15 -> Option C

4. Quick Check:

   Input 10 + 5 = 15 [OK]

Hint: Add input to 5 as function returns input + 5 [OK]

Common Mistakes:

• Confusing input and output values
• Assuming function returns input unchanged
• Expecting syntax error due to missing parameters

4. Identify the error in the following user-defined function:

CREATE FUNCTION dbo.MultiplyByTwo(@val INT)
RETURNS INT
BEGIN
  SET @val = @val * 2;
  RETURN @val;
END;

medium

A. Missing AS keyword before BEGIN

B. Functions cannot modify input parameters

C. RETURN statement is not allowed in functions

D. Function must return VARCHAR, not INT

Solution

1. Step 1: Review function syntax rules

   In SQL, the AS keyword is required before the BEGIN block in function definitions.

2. Step 2: Check the given function

   The function is missing AS before BEGIN, causing a syntax error.

3. Final Answer:

   Missing AS keyword before BEGIN -> Option A

4. Quick Check:

   CREATE FUNCTION ... RETURNS ... AS BEGIN ... [OK]

Hint: Always include AS before BEGIN in function syntax [OK]

Common Mistakes:

• Trying to modify input parameters inside functions
• Omitting AS keyword before BEGIN
• Confusing function return types

5. You want to create a user-defined function that returns the length of a string only if the string is not empty; otherwise, it returns 0. Which of the following function definitions correctly implements this logic?

hard

A. CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN RETURN NULL; END;

B. CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN IF @input = '' RETURN 1; ELSE RETURN LEN(@input); END;

C. CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN RETURN CASE WHEN @input = '' THEN 1 ELSE LEN(@input) END; END;

D. CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN RETURN LEN(@input); END;

Solution

1. Step 1: Understand the requirement

   The function must return 0 if the string is empty, otherwise return its length.

2. Step 2: Analyze each option

   CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN RETURN LEN(@input); END; correctly returns LEN(@input), and since LEN('')=0 it satisfies the logic. CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN IF @input = '' RETURN 1; ELSE RETURN LEN(@input); END; returns 1 for empty strings, incorrect. CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN RETURN CASE WHEN @input = '' THEN 1 ELSE LEN(@input) END; END; returns 1 for empty strings using CASE, incorrect. CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN RETURN NULL; END; always returns NULL, which is incorrect.

3. Final Answer:

   CREATE FUNCTION dbo.StringLength(@input VARCHAR(100)) RETURNS INT AS BEGIN RETURN LEN(@input); END; -> Option D

4. Quick Check:

   LEN('') = 0 satisfies condition [OK]

Hint: LEN('') returns 0 for empty strings [OK]

Common Mistakes:

• Returning incorrect value (like 1) for empty strings
• Using overly complex conditional logic unnecessarily
• Returning NULL instead of 0 for empty strings