09:11

# 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.shipWHERE result = 'sunk'GROUP BY t1.class-- choose ship classes that absent from the first query.-- these are classes having no sunken ships.UNIONSELECT 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                       ) SunksFROM 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 1 2 3 4 5