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