Упражнение 46

Все нужные нам корабли, принимавшие участие в сражении при Гвадалканале, находятся в таблице Outcomes, а требуемые характеристики — в таблице Classes. Поэтому первое, что приходит в голову, — это соединить эти таблицы для получения нужного результата:

Решение 3.1.1

SELECT Outcomes.ship, Classes.displacement, Classes.numGuns
FROM Classes 
    RIGHT JOIN Outcomes ON Classes.class = Outcomes.ship
WHERE Outcomes.battle = 'Guadalcanal';

Внешнее соединение здесь используется законно, так как поскольку в задании сказано о кораблях, участвовавших в сражении, то выводить нужно все такие корабли вне зависимости от того, совпадает его имя с именем класса или нет. Заметим, что внутреннее соединение вернет пустой набор записей, так как в основной базе данных не оказалось головных кораблей, участвовавших в этом сражении. А так мы имеем:

Правильным же ответом является:

Откуда же берется эта информация? Информация о классе корабля содержится в таблице Ships, то есть нужна еще одна таблица. Итак, если корабль из Outcomes имеется в Ships (Outcomes.ship = Ships.name), то нам известен его класс, и, следовательно, вся необходимая информация о нем может быть взята из таблицы Classes (Ships.class = Classes.class). Рассмотрим решение, которое выполняет нужные соединения:

Решение 3.1.2

SELECT o.ship, c.displacement, c.numGuns
FROM Outcomes o 
    LEFT JOIN Ships s ON o.ship = s.name 
    LEFT JOIN Classes c ON s.class=c.class
WHERE o.battle = 'Guadalcanal';

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

Корабль NULL NULL

Ошибка заключается в пресловутом «Бисмарке». Не именно в нем, а в той ситуации, когда в Outcomes имеется головной корабль, которого нет в Ships. Предположим, что «Бисмарк» участвовал в сражении при Гвадалканале. Рассматриваемый нами запрос вернет такую строку:

Bismarck NULL NULL

так как этого корабля нет в Ships. Однако его характеристики нам известны, поскольку известен класс корабля (головной корабль!). Правильной строкой будет:

Bismarck 8 42000

Строку же

Корабль NULL NULL

мы получаем только в том случае, если в битве принимал участие неголовной корабль, отсутствующий в таблице Ships. Подобная ситуация могла бы еще возникать и при неизвестном классе корабля в таблице Ships, однако, она исключается ограничением NOT NULL на столбце class в этой таблице.

В заключение приведу еще одно решение, содержащее ту же ошибку, но не использующее внешние соединения:

Решение 3.1.3

SELECT a.ship, b.displacement, b.numguns
FROM Outcomes a, Ships c, Classes b
WHERE a.battle='Guadalcanal' 
    AND a.ship = c.name 
    AND c.class = b.class
UNION
SELECT a.ship, NULL AS displacement, NULL AS numguns
FROM Outcomes a
WHERE a.battle = 'Guadalcanal' 
    AND a.ship NOT IN (SELECT name
                        FROM Ships
                        );

Заметим, что первое рассмотренное нами решение дало бы правильный результат для такого головного корабля. Поэтому чтобы решить эту задачу, нужно второе решение дополнить первым. Как не следует «дополнять», можно посмотреть в ПиР.

Решить задачу на SQL-EX.RU