Question

Run the following queries from an SQL database containing the following normalized entities:

STORE(SID(pk), SName, SCity, SState, SSqFeet)

ITEM(ISerialNum(pk), IName, IType, IPrice)

SELLS(SID, ISerialNum) (concatenated pk and both fk)

1. Show all the items with name, type, and price and the store that sold them, order by most
expensive item first.
2. Show all stores that sold a specific type of an item. For example: show all stores that sold
cooking items.
3. Show all stores that have a square footage greater than 2000.
4. Find the average price of each item in store number 102.

Answer 1

1)select i.IName,i.IType,i.IPrice,s.SID from ITEM i join SELLS s on i.ISerialNum=s.ISerialNum group by i.IName,i.IType,i.IPrice,s.SID order by i.IPrice desc;

2)select s.SID from ITEM i join SELLS s on i.ISerialNum=s.ISerialNum group by i.IType;

or

if you want directly write in were clause

select s.SID from ITEM i join SELLS s on i.ISerialNum=s.ISerialNum where i.IType="cooking";

3)select SID,SName from STORES where SSqFeet>2000;

4)select SID,avg(price) from STORES where SID=102 group by SID;

Please do upvote thank you.