Hi,
For this proble I need to create a table called instructor_course_nums within a university database schema.
1. I need to write a procedure that accepts an instructor ID as input. The procedure calculates the total number of course sections taught by that instructor, and adds a tuple to the instructor_course_nums table consisting of the instructors ID number, name, and total courses taught - call these attributes: ID, name, and tot_courses.
2. If the instructor already has an entry in the table, then the procedure makes sure the total number of courses taught in the instructor_course_nums table is up-to-date.
Here is what I have that answers the first part of the question:
CREATE TABLE instructor_course_nums (
id varchar (10),
name varchar (30),
tot_courses integer,
CONSTRAINT instructor_course_nums_pkey PRIMARY KEY (id));
CREATE OR REPLACE PROCEDURE Student_00_insCourseNumsProc(ID varchar(10), name varchar (25), tot_courses varchar (25))
LANGUAGE plpgsql AS $$
BEGIN
SELECT name INTO instructor_course_nums
FROM instructor
WHERE ID = instructor.ID;
SELECT COUNT(*) INTO instructor_course_nums
FROM teaches
WHERE instructor_course_nums.id = teaches.ID;
END;
$$;
CALL Student_00_insCourseNumsProc('');
The code I have here the prof in the new table with the total number of courses they've taught. My issue is that I am unable to figure out how to get the table to update when new information is added. Help is gratly appreciated! Thank you!