Write a procedure COURSES_BY_DEPARTMENT (departmentId VARCHAR(3)). When executed

Question

Write a procedure COURSES_BY_DEPARTMENT (departmentId VARCHAR(3)). When executed it displays Professor Name, Course Name, Semester and Section for all courses in the department.Test your procedure by executing these calls.

CALL COURSES_BY_DEPARTMENT ("CSC");

CALL COURSES_BY_DEPARTMENT ("MTH");

CALL COURSES_BY_DEPARTMENT ("PSH");

Tables are bellow:

Student Table

Department Table

Professor Table

Course Table

Teaching_Assignment Table:

Transcript Table:

Banner Id 100 101 102 103 104 105 106 107 108 109 110 Name Leona Jerald Freddie Shannon Billie Casey Nicole Alex Mona Anthony Thomas Curtis Addres Status Freshman Freshman Freshman Sophomore Sophomore Sophomore Junior Junior Junior Senior Senior Senior

Explanation / Answer

set server output on; // for enabling display of the output

create procedure COURSES_BY_DEPARTMENT (departmentId VARCHAR(3))
       as
       v_Name Professor.name%type;
       v_CourseName Course.CourseName%type;
       v_Semester Teaching_Assigment.Semester%type;
       v_Section Teaching_Assigment.Section%type;
  
   begin
       select p.Name, c.CourseName,ta.Semester,ta.Section
       into
       v_Name, v_CourseName,v_Semester,v_Section
       from
       Department d,Professor p,Course c,Teaching_Assignment ta
       where
       p.departmentId=d.id and d.id=c.departmentId and p.id=ta.professorId
       and c.DepartmentId=departmentId;

DBMS_OUTPUT.PUT_LINE(v_Name);
DBMS_OUTPUT.PUT_LINE(v_CourseName);
DBMS_OUTPUT.PUT_LINE(v_Semester);
DBMS_OUTPUT.PUT_LINE(v_Section);
   end COURSES_BY_DEPARTMENT;
/

note:if any doubts related your question ,please comment me i will clarify your doubt

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.