CREATE TABLE STUDENT ( StudentID varchar(255) NOT NULL, FirstName varchar(255),

Question

CREATE TABLE STUDENT
(
StudentID varchar(255) NOT NULL,
FirstName varchar(255),
LastName varchar(255),
HomeAddress varchar(255),
Street varchar(255),
City varchar(255),
State varchar(255),
Zip varchar(255),
HomeTelephone varchar(255),
AdvisorID varchar(255) NOT NULL,
EnrollmentstatusID varchar(255) NOT NULL,
CONSTRAINT pk_StudentID PRIMARY KEY (StudentID)
);

CREATE TABLE FACULTY
(
FacultyID varchar(255) NOT NULL,
FirstName varchar(255),
LastName varchar(255),
CONSTRAINT pk_FacultyID PRIMARY KEY (FacultyID)
);

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. Create 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;

Explanation / Answer

Using the student and faculty tables create a select query that outputs all students for a specific advisor.

Answer:
select * from student where student.advisorid = faculty.facultyid left join faculty on student.advisorid = faculty.facultyid ;

Create an index that allows for a better join between the two tables
Answer:
create index stud on student ( studentID, advisorID);

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.