Exercise #37 (tips and solutions)

Consider the following solution that is free from mistakes analyzed here:

SELECT t1.class
FROM (SELECT a.class AS class, COUNT(b.name) AS coun
    FROM Classes a 
        LEFT JOIN Ships b ON b.class = a.class
    GROUP BY a.class
    UNION ALL
    SELECT a1.class AS class, COUNT(ship) AS coun
    FROM Classes a1 
        LEFT JOIN Outcomes d ON d.ship = a1.class
    WHERE d.ship NOT IN (SELECT b.name
                         FROM Ships b
                        )
    GROUP BY a1.class
    ) t1
GROUP BY t1.class
HAVING SUM(t1.coun) = 1;

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 have observed yet.

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

To solve a problem on SQL-EX.RU