Main page
Typical errors at solution of exercises.
Exercise 46
S. Moiseenko
Point out the battles in which at least three ships of the same country took
part.
Until the resent update of the data in the main database on the site a wrong
solution could pass:
SELECT battle FROM Classes c
LEFT JOIN Ships s ON c.class = s.class
INNER JOIN Outcomes o ON o.ship = s.name OR
c.class = o.ship
GROUP BY battle, country HAVING COUNT(ship) > 3
Look at HAVING COUNT(ship) > 3. Using the right predicate with >=3 clause
was making it incorrect (and that's what it is, in fact). Adjusting the
solution allowed to find the hole in testing which was removed.
So, the query combines classes with ships from the Ships table to define the
country of the vessel. The left conjunction (LEFT JOIN) is purposed to avoid
loosing a class if its ships are not in the Ships table. This class (and not
only this) will be necessary to take into account the leading ships from the
Outcomes table, and that is done in the next (inner) combination. Its predicate
(1)
ON o.ship = s.name OR c.class = o.ship
will form a string, in which the ship column will contain the name of a ship
that took part in battles, if its name coincides with the name of a ship in a
known class from the Ships table, OR if its name coincides with the class name
(a leading ship). If a vessel didn't take part in a battle, the ship column
will contain NULL. Then grouping by a couple of attributes {battle, country}
with HAVING COUNT(ship) >= 3 clause is performed. This allows to choose only
those countries which used more than two vessels for a fight. Notice that COUNT
function will work with NULL-values in the ships column correctly.
» You can read about the usage of COUNT(*) and COUNT(<column name>) in the
help section on the site.
That “OR” of the predicate (1) has the basic mistake of this query. If the same
leading ship is in the Outcomes and Ships tables it will be counted twice for
one battle. It can be seen from the request politely suggested by the author of
the wrong solution (BSV1969):
SELECT battle, country, ship, COUNT(*) qty FROM Classes c
LEFT JOIN Ships s ON c.class = s.class
INNER JOIN Outcomes o ON o.ship = s.name OR
c.class = o.ship
GROUP BY battle, country, ship
I post here only one incorrect string of the result:
Battle
country
ship
qty
Surigao Strait
USA
Tennessee
2
It's an obvious error because the same ship can't be mentioned twice for one
battle (Surigao Strait) and that's restricted by the primary key on the
Outcomes table.
Let's note that the query we examine has another mistake which is described in
FAQ. As the grouping is executed by a pair of attributes { battle, country},
the battle will be printed out more than once if 3 or more ships from a country
participated in it.
There's only one question left. Why was this query admitted by the system while
having 3 remarked mistakes (>3 instead of >=3, incorrect combination and
the possibility of getting duplicates)?
Let's try to clarify this. There were no battles in the database for which the
task condition would be fulfilled. In the correct solution an empty set was
printed out. That's why the wrong ships' number increase did not work with the
right criteria (>=3), as the query passed out Surigao Strait battle, though
in fact 2 ships from the USA took part in it. And the >3 clause gave an
empty set again.
In the testing base there were two sets of 3 ships from different countries for
blocking the solutions with non-excluded duplicates for one battle. At the same
time in one set the leading ship was included in both tables (Outcomes and
Ships). For this set the query we examine was giving a wrong number of 4 ships,
and a right one for the other - 3. That's why the condition in the HAVING -
> 3 predicate was printing out only one battle, solving the problem with
duplicates in the most unusual way.
The SQL world is full of surprises; the more mistakes you make, the greater the
possibility of coincidental results :).
An empty set for the solution of this task on the main database had repeatedly
caused disapproval. That's why I added data to the main database blocking the
considered solution.
» 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