| 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 15, "Combining Multiple Queries into One" Quiz Answers 1: | Is the syntax correct for the following compound queries? If not, what would correct the syntax? Use the EMPLOYEE_TBL and the EMPLOYEE_PAY_TBL shown as follows : EMPLOYEE_TBL | | EMP_ID | VARCHAR(9) | NOT NULL, | 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), | | CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID) 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 | DATE, | | | SALARY | DECIMAL(8,2), | | | BONUS | DECIMAL(6,2), | | | CONSTRAINT EMP_FK FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL (EMP_ID) -
SELECT EMP_ID, LAST_NAME, FIRST_NAME FROM EMPLOYEE_TBL UNION SELECT EMP_ID, POSITION, DATE_HIRE FROM EMPLOYEE_PAY_TBL; This compound query does not work because the data types do not match. The EMP_ID columns match, but the LAST_NAME and FIRST_NAME data types do not match the POSITION and DATE_HIRE data types. -
SELECT EMP_ID FROM EMPLOYEE_TBL UNION ALL SELECT EMP_ID FROM EMPLOYEE_PAY_TBL ORDER BY EMP_ID; Yes, the statement is correct. -
SELECT EMP_ID FROM EMPLOYEE_PAY_TBL INTERSECT SELECT EMP_ID FROM EMPLOYEE_TBL ORDER BY 1; Yes, this compound query works. | a | Show duplicates | a | UNION ALL | b | Return only rows from the first query that match those in the second query | b | INTERSECT | c | Return no duplicates | c | UNION | d | Return only rows from the first query not returned by the second | d | EXCEPT | Exercise Answers Q1: | Refer to the Oracle syntax covered in this hour for the following exercises. Write your queries out by hand on a sheet of paper because MySQL does not support the operators covered in this hour. When you are finished, compare your results to mine. Using the CUSTOMER_TBL and the ORDERS_TBL as listed: CUSTOMER_TBL | | CUST_IN | 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 | | | | A1: | -
Write a compound query to find the customers that have placed an order. SELECT CUST_ID FROM CUSTOMER_TBL INTERSECT SELECT CUST_ID FROM ORDERS_TBL; -
Write a compound query to find the customers that have not placed an order. SELECT CUST_ID FROM CUSTOMER_TBL EXCEPT SELECT CUST_ID FROM ORDERS_TBL; | | |