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;
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]

The only mistake of this solution occures 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