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