Question

You are given a database in a MS Access file called ‘DBP-e14-Cape-Codd’ 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 and save them in MS Access as Q1, Q2,… Please submit the MS Access and MS Word to help speed the grading turnout! in Blackboard. (to help you answer these questions, you shoud practice running the practice SQL queries shown in the videoes). Query Question SQL Statement

7 List all records from retail_order and order_item tables for the year 2014. Sort by order number and SKU of Order_Item

8 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?

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

12 How much total money (extendedprice) was each manager responsible for?

Answer 1

ANSWER:

7.
SELECT r.*,o.*
FROM retail_order r JOIN order_item o
where r.OrderYear = 2014
ORDER BY o.SKU ASC,o.OrderNumber ASC;

Created r object for Retail_Order table
and o object for Order_Item table
checking condition for year and sorting by Order Number and SKU

8.
select o.AVG(Price)
From Order_Item o JOIN SKU_DATA s
on o.SKU=s.SKU
GROUP BY s.Department

9.
select w.Manager,i.QuantityOnOrder
FROM WAREHOUSE w
inner join
(
select i.QuantityOnOrder , count(distinct i.SKU) as number_of_ordered
from SKU_DATA s
inner join INVENTORY i on s.SKU=i.SKU
group by i.QuantityOnOrder
having count(distinct i.QuantityOnOrder )
) i
on i.WarehouseID=w.WarehouseID
order by w.Manager,i.QuantityOnOrder

10.

select w.Manager,i.QuantityOnOrder
from
(
select count(i.QuantityOnOrder) as counter
from INVENTORY
group by w.Manager
order by counter desc
limit 1
) as maxcount

inner join

(
select i.QuantityOnOrder , count(distinct i.SKU) as number_of_ordered
from SKU_DATA s
inner join INVENTORY i on s.SKU=i.SKU
)w.Manager