I am trying to solve: States where the number of suppliers exceeds the number of

Question

I am trying to solve:

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?

The following is the database configuration:

Supplier(Supp_ID, Name, City, State)

Consumer(Con_ID, Name, City, State)

Product(Prod_ID, Name, Product_Category, Product_Line, Product_Packaging)

Offers(Supp_ID, Prod_ID, Quantity, Price)

Requests(Con_ID, Prod_ID, Quantity, Price)

Transactions(Tran_ID, Supp_ID, Con_ID, Prod_ID, Quantity, Price)

Any help would be great. Thanks!

Explanation / Answer

SOLUTION:-

1) States where the number of suppliers exceeds the number of consumers :-

select sst.s_number, cst.c_number, IF(sst.s_number> cst.c_number, “Yes”, “No”) Compare
from (select count(*) from Supplier) sst, (select count(*) from Consumer) cst;

2) Comparative list of supplier states and cities versus consumer states and states and cities with respective number of suppliers and consumers in each city:-

select SUP.State, SUP.City, CON.State, CON.City from Supplier SUP, Consumer CON
where SUP.State = CON.State
and SUP.City = CON.City;

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

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

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