00:53

# Exercise #57 page 2

Exercise 3.13.2

Console
Execute
`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 classHAVING SUM(sunk) > 0`

Exercise 3.13.3

Console
Execute
`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

Console
Execute
`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 head ships 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

 Pages 1 2