Workshop

The following workshop is composed of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises are intended to afford you the opportunity to apply the concepts discussed during the current hour, as well as build upon the knowledge acquired in previous hours of study. Please take time to complete the quiz questions and exercises before continuing. Refer to Appendix C, "Answers to Quizzes and Exercises," for answers.

Quiz

1:Would the use of a unique index on a small table be of any benefit?
2:What happens when the optimizer chooses not to use an index on a table when a query has been executed?
3:Should the most restrictive clause(s) be placed before the join condition(s) or after the join conditions in the WHERE clause?

Exercises

1:Rewrite the following SQL statements to improve their performance. Use the EMPLOYEE_TBL and the EMPLOYEE_PAY_TBL as described here:
EMPLOYEE_TBL   
EMP_IDVARCHAR2(9)NOT NULLPRIMARY KEY,
LAST_NAMEVARCHAR2(15)NOT NULL, 
FIRST_NAMEVARCHAR2(15)NOT NULL, 
MIDDLE_NAMEVARCHAR2(15),  
ADDRESSVARCHAR2(30)NOT NULL, 
CITYVARCHAR2(15)NOT NULL, 
STATECHAR(2)NOT NULL, 
ZIPNUMBER(5)NOT NULL, 
PHONECHAR(10),  
PAGERCHAR(10),  

CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID)

EMPLOYEE_PAY_TBL   
EMP_IDVARCHAR2(9)NOT NULLPRIMARY KEY,
POSITIONVARCHAR2(15)NOT NULL, 
DATE_HIREDATE,  
PAY_RATENUMBER(4,2)NOT NULL, 
DATE_LAST_RAISEDATE,  
SALARYNUMBER(8,2),  
BONUSNUMBER(8,2),  

CONSTRAINT EMP_FK FOREIGN KEY (EMP_ID)
  REFERENCES EMPLOYEE_TBL (EMP_ID)


  1. SELECT EMP_ID, LAST_NAME, FIRST_NAME,
          PHONE
   FROM EMPLOYEE_TBL
   WHERE SUBSTR(PHONE, 1, 3) = '317' OR
         SUBSTR(PHONE, 1, 3) = '812' OR
         SUBSTR(PHONE, 1, 3) = '765';
										
  2. SELECT LAST_NAME, FIRST_NAME
   FROM EMPLOYEE_TBL
   WHERE LAST_NAME LIKE '%ALL%;
										
  3. SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME,
          EP.SALARY
   FROM EMPLOYEE_TBL E,
   EMPLOYEE_PAY_TBL EP
   WHERE LAST_NAME LIKE 'S%'
   AND E.EMP_ID = EP.EMP_ID;
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset