1. Use the baseball database you created for this assignment (Note: please copy

Question

1. Use the baseball database you created for this assignment (Note: please copy the Access file and paste, give a name like BaseballLec02). Create Access queries (each question needs a query, please save each query) (a) Display the item id, description, cost, and units on hand of all items supplied by Beverage Holders Display the item id, description, and on-hand value (units on hand*cost) of all items. (b) (c) Display the item id, description, units on hand, and current profit (selling price - cost) of all items that have a selling price greater than S15. 102 Assignment (d) Find the lowest and highest price (e) Display the average selling price of supplier BH's items (f) Find a count of different types of items grouped by suppliers

Explanation / Answer

Question 1)
a) In where clause SupplierName Beverage Hold is given to select only this information of this supplier.

SELECT ItemID, Description, Cost, UnitsOnHand
FROM Baseball
WHERE SupplierName = 'Beverage Hold';

b) Cost and UnitsOnHand of items are multiplied to get the on hand value of an item.

SELECT ItemID, Description, (Cost*UnitsOnHand) AS 'OnHandValue'
FROM Baseball;

c) Current Profit is calculated in SELECT clause using formula Selling Price minus Cost.

SELECT ItemID, Description, UnitsOnHand, (SellingPrice - Cost) AS 'CurrentProfit'
FROM Baseball
WHERE SellingPrice > 15.00;

d) Aggragate function MIN() and MAX() is used to get the minimum and maximum selling price across all items.

SELECT MIN(SellingPrice), MAX(SellingPrice)
FROM Baseball;

e) Aggregate function AVG() is used to get the average selling price of Supplier with code BH.

SELECT AVG(SellingPrice)
FROM Baseball
WHERE SupplierCode = 'BH';

f) Aggregate function COUNT() is used to get count of all items. Group by is used to get the count of items of each supplier.

SELECT SupplierName, COUNT(ItemID)
FROM Baseball
GROUP BY SupplierName;

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.