Exercise #57 (tips and solutions)

SELECT f.class, SUM(count_out) AS cnt
FROM (SELECT t.class, SUM(cnt) AS count_out
      FROM (SELECT c.class, ship, COUNT(*) CNT
            FROM Classes c LEFT JOIN
                 Ships s ON c.class = s.class INNER JOIN
                 Outcomes o ON o.ship = s.name AND
                               result = 'sunk'
            GROUP BY c.class,ship
            ) AS t
     GROUP BY t.class
     UNION ALL
     SELECT t.class, SUM(cnt) AS count_out
     FROM (SELECT c.class, ship, COUNT(*) cnt
           FROM Classes c INNER JOIN
                Outcomes o ON c.class = o.ship AND
                              o.result = 'sunk' AND
                              NOT EXISTS (SELECT *
                                          FROM Ships
                                          WHERE o.ship = name
                                          )
           GROUP BY c.class,ship
           ) AS t
     GROUP BY t.class
     ) AS f
GROUP BY f.class
HAVING 2 < (SELECT SUM(cnt)
            FROM (SELECT COUNT(c.class) AS cnt
                  FROM Classes c, Ships s
                  WHERE c.class = s.class AND
                        c.class = f.class
                  UNION
                  SELECT COUNT(c.class) AS cnt
                  FROM Classes c, Outcomes o
                  WHERE c.class = o.ship AND
                        c.class = f.class AND
                        NOT EXISTS (SELECT *
                                    FROM Ships
                                    WHERE o.ship = name
                                    )
                  ) AS k
            );