Books and articles about SQL   Сменить язык на: Русский 24 October 2020 20:44:05   Print version

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

As you can see, model 1121 (PC) belongs to the maker B, but model 1752 (laptop) – to the maker A. So, we don't have a reason to consider that both these makers satisfy the task conditions.

Given examples here can be done directly on the website by selecting the check box “Without checking” on the page with SELECT exercises.

Main page Print version


 Usage of any materials of this site is possibleonly under condition of mandatory allocation of the direct link to a sitehttp://www.sqlbooks.ruon each page where used materials are placed. Main Articles Books     Copyright c 2002-2006. All rights reserved. contact