Main page
Typical errors at solution of exercises.
Exercise 57
S. Moiseenko
For every class that has not less than 3 ships in the database determine the
number of ships of this class sunk in the battles, if any. Output: class and
the number of the sunken ships.
Let's consider the solution that the check system doesn't accept.
SELECT c.class, SUM( outc) FROM Classes c
LEFT JOIN Ships s ON c.class=s.class
LEFT JOIN (SELECT ship, 1 outc FROM Outcomes WHERE result='sunk')
o
ON s.name=o.ship OR c.class=o.ship GROUP BY
c.class
HAVING COUNT(*) > 2 AND SUM(outc) IS NOT NULL
The first left join gives all classes repeating so many times as the number of
ships available in the table “Ships”. If any class doesn't have ships in this
table, it will be noted one time, and it gives us an opportunity to consider
the leading ships of the class in the table “Outcomes”, if any.
Next, one left join is being worked out with the set of sunken ships on the
predicate
ON s.name=o.ship OR c.class=o.ship
In the calculating column 1 is being inserted, if the name of the sunken ship
coincides either with the name of the ship, or with the name of the class from
the set had been got earlier. So, here we do try to consider the leading ships.
Finally, the grouping by the classes with selection by the number of ships
(rows) in the class is being worked out, and the sum of the sunken ships (units
in the column “outs”) is being calculated.
As far as I understood, the author offers the rational way to calculate in one
grouping both the total number of ships, and the quantity of the sunken ships
in the class. The predicate,
SUM( outc) IS NOT NULL
in accordance with the terms of the task, removes from the result such classes
that don't have any sunken ships.
Those who read the analysis of the task 46, have already guessed, what the
problem is. That's right, the problem is in the predicate of the second join.
But not only in this.
Let's consider the next variant of data. Let for some class class_N in the table
“Ships” we have two ships: ship_1 and ship_2. Besides, in the table “Outcomes”
there is the sunken ship ship_1 and survived the leading ship – class_N.
The first join gives:
class |
ship
|
Class_N |
ship_1 |
Class_N |
ship_2 |
We work out the second join:
class |
ship
|
outs
|
Class_N |
ship_1 |
1 |
Class_N |
ship_2 |
NULL |
In the result this class will not get into the resulting set at all, because the
condition COUNT(*) > 2 won't be held, but actually there are three ships.
The reason of the mistake lies in the fact that we perform the join only on the
sunken ships, simultaneously counting the total number of ships.
Now let's change a little data in the example. And let the leading ship class_N
to be also sunk. Then the result of the join is:
Class |
ship
|
Outs
|
class_N |
ship_1 |
1 |
class_N |
ship_2 |
NULL |
class_N |
ship_1 |
1 |
class_N |
ship_2 |
1 |
The last two rows will be got in the result of joining the row of the sunken
leading ship, as the predicate c.class=o.ship gives “true”. So, instead of one
row for the leading ship we get a row for every ship of the class from the
table “Ships”. Totally, instead of
we have
You may try to correct this solution or to use another way on the basis of the
inner join and union.
» 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