Hour 15, Combining Multiple Queries into One

Team-Fly

---

 

	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) 

1.  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.

1.  SELECT EMP_ID FROM EMPLOYEE_TBL  UNION ALL SELECT EMP_ID FROM EMPLOYEE_PAY_TBL ORDER BY EMP_ID; 

   Yes, the statement is correct.

1.  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:

1. 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; 

2. 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; 

---

Team-Fly

Top