Please solved these GROUP by queries. Solve the following queries: 1) Product ca
ID: 3674507 • Letter: P
Question
Please solved these GROUP by queries.
Solve the following queries: 1) Product categories and number of products in each category? 2) Cities having at least 5 different consumers? 3) Cities and number of product types offered in city? 4) List of states and number of car suppliers in each state? 5) Product name and quantity offered in each city? 6) Supplier name and quantity of TV sold in each consumer city? 7) Supplier-consumer-product names such that supplier is selling product to consumer with total value of transactions between supplier and consumer for given product? 8) Supplier-consumer cities and product names such that suppliers in first city are selling product to consumers in second city with total value of transactions between cities for given product? 9) States where the number of suppliers exceeds the number of consumers? Comparative list of supplier states and cities versus consumer states and states and cities with respective number of suppliers and consumers in each city? 10)Explanation / Answer
Please find the quires below :
1) select Product_Category, COUNTProduct_Category) from Tb_Product group by Product_Category
2) select City, COUNT(City) from Tb_Consumer group by city having count(city) >= 5
3) S.City, COUNT(P.Product_Category) from Tb_Supplier S, Tb_Offers O, Tb_Product P where O.Supp_ID = S.Con_ID and O.Prod_ID = P.Prod_ID group by P.Product_Category
5) P.Name, C.City, R.Quantity from Tb_Product P, Tb_Consumer C, Tb_Request R where R.Con_ID = C.Con_ID and R.Prod_ID = P.Prod_ID
6) S.Name, C.City, T.Quantity from Tb_Supplier S, Tb_Consumer C, Tb_Transaction T where C.Con_ID = T.Con_ID and T.Supp_ID = S.Supp_ID