Main page

## Typical errors at solution of exercises. Exercise 39

S. Moiseenko

Define the ships that "survived for future battles"; being damaged in one battle, they took part in another.

Here is example of wrong understood condition:

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 f ou ght 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).

...

I return to considered earlier task 39. 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, that perform joining the ships taken part in battles (table “Outcomes”) with the tables “Ships” and “Classes” 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 in the description of the database is said that in the table “Outcomes” can be ships that are absent in “Ships”. That is, if not the leading ship is found, which is absent in the table “Ships” 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.

Thirdly, I don't quite understand the condition:

(min(result) = 'damaged' or max(result) = 'damaged')

In accordance with the description of the enterprise the ship can be:

Damaged
Ok
Sunk

That's why the condition min( result) = 'damaged' will be executed, if in one of the battles the ship was damaged (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 bases. Measures have already been taken, that is test data had been inserted, on which the current solution gives the wrong result. As it must be.

» Given examples here can be done directly on the website by selecting the check box “Without checking” on the page with SELECT exercises.

Main page

 Usage of any materials of this site is possibleonly under condition of mandatory allocation of the direct link to a sitehttp://www.sqlbooks.ruon each page where used materials are placed. Main Articles Books