Azure Jane Lunatic (azurelunatic) wrote,
Azure Jane Lunatic
azurelunatic

Database Lab (backdated, reference)

CIS349 – DATABASE I
JOAN LAUREL
LAB #3

1. List the company name for all customers who have at least one order amount that is greater than the average amount of all orders. Sort by company name.

SELECT distinct company
from customers, orders
where (cust_num = cust) and (amount > (select avg(amount) from orders));

company
Ace International
Acme Mfg.
Chen Associates
Ian & Schmidt
J.P. Sinclair
Orion Corp.
Zetacorp


2. List the company name for all customers who have ALL of their order amounts greater than the average amount of all orders. Sort by company name.

SELECT company, amount
from customers, orders
where (cust_num = cust) and amount > (select avg(amount) from orders);

company amount
Acme Mfg. $27,500.00
Ace International $22,500.00
Chen Associates $31,350.00
Zetacorp $45,000.00
Ian & Schmidt $22,500.00
Orion Corp. $15,000.00
J.P. Sinclair $31,500.00



3. List the salesreps name and title for all salesreps who work in the Eastern region. Sort by title.

SELECT name, title
from salesreps, offices
where (rep_office = office) AND region = ('Eastern');

name title
Sam Clark VP Sales
Mary James Sales Rep
Dan Roberts Sales Rep
Paul Cruz Sales Rep
Bob Smith Sales Mgr
Bill Adams Sales Rep


4. List the salesreps name and sales for all salesreps whose sales are less than the lowest target. Sort descending by sales.



5. List the name, title, and hire date for all salesreps in the Chicago office.

6. List the manufacturer ID, product ID, description, and quantity on hand for all products that have no existing order. Sort by description.

7. List the company name and customer ID for all customers who have placed an order. Sort by company name.

8. List the salesrep name and office city for all salesreps having companies with no orders. Sort by office, then salesrep name.

9. List the manufacturer ID, product ID, and description for all products where the quantity on hand is less the total quantity ordered for that product. Sort by the manufacturer ID and product ID.

10. List the company name of those companies whose total order amount exceeds their credit limit. Sort by company name.

11. List the company name and credit limit of those companies who are served by salesreps in the New York office. Sort by company name.

12. List the name of the companies whose average order amount is greater than the average order amount for all orders.

13. List the salesrep name and company name for all salesreps representing companies whose credit limit exceeds the average of all credit limits.
Subscribe
Comments for this post were disabled by the author