21:00

# Exercise #57 (tips and solutions)

Below is the solution which calculates correctly the number of the sunk ships (though, in our opinion, is rather bulky), however the general number of the ships in a class is incorrectly counted up.

Console
Execute
`SELECT f.class, SUM(count_out) AS cntFROM (SELECT t.class, SUM(cnt) AS count_out      FROM (SELECT c.class, ship, COUNT(*) CNT             FROM Classes c LEFT JOIN                  Ships s ON c.class = s.class INNER JOIN                  Outcomes o ON o.ship = s.name AND                                result = 'sunk'            GROUP BY c.class,ship            ) AS t     GROUP BY t.class     UNION ALL     SELECT t.class, SUM(cnt) AS count_out     FROM (SELECT c.class, ship, COUNT(*) cnt            FROM Classes c INNER JOIN                 Outcomes o ON c.class = o.ship AND                               o.result = 'sunk' AND                               NOT EXISTS (SELECT *                                           FROM Ships                                           WHERE o.ship = name                                          )            GROUP BY c.class,ship           ) AS t     GROUP BY t.class     ) AS fGROUP BY f.classHAVING 2 < (SELECT SUM(cnt)             FROM (SELECT COUNT(c.class) AS cnt                   FROM Classes c, Ships s                  WHERE c.class = s.class AND                         c.class = f.class                  UNION                  SELECT COUNT(c.class) AS cnt                   FROM Classes c, Outcomes o                  WHERE c.class = o.ship AND                         c.class = f.class AND                         NOT EXISTS (SELECT *                                     FROM Ships                                     WHERE o.ship = name                                    )                  ) AS k            );`

Calculation of the total number of the ships in a class is carried out here in HAVING clause of the main query. In a subquery of this clause for each class in main query the unite of number of the ships in Ships table with number of the ships (head ships) of Outcomes table is carried out provided that latter ships have not been considered before (they are not present in Ships table).

It is obvious that as one-attribute relations are united by means of UNION operator we receive incorrect result in view of elimination of duplicates if one ship is available in each of the sets. However here it should not be a mistake, as we select the classes having more than two ships in sum. And other possible variants should not be here, as the head ship, if any, is only one (despite of excessive use of COUNT in the second query). And still the mistake is discovered here. The matter is that the head ship might take part in more than one battle, so we are considering she as many times as the number of her battles, certainly, if she is not present in Ships table.

It is simple to correct this solution, we suggest you to make it by yourself. However it is possible to write more simple (and more effective also) query.

To solve a problem on SQL-EX.RU