Упражнение 151

Несмотря на коэффициент сложности 2, решение этой задачи вызывало, по-видимому, наибольшие затруднения. Причем связано это не со сложностью в построении запроса, а с логикой решения задачи.

Эта задача заменила более простую задачу, которая звучала так: «Найдите названия всех кораблей из базы данных, спущенных на воду до 1918 г». Казалось бы, какая разница? Разве следующее решение не является правильным?

Решение 3.5.1

SELECT name AS shipName
FROM Ships
WHERE launched < 1941;

Нет. Как справедливо было замечено посетителями сайта, здесь никак не учитываются даты сражений. Действительно, если год спуска на воду корабля неизвестен, и при этом он участвовал в сражении, которое произошло до 1941 года, то такой корабль следует включать в результат. В первоначальной формулировке этой задачи, где фигурировал 1918 год, об этом можно было не заботиться, так как формально база данных содержит информацию о сражениях второй мировой войны, которая началась в 1939 году.

[!IMPORTANT](Следует заметить, что во многих задачах на сайте sql-ex.ru никак не учитывается ограничение на участие кораблей во Второй мировой войне. Т.е. в проверочной базе данных могут быть сражения, которые состоялись как задолго до этой войны, так и в далеком будущем. Это позволяет нам ограничиться небольшим числом учебных баз для большого числа разнообразных задач, в частности, связанных с обработкой даты/времени. Поэтому всегда следует ориентироваться на схему базы данных и ее ограничения.)

Решение 3.5.2

SELECT name
FROM Ships
WHERE launched < 1941
UNION
SELECT ship
FROM Outcomes, Battles
WHERE name = battle 
      AND DATEPART(YEAR, date) < 1941
UNION
SELECT ship
FROM Outcomes
WHERE ship IN (SELECT class
               FROM Ships
               WHERE launched < 1941
               );

Решение 3.5.2 учитывает:

1. Корабли из таблицы Ships c известным годом спуска на воду до 1941 года.
2. Корабли, которые принимали участие в сражениях до 1941 года (естественно, такие корабли должны были быть спущены на воду до сражения, в котором они принимали участие).
3. Корабли из таблицы Outcomes, имена которых совпадает с именем класса какого-нибудь корабля из Ships, спущенного на воду до 1941 года.

Следует заметить, что возможные дубликаты устраняются использованием объединения посредством UNION.

Последний вариант учитывает и те случаи, когда головной корабль принимал участие в сражениях только после 1941 года, так как более ранние сражения учтены предыдущим запросом. Осталось выяснить, зачем это нужно. Ответ на этот вопрос следует искать в «висящих» головных кораблях. Итак, корабль из Outcomes, имя которого совпадает с именем одного из классов (головной корабль), отсутствует в таблице Ships или присутствует, но с неизвестным годом спуска на воду. Пусть в таблице Ships есть корабль того же класса с известным годом спуска на воду. Если этот год окажется меньше 1941, то головной корабль следует включать в результирующий набор наряду с упомянутым кораблем. Это следует из того факта, что головной корабль — это первый корабль в своем классе и, следовательно, должен был быть спущен на воду не позднее любого другого корабля своего класса.

Во втором из объединяемых запросов решения 3.5.2 используется специфическая для SQL Server функция DATEPART. Она необходима, так как из даты сражения (поле date имеет темпоральный тип данных — datetime) нужно извлечь год сражения; в противном случае предикат

date < 1941

будет давать сравнение с датой 1905 года, в результате преобразования целого числа к типу дата-время (как число дней прошедших от начала 1900 года, что является точкой отсчета дат для настроек по умолчанию в SQL Server). В этом легко убедиться, если выполнить запрос:

SELECT CAST(1941 AS DATETIME);

1905-04-26 00:00:00.000

Чтобы оставаться в рамках стандарта, можно было бы использовать следующий предикат:

date < '1941'

Тогда неявное преобразование типа дало бы нужный результат. Опять проверим:

SELECT CAST('1941' AS DATETIME);

1941-01-01 00:00:00.000

Естественно, правильным будет и предикат, содержащий полную дату начала 1941 года (1 января):

date < '19410101'

Однако вернемся к рассматриваемому решению. Оно неверно. Как было сказано, здесь не учитывается ситуация, когда головной корабль присутствует только в Ships, и год его спуска на воду неизвестен. Учесть эту ситуацию можно, добавив к объединению еще один запрос.

Решение 3.5.3

SELECT name   
FROM Ships  
WHERE launched < 1941   
UNION   
SELECT ship  
FROM Outcomes, Battles   
WHERE name = battle 
     AND DATEPART(YEAR, date) < 1941  
UNION   
SELECT ship  
FROM Outcomes  
WHERE ship IN (SELECT class  
               FROM Ships  
               WHERE launched < 1941  
               )  
UNION  
SELECT name  
FROM Ships  
WHERE name IN (SELECT class  
               FROM Ships  
               WHERE launched < 1941  
               );

Сколько интересной информации можно извлечь из этой задачи, всего лишь поменяв год!

Однако и это еще не все. Предлагаем самостоятельно найти дополнительные корабли, отвечающие условиям задачи. Проверить себя вы сможете, заглянув в ПиР.

ПиР

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