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

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

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

Решение 3.8.1. Не очень оптимальное решение и, к тому же, содержащее ошибку.

Консоль

Выполнить

SELECT name
FROM (SELECT O.ship AS name, numGuns, displacement
 FROM Outcomes O INNER JOIN 
 Classes C ON O.ship = C.class AND 
 O.ship NOT IN (SELECT name 
 FROM Ships
 ) 
 UNION
 SELECT S.name AS name, numGuns, displacement 
 FROM Ships S INNER JOIN 
 Classes C ON S.class = C.class 
 ) OS INNER JOIN 
 (SELECT MAX(numGuns) AS MaxNumGuns, displacement
 FROM Outcomes O INNER JOIN 
 Classes C ON O.ship = C.class AND 
 O.ship NOT IN (SELECT name 
 FROM Ships
 ) 
 GROUP BY displacement
 UNION
 SELECT MAX(numGuns) AS MaxNumGuns, displacement
 FROM Ships S INNER JOIN 
 Classes C ON S.class = C.class
 GROUP BY displacement
 ) GD ON OS.numGuns = GD.MaxNumGuns AND 
 OS.displacement = GD.displacement;

SELECT name FROM (SELECT O.ship AS name, numGuns, displacement FROM Outcomes O INNER JOIN Classes C ON O.ship = C.class AND O.ship NOT IN (SELECT name FROM Ships ) UNION SELECT S.name AS name, numGuns, displacement FROM Ships S INNER JOIN Classes C ON S.class = C.class ) OS INNER JOIN (SELECT MAX(numGuns) AS MaxNumGuns, displacement FROM Outcomes O INNER JOIN Classes C ON O.ship = C.class AND O.ship NOT IN (SELECT name FROM Ships ) GROUP BY displacement UNION SELECT MAX(numGuns) AS MaxNumGuns, displacement FROM Ships S INNER JOIN Classes C ON S.class = C.class GROUP BY displacement ) GD ON OS.numGuns = GD.MaxNumGuns AND OS.displacement = GD.displacement;

    Схема

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

O.ship NOT IN (SELECT name 
 FROM Ships
 )

Почему неправильная? Да потому, что она все равно оставляет дубликаты, учитывая головной корабль столько раз, сколько раз он участвовал в сражениях. Ну, а излишней она является потому, что предложение UNION все равно устранит дубликаты. Это в данном случае оказалось совсем нелишним, в результате чего подзапрос, хотя и не оптимальный, дает то, что и предполагалось по алгоритму.

Второй подзапрос в соединении определяет максимальное число орудий для каждого значения водоизмещения имеющихся кораблей, при этом опять, как и ранее, эти значения определяются отдельно для кораблей из Ships и головных кораблей из Outcomes с последующим объединением.

Соединение выполняется по совпадению числа орудий и водоизмещения в строках этих подзапросов.

Логика построения решения вполне верная; не верна реализация. Чтобы доказать это, обычно прибегают к контрпримеру. Другими словами, приведем пример данных, на котором этот запрос даст неверное решение задачи. Итак, пусть только в таблице Ships есть корабли водоизмещением 40 000 тонн с максимальным числом орудий 16, и только в таблице Outcomes имеется головной корабль водоизмещения 40 000 тонн и числом орудий 17. Тогда второй подзапрос из соединения даст нам две строки:

ПиР