20:29

# Exercise #39 (tips and solutions)

Let's consider a solution which takes into account the dates of battles, but nevertheless is not quite correct:

Console
Execute
`SELECT t.name FROM (SELECT o.ship AS name, battle       FROM Outcomes o      ) t, Battles bWHERE t.battle = b.nameGROUP BY t.nameHAVING (SELECT result         FROM Outcomes, Battles         WHERE ship = t.name AND               battle = name AND               date = MIN(b.date)        ) = 'damaged' AND       (SELECT result         FROM Outcomes, Battles         WHERE ship = t.name AND               battle = name AND               date = MAX(b.date)        ) IN ('damaged', 'ok', 'sunk') AND        COUNT(t.name) > 1;`

In this solution, the ships participated in battles are grouped by names, then only those of them are left which match the following conditions:

•    The ship should be damaged in battle with the minimal date;

•    The result of battle can be any in battle with the maximal date;

•    The number of battles should be more than one.

The logic mistake admitted in this query lies in the fact that if the ship participated more than in two battles, the ship need not be damaged in the first battle (battle with the minimal date). More precisely, the result of its battle should be 'ok', for the above solution to be ceased to give correct result. Really, the ship cannot be sunken, otherwise she would not participate in later battles. If she has been damaged, the query fairly would consider it. But if the sequence of results will be the following: 'ok', 'damaged' and any of three possible results, presented solution did not deduce such ship even if it responds conditions of a problem.