SQL Exercise: Company whose products have an average price

Last update on April 22 2025 10:56:53 (UTC/GMT +8 hours)

24. Average Price (>=350) by Company

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:

For more Practice: Solve these Related Problems:

Write a SQL query to join company_mast and item_mast, then group by com_name and calculate the average product price for items priced at least 350.
Write a SQL query to list each company along with the average price of products where pro_price >= 350.
Write a SQL query to compute and display the average item price per company, including only those items with a price not less than 350.
Write a SQL query to display company names and the average product price for items meeting the condition pro_price >= 350, using a WHERE clause before grouping.

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.