Question

Consider the tables PRODUCT and BRAND given below:

Table: PRODUCT
PCode	PName	UPrice	Rating	BID
P01	Shampoo	120	6	M03
P02	Toothpaste	54	8	M02
P03	Soap	25	7	M03
P04	Toothpaste	65	4	M04
P05	Soap	38	5	M05
P06	Shampoo	245	6	M05

Table: BRAND
BID	BName
M02	Dant Kanti
M03	Medimix
M04	Pepsodent
M05	Dove

Write SQL queries for the following:

1. Display product name and brand name from the tables PRODUCT and BRAND.
2. Display the structure of the table PRODUCT.
3. Display the average rating of Medimix and Dove brands.
4. Display the name, price, and rating of products in descending order of rating.

Answer 1

1. SELECT PName, BName FROM PRODUCT P, BRAND B WHERE P.BID = B.BID;
2. DESC PRODUCT;
3. SELECT BName, AVG(Rating) FROM PRODUCT P, BRAND B
   WHERE P.BID = B.BID
   GROUP BY BName
   HAVING BName = 'Medimix' OR
   BName = 'Dove';
4. SELECT PName, UPrice, Rating
   FROM PRODUCT
   ORDER BY Rating DESC;