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:

Practice Online

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.