ORACLE PL/SQL PROJECT.....
In this file, write a function called get_classroom_teacher().
-
The function should take four IN parameters:
-
the subject
-
the school name
-
the year a class is taught
-
the semester a class is taught
-
The function should return the full name of the classroom teacher.
-
If no classroom teacher is found, the function should return NULL.
- Think through what exceptions might occur and include exception handling as appropriate.
-
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.'
-
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)
