9 Which manager had the largest amount (quantity) of item-orders? 10 For every s
ID: 3743333 • Letter: 9
Question
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?
SELECT FROM 1 Table Na Condtional Expression WHERE2 GROUP BY 3 HAVING4 Colum List Condtion Colbme ist ORDER BY 6 INVENTORY WarehouseID AREHOUSE ATALOG SKU 2014 ETAIL ORDER OrderNumber StoreNumber StoreZIP OrderMonth Orderfear OrderTotal RDER ITEM OrderNumber SKU Quantity KU DATA SKU Description Buver SKU SKU Description Department CataloqPage DateOnWebSite ExtendedPriceExplanation / Answer
If you have any doubts, please give me comment...
9)
SELECT Manager
FROM WAREHOUSE W, INVENTORY I, SKU_DATA S, ORDER_ITEM O
WHERE W.WarehouseID = I.WarehouseID AND I.SKU = S.SKU AND S.SKU = O.SKU
GROUP BY Manager
HAVING COUNT(*) = (
SELECT MAX(no_of_orders)
FROM (
SELECT Manager, COUNT(*) AS no_of_orders
FROM WAREHOUSE W, INVENTORY I, SKU_DATA S, ORDER_ITEM O
WHERE W.WarehouseID = I.WarehouseID AND I.SKU = S.SKU AND S.SKU = O.SKU GROUP BY Manager
) AS T
);
-- 10)
SELECT StoreNumber, COUNT(*) AS no_of_Departments
FROM RETAIL_ORDER R, ORDER_ITEM O, SKU_DATA S
WHERE R.OrderNumber = O.OrderNumber AND O.SKU = S.SKU
GROUP BY StoreNumber;
-- 11)
SELECT Buyer, SUM(extendedPrice) AS total_money
FROM ORDER_ITEM O INNER JOIN SKU_DATA S ON O.SKU = S.SKU
GROUP BY Buyer;
-- 12)
SELECT Manager, SUM(extendedPrice) AS total_money
FROM ORDER_ITEM O, SKU_DATA S, INVENTORY I, WARHOUSE W
WHERE O.OrderNumber = S.OrderNUmber AND S.SKU = I.SKU AND I.WarehouseID = W.WarehouseID
GROUP BY Manager;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.