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) );

just

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);

orignal