A HOMEWORK IN DATABASE
zoz_15hw1database_lp_2.accdb
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;