Use the Baseball database you created in the first week for this assignment (copy and save as YourLastNameLec3). Now by analyzing the baseball inventory data, we know one table design is not a good design because we put item information and supplier information into one table. A better design is to have two tables. Please:

(2a). Create a new table for Suppliers, set the primary key, input data (Note: the supplier data are from Week 1 Assignment).

(2b). Change the structure of the original baseball inventory table (Note: removing SupplierName, and SupplierTelephone, but keeping SupplierCode as the foreign key)

Then create Access GUI queries (please save each query)

(2c). Display item id, description, selling price, supplier name for all items.

(2d). Display the item id, description, cost, and units on hand of all items supplied by Beverage Holders

(2e) Display the average selling price of supplier Beverage Holders’s items

(2f) Find a count of different types of items grouped by supplier names

3. Use the baseball database you created in question 2 (now you have two tables) for this assignment. Write SQL queries (please save each query):

(3a). Display the item id, description, and on-hand value (UnitsOnHand*Cost), supplier name of all items.

(3b). Display the average selling price of supplier Logo Goods’ (we only know this supplier name) items. Use Join

(3c). Display the average selling price of supplier Logo Goods’ (we only know this supplier name) items. Use Subquery/nested query

(3d). Display the average selling price of supplier Beverage Holders’s items

(3e). display supplier name and the count of different types of items grouped by supplier names.

(3f). display item id, decription, selling price for the item that has the lowest selling price.

Note: when you join two or more tables together, don’t use INNER JOIN, LEFT JOIN, RIGHT JOIN format, otherwise, you will get 0 point.

Please submit the Access file for Q2 and Q3.

here is the data

 

I BaseballTeam All Access Obje.. ItemID - UnitsOnHan - - SellingPrice - SupplierCoc - SupplierNar - SupplierTel - Description Baseball Cap Cost Search. Logo Goods Beverage Hold 317-555-4747 3663 30 $10.15 $18.76 LG 517-555-3853 Tables 3683 Coasters 12 $7.45 $9.00 BH E BaseballTeam 4563 Coffee Mug 20 $1.85 $4.75 BH Beverage Hold 317-555-4747 4593 Glasses 8 $8.20 $10.75 BH Beverage Hold 317-555-4747 Jacket $44.75 $54.95 LG Logo Goods Al Clothes 5923 12 517-555-3853 5953 Short 10 $14.95 $19.95 AC 616-555-9228 $3.25 $27.45 Sport Towel $6.75 LG Logo Goods Al Clothes Al Clothes 6189 24 517-555-3853 6343 Sweatshirt 9 $34.95 AC 616-555-9228 7810 Tee Shirt 32 $9.50 $14.95 AC 616-555-9228 7930 Coffee Travel Mug 11 $2.90 $3.25 BH Beverage Hold 317-555-4747 $0.00 $0.00