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.

And after elimination of duplicates we receive one sunk ship instead of two.

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

and the second

As a result we shall receive

Though actually there are only two ships.

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 headship absent from Ships 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

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:

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

because the comparison will be only fulfilled by name of ship (A), but not by class!

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