Exercise #56 (tips and solutions)

SELECT class, sum(r) sunks
FROM (SELECT name, class, CASE
                             WHEN result = 'sunk'
                             THEN 1 ELSE 0
                          END r
      FROM Ships AS s LEFT JOIN
           Outcomes AS o ON o.ship = s.name
      UNION
      SELECT ship, class, CASE
                             WHEN result = 'sunk'
                             THEN 1 ELSE 0
                          END r
      FROM Classes c JOIN
           (SELECT *
            FROM Outcomes
            WHERE NOT Ship IN (SELECT name
                               FROM Ships)
                               ) AS ot ON ot.ship = c.class
            ) AS b GROUP BY class;