Упражнение 57 (подсказки и решения)

Консоль

Выполнить

SELECT f.class, SUM(count_out) AS cnt
FROM (SELECT t.class, SUM(cnt) AS count_out
      FROM (SELECT c.class, ship, COUNT(*) CNT 
            FROM Classes c LEFT JOIN 
                 Ships s ON c.class = s.class INNER JOIN 
                 Outcomes o ON o.ship = s.name AND 
                               result = 'sunk'
            GROUP BY c.class,ship
            ) AS t
     GROUP BY t.class
     UNION ALL
     SELECT t.class, SUM(cnt) AS count_out
     FROM (SELECT c.class, ship, COUNT(*) cnt 
           FROM Classes c INNER JOIN 
                Outcomes o ON c.class = o.ship AND 
                              o.result = 'sunk' AND 
                              NOT EXISTS (SELECT * 
                                          FROM Ships 
                                          WHERE o.ship = name
                                          ) 
           GROUP BY c.class,ship
           ) AS t
     GROUP BY t.class
     ) AS f
GROUP BY f.class
HAVING 2 < (SELECT SUM(cnt) 
            FROM (SELECT COUNT(c.class) AS cnt 
                  FROM Classes c, Ships s
                  WHERE c.class = s.class AND 
                        c.class = f.class
                  UNION
                  SELECT COUNT(c.class) AS cnt 
                  FROM Classes c, Outcomes o
                  WHERE c.class = o.ship AND 
                        c.class = f.class AND 
                        NOT EXISTS (SELECT * 
                                    FROM Ships 
                                    WHERE o.ship = name
                                    )
                  ) AS k
            );

SELECT f.class, SUM(count_out) AS cnt FROM (SELECT t.class, SUM(cnt) AS count_out       FROM (SELECT c.class, ship, COUNT(*) CNT             FROM Classes c LEFT JOIN                  Ships s ON c.class = s.class INNER JOIN                  Outcomes o ON o.ship = s.name AND                                result = 'sunk'             GROUP BY c.class,ship             ) AS t      GROUP BY t.class      UNION ALL      SELECT t.class, SUM(cnt) AS count_out      FROM (SELECT c.class, ship, COUNT(*) cnt            FROM Classes c INNER JOIN                 Outcomes o ON c.class = o.ship AND                               o.result = 'sunk' AND                               NOT EXISTS (SELECT *                                           FROM Ships                                           WHERE o.ship = name                                           )            GROUP BY c.class,ship            ) AS t      GROUP BY t.class      ) AS f GROUP BY f.class HAVING 2 < (SELECT SUM(cnt)             FROM (SELECT COUNT(c.class) AS cnt                   FROM Classes c, Ships s                   WHERE c.class = s.class AND                         c.class = f.class                   UNION                   SELECT COUNT(c.class) AS cnt                   FROM Classes c, Outcomes o                   WHERE c.class = o.ship AND                         c.class = f.class AND                         NOT EXISTS (SELECT *                                     FROM Ships                                     WHERE o.ship = name                                     )                   ) AS k             );

    Схема