Main page
Typical errors at solution of exercises.
Exercise 14
S. Moiseenko
For Product table, receive result set in the form of a table with columns: maker, pc, laptop, and
printer. For each maker, this table must include "yes" if a maker has products of corresponding type or
"no" otherwise. In the first case (yes), specify in brackets (without spaces) the quantity of available
distinct models of corresponding type (i.e. being in PC, Laptop, and Printer tables).
If the manufacturer has models of some type in the Product table, but none of them
is available, then according to the formulation the result should be
yes(0)
Instead of
no
Many find a mistake in the test solution. Claims are reduced to the following query:
SELECT COUNT(*)
FROM Product
WHERE Maker = 'E' AND type='PC'
which gives 3 models of PC for manufacturer Е while "right answer" gives only one
model of a computer for this manufacturer. We need return to the formulation that
tells us:
“… specify in brackets (without spaces) the quantity of available distinct models
of corresponding type (i.e. being in PC, Laptop, and Printer tables).”
In our case, this means that we need specify in brackets a quantity of distinct
models of the personal computer of manufacturer Е in PC table. In terms of SQL it
means:
SELECT COUNT(DISTINCT pc.model)
FROM Product pr
JOIN PC ON pr.model=pc.model
WHERE Maker = 'E'
Main page