CREATE TABLE Faculty_T (FacultyID NUMBER(11,0) NOT NULL, FacFName VARCHAR(25) NO
ID: 3667081 • Letter: C
Question
CREATE TABLE Faculty_T (FacultyID NUMBER(11,0) NOT NULL,
FacFName VARCHAR(25) NOT NULL,
FacLName VARCHAR(30),
FacOfficeNumber VARCHAR(10),
FacultyPhone# VARCHAR(12),
CONSTRAINT Faculty_PK PRIMARY KEY(FacultyID));
CREATE TABLE Student_T (StudentID NUMBER(11,0) NOT NULL,
StudentFirstName VARCHAR(25) NOT NULL,
StudentLastName VARCHAR(30),
StudentAddress VARCHAR(30),
StudentPhone# VARCHAR(12),
StuOffNo VARCHAR(12),
ConvocationDate VARCHAR(10),
SpouseName VARCHAR(40),
StudentErollStatus VARCHAR(15),
FacultyID NUMBER(11,0) NOT NULL,
CONSTRAINT StudentID_pk PRIMARY KEY(StudentID),
CONSTRAINT fk_Advisor FOREIGN KEY (FacultyID)
REFERENCES Faculty_T(FacultyID));
Base of the table createion above I need:
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
The above tables faculty and student are defined above.and two primary keys aredefined in each table.those are FacultyID and StudentID respectively.to establish relation between two tables we created.we used FOREIGNKEY constraints in student table.
QUERY:
The select query for the number of students for a specific advisor is mentioned below.
SELECT * from Student_T where Faculty_T.FacultyID=3;
this query results the list of all students for the FacultyID 3.
INDEX can be created on a table which is useful to find the data fastly.the syntax for creating index is as follows.
CREATE INDEX indexname ON tablename(columnname);
for our example the INDEX creation as follows:
CREATE INDEX Studentlist ON Student_T(studentID);