14:15

# Exercise #70 page 2

Solution 3.7.2

Console
Execute
`SELECT bat FROM (SELECT DISTINCT d.battle AS bat, a.country, COUNT(d.ship) AS s      FROM Outcomes d, Ships b, Classes a      WHERE d.ship = b.name AND             b.class=a.class      GROUP BY d.battle, a.country       UNION       SELECT DISTINCT d.battle AS bat, a.country, COUNT(d.ship) AS s      FROM Outcomes d, Classes a      WHERE d.ship = a.class AND             d.ship NOT IN (SELECT name                            FROM Ships                           )      GROUP BY d.battle, a.country      ) AS t1WHERE s > 2;`

The typical mistake of many beginners is in the order of operations under the resultant set. At first, they implement grouping and then they unioning. In this case the mistake of solution 3.7.1 is absent, but this solution do not returns country, which have three ships taking part in one battle, two of its` are in the table Ships and one (leading ship) is in the Outcomes table.

There was time the checking system accepted the wrong solution:

 Pages 1 2 3 4