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: | What is the function of a subquery when used with a SELECT statement? |
| 2: | Can you update more than one column when using the UPDATE statement in conjunction with a subquery? |
| 3: | Are the following syntaxes correct? If not, what is the correct syntax?
|
| 4: | What would happen if the following statement were run?
DELETE FROM EMPLOYEE_TBL
WHERE EMP_ID IN
(SELECT EMP_ID
FROM EMPLOYEE_PAY_TBL);
|
Exercises
Use the following tables to complete the exercises:
| EMPLOYEE_TBL | |||
|---|---|---|---|
| EMP_ID | VARCHAR2(9) | NOT NULL | PRIMARY KEY |
| LAST_NAME | VARCHAR2(15) | NOT NULL | |
| FIRST_NAME | VARCHAR2(15) | NOT NULL | |
| MIDDLE_NAME | VARCHAR2(15) | ||
| ADDRESS | VARCHAR2(30) | NOT NULL | |
| CITY | VARCHAR2(15) | NOT NULL | |
| STATE | CHAR(2) | NOT NULL | |
| ZIP | NUMBER(5) | NOT NULL | |
| PHONE | CHAR(10) | ||
| PAGER | CHAR(10) |
| EMPLOYEE_TBL | |||
|---|---|---|---|
| EMP_ID | VARCHAR2(9) | NOT NULL | PRIMARY KEY |
| POSITION | VARCHAR2(15) | NOT NULL | |
| DATE_HIRE | DATE | ||
| PAY_RATE | NYMBER(4,2) | NOT NULL | |
| DATE_LAST_RAISE | DATE | ||
| CONSTRAINT EMP_FK FOREIGN KEY (EMP_ID_REFERENCES EMPLOYEE_TBL (EMP_ID) | |||
| CUSTOMER_TBL | |||
|---|---|---|---|
| CUST_ID | VARCHAR2(10) | NOT NULL | PRIMARY KEY |
| CUST_NAME | VARCHAR2(30) | NOT NULL | |
| CUST_ADDRESS | VARCHAR2(20) | NOT NULL | |
| CUST_CITY | VARCHAR2(15) | NOT NULL | |
| CUST_STATE | CHAR(2) | NOT NULL | |
| CUST_ZIP | NUMBER(5) | NOT NULL | |
| CUST_PHONE | NUMBER(10) | ||
| CUST_FAX | NUMBER(10) |
| ORDERS_TBL | |||
|---|---|---|---|
| ORD_NUM | VARCHAR2(10) | NOT NULL | PRIMARY KEY |
| CUST_ID | VARCHAR2(10) | NOT NULL | |
| PROD_ID | VARCHAR2(10) | NOT NULL | |
| QTY | NUMBER(6) | NOT NULL | |
| ORD_DATE | DATE |
| PRODUCTS_TBL | |||
|---|---|---|---|
| PROD_ID | VARCHAR2(10) | NOT NULL | PRIMARY KEY |
| PROD_DESC | VARCHAR2(40) | NOT NULL | |
| COST | NUMBER(6,2) | NOT NULL |
| 1: | Using a subquery, write an SQL statement to update the CUSTOMER_TBL table, changing the customer name to DAVIDS MARKET, who has an order with order number 23E934. |
| 2: | Using a subquery, write a query that returns all the names of all employees who have a pay rate greater than JOHN DOE, whose employee identification number is 343559876. |
| 3: | Using a subquery, write a query that lists all products that cost more than the average cost of all products. |
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.