Sams Teach Yourself SQL in 24 Hours, Third Edition By Ronald R. Plew, Ryan K. Stephens
Table of Contents
Appendix C. Answers to Quizzes and Exercises
Hour 14, "Using Subqueries to Define Unknown Data"
Quiz Answers
1:
What is the function of a subquery when used with a SELECT statement?
A1:
The main function of a subquery when used with a SELECT statement is to return data that the main query can use to resolve the query.
2:
Can you update more than one column when using the UPDATE statement in conjunction with a subquery?
A2:
Yes, you can update more than one column using the same UPDATE and subquery statement.
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');
A3:
SELECT CUST_ID, CUST_NAME FROM CUSTOMER_TBL WHERE CUST_ID = (SELECT CUST_ID FROM ORDERS_TBL WHERE ORD_NUM = '16C17');
Yes, this syntax is correct.
SELECT EMP_ID, SALARY FROM EMPLOYEE_PAY_TBL WHERE SALARY BETWEEN '20000' AND (SELECT SALARY FROM EMPLOYEE_ID WHERE SALARY = '40000');
No. The BETWEEN operator cannot be used in this format.
UPDATE PRODUCTS_TBL SET COST = 1.15 WHERE CUST_ID = (SELECT CUST_ID FROM ORDERS_TBL WHERE ORD_NUM = '32A132');
Yes, this syntax is correct.
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);
A4:
All rows that were retrieved from the EMPLOYEE_PAY_TBL would be used by the DELETE to remove them from the EMPLOYEE_TBL. A WHERE clause in the subquery is highly advised.
Exercise Answers
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.
A1:
UPDATE CUSTOMER_TBL SET CUST_NAME = 'DAVIDS MARKET' WHERE CUST_ID = (SELECT CUST_ID FROM ORDERS_TBL WHERE ORD_NUM = '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.
A2:
SELECT E.LAST_NAME, E.FIRST_NAME, E.MIDDLE_NAME FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL P WHERE P.PAY_RATE > (SELECT PAY_RATE FROM EMPLOYEE_PAY_TBL WHERE EMP_ID = '343559876');
3:
Using a subquery, write a query that lists all products that cost more than the average cost of all products.
A3:
SELECT PROD_DESC FROM PRODUCTS_TBL WHERE COST > (SELECT AVG(COST) FROM PRODUCTS_TBL);
