Common mistakes in solving SELECT statement exercises

Database «Ships»

Here is one more solution to this task, which does not use UNION, but contains another mistake:

Solution 3.12.2

Console

Execute

SELECT classes.class, COUNT(ship) sunked 
FROM Classes FULL JOIN 
 Ships ON classes.class = ships.class LEFT JOIN 
 (SELECT ship 
 FROM Outcomes 
 WHERE result = 'sunk'
 ) s ON s.ship = ships.name OR 
 s.ship = classes.class 
GROUP BY classes.class;

SELECT classes.class, COUNT(ship) sunked FROM Classes FULL JOIN Ships ON classes.class = ships.class LEFT JOIN (SELECT ship FROM Outcomes WHERE result = 'sunk' ) s ON s.ship = ships.name OR s.ship = classes.class GROUP BY classes.class;

    Scheme

The first (full) join

Classes FULL JOIN 
Ships ON classes.class = ships.class

Then the left join with sunken ships from Outcomes table is taken on the following predicate (the set s includes all the sunken ships):

ON s.ship = ships.name OR s.ship = classes.class

Thus, result set includes a ship if her name coincides with a name of sunken ship or if the class coincides with a name of sunken ship. For the examples of data that we considered before, the above query will work correctly, unlike of solution 3.12.1. Indeed, if two sunken ships belong to the Bismarck class and one of these is a head ship absent from Shipa table, the both will be accounted up due to above predicate. On the other hand, if the head ship is presented in Ships table, it does not matter as the predicate will be evaluated as true also.

But where is mistake in the solution? The mistake consists just in the predicate of latter join. Let Ships table include unsunken ships of any class (for example, ships "A" and "B" of Class_1 class). In addition, let Outcomes table have sunken head ship of the same class. Then the following tables will be joined (only columns meaningful for analysis are retained):

and

s.ship = classes.class

As a result the table will be obtained that includes ships which did not be sunken but be considered by this solution:

In other words, sunken head ship is counted up not once but with each ship of the same class in the Ships table (both the sunken and unsunken). Anyhow, but COUNT (ship) = 2, that is incorrect, as only one ship has been sunken.

By the way, it is obviously from what have been said how to correct this solution; this is very simply in so doing. You can add 8 characters. :-) Do you ready to try?