w3resource

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


Sample table: item_mast


Sample Solution:

 SELECT AVG(pro_price), company_mast.com_name
   FROM item_mast INNER JOIN company_mast
   ON item_mast.pro_com= company_mast.com_id
   GROUP BY company_mast.com_name
   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 Expression: Display the names of the company whose products have an average price larger than or equal to Rs.350.

Relational Algebra Tree:

Relational Algebra Tree: Display the names of the company whose products have an average price larger than or equal to Rs.350.

Practice Online


Query Visualization:

Duration:

Query visualization of Display the names of the company whose products have an average price larger than or equal to Rs.350 - Duration

Rows:

Query visualization of Display the names of the company whose products have an average price larger than or equal to Rs.350 - Rows

Cost:

Query visualization of Display the names of the company whose products have an average price larger than or equal to Rs.350 - 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.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Grouped LIMIT in PostgreSQL: Show the first N rows for each group?

db=# SELECT * FROM xxx;
 id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  3 |          1 | C
  4 |          1 | D
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
  8 |          2 | H
(8 rows)

I need the first 2 rows (ordered by name) for each section_id, i.e. a result similar to:

id | section_id | name
----+------------+------
  1 |          1 | A
  2 |          1 | B
  5 |          2 | E
  6 |          2 | F
  7 |          3 | G
(5 rows)

PostgreSQL v9.3 you can do a lateral join

select distinct t_outer.section_id, t_top.id, t_top.name from t t_outer
join lateral (
    select * from t t_inner
    where t_inner.section_id = t_outer.section_id
    order by t_inner.name
    limit 2
) t_top on true
order by t_outer.section_id;

Database: PostgreSQL

Ref: https://bit.ly/3AfYwZI

 

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook