Exercise #56 page 5

Solution 3.12.5

Console

Execute

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

Console

Execute

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

name	class
ship1_class_1	class_1
ship2_class_1	class_1

In Outcomes table:

ship	result
ship1_class_1	Sunk
class_1	Sunk

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

name	class
ship1_class_2	class_2
class_2	class_2

In таблице Outcomes:

ship	result
ship1_class_2	sunk
class_2	sunk

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

Pages

mistakes UNION ALL exercise 56

Previous [Exercise #55]

[Exercise #57] Next

SQL Problems and solutions

S. I. Moiseenko

Common mistakes in solving SELECT statement exercises

Database «Ships»

Exercise #56 page 5