Книги и статьи по SQL Rambler's Top100 Switch language to: English 10 декабря 2019 г. 16:47:57


www.sql-ex.ru
Skip Navigation Links  

 

Print  Версия для печати

На главную страницу

Характерные ошибки при решении упражнений. Задача 38

Моисеенко С.И.

Найдите страны, имевшие когда-либо классы обычных боевых кораблей ('bb') и имевшие когда-либо классы крейсеров ('bc').

Ранее формулировка задания выглядела так:

Найдите страны, владевшие когда-либо как обычными кораблями, так и крейсерами.

Т.е. нужно найти страны, которые имели корабли типа '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') для страны, которая вообще не имеет кораблей в БД. Следовательно,  формулировку необходимо уточнить:

Найдите страны, имеющие классы как обычных боевых кораблей ('bb'), так и крейсеров ('bc').

Такой вид она имеет сейчас.

Приведенные здесь примеры можно выполнить непосредственно на сайте, установив флажок "Без проверки" на странице с упражнениями на SELECT.

Перейти к решению задачи #38

На главную страницу

Print  Версия для печати


Использование любых материалов данного сайта возможно только
при условии обязательного размещения прямой ссылки на сайт
http://www.sqlbooks.ru
на каждой странице, где размещены используемые материалы.

 Начало   Статьи    Книги 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.