B D E F G 1 Purchase_Order ID 2 3 4 Raw Material_Purchase_Price 3 10 5 6 7 Raw_Materials Code 3 2 2 1 1 2 2 1 1 8 9 10 11 1 3 6 12 4 12 5 1 1 4 12 11 6 7 11 11 12 13 14 15 16 17 18 4 19 20 21 22 23 Purchase_Order Date Purchase_Employee_ID 20500 11/1/19 20501 11/5/19 20502 11/10/19 20503 11/13/19 20504 11/16/19 20505 11/19/19 20506 11/23/19 20506 11/23/19 20507 11/26/19 20508 11/29/19 20509 12/4/19 20510 12/9/19 20511 12/12/19 20512 12/16/19 20513 12/19/19 20514 12/24/19 20515 12/28/19 20516 1/2/20 20517 1/5/20 20518 1/9/20 20519 1/14/20 20520 1/18/20 20521 1/23/20 20522 1/26/20 20523 1/31/20 20524 2/4/20 20525 2/8/20 20525 2/8/20 20525 20526 2/13/20 20527 2/16/20 20527 2/16/20 20528 2/21/20 20529 2/24/20 20530 2/28/20 20530 2/28/20 20531 3/4/20 20532 3/7/20 20533 3/12/20 20534 3/15/20 20535 3/19/20 20536 3/23/20 20537 3/27/20 20538 3/31/20 20539 4/4/20 20540 4/8/20 20541 4/11/20 20541 4/11/20 20542 4/14/20 20542 4/14/20 20543 4/18/20 20544 4/21/20 24 25 26 27 Supplier_ID 1009 1009 1009 1008 1009 1008 1009 1009 1009 1009 1009 1009 1008 1009 1008 1009 1009 1008 1009 1009 1008 1008 1008 1009 1009 1009 1008 1009 1008 1009 1009 1009 1008 1008 1008 1009 1008 1008 1009 1008 1009 1009 1008 1009 1008 1008 1009 1008 1009 1009 1009 1008 Quantity_Purchased 1001 1003 1003 1002 1001 1001 1001 1003 1001 1001 1001 1001 1003 1001 1002 1002 1002 1003 1001 1003 1001 1003 1001 1002 1001 1001 1002 1002 1003 1001 1001 1003 1001 1003 1001 1003 1003 1002 1002 1002 1 2 3 3 2 3 1 1 2 1 2 1 3 3 1 2 3 3 3 2 2 2 2 1 3 3 3 3 3 1 1 2 3 2 11 10 12 1 1 12 6 10 8 1 5 5 2 6 19.99 29 29 44.99 19.99 19.99 19.99 29 19.99 19.99 19.99 19.99 29 19.99 44.99 44.99 44.99 29 19.99 29 19.99 29 19.99 44.99 19.99 19.99 44.99 44.99 29 19.99 19.99 29 19.99 29 19.99 29 29 44.99 44.99 44.99 44.99 19.99 29 44.99 29 19.99 29 29 29 44.99 44.99 28 29 30 2/8/20 31 32 33 34 35 36 37 38 39 1002 40 41 42 43 44 45 46 11 12 6 3 10 2 2 2 2 7 2 12 5 7 12 6 5 6 6 3 10 3 6 5 1 1 4 2 1 3 2 3 3 47 48 49 50 51 1001 1003 1002 1003 1001 1003 1003 1003 1002 1002 1002 1 1 52 53 1 2 44.99 1 Supplier_ID 2 1 3 2 Supplier_St MN WA 3 AR 4 Supplier_company_name Supplier_Address Supplier_City Northern Brewery Homebrew Sl 6021 Lyndale Ave S Minneapolis Hops Direct, LLC 686 Green Valley Road Mabton The Home Brewery 455 E. Township St. Fayetteville The Payroll Company 408 N. Walton Blvd Bentonville City of Fayetteville - Water 113 West Mountain Stre Fayetteville Southwestern Gas P.O. Box 98890 ILas Vegas Bob's Repair 3541 Horse Meadow Dri Fayetteville Farmer's Insurance 1943 College Avenue Fayetteville Lindsey Management Co. 1200 East Joyce Blvd. Fayetteville Supplier_Zip Supplier_Phone 55419 612-843-4444 98935 509-837-3616 72703 479-587-1440 72712 1-866-899-8439 72701 479-521-1258 877-860-6020 72703 479-345-2345 72701 479-236-0013 72703 479-521-6686 Notes Bulk Malt Supplier Bulk Hops Supplier Small batch and Speciality Ingredient Supplier Payroll Water Electric and Gas Repairs Insurance Rent AR AR NV AR 6 5 7 6 7 9 8 10 9 AR AR 11 1 Raw Materials_Code Raw_Materials_Description 2 1001 Malt - Barley 3 1002 Malt - Wheat 4 1003 Hops 5 Raw_Materials_Price $19.99 $44.99 $29.00 Required: 1. What is the Foreign Key in the Purchase_Orders table that allows you to relate it to the Suppliers table? O Purchase_Order_ID O Employee_ID Supplier_ID O Customer_ID 2. What is the Foreign Key in the Purchase_Orders table that allows you to relate it to the Raw_Materials table? O Supplier_ID O Purchase_Order_ID O Raw_Materials_Code O Customer_ID 3. How many Purchase Orders for Hops were created for suppliers in Arkansas? O 52 04 05 08 4. How many Purchase Orders were created for suppliers in Arkansas (for any products)? 52 5 O 18 5. Change the PivotTable so that you see the sum of quantity purchased associated with each product and state. What was the quantity of hops purchased from suppliers in Minnesota? 50 42 344 28
