Упражнение 56 стр. 5

Консоль

Выполнить

SELECT t1.class, COUNT(*) AS cnt 
FROM (SELECT a.class, b.name 
      FROM Classes a LEFT JOIN 
-- соединение с Ships без головных кораблей:
           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'
GROUP BY t1.class
-- Выбираются классы кораблей, которые не попали в первый запрос.
-- Это классы, не имеющие потопленных кораблей.
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'
                    );

SELECT t1.class, COUNT(*) AS cnt FROM (SELECT a.class, b.name       FROM Classes a LEFT JOIN -- соединение с Ships без головных кораблей:            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' GROUP BY t1.class -- Выбираются классы кораблей, которые не попали в первый запрос. -- Это классы, не имеющие потопленных кораблей. 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'                     );

    Схема

Консоль

Выполнить

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;

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;

    Схема