Using the student and faculty tables create a select query that outputs all stud

Question

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;

select s.firstname, s.lastname, f.firstname, f.lastname

from student s

INNER JOIN faculty f on s.advisorID = f.facultyID;

Explanation / Answer

SET AUTOTRACE ON :- The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.

To use this feature, the PLUSTRACE role must be granted to the user. DBA privileges are required to grant the PLUSTRACE role

SET AUTOTRACE OFF:- No AUTOTRACE report is generated. This is the default.

Query

SELECT S.LastName AS StudentLastName,S.FirstName AS StudentFirstName,

F.LastName AS AdvisorLastName, F.FirstName AS AdvisorFirstName

FROM Students S, Faculty F

INNER JOIN faculty F on S.advisorID = F.facultyID;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

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