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!
Hi,
For this proble I need to create a table called instructor_course_nums within a university
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!
Solution:
The code for above questions is given below.
Trending now
This is a popular solution!
Step by step
Solved in 3 steps
