Books and articles about SQL Rambler's Top100 Ñìåíèòü ÿçûê íà: Ðóññêèé 19 March 2024 09:42:02


www.sql-ex.ru
Skip Navigation Links  

 

Print  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  Print version


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Ðåéòèíã@Mail.ru Rambler's Top100 Alt Óïðàæíåíèÿ ïî SQL: îáó÷åíèå, òåñòèðîâàíèå, ñåðòèôèêàöèÿ ïî ÿçûêó SQL Copyright c 2002-2006. All rights reserved.