﻿ SQL: Company whose products have an average price

SQL Exercise: Company whose products have an average price

SQL JOINS: Exercise-24 with Solution

From the following tables write a SQL query to calculate and find the average price of items of each company higher than or equal to Rs. 350. Return average value and company name.

Sample table: company_mast

```COM_ID COM_NAME
------ -------------
11 Samsung
12 iBall
13 Epsion
14 Zebronics
15 Asus
16 Frontech
```

Sample table: item_mast

``` PRO_ID PRO_NAME                       PRO_PRICE    PRO_COM
------- ------------------------- -------------- ----------
101 Mother Board                    3200.00         15
102 Key Board                        450.00         16
103 ZIP drive                        250.00         14
104 Speaker                          550.00         16
105 Monitor                         5000.00         11
106 DVD drive                        900.00         12
107 CD drive                         800.00         12
108 Printer                         2600.00         13
109 Refill cartridge                 350.00         13
110 Mouse                            250.00         12
```

Sample Solution:

``````-- Selecting the average of 'pro_price' and the company name from the result of an inner join between 'item_mast' and 'company_mast' tables
SELECT AVG(pro_price), company_mast.com_name
-- Specifying the tables to retrieve data from ('item_mast' as the left table and 'company_mast' as the right table)
FROM item_mast
-- Performing an inner join based on the equality of 'pro_com' in 'item_mast' and 'com_id' in 'company_mast'
INNER JOIN company_mast
ON item_mast.pro_com = company_mast.com_id
-- Grouping the results by the company name
GROUP BY company_mast.com_name
-- Applying a filter to the grouped results, including only those with an average 'pro_price' greater than or equal to 350
HAVING AVG(pro_price) >= 350;
``````

Output of the Query:

```          avg          | com_name
-----------------------+----------
5000.0000000000000000 | Samsung
650.0000000000000000 | iBall
1475.0000000000000000 | Epsion
500.0000000000000000 | Frontech
3200.0000000000000000 | Asus
(5 rows)
```

Explanation:

The said SQL query is selecting the average price (AVG(pro_price)) of items and the name of the company (company_mast.com_name) they are associated with, by joining the item_mast and company_mast tables on the pro_com column in the item_mast table and the com_id column in the company_mast table. The results are grouped by the company name and only showing the results where the average price is greater than or equal to 350.

Relational Algebra Expression:

Relational Algebra Tree:

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Display the average price of items of each company.
Next SQL Exercise: Display ID and price of most expensive product.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