Question

Provide SQL Statement for the ff queries:

1. List the total computer offer value in Stevens Point

2. List of the computer transactions ordered by transaction value in descending order

3. List of the number and average value of computer requests in Wausau

4. List the number and average value of computer transactions

Tables are :

Tb_Product

Prod_ID, Name, MU

Tb_Offers

Supp_ID, Prod_ID, Price, Quantity

Tb_Transactions

Tran_ID,Supp_ID, Con_ID, Prod_ID, Price, Quantity

Tb_Suppliers

Supp_ID, Name, City

Tb_Request

Con_ID, Prod_ID, Price, Quantity

Answer 1

If there is any issue , you can modify it as I dont have the details in Table and table structure in MySQL database ,

it is similar to all databases , might need some modifications

 1- SELECT SUM(TO.price*TO.Quatity) as total , TP.Name FROM Tb_Offers TO , Tb_Suppliers TS, Tb_Product TP where TO.Supp_ID = TS.Supp_ID and TP.Prod_ID=TO.Prod_ID and TP.Name="computer" where TS.city="Stevens Point"; 2- select SUM(TR.price*TR.Quatity) as total ,TP.Name as name from Tb_Product TP, Tb_Transactions TR where TP.Prod_ID=TR.Prod_ID and name="computer" order By total desc; 3- select count(*) as num , avg(TR.price*TR.Quatity) , TP.name as name , TS.city as city from Tb_Request TR, Tb_Suppliers TS, Tb_Product TP where city="Wausau" and name="computer" and TR.Prod_ID=TP.Prod_ID; 4- select count(*) as num , avg(TR.price*TR.Quatity) , TP.name as name from Tb_Transactions TR, Tb_Product TP where TP.Prod_ID=TR.Prod_ID and name="computer"