Exercise #39

Here is example of wrong understood condition:

Solution 3.4.1

SELECT DISTINCT ship FROM Outcomes os
WHERE EXISTS (SELECT ship
FROM Outcomes oa
WHERE oa.ship = os.ship AND
result = 'damaged'
) AND
EXISTS (SELECT SHIP
FROM Outcomes ou
WHERE ou.ship=os.ship
GROUP BY ship
HAVING COUNT(battle)>1
);

This solution is realized in a relational calculus style, namely, the boats which were damaged (first EXISTS) and which were participated in more then one battle (second EXISTS) are being searched in the Outcomes table.

The mistake is the condition “preserved for future sea-battle” was ignored; this phrase implies, that after the battle, where the ship was damaged, it fought in LATER battle . Thus, reception of the correct decision for this task, need s to review dates of battles, which contain in table of battles (Battles table). 

Solution 3.4.2. The same result gives solution which uses self-join:

SELECT DISTINCT o.ship
FROM Outcomes AS o, Outcomes AS o2
WHERE (o.result = 'damaged' OR
       o2.result = 'damaged'
       ) AND
       o.battle <> o2.battle AND
       o.ship = o2.ship;

Here is applied the join of Outcomes table with itself provided that ship is the same, but fights are different. Besides, the ship has been damaged in one of fights. As easy to see, there was no check of a battle in which the ship has been damaged for earlier date.

Solution 3.4.3

It’s said but true, the following query was accepted by the system until the last days: 

SELECT s.name
FROM Ships s JOIN
Outcomes o ON s.name = o.ship JOIN
Battles b ON o.battle = b.name
GROUP BY s.name
HAVING COUNT(s.name) = 2 AND
(MIN(result) = 'damaged' OR
MAX(result) = 'damaged'
)
UNION
SELECT o.ship
FROM Classes c JOIN
Outcomes o ON c.class = o.ship JOIN
Battles b ON o.battle = b.name
WHERE o.ship NOT IN (SELECT name
FROM Ships
)
GROUP BY o.ship
HAVING COUNT(o.ship) = 2 AND
(MIN(result) = 'damaged' OR
MAX(result) = 'damaged'
);

Firstly, the queries, which perform joining the ships taken part in battles (Outcomes table) with the Ships table and Classes table accordingly, are being united. By the way, the predicate  

o.ship NOT IN (SELECT name FROM Ships)

in the second query is apparently unwanted, because UNION will exclude all possible duplicates.

These joins are not just surplus, they are wrong, as the description of the database says that in the Outcomes table can be ships that are absent from Ships table. That is, if not the leading ship is found, which is absent from the Ships table and which satisfies the task terms, then it will not get into the resulting set of the aforecited query.

Secondly, the predicate

HAVING COUNT (o.ship) = 2

confines possible variants with only two ship battles. And why can’t the ship take part in more than two battles? It was not obligatory sunk after it had been damaged. And he could participate in battles and before damage (for example, with result “ok”). Then, if in the following and the last battle of a ship she has been damaged, the query 3.4.3 will deduce this ship though it and does not respond conditions of the task.

Thirdly, I don’t quite understand the condition: 

(MIN(result) = 'damaged' OR MAX(result) = 'damaged')

However let’s return to a wording. According to the description of a subject area, the ship can be:

• damaged;
• ok;
• sunk.

That’s why the condition min( result) = ‘damaged’ will be executed, if a ship was damaged in one of the battles  (under the natural sorting of the text strings the letter “D” goes earlier that the letters “O” and “S”). However, it doesn’t quite mean that it had been damaged before it took part in the next battle that is required by the terms of the task. Here we need to appreciate the date of the battle. As concerns about MAX(result) = ‘damaged’, then this condition will not be executed, if the results of the battles were different; and if similar, then it will give nothing new in comparison with the first condition on minimum.

And such a superposition of the mistakes gave the right result on the both databases. Precautions have already been taken - test data had been added, and the current solution gives the wrong result now. As it should be. 

T&S

To solve the problem on SQL-EX.RU