SQL Exercises: Prices for the most expensive products by each company
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_mastCOM_ID COM_NAME ------ ------------- 11 Samsung 12 iBall 13 Epsion 14 Zebronics 15 Asus 16 FrontechSample table: item_mast
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:
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics