Exercise #39 (tips and solutions)
Let's consider a solution which takes into account the dates of battles, but nevertheless is not quite correct:
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 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.
To return to discussion of exercise #39
To solve the problem on SQL-EX.RU