SQL Exercises: Display the name of each company along with the ID and price for their most expensive product

SQL JOINS: Exercise-25 with Solution

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.

Sample table: company_mast

Sample table: item_mast

Sample Solution:

SELECT A.pro_name, A.pro_price, F.com_name
   FROM item_mast A INNER JOIN company_mast F
   ON A.pro_com = F.com_id
     AND A.pro_price =
     (
       SELECT MAX(A.pro_price)
         FROM item_mast A
         WHERE A.pro_com = F.com_id
     );

Output of the Query:

pro_name	pro_price	com_name
Monitor		5000.00		Samsung
DVD drive	900.00		iBall
Printer		2600.00		Epsion
ZIP drive	250.00		Zebronics
Mother Board	3200.00		Asus
Speaker		550.00		Frontech

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a SQL query to display the names of the company whose products have an average price larger than or equal to Rs.350.
Next: From the following tables write a SQL query to display all the data of employees including their department.