Answered: I am working on the stored procedure.…

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Question

Hello,

I am working on the stored procedure. However, I am stuck a little and cannot figure out how to get my procedure to run.

I did the first part of the question, which is to create the table instructor_course_nums. Then I went on to write the second portion, but I am stuck.

Could someone please take a look the stored procedure and help me to get it to run?

Code

---created table here as the first part of the problem
CREATE TABLE instructor_course_nums (
ID_Number INTEGER,
name VARCHAR(30),
tot_courses VARCHAR(30),
CONSTRAINT instructor_course_nums_pkey PRIMARY KEY (ID_Number)
);

--- second part of the problem. See procedure below
CREATE OR REPLACE PROCEDURE Moreno_03_insCourseNumsProc(INOUT i_ID VARCHAR(5))
LANGUAGE PLPGSQL
AS
$$

BEGIN
SELECT name INTO instructor_course_nums FROM instructor WHERE instructor.id = i_id;
SELECT COUNT(*) INTO instructor_course_nums FROM teaches WHERE teaches.id = i_id;
SELECT COUNT(*) INTO instructor_course_nums FROM instructor_course_nums WHERE instructor_id= i_id;
END;
$$;

Thank you so much!

---created table here as first part of the problem
2
CREATE TABLE instructor_course_nums (
ID_Number INTEGER,
4
name VARCHAR (30),
tot_courses VARCHAR (30),
6.
CONSTRAINT instructor_course_nums_pkey PRIMARY KEY (ID_Number)
7
8
9.
second part of the problem. See procedure below
---
10
CREATE OR REPLACE PROCEDURE Moreno_03_insCourseNumsProc(INOUT i_ID VARCHAR (5))
11
LANGUAGE PLPGSQL
12
AS
13
$$
14 ▼
BEGIN
15
SELECT name INTO instructor_course_nums FROM instructor WHERE instructor.jd =
i_id;
16
SELECT COUNT ( *) INT0 instructor_course_nums FROM teaches WHERE teaches.id =
i_id;
17
SELECT COUNT ( *) INTO instructor_course_nums FROM instructor_course_nums WHERE instructor_id= i_id;
18
END;
19
$3;
Data Output Explain Messages
Notifications
ERROR:
"instructor_course_nums'
is not a known variable
LINE 6:
SELECT name INTO instructor_course_nums FROM instructor WH...
SQL state: 42601
Character: 129

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

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 3 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions