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

Подсказки и решения

Беда с этими головными кораблями! Вот как иногда неправильно решается вопрос о головных кораблях, спущенных на воду ранее 1941 года:

Консоль

Выполнить

SELECT class 
FROM Classes 
WHERE EXISTS (SELECT 1 
 FROM Ships 
 WHERE launched < 1941 AND 
 Ships.class = Classes.class
 );

SELECT class FROM Classes WHERE EXISTS (SELECT 1 FROM Ships WHERE launched < 1941 AND Ships.class = Classes.class );

    Схема

То есть здесь класс отождествляется с наличием головного корабля в БД, а именно, разыскивается класс, который имеет в БД корабль, спущенный на воду ранее 1941 года. Однако из описания предметной области следует, что не всегда есть корабль, имя которого совпадает с именем класса. Поэтому искать неучтенные в Ships головные корабли следует исключительно в таблице Outcomes.

Наконец, о неучтенном варианте в решении 5.3.3. Итак, возможна следующая ситуация. Имеется головной корабль с неизвестным годом спуска на воду. Более того, он может участвовать только в сражениях после 1941 года. Пусть для всех других корабли того же класса год спуска на воду тоже неизвестен (это допускается схемой данных). Однако, если хотя бы один из этих кораблей участвовал в сражении до 1941 года, то нам следует такой корабль включить в результирующий набор вместе с головным, так как головной корабль (если он есть!) должен быть спущен на воду ранее любого другого корабля аналогичного класса.

Вот решение, которое, казалось бы, учитывает все оговоренные моменты:

Консоль

Выполнить

-- Корабли, спущенные на воду до 1941 года
SELECT name 
FROM Ships 
WHERE launched < 1941
 
UNION 
-- Корабли, принимавшие участие в сражениях до 1941 года
SELECT ship 
FROM Outcomes JOIN 
 Battles ON Battles.name = Outcomes.battle 
WHERE date < '19410101'
 
UNION 
-- Головные корабли из Outcomes, в классе которых есть другие корабли, 
-- спущенные на воду до 1941 года
SELECT ship 
FROM Outcomes 
WHERE ship IN (SELECT class 
 FROM Ships
 WHERE launched < 1941
 )
 
UNION 
-- Головные корабли из Outcomes при условии, что хотя бы один из кораблей 
-- того же класса, участвовал в сражении до 1941 года
SELECT ship 
FROM Outcomes 
WHERE Ship IN (SELECT class 
 FROM Ships JOIN
 Outcomes ON Ships.name = Outcomes.ship JOIN 
 Battles ON Battles.name = Outcomes.battle 
 WHERE date < '19410101'
 );

-- Корабли, спущенные на воду до 1941 года SELECT name FROM Ships WHERE launched < 1941 UNION -- Корабли, принимавшие участие в сражениях до 1941 года SELECT ship FROM Outcomes JOIN Battles ON Battles.name = Outcomes.battle WHERE date < '19410101' UNION -- Головные корабли из Outcomes, в классе которых есть другие корабли, -- спущенные на воду до 1941 года SELECT ship FROM Outcomes WHERE ship IN (SELECT class FROM Ships WHERE launched < 1941 ) UNION -- Головные корабли из Outcomes при условии, что хотя бы один из кораблей -- того же класса, участвовал в сражении до 1941 года SELECT ship FROM Outcomes WHERE Ship IN (SELECT class FROM Ships JOIN Outcomes ON Ships.name = Outcomes.ship JOIN Battles ON Battles.name = Outcomes.battle WHERE date < '19410101' );

    Схема

Однако система все равно сообщает об ошибке…

Как уже было отмечено в пункте 3.5, головные корабли с неизвестным годом спуска на воду могут находиться не только в таблице Outcomes, но и в таблице Ships. Более того, такие корабли не будут учтены рассматриваемым запросом, если их нет в таблице Outcomes, то есть они либо не участвовали в сражениях, либо информация об их участии неизвестна.

Так что здесь чистая логика, и никаких подвохов.

Вернуться к обсуждению упражнения 151