Main page

Typical errors at solution of exercises. Exercise 56

S. Moiseenko

For each class, define the number of ships of this class that were sunked in a battles. Result set: class, number of sunked ships.

Let's examine the following task solution:

SELECT aa.class , SUM( aa.sunks ) Sunks FROM
(
-- 1
SELECT c.class , COUNT( a.ship ) sunks
     FROM outcomes a INNER JOIN ships b ON a.ship = b.name
         INNER JOIN classes c on b.class = c.class
     WHERE a.result ='sunk'
     GROUP BY c.class
UNION
-- 2
SELECT c.class , COUNT( a.ship )
     FROM outcomes a INNER JOIN classes c ON a.ship = c.class
         WHERE a.result ='sunk'
     GROUP by c.class
UNION
-- 3
SELECT c.class , 0 FROM classes c
) aa
     GROUP BY aa.class

Three tables are united in the subquery:

1. A class and a number of the sunk ships which are in the table Ships.

2. A class and a number of the sunk head ships of this class. There is an "excess" already, namely: there is no necessity to use a grouping and accordingly the function COUNT, since a class may have only one head ship and a ship may be sunk only once.

3. Each class with the zero amount of sunk ships. It allows to take into account those classes which have no sunk ships and, hence, do not get in the previous two sets of the records.

Association with the use UNION eliminates duplicates, that, in opinion of the author, allows correctly to process a situation when the sunk head ship is also in the table Ships. At last, the grouping on classes with summation is carried out. Thus last set will not give the contribution to the final result if in a class there are sunk ships that is correct.

However the mistake is covered that two-attribute tuples are united {a class, a number of the sunk ships}. Therefore if there are two sunk ships in some class (for example, Bismarck ), and the head ship is absent in Ships, then two identical tuples will be united.

Bismarck          1

And after elimination of duplicates we receive one sunk ship instead of two.

But it is not all. Even we may count the head ship twice if it is in Ships too. It is fair for a case if there are also other ships of this class sunk in battles. Let's take for an example Bismarck again, but now it is also in the table Ships. Let there is also one more sunk ship (not a head) of this class. Then the first set will give

Bismarck            2

and the second

Bismarck            1

In the result we shall receive

Bismarck            3

Though actually there are only two ships.

» 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


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.