Exercise #18 (tips and solutions)
Exercise #18 (tips and solutions)
In the following solution
SELECT c.maker, a.priceA price
FROM (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;
mssql
🚫
[[ error ]]
[[ column ]] |
---|
[[ value ]] |
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 |
A | 270 |
Meanwhile, correct answer gives only one row:
maker | price |
---|---|
D | 270 |
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 | y | Jet |
A | 270 | n | Matrix |
Second mistake is in possible duplicates, as a maker may have several printers having the same price.