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

'

-- 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); INSERT INTO COURSE VALUES (176, 'BUSINESS WRITING', 200, 40); INSERT INTO COURSE VALUES (175, 'AMERICAN LITERATURE', 300, 40);

-- INSERTING ROWS IN THE EXAM_TYPE TABLE INSERT INTO EXAM_TYPE VALUES('MCE','Multiple Choice Exams','CHOOSE MORE THAN ONE ANSWER'); INSERT INTO EXAM_TYPE VALUES('TF','TRUE AND FALSE Exams','CHOOSE EITHER TRUE OR FALSE'); INSERT INTO EXAM_TYPE VALUES('FIB','FILL IN THE BLANKS Exams','TYPE IN THE CORRECT ANSWER'); INSERT INTO EXAM_TYPE VALUES('ESS','ESSAY Exams','WRITE PARAGRAPHS'); INSERT INTO EXAM_TYPE VALUES('SA','SHORT ANSWER Exams','WRITE SHORT ANSWERS'); INSERT INTO EXAM_TYPE VALUES('PS','PROBLEM SOLVING Exams','SOLVE THE PROBLEM'); INSERT INTO EXAM_TYPE VALUES('LAB','LAB Exams','HANDS ON EXAM'); INSERT INTO EXAM_TYPE VALUES('OB','OPEN BOOK Exams','OPEN BOOK METHOD');

-- INSERTING ROWS IN THE EXAM_DETAILS TABLE INSERT INTO EXAM_DETAIL VALUES(500,'MCE',TO_DATE('12-Sep-2013','DD-Mon-YYYY'),'FALL SESSION EXAM'); INSERT INTO EXAM_DETAIL VALUES(510,'SA',TO_DATE('15-Sep-2013','DD-Mon-YYYY'),'FALL SESSION EXAM'); INSERT INTO EXAM_DETAIL VALUES(520,'LAB',TO_DATE('18-Sep-2013','DD-Mon-YYYY'),'FALL SESSION EXAM'); INSERT INTO EXAM_DETAIL VALUES(530,'ESS',TO_DATE('21-Mar-2014','DD-Mon-YYYY'),'SPRING SESSION EXAM'); INSERT INTO EXAM_DETAIL VALUES(540,'OB',TO_DATE('25-Mar-2014','DD-Mon-YYYY'),'SPRING SESSION EXAM'); INSERT INTO EXAM_DETAIL VALUES(550,'TF',TO_DATE('02-Apr-2014','DD-Mon-YYYY'),'SPRING SESSION EXAM'); INSERT INTO EXAM_DETAIL VALUES(560,'FIB',TO_DATE('26-May-2014','DD-Mon-YYYY'),'SUMMER SESSION EXAM'); INSERT INTO EXAM_DETAIL VALUES(570,'SA',TO_DATE('30-May-2014','DD-Mon-YYYY'),'SUMMER SESSION EXAM');

-- INSERTING ROWS IN THE PARENT_INFORMATION TABLE INSERT INTO PARENT_INFORMATION VALUES(600,'NEIL SMITH','DORIS SMITH'); INSERT INTO PARENT_INFORMATION VALUES(610,'WILLIAM BEN','NITA BEN'); INSERT INTO PARENT_INFORMATION VALUES(620,'SEAN TAYLOR','RHEA TAYLOR'); INSERT INTO PARENT_INFORMATION VALUES(630,'DAVE CARMEN','CATHY CARMEN'); INSERT INTO PARENT_INFORMATION VALUES(640,'JOHN AUDRY','JANE AUDRY');

-- INSERTING ROWS IN THE STUDENT_DETAILS TABLE INSERT INTO STUDENT VALUES(720, 'JACK', 600, TO_DATE('01-Jan-2012','DD-Mon-YYYY'),NULL); INSERT INTO STUDENT VALUES(740, 'RHONDA', 620, TO_DATE('01-Sep-2012','DD-Mon-YYYY'),NULL); INSERT INTO STUDENT VALUES(750, 'ROBERT', 610, TO_DATE('01-Mar-2012','DD-Mon-YYYY'),NULL); INSERT INTO STUDENT VALUES(760, 'JEANNE', 610, TO_DATE('01-Mar-2012','DD-Mon-YYYY'),NULL); INSERT INTO STUDENT VALUES(770, 'MILLS', 630, TO_DATE('01-Apr-2013','DD-Mon-YYYY'),NULL); INSERT INTO STUDENT VALUES(710, 'NINA', 630, TO_DATE('01-Jan-2011','DD-Mon-YYYY'),NULL); INSERT INTO STUDENT VALUES(780, 'NATHAN', 640, TO_DATE('01-Jan-2014','DD-Mon-YYYY'),NULL); INSERT INTO STUDENT VALUES(730, 'NOAH', 640, TO_DATE('01-Jan-2012','DD-Mon-YYYY'),NULL);

