QuestionQuestion

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

Format: Submit the code to 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).

Goals:
Develop and demonstrate SQL skills
Develop your understanding of some database administration issues

Criteria:
1) Well-written code
2) Code meets requirements

Solution PreviewSolution Preview

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

--------------------------------------------------------
-- Create the tables, Insert data,
--------------------------------------------------------
--------------------------------------------------------
-- DDL for Table PROFESSOR
--------------------------------------------------------

CREATE TABLE "PROFESSOR"
   ( "PROFESSORID" NUMBER,
"FIRSTNAME" VARCHAR2(20),
"LASTNAME" VARCHAR2(20),
"OFFICENUMBER" NUMBER,
"ADDRESS" VARCHAR2(100),
"CITY" VARCHAR2(20),
"STATE" VARCHAR2(20),
"ZIPCODE" VARCHAR2(20)
   );

Insert into PROFESSOR (PROFESSORID,FIRSTNAME,LASTNAME,OFFICENUMBER,ADDRESS,CITY,STATE,ZIPCODE) values (1,'John','Smith',25,'address1','Houston','Texas','5654');
Insert into PROFESSOR (PROFESSORID,FIRSTNAME,LASTNAME,OFFICENUMBER,ADDRESS,CITY,STATE,ZIPCODE) values (2,'Bob','Taylor',24,'address2','Chicago','Illinois','4565');...
$80.00 for this solution

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats