(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. Write the query to show the invoice number, the customer number, the customer name, the invoice date, and the invoice amount for all customers with a customer balance of $1,000 or more. This command will run in Oracle and in MS Access: CUSTOMER.CUST_NUM, CUST_LNAME, CUST_FNAME, INV_DATE, INV_AMOUNT FROM INVOICE INNER JOIN CUSTOMER ON INVOICE.CUST_NUM=CUSTOMER.CUST_NUM WHERE CUST_BALANCE>=1000; Write the query that will show the invoice number, the invoice amount, the average invoice amount, and the difference between the average invoice amount and the actual invoice amount. There are at least two ways to do this query. SELECT INV_NUM, AVG_INV, (INV_AMOUNT - AVG_INV) AS DIFF FROM INVOICE, (SELECT AVG(INV_AMOUNT) AS AVG_INV FROM INVOICE) GROUP BY INV_NUM, AVG_INV, INV_AMOUNT- AVG_INV Another way to write this query is: SELECT INV_NUM, INV_AMOUNT, (SELECT AVG(INV_AMOUNT) FROM INVOICE) AS AVG_INV, (INV_AMOUNT-(SELECT AVG(INV_AMOUNT) FROM INVOICE)) AS DIFF FROM INVOICE GROUP BY INV_NUM, INV_AMOUNT; The preceding code examples will run in both Oracle and MS Access. Modify the CUSTOMER to included two new attributes: CUST_DOB and CUST_AGE. Customer 1000 was born on March 15, 1979, and customer 1001 was born on December 22, 1988. In Oracle:
(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. Write the query to show the invoice number, the customer number, the customer name, the invoice date, and the invoice amount for all customers with a customer balance of $1,000 or more. This command will run in Oracle and in MS Access: CUSTOMER.CUST_NUM, CUST_LNAME, CUST_FNAME, INV_DATE, INV_AMOUNT FROM INVOICE INNER JOIN CUSTOMER ON INVOICE.CUST_NUM=CUSTOMER.CUST_NUM WHERE CUST_BALANCE>=1000; Write the query that will show the invoice number, the invoice amount, the average invoice amount, and the difference between the average invoice amount and the actual invoice amount. There are at least two ways to do this query. SELECT INV_NUM, AVG_INV, (INV_AMOUNT - AVG_INV) AS DIFF FROM INVOICE, (SELECT AVG(INV_AMOUNT) AS AVG_INV FROM INVOICE) GROUP BY INV_NUM, AVG_INV, INV_AMOUNT- AVG_INV Another way to write this query is: SELECT INV_NUM, INV_AMOUNT, (SELECT AVG(INV_AMOUNT) FROM INVOICE) AS AVG_INV, (INV_AMOUNT-(SELECT AVG(INV_AMOUNT) FROM INVOICE)) AS DIFF FROM INVOICE GROUP BY INV_NUM, INV_AMOUNT; The preceding code examples will run in both Oracle and MS Access. Modify the CUSTOMER to included two new attributes: CUST_DOB and CUST_AGE. Customer 1000 was born on March 15, 1979, and customer 1001 was born on December 22, 1988. In Oracle: