Given the database description below: Tb_Supplier(Supp_ID, Name, City, State) Tb
ID: 674624 • Letter: G
Question
Given the database description below: Tb_Supplier(Supp_ID, Name, City, State) Tb_Consumer(Con_ID, Name, City, State) Tb_Product(Prod_ID, Name, Product_Category, Product_Line, Product_Packaging) Tb_Offers(Supp_ID, Prod_ID, Quantity, Price) Tb_Requests(Con_ID, Prod_ID, Quantity, Price) Tb_Transactions(Tran_ID, Supp_ID, Con_ID, Prod_ID, Quantity, Price)
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?
10) Comparative list of supplier states and cities versus consumer states and states and cities with respective number of suppliers and consumers in each city?
Explanation / Answer
1.
Select product_category, count(product_category)
from Tb_product
group by product_category;
2.
Select city
From tb_consumer
group by consumer_id, having count(distinct_consumer)>5;
3.
Select a.city, count(distinct b.product_id)
From Tb-consumer a, Tb_transaction b
Where
a.consumer_id = b.Consumer_id;
4.
Select a.states, count(b.quantity) from Tb_Consumer a,
INNER JOIN ON
Tb_transaction b.
Where (a.consumer_id == b.consumer_id)
5.
Select a.name from Tb_suppliers a
INNER JOIN
Tb_offers o
ON
a.Supplier_id = o.Supplier_id
INNER JOIN ON
Tb_product p
ON
o.product_id = p.product-id
where(Tb_supllier_id = = Offers_id)
Note: Please provide full information from the data base table.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.