i Write a PL/PGSQL program that retrieves all the records from the
"Student" table and displays the student details
-- Create a function to retrieve and display student details
CREATE OR REPLACE FUNCTION gsd3()
RETURNS record AS
$$
DECLARE
student_record RECORD;
BEGIN
FOR student_record IN SELECT * FROM student LOOP
-- Display the student details
RAISE NOTICE 'regno: %', student_record.regno;
RAISE NOTICE 'name: %', student_record.name;
RAISE NOTICE 'address: %', student_record.address;
RAISE NOTICE 'phone: %', student_record.phone;
RAISE NOTICE 'Age: %', student_record.age;
RAISE NOTICE '-------------------------';
END LOOP;
END;
$$
LANGUAGE plpgsql;
//call the function
select gsd3()
ii. Create a PL/PGSQL procedure that takes a student's registration
number as input and updates their age to 20. Test the procedure by
updating the age of a specific student
create or replace procedure usa1(regno1 int,age1 int)
language plpgsql
as $$
begin
update student
set age = age1
where regno = regno1;
commit;
end;$$;
call usa1(107,20)
select * from student
iii Develop a PL/PGSQL function that calculates the average age of all
students in the "Student" table and returns the result.
-- Create a PL/pgSQL function to calculate the average age of students
CREATE OR REPLACE FUNCTION avgage()
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
total_age NUMERIC := 0;
student_count INT := 0;
average_age NUMERIC := 0;
BEGIN
-- Calculate the total age and count of students
SELECT SUM(Age), COUNT(*) INTO total_age, student_count
FROM student;
-- Calculate the average age (avoid division by zero)
IF student_count > 0 THEN
average_age := total_age / student_count;
END IF;
-- Return the calculated average age
RETURN average_age;
END;
$$;
select avgage()
iv Create a PL/PGSQL procedure that inserts a new row into the "Course"
table, enrolling a student with a given registration number into a
specified course. Test the procedure by enrolling a student in a course
-- Create a PL/pgSQL procedure to enroll a student in a course
CREATE OR REPLACE PROCEDURE enroll2(
courseid1 INT,
regno1 INT
)
LANGUAGE plpgsql
AS $$
BEGIN
-- Check if the student with the given registration number exists
IF NOT EXISTS (SELECT 1 FROM Student WHERE regno = regno1) THEN
RAISE EXCEPTION 'Student with Registration Number % does not exist.', regno1;
END IF;
-- Check if the course with the given course ID exists
IF NOT EXISTS (SELECT 1 FROM Course WHERE courseid = courseid1) THEN
RAISE EXCEPTION 'Course with Course ID % does not exist.', courseid1;
END IF;
-- Insert a new row into the Course table to enroll the student in the course
INSERT INTO course (regno, courseid)
VALUES ((SELECT regno FROM Student WHERE regno = regno1), courseid1);
-- Commit the transaction
COMMIT;
END;
$$;
call enroll2(2,104)
select * from course
V Write a PL/PGSQL function that retrieves the count of students in the
"Student" table and returns the result.
CREATE OR REPLACE FUNCTION get_student_count()
RETURNS INTEGER AS
$$
DECLARE
student_count INTEGER;
BEGIN
-- Use the SELECT COUNT(*) query to retrieve the count of students
SELECT COUNT(*) INTO student_count FROM student;
-- Return the count
RETURN student_count;
END;
$$
LANGUAGE plpgsql;
SELECT get_student_count();
VI. Develop a PL/PGSQL procedure that deletes a student's record from
the "Student" table based on their registration number. Test the
procedure by deleting a specific student's record
CREATE OR REPLACE PROCEDURE delete_student1(
regno1 int
)
LANGUAGE plpgsql
AS $$
BEGIN
DELETE FROM student WHERE regno = regno1;
COMMIT;
END;
$$;
CALL delete_student1(105);
VII Develop a PL/SQL procedure that updates the address of a student
based on their registration number. Test the procedure by updating a
specific student's address.
CREATE OR REPLACE PROCEDURE update_student_address(
p_reg_number INT,
p_new_address VARCHAR
LANGUAGE plpgsql
AS $$
BEGIN
-- Update the student's address
UPDATE students
SET address = p_new_address
WHERE reg_number = p_reg_number;
-- Check if the row was updated
IF NOT FOUND THEN
RAISE NOTICE 'Student with registration number % not found.',
p_reg_number;
ELSE
RAISE NOTICE 'Address updated successfully for student with registration
number %.', p_reg_number;
END IF;
END;
$$;
Test the Procedure
INSERT INTO students (name, address) VALUES ('John Doe', '123 Main St');
CALL update_student_address(1, '456 Elm St');
SELECT * FROM students WHERE reg_number = 1;
VIII. Write a PL/SQL program that accepts an age as input and retrieves
all the student details from the "Student" table with that age
DO $$
DECLARE
v_age INT;
student_record RECORD;
BEGIN
-- Accept age as input
v_age := 20; -- Replace with the desired age or use an input method if available
-- Loop through each student with the given age
FOR student_record IN
SELECT reg_number, name, age, address
FROM Student
WHERE age = v_age
LOOP
-- Display the student details
RAISE NOTICE 'Reg Number: %, Name: %, Age: %, Address: %',
student_record.reg_number,
student_record.name,
student_record.age,
student_record.address;
END LOOP;
-- If no students are found, display a notice
IF NOT FOUND THEN
RAISE NOTICE 'No students found with age %.', v_age;
END IF;
END $$;
