Exercise #57 (tips and solutions)
Below is the solution which calculates correctly the number of the sunk ships (though, in our opinion, is rather bulky), however the general number of the ships in a class is incorrectly counted up.
Calculation of the total number of the ships in a class is carried out here in HAVING clause of the main query. In a subquery of this clause for each class in main query the unite of number of the ships in Ships table with number of the ships (head ships) of Outcomes table is carried out provided that latter ships have not been considered before (they are not present in Ships table).
It is obvious that as one-attribute relations are united by means of UNION operator we receive incorrect result in view of elimination of duplicates if one ship is available in each of the sets. However here it should not be a mistake, as we select the classes having more than two ships in sum. And other possible variants should not be here, as the head ship, if any, is only one (despite of excessive use of COUNT in the second query). And still the mistake is discovered here. The matter is that the head ship might take part in more than one battle, so we are considering she as many times as the number of her battles, certainly, if she is not present in Ships table.
It is simple to correct this solution, we suggest you to make it by yourself. However it is possible to write more simple (and more effective also) query.