Question

2. Join (30) Let a database have 3 tables: Retail Order OrderNumber, StoreNumber, StoreZip, OrderMonth, Orderyear,Order Total ) SKU, SKU_Description, Department, Buyer) ID, OrderNumber, SKU, Quantity, Price, ExtendedPrice) SKU Data Orderltem (1) Find Camping Department's Revenue. (2) List buyers who bought some items sold in a store with StoreNumber- 10.

Answer 1

NOTE: I have completed answers for your question along with explanation. Please check and let me know if you have any questions. I will revert back within 24 hours. Thanks for your patience.

1)
select
sum(oi.ExtendedPrice) as revenue
from
OrderItem oi
inner join
sku_data sd
inner join
retail_order ro
on
oi.sku = sd.sku
and
oi.OrderNumber = ro.OrderNumber
and
sd.Department = 'Camping';

Explanation:
1) We need to join all the tables to get the sum of revenue.
2) Then we need to match records for a particular department Camping.
3) For all the above records which department camping we have to get sum of extended price.

2)
select
sd.buyer
from
sku_data sd
inner join
OrderItem oi
inner join
retail_order ro
on
sd.sku = oi.sku
and
oi.OrderNumber = ro.OrderNumber
and
ro.storenumber = 10;

Explanation:
1) Here we need to join all the tables to get the required result.
2) We need to filter for store number 10 once all the tables are joined.
3) In the filtered result we need to identify the buyers.