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.
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;
$$;
![2
3
CREATE TEMPORARY TABLE instructor_course_nums (
4
ID VARCHAR (5),
name VARCHAR (20),
tot_courses INTEGER
7
) ;
8.
9.
CREATE OR REPLACE PROCEDURE Moreno_03_insCourseNumsProc (INOUT i_ID VARCHAR (5))
10
LANGUAGE PLPGSQL
11
AS
12
$$
13 ▼
BEGIN
14
SELECT name INTO instructor.name FROM instructor WHERE instructor.jd
i_id;
15
SELECT COUNT (*) INT0 course FROM teaches WHERE teaches.id =
i_id;
16
SELECT COUNT ( *) INTO l_Exist_count FROM instructor_course_nums WHERE instructor_id= i_id;
17
END;
18
$$;
Data Output Explain Messages
Notifications
ERROR:
"instructor.name" is not a known variable
LINE 12:
SELECT name INTO instructor.name FROM instructor WHERE ins...
SQL state: 42601
Character: 234](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F38747a86-d5c9-4d7f-9fb1-5541ee09ebb8%2Fb8c63a5f-f6d0-4bc2-afcf-1c3504ab28fc%2Fsrqpsrj_processed.png&w=3840&q=75)
![4. For this problem create a table called instructor_course_nums. 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. 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. You must name your procedure:
<LastName>_<DOB_Day0fMonth>_insCourseNumsProc
Where <LastName> is your last name and <DOB_DayofMonth> is the day of the month
you were born. Below is an example of how I named my procedure:
Morabito 05 inscourseNumsProc](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F38747a86-d5c9-4d7f-9fb1-5541ee09ebb8%2Fb8c63a5f-f6d0-4bc2-afcf-1c3504ab28fc%2Ftt7kbh_processed.png&w=3840&q=75)
![](/static/compass_v2/shared-icons/check-mark.png)
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
![Blurred answer](/static/compass_v2/solution-images/blurred-answer.jpg)
![Computer Networking: A Top-Down Approach (7th Edi…](https://www.bartleby.com/isbn_cover_images/9780133594140/9780133594140_smallCoverImage.gif)
![Computer Organization and Design MIPS Edition, Fi…](https://www.bartleby.com/isbn_cover_images/9780124077263/9780124077263_smallCoverImage.gif)
![Network+ Guide to Networks (MindTap Course List)](https://www.bartleby.com/isbn_cover_images/9781337569330/9781337569330_smallCoverImage.gif)
![Computer Networking: A Top-Down Approach (7th Edi…](https://www.bartleby.com/isbn_cover_images/9780133594140/9780133594140_smallCoverImage.gif)
![Computer Organization and Design MIPS Edition, Fi…](https://www.bartleby.com/isbn_cover_images/9780124077263/9780124077263_smallCoverImage.gif)
![Network+ Guide to Networks (MindTap Course List)](https://www.bartleby.com/isbn_cover_images/9781337569330/9781337569330_smallCoverImage.gif)
![Concepts of Database Management](https://www.bartleby.com/isbn_cover_images/9781337093422/9781337093422_smallCoverImage.gif)
![Prelude to Programming](https://www.bartleby.com/isbn_cover_images/9780133750423/9780133750423_smallCoverImage.jpg)
![Sc Business Data Communications and Networking, T…](https://www.bartleby.com/isbn_cover_images/9781119368830/9781119368830_smallCoverImage.gif)