Need help Questions For every department, show the average order item price 9 Wh

Question

Need help Questions For every department, show the average order item price 9 Which manager had the largest amount (quantity) of item-orders? 10 For every store (storeNumber), how many departments are there? 11 How much total money (extendedprice) did we make from each buyer? 12 How much total money (extendedprice) was each manager responsible for?

SELECT Name) Table Names Required foreveny FROM , 2 WHERE2 Conditional Expression GROUP BY Co Lat Conn Conditiona HAVING4 Colume List ORDER BY6 ATALOG SK CataloalD SKU SKU Description Department CataloaPace DateOnWebSite WarehouseID OrderNumber OrderNumber Sinas StoreZIP OrderMonth OrderYear OrderTotal SKU Description Department Buyer SKU Ouantity SKU Description te Manager

Explanation / Answer

Q1) For every department, show the average order item price

SELECT SKU_DATA.DEPARTMENT, AVG(ORDER_ITEM.PRICE) AS 'AVG_PRICE' FROM SKU_DATA JOIN ORDER_ITEM ON SKU_DATA.SKU = ORDER_ITEM.SKU GROUP BY (SKU_DATA.DEPARTMENT);

Q2) Which manager had the largest amount (quantity) of item-orders?

SELECT MANAGER, COUNT(ORDER_ITEM.ORDERNUMBER) AS 'MAX_ORDER' FROM WAREHOUSE JOIN INVENTORY ON WAREHOUSE.WAREHOUSEID = INVENTORY.WAREHOUSEID JOIN SKU_DATA ON SKU_DATA.SKU = INVENTORY.SKU JOIN ORDER_ITEM ON ORDER_ITEM.SKU = SKU_DATA.SKU WHERE ROWNUM = 1 GROUP BY (MANAGER) ORDER BY MAX_ORDER DESC;

Q3) For every store (storeNumber), how many departments are there

SELECT STORENUMBER, COUNT(DEPARTMENT) AS 'NO_OF_DEPT' FROM RETAIL_ORDER JOIN ORDER_ITEM ON RETAIL_ORDER.ORDERNUMBER = ORDER_ITEM.ORDERNUMBER JOIN SKU_DATA ON ORDER_ITEM.SKU = SKU_DATA.SKU GROUP BY (STORENUMBER);

Q4) How much total money (extendedprice) did we make from each buyer?

SELECT BUYER, SUM(EXTENDEDPRICE) FROM ORDER_ITEM JOIN SKU_DATA ON ORDER_ITEM.SKU = SKU_DATA.SKU GROUP BY (BUYER);

Please let me know in case of any clarifications required. Thanks!

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

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