Exercise #37 (tips and solutions)
Consider the following solution that is free from mistakes analyzed in 3.2:
Indeed, a subquery consists of the two queries, the first of which counts ships for each class from the Ships table and the second one counts only those leading ships that are absent in the Ships table. After that the main query sums the numbers for each class and filters classes with many ships.
Please, pay attention that it is necessary to use the UNION ALL clause here. Otherwise duplicate pairs (class, ships number) would be eliminated and a class containing one non-leading ship in the Ships table and a leading one in the Outcomes table would be produced. It is the usual mistake that we observed in 3.2.
What would remain to correct still, if even that solution is rejected by the system? The cause of the rejection is that a leading ship of a class is able to fight several battles and then, the last of the queries in the union counts the same leading ship as many times as many battles it has fought.
To return to discussion of exercise #37