Sams Teach Yourself SQL in 24 Hours

Sep 9, 2005

📄 Contents

␡

⎙ Print

< Back Page 232 of 251 Next >

Recommended Book 

Sams Teach Yourself SQL in 24 Hours, 3rd Edition

Learn More Buy

Hour 15, "Combining Multiple Queries into One"

Quiz Answers

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`	VAR`CHAR(2)`	`NOT NULL`,
`ZIP`	`INTEGER(5)`	`NOT NULL`,
`PHONE`	VAR`CHAR(10),`
`PAGER`	VAR`CHAR(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`	VAR`CHAR(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;
```

< Back Page 232 of 251 Next >

🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Email Address