-- INSERTING ROWS IN THE STUDENT_COURSE_DETAILS TABLE INSERT INTO STUDENT_COURSE VALUES(720,190); INSERT INTO STUDENT_COURSE VALUES(720,193); INSERT INTO STUDENT_COURSE VALUES(720,191); INSERT INTO STUDENT_COURSE VALUES(740,195); INSERT INTO STUDENT_COURSE VALUES(740,197); INSERT INTO STUDENT_COURSE VALUES(740,199); INSERT INTO STUDENT_COURSE VALUES(750,192); INSERT INTO STUDENT_COURSE VALUES(750,175); INSERT INTO STUDENT_COURSE VALUES(750,176); INSERT INTO STUDENT_COURSE VALUES(750,189); INSERT INTO STUDENT_COURSE VALUES(760,188); INSERT INTO STUDENT_COURSE VALUES(760,187); INSERT INTO STUDENT_COURSE VALUES(760,190); INSERT INTO STUDENT_COURSE VALUES(760,192); INSERT INTO STUDENT_COURSE VALUES(770,188); INSERT INTO STUDENT_COURSE VALUES(770,187); INSERT INTO STUDENT_COURSE VALUES(710,176); INSERT INTO STUDENT_COURSE VALUES(710,175); INSERT INTO STUDENT_COURSE VALUES(780,192); INSERT INTO STUDENT_COURSE VALUES(780,193); INSERT INTO STUDENT_COURSE VALUES(780,194); INSERT INTO STUDENT_COURSE VALUES(730,199); INSERT INTO STUDENT_COURSE VALUES(730,198);

-- INSERTING ROWS IN THE STUDENT_ATTENDANCE INSERT INTO STUDENT_ATTENDANCE VALUES( 710, 180, 20,'Y'); INSERT INTO STUDENT_ATTENDANCE VALUES( 720, 180, 21,'Y'); INSERT INTO STUDENT_ATTENDANCE VALUES( 730, 180, 11,'Y'); INSERT INTO STUDENT_ATTENDANCE VALUES( 740, 180, 12,'Y'); INSERT INTO STUDENT_ATTENDANCE VALUES( 750, 180, 14,'Y'); INSERT INTO STUDENT_ATTENDANCE VALUES( 760, 180, 15,'Y'); INSERT INTO STUDENT_ATTENDANCE VALUES( 770, 180, 13,'Y'); INSERT INTO STUDENT_ATTENDANCE VALUES( 780, 180, 10,'Y');

-- INSERTING ROWS IN THE FACULTY_DETAILS TABLE INSERT INTO FACULTY(FACULTY_ID,FACULTY_NAME) VALUES (800, 'JILL MILLER'); INSERT INTO FACULTY(FACULTY_ID,FACULTY_NAME) VALUES (810, 'JAMES BORG'); INSERT INTO FACULTY(FACULTY_ID,FACULTY_NAME) VALUES (820, 'LYNN BROWN'); INSERT INTO FACULTY(FACULTY_ID,FACULTY_NAME) VALUES (830, 'ARTHUR SMITH'); INSERT INTO FACULTY(FACULTY_ID,FACULTY_NAME) VALUES (840, 'SALLY JONES');

