00:05

Exercise #18 (tips and solutions)

In the following solution

Console
Execute
`SELECT c.maker, a.priceA priceFROM (SELECT MIN(price) priceA  FROM Printer  WHERE Color ='y' ) a INNER JOIN  Printer b ON a.priceA = b.price INNER JOIN  Product c ON b.model = c.model;`
subquery defines lowest price for color printers with subsequent joining with Printer table on this price to find all printers having such a price. Finally, joining with Product table gives us the makers of these printers.

Surely, joining on price might be replaced by simple comparison:

`WHERE price = (SELECT MIN(price) priceA  FROM Printer  WHERE Color ='y' )`

A mistake in the query is not related to said above, but to the fact that the solution determines ANY printers which have price coincident with a minimal price for color printers. Database includes corresponding items. As a result, we obtain

maker price
D 270.0
A 270.0

Meanwhile, correct answer gives only one row:

maker price
D 270.0

Second row is not related to color printer. You can be easyly convinced of it if you'll include additional columns in SELECT clause:

`SELECT c.maker, a.priceA price, color, b.type`
which give

maker price color type
D 270.0 y Jet
A 270.0 n Matrix

Second mistake is in possible duplicates, as a maker may have several printers having the same price.

To solve a problem on SQL-EX.RU