Main page
Typical errors at solution of exercises.
Exercise 23
S. Moiseenko
Find the producers, which produce both PC with speed not less than 750MHz, and
Laptops with speed not less than 750 MHz. Output: Maker.
This task is elucidated in FAQ on the website. I'll repeat for ones who live too
far :-).
SELECT DISTINCT maker FROM product
WHERE model IN (SELECT model FROM pc WHERE speed>=750)
OR
model IN (SELECT model FROM laptop WHERE speed>=750)
In the given example the producer will appear in the resulting set that makes
something one: either PC or laptops, because for appearing in the resulting
set, one of the conditions, connected with OR operator, is enough to be held.
This doesn't satisfy the task conditions and is fairly discarded by a system.
Altering Tolstoy, I'll say “All right solutions are right identically, but wrong
solutions are wrong in it's own way”. There is one more attempt to “change” the
situation in the better way:
SELECT DISTINCT maker FROM product a, pc b, laptop c
WHERE b.speed >= 750 AND c.speed >= 750
AND
( a.model = b.model OR a.model = c.model )
Using an equality of predicates,
x AND (y OR z) = (x AND y) OR (x AND z)
we'll perform syntax conversions of the considered query:
SELECT DISTINCT maker FROM product a, pc b, laptop c
WHERE (( b.speed >= 750 AND c.speed >= 750)
AND a.model = b.model )
OR
(( b.speed >= 750 AND c.speed >= 750)
AND a.model = c.model )
The strings that satisfy even one of predicates, connected with an OR operator,
will appear in the resulting set. Let's consider, for example, a query with a
first predicate:
SELECT DISTINCT maker FROM product a, pc b, laptop c
WHERE (( b.speed >= 750 AND c.speed >= 750)
AND a.model = b.model )
Let's rewrite it in a more convenient way from the syntax point of view:
SELECT DISTINCT maker
FROM product a JOIN pc b ON a.model = b.model ,
laptop c
WHERE ( b.speed >= 750 AND c.speed >= 750)
and further
SELECT DISTINCT maker FROM
(
SELECT maker FROM product a JOIN pc b ON a.model = b.model
WHERE b.speed >= 750
) x,
(
SELECT * FROM laptop c WHERE ( c.speed >= 750)
) y
Now we may analyze it. First subquery, which we marked as “x”, joins table PC
with table Product on a foreign key, selecting the producers of PC with the
speed >=750. Second (“y”) selects models of laptops with the speed >=750.
The way of joining “x” and “y” is called Cartesian product. That is the producer
of required PC in the next set will match with EVERY model of laptop, even if
it was produced by ANOTHER maker.
I don't know what about Tolstoy, but as for me it seems like I've made a
mistake. The same rake, but in another bush. Namely, we will get again the
producers that can make only something one. And “another bush” because, if
NOONE produces laptops with a required speed, then we'll get the empty
resulting set. The query from FAQ doesn't give this partially correct result.
The coincidence of results on the main database is completely accidental. It's
turned out that those makers, who produce PC, required by task conditions, also
produce required laptops.
So, in spite of coincidence of results on “visible” database the query is wrong
in any scheme-compatible state of DB.
In conclusion, I'll show the results of original query with an extension of
output rows set. There's a query:
SELECT maker, a.model a_m , b.model b_m , c.model c_m
FROM product a, pc b, laptop c
WHERE (( b.speed >= 750 AND c.speed >= 750)
AND a.model = b.model )
OR
(( b.speed >= 750 AND c.speed >= 750)
AND a.model = c.model )
And there is a couple of strings from the result:
Maker
|
a_m
|
b_m
|
c_m
|
B
|
1121
|
1121
|
1752
|
A
|
1752
|
1121
|
1752
|