SQL Exercises: Prices for the most expensive products by each company

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.

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 columns 'pro_name' as "Product Name", 'pro_price' as "Price," and 'com_name' as "Company"
SELECT P.pro_name AS "Product Name", 
       P.pro_price AS "Price", 
       C.com_name AS "Company"
-- Specifying the tables to retrieve data from ('item_mast' aliased as 'P' and 'company_mast' aliased as 'C') and defining the relationship between them
FROM item_mast P, company_mast C
-- Filtering the results based on the condition that 'pro_com' in 'P' matches 'com_id' in 'C'
WHERE P.pro_com = C.com_id
-- Further filtering the results based on the condition that 'pro_price' in 'P' is equal to the maximum 'pro_price' returned by a subquery
AND P.pro_price =
     -- Subquery: Selecting the maximum 'pro_price' from 'item_mast' (aliased as 'P') where 'pro_com' matches 'com_id' in 'C'
     (
       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

Explanation:

The said SQL query is selecting the product name, price, and company name from two tables 'item_mast' and 'company_mast' where the price of the product is the maximum price among the products of the same company.
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 columns "P.pro_name" as "Product Name", "P.pro_price" as "Price", and "C.com_name" as "Company".
The subquery in the WHERE clause is used to find the maximum price of products of a company, and the outer query is used to get the details of the product with that maximum price for that company

Visual Explanation:

Practice Online

Sample Database:

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: List of products with an average price of 350 or more.
Next SQL Exercise: Find all employees with last names Gabriel or Dosio.