Русский    English

Exercise #70 page 3

Solution 3.7.3

  1. SELECT battle
  2. FROM Classes c LEFT JOIN
  3. Ships s ON c.class = s.class INNER JOIN
  4. Outcomes o ON o.ship = s.name OR
  5. c.class = o.ship
  6. GROUP BY battle, country
  7. 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 item 5.5 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 following query:

  1. SELECT battle, country, ship, COUNT(*) qty
  2. FROM Classes c LEFT JOIN
  3. Ships s ON c.class = s.class INNER JOIN
  4. Outcomes o ON o.ship = s.name OR
  5. c.class = o.ship
  6. 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, as well as the solutions 3.7.1 and 3.7.2, has another mistake which even was described in site's 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 each 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 checking 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 predicate - "> 3" - was printing out only one battle, solving the problem with duplicates in the most unusual way.

The  SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.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. 

Bookmark and Share
Pages 1 2 3 4
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.