(iii) Display the average rating of Medimix and Dove brands
Answer
Answer by student
SELECT BName, AVG(Rating) AS Average_Rating
FROM PRODUCT, BRAND
WHERE PRODUCT.BID = BRAND.BID
GROUP BY BName
HAVING BName = 'Medimix' OR BName = 'Dove';
Detailed answer by teachoo
- The question asks us to display the average rating of Medimix and Dove brands. The rating of a product is stored in the Rating column of the PRODUCT table. The brand name of a product is stored in the BName column of the BRAND table. These are two different tables that have a common column called BID, which stands for brand ID. This column can be used to join the two tables and fetch the relevant data.
- To display the average rating of Medimix and Dove brands, we need to use the SELECT statement in SQL. The SELECT statement is used to query data from one or more tables in a database.
- The syntax of the SELECT statement is:
SELECT column1, column2, ...
FROM table1, table2, ...
WHERE condition
GROUP BY column3, column4, ...
HAVING condition2;
- The SELECT clause specifies the columns to be displayed in the result. The FROM clause specifies the tables to be queried. The WHERE clause specifies the condition to filter the rows that match the criteria . The G ROUP BY clause specifies the columns to group the rows by a common value. The HAVING clause specifies the condition to filter the groups that match the criteria .
- In this case, we want to display the BName column from the BRAND table and the average of the Rating column from the PRODUCT table. We also want to query both the PRODUCT and BRAND tables . We need to use a condition that matches the BID column from both tables, so that we can get the correct brand name and rating for each product. We need to group the rows by BName , so that we can calculate the average rating for each brand. We also need to use a HAVING clause that filters only Medimix and Dove brands.
- The SQL query for this question is:
-- Display the average rating of Medimix and Dove brands
SELECT BName, AVG(Rating) AS Average_Rating
-- Select the columns BName and average of Rating and give it an alias Average_Rating
FROM PRODUCT, BRAND
-- Query both the PRODUCT and BRAND tables
WHERE PRODUCT.BID = BRAND.BID
-- Use a condition that matches the BID column from both tables
GROUP BY BName
-- Group the rows by BName
HAVING BName = 'Medimix' OR BName = 'Dove';
-- Use a HAVING clause that filters only Medimix and Dove brands
- This query will display the average rating of Medimix and Dove brands in a tabular format. The output will show two rows: one for Medimix and one for Dove. For example:
|
BName |
Average_Rating |
|
Medimix |
6.5 |
|
Dove |
5.5 |
So, this is how we can write an SQL query to display the average rating of Medimix and Dove brands.
