Exercise #56

Solution 3.12.1

SELECT aa.class, SUM(aa.sunks) Sunks   
FROM (  
-- 1  
 SELECT c.class, COUNT(a.ship) sunks   
 FROM Outcomes a INNER JOIN   
 Ships b ON a.ship = b.name INNER JOIN   
 Classes c on b.class = c.class  
 WHERE a.result = 'sunk'  
 GROUP BY c.class  
 UNION  
-- 2  
 SELECT c.class, COUNT(a.ship)  
 FROM Outcomes a INNER JOIN   
 Classes c ON a.ship = c.class  
 WHERE a.result = 'sunk'  
 GROUP by c.class  
 UNION  
-- 3  
 SELECT c.class, 0   
 FROM Classes c  
 ) aa  
GROUP BY aa.class;

Three tables are united in the subquery in FROM clause:

1. A class and a number of the sunk ships which are in the table Ships.

2. A class and a number of the sunk head ships of this class. There is an “excess” already, namely: there is no  necessity to use a grouping and accordingly the function COUNT, since a class may have only one head ship and a ship may be sunk only once.

3. Each class with the zero amount of sunk ships. It allows to take into account those classes which have no sunk ships and, hence, do not get in the previous two sets of the records.

Uniting with the use of UNION eliminates duplicates, that, in opinion of the author, allows correctly to process a  situation when the sunk head ship is also in the table Ships. At last, the grouping on classes with summation is  carried out. Thus last set will not give the contribution to the final result if in a class there are sunk ships that is correct.

However the mistake is covered that two-attribute tuples are united {a class, a number of the sunk ships}. Therefore if there are two sunk ships in some class (for example, Bismarck), and the head ship is absent from Ships table then two identical tuples will be united. 

But it is not all. Even we may count the head ship twice if it is in Ships table too. It is fair for a case if there are also other ships of this class sunk in battles. Let’s take for an example Bismarck again, but now it is also in the Ships table. Let there is also one more sunk ship (not a head) of this class. Then the first set will give

As a result we shall receive

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

Solution 3.12.2

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

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?

Solution 3.12.3

SELECT class, SUM(CASE
WHEN result = 'sunk'
THEN 1 ELSE 0
END)
FROM (SELECT c.class, sh.name, o.ship, o.result
FROM Classes c LEFT JOIN
Ships sh ON c.class = sh.class LEFT JOIN
Outcomes o ON ISNULL(sh.name, c.class) = o.ship
) t
GROUP BY class;

Let’s disregard calculation of quantity of the sunken ships. A mistake is not in it, but is in how the set of rows for this calculation was formed.

So, the left join of Classes table with Ships table on a class column allows us to consider also classes which have not the ships in Ships table. It is correct, as we should deduce a given class with value of 0 as quantity of the sunken ships if those are absent.

Further the left join with Outcomes table which contains the information on results of battles has been carried out. The join predicate uses ISNULL function specific for SQL Server, which returns the first argument if it is not NULL, and the second otherwise:

ISNULL(sh.name, c.class) = o.ship

I.e. the name of a ship in Outcomes table compares to the name of a ship received from Ships table or to the class name if the name of a ship contains NULL value. NULLs arise in the previous join when the class has no ships in Ships table; and only in this case!

Let’s again consider the case when there is ship “A” of some class (Class_1) in Ships table, and Outcomes table contains both this ship and the head ship of Class_1 class (the name of head ship coincides with a class name). Let both these ships be sunken. Then the first join will give:

The second join will look for in Outcomes table those rows that satisfy to the above predicate. Such row will be only one:

Solution 3.12.4

SELECT class, SUM(sunks) sunks
FROM (SELECT cl.class, 1 sunks
      FROM Classes cl LEFT JOIN
           Ships sh ON cl.class = sh.class INNER JOIN
           Outcomes ou ON ou.ship = sh.name OR
                          ou.ship = cl.class
           WHERE result='sunk'
       UNION
       SELECT DISTINCT class, 0 sunks
       FROM classes
      ) tab
GROUP BY class;

The first of united queries in the FROM clause gives the following row in the result set for each sunken ship:

class            1

In the main query, these «units» are being summed. But as UNION eliminates duplicates, finally we obtain either 1 (from this first query) or 0 (from second query which considers the classes which are not having the sunken ships) for any quantity of the sunken ships in a class.

If to unite by means of UNION ALL, we’ll get to the solution 3.12.2 which contains a similar mistake.

Solution 3.12.5

SELECT t1.class, COUNT(*) AS cnt   
FROM (SELECT a.class, b.name   
      FROM Classes a LEFT JOIN   
-- join with Ships without head ships:  
           Ships b ON a.class = b.class AND   
                      a.class <> b.name  
      ) AS t1 JOIN   
-- join either by class for head ships or by name:  
      Outcomes t2 ON t1.class = t2.ship OR   
                     t1.name = t2.ship  
WHERE result = 'sunk'  
GROUP BY t1.class  
-- choose ship classes that absent from the first query.  
-- these are classes having no sunken ships.  
UNION  
SELECT class, '0'   
FROM Classes   
WHERE class NOT IN (SELECT DISTINCT t1.class   
                    FROM (SELECT a.class, b.name   
                          FROM Classes a LEFT JOIN   
                               Ships b ON a.class = b.class AND   
                                          a.class <> b.name  
                          ) AS t1 JOIN   
                          Outcomes t2 ON t1.class = t2.ship OR   
                                         t1.name = t2.ship  
                    WHERE result = 'sunk'  
                    );

Solution 3.12.6

SELECT d.class class, (SELECT COUNT(f.result)  
                       FROM (SELECT c.result  
                             FROM Ships b LEFT OUTER JOIN   
                                  Outcomes c ON (b.name = c.ship)  
                             WHERE c.result = 'sunk' AND  
                                   d.class = b.class  
                             UNION ALL  
                             SELECT c.result  
                             FROM Outcomes c  
                             WHERE c.result = 'sunk' AND  
                                   d.class = c.ship  
                             ) f  
                       ) Sunks  
FROM Classes d

For the analysis of two last solution - 3.12.5 and 3.12.6 - we shall consider following variants of data. In Ships table (for the purpose of analysis, meaningful columns are only shown):

In Outcomes table:

Then according to a join predicate in the solution 3.12.5

ON t1.class = t2.ship OR   
        t1.name = t2.ship

ship1_class_1 ship from Ships table will fall twice into result set as its name coincides with the name of the ship from the first row in Outcomes table and its class coincides with name of the ship from the second row. As a result we shall receive 3 sunken ships, though actually them only 2.

The task solution 3.12.6 will give us correct result in this case, as the first query in union (join by ship name) will give ship1_class_1 only, whereas the second gives class_1 only. However this solution is not correct also as will be shown on other variant of data.

In Ships table

In таблице Outcomes:

The first query in union will give us the both of sunken ships in the class_2 class, and the second one gives the head ship of this class. As here UNION ALL is used, the head ship will be twice presented in result set; therefore we again receive 3 ships instead of 2. Cosmetic correction that changes UNION ALL by UNION gives not correct solution, because we get the same mistake as in the solution 3.12.4 when for any quantity of the sunken ships of a class only 1 ship is presented in the result set .

By the way, the solution 3.12.5 gives value of 3 on these data also, but for other reason described above.

T&S

To solve the problem on SQL-EX.RU