Database Design Questions

ECET 450: Laboratory 3

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.

select cust_id, cust_name from customer;

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

select * from rep;

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

select * from customer where cust_limit = 10000;

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’.

select INVOICE_NUM from invoice where cust_id = 1619 AND invoice_date = '13-SEP-07';

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

Select cust_id, cust_name from customer where rep_id = 237 or rep_id = 268;

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

Select * from product where prod_type != ‘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.

Select prod_id, prod_desc, prod_quantity from product where prod_quantity between ‘12’ and ‘20’;

Select prod_id, prod_desc, prod_quantity from product where prod_quantity > 12  and prod_quantity < 20;

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.

Select prod_id, prod_desc, prod_quantity * prod_price as TOTAL_VALUE from product;

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.

Select prod_id, prod_desc, prod_quantity * prod_price as TOTAL_VALUE from product where prod_quantity * prod_price >= 4000;

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

Select prod_id, prod_desc from product where prod_type = ‘SG’ or prod_type = ‘AP’;

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

Select cust_id, cust_name from customer where cust_name like ‘S%’;

Part B:

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

Select * from product;

select * from product order by prod_desc;

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.

Select * from product;

select * from product order by prod_type, prod_id;

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

Select * from customer where cust_balance < cust_limit;

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.

Select * from customer where cust_balance < cust_limit and rep_id = 237, select SUM(cust_balance);

Select SUM(cust_balance) from customer where cust_balance < cust_limit and rep_id = 237;

Select SUM(cust_balance) from customer where rep_id = 237 and cust_balance < cust_limit;

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.