) Create the SQL statements for displaying the results for each of the following scenarios in the Academic Database. Save these statements in a script called M06_LastNameFirstName_ Queries (where LastName is your last name and FirstName is your first name). Run/execute these commands in Oracle Application Express. Based on data in the Academic Database, complete the following:

1. Write a simple query to view the data inserted in student table created for the Academic Database.
2. Write a query to retrieve the marks obtained by the student for each exam attempted.
3. The faculty of the different Departments realized that the marks entered in Exam Results were showing a decreased value of 7 marks for each entry. Display the marks by adding 7 points to the grades obtained by each student.
4. Display the First Name and Email Address as "The email address of is ".
5. Display the department name and the HOD of the department from the Department table.
6. Display the distinct Department ID from the Course table.
7. Display the course details for the Spring Session (Session ID 100).
8. Display the details of the students who have scored more than 93.
9. Display the course details for departments 20 and 30.
10. Display the details of students whose first name begins with the letter "M".
11. Display the details of students who have opted for courses 190 or 193.
12. Display the course details offered by department 30 for the Fall Session (Session ID 200).

(2 points each) Create the SQL statements for displaying the results for each of the following scenarios in the Academic Database. Save these statements in a script called M06_LastNameFirstName_Joins (where LastName is your last name and FirstName is your first name). Run/execute these commands in Oracle Application Express. To help with identifying tables that are needed to do joins, the ERD of the Academic Database has been included on the very last page of this activity. Based on the data given (shown below) for the Academic Database, complete the following:

13. Display the name of courses offered and the name of each session it is offered in.
14. Display the name of courses offered in the Fall session.
15. Display the course details and students who have enrolled for those courses.
16. Display the course details and students who have enrolled for those courses for department 30.
17. Will the given statement execute successfully? If not, what must be changed? Explain your answer in a comment in your script. Make the appropriate changes and include in the script.

SELECT COURSE_NAME, GRADE, EXAM_ID

FROM COURSE_NAME d JOIN EXAM_RESULT a

USING (a.COURSE_ID)

18. Explain what output would be generated when the given statement is executed. Include your answers in a comment in your script.

SELECT COURSE_NAME, GRADE, EXAM_ID

FROM COURSE d JOIN EXAM_RESULT a

ON a.COURSE_ID

BETWEEN 190 AND 195

19. Explain what output would be generated when the given statement is executed. Include your answers in a comment in your script.

SELECT *

FROM EXAM_RESULT

CROSS JOIN EXAM_TYPE

(2 points) In the script created above, include the following comments:

• At the beginning of the script include your name, the date, and the name of the script and a summary of what the script will do.
• Throughout the script, include comments to describe briefly what each section of script will do.

THE SCRIPT NEEDED FOR ABOVE TO WORK, GIVEN TO ME BY INSTRUCTOR

-- CREATING EACH TABLE IN THE ACADEMIC DATABASE

CREATE TABLE ACADEMIC_SESSION

(

SESSION_ID NUMBER NOT NULL ,

SESSION_NAME VARCHAR2(50)

) ;

CREATE TABLE COURSE

(

COURSE_ID NUMBER NOT NULL ,

COURSE_NAME VARCHAR2 (50) ,

SESSION_ID NUMBER ,

DEPARTMENT_ID NUMBER

) ;

CREATE TABLE DEPARTMENT

(

DEPARTMENT_ID NUMBER NOT NULL ,

DEPARTMENT_NAME VARCHAR2(50) ,

HOD VARCHAR2(50)

) ;

CREATE TABLE EXAM_DETAIL

(

EXAM_ID NUMBER NOT NULL ,

EXAM_TYPE VARCHAR2(50) NOT NULL ,

START_DATE DATE ,

NAME VARCHAR2(50)

) ;

CREATE TABLE EXAM_RESULT

(

STUDENT_ID NUMBER NOT NULL,

EXAM_ID NUMBER NOT NULL,

COURSE_ID NUMBER NOT NULL,

GRADE NUMBER

) ;

CREATE TABLE EXAM_TYPE

(

EXAM_TYPE VARCHAR2(50) NOT NULL ,

EXAM_NAME VARCHAR2(50) ,

EXAM_DESC VARCHAR2(50)

) ;

CREATE TABLE FACULTY_COURSE

(

FACULTY_ID NUMBER NOT NULL,

COURSE_ID NUMBER NOT NULL

) ;

CREATE TABLE FACULTY

(

FACULTY_ID NUMBER NOT NULL ,

FACULTY_NAME VARCHAR2(50) ,

FIRST_NAME VARCHAR2(50),

LAST_NAME VARCHAR2(50)

) ;

CREATE TABLE FACULTY_LOGIN

(

FACULTY_ID NUMBER NOT NULL,

LOGIN_DATE DATE NOT NULL,

LOGIN_TIME DATE NOT NULL,

DETAILS VARCHAR2(50)

) ;

CREATE TABLE PARENT_INFORMATION

(

PARENT_ID NUMBER NOT NULL ,

FATHER_NAME VARCHAR2(50) ,

MOTHER_NAME VARCHAR2(50)

) ;

