Books and articles about SQL Rambler's Top100 : 23 October 2017 07:15:25
Skip Navigation Links  


Print  Print version

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 ship FROM Outcomes oa
         (WHERE oa.ship = os.ship AND result='damaged'
         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:

   FROM Ships s JOIN Outcomes o ON = o.ship
   JOIN Battles b ON o.battle = 
      AND (MIN(result) = 'damaged' or MAX(result) = 'damaged')
SELECT o.ship
   FROM Classes c JOIN Outcomes o ON c.class = o.ship 
   JOIN Battles b ON o.battle = 
   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:


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

Print  Print version

Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
on each page where used materials are placed.

 Main   Articles    Books Rambler's Top100 Alt   SQL: , ,    SQL Copyright c 2002-2006. All rights reserved.