Purpose:

This laboratory provides practice in the creation and use of basic and advanced SQL queries involving 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 ids and names for all customers.

2. Display all the data in the sales representative table.

3. Display the names of all customers whose credit limits are $10,000 or more.

4. Display the invoice number for all orders placed by the customer whose id is 1619 on September 13, 2007.  Please note: A date in a condition should be in the form '13-SEP-07'.

5. Display the id and the name for all customers whose sales representative has an id of either 237 or 268.

6. Display the id and description for all products whose type is not AP.

7. Display the id, the description, and the number of items for each product that has between 12 and 30 items. Perform this query two different ways.

8. Display the id, the description, and the total value (product quantity * product price) of each product whose product type is HW.  Assign the column name TOTAL_VALUE to this calculation.

9. Display the id, the description, and the total value (product quantity * product price) of each product whose total value is greater than or equal to $4,000.  Assign the column name TOTAL_VALUE to the calculation.

10. Display the id and the description for each product whose type is either SG or AP using the IN operator.

11. Determine the id and the name of each customer whose name starts with the letter "S".

Part B:

12. Display all the data in the products table.  Order the display by the product description.

13. Display all the data in the products table.  Order the display by product type.  Within each product type, order the display by product id.

14. Determine the number of customers whose balances are less than their credit limits.

15. Display the total of the balances of all customers who are represented by sales representative 237 and whose balances are less than their credit limits.

16. Display the id, the description, and the total value of each product whose number of items is greater than the average number of items for all products. You may want to use a subquery.

17. Display the balance of the customer whose balance is the smallest.

18. Display the id, the name, and the balance of the customer with the largest balance.

19. Display the sales representative's id and the sum of the balances of all customers represented by each of these sales representatives. Group and order the display by the sales representative ids.

20. Display the sales representative's id and the sum of the balances of all customers represented by each of these sales representatives, but limit the result to those sales representatives whose sum is more than $12,000.

21. Display the ids of all products whose description is not known.