10:14

# Exercise #56 page 4

Solution 3.12.4

Console
Execute
`SELECT class, SUM(sunks) sunks FROM (SELECT cl.class, 1 sunks       FROM Classes cl LEFT JOIN            Ships sh ON cl.class = sh.class INNER JOIN            Outcomes ou ON ou.ship = sh.name OR                          ou.ship = cl.class            WHERE result='sunk'       UNION       SELECT DISTINCT class, 0 sunks        FROM classes      ) tabGROUP BY class;`

The first of united queries in the FROM clause gives the following row in the result set for each sunken ship:

class            1

In the main query, these «units» are being summed. But as UNION eliminates duplicates, finally we obtain either 1 (from this first query) or 0 (from second query which considers the classes which are not having the sunken ships) for any quantity of the sunken ships in a class.

If to unite by means of UNION ALL, we'll get to the solution 3.12.2 which contains a similar mistake.

 Pages 1 2 3 4 5
Tags
The book was updated
several days ago
Wholesale fitted hats . https://exchangesumo.com/obmen/SVBRUB-NIXEUR/