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 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
go
Console
