Книги и статьи по SQL Rambler's Top100 Switch language to: English 24 июня 2019 г. 16:27:37


www.sql-ex.ru
Skip Navigation Links  

 

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

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

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

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

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

Ранее на сайте проходил заведомо неправильное решение:

SELECT battle FROM Classes c
   LEFT JOIN Ships s ON c.class = s.class 
      INNER JOIN Outcomes o ON o.ship = s.name OR c.class = o.ship
   GROUP BY battle, country HAVING COUNT(ship) > 3

Обратите внимание на HAVING COUNT(ship) > 3. Использование правильного предиката с условием >=3 делало запрос неверным, каким он и является. Подгонка решения позволила обнаружить дыру в проверке, которая и была устранена.

Итак, запрос соединяет классы с кораблями из таблицы Ships, чтобы определить страну корабля. Левое соединение (LEFT JOIN) используется для того, чтобы не потерять класс, если кораблей этого класса нет в таблице Ships. Такой (и не только) класс понадобится для того, чтобы учесть головные корабли из таблицы Outcomes, что и делается в следующем (внутреннем) соединении. Предикат этого соединения

(1)
ON o.ship = s.name OR c.class = o.ship

сформирует строку, в столбце ship которой будет находиться имя корабля, принимавшего участие в сражениях, если его имя совпадает с именем корабля известного класса в таблице Ships ИЛИ если его имя совпадает с именем класса (головной корабль). Если корабль не принимал участия в сражении, то в столбце ship будет NULL. Затем выполняется группировка по паре атрибутов {battle, country} с предложением HAVING COUNT(ship) >= 3, что позволяет отобрать только те страны, которые участвовали в битве более чем двумя кораблями. Заметим, что функция COUNT корректно обработает NULL-значения в столбце ship.

О разнице в использовании COUNT(*) и COUNT(<имя столбца>) можно почитать в справке на сайте.

В этом "ИЛИ" предиката (1) и заключается основная ошибка этого запроса. Если один и тот же головной корабль имеется в таблице Outcomes и в таблице Ships, то он будет учтен дважды для одной и той же битвы. Это можно увидеть из запроса, который любезно предложил автор неправильного решения (BSV1969):

SELECT battle, country, ship, COUNT(*) qty FROM Classes c
   LEFT JOIN Ships s ON c.class = s.class
      INNER JOIN Outcomes o ON o.ship = s.name OR c.class = o.ship
   GROUP BY battle, country, ship

Я привожу здесь только одну неправильную строку результата:

Battle country ship qty
Surigao Strait USA Tennessee 2

Явная ошибка, т.к. один и тот же корабль не может дважды упоминаться для одной и той же битвы (Surigao Strait), что запрещено первичным ключом на таблице Outcomes.

Отметим, что рассматриваемый запрос содержит еще одну ошибку, описанную в FAQ. Поскольку группировка выполняется по паре атрибутов {battle, country}, то битва будет выводиться неоднократно, если в ней принимало участие по 3 и более корабля от каждой страны.

Остается один вопрос. Почему же при трех отмеченных ошибках (>3 вместо >=3, ошибочное соединение и возможное появление дубликатов) запрос был принят системой?

Попробуем разобраться. В основной базе не было ни одной битвы, для которой бы выполнялось условие задачи. В правильном решении выводился пустой набор записей. Поэтому ошибочное увеличение числа кораблей не работало с правильным критерием (>=3), т.к. запрос выдавал битву Surigao Strait, хотя в ней реально принимало участие 2 корабля из USA. А вот условие >3 опять давало пустой набор.

В проверочной базе для блокировки решения с неисключенными дубликатами для одной битвы было два набора по 3 корабля разных стран. При этом в одном наборе головной корабль присутствовал в обеих таблицах (Outcomes и Ships). Для этого набора рассматриваемым запросом ошибочно считалось 4 корабля, а для второго правильно - 3. Поэтому условие в предикате HAVING - > 3 и давало только одну битву, разрешая самым неожиданным образом проблему с дубликатами.

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

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

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

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


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

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