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


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.