Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
w3resource

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

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)

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: From the following tables write a SQL query to calculate the average price of items of each company. Return average value and company name.
Next: From the following tables write a SQL query to find the most expensive product of each company. Return pro_name, pro_price and com_name.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

SQL Server SELECT into existing table. 

INSERT INTO dbo.TABLETWO
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:

INSERT INTO dbo.TABLETWO
  (col1, col2)
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

Database: SQL Server

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