SQL Subquery Exercises: Display the name of each company, price for their most expensive product along with their Name

SQL SUBQUERY: Exercise-33 with Solution

33. From the following tables, write a SQL query to find the most expensive product of each company. Return Product Name, Price and Company.

Sample table: company_mast

Sample table: item_mast

Sample Solution:

SELECT P.pro_name AS "Product Name", 
       P.pro_price AS "Price", 
       C.com_name AS "Company"
   FROM item_mast P, company_mast C
   WHERE P.pro_com = C.com_id
     AND P.pro_price =
     (
       SELECT MAX(P.pro_price)
         FROM item_mast P
         WHERE P.pro_com = C.com_id
     );

Output of the Query:

Product Name	Price		Company
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

Pictorial Presentation:

Practice Online

Sample Database:

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous: From the following tables, write a SQL query to calculate the average price of the products and find price which are more than or equal to 350. Return Average Price and Company.
Next: From the following tables, write a SQL query to find those employees whose last name is 'Gabriel' or 'Dosio'. Return emp_idno, emp_fname, emp_lname and emp_dept.