Assignment 3: Cursors
SET A
Bus – Route Database
Consider the following database
Bus (bus_no int, capacity int , depot_name varchar(20))
Route (route_no int, source varchar(20), destination varchar(20), No_of_stations int) Bus and
Route are related with many to many relationship.
Create the above database in PostGreSQL and insert sufficient records.
a. Write a stored function using cursor, which will give details of all routes on which bus no
108 is running.
b. Write a stored function using cursor, which will give details of all buses on route from
“Station” to “Airport”.
-- Bus Table
CREATE TABLE Bus (
bus_no INT PRIMARY KEY,
capacity INT,
depot_name VARCHAR(20)
);
-- Route Table
CREATE TABLE Route (
route_no INT PRIMARY KEY,
source VARCHAR(20),
destination VARCHAR(20),
no_of_stations INT
);
-- Junction Table for Many-to-Many Relationship
CREATE TABLE Bus_Route (
bus_no INT,
route_no INT,
PRIMARY KEY (bus_no, route_no),
FOREIGN KEY (bus_no) REFERENCES Bus(bus_no),
FOREIGN KEY (route_no) REFERENCES Route(route_no)
);
2. Insert Sufficient Records
sql
-- Insert into Bus
INSERT INTO Bus VALUES
(101, 45, 'Nashik'),
(108, 50, 'Mumbai'),
(112, 40, 'Pune'),
(120, 55, 'Nashik');
-- Insert into Route
INSERT INTO Route VALUES
(1, 'Station', 'Airport', 10),
(2, 'CityCenter', 'Station', 8),
(3, 'Station', 'BusStand', 6),
(4, 'Airport', 'CityCenter', 12);
-- Insert into Bus_Route
INSERT INTO Bus_Route VALUES
(108, 1),
(108, 2),
(101, 1),
(112, 3),
(120, 1),
(120, 4);
3. Stored Function Using Cursor
a) Details of all routes on which bus no 108 is running
sql
CREATE OR REPLACE FUNCTION routes_of_bus_108()
RETURNS TABLE (
route_no INT,
source VARCHAR,
destination VARCHAR,
no_of_stations INT
)
LANGUAGE plpgsql
AS $$
DECLARE
route_cursor CURSOR FOR
SELECT r.route_no, r.source, r.destination, r.no_of_stations
FROM Route r
JOIN Bus_Route br ON r.route_no = br.route_no
WHERE br.bus_no = 108;
rec RECORD;
BEGIN
OPEN route_cursor;
LOOP
FETCH route_cursor INTO rec;
EXIT WHEN NOT FOUND;
route_no := rec.route_no;
source := rec.source;
destination := rec.destination;
no_of_stations := rec.no_of_stations;
RETURN NEXT;
END LOOP;
CLOSE route_cursor;
END;
$$;
Execution
SELECT * FROM routes_of_bus_108();
4. Stored Function Using Cursor
b) Details of all buses running on route from “Station” to “Airport”
sql
CREATE OR REPLACE FUNCTION buses_station_to_airport()
RETURNS TABLE (
bus_no INT,
capacity INT,
depot_name VARCHAR
)
LANGUAGE plpgsql
AS $$
DECLARE
bus_cursor CURSOR FOR
SELECT b.bus_no, b.capacity, b.depot_name
FROM Bus b
JOIN Bus_Route br ON b.bus_no = br.bus_no
JOIN Route r ON br.route_no = r.route_no
WHERE r.source = 'Station'
AND r.destination = 'Airport';
rec RECORD;
BEGIN
OPEN bus_cursor;
LOOP
FETCH bus_cursor INTO rec;
EXIT WHEN NOT FOUND;
bus_no := rec.bus_no;
capacity := rec.capacity;
depot_name := rec.depot_name;
RETURN NEXT;
END LOOP;
CLOSE bus_cursor;
END;
$$;
Execution
SELECT * FROM buses_station_to_airport();
SET B
Student –Teacher database
Consider the following database
Teacher( t_no int, t_name varchar(20), age int, yr_experience int)
Subject (s_no int, s_namevarchar(15))
Teacher and Subject are related with many to many relationship
Create the above database in PostGreSQL and insert sufficient records.
a. Write a stored function using cursor which will accept the subject name and print the
names of all teachers teaching that subject.
b. Write a cursor to accept the subject's name from the user as an input and display names
of all teachers teaching that student.
-- Teacher Table
CREATE TABLE Teacher (
t_no INT PRIMARY KEY,
t_name VARCHAR(20),
age INT,
yr_experience INT
);
-- Subject Table
CREATE TABLE Subject (
s_no INT PRIMARY KEY,
s_name VARCHAR(15)
);
-- Junction Table
CREATE TABLE Teacher_Subject (
t_no INT,
s_no INT,
PRIMARY KEY (t_no, s_no),
FOREIGN KEY (t_no) REFERENCES Teacher(t_no),
FOREIGN KEY (s_no) REFERENCES Subject(s_no)
);
2. Insert Sufficient Records
sql
-- Insert into Teacher
INSERT INTO Teacher VALUES
(1, 'Anita', 40, 15),
(2, 'Rahul', 35, 10),
(3, 'Sneha', 45, 20),
(4, 'Amit', 30, 6);
-- Insert into Subject
INSERT INTO Subject VALUES
(101, 'DBMS'),
(102, 'ComputerNet'),
(103, 'AI');
-- Insert into Teacher_Subject
INSERT INTO Teacher_Subject VALUES
(1, 101),
(2, 101),
(3, 102),
(1, 103),
(4, 101),
(3, 103);
3. Stored Function Using Cursor
a) Accept subject name and display names of teachers teaching that subject
sql
CREATE OR REPLACE FUNCTION teachers_by_subject(sub_name VARCHAR)
RETURNS TABLE (
teacher_name VARCHAR
)
LANGUAGE plpgsql
AS $$
DECLARE
teacher_cursor CURSOR FOR
SELECT t.t_name
FROM Teacher t
JOIN Teacher_Subject ts ON t.t_no = ts.t_no
JOIN Subject s ON ts.s_no = s.s_no
WHERE s.s_name = sub_name;
rec RECORD;
BEGIN
OPEN teacher_cursor;
LOOP
FETCH teacher_cursor INTO rec;
EXIT WHEN NOT FOUND;
teacher_name := rec.t_name;
RETURN NEXT;
END LOOP;
CLOSE teacher_cursor;
END;
$$;
Execution
sql
SELECT * FROM teachers_by_subject('DBMS');
4. Cursor Program Accepting Subject Name from User
b) Display names of teachers teaching the given subject
(Implemented as a stored procedure using cursor and user input parameter)
sql
CREATE OR REPLACE PROCEDURE display_teachers(sub_name VARCHAR)
LANGUAGE plpgsql
AS $$
DECLARE
rec RECORD;
teacher_cursor CURSOR FOR
SELECT t.t_name
FROM Teacher t
JOIN Teacher_Subject ts ON t.t_no = ts.t_no
JOIN Subject s ON ts.s_no = s.s_no
WHERE s.s_name = sub_name;
BEGIN
OPEN teacher_cursor;
LOOP
FETCH teacher_cursor INTO rec;
EXIT WHEN NOT FOUND;
RAISE NOTICE 'Teacher Name: %', rec.t_name;
END LOOP;
CLOSE teacher_cursor;
END;
$$;
Execution
CALL display_teachers('AI');
SET C
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 cursor to accept a month as an input parameter from the user and display the
names of persons whose birthday falls in that particular month.
b. Write a cursor to display the names of persons living in urban area.
c. Write a cursor to print names of all persons having income between50,000 and 1,00,000.
-- 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
sql
-- 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. Cursor Programs
a) Cursor to accept month and display persons whose birthday falls in that month
sql
CREATE OR REPLACE PROCEDURE birthday_in_month(m INT)
LANGUAGE plpgsql
AS $$
DECLARE
rec RECORD;
bday_cursor CURSOR FOR
SELECT name
FROM Person
WHERE EXTRACT(MONTH FROM birthdate) = m;
BEGIN
OPEN bday_cursor;
LOOP
FETCH bday_cursor INTO rec;
EXIT WHEN NOT FOUND;
RAISE NOTICE 'Person Name: %', rec.name;
END LOOP;
CLOSE bday_cursor;
END;
$$;
Execution
CALL birthday_in_month(3);
b) Cursor to display names of persons living in urban area
CREATE OR REPLACE PROCEDURE persons_in_urban_area()
LANGUAGE plpgsql
AS $$
DECLARE
rec RECORD;
urban_cursor CURSOR FOR
SELECT p.name
FROM Person p
JOIN Area a ON p.aid = a.aid
WHERE a.area_type = 'urban';
BEGIN
OPEN urban_cursor;
LOOP
FETCH urban_cursor INTO rec;
EXIT WHEN NOT FOUND;
RAISE NOTICE 'Person Name: %', rec.name;
END LOOP;
CLOSE urban_cursor;
END;
$$;
Execution
sql
Copy code
CALL persons_in_urban_area();
c) Cursor to print names of persons having income between 50,000 and 1,00,000
(Money type safely cast to numeric)
CREATE OR REPLACE PROCEDURE income_range_persons()
LANGUAGE plpgsql
AS $$
DECLARE
rec RECORD;
income_cursor CURSOR FOR
SELECT name
FROM Person
WHERE income::numeric BETWEEN 50000 AND 100000;
BEGIN
OPEN income_cursor;
LOOP
FETCH income_cursor INTO rec;
EXIT WHEN NOT FOUND;
RAISE NOTICE 'Person Name: %', rec.name;
END LOOP;
CLOSE income_cursor;
END;
$$;
Execution
CALL income_range_persons();
SET D
Student – Competition Database
Consider the following entities and their relationship.
Student (s_reg_no int, s_name varchar(20), s_class varchar(20))
Competition (comp_no int, comp_name varchar(20), comp_type varchar(20))
Relationship between Student and Competition is many-to-many with descriptive attribute rank
and year.
a) Write a cursor which will display year wise details of competitions held. (Use
parameterized cursor)
b) Write a cursor which will display student wise total count of competition participated.
-- Student Table
CREATE TABLE Student (
s_reg_no INT PRIMARY KEY,
s_name VARCHAR(20),
s_class VARCHAR(20)
);
-- Competition Table
CREATE TABLE Competition (
comp_no INT PRIMARY KEY,
comp_name VARCHAR(20),
comp_type VARCHAR(20)
);
-- Relationship Table with descriptive attributes
CREATE TABLE Student_Competition (
s_reg_no INT,
comp_no INT,
rank INT,
year INT,
PRIMARY KEY (s_reg_no, comp_no, year),
FOREIGN KEY (s_reg_no) REFERENCES Student(s_reg_no),
FOREIGN KEY (comp_no) REFERENCES Competition(comp_no)
);
2. Insert Sufficient Records
sql
-- Insert into Student
INSERT INTO Student VALUES
(1, 'Amit', 'BSc CS'),
(2, 'Sneha', 'BSc IT'),
(3, 'Rahul', 'BCA');
-- Insert into Competition
INSERT INTO Competition VALUES
(101, 'CodeFest', 'Technical'),
(102, 'QuizMania', 'Academic'),
(103, 'Hackathon', 'Technical');
-- Insert into Student_Competition
INSERT INTO Student_Competition VALUES
(1, 101, 1, 2023),
(1, 102, 2, 2023),
(2, 101, 3, 2022),
(2, 103, 1, 2023),
(3, 102, 1, 2022),
(3, 103, 2, 2023);
3. Cursor Programs
a) Parameterized Cursor to display year-wise competition details
CREATE OR REPLACE PROCEDURE competitions_by_year(y INT)
LANGUAGE plpgsql
AS $$
DECLARE
rec RECORD;
comp_cursor CURSOR (yr INT) FOR
SELECT DISTINCT c.comp_name, c.comp_type, sc.year
FROM Competition c
JOIN Student_Competition sc ON c.comp_no = sc.comp_no
WHERE sc.year = yr;
BEGIN
OPEN comp_cursor(y);
LOOP
FETCH comp_cursor INTO rec;
EXIT WHEN NOT FOUND;
RAISE NOTICE 'Year: %, Competition: %, Type: %',
rec.year, rec.comp_name, rec.comp_type;
END LOOP;
CLOSE comp_cursor;
END;
$$;
Execution
CALL competitions_by_year(2023);
b) Cursor to display student-wise total count of competitions participated
CREATE OR REPLACE PROCEDURE student_competition_count()
LANGUAGE plpgsql
AS $$
DECLARE
rec RECORD;
count_cursor CURSOR FOR
SELECT s.s_name, COUNT(sc.comp_no) AS total_competitions
FROM Student s
LEFT JOIN Student_Competition sc
ON s.s_reg_no = sc.s_reg_no
GROUP BY s.s_name;
BEGIN
OPEN count_cursor;
LOOP
FETCH count_cursor INTO rec;
EXIT WHEN NOT FOUND;
RAISE NOTICE 'Student: %, Competitions Participated: %',
rec.s_name, rec.total_competitions;
END LOOP;
CLOSE count_cursor;
END;
$$;
Execution
CALL student_competition_count();
SET E
Car – Driver Database
Consider the following database:
Car (c_no int, owner varchar(20), model varchar(10), color varchar(10)
Driver (driver_no int, driver_namevarchar(20), license_no int, d_age int, salary float) Car
and Driver are related with many to many relationship
Create the above database in PostGreSQL and insert sufficient records.
a. Write a stored function with cursor which accepts the color and prints the names of all owners
who own a car of that color.
b. Write a cursor which accepts the driver name and prints the details of all cars that this driver
has driven, if the driver name is invalid, print an appropriate message.
-- Car Table
CREATE TABLE Car (
c_no INT PRIMARY KEY,
owner VARCHAR(20),
model VARCHAR(10),
color VARCHAR(10)
);
-- Driver Table
CREATE TABLE Driver (
driver_no INT PRIMARY KEY,
driver_name VARCHAR(20),
license_no INT,
d_age INT,
salary FLOAT
);
-- Junction Table
CREATE TABLE Car_Driver (
c_no INT,
driver_no INT,
PRIMARY KEY (c_no, driver_no),
FOREIGN KEY (c_no) REFERENCES Car(c_no),
FOREIGN KEY (driver_no) REFERENCES Driver(driver_no)
);
2. Insert Sufficient Records
-- Insert into Car
INSERT INTO Car VALUES
(1, 'Amit', 'Swift', 'Red'),
(2, 'Neha', 'City', 'White'),
(3, 'Rahul', 'i20', 'Red'),
(4, 'Sneha', 'Creta', 'Black');
-- Insert into Driver
INSERT INTO Driver VALUES
(101, 'Ramesh', 5551, 35, 25000),
(102, 'Suresh', 5552, 40, 30000),
(103, 'Mahesh', 5553, 30, 22000);
-- Insert into Car_Driver
INSERT INTO Car_Driver VALUES
(1, 101),
(2, 101),
(3, 102),
(4, 103),
(1, 102);
3. Cursor-Based Programs
a) Stored function using cursor Accept color and print names of owners who own a car of that color
CREATE OR REPLACE FUNCTION owners_by_color(c_color VARCHAR)
RETURNS TABLE (
owner_name VARCHAR
)
LANGUAGE plpgsql
AS $$
DECLARE
rec RECORD;
owner_cursor CURSOR FOR
SELECT DISTINCT owner
FROM Car
WHERE color = c_color;
BEGIN
OPEN owner_cursor;
LOOP
FETCH owner_cursor INTO rec;
EXIT WHEN NOT FOUND;
owner_name := rec.owner;
RETURN NEXT;
END LOOP;
CLOSE owner_cursor;
END;
$$;
Execution
SELECT * FROM owners_by_color('Red');
b) Cursor accepting driver name and displaying details of all cars driven If driver name is invalid,
print an appropriate message
CREATE OR REPLACE PROCEDURE cars_driven_by_driver(dname VARCHAR)
LANGUAGE plpgsql
AS $$
DECLARE
rec RECORD;
driver_count INT;
car_cursor CURSOR FOR
SELECT c.c_no, c.owner, c.model, c.color
FROM Car c
JOIN Car_Driver cd ON c.c_no = cd.c_no
JOIN Driver d ON cd.driver_no = d.driver_no
WHERE d.driver_name = dname;
BEGIN
-- Check if driver exists
SELECT COUNT(*) INTO driver_count
FROM Driver
WHERE driver_name = dname;
IF driver_count = 0 THEN
RAISE NOTICE 'Invalid Driver Name: %', dname;
RETURN;
END IF;
OPEN car_cursor;
LOOP
FETCH car_cursor INTO rec;
EXIT WHEN NOT FOUND;
RAISE NOTICE 'Car No: %, Owner: %, Model: %, Color: %',
rec.c_no, rec.owner, rec.model, rec.color;
END LOOP;
CLOSE car_cursor;
END;
$$;
Execution
sql
CALL cars_driven_by_driver('Ramesh');
sql
CALL cars_driven_by_driver('Unknown');
No comments:
Post a Comment