-- INSERTING ROWS IN THE FACULTY_COURSE_DETAILS TABLE INSERT INTO FACULTY_COURSE VALUES (800, 192); INSERT INTO FACULTY_COURSE VALUES (800, 193); INSERT INTO FACULTY_COURSE VALUES (800, 190); INSERT INTO FACULTY_COURSE VALUES (800, 191); INSERT INTO FACULTY_COURSE VALUES (810, 194); INSERT INTO FACULTY_COURSE VALUES (810, 195); INSERT INTO FACULTY_COURSE VALUES (820, 196); INSERT INTO FACULTY_COURSE VALUES (820, 197); INSERT INTO FACULTY_COURSE VALUES (830, 198); INSERT INTO FACULTY_COURSE VALUES (830, 199); INSERT INTO FACULTY_COURSE VALUES (830, 187); INSERT INTO FACULTY_COURSE VALUES (830, 188); INSERT INTO FACULTY_COURSE VALUES (830, 189); INSERT INTO FACULTY_COURSE VALUES (840, 176); INSERT INTO FACULTY_COURSE VALUES (840, 175);

-- INSERTING ROWS IN THE FACULTY_LOGIN_DETAILS TABLE INSERT INTO FACULTY_LOGIN(FACULTY_ID,LOGIN_DATE,LOGIN_TIME) VALUES (800,TO_DATE('23-Nov-2014','DD-Mon-YYYY'), TO_DATE('12:00:00','hh24:mi:ss')); INSERT INTO FACULTY_LOGIN(FACULTY_ID,LOGIN_DATE,LOGIN_TIME) VALUES (800,TO_DATE('24-Nov-2014','DD-Mon-YYYY'), TO_DATE('1:00:00','hh24:mi:ss')); INSERT INTO FACULTY_LOGIN(FACULTY_ID,LOGIN_DATE,LOGIN_TIME) VALUES (810,TO_DATE('22-Nov-2014','DD-Mon-YYYY'), TO_DATE('2:00:00','hh24:mi:ss')); INSERT INTO FACULTY_LOGIN(FACULTY_ID,LOGIN_DATE,LOGIN_TIME) VALUES (810,TO_DATE('24-Nov-2014','DD-Mon-YYYY'), TO_DATE('3:00:00','hh24:mi:ss')); INSERT INTO FACULTY_LOGIN(FACULTY_ID,LOGIN_DATE,LOGIN_TIME) VALUES (820,TO_DATE('01-Dec-2014','DD-Mon-YYYY'), TO_DATE('4:00:00','hh24:mi:ss')); INSERT INTO FACULTY_LOGIN(FACULTY_ID,LOGIN_DATE,LOGIN_TIME) VALUES (820,TO_DATE('02-Dec-2014','DD-Mon-YYYY'), TO_DATE('5:00:00','hh24:mi:ss')); INSERT INTO FACULTY_LOGIN(FACULTY_ID,LOGIN_DATE,LOGIN_TIME) VALUES (830,TO_DATE('16-Oct-2014','DD-Mon-YYYY'), TO_DATE('6:00:00','hh24:mi:ss')); INSERT INTO FACULTY_LOGIN(FACULTY_ID,LOGIN_DATE,LOGIN_TIME) VALUES (830,TO_DATE('17-Oct-2014','DD-Mon-YYYY'), TO_DATE('7:00:00','hh24:mi:ss')); INSERT INTO FACULTY_LOGIN(FACULTY_ID,LOGIN_DATE,LOGIN_TIME) VALUES (830,TO_DATE('24-Nov-2014','DD-Mon-YYYY'), TO_DATE('8:00:00','hh24:mi:ss')); INSERT INTO FACULTY_LOGIN(FACULTY_ID,LOGIN_DATE,LOGIN_TIME) VALUES (840,TO_DATE('24-Nov-2014','DD-Mon-YYYY'), TO_DATE('9:00:00','hh24:mi:ss')); INSERT INTO FACULTY_LOGIN(FACULTY_ID,LOGIN_DATE,LOGIN_TIME) VALUES (840,TO_DATE('03-Dec-2014','DD-Mon-YYYY'), TO_DATE('10:00:00','hh24:mi:ss')); INSERT INTO FACULTY_LOGIN(FACULTY_ID,LOGIN_DATE,LOGIN_TIME) VALUES (820,TO_DATE('03-Dec-2014','DD-Mon-YYYY'), TO_DATE('11:00:00','hh24:mi:ss'));

-- INSERTING ROWS IN THE EXAM_RESULTS TABLE INSERT INTO EXAM_RESULT VALUES(720,500,190,91); INSERT INTO EXAM_RESULT VALUES(720,520,193,97); INSERT INTO EXAM_RESULT VALUES(720,530,191,89); INSERT INTO EXAM_RESULT VALUES(740,560,195,69); INSERT INTO EXAM_RESULT VALUES(740,570,197,70); INSERT INTO EXAM_RESULT VALUES(740,570,199,76); INSERT INTO EXAM_RESULT VALUES(750,500,192,60); INSERT INTO EXAM_RESULT VALUES(750,510,175,97); INSERT INTO EXAM_RESULT VALUES(750,520,176,78); INSERT INTO EXAM_RESULT VALUES(750,520,189,85); INSERT INTO EXAM_RESULT VALUES(760,540,188,65); INSERT INTO EXAM_RESULT VALUES(760,530,187,60); INSERT INTO EXAM_RESULT VALUES(760,540,190,79); INSERT INTO EXAM_RESULT VALUES(760,510,192,70); INSERT INTO EXAM_RESULT VALUES(770,560,188,91); INSERT INTO EXAM_RESULT VALUES(770,550,187,99); INSERT INTO EXAM_RESULT VALUES(710,500,176,91); INSERT INTO EXAM_RESULT VALUES(710,510,175,77); INSERT INTO EXAM_RESULT VALUES(780,520,192,56); INSERT INTO EXAM_RESULT VALUES(780,530,193,61); INSERT INTO EXAM_RESULT VALUES(780,530,194,65); INSERT INTO EXAM_RESULT VALUES(730,540,199,87); INSERT INTO EXAM_RESULT VALUES(730,530,198,85);

-- UPDATING THE STUDENT TABLE WITH UNIQUE EMAIL ADDRESSES UPDATE STUDENT SET EMAIL_ADDR = 'jack@abc.com' where STUDENT_ID = 720;

UPDATE STUDENT SET EMAIL_ADDR = 'rhonda@def.com' where STUDENT_ID = 740;

UPDATE STUDENT SET EMAIL_ADDR = 'robert@abc.com' where STUDENT_ID = 750;

UPDATE STUDENT SET EMAIL_ADDR = 'jeanne@ghi.com' where STUDENT_ID = 760;

UPDATE STUDENT SET EMAIL_ADDR = 'mills@def.com' where STUDENT_ID = 770;

UPDATE STUDENT SET EMAIL_ADDR = 'nina@def.com' where STUDENT_ID = 710;

UPDATE STUDENT SET EMAIL_ADDR = 'nathan@abc.com' where STUDENT_ID = 780;

UPDATE STUDENT SET EMAIL_ADDR = 'noah@xyz.com' where STUDENT_ID = 730;

FACULTY STUDENT_COURSE STUDENT STUDENT_ATTENDANCE *R _D FACULTY_COURSE PK Student_D Student D PKK Student ID PK +Rat_D Faculty_Name FK * Cou_D First Name No_04_WorkingDa First_Name P ourse_D Student_Reg_Year No_04_De_OM Last Name TK Parent_ID E GORY_For_Exam COURSE DEPARTMENT PARENT INFORMATION Cour_D *Department D FAOUTLY_LOGIN Berat D Courm Department_Name PKK Rauty_D Father_Name TK Department_ID HOD lan Dato Mother Name leen Time ACADEMIC_3830N Details BAM_RESULT Secion D PKR Sudent D Seson_Name PKP = Course_D EXAM_DETAIL EXAM TYPE Crede Exam D *Exam Tipe Exam_Type Exam_Name Start Date Exam_Desc FACULTY STUDENT_COURSE STUDENT STUDENT_ATTENDANCE *R _D FACULTY_COURSE PK Student_D Student D PKK Student ID PK +Rat_D Faculty_Name FK * Cou_D First Name No_04_WorkingDa First_Name P ourse_D Student_Reg_Year No_04_De_OM Last Name TK Parent_ID E GORY_For_Exam COURSE DEPARTMENT PARENT INFORMATION Cour_D *Department D FAOUTLY_LOGIN Berat D Courm Department_Name PKK Rauty_D Father_Name TK Department_ID HOD lan Dato Mother Name leen Time ACADEMIC_3830N Details BAM_RESULT Secion D PKR Sudent D Seson_Name PKP = Course_D EXAM_DETAIL EXAM TYPE Crede Exam D *Exam Tipe Exam_Type Exam_Name Start Date Exam_Desc