Database homework

hw1database_lp_2.accdb

Home >Computer Science homework help >Database homework

CustomerNum	CustomerName	Street	City	State	Zip	Balance	CreditLimit	RepNum
148	Ali's Appliance and Sport	2837 Greenway	Fillmore	IA	33336	¤ 6,550.00	¤ 10,000.00	20
282	Brookings Direct	3827 Devon	Grove	IA	33321	¤ 431.50	¤ 15,000.00	35
356	Page's	382 Wildwood	Northfield	IA	33146	¤ 5,785.00	¤ 7,500.00	65
408	The All You Need Shop	1828 Raven	Crystal	IA	33503	¤ 5,285.25	¤ 7,500.00	35
462	Bargains Expected	3829 Central	Grove	IA	33321	¤ 3,412.00	¤ 10,000.00	65
524	Kline's	838 Ridgeland	Fillmore	IA	33336	¤ 12,762.00	¤ 15,000.00	20
608	Johnson's Department Store	372 Oxford	Sheldon	IA	33553	¤ 2,106.00	¤ 5,000.00	65
687	Lea's Sport and Appliance	282 Evergreen	Altonville	IA	32543	¤ 2,851.00	¤ 7,500.00	35
725	Deerfield's Four Seasons	282 Columbia	Sheldon	IA	33553	¤ 248.00	¤ 5,000.00	35
842	All Seasons	28 Lakeview	Grove	IA	33321	¤ 8,221.00	¤ 7,500.00	20

OrderNum	PartNum	NumOrdered	QuotedPrice
21608	AT94	11.0	¤ 11.95
21610	DR93	1.0	¤ 795.00
21610	DW11	1.0	¤ 649.99
21613	KL62	4.0	¤ 609.95
21614	KT03	2.0	¤ 795.00
21617	BV06	2.0	¤ 25.95
21617	CD52	4.0	¤ 250.00
21619	DR93	1.0	¤ 800.00
21623	KV29	2.0	¤ 1,200.00

OrderNum	OrderDate	CustomerNum
21608	9/2/15	148
21610	9/2/15	356
21613	9/3/15	408
21614	9/3/15	282
21617	9/4/15	608
21619	9/4/15	148
21623	9/4/15	608

PartNum	Description	OnHand	Class	Warehouse	Price
AT94	Ironing Board	50.0	HW	3	¤ 14.95
BV06	Yoga Mat	45.0	SG	2	¤ 27.95
CD52	Microwave Oven	32.0	AP	1	¤ 265.00
DL71	Cordless Drill	21.0	HW	3	¤ 79.95
DR93	Gas Range	8.0	AP	2	¤ 895.00
DW11	Washer	12.0	AP	3	¤ 699.99
FD21	Stand Mixer	22.0	HW	3	¤ 259.95
KL62	Dryer	12.0	AP	1	¤ 649.95
KT03	Dishwasher	8.0	AP	3	¤ 795.00
KV29	Rowning Machine	9.0	SG	2	¤ 1,300.00

RepNum	LastName	FirstName	Street	City	State	Zip	Commission	Rate
20	Kaiser	Valerie	624 Randall	Grove	IA	33321	¤ 20,542.50	0.05
35	Hull	Richard	532 Jackson	Sheldon	IA	33553	¤ 39,216.00	0.07
65	Perez	Juan	1626 Taylor	Fillmore	IA	33336	¤ 23,487.00	0.05

SwitchboardID	ItemNumber	ItemText	Command	Argument
1	0	Main Switchboard		Default
1	1	&Maintain Data...	1	2
1	2	&Produce Reports...	1	3
1	3	&Close Database	6
2	0	Maintain Data	0
2	1	&Customer	3	Customer
2	2	&Orders	3	Orders
2	3	&Part	3	Part
2	4	&Rep	3	Rep
2	5	Return to Main &Switchboard	1	1
3	0	Produce Reports	0
3	1	&Customer	4	Customer
3	2	&Orders	4	Orders
3	3	&Part	4	Part
3	4	&Rep	4	Rep
3	5	Return to Main &Switchboard	1	1

PARAMETERS __OrderNum Value; SELECT DISTINCTROW * FROM OrderLine AS Orders WHERE ([__OrderNum] = OrderNum);

SELECT DISTINCTROW * FROM Customer;

SELECT DISTINCTROW * FROM OrderLine;

SELECT DISTINCTROW * FROM Orders;

SELECT DISTINCTROW * FROM Part;

SELECT DISTINCTROW * FROM Rep;

SELECT DISTINCTROW * FROM [Switchboard Items];

SELECT DISTINCTROW * FROM Customer;

SELECT [Orders].[OrderNum], [Orders].[OrderDate], [Orders].[CustomerNum], [OrderLine].[PartNum], [OrderLine].[NumOrdered], [OrderLine].[QuotedPrice] FROM Orders INNER JOIN OrderLine ON [Orders].[OrderNum]=[OrderLine].[OrderNum];

SELECT DISTINCTROW * FROM Part;

SELECT DISTINCTROW * FROM Rep;

SELECT Customer.CustomerName, OrderLine.OrderNum, OrderLine.PartNum, Part.Description, OrderLine.NumOrdered, OrderLine.QuotedPrice, Part.Warehouse, Rep.RepNum FROM Rep INNER JOIN (Part INNER JOIN ((Customer INNER JOIN Orders ON Customer.CustomerNum=Orders.CustomerNum) INNER JOIN OrderLine ON Orders.OrderNum=OrderLine.OrderNum) ON Part.PartNum=OrderLine.PartNum) ON Rep.RepNum=Customer.RepNum ORDER BY Customer.CustomerName, OrderLine.OrderNum, OrderLine.PartNum;