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

Database Lab 2

Joan Laurel
Access Database
Lab 2

1. Determine and print the number of orders taken.

SELECT COUNT(ORDER_NUM) AS [Number Of Orders]
FROM ORDERS;

Number Of Orders
30

2. Determine and print the number of customers who have made an order.

SELECT COUNT(CUST) AS [Number of Customers With Orders]
FROM (SELECT DISTINCT CUST FROM ORDERS, CUSTOMERS WHERE ORDERS.CUST = CUSTOMERS.CUST_NUM);

Number of Customers With Orders
15


3. List the highest target value, the lowest target, and the average target value.

SELECT MAX(TARGET) AS [Maximum], MIN(TARGET) AS [Minimum], AVG(TARGET) AS [Average]
FROM OFFICES;

Maximum Minimum Average
$800,000.00 $300,000.00 $550,000.00

4. Determine and print the number of different products manufactured by ACI as well as the maximum, minimum, and average cost of those products.

SELECT COUNT(PRODUCT_ID) AS [Number of Products], MAX(PRICE) AS [Maximum Price], MIN(PRICE) AS [Minimum Price], AVG(PRICE) AS[Average Price]
FROM (SELECT * FROM PRODUCTS WHERE (MFR_ID = 'ACI'));

Number of Products Maximum Price Minimum Price Average Price
7 $2,750.00 $25.00 $804.29


5. Determine and print the number of customers who have placed orders with Larry Fitch.

SELECT COUNT(CUST_NUM) AS [All Your Customers With Orders Are Belong To Larry Fitch]
FROM (
SELECT DISTINCT CUST_NUM
FROM (
SELECT CUST_NUM FROM CUSTOMERS
WHERE CUST_REP = (
SELECT EMPL_NUM FROM SALESREPS WHERE (NAME = 'Larry Fitch'))
), ORDERS
WHERE CUST_NUM = CUST);

All Your Customers With Orders Are Belong To Larry Fitch
2


6. Determine and print the total and average order amounts for Ace International.


select sum(amount) as [Total], avg(amount) as [Average]
from orders
where (cust = (SELECT CUST_NUM FROM CUSTOMERS WHERE (COMPANY = 'Ace International'))) ;

Total Average
$23,132.00 $11,566.00

7. Determine and print the difference between total sales and total orders for the Los Angeles office.

SELECT *
FROM OFFICES
WHERE ((([OFFICES].[CITY])=('Los Angeles')));

SELECT [OFFICE]
FROM OFFICES
WHERE CITY=('Los Angeles');

SELECT [amount]
FROM orders, [Salesreps from LA]
WHERE rep=empl_num;

SELECT [sales]
FROM [LA office];

SELECT *
FROM salesreps
WHERE rep_office = (SELECT [OFFICE] FROM OFFICES WHERE CITY=('Los Angeles'));


SELECT sum(sales) as [Stated Sales], sum(amount) as [Actual Orders], (sum(sales)-sum(amount)) as [Difference]
from [LA Orders], [LA sales];

Stated Sales Actual Orders Difference
$9,195,065.00 $81,343.00 $9,113,722.00

8. Print each salesperson and determine and print how many customers are served by each of those salespersons. Sort by salesperson name. Include those salespersons with no customers.

SELECT name, company
from salesreps left join customers on salesreps.empl_num = customers.cust_rep
order by salesreps.name;

name company
Bill Adams Acme Mfg.
Bill Adams Three-Way Lin
Bob Smith Ian & Schmidt
Dan Roberts First Corp.
Dan Roberts AAA Investments
Dan Roberts Orion Corp.
Dan Roberts Smithson Corp>
Larry Fitch Zetacorp
Larry Fitch Midwest Systems
Mary James Holm & Landis
Mary James Solomon Inc
Nancy Angelli Peter Brothers
Paul Cruz QMA Assoc.
Paul Cruz JCP Inc.
Paul Cruz Chen Associates
Sam Clark Jones Mfg.
Sam Clark J.P. Sinclair
Sue Smith Carter & Sons
Sue Smith Rico Enterprise
Sue Smith Fred Lewis Corp.
Tom Snyder Ace International


9. Print each city in which an office is located and determine and print the number of salespersons working from each of those offices. Sort by city. Include those offices with no salespersons.

SELECT city, count(empl_num)
from offices left join salesreps on offices.office = salesreps.rep_office
group by city;

city Expr1001
Atlanta 1
Chicago 3
Denver 1
Los Angeles 2
New York 2



10. List each salesperson’s name and their average order amount. Sort by descending average order amount.

SELECT name, avg(amount)
from salesreps, orders
where salesreps.empl_num = orders.rep
group by name;

