Common mistakes in solving SELECT statement exercises

Database «Ships»

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

Solution 3.12.1

Console

Execute

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;

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;

    Scheme

Three tables are united in the subquery in FROM clause:

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.

Uniting with the use of 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 from Ships table then two identical tuples will be united. 

But it is not all. Even we may count the head ship twice if it is in Ships table 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 Ships table. Let there is also one more sunk ship (not a head) of this class. Then the first set will give

As a result we shall receive