Assignment 4: Handling errors and Exceptions
SET A
Project-Employee database
Consider the following database:
Project (pno int, pname char (30), ptype char (20), duration int)
Employee (empno int, ename char (20), joining_date date)
The relationship between Project and Employee is many to many with descriptive attribute
start_date.
Create the above database in PostGreSQL and insert sufficient records.
a. Write a stored function to accept project name as input and print the names of employees
working on the project. Also print the total number of employees working on that project.
Raise an exception for an invalid project name.
b. Write a stored function to accept empno as an input parameter from the user and count the
number of projects of a given employee. Raise an exception if the employee number is invalid.
1. Database Creation (PostgreSQL)
Since Project and Employee have a many-to-many relationship with descriptive attribute
start_date, a relationship table is required.
-- Project Table
CREATE TABLE Project (
pno INT PRIMARY KEY,
pname CHAR(30),
ptype CHAR(20),
duration INT
);
-- Employee Table
CREATE TABLE Employee (
empno INT PRIMARY KEY,
ename CHAR(20),
joining_date DATE
);
-- Relationship Table with descriptive attribute
CREATE TABLE Project_Employee (
pno INT,
empno INT,
start_date DATE,
PRIMARY KEY (pno, empno),
FOREIGN KEY (pno) REFERENCES Project(pno),
FOREIGN KEY (empno) REFERENCES Employee(empno)
);
2. Insert Sufficient Records
-- Insert into Project
INSERT INTO Project VALUES
(1, 'ERP System', 'Software', 12),
(2, 'Website Dev', 'Web', 6),
(3, 'AI Research', 'Research', 18);
-- Insert into Employee
INSERT INTO Employee VALUES
(101, 'Amit', '2020-01-10'),
(102, 'Sneha', '2019-07-15'),
(103, 'Rahul', '2021-03-20'),
(104, 'Neha', '2022-06-01');
-- Insert into Project_Employee
INSERT INTO Project_Employee VALUES
(1, 101, '2022-01-01'),
(1, 102, '2022-01-15'),
(2, 101, '2023-02-01'),
(2, 103, '2023-02-10'),
(3, 102, '2023-03-01'),
(3, 104, '2023-03-15');
3. Stored Functions
a) Stored function to accept project name
Display employee names and total count Raise exception for invalid project name
CREATE OR REPLACE FUNCTION employees_by_project(prj_name CHAR)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
rec RECORD;
emp_count INT;
prj_no INT;
emp_cursor CURSOR FOR
SELECT e.ename
FROM Employee e
JOIN Project_Employee pe ON e.empno = pe.empno
WHERE pe.pno = prj_no;
BEGIN
-- Check if project exists
SELECT pno INTO prj_no
FROM Project
WHERE pname = prj_name;
IF NOT FOUND THEN
RAISE EXCEPTION 'Invalid Project Name: %', prj_name;
END IF;
OPEN emp_cursor;
emp_count := 0;
LOOP
FETCH emp_cursor INTO rec;
EXIT WHEN NOT FOUND;
emp_count := emp_count + 1;
RAISE NOTICE 'Employee Name: %', rec.ename;
END LOOP;
CLOSE emp_cursor;
RAISE NOTICE 'Total Employees Working on % : %', prj_name, emp_count;
END;
$$;
Execution
SELECT employees_by_project('ERP System');
b) Stored function to accept empno
Count number of projects Raise exception for invalid employee number
CREATE OR REPLACE FUNCTION project_count_by_employee(e_no INT)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
proj_count INT;
BEGIN
-- Validate employee
IF NOT EXISTS (SELECT 1 FROM Employee WHERE empno = e_no) THEN
RAISE EXCEPTION 'Invalid Employee Number: %', e_no;
END IF;
SELECT COUNT(*) INTO proj_count
FROM Project_Employee
WHERE empno = e_no;
RETURN proj_count;
END;
$$;
Execution
SELECT project_count_by_employee(101);
SET B
Person – Area database
Person (pno int, name varchar (20), birthdate date, income money) Area
(aid int, aname varchar (20), area_type varchar (5))
The person and area related to many to one relationship. The attribute ‘area_type’ can have values
either ‘urban’ or ‘rural’.
Create the above database in PostGreSQL and insert sufficient records.
a. Write a stored function that accepts the area name as an input parameter from the user
and displays the details of persons living in that area. Raise an exception if area name is invalid.
-- Area Table
CREATE TABLE Area (
aid INT PRIMARY KEY,
aname VARCHAR(20),
area_type VARCHAR(5)
CHECK (area_type IN ('urban', 'rural'))
);
-- Person Table
CREATE TABLE Person (
pno INT PRIMARY KEY,
name VARCHAR(20),
birthdate DATE,
income MONEY,
aid INT,
FOREIGN KEY (aid) REFERENCES Area(aid)
);
2. Insert Sufficient Records
-- Insert into Area
INSERT INTO Area VALUES
(1, 'Nashik', 'urban'),
(2, 'Lasalgaon', 'rural'),
(3, 'Pune', 'urban');
-- Insert into Person
INSERT INTO Person VALUES
(101, 'Amit', '1995-03-15', 75000, 1),
(102, 'Sneha', '1998-07-10', 52000, 2),
(103, 'Rahul', '1996-03-25', 98000, 3),
(104, 'Neha', '1997-12-05', 45000, 1),
(105, 'Suresh','1994-07-18', 60000, 3);
3. Stored Function
Accept area name and display details of persons living in that area Raise exception for invalid area name
sql
CREATE OR REPLACE FUNCTION persons_by_area(area_name VARCHAR)
RETURNS TABLE (
pno INT,
pname VARCHAR,
birthdate DATE,
income MONEY
)
LANGUAGE plpgsql
AS $$
DECLARE
area_id INT;
BEGIN
-- Validate area name
SELECT aid INTO area_id
FROM Area
WHERE aname = area_name;
IF NOT FOUND THEN
RAISE EXCEPTION 'Invalid Area Name: %', area_name;
END IF;
-- Return person details
RETURN QUERY
SELECT p.pno, p.name, p.birthdate, p.income
FROM Person p
WHERE p.aid = area_id;
END;
$$;
4. Execution
SELECT * FROM persons_by_area('Nashik');
SELECT * FROM persons_by_area('Mumbai');
Output:
ERROR: Invalid Area Name: Mumbai
SET C
Wholesaler – Product database
Consider the following entities and their relationships.
Wholesaler (w_no, w_name, address, city)
Product (product_no, product_name, rate)
Relation between Wholesaler and Product is Many to Many with quantity as descriptive attribute.
Create the above database in PostGreSQL and insert sufficient records.
a. Write a function to accept quantity from user. Quantity must be within range 50-200. If user
enters the quantity out of range, then raise a user defined exception “quantity_out_of _range”
otherwise enter the record in table.
b. Write a function which accept rate from user. If user enters rate less than or equal to zero then
raise an user defined exception “Invalid_Rate_Value” otherwise display message “Correct Input”.
c. Write a function to accept product name as parameter. If entered product name is not valid then
raise an user defined exception”Invalid_Product_Name” otherwise display product details of
Specified product.
-- Wholesaler Table
CREATE TABLE Wholesaler (
w_no INT PRIMARY KEY,
w_name VARCHAR(50),
address VARCHAR(100),
city VARCHAR(30)
);
-- Product Table
CREATE TABLE Product (
product_no INT PRIMARY KEY,
product_name VARCHAR(50),
rate NUMERIC(10,2)
);
-- Junction Table with descriptive attribute Quantity
CREATE TABLE Wholesaler_Product (
w_no INT,
product_no INT,
quantity INT,
PRIMARY KEY (w_no, product_no),
FOREIGN KEY (w_no) REFERENCES Wholesaler(w_no),
FOREIGN KEY (product_no) REFERENCES Product(product_no)
);
2. Insert Sample Records
-- Insert into Wholesaler
INSERT INTO Wholesaler VALUES
(1, 'ABC Traders', '123 Main Street', 'Mumbai'),
(2, 'XYZ Enterprises', '456 Park Avenue', 'Pune');
-- Insert into Product
INSERT INTO Product VALUES
(101, 'Laptop', 50000),
(102, 'Mobile', 15000),
(103, 'Printer', 8000);
-- Sample Junction Table Entry (quantity between 50-200)
INSERT INTO Wholesaler_Product VALUES
(1, 101, 100),
(1, 102, 150),
(2, 102, 50);
3. Stored Functions with User-Defined Exceptions
a) Function to accept quantity Must be between 50-200 Raise exception quantity_out_of_range if
invalid Otherwise insert record into Wholesaler_Product
sql
CREATE OR REPLACE FUNCTION insert_wholesaler_product(
w INT, p INT, q INT
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
IF q < 50 OR q > 200 THEN
RAISE EXCEPTION 'quantity_out_of_range: %', q;
END IF;
-- Insert into Wholesaler_Product
INSERT INTO Wholesaler_Product (w_no, product_no, quantity)
VALUES (w, p, q);
RAISE NOTICE 'Record inserted successfully: Wholesaler %, Product %, Quantity %', w, p, q;
END;
$$;
Execution Examples
sql
-- Valid quantity
SELECT insert_wholesaler_product(2, 103, 100);
-- Invalid quantity
SELECT insert_wholesaler_product(2, 103, 30);
-- ERROR: quantity_out_of_range: 30
b) Function to accept rate If rate ≤ 0, raise Invalid_Rate_Value Otherwise display “Correct Input”
CREATE OR REPLACE FUNCTION validate_rate(r NUMERIC)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
IF r <= 0 THEN
RAISE EXCEPTION 'Invalid_Rate_Value: %', r;
ELSE
RAISE NOTICE 'Correct Input';
END IF;
END;
$$;
Execution Examples
sql
SELECT validate_rate(15000);
-- NOTICE: Correct Input
SELECT validate_rate(-500);
-- ERROR: Invalid_Rate_Value: -500
c) Function to accept product_name If product name does not exist, raise Invalid_Product_Name
Otherwise display product details
sql
CREATE OR REPLACE FUNCTION product_details_by_name(pname VARCHAR)
RETURNS TABLE (
product_no INT,
product_name VARCHAR,
rate NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
rec RECORD;
BEGIN
-- Check if product exists
SELECT * INTO rec
FROM Product
WHERE product_name = pname;
IF NOT FOUND THEN
RAISE EXCEPTION 'Invalid_Product_Name: %', pname;
END IF;
-- Return product details
RETURN QUERY
SELECT product_no, product_name, rate
FROM Product
WHERE product_name = pname;
END;
$$;
Execution Examples
SELECT * FROM product_details_by_name('Laptop');
SELECT * FROM product_details_by_name('Tablet');
-- ERROR: Invalid_Product_Name: Tablet
SET D
Student Teacher Database
Student (sno integer, s_name char(30), s_class char(10), s_addr Char(50))
Teacher (tno integer, t_name char (20), qualification char (15), experience integer)
The relationship is as follows:
Student-Teacher: Many to Many with descriptive attribute Subject.
a. Write a stored function to count the number of the teachers teaching to a student named “ ”. (Accept student name as input parameter). Raise an exception if student name does not exist.
b. Write a stored function to count the number of the students who are studying subject named “ ” (Accept subject name as input parameter). Display error message if subject name is not valid.
c. Write a stored function to display teacher details who have qualification as “ ” (Accept teacher’s qualification as input parameter). Raise an exception for invalid qualification.
-- Student Table
CREATE TABLE Student (
sno INT PRIMARY KEY,
s_name CHAR(30),
s_class CHAR(10),
s_addr CHAR(50)
);
-- Teacher Table
CREATE TABLE Teacher (
tno INT PRIMARY KEY,
t_name CHAR(20),
qualification CHAR(15),
experience INT
);
-- Junction Table with descriptive attribute Subject
CREATE TABLE Student_Teacher (
sno INT,
tno INT,
subject CHAR(30),
PRIMARY KEY (sno, tno, subject),
FOREIGN KEY (sno) REFERENCES Student(sno),
FOREIGN KEY (tno) REFERENCES Teacher(tno)
);
2. Insert Sample Records
sql
-- Insert into Student
INSERT INTO Student VALUES
(1, 'Amit', 'BScCS', '123 Main Street'),
(2, 'Sneha', 'BScIT', '456 Park Avenue'),
(3, 'Rahul', 'BCA', '789 Oak Street');
-- Insert into Teacher
INSERT INTO Teacher VALUES
(101, 'Ramesh', 'MScCS', 10),
(102, 'Suresh', 'MCA', 8),
(103, 'Mahesh', 'BScCS', 5);
-- Insert into Student_Teacher
INSERT INTO Student_Teacher VALUES
(1, 101, 'DBMS'),
(1, 102, 'AI'),
(2, 101, 'DBMS'),
(2, 103, 'ComputerNet'),
(3, 102, 'AI');
3. Stored Functions
a) Count teachers teaching a student Accept student name as input Raise exception if student name does not exist
sql
CREATE OR REPLACE FUNCTION count_teachers_by_student(student_name CHAR)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
student_id INT;
teacher_count INT;
BEGIN
-- Validate student
SELECT sno INTO student_id
FROM Student
WHERE s_name = student_name;
IF NOT FOUND THEN
RAISE EXCEPTION 'Student Name Not Found: %', student_name;
END IF;
-- Count teachers teaching this student
SELECT COUNT(DISTINCT tno) INTO teacher_count
FROM Student_Teacher
WHERE sno = student_id;
RETURN teacher_count;
END;
$$;
Execution Example
sql
SELECT count_teachers_by_student('Amit');
b) Count students studying a given subject Accept subject name as input Raise error if subject
name not found
sql
CREATE OR REPLACE FUNCTION count_students_by_subject(sub_name CHAR)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
total_students INT;
BEGIN
-- Validate subject exists
IF NOT EXISTS (
SELECT 1 FROM Student_Teacher WHERE subject = sub_name
) THEN
RAISE EXCEPTION 'Invalid Subject Name: %', sub_name;
END IF;
-- Count students
SELECT COUNT(DISTINCT sno) INTO total_students
FROM Student_Teacher
WHERE subject = sub_name;
RETURN total_students;
END;
$$;
Execution Example
sql
SELECT count_students_by_subject('DBMS');
c) Display teacher details by qualification Accept teacher qualification as input Raise exception if
qualification not found
sql
CREATE OR REPLACE FUNCTION teachers_by_qualification(q CHAR)
RETURNS TABLE (
tno INT,
t_name CHAR(20),
qualification CHAR(15),
experience INT
)
LANGUAGE plpgsql
AS $$
BEGIN
-- Validate qualification exists
IF NOT EXISTS (SELECT 1 FROM Teacher WHERE qualification = q) THEN
RAISE EXCEPTION 'Invalid Qualification: %', q;
END IF;
-- Return teacher details
RETURN QUERY
SELECT tno, t_name, qualification, experience
FROM Teacher
WHERE qualification = q;
END;
$$;
Execution Example
sql
SELECT * FROM teachers_by_qualification('MCA');
SET E
Railway Reservation System Database
TRAIN: (train_no int, train_name varchar(20), depart_time time , arrival_time time, source_stn
varchar (20),dest_stn varchar (20), no_of_res_bogies int ,bogie_capacity int)
PASSENGER : (passenger_id int, passenger_name varchar(20), address varchar(30), age int
gender char)
Relationships:
Train _Passenger: Many to Many relationship named ticket with descriptive attributes as follows
TICKET: ( train_no int, passenger_id int, ticket_no int ,bogie_no int, no_of_berths int ,tdate date ,
ticket_amt decimal(7,2),status char)
Constraints: The status of a berth can be 'W' (waiting) or 'C' (confirmed).
a. Write a stored function to print the details of train wise confirmed bookings on date “ ” (Accept
date as input parameter).Raise an error in case of invalid date.
b. Write a stored function to accept date and passenger name and display no of berths reserved
and ticket amount paid by him. Raise exception if passenger name is invalid.
c. Write a stored function to display the ticket details of a train. (Accept train name as input
parameter).Raise an exception in case of invalid train name.
-- TRAIN Table
CREATE TABLE TRAIN (
train_no INT PRIMARY KEY,
train_name VARCHAR(20),
depart_time TIME,
arrival_time TIME,
source_stn VARCHAR(20),
dest_stn VARCHAR(20),
no_of_res_bogies INT,
bogie_capacity INT
);
-- PASSENGER Table
CREATE TABLE PASSENGER (
passenger_id INT PRIMARY KEY,
passenger_name VARCHAR(20),
address VARCHAR(30),
age INT,
gender CHAR
);
-- TICKET Table (Many-to-Many relationship)
CREATE TABLE TICKET (
train_no INT,
passenger_id INT,
ticket_no INT,
bogie_no INT,
no_of_berths INT,
tdate DATE,
ticket_amt DECIMAL(7,2),
status CHAR CHECK (status IN ('W', 'C')),
PRIMARY KEY (ticket_no),
FOREIGN KEY (train_no) REFERENCES TRAIN(train_no),
FOREIGN KEY (passenger_id) REFERENCES PASSENGER(passenger_id)
);
2. Insert Sample Records
-- Insert into TRAIN
INSERT INTO TRAIN VALUES
(101, 'Express1', '06:00', '12:00', 'Mumbai', 'Delhi', 10, 50),
(102, 'Express2', '08:00', '14:00', 'Pune', 'Mumbai', 8, 40);
-- Insert into PASSENGER
INSERT INTO PASSENGER VALUES
(1, 'Amit', 'Mumbai', 30, 'M'),
(2, 'Sneha', 'Pune', 25, 'F'),
(3, 'Rahul', 'Delhi', 28, 'M');
-- Insert into TICKET
INSERT INTO TICKET VALUES
(101, 1, 1001, 1, 2, '2026-01-25', 3000, 'C'),
(101, 2, 1002, 1, 1, '2026-01-25', 1500, 'C'),
(101, 3, 1003, 2, 1, '2026-01-25', 1500, 'W'),
(102, 1, 1004, 1, 1, '2026-01-26', 2000, 'C');
3. Stored Functions
a) Train-wise confirmed bookings on a given date
sql
CREATE OR REPLACE FUNCTION confirmed_bookings_by_date(b_date DATE)
RETURNS TABLE(
train_no INT,
train_name VARCHAR,
passenger_name VARCHAR,
ticket_no INT,
bogie_no INT,
no_of_berths INT,
ticket_amt DECIMAL(7,2)
)
LANGUAGE plpgsql
AS $$
BEGIN
-- Validate date
IF b_date IS NULL THEN
RAISE EXCEPTION 'Invalid Date Input';
END IF;
RETURN QUERY
SELECT t.train_no, t.train_name, p.passenger_name, tk.ticket_no, tk.bogie_no, tk.no_of_berths,
tk.ticket_amt
FROM TRAIN t
JOIN TICKET tk ON t.train_no = tk.train_no
JOIN PASSENGER p ON tk.passenger_id = p.passenger_id
WHERE tk.tdate = b_date AND tk.status = 'C';
END;
$$;
Execution Example
sql
SELECT * FROM confirmed_bookings_by_date('2026-01-25');
b) Berths and ticket amount for a passenger on a given date
sql
CREATE OR REPLACE FUNCTION berths_by_passenger(b_date DATE, pname VARCHAR)
RETURNS TABLE(
no_of_berths INT,
ticket_amt DECIMAL(7,2)
)
LANGUAGE plpgsql
AS $$
DECLARE
pid INT;
BEGIN
-- Validate passenger
SELECT passenger_id INTO pid
FROM PASSENGER
WHERE passenger_name = pname;
IF NOT FOUND THEN
RAISE EXCEPTION 'Invalid Passenger Name: %', pname;
END IF;
RETURN QUERY
SELECT SUM(no_of_berths), SUM(ticket_amt)
FROM TICKET
WHERE passenger_id = pid AND tdate = b_date;
END;
$$;
Execution Example
sql
SELECT * FROM berths_by_passenger('2026-01-25', 'Amit');
c) Display ticket details of a train by train name
sql
CREATE OR REPLACE FUNCTION ticket_details_by_train(tname VARCHAR)
RETURNS TABLE(
ticket_no INT,
passenger_name VARCHAR,
bogie_no INT,
no_of_berths INT,
tdate DATE,
ticket_amt DECIMAL(7,2),
status CHAR
)
LANGUAGE plpgsql
AS $$
DECLARE
tno INT;
BEGIN
-- Validate train name
SELECT train_no INTO tno
FROM TRAIN
WHERE train_name = tname;
IF NOT FOUND THEN
RAISE EXCEPTION 'Invalid Train Name: %', tname;
END IF;
RETURN QUERY
SELECT tk.ticket_no, p.passenger_name, tk.bogie_no, tk.no_of_berths, tk.tdate, tk.ticket_amt,
tk.status
FROM TICKET tk
JOIN PASSENGER p ON tk.passenger_id = p.passenger_id
WHERE tk.train_no = tno;
END;
$$;
Execution Example
sql
SELECT * FROM ticket_details_by_train('Express1');