CREATE TABLE STUDENT_ATTENDANCE

(

STUDENT_ID NUMBER NOT NULL,

NO_OF_WORKING_DAYS NUMBER ,

NO_OF_DAYS_OFF NUMBER ,

ELIGIBILITY_FOR_EXAMS CHAR

) ;

CREATE TABLE STUDENT_COURSE

(

STUDENT_ID NUMBER ,

COURSE_ID NUMBER

) ;

CREATE TABLE STUDENT

(

STUDENT_ID NUMBER NOT NULL ,

FIRST_NAME VARCHAR2(50) ,

PARENT_ID NUMBER,

STUDENT_REG_YEAR DATE

) ;

-- ADDING PRIMARY KEYS TO EACH TABLE

ALTER TABLE ACADEMIC_SESSION ADD CONSTRAINT ACADEMIC_SESSION_PK PRIMARY KEY

( SESSION_ID ) ;

ALTER TABLE COURSE ADD CONSTRAINT COURSE_PK PRIMARY KEY ( COURSE_ID ) ;

ALTER TABLE DEPARTMENT ADD CONSTRAINT DEPARTMENT_PK PRIMARY KEY ( DEPARTMENT_ID ) ;

ALTER TABLE EXAM_DETAIL ADD CONSTRAINT EXAM_DETAIL_PK PRIMARY KEY ( EXAM_ID ) ;

ALTER TABLE EXAM_TYPE ADD CONSTRAINT EXAM_TYPE_PK PRIMARY KEY ( EXAM_TYPE ) ;

ALTER TABLE FACULTY ADD CONSTRAINT FACULTY_PK PRIMARY KEY ( FACULTY_ID ) ;

ALTER TABLE PARENT_INFORMATION ADD CONSTRAINT PARENT_INFORMATION_PK PRIMARY KEY

( PARENT_ID ) ;

ALTER TABLE STUDENT ADD CONSTRAINT STUDENT_PK PRIMARY KEY ( STUDENT_ID ) ;

ALTER TABLE STUDENT_ATTENDANCE ADD CONSTRAINT STUDENT_ATTENDANCE_PK PRIMARY KEY

( STUDENT_ID ) ;

ALTER TABLE EXAM_RESULT ADD CONSTRAINT EXAM_RESULT_PK PRIMARY KEY ( STUDENT_ID,

COURSE_ID, EXAM_ID ) ;

ALTER TABLE FACULTY_LOGIN ADD CONSTRAINT FACULTY_LOGIN_PK PRIMARY KEY ( FACULTY_ID,

LOGIN_DATE, LOGIN_TIME ) ;

ALTER TABLE FACULTY_COURSE ADD CONSTRAINT FACULTY_COURSE_PK PRIMARY KEY

( FACULTY_ID, COURSE_ID ) ;

ALTER TABLE STUDENT_COURSE ADD CONSTRAINT STUDENT_COURSE_PK PRIMARY KEY

( STUDENT_ID, COURSE_ID) ;

-- ADDING FOREIGN KEYS TO EACH TABLE

ALTER TABLE COURSE ADD CONSTRAINT COURSE_FK FOREIGN KEY ( SESSION_ID ) REFERENCES

ACADEMIC_SESSION ( SESSION_ID ) ;

ALTER TABLE COURSE ADD CONSTRAINT COURSE_FKv2 FOREIGN KEY ( DEPARTMENT_ID )

REFERENCES DEPARTMENT ( DEPARTMENT_ID ) ;

ALTER TABLE EXAM_DETAIL ADD CONSTRAINT EXAM_DETAIL_FK FOREIGN KEY ( EXAM_TYPE )

REFERENCES EXAM_TYPE ( EXAM_TYPE ) ;

ALTER TABLE EXAM_RESULT ADD CONSTRAINT EXAM_RESULT_FK FOREIGN KEY ( COURSE_ID )

REFERENCES COURSE ( COURSE_ID ) ;

ALTER TABLE EXAM_RESULT ADD CONSTRAINT EXAM_RESULT_FKv2 FOREIGN KEY ( EXAM_ID )

REFERENCES EXAM_DETAIL ( EXAM_ID ) ;

ALTER TABLE EXAM_RESULT ADD CONSTRAINT EXAM_RESULT_FKv3 FOREIGN KEY ( STUDENT_ID )

REFERENCES STUDENT ( STUDENT_ID ) ;

ALTER TABLE FACULTY_COURSE ADD CONSTRAINT FACULTY_COURSE_FK FOREIGN KEY

( FACULTY_ID ) REFERENCES FACULTY ( FACULTY_ID ) ;

ALTER TABLE FACULTY_COURSE ADD CONSTRAINT FACULTY_COURSE_FKv2 FOREIGN KEY

( COURSE_ID ) REFERENCES COURSE ( COURSE_ID ) ;

ALTER TABLE FACULTY_LOGIN ADD CONSTRAINT FACULTY_LOGIN_FK FOREIGN KEY

( FACULTY_ID ) REFERENCES FACULTY ( FACULTY_ID ) ;

ALTER TABLE STUDENT_ATTENDANCE ADD CONSTRAINT STUDENT_ATTENDANCE_FK FOREIGN KEY

( STUDENT_ID ) REFERENCES STUDENT ( STUDENT_ID ) ;

ALTER TABLE STUDENT_COURSE ADD CONSTRAINT STUDENT_COURSE_FK FOREIGN KEY

( STUDENT_ID ) REFERENCES STUDENT ( STUDENT_ID ) ;

ALTER TABLE STUDENT_COURSE ADD CONSTRAINT STUDENT_COURSE_FKv2 FOREIGN KEY

( COURSE_ID ) REFERENCES COURSE ( COURSE_ID ) ;

ALTER TABLE STUDENT ADD CONSTRAINT STUDENT_FK FOREIGN KEY ( PARENT_ID ) REFERENCES

PARENT_INFORMATION ( PARENT_ID ) ;

-- ALTERING THE FACULTY_LOGIN TABLE FOR DEFAULT DATES

ALTER TABLE FACULTY_LOGIN MODIFY (LOGIN_DATE DATE DEFAULT SYSDATE);

-- ALTERING THE STUDENT TABLE FOR UNIQUE EMAIL ADDRESS

ALTER TABLE STUDENT ADD (EMAIL_ADDR VARCHAR2 (100) UNIQUE );

-- INSERTING ROWS IN THE ACADEMIC_SESSION TABLE

INSERT INTO ACADEMIC_SESSION VALUES (100, 'SPRING SESSION');

INSERT INTO ACADEMIC_SESSION VALUES (200, 'FALL SESSION');

INSERT INTO ACADEMIC_SESSION VALUES (300, 'SUMMER SESSION');

-- INSERTING ROWS IN THE DEPARTMENT TABLE

INSERT INTO DEPARTMENT VALUES(10, 'ACCOUNTING', 'MARK SMITH');

INSERT INTO DEPARTMENT VALUES(20, 'BIOLOGY', 'DAVE GOLD');

INSERT INTO DEPARTMENT VALUES(30, 'COMPUTER SCIENCE', 'LINDA BROWN');

INSERT INTO DEPARTMENT VALUES(40, 'LITERATURE', 'ANITA TAYLOR');

-- INSERTING ROWS IN THE COURSE_DETAILS TABLE

INSERT INTO COURSE VALUES (190, 'PRINCIPLES OF ACCOUNTING', 100, 10);

INSERT INTO COURSE VALUES (191, 'INTRODUCTION TO BUSINESS LAW', 100, 10);

INSERT INTO COURSE VALUES (192, 'COST ACCOUNTING', 100, 10);

INSERT INTO COURSE VALUES (193, 'STRATEGIC TAX PLANNING FOR BUSINESS', 100, 10);

INSERT INTO COURSE VALUES (194, 'GENERAL BIOLOGY', 200, 20);

INSERT INTO COURSE VALUES (195, 'CELL BIOLOGY', 200, 20);

INSERT INTO COURSE VALUES (196, 'INTRODUCTION TO PLANT PHYSIOLOGY', 200, 20);

INSERT INTO COURSE VALUES (197, 'MARINE BIOLOGY', 200, 20);

INSERT INTO COURSE VALUES (198, 'SIMULATION AND MODELING', 300, 30);

INSERT INTO COURSE VALUES (199, 'WEB PROGRAMMING', 300, 30);

INSERT INTO COURSE VALUES (187, 'DATA STRUCTURES', 300, 30);

INSERT INTO COURSE VALUES (188, 'OOAD', 300, 30);

INSERT INTO COURSE VALUES (189, 'COLLEGE READING', 100, 40);

FACULTY STUDENT COURSE STUDENT Raty_D FACULTY_COURSE * K Student D *Student D urse_D Faculty Name PK TRD K First Name First Name * * * _D 2 Sudent__Yr Last Name TK Parent_ID COURSE DEPARTMENT PARENT_INFORMATION * Course D Depamant D FACUTLY_LOGIN *Peront D Course Name Department_Name nk Peauty ID Father Name FK Department_o HOD *Login Dato Mother Name TK Sosion_ID Loan Timo ACADEMIC_SESSION EXAM_RESULT Senin D PKR Sudont D Seccion_Name PX - Cur_D * Bom_D EXAM_DETAIL EXAM_TYPE Grade Bom D Eem Type Exam_type Exam_Name Start_Date Exam_Desc FACULTY STUDENT COURSE STUDENT Raty_D FACULTY_COURSE * K Student D *Student D urse_D Faculty Name PK TRD K First Name First Name * * * _D 2 Sudent__Yr Last Name TK Parent_ID COURSE DEPARTMENT PARENT_INFORMATION * Course D Depamant D FACUTLY_LOGIN *Peront D Course Name Department_Name nk Peauty ID Father Name FK Department_o HOD *Login Dato Mother Name TK Sosion_ID Loan Timo ACADEMIC_SESSION EXAM_RESULT Senin D PKR Sudont D Seccion_Name PX - Cur_D * Bom_D EXAM_DETAIL EXAM_TYPE Grade Bom D Eem Type Exam_type Exam_Name Start_Date Exam_Desc