PostgreSQL & PL/pgSQL

Practice Question Bank

22 solved IPE exercises covering SQL queries, subqueries, joins,

stored procedures, functions, triggers, and cursors.

Compiled from LJIET's Students Club materials

Credit: Kartik Dafda

PostgreSQL & PL/pgSQL Practice Question Bank Page 1

 Table of Contents

IPE_1 Manager / Department / Employee Queries

IPE_2 Customer Backup Trigger

IPE_3 Art Gallery: Paintings, Artists, Customers, Sales

IPE_4 Insurance Company / Agent / Customer

IPE_5 Faculty / Department / Student (Subqueries)

IPE_6 ATM Transaction Simulation

IPE_7 Library Book Availability Check

IPE_8 Employee Salary Bonus Calculation

IPE_9 Employee Info Procedure with Cursor

IPE_10 Count of Completed Months Between Two Dates

IPE_11 Student Marksheet Generation with Cursor

IPE_12 Duplicate Product Name Trigger

IPE_13 Library Book Count Decrement Trigger

IPE_14 Order Logging Trigger

IPE_15 Salary Update Monitoring Trigger

IPE_16 Temperature Conversion Function

IPE_17 Fetch Order Details with Cursor

IPE_18 Scholarship Assignment Trigger

IPE_19 Update Student Marks Procedure

IPE_20 Employee Mobile Number Update Trigger

IPE_21 Movies Rented in the Last Month

IPE_22 Average Salary by Department

PostgreSQL & PL/pgSQL Practice Question Bank Page 2

 IPE_1 — Manager / Department / Employee Queries

SCHEMA
Manager (mid, eid, mname)
Department (did, mid, dname, location)
Employee (eid, ename, mobile, salary, joining_date, mid)

Write a SQL query considering the above schema. Create tables with Primary Key, Foreign Key
constraints in given schemas.

i. Give the name and salary of employees whose salary is greater than each and every employee
working under manager id 14.
SELECT [Link], [Link]
FROM Employee e
WHERE [Link] > ALL (
SELECT [Link]
FROM Employee e2
WHERE [Link] = 14
);

ii. Give the name of employees who have not been assigned any department.
SELECT [Link]
FROM Employee e
LEFT JOIN Department d ON [Link] = [Link]
WHERE [Link] IS NULL;

iii. Give the employee names whose location is Kota.

SELECT [Link]
FROM Employee e
JOIN Department d ON [Link] = [Link]
WHERE [Link] = 'Kota';

iv. Give the name of manager along with the count of employees assigned to him/her, in
descending order.
SELECT [Link], COUNT([Link]) AS employee_count
FROM Manager m
JOIN Employee e ON [Link] = [Link]
GROUP BY [Link]
ORDER BY employee_count DESC;

PostgreSQL & PL/pgSQL Practice Question Bank Page 3

 IPE_2 — Customer Backup Trigger

SCHEMA
Customer_backup (first_name, last_name, amount, payment_id)
Customer (customer_id, payment_id, first_name, last_name, amount)

Write PL/pgSQL using a trigger to insert first_name, last_name, amount and payment_id into
Customer_backup when a deletion happens from Customer. Create tables with Primary Key, Foreign Key
constraints in given schemas.

CREATE OR REPLACE FUNCTION backup_customer_before_delete()

RETURNS TRIGGER AS $$
BEGIN
INSERT INTO Customer_backup
VALUES (OLD.first_name, OLD.last_name, [Link], OLD.payment_id);
RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER trigger_backup_customer

BEFORE DELETE ON Customer
FOR EACH ROW
EXECUTE FUNCTION backup_customer_before_delete();

PostgreSQL & PL/pgSQL Practice Question Bank Page 4

 IPE_3 — Art Gallery: Paintings, Artists, Customers, Sales

SCHEMA
Paintings (p_id, name, a_id, listed_price)
Artists (a_id, first_name, last_name)
Customer (c_id, first_name, last_name)
Sales (s_id, date, p_id, a_id, c_id, sales_price)

