Question

PLease answer in SQL PLUS

vendor (vid, vendorname, address)

mis (fedid, stateid, name, address)

storerequest (requestno, storeno, date)

vendorcatalog (vid, itemno)

storerequestItem (requestno, itemno, quantity)

inventory (itemno, location, quantity)

inventorydes (itemno, itemdescription, unitcost)

store (storeno, name, address, fedid, description)

List the every store number and request number of the request amount is over $1,000

Answer 1

Solution can be achieved by doing joins over tables. Below is query.

Select s.storeno,s.requestno from storerequest s inner join. Storequestitem si on s.requestno=si.requestno inner join inventorydes id on si.itemno=I'd.itemno group by s.storeno,s.requestni having sum(si.qunatity)*sum(id.unitcost )> 1000;

In where condition we did multiplication of 2 columns quantity and unit cost to get total cost.