Use the Baseball database you created in the first week for this assignment (copy and save as
Question:
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
Expert Answer:
Solution Q2 2a CREATE TABLE Suppliers SupplierCode INT NOT NULL PRIMARY KEY SupplierName VARCHAR50 S... View the full answer