name Expr1001
Bill Adams $7,865.40
Dan Roberts $8,876.00
Larry Fitch $8,366.71
Mary James $3,552.50
Nancy Angelli $11,477.33
Paul Cruz $1,350.00
Sam Clark $16,479.00
Sue Smith $5,694.00
Tom Snyder $11,566.00


11. List each product’s total and average order amount, including those which have not been ordered.

SELECT description, avg(amount) as [Average order], sum(amount) as [Total order]
from products left join orders on (products.mfr_id = orders.mfr AND products.product_id = orders.product)
group by description;

description Average order Total order
300-lb Brace $2,925.00 $2,925.00
500-lb Brace $31,350.00 $31,350.00
900-lb Brace $4,687.50 $9,375.00
Brace H older
Brace Pin
Brace Retainer
Handle $652.00 $1,304.00
Hinge Pin $2,100.00 $2,100.00
Housing $1,480.00 $1,480.00
Left Hinge $31,500.00 $31,500.00
Motor Mount $1,944.00 $3,888.00
Plate
Ratchet Link $1,264.00 $2,528.00
Reducer $2,840.00 $11,360.00
Retainer
Right Hinge $33,750.00 $67,500.00
Size 1 Widget
Size 2 Widget $2,432.00 $4,864.00
Size 3 Widget $3,745.00 $3,745.00
Size 4 Widget $2,652.00 $7,956.00
Widget Adjuster $375.00 $750.00
Widget Installer $18,750.00 $37,500.00
Widget Remover $27,500.00 $27,500.00


12. List the company name, number of orders, total order amount, and average order amount for the following companies:
Carter & Sons Zetacorp
QMA Assoc. J.P. Sinclair
Midwest Systems

SELECT *
FROM customers
WHERE company in ('Carter & Sons', 'Zetacorp', 'QMA Assoc.', 'J.P. Sinclair', 'Midwest Systems');

SELECT company, count(order_num) AS [Number of Orders], sum(amount) AS [Total order amount], avg(amount) AS [average order amount]
FROM [2_12 companies], orders
where cust_num = cust
GROUP BY company;

company Number of Orders Total order amount average order amount
J.P. Sinclair 1 $31,500.00 $31,500.00
Midwest Systems 4 $3,542.00 $885.50
Zetacorp 2 $47,925.00 $23,962.50




12.
13. List the company name, credit limit, and total order amount for those companies whose total order amount exceeds $25,000. Sort descending by total order amount.

SELECT company, credit_limit, sum(amount)
from customers, orders
where cust_num = cust
group by company, credit_limit;

select *
from [2_13 source]
where credit_limit > 25000;

company credit_limit Expr1002
Ace International $35,000.00 $23,132.00
Acme Mfg. $50,000.00 $35,582.00
First Corp. $65,000.00 $3,978.00
Fred Lewis Corp. $65,000.00 $4,026.00
Holm & Landis $55,000.00 $7,255.00
J.P. Sinclair $35,000.00 $31,500.00
JCP Inc. $50,000.00 $6,445.00
Jones Mfg. $65,000.00 $1,458.00
Midwest Systems $60,000.00 $3,542.00
Peter Brothers $40,000.00 $3,082.00
Rico Enterprise $50,000.00 $3,750.00
Zetacorp $50,000.00 $47,925.00





14. List the salesperson name, company name of who they represent, and total order amount for that company. Include those companies with no orders. Sort by salesperson name.
SELECT *
FROM customers LEFT JOIN orders ON [customers].[cust_num]=[orders].[cust];

SELECT name, company, sum(amount)
from salesreps, [2_14 sub]
where empl_num = rep
group by name, company;

name company Expr1002
Bill Adams Acme Mfg. $35,582.00
Bill Adams JCP Inc. $3,745.00
Dan Roberts First Corp. $3,978.00
Dan Roberts Holm & Landis $150.00
Dan Roberts Ian & Schmidt $22,500.00
Larry Fitch Midwest Systems $3,542.00
Larry Fitch Orion Corp. $7,100.00
Larry Fitch Zetacorp $47,925.00
Mary James Holm & Landis $7,105.00
Nancy Angelli Chen Associates $31,350.00
Nancy Angelli Peter Brothers $3,082.00
Paul Cruz JCP Inc. $2,700.00
Sam Clark J.P. Sinclair $31,500.00
Sam Clark Jones Mfg. $1,458.00
Sue Smith Fred Lewis Corp. $4,026.00
Sue Smith Orion Corp. $15,000.00
Sue Smith Rico Enterprise $3,750.00
Tom Snyder Ace International $23,132.00
Subscribe
Comments for this post were disabled by the author