Hi,I am looking for some help to solve this question. I cannot figure out how to solve it and the code below is all I have.The application being used: PostgreSQL edition 12 or 13. I must use LANGUAGE PLPGSLQ as that is what the professor is looking for.Thank you for your help.Tables in the DB:instructor (id, name, dept_name, salary)teaches (id, course_id, sec_id, semester, year)CREATE TEMPORARY TABLE instructor_course_nums (ID VARCHAR(5),name VARCHAR(20),tot_courses INTEGER);CREATE OR REPLACE PROCEDURE Moreno_03_insCourseNumsProc(INOUT i_ID VARCHAR(5))LANGUAGE PLPGSQLAS$$BEGINSELECT name INTO instructor.name FROM instructor WHERE instructor.id = i_id;SELECT COUNT(*) INTO course FROM teaches WHERE teaches.id = i_id;SELECT COUNT(*) INTO l_Exist_count FROM instructor_course_nums WHERE instructor_id= i_id;END;$$; 23CREATE TEMPORARY TABLE instructor_course_nums (4ID VARCHAR (5),name VARCHAR (20),tot_courses INTEGER7) ;8.9.CREATE OR REPLACE PROCEDURE Moreno_03_insCourseNumsProc (INOUT i_ID VARCHAR (5))10LANGUAGE PLPGSQL11AS12$$13 ▼BEGIN14SELECT name INTO instructor.name FROM instructor WHERE instructor.jdi_id;15SELECT COUNT (*) INT0 course FROM teaches WHERE teaches.id =i_id;16SELECT COUNT ( *) INTO l_Exist_count FROM instructor_course_nums WHERE instructor_id= i_id;17END;18$$;Data Output Explain MessagesNotificationsERROR:"instructor.name" is not a known variableLINE 12:SELECT name INTO instructor.name FROM instructor WHERE ins...SQL state: 42601Character: 234 4. For this problem create a table called instructor_course_nums. Write a procedure thataccepts an instructor ID as input. The procedure calculates the total number of coursesections taught by that instructor, and adds a tuple to the instructor_course_nums tableconsisting of the instructors ID number, name, and total courses taught - call theseattributes: ID, name, and tot_courses. If the instructor already has an entry in the table,then the procedure makes sure the total number of courses taught in theinstructor_course_nums table is up-to-date. You must name your procedure:__insCourseNumsProcWhere is your last name and is the day of the monthyou were born. Below is an example of how I named my procedure:Morabito 05 inscourseNumsProc

As per our guidelines we are supposed to answer only one question. Kindly repost the other question…

Answered: I am looking for some help to solve…

Computer Networking: A Top-Down Approach (7th Edition)

7th Edition

ISBN:9780133594140

Author:James Kurose, Keith Ross

Publisher:James Kurose, Keith Ross

Chapter1: Computer Networks And The Internet

Section: Chapter Questions

Problem R1RQ: What is the difference between a host and an end system? List several different types of end...

See similar textbooks

Related questions

Question

Hi,

I am looking for some help to solve this question. I cannot figure out how to solve it and the code below is all I have.

The application being used: PostgreSQL edition 12 or 13. I must use LANGUAGE PLPGSLQ as that is what the professor is looking for.

Thank you for your help.

Tables in the DB:
instructor (id, name, dept_name, salary)
teaches (id, course_id, sec_id, semester, year)

CREATE TEMPORARY TABLE instructor_course_nums (
ID VARCHAR(5),
name VARCHAR(20),
tot_courses INTEGER
);

CREATE OR REPLACE PROCEDURE Moreno_03_insCourseNumsProc(INOUT i_ID VARCHAR(5))
LANGUAGE PLPGSQL
AS
$$
BEGIN
SELECT name INTO instructor.name FROM instructor WHERE instructor.id = i_id;
SELECT COUNT(*) INTO course FROM teaches WHERE teaches.id = i_id;
SELECT COUNT(*) INTO l_Exist_count FROM instructor_course_nums WHERE instructor_id= i_id;
END;
$$;