Exercise #57

This exercise is somewhat similar to the exercise 56, i.e. here it is possible to suppose the same mistakes in calculation of the number of sunken ships. However the situation is also aggravated with definition of the total number of the ships in a class. Let’s consider the solution that the check system doesn’t accept.

Exercise 3.13.1

SELECT c.class, SUM(outc)
FROM Classes c 
    LEFT JOIN Ships s ON c.class = s.class 
    LEFT JOIN (SELECT ship, 1 outc
                FROM Outcomes
                WHERE result = 'sunk'
              ) o ON s.name = o.ship 
                  OR c.class = o.ship
GROUP BY c.class
HAVING COUNT(*) > 2 
    AND SUM(outc) IS NOT NULL;

The first left join gives all classes repeating so many times as the number of ships available in the Ships table. If any class doesn’t have ships in this table, it will be noted one time, and it gives us an opportunity to consider the leading ships of the class in the Outcomes table, if any.

Next, one left join is being worked out with the set of sunken ships on the predicate

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

In the calculating column 1 is being inserted, if the name of the sunken ship coincides either with the name of the ship, or with the name of the class from the set had been got earlier. So, here we do try to consider the leading (head) ships.

Finally, the grouping by the classes with selection by the number of ships (rows) in the class is being worked out, and the sum of the sunken ships (units in the column “outs”) is being calculated. Author of this solution offers the rational way to calculate in one grouping both the total number of ships, and the quantity of the sunken ships in the class. The predicate,

SUM(outc) IS NOT NULL

in accordance with the terms of the task, removes from the result such classes that don’t have any sunken ships.

Those who read the analysis of the previous tasks, have already guessed, what the problem is. That’s right, the problem is in the predicate of the second join. But not only in this.
в этом.

Let’s consider the next variant of data. Let for some class class_N in the Ships table we have two ships: ship_1 and ship_2. Besides, in the Outcomes table there is the sunken ship ship_1 and survived the leading ship – class_N.

The first join gives:

We work out the second join:

In the result this class will not get into the resulting set at all, because the condition COUNT(*) > 2 won’t be held, but actually there are three ships. The reason of the mistake lies in the fact that we perform the join only on the sunken ships, simultaneously counting the total number of ships.

Now let’s change a little data in the example. And let the leading ship class_N to be also sunk. Then the result of the join is:

The last two rows will be got in the result of joining the row of the sunken leading ship, as the predicate c.class=o.ship gives “true”. So, instead of one row for the leading ship we get a row for every ship of the class from the Ships table. Totally, instead of

we have

You may try to correct this solution or to use another way on the basis of the inner join and union.

As it will seem surprising, but three absolutely different solutions presented below contain the same mistake, at least, they return the same result on the checking database of a site.

Exercise 3.13.2

SELECT class, SUM(sunk)
FROM (SELECT class, COUNT(*) AS sunk
    FROM Ships a 
        JOIN Outcomes b ON a.name = b.ship 
                        AND a.class <> b.ship
    WHERE result = 'sunk'
    GROUP BY class
    UNION ALL
    SELECT class, '1'
    FROM Classes a 
        JOIN Outcomes b ON a.class = b.ship
    WHERE result = 'sunk'
    UNION ALL
    SELECT class, '0'
    FROM classes
) t
-- classes the number of ships in which great than 2:
WHERE class IN (SELECT t1.class
                FROM (SELECT a.class
                    FROM Classes a 
                        LEFT JOIN Ships b ON a.class = b.class
                    ) t1 
                    LEFT JOIN (SELECT DISTINCT ship
                                FROM Outcomes
                                WHERE ship NOT IN (SELECT name
                                                    FROM Ships
                                                   )
                              ) t2 ON t1.class = t2.ship
                GROUP BY t1.class
                HAVING COUNT(*) > 2
                )
GROUP BY class
HAVING SUM(sunk) > 0;

Exercise 3.13.3

SELECT a.class AS cls, a.num_sunks AS sunk
FROM (SELECT c.class, COUNT (o.ship) AS num_sunks
    FROM Outcomes o 
        LEFT JOIN Ships s ON o.ship = s.name 
        LEFT JOIN Classes c ON s.class = c.class
    WHERE o.result = 'sunk'
    GROUP BY c.class
    ) a,
    (SELECT c1.class
    FROM Ships s1, Classes c1
    WHERE s1.class = c1.class
    GROUP BY c1.class
    HAVING COUNT(name) >= 3
    ) B
WHERE a.class = b.class;

Exercise 3.13.4

SELECT class, COUNT(result) AS sunk
FROM (SELECT class, result, name
    FROM Ships 
        LEFT JOIN Outcomes ON ship=name 
                AND class IS NOT NULL 
                AND result = 'sunk'
     ) T
GROUP BY class
HAVING COUNT(class) > 2 
   AND COUNT(result) > 0;

Analyse subtleties of the above-mentioned solutions, the most beautiful of which, certainly, is 3.13.4. Only one join for which at once it is counted up both quantity of sunken ships, and the total number of the ships in a class. These solutions have a common mistake we have talked above about: the headships which are present in Outcomes table and are absent in Ships table have been not taken into account.

T&S

To solve the problem on SQL-EX.RU