Exercise #55 (tips and solutions)

The next solution is correctly (but excessively) takes into account ships from Outcomes table.

SELECT t1.class, MIN(b.launched)
FROM (SELECT name, class
      FROM Ships
      UNION
      SELECT ship, class
      FROM Outcomes 
        JOIN Classes ON ship = class 
            AND ship NOT IN(SELECT name
                            FROM Ships
                           )
     ) t1 LEFT JOIN
Ships b ON t1.class = b.class
GROUP BY t1.class;

The only mistake of this solution occurs in case when there is a class without ships in current state of DB. Remark that such situation is possible, because tables Classes and Ships connected by one-to-many relationships.

To return to discussion of exercise #55

To solve a problem on SQL-EX.RU