CHAPTER 7

3. Write the query that will generate a combined list of customers (from tables CUSTOMER and CUSTOMER_2) that do not include the duplicate customer records. (Note that only the customer named Juan Ortega shows up in both customer tables.)

SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER UNION SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER_2;

4. Write the query that will generate a combined list of customers to include the duplicate customer records.

SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER UNION ALL SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER_2;

5. Write the query that will show only the duplicate customer records.

We have shown both Oracle and MS Access query formats:

Oracle

SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER INTERSECT SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER_2;

MS Access

SELECT C.CUST_LNAME, C.CUST_FNAME FROM CUSTOMER AS C, CUSTOMER_2 AS C2 WHERE C.CUST_LNAME=C2.CUST_LNAME AND C.CUST_FNAME=C2.CUST_FNAME;

Because Access doesn’t support the INTERSECT SQL operator, you need to list only the rows in which all the attributes match.

6. Write the query that will generate only the records that are unique to the CUSTOMER_2 table.

We have shown both Oracle and MS Access query formats:

Oracle

SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER_2 MINUS SELECT CUST_LNAME, CUST_FNAME FROM CUSTOMER;

MS Access

SELECT C2.CUST_LNAME, C2.CUST_FNAME FROM CUSTOMER_2 AS C2 WHERE C2.CUST_LNAME + C2.CUST_FNAME NOT IN (SELECT C1.CUST_LNAME + C1.CUST_FNAME FROM CUSTOMER C1);

Because Access doesn’t support the MINUS SQL operator, you need to list only the rows that are in CUSTOMER_2 that do not have a matching row in CUSTOMER.

7. Write the query to show the invoice number, the customer number, the