Create tables with Primary Key, Foreign Key constraints in given schemas, then write queries to retrieve
the following data.

1) Find the paintings that were sold in the first thirteen days of August 2023.
SELECT [Link]
FROM Paintings p
JOIN Sales s ON p.p_id = s.p_id
WHERE [Link] BETWEEN '2023-08-01' AND '2023-08-13';

2) Display painting name and its listed price, along with its sales price, where it was sold at a
price higher than the listed price to any customer.
SELECT [Link], Paintings.listed_price, Sales.sales_price
FROM Paintings
INNER JOIN Sales ON Paintings.p_id = Sales.p_id
WHERE Sales.sales_price > Paintings.listed_price;

3) Retrieve details of paintings that were sold at a price equal to the listed price.
SELECT [Link], p.listed_price, s.sales_price
FROM Paintings p
JOIN Sales s ON p.p_id = s.p_id
WHERE s.sales_price = p.listed_price;

4) Display the names of customers who have bought at least one painting.
SELECT DISTINCT first_name, last_name
FROM Customer
JOIN Sales ON Customer.c_id = Sales.c_id;

5) Find the total sales value for each artist.

SELECT first_name, last_name, SUM(sales_price) AS total_sales_value
FROM Artists
JOIN Sales ON Artists.a_id = Sales.a_id
GROUP BY Artists.first_name, Artists.last_name;

PostgreSQL & PL/pgSQL Practice Question Bank Page 5

 IPE_4 — Insurance Company / Agent / Customer

SCHEMA
Insurance_Company (c_id, c_name, city, state, policy_no, policy_name, premium, cust_id,
a_id)
Agent (a_id, a_name, address, a_city, a_state, c_name, salary, incentive)
Customer (cust_id, name, age, cust_address, cust_city, cust_state, a_id, policy_no)

Using the above schema, write SQL statements to fetch the correct data. Create tables with Primary Key,
Foreign Key constraints in given schemas.

1) Display the details of customers who have taken a policy from 'Ahmedabad Insurance
Company'.
SELECT *
FROM Customer
INNER JOIN Insurance_Company
ON Customer.policy_no = Insurance_Company.policy_no
WHERE Insurance_Company.c_name = 'Ahmedabad Insurance Company';

2) Display the agent name of agents who have not sold even a single policy.
SELECT a_name
FROM Agent
LEFT JOIN Customer ON Agent.a_id = Customer.a_id
WHERE cust_id IS NULL;

3) Display the name of the insurance company with the maximum number of customers.
SELECT Insurance_Company.c_name
FROM Insurance_Company
INNER JOIN Customer
ON Insurance_Company.policy_no = Customer.policy_no
GROUP BY Insurance_Company.c_name
ORDER BY COUNT(Customer.cust_id) DESC
LIMIT 1;

PostgreSQL & PL/pgSQL Practice Question Bank Page 6

 IPE_5 — Faculty / Department / Student (Subqueries)

SCHEMA
Faculty (f_id, f_name, address, city, state, age, salary, d_id)
Department (dep_id, d_name, course, location, sub_code, subject)
Student (enrl_no, s_name, s_address, s_city, s_state, s_age, course, f_id, hobby)

Write SQL statements for the given queries using subqueries. Create tables with Primary Key, Foreign Key
constraints in given schemas.

1) Display the names of faculties and students who are not living in the same city.
SELECT f_name
FROM Faculty
WHERE city NOT IN (
SELECT s_city
FROM Student
WHERE f_id = Faculty.f_id
)
UNION
SELECT s_name
FROM Student
WHERE s_city NOT IN (
SELECT city
FROM Faculty
WHERE f_id = Student.f_id
);

2) Find how much is spent on salaries of faculties working in the 'IT Engineering' department.
SELECT SUM(salary)
FROM Faculty
WHERE d_id = (
SELECT dep_id
FROM Department
WHERE d_name = 'IT Engineering'
);

3) Display department name, course, subject code and subject for anyone who has enrolled as a
student.
SELECT Department.d_name, [Link], Department.sub_code, [Link]
FROM Department
WHERE Department.dep_id IN (
SELECT Faculty.d_id
FROM Faculty
INNER JOIN Student ON Faculty.f_id = Student.f_id
);

