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;