2. GROUP BY Scenario 2: The R&D; department of PVFC is conducting a study for it

Question

2. GROUP BY Scenario 2: The R&D; department of PVFC is conducting a study for its new product design. The department needs to find out how existing products are priced differently based on product type, wood finlish and product line. As a data anallyst in the Bl department, you are given the following three sets of requirements and asked to generate three result sets from the PVFD DB for the R&D; For each product ine ID, calculate the average and the highest standard price Display the product line ID and the calculated data if the highest standard price is above $500 FROM Product T BY Product Line ID 500 Page 2 of6 OMSS0, Spring 2018 isenberg School of Management, UMass Arst Select the products of which the product Ine ID is 2 or 3 For each product finish, calculate the number of products and the lowest standard price Display the product finish and the calculated data if the average standard price is below $700 Sort the result set by product finish in alphabetical order · SELECT 'COUNT(. FROM Product T GROUP BY ORDER BY . Select the products of which the product description contains the word "Desk or Table Hint: use LIKE and OR operators For each product line id, calculate the number of products, the average standard price and the difference between the highest and the lowest standard prices (Hint: MAX MIN Display the product line ID and calculated data if the average standard price is above $150 * Sort the result set by the product line ID in ascending order

Explanation / Answer

2.1

SELECT Product_Line_ID, AVG(Standard_Price), MAX(Standard_Price) FROM Product_T GROUP BY Product_Line_ID HAVING MAX(Standard_Price)>500

2.2

SELECT Product_finish, COUNT(Product_Line_ID), MIN(Standard_Price) FROM Product_T WHERE Product_Line_ID in (2 or 3) GROUP BY Product_finish HAVING AVG(Standard_Price)<700 ORDER BY Product_finish

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

---

---

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