Purpose:

This laboratory provides practice in the creation and use of basic and advanced SQL queries involving more than one table in a DB schema.

Procedure:

Using your assigned user name, password, and host string, log in to Oracle SQL*Plus, and open a spool file to collect your dialog. Create queries to answer the following questions. Your queries should be written so that they use only the data provided and display only the data requested.

Don't edit the initial spool file. You will make mistakes and will not be penalized for them. Attach it to the back of your report. Follow the instructor's instruction concerning any additional required items, any needed sign-offs, and the due date of this report.

Part A:

1. Display the invoice number, the invoice date, the customer id, and the customer name for each order in the database.

2. Display the invoice number, the customer id, and the customer name for each order placed on September 12th, 2007.

3. Display the invoice number, the invoice date, the product id, the number of units ordered, and the line price for each line in each order.

4. Display the id and the name of each customer that placed an order on September 12th, 2007, using the IN operator in your query.

5. Display the id and the name of each customer that placed an order on September 12th, 2007, using the EXISTS operator in your query.

6. Display the id and the name of each customer that did not place an order on September 12th, 2007. (Be careful in performing this query.)

7. Display the invoice number, the invoice date, the product id, the product description, and the product type for each line in each order.

8. Display the same data as in question 7, but order the display by product type. Within each type, order the display by invoice number.

9. Display the sales representative's id, last name, and first name of each representative who represents, at a minimum, one customer whose credit is $10,000 using a subquery.

10. Display the same data as in the previous question without using a subquery.

Part B:

11. Display the id and the name of each customer with a current order for a Blender.

12. Display the invoice number and the invoice date for each customer order placed by Charles Appliance and Sport.

13. Display the invoice number and the invoice date for each invoice that contains an Electric Range.

14. Display the invoice number and the invoice date for each invoice that was either placed by Charles Appliance and Sport or whose invoice contains an Electric Range. Use a set operation to perform this query.

15. Display the invoice number and the invoice date for each invoice that was placed by Charles Appliance and Sport and whose invoice contains an Electric Range. Use a set operation to perform this query.

16. Display the invoice number and the invoice date for each invoice that was placed by Charles Appliance and Sport and whose invoice does not contain an Electric Range. Use a set operation to perform this query.

17. Display the product id, the product description, the product price, and the product type for each product whose product price is greater than the price of every part in product type SG. Be sure to correctly choose either the ALL or the ANY operator in your query.

18. Display the same attributes as in the previous question. However, use the other of the two operators: ALL or ANY. This version of the SQL statement provides the answer to a question. What is that question? Add your answer as a comment to your list file.

19. Display the id, the description, the quantity, the invoice number, and the number of units ordered for each product. Make sure to include all products in your output. The order number and the number of ordered units must remain blank for any product that is not contained in an invoice. Order your display by product number.