You are given a database in a MS Access file that contains data collected in an

Question

You are given a database in a MS Access file that contains data collected in an outdoor sports retail store. The retail store hired you as a data analyst to answer questions in order to help them make business decisions that will help them grow. Write the SQL statement for each query.

Table Name FROM 1 Conditional Expression WHERE2 GROUP BYCondstional HAVING 4 Colum Lise ORDER BY 6 KU DATA ATALOG SK WarehouseID SKU Quantity Price SKU Descriotion Department Buver SKU SKU Description StorezIP QuantityOnHand QuantityOnOrder Manager OrderYear OrderTotal Quer y Question Listall records from retail_order and order_itemtables for the year 2014. Sort SQL Statement 7 by order number and SKU of Order_Item 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? 12How much total money (extendedprice) was each manager responsible for?

Explanation / Answer

Answers)

Q7) List records for retail_order and order_item tables for year 2014.

select * from retail_order inner join order_item

on retail_order.OrderNumber = order_item.OrderNumber;

Q8) For every department, the average order item proce.

select sku_data.Department, avg(price) from order_item inner join sku_data

on order_item.SKU = sku_data.SKU

group by sku_data.Department;

Q9) Which manager has highest quantity of item orders.

select Manager from Warehouse where WarehouseID in

(select WarehouseID from Inventory where SKU in

(select SKU from order_item where quantity =(

select max(Quantity) from order_item)));

Q10) For every store (storeNumber) how many departments are there?

SELECT retail_order.storeNumber, count(sku_data.department)

FROM ((retail_order

INNER JOIN order_item ON retail_order.OrderNumber = order_item.OrderNumber)

INNER JOIN SKU_DATA ON order_item.SKU = SKU_DATA.SKU)

group by retail_order.storeNumber;

Q11) select sum(extendedprice), SKU_DATA.Buyer

from order_item inner join sku_data

on order_item.SKU = sku_data.SKU

group by sku_data.Buyer;

Q12)

SELECT sum(order_item.extendedprice), wareshouse.Manager

FROM (((order_item

INNER JOIN SKU_DATA ON order_item.SKU = SKU_DATA.SKU)

INNER JOIN Inventory ON SKU_DATA.SKU = Inventory.SKU)

INNER JOIN Warehouse ON Inventory.WarehouseID = Warehouse.WarehouseID)

group by wareshouse.Manager;

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

---

---

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