Books and articles about SQL Rambler's Top100 Ñìåíèòü ÿçûê íà: Ðóññêèé 29 March 2024 04:23:54


www.sql-ex.ru
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 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

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
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Ðåéòèíã@Mail.ru Rambler's Top100 Alt Óïðàæíåíèÿ ïî SQL: îáó÷åíèå, òåñòèðîâàíèå, ñåðòèôèêàöèÿ ïî ÿçûêó SQL Copyright c 2002-2006. All rights reserved.