Using the tables below..I need help with the questions at the bottom. Thanks for

ID: 3711839 • Letter: U

Question

Using the tables below..I need help with the questions at the bottom.

Thanks for any help.

Consumer Table
Tb_Consumers
Con_ID | Name | City

Offers Table
Tb_Offers
Supp_ID | Prod_ID | Price | Quantity

Product Table
Tb_Products
Prod_ID | Name | MU

Requests Table
Tb_Requests
Con_ID | Prod_ID | Price | Quantity

Supplier Table
Tb_Supplier
Supp_ID | Name | City

QUESTIONS:

--List of name of consumer and the supplier city where he/she spent the most.

--List of cheapest product(s) offered by each supplier and corresponding price.

--List of names of consumers having purchases from all supplier cities.

1. List of name of consumer and the supplier city where he/she spent the most.

(Here I'm considering Tb_Transaction as Transaction table)

select distinct a.Name as consumer_name,c.City as supplier_city from Tb_Consumers a

inner join Tb_Transaction b on a.Con_ID = b.Con_ID

inner join Tb_Supplier c on b.Supp_ID = c.Supp_ID

whre c.Supp_ID in

(select Supp_ID from Tb_Transaction group by Con_ID ,Supp_ID having count(*) = (select max(mycount) from

(select Con_ID ,Supp_ID ,count(*) as mycount from Tb_Transaction where Con_ID = b.Con_ID group by Con_ID ,Supp_ID)));

=======================================================================================

2.List of cheapest product(s) offered by each supplier and corresponding price.

select c.Name as supplir_name,a.Name as cheapest_product,b.Price from Tb_Products a

inner join Tb_Offers b on a.Prod_ID = b.Prod_ID

inner join Tb_Supplier c on b.Supp_ID = c.Supp_ID

where b.Prod_ID in

(select Prod_ID from Tb_Offers where Price in ( select min(Price) from Tb_Offers where Supp_ID =

b.Supp_ID));

======================================================================================

3.List of names of consumers having purchases from all supplier cities.

select c.Name as consumer_name from Tb_Transaction a

inner join Tb_Supplier b on a.Supp_ID = b.Supp_ID

inner join Tb_Consumers c on a.Con_ID = c.Con_ID

group by a.Supp_ID having count(distinct b.city) = (select count(distinct city) from Tb_Supplier);

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.