Характерные ошибки при решении задач на написание запросов на выборку (SELECT)

База данных «Корабли»

Для каждого класса определите год, когда был спущен на воду первый корабль этого класса. Если год спуска на воду головного корабля неизвестен, определите минимальный год спуска на воду кораблей этого класса. Вывести: класс, год.

Видимо, ошибки вызваны наличием ловушек в предыдущих задачах. При решении задач со сложностью 1 делаются попытки учесть все и вся. Сам по себе учет излишних фактов не делает решение неверным, разве что увеличивает стоимость выполнения запроса, но дело в том, что при этом допускались столь характерные ошибки, что автор решил рассмотреть несколько подобных решений.

Что же лишнего пытаются учесть при решении этой задачи? Это — головные корабли из таблицы Outcomes. Таблица Outcomes вообще не нужна для решения этой задачи. Ведь нам нужно определить год, который является атрибутом таблицы Ships. Поэтому даже если в таблице Outcomes есть головной корабль, отсутствующий в таблице Ships, то мы все равно не знаем года его спуска на воду. В случае же отсутствия в БД других кораблей этого класса наличие такого корабля все равно ничего не дает, так как результат должен выглядеть следующим образом:

поскольку в задании сказано «для каждого класса». Тем самым утверждается, что для данного класса год спуска первого корабля неизвестен. Но такую строку в результирующем наборе мы можем получить и без таблицы Outcomes, выполнив внешнее соединение таблицы Classes с таблицей Ships. Перейдем к анализу решений, в которых были допущены ошибки при учете кораблей из таблицы Outcomes.

Решение 3.11.1

Консоль

Выполнить

SELECT C.class , launched
FROM Classes C ,
 (SELECT name , class , launched
 FROM Ships
 UNION
 SELECT ship , ship , NULL
 FROM Outcomes O
 WHERE NOT EXISTS (SELECT *
 FROM Ships S
 WHERE S.class = O.ship
 )
 UNION
 SELECT ship , ship , MIN(launched)
 FROM Ships S , 
 Outcomes O
 WHERE S.class = O.ship
 GROUP BY ship 
 ) S
WHERE C.class = S.name;

SELECT C.class , launched FROM Classes C , (SELECT name , class , launched FROM Ships UNION SELECT ship , ship , NULL FROM Outcomes O WHERE NOT EXISTS (SELECT * FROM Ships S WHERE S.class = O.ship ) UNION SELECT ship , ship , MIN(launched) FROM Ships S , Outcomes O WHERE S.class = O.ship GROUP BY ship ) S WHERE C.class = S.name;

    Схема

Рассмотрим подзапрос S, в котором объединяются три запроса, результирующий набор каждого из которых содержит три столбца {имя корабля, класс, год спуска на воду}. В первом из них выбираются все корабли из таблицы Ships. Во втором выбираются корабли из Outcomes, имя которых не совпадает ни с одним классом кораблей из таблицы Ships. При этом в качестве года спуска на воду используется NULL, что правильно, а имя корабля ассоциируется с именем класса (SELECT ship, ship, NULL). Последнее не является здесь ошибкой, так как в последующем будут отбираться только головные корабли:

WHERE C.class = S.name

Наконец, в третьем запросе определяется минимальный год для классов кораблей, у которых головной корабль имеется в таблице Outcomes.

Принципиальная ошибка заключается в наличии ситуации, когда головной корабль из Outcomes присутствует также и в таблице Ships с неизвестным годом спуска на воду. Кроме того, имеются и другие корабли того же класса с известным годом спуска на воду. Тогда первый запрос даст строку с годом NULL, а третий — с минимальным годом по этому классу. В итоге получим две строки, которые не являются дубликатами и, следовательно, не будут исключены использованием UNION.

Наконец, условие отбора только по головным кораблям исключит из рассмотрения классы, вообще не имеющие головных кораблей.