22:02

# Exercise #56 page 1

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 (-- 1SELECT 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.classWHERE a.result = 'sunk'GROUP BY c.classUNION-- 2SELECT c.class, COUNT(a.ship)FROM Outcomes a INNER JOIN Classes c ON a.ship = c.classWHERE a.result = 'sunk'GROUP BY c.classUNION-- 3SELECT c.class, 0 FROM Classes c) aaGROUP BY aa.class;`

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.

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

Bismarck 2
and the second

Bismarck 1

As a result we shall receive

Bismarck 3
Though actually there are only two ships.

 Pages 1 2 3 4 5