01:57

# Exercise #56 page 2

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;`

The first (full) join

`Classes FULL JOIN Ships ON classes.class = ships.class`
will include all possible classes of ships. Note that in this case LEFT JOIN would be enough in view of constraints on the Ships table, i.e. this table can't have a ship of a class which is absent from Classes table.

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):

Class Name
Class_1 A
Class_1 B

and

Ship (derived table s)
Class_1
on predicate

`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:

Class Name Ship
Class_1 A Class_1
Class_1 B Class_1

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?

 Pages 1 2 3 4 5