Better solution for Question no 1.16 in 1_questions_and_solutions.sql (SQL_exercise_01).

[RohitDahiyardx]

I have a better solution for Question 1.16 in SQL_exercise_01 which can solve the query in just 2 lines.

My solution:

select b.name, a.price, a.name from products a join manufacturers b on (a.Manufacturer= b.code ) where a.price in (select max(price) as p from products group by manufacturer order by max(price) );

Current solution:

select max_price_mapping.name as manu_name, max_price_mapping.price, products_with_manu_name.name as product_name from (SELECT Manufacturers.Name, MAX(Price) price FROM Products, Manufacturers WHERE Manufacturer = Manufacturers.Code GROUP BY Manufacturers.Name) as max_price_mapping left join (select products.*, manufacturers.name manu_name from products join manufacturers on (products.manufacturer = manufacturers.code)) as products_with_manu_name on (max_price_mapping.name = products_with_manu_name.manu_name and max_price_mapping.price = products_with_manu_name.price);

[PD013]

SELECT M.name , P.name , P.price FROM products P INNER JOIN manufacturers M ON M.code = P.manufacturer AND P.Price = ( SELECT MAX(P.Price) FROM products P WHERE P.Manufacturer = M.Code);

Can also be written like this