Упражнение 38

Таким образом, нужно найти страны, которые имели корабли типа bc и bb. Слова «владевшие когда-либо» должно, по мнению автора, задействовать следующую логическую цепочку рассуждений:

• В текущем состоянии БД может не быть корабля какого-либо класса, хотя страна могла их иметь.
• Тогда откуда мы можем узнать, что такие корабли были? Только по имеющимся в БД классам, поскольку только в таблице Classes имеется информация о типе и стране.
• Если есть класс, скажем, типа bc (крейсер), то были и корабли этого класса, даже если их нет в таблицах Ships и Outcomes, поскольку информация о нереализованном проекте вряд ли станет доступной.

Вывод: для решения этой задачи нужно рассматривать только таблицу Classes. В результате получаем достаточно простую задачу.

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

SELECT DISTINCT c1.country
FROM Classes c1 
    INNER JOIN Classes c2 ON c1.country = c2.country 
    INNER JOIN Ships s1 ON c1.class = s1.class 
    INNER JOIN Ships s2 ON c2.class = s2.class
WHERE c1.type = 'bb' 
    AND c2.type = 'bc'
UNION
SELECT DISTINCT c1.country
FROM Classes c1 
    INNER JOIN Classes c2 ON c1.country = c2.country 
    INNER JOIN Ships s1 ON c1.class = s1.class 
    INNER JOIN Outcomes s2 ON c2.class = s2.ship
WHERE c1.type = 'bb' 
    AND c2.type = 'bc' 
    OR c2.type = 'bb' 
    AND c1.type = 'bc';

Какой формулировке соответствует это решение? Найти страны, у которых в БД имеются корабли обоих типов? Если ответ «да», то это решение все равно не является правильным.

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

Но есть же еще один вариант, когда имеются только головные корабли в Outcomes, причем обоих типов. Добавьте, например, в свою базу данных следующие строки:

INSERT INTO Classes
    VALUES('c_bb', 'bb' , 'AAA' ,10 ,15 , 35000);
INSERT INTO Classes
    VALUES('c_bc', 'bc', 'AAA', 6, 15, 45000);
INSERT INTO Outcomes
    VALUES('c_bb', 'Guadalcanal', 'ok');
INSERT INTO Outcomes
    VALUES('c_bc', 'Guadalcanal', 'ok');

Страна ААА имеет корабли обоих типов. Однако вышеприведенный запрос не выведет эту страну, как это и следовало ожидать.

Замечу также, что предложение DISTINCT в обоих запросах совершенно излишне, так как UNION устранит возможные дубликаты. С точки зрения логики это замечание не является существенным. Однако с точки зрения оптимизации это достаточно важный момент. Сервер тратит значительные ресурсы на удаление дубликатов, поэтому не стоит это делать несколько раз. Сравните планы выполнения запросов с DISTINCT и без него.

А вот пример половинчатого решения, принимаемого системой на момент его написания:

SELECT DISTINCT country
FROM Classes 
    RIGHT JOIN (SELECT DISTINCT COALESCE(ship, name) AS name, class
                FROM Outcomes 
                    FULL OUTER JOIN Ships ON ship = name
                )AS z ON z.name = Classes.class 
                    OR z.class = Classes.class
WHERE type = 'bb' 
    AND country IN (SELECT country
                    FROM classes
                    WHERE type = 'bc'
                    );

Здесь берутся все корабли из обеих таблиц — Ships и Outcomes. Далее результат соединяется с таблицей Classes, определяется класс кораблей, и отбираются только те из них, которые имеют тип bb (боевые корабли). Наконец, проверяется, что страна найденных кораблей имеет также классы bc. Решение оказалось правильным только потому, что страны, имеющие классы обоих типов, имеют в текущем состоянии БД корабли типа ‘bb’.

Заблокировать подобные решения очень просто: достаточно добавить в таблицу Classes два класса (типа ‘bc’ и ‘bb’) для страны, которая вообще не имеет кораблей в БД. Однако лучше уточнить формулировку, скажем, так:

Изменение формулировки уже выполнено на сайте. Тем не менее, надеюсь, что проведенный анализ решений оказался полезным для вас.

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