4) Display the id and name of faculty working in the 'IT Engineering' department with a salary
greater than Rs. 60000.
SELECT Faculty.f_id, Faculty.f_name
FROM Faculty
WHERE Faculty.d_id = (
SELECT Department.dep_id
FROM Department
WHERE Department.d_name = 'IT Engineering'
)
AND [Link] > 60000;

PostgreSQL & PL/pgSQL Practice Question Bank Page 7

 IPE_6 — ATM Transaction Simulation

Prepare a PL/pgSQL block that simulates an ATM transaction system:

- Withdrawal amount ≤ 0 → "Invalid withdrawal amount"
- Withdrawal amount greater than account balance → "Insufficient funds"
- Withdrawal amount up to Rs. 20,000 → withdraw, deduct from balance
- Withdrawal amount up to Rs. 40,000 → withdraw, deduct, apply 5% transaction fee
- Withdrawal amount above Rs. 40,000 → withdraw, deduct, apply 18% transaction fee
The block also displays the current account balance after each scenario.

DO $$
DECLARE
withdrawal_amount NUMERIC := 25000;
account_balance NUMERIC := 50000;
transaction_fee NUMERIC := 0;
BEGIN
IF withdrawal_amount <= 0 THEN
RAISE NOTICE 'Invalid withdrawal amount';

ELSIF withdrawal_amount > account_balance THEN

RAISE NOTICE 'Insufficient funds';

ELSIF withdrawal_amount <= 20000 THEN

account_balance := account_balance - withdrawal_amount;
RAISE NOTICE 'Withdrawal successful. Your current balance is Rs. %', account_balance;

ELSIF withdrawal_amount >= 20000 AND withdrawal_amount <= 40000 THEN

transaction_fee := withdrawal_amount * 0.05;
account_balance := account_balance - withdrawal_amount - transaction_fee;
RAISE NOTICE 'Withdrawal successful. A 5%% fee of Rs. % was applied. Your current
balance is Rs. %',
transaction_fee, account_balance;

ELSE
transaction_fee := withdrawal_amount * 0.18;
account_balance := account_balance - withdrawal_amount - transaction_fee;
RAISE NOTICE 'Withdrawal successful. An 18%% fee of Rs. % was applied. Your current
balance is Rs. %',
transaction_fee, account_balance;
END IF;
END $$;

PostgreSQL & PL/pgSQL Practice Question Bank Page 8

 IPE_7 — Library Book Availability Check

SCHEMA
Books (books_isbn, book_name)

Design a database for a library management system where each book has multiple copies. Write a
PL/pgSQL stored function to check availability of a book by ISBN — return 1 if available, else 0. Example:
check availability of ISBN 9780451524935.

-- Books Table
CREATE TABLE Books (
books_isbn VARCHAR(13) PRIMARY KEY,
book_name VARCHAR(255)
);

-- BookCopies Table
CREATE TABLE BookCopies (
copy_id SERIAL PRIMARY KEY,
books_isbn VARCHAR(13) REFERENCES Books(books_isbn),
is_available BOOLEAN
);

CREATE OR REPLACE FUNCTION check_book_availability(books_isbn VARCHAR(13))

RETURNS INT AS $$
DECLARE
available_count INT;
BEGIN
-- Count the number of available copies for the given ISBN
SELECT COUNT(*) INTO available_count
FROM BookCopies
WHERE books_isbn = check_book_availability.books_isbn
AND is_available = TRUE;

-- If there are available copies, return 1, otherwise return 0

IF available_count > 0 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;
$$ LANGUAGE plpgsql;

PostgreSQL & PL/pgSQL Practice Question Bank Page 9

 IPE_8 — Employee Salary Bonus Calculation

SCHEMA
employee (eid, ename, salary)

Create a PL/pgSQL function calculate_salary_bonus which takes employee_id and bonus_percentage,

and calculates the bonus amount based on the employee's current salary and the provided percentage.

