SQL Exercises: List of products with an average price of 350 or more

SQL SUBQUERY: Exercise-32 with Solution

32. From the following tables write a SQL query to calculate the average price of each manufacturer's product of 350 or more. Return Average Price and Company.

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

 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 'pro_price' as "Average Price" and 'com_name' from the 'company_mast' table as "Company"
SELECT AVG(pro_price) AS "Average Price", 
   company_mast.com_name AS "Company"
-- Specifying the tables to retrieve data from ('item_mast' and 'company_mast') and defining the relationship between them
FROM item_mast, company_mast
-- Filtering the results based on the condition that 'pro_com' in 'item_mast' matches 'com_id' in 'company_mast'
WHERE item_mast.pro_com = company_mast.com_id
-- Grouping the results by 'com_name' from 'company_mast'
GROUP BY company_mast.com_name
-- Filtering the grouped results based on the condition that the average 'pro_price' is greater than or equal to 350
HAVING AVG(pro_price) >= 350;

Output of the Query:

Average Price		Company
5000.0000000000000000	Samsung
650.0000000000000000	iBall
1475.0000000000000000	Epsion
500.0000000000000000	Frontech
3200.0000000000000000	Asus

Explanation:

The said SQL query is selecting the average price of products and the company name from two tables 'item_mast' and 'company_mast', where the average price of products is greater than or equal to 350.
The query is grouping the results by company name, so the average price of each company's products will be displayed separately. The query is joining the two tables on the pro_com column of the item_mast table, and the com_id column of the company_mast table to get the company name for each product. It also renaming the calculated column "AVG(pro_price)" as "Average Price" and "company_mast.com_name" as "Company".
The HAVING clause is used to filter the groups based on aggregate values here is, the average price and only the groups that meet the condition in the HAVING clause will be displayed.

Visual Explanation:

Practice Online

Sample Database:

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Find the average price of each manufacturer products.
Next SQL Exercise: Prices for the most expensive products by each company.