Database System

hw2database.mdb

Home >Computer Science homework help >Database System

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

OrderNum	PartNum	NumOrdered	QuotedPrice
21608	AT94	11.0	¤ 21.95
21610	DR93	1.0	¤ 495.00
21610	DW11	1.0	¤ 399.99
21613	KL62	4.0	¤ 329.95
21614	KT03	2.0	¤ 595.00
21617	BV06	2.0	¤ 794.95
21617	CD52	4.0	¤ 150.00
21619	DR93	1.0	¤ 495.00
21623	KV29	2.0	¤ 1,290.00

OrderNum	OrderDate	CustomerNum
21608	10/20/07	148
21610	10/20/07	356
21613	10/21/07	408
21614	10/21/07	282
21617	10/23/07	608
21619	10/23/07	148
21623	10/23/07	608

PartNum	Description	OnHand	Class	Warehouse	Price
AT94	Iron	50.0	HW	3	¤ 24.95
BV06	Home Gym	45.0	SG	2	¤ 794.95
CD52	Microwave Oven	32.0	AP	1	¤ 165.00
DL71	Cordless Drill	21.0	HW	3	¤ 129.95
DR93	Gas Range	8.0	AP	2	¤ 495.00
DW11	Washer	12.0	AP	3	¤ 399.99
FD21	Stand Mixer	22.0	HW	3	¤ 159.95
KL62	Dryer	12.0	AP	1	¤ 349.95
KT03	Dishwasher	8.0	AP	3	¤ 595.00
KV29	Treadmill	9.0	SG	2	¤ 1,390.00

RepNum	LastName	FirstName	Street	City	State	Zip	Commission	Rate
20	Kaiser	Valerie	624 Randall	Grove	FL	33321	¤ 20,542.50	0.05
35	Hull	Richard	532 Jackson	Sheldon	FL	33553	¤ 39,216.00	0.07
65	Perez	Juan	1626 Taylor	Fillmore	FL	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;