16:42

# Exercise #56 (tips and solutions)

Below is a solution which accounts the sunken ships correctly. This solution has an insignificant mistake; we suggest you to find it by yourself. In case of difficulty, return to solution 3.12.3.

Console
Execute
1. SELECT class, SUM(r) sunks
2. FROM (SELECT name, class, CASE
3.                              WHEN result = 'sunk'
4.                              THEN 1 ELSE 0
5.                           END r
6.       FROM Ships AS s LEFT JOIN
7.            Outcomes AS o ON o.ship = s.name
8.       UNION
9.       SELECT ship, class, CASE
10.                              WHEN result = 'sunk'
11.                              THEN 1 ELSE 0
12.                           END r
13.       FROM Classes c JOIN
14.            (SELECT *
15.             FROM Outcomes
16.             WHERE NOT Ship IN (SELECT name
17.                                FROM Ships)
18.                                ) AS ot ON ot.ship = c.class
19.             ) AS b GROUP BY class;

To solve a problem on SQL-EX.RU