Write the following sql queries using the Cape Codd DB on the bottom.. 4. Find o

Question

Write the following sql queries using the Cape Codd DB on the bottom..

4. Find out the total revenue collected by each store.

5. Which department is associated with the highest revenue?

6. Which department is associated with the lowest revenue?

7. Find out the storenumber and zipcode of the store that sold the maximum number of products.

8. Find out other warehouses whose average quantity on hand is not smaller than That of Atlanta warehouse.

9. List sku’s and descriptions for all products whose sku starts with a "2". Use the BETWEEN operator. You may assume all sku's have 6 digits.

10. List the warehouses that currently have on average less than 225 items in stock of the products they carry. Use the HAVING operator.

11. List all order items for products currently out of stock in Atlanta. Use a subquery.

12. Get a list of buyers and their departments for any products out of stock (at any warehouse, not all warehouses). Use a join.

13. Get a list of departments and the total items on order for each department, sorted from largest to smallest quantity.

Explanation / Answer

Question 4:

select r.StoreNumber, SUM(r.OrderTotal) as Revenue
from RETAIL_ORDER r
where r.StoreNumber in (
select distinct StoreNumber from RETAIL_ORDER
)
group by r.StoreNumber;

Question 5:

select SKU_DATA.Department, SUM(ORDER_ITEM.ExtendedPrice) as Revenue
from SKU_DATA, ORDER_ITEM
where SKU_DATA.SKU= ORDER_ITEM.SKU
group by SKU_DATA.Department
order by Revenue desc
limit 1;

Question 6:

select SKU_DATA.Department, SUM(ORDER_ITEM.ExtendedPrice) as Revenue
from SKU_DATA, ORDER_ITEM
where SKU_DATA.SKU= ORDER_ITEM.SKU
group by SKU_DATA.Department
order by Revenue
limit 1;

Question 7:

select
ro.StoreNumber,
ro.StoreZip,
(select sum(t.c) from
(
select count(1) as c, r.StoreNumber as stNum
from ORDER_ITEM o, RETAIL_ORDER r
where r.OrderNumber = o.OrderNumber
group by r.StoreNumber
) t
where t.stNUm = ro.StoreNumber
) as product_count
from RETAIL_ORDER ro
group by ro.StoreNumber, ro.StoreZip
order by product_count desc
limit 1;

Due to the time constraint, i am able to answer only first 4 question of your query.. Request you to please post the other parts in seperate thread.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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