Common mistakes in solving SELECT statement exercises

Database «Ships»

Solution 3.7.3

Console

Execute

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;

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;

    Scheme

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

ON o.ship = s.name OR c.class = o.ship

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:

Console

Execute

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;

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;

    Scheme

I post here only one incorrect string of the result:

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.