Workshop

Team-Fly

---

 

	Sams Teach Yourself SQL in 24 Hours, Third Edition By Ronald R. Plew, Ryan K. Stephens
	Table of Contents
	Hour  14.  Using Subqueries to Define Unknown Data

---

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 syntax(s) correct? If not, what is the correct syntax? SELECT CUST_ID, CUST_NAME FROM CUSTOMER_TBL WHERE CUST_ID = (SELECT CUST_ID FROM ORDERS_TBL WHERE ORD_NUM = '16C17'); SELECT EMP_ID, SALARY FROM EMPLOYEE_PAY_TBL WHERE SALARY BETWEEN '20000' AND (SELECT SALARY FROM EMPLOYEE_ID WHERE SALARY = '40000'); UPDATE PRODUCTS_TBL SET COST = 1.15 WHERE CUST_ID = (SELECT CUST_ID FROM ORDERS_TBL WHERE ORD_NUM = '32A132');
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

In this exercise section, refer to the Oracle syntax of suqueries covered in this hour. Write the Oracle SQL code for the requested subqueries by hand on a sheet of paper and compare your results to ours. Use the following tables to complete the exercises:

EMPLOYEE_TBL
EMP_ID	VARCHAR(9)	NOT NULL	PRIMARY KEY
LAST_NAME	VARCHAR(15)	NOT NULL
FIRST_NAME	VARCHAR(15)	NOT NULL
MIDDLE_NAME	VARCHAR(15)
ADDRESS	VARCHAR(30)	NOT NULL
CITY	VARCHAR(15)	NOT NULL
STATE	VARCHAR(2)	NOT NULL
ZIP	INTEGER(5)	NOT NULL
PHONE	VARCHAR(10)
PAGER	VARCHAR(10)

EMPLOYEE_PAY_TBL
EMP_ID	VARCHAR(9)	NOT NULL	PRIMARY KEY
POSITION	VARCHAR(15)	NOT NULL
DATE_HIRE	DATETIME
PAY_RATE	DECIMAL(4,2)	NOT NULL
DATE_LAST_RAISE DATETIME
CONSTRAINT EMP_FK FOREIGN KEY (EMP_ID_ REFERENCES EMPLOYEE_TBL (EMP_ID)

CUSTOMER_TBL
CUST_ID	VARCHAR(10)	NOT NULL	PRIMARY KEY
CUST_NAME	VARCHAR(30)	NOT NULL
CUST_ADDRESS	VARCHAR(20)	NOT NULL
CUST_CITY	VARCHAR(15)	NOT NULL
CUST_STATE	VARCHAR(2)	NOT NULL
CUST_ZIP	INTEGER(5)	NOT NULL
CUST_PHONE	INTEGER(10)
CUST_FAX	INTEGER(10)

ORDERS_TBL
ORD_NUM	VARCHAR(10)	NOT NULL	PRIMARY KEY
CUST_ID	VARCHAR(10)	NOT NULL
PROD_ID	VARCHAR(10)	NOT NULL
QTY	INTEGER(6)	NOT NULL
ORD_DATE	DATETIME

PRODUCTS_TBL
PROD_ID	VARCHAR(10)	NOT NULL	PRIMARY KEY
PROD_DESC	VARCHAR(40)	NOT NULL
COST	DECIMAL(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.

---

Team-Fly

Top