-- how many shoes sold per store 

select storename, categoryid, sum(quantity) from storeinfo si 
join saletransaction st on si.storeid = st.storeid
join saleitem i on st.transactionid = i.transactionid
group by storename, categoryid


select * from v_qualtityreportperstore

create view v_qualtityreportperstore as
select storename, mc.categoryname, sum(quantity) totalitemssold from storeinfo si 
join saletransaction st on si.storeid = st.storeid
join saleitem i on st.transactionid = i.transactionid
join categorymap cm on i.categoryid = cm.categorymapid
join maincategory mc on mc.categoryid = cm.maincategoryid
group by storename, mc.categoryname

-- what is total revenue generated by each store for each main category

create view v_totalrevenureperstore as
select storename, mc.categoryname, sum(finalprice) totalrevenue from storeinfo si 
join saletransaction st on si.storeid = st.storeid
join saleitem i on st.transactionid = i.transactionid
join categorymap cm on i.categoryid = cm.categorymapid
join maincategory mc on mc.categoryid = cm.maincategoryid
group by storename, mc.categoryname


select * from v_totalrevenureperstore where totalrevenue > 1500000

create view v_monthlysale as
select date_part('month', purchasedate) monthofsale, date_part('year', purchasedate) yearofsale, sum(quantity) totalquantity 
from saletransaction st 
join saleitem si on st.transactionid = si.transactionid 
group by date_part('month', purchasedate), date_part('year', purchasedate)

select * from v_monthlysale where totalquantity > 5000

select * from "customer" where name like 'K%'