Gain an understanding of execution plans and index creation. Learn how to use the Autotrace statement,  an index statement, and how to read the autotrace results.

Tasks

 

1. Using the student and faculty tables create a select query that outputs all students for a specific advisor. Generate the execution plan by first setting Autotrace on.  an index that allows for a better join between the two tables. Again generate and display the revised execution plan using Autotrace.

The commands for using Autotrace are as follows:

SET AUTOTRACE ON;

SET AUTOTRACE OFF;

Format

 

Submit the code to drop the tables and index, create the tables, insert data, run the select, show the execution plan, create an index, and then generate and show the execution plan again. You must submit the code as scripts that the instructor can run from an SQL prompt on an Oracle DBMS. Your submission must include a description of the scripts, their actions, and the order in which they should be run. Your code must not have any "side effects" (that is, it must not do anything not listed in the assignment description, and all of its actions must be described in the program header).

 

Tables are below. Not sure If they are 100% correct though.

 table 

 

Students( StudentID VARCHAR(20), FirstName VARCHAR(20), LastName VARCHAR(20), Birthdate int, EmailAddress VARCHAR(20), Address VARCHAR(20), Preferred Pronouns VARCHAR(20), Mailing AddressID (FK) Foreign Key AdvisorID (Foreign Key referencing Faculty); MatriculationDate int, EnrollmentStatus VARCHAR(20), GraduationDate VARCHAR(20)); 

 

Create table FACULTY( FacultyID int primary key not null, FirstName varchar(20), LastName varchar(20), Title varchar(20), OfficePhone int, HomePhone int, OfficeNumber int, SupervisorID int, Title varchar(20), Foreign Key (HomeAddressID) references FACULTY(FacultyID); Foreign Key (OfficeAddressID) references FACULTY(FacultyID); Primary Key (SupervisorID) references FACULTY(FacultyID));