ORACLE PL/SQL PROJECT.....

In this file, write a function called get_classroom_teacher().

1. The function should take four IN parameters:

   1. the subject

   2. the school name

   3. the year a class is taught

   4. the semester a class is taught

2. The function should return the full name of the classroom teacher.

3. If no classroom teacher is found, the function should return NULL.

4. Think through what exceptions might occur and include exception handling as appropriate.

5. Write a PL/SQL block calling the function using the following variables: l_subject := 'Science'; l_school := 'Fayetteville-Manlius School'; l_year := 2021; l_semester := 'spring';

   With these arguments, the function should return 'Megan Gray' and you should output 'The teacher is Megan Gray.'

6. Write another PL/SQL block identical to the one above with one exception: change l_year to 2023. Then run PL/SQL block again. This time, the function should return NULL and you should output 'No teacher found.'

SYSTEM.SCHOOLS SCHOOL_ID NUMBER SCHOOL_NAME VARCHAR2 (35 BYTE) ADDRESS VARCHAR2 (60 BYTE) CITY VARCHAR2 (15 BYTE) REGION VARCHAR2 (15 BYTE) POSTAL_CODE VARCHAR2 (10 BYTE) COUNTRY VARCHAR2 (15 BYTE) PRINCIPAL_ID NUMBER > SCHOOLS_PK (SCHOOL_ID) SCHOOL_PRINCIPAL_FK (PRINCIPAL_ID) SYSTEM.PEOPLE P PERSON_ID NUMBER IF SCHOOL_ID NUMBER * FIRST_NAME VARCHAR2 (10 BYTE) LAST_NAME VARCHAR2 (20 BYTE) + * BIRTH_DATE DATE ADDRESS VARCHAR2 (60 BYTE) CITY VARCHAR2 (15 BYTE) REGION VARCHAR2 (15 BYTE) POSTAL_CODE VARCHAR2(10 BYTE) COUNTRY VARCHAR2 (15 BYTE) - PEOPLE_PK (PERSON_ID) E SYS_C0021064 (SCHOOL_ID) SYSTEM.TEACHERS P TEACHER_ID NUMBER PERSON_ID NUMBER SUBJECT_ID NUMBER +-K SALARY NUMBER TEACHERS_PK (TEACHER_ID) SYS_C0021067 (PERSON_ID) SYS_C0021068 (SUBJECT_ID) SYSTEM.SUBJECTS IP SUBJECT_ID NUMBER SUBJECT VARCHAR2 (60 BYTE) SUBJECTS_PK (SUBJECT_ID) SYSTEM.PRINCIPALS IP PRINCIPAL_ID NUMBER PERSON_ID NUMBER SALARY NUMBER -> PRINCIPALS_PK (PRINCIPAL_ID) SYS_C0021074 (PERSON_ID) SYSTEM.STUDENTS P" STUDENT_ID NUMBER PERSON_ID NUMBER GRADE_LEVEL NUMBER STUDENTS_PK (STUDENT_ID) SYS_C0021071 (PERSON_ID) KA SYSTEM.CLASSROOMS P" CLASSROOM_ID NUMBER TEACHER_ID NUMBER SUBJECT_ID NUMBER SEMESTER VARCHAR2 (6 BYTE) YEAR NUMBER (4) - CLASSROOMS_PK (CLASSROOM_ID) SYS_C0021077 (TEACHER_ID) SYS_C002 1078 (SUBJECT_ID) P SYSTEM.CLASSROOM_STUDENTS CLASSROOM_STUDENT_ID NUMBER CLASSROOM_ID NUMBER STUDENT_ID NUMBER GRADE NUMBER 839394 CLASSROOM_STUDENTS_PK (CLASSROOM_STUDENT_ID) SYS_C0021082 (STUDENT_ID) SYS_C0021081 (CLASSROOM_ID) SYSTEM.SCHOOLS SCHOOL_ID NUMBER SCHOOL_NAME VARCHAR2 (35 BYTE) ADDRESS VARCHAR2 (60 BYTE) CITY VARCHAR2 (15 BYTE) REGION VARCHAR2 (15 BYTE) POSTAL_CODE VARCHAR2 (10 BYTE) COUNTRY VARCHAR2 (15 BYTE) PRINCIPAL_ID NUMBER > SCHOOLS_PK (SCHOOL_ID) SCHOOL_PRINCIPAL_FK (PRINCIPAL_ID) SYSTEM.PEOPLE P PERSON_ID NUMBER IF SCHOOL_ID NUMBER * FIRST_NAME VARCHAR2 (10 BYTE) LAST_NAME VARCHAR2 (20 BYTE) + * BIRTH_DATE DATE ADDRESS VARCHAR2 (60 BYTE) CITY VARCHAR2 (15 BYTE) REGION VARCHAR2 (15 BYTE) POSTAL_CODE VARCHAR2(10 BYTE) COUNTRY VARCHAR2 (15 BYTE) - PEOPLE_PK (PERSON_ID) E SYS_C0021064 (SCHOOL_ID) SYSTEM.TEACHERS P TEACHER_ID NUMBER PERSON_ID NUMBER SUBJECT_ID NUMBER +-K SALARY NUMBER TEACHERS_PK (TEACHER_ID) SYS_C0021067 (PERSON_ID) SYS_C0021068 (SUBJECT_ID) SYSTEM.SUBJECTS IP SUBJECT_ID NUMBER SUBJECT VARCHAR2 (60 BYTE) SUBJECTS_PK (SUBJECT_ID) SYSTEM.PRINCIPALS IP PRINCIPAL_ID NUMBER PERSON_ID NUMBER SALARY NUMBER -> PRINCIPALS_PK (PRINCIPAL_ID) SYS_C0021074 (PERSON_ID) SYSTEM.STUDENTS P" STUDENT_ID NUMBER PERSON_ID NUMBER GRADE_LEVEL NUMBER STUDENTS_PK (STUDENT_ID) SYS_C0021071 (PERSON_ID) KA SYSTEM.CLASSROOMS P" CLASSROOM_ID NUMBER TEACHER_ID NUMBER SUBJECT_ID NUMBER SEMESTER VARCHAR2 (6 BYTE) YEAR NUMBER (4) - CLASSROOMS_PK (CLASSROOM_ID) SYS_C0021077 (TEACHER_ID) SYS_C002 1078 (SUBJECT_ID) P SYSTEM.CLASSROOM_STUDENTS CLASSROOM_STUDENT_ID NUMBER CLASSROOM_ID NUMBER STUDENT_ID NUMBER GRADE NUMBER 839394 CLASSROOM_STUDENTS_PK (CLASSROOM_STUDENT_ID) SYS_C0021082 (STUDENT_ID) SYS_C0021081 (CLASSROOM_ID)