CREATE OR REPLACE FUNCTION calculate_bonus(employee_id INT, bonus_percentage NUMERIC)

RETURNS NUMERIC AS $$
DECLARE
current_salary NUMERIC;
bonus_amount NUMERIC;
BEGIN
SELECT salary INTO current_salary FROM employee WHERE eid = employee_id;

IF NOT FOUND THEN

RAISE EXCEPTION 'Employee with ID % not found', employee_id;
END IF;

bonus_amount = current_salary * (bonus_percentage / 100.0);

RETURN bonus_amount;
END;
$$ LANGUAGE plpgsql;

PostgreSQL & PL/pgSQL Practice Question Bank Page 10

 IPE_9 — Employee Info Procedure with Cursor

SCHEMA
Employee (employeeno, firstname, lastname, hiredate, job_id, salary, job_title)

Create a procedure emp_infor that accepts employeeno and job_title, passes them to an explicit cursor
emp_list retrieving firstname, lastname, hiredate, job_id and salary of employees earning less than 11000
with 'O' as the second letter of the lastname. Create an anonymous block that calls the procedure.

CREATE OR REPLACE PROCEDURE emp_infor(

p_employeeno IN [Link]%TYPE,
p_job_title IN Employee.job_title%TYPE
)
LANGUAGE plpgsql
AS $$
DECLARE
CURSOR emp_list IS
SELECT firstname, lastname, hiredate, job_id, salary
FROM Employee
WHERE salary < 11000
AND SUBSTRING(lastname, 2, 1) = 'O'
AND employeeno = p_employeeno
AND job_title = p_job_title;

v_firstname [Link]%TYPE;
v_lastname [Link]%TYPE;
v_hiredate [Link]%TYPE;
v_job_id Employee.job_id%TYPE;
v_salary [Link]%TYPE;
BEGIN
OPEN emp_list;
FETCH emp_list INTO v_firstname, v_lastname, v_hiredate, v_job_id, v_salary;

IF NOT FOUND THEN

RAISE NOTICE 'No employee found with employeeno % and job_title %', p_employeeno,
p_job_title;
ELSE
RAISE NOTICE 'Firstname: %, Lastname: %, Hire Date: %, Job ID: %, Salary: Rs. %',
v_firstname, v_lastname, v_hiredate, v_job_id, v_salary;
END IF;

CLOSE emp_list;
END $$;

DO $$
BEGIN
-- Call the emp_infor procedure with specific employeeno and job_title parameters
CALL emp_infor(101, 'Software Engineer');
END $$;

PostgreSQL & PL/pgSQL Practice Question Bank Page 11

 IPE_10 — Count of Completed Months Between Two Dates

Write a PL/pgSQL block to find the count of completed months between two dates entered by the user.

DO $$
DECLARE
start_date DATE := '2024-01-15';
end_date DATE := '2024-08-12';
completed_months INT;
BEGIN
IF end_date < start_date THEN
RAISE EXCEPTION 'End date must be after start date';
END IF;

completed_months := (EXTRACT(YEAR FROM age(end_date, start_date)) * 12)

+ EXTRACT(MONTH FROM age(end_date, start_date));

IF EXTRACT(DAY FROM end_date) < EXTRACT(DAY FROM start_date) THEN

completed_months := completed_months - 1;
END IF;

RAISE NOTICE 'Completed Months: %', completed_months;

END $$;

PostgreSQL & PL/pgSQL Practice Question Bank Page 12

 IPE_11 — Student Marksheet Generation with Cursor

SCHEMA
Student_Marksheet (ROLLNO, SNAME, DIV, DBMS, DS, TOTAL_MARKS, PERCENTAGE, GRADE)

Write a PL/pgSQL block to generate a marksheet. Take the first five column values and calculate
TOTAL_MARKS, PERCENTAGE and GRADE using a cursor. Assume suitable data.

CREATE TABLE Student_Marksheet (

ROLLNO SERIAL PRIMARY KEY,
SNAME VARCHAR(100),
DIV CHAR(1),
DBMS INT,
DS INT,
TOTAL_MARKS INT,
PERCENTAGE NUMERIC,
GRADE CHAR(1)
);

INSERT INTO Student_Marksheet (SNAME, DIV, DBMS, DS)

VALUES
('Alice', 'A', 78, 85),
('Bob', 'B', 65, 75),
('Charlie', 'A', 90, 95),
('David', 'B', 50, 60),
('Eve', 'A', 88, 92);

DO $$
DECLARE
student_cursor CURSOR FOR
SELECT ROLLNO, SNAME, DBMS, DS
FROM Student_Marksheet;

v_rollno Student_Marksheet.ROLLNO%TYPE;
v_sname Student_Marksheet.SNAME%TYPE;
v_dbms Student_Marksheet.DBMS%TYPE;
v_ds Student_Marksheet.DS%TYPE;
v_total_marks INT;
v_percentage NUMERIC;
v_grade CHAR(1);
BEGIN
OPEN student_cursor;

LOOP
FETCH student_cursor INTO v_rollno, v_sname, v_dbms, v_ds;
EXIT WHEN NOT FOUND;

v_total_marks := v_dbms + v_ds;

v_percentage := (v_total_marks * 100) / 200; -- each subject out of 100

IF v_percentage >= 90 THEN

v_grade := 'A';
ELSIF v_percentage >= 75 THEN
v_grade := 'B';
ELSIF v_percentage >= 60 THEN
v_grade := 'C';
ELSIF v_percentage >= 50 THEN
v_grade := 'D';
ELSE
v_grade := 'F';
END IF;

PostgreSQL & PL/pgSQL Practice Question Bank Page 13

 UPDATE Student_Marksheet
SET TOTAL_MARKS = v_total_marks,
PERCENTAGE = v_percentage,
GRADE = v_grade
WHERE ROLLNO = v_rollno;

RAISE NOTICE 'ROLLNO: %, SNAME: %, TOTAL MARKS: %, PERCENTAGE: %, GRADE: %',

v_rollno, v_sname, v_total_marks, v_percentage, v_grade;
END LOOP;

CLOSE student_cursor;
END $$;

PostgreSQL & PL/pgSQL Practice Question Bank Page 14

 IPE_12 — Duplicate Product Name Trigger

SCHEMA
products (product_id, product_name)

Create a trigger that checks for a duplicate entry in product_name and raises an error before insertion of a
duplicate value.

CREATE TABLE products (

product_id SERIAL PRIMARY KEY,
product_name VARCHAR(100)
);

CREATE OR REPLACE FUNCTION check_duplicate_product()

RETURNS TRIGGER AS $$
BEGIN
-- Check if a product with the same name already exists
IF EXISTS (SELECT 1 FROM products WHERE product_name = NEW.product_name) THEN
RAISE EXCEPTION 'Duplicate product name: %', NEW.product_name;
END IF;

-- If no duplicate, allow the insertion

RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_check_duplicate_product

BEFORE INSERT ON products
FOR EACH ROW
EXECUTE FUNCTION check_duplicate_product();

PostgreSQL & PL/pgSQL Practice Question Bank Page 15

 IPE_13 — Library Book Count Decrement Trigger

SCHEMA
Books (bid, btitle, book_count)
Book_issue (id, sid, btitle)

Write a trigger for a library book management system: when a student borrows a book, the count of that
specified book should be decremented. Create tables with Primary Key, Foreign Key constraints.

CREATE OR REPLACE FUNCTION decrement_book_count()

RETURNS TRIGGER AS $$
BEGIN
-- Check if there are enough copies of the book to decrement
IF (SELECT book_count FROM Books WHERE btitle = [Link]) <= 0 THEN
RAISE EXCEPTION 'No copies available for book: %', [Link];
END IF;

-- Decrement the book count

UPDATE Books
SET book_count = book_count - 1
WHERE btitle = [Link];

RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_decrement_book_count

BEFORE INSERT ON Book_issue
FOR EACH ROW
EXECUTE FUNCTION decrement_book_count();

PostgreSQL & PL/pgSQL Practice Question Bank Page 16

 IPE_14 — Order Logging Trigger

SCHEMA
Products (product_id, product_name, price)
Orders (order_id, product_id, quantity)
Order_logs (log_id, product_id, product_name, price, quantity, order_date)

Write a trigger that logs the details of each new order, including associated product information, into
order_logs whenever a new order is inserted.

CREATE OR REPLACE FUNCTION log_order_details()

RETURNS TRIGGER AS $$
BEGIN
INSERT INTO Order_logs (product_id, product_name, price, quantity, order_date)
SELECT
NEW.product_id,
p.product_name,
[Link],
[Link],
CURRENT_TIMESTAMP
FROM Products p
WHERE p.product_id = NEW.product_id;

RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_log_order

AFTER INSERT ON Orders
FOR EACH ROW
EXECUTE FUNCTION log_order_details();

PostgreSQL & PL/pgSQL Practice Question Bank Page 17

 IPE_15 — Salary Update Monitoring Trigger

SCHEMA
Employees (EmployeeID, Name, Salary)

Write an AFTER UPDATE trigger that fires after an UPDATE on the Salary column, and calculates and
displays the old salary, new salary, and the difference for each updated record.

CREATE OR REPLACE FUNCTION log_salary_update()

RETURNS TRIGGER AS $$
BEGIN
RAISE NOTICE 'EmployeeID: %, Old Salary: %, New Salary: %, Difference: %',
[Link], [Link], [Link], [Link] - [Link];
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_log_salary_update

AFTER UPDATE OF Salary ON Employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_update();

PostgreSQL & PL/pgSQL Practice Question Bank Page 18

 IPE_16 — Temperature Conversion Function

Write a PL/pgSQL function that converts temperatures between Fahrenheit, Celsius and Kelvin, given a
temperature value, an input scale, and an output scale.

CREATE OR REPLACE FUNCTION convert_temperature(

temperature_value NUMERIC,
input_scale CHAR(1),
output_scale CHAR(1)
)
RETURNS NUMERIC AS $$
DECLARE
temp_in_celsius NUMERIC;
temp_in_fahrenheit NUMERIC;
temp_in_kelvin NUMERIC;
BEGIN
IF input_scale = 'F' THEN
temp_in_celsius := (temperature_value - 32) * 5/9;
ELSIF input_scale = 'C' THEN
temp_in_celsius := temperature_value;
ELSIF input_scale = 'K' THEN
temp_in_celsius := temperature_value - 273.15;
ELSE
RAISE EXCEPTION 'Invalid input scale: %', input_scale;
END IF;

IF output_scale = 'F' THEN

temp_in_fahrenheit := (temp_in_celsius * 9/5) + 32;
RETURN temp_in_fahrenheit;
ELSIF output_scale = 'C' THEN
RETURN temp_in_celsius;
ELSIF output_scale = 'K' THEN
temp_in_kelvin := temp_in_celsius + 273.15;
RETURN temp_in_kelvin;
ELSE
RAISE EXCEPTION 'Invalid output scale: %', output_scale;
END IF;
END;
$$ LANGUAGE plpgsql;

PostgreSQL & PL/pgSQL Practice Question Bank Page 19

 IPE_17 — Fetch Order Details with Cursor

SCHEMA
Products (product_id, product_name, price)
Orders (order_id, product_id, quantity)

Write a stored procedure that uses a cursor to fetch and print the details of each order, including
associated product information, ordered by order_id.

CREATE OR REPLACE PROCEDURE fetch_order_details()

LANGUAGE plpgsql
AS $$
DECLARE
order_cursor CURSOR FOR
SELECT o.order_id, o.product_id, [Link], p.product_name, [Link]
FROM Orders o
JOIN Products p ON o.product_id = p.product_id
ORDER BY o.order_id;

v_order_id Orders.order_id%TYPE;
v_product_id Products.product_id%TYPE;
v_quantity [Link]%TYPE;
v_product_name Products.product_name%TYPE;
v_price [Link]%TYPE;
v_total_cost NUMERIC(10, 2);
BEGIN
OPEN order_cursor;

LOOP
FETCH order_cursor INTO v_order_id, v_product_id, v_quantity, v_product_name, v_price;
EXIT WHEN NOT FOUND;

v_total_cost := v_quantity * v_price;

RAISE NOTICE 'Order ID: %, Product ID: %, Product Name: %, Quantity: %, Price: Rs. %,
Total Cost: Rs. %',
v_order_id, v_product_id, v_product_name, v_quantity, v_price,
v_total_cost;
END LOOP;

CLOSE order_cursor;
END $$;

PostgreSQL & PL/pgSQL Practice Question Bank Page 20

 IPE_18 — Scholarship Assignment Trigger

SCHEMA
APPLICATION (StudentID, Date, State)
COURSE (CourseID, Title, Credits)
RANKING (StudentID, Avg_score, Credits, Rank)
EXAM (CourseID, StudentID, Date, Grade)

Manage scholarship assignment through a system of triggers. Scholarships are awarded to students who
apply and, at the date of application, have taken exams for at least 50 credits with an average score of at
least 27.

CREATE OR REPLACE FUNCTION check_scholarship()

RETURNS TRIGGER AS $$
DECLARE
total_credits INT;
avg_score NUMERIC(5, 2);
BEGIN
-- Calculate the total credits for the student
SELECT SUM(Credits) INTO total_credits
FROM EXAM
WHERE StudentID = [Link];

-- Get the average score for the student

SELECT Avg_score INTO avg_score
FROM RANKING
WHERE StudentID = [Link];

-- Check if the student meets the criteria for a scholarship

IF total_credits >= 50 AND avg_score >= 27 THEN
RAISE NOTICE 'Student % is eligible for a scholarship.', [Link];
END IF;

RETURN NEW;
END;
$$ LANGUAGE plpgsql;

PostgreSQL & PL/pgSQL Practice Question Bank Page 21

 IPE_19 — Update Student Marks Procedure

SCHEMA
Result_SEM_II (EN_NO, NAME, DBMS_MARKS, DS_MARKS, JAVA_MARKS, FEE_MARKS, MATHS_MARKS)

Write a stored procedure update_marks that updates a student's marks in Result_SEM_II, handling the
error if Enrollment_no is not found. Parameters: Enrollment_no, Subject_name, New_marks.

CREATE OR REPLACE PROCEDURE update_marks(

p_enrollment_no INT,
p_subject_name TEXT,
p_new_marks INT
)
LANGUAGE plpgsql
AS $$
DECLARE
row_count INT;
BEGIN
SELECT COUNT(*) INTO row_count
FROM Result_SEM_II
WHERE EN_NO = p_enrollment_no;

IF row_count = 0 THEN
RAISE EXCEPTION 'Enrollment number % not found', p_enrollment_no;
ELSE
IF p_subject_name = 'DBMS' THEN
UPDATE Result_SEM_II SET DBMS_MARKS = p_new_marks WHERE EN_NO = p_enrollment_no;
ELSIF p_subject_name = 'DS' THEN
UPDATE Result_SEM_II SET DS_MARKS = p_new_marks WHERE EN_NO = p_enrollment_no;
ELSIF p_subject_name = 'JAVA' THEN
UPDATE Result_SEM_II SET JAVA_MARKS = p_new_marks WHERE EN_NO = p_enrollment_no;
ELSIF p_subject_name = 'FEE' THEN
UPDATE Result_SEM_II SET FEE_MARKS = p_new_marks WHERE EN_NO = p_enrollment_no;
ELSIF p_subject_name = 'MATHS' THEN
UPDATE Result_SEM_II SET MATHS_MARKS = p_new_marks WHERE EN_NO = p_enrollment_no;
ELSE
RAISE EXCEPTION 'Invalid subject name %', p_subject_name;
END IF;
END IF;
END;
$$;

PostgreSQL & PL/pgSQL Practice Question Bank Page 22

 IPE_20 — Employee Mobile Number Update Trigger

SCHEMA
Emp (id, fname, mno)
Emp_log (id, fname, old_mno, new_mno, update_time)

Create a trigger that, while updating an employee's mobile number, stores the old and new number into
Emp_log with employee id, fname, old_mno, new_mno and update_time. If the updated number is the
same as the old number, display a notice that "mobile number cannot be updated" and Emp_log should
not be updated.

CREATE TABLE Emp (

id INT PRIMARY KEY,
fname VARCHAR(100),
mno VARCHAR(15)
);

CREATE TABLE Emp_log (

id INT,
fname VARCHAR(100),
old_mno VARCHAR(15),
new_mno VARCHAR(15),
update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO Emp (id, fname, mno) VALUES

(1, 'John', '1234567890'),
(2, 'Alice', '9876543210'),
(3, 'Bob', '5555555555'),
(4, 'Charlie', '6666666666'),
(5, 'David', '7777777777');

CREATE OR REPLACE FUNCTION update_mobile_trigger()

RETURNS TRIGGER AS $$
BEGIN
IF [Link] = [Link] THEN
RAISE NOTICE 'Mobile number cannot be updated to the same value';
RETURN NULL; -- Prevent the update and logging
ELSE
INSERT INTO Emp_log (id, fname, old_mno, new_mno, update_time)
VALUES ([Link], [Link], [Link], [Link], CURRENT_TIMESTAMP);
RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_mobile

BEFORE UPDATE OF mno ON Emp
FOR EACH ROW
EXECUTE FUNCTION update_mobile_trigger();

PostgreSQL & PL/pgSQL Practice Question Bank Page 23

 IPE_21 — Movies Rented in the Last Month

SCHEMA
movies (movie_id, title, release_date)
rentals (rental_id, movie_id, rental_date)

Write a PL/pgSQL cursor to fetch the titles of movies that were rented out in the last one month from the
current date.

CREATE TABLE movies (

movie_id SERIAL PRIMARY KEY,
title VARCHAR(255) NOT NULL,
release_date DATE
);

CREATE TABLE rentals (

rental_id SERIAL PRIMARY KEY,
movie_id INT REFERENCES movies(movie_id),
rental_date DATE NOT NULL
);

INSERT INTO movies (title, release_date) VALUES

('The Matrix', '1999-03-31'),
('Inception', '2010-07-16'),
('Interstellar', '2014-11-07'),
('The Godfather', '1972-03-24');

INSERT INTO rentals (movie_id, rental_date) VALUES

(1, CURRENT_DATE - INTERVAL '10 days'),
(2, CURRENT_DATE - INTERVAL '15 days'),
(3, CURRENT_DATE - INTERVAL '40 days'),
(4, CURRENT_DATE - INTERVAL '5 days');

DO $$
DECLARE
movie_cursor CURSOR FOR
SELECT title
FROM movies
INNER JOIN rentals ON movies.movie_id = rentals.movie_id
WHERE rentals.rental_date >= CURRENT_DATE - INTERVAL '1 month'
ORDER BY rentals.rental_date DESC;

v_title [Link]%TYPE;
BEGIN
OPEN movie_cursor;

LOOP
FETCH movie_cursor INTO v_title;
EXIT WHEN NOT FOUND;

RAISE NOTICE 'Movie Title: %', v_title;

END LOOP;

CLOSE movie_cursor;
END $$;

PostgreSQL & PL/pgSQL Practice Question Bank Page 24

 IPE_22 — Average Salary by Department

SCHEMA
employees (employee_id, name, department_id, salary, hire_date)

Create a function to calculate the average salary of employees for a department ID given by the user.

CREATE OR REPLACE FUNCTION avg_salary_by_dept(dept_id INT)

RETURNS NUMERIC AS $$
DECLARE
avg_salary NUMERIC;
BEGIN
-- Calculate the average salary for the given department
SELECT AVG(salary) INTO avg_salary
FROM employees
WHERE department_id = dept_id;

RETURN avg_salary;
END;
$$ LANGUAGE plpgsql;

PostgreSQL & PL/pgSQL Practice Question Bank Page 25