На главную страницу
Характерные ошибки при решении упражнений.
Задача 54
Моисеенко С.И.
С точностью до 2-х десятичных знаков определите среднее число орудий всех
линейных кораблей (учесть корабли из таблицы Outcomes).
При решении упражнений на сайте регулярно допускается одна и та же ошибка. Вот
запрос, который ее содержит:
SELECT CAST(AVG(numGuns*1.0) AS NUMERIC(10,2)) FROM
(
SELECT numguns
FROM [Classes] c JOIN Ships s ON c.class=s.class
WHERE type='bb'
UNION ALL
SELECT numguns
FROM [Classes] c JOIN Outcomes o ON c.class=o.ship
WHERE type='bb' and o.ship NOT IN(SELECT name FROM
Ships)
) t
В подзапросе объединяются (UNION ALL) два запроса. Первый определяет число
орудий для кораблей в таблице Ships, принадлежащих классам линейных кораблей
(тип 'bb'). Второй учитывает головные корабли соответствующих классов при
условии, что их нет в таблице Ships.
Тем самым, сделана попытка учесть каждый корабль в БД только один раз. Поскольку
для объединения используется UNION ALL, то дубликаты устраняться не будут. Это
важно, т.к. многие корабли будут иметь одинаковое число орудий, а в предложении
SELECT подзапроса выводится только этот столбец.
И все же ошибку дает именно UNION ALL. Поступим формально, т.е. не будем
домысливать предметную область, а обратимся к схеме. В таблице Ships первичным
ключом является имя корабля, поэтому первый запрос в объединении даст нам по
одной строке на каждый корабль известного класса. В таблице же Outcomes ключом
является пара {ship, battle}, т.е. уникальность обеспечивается для комбинации
имени корабля и сражения, в котором он принимал участие. Отсюда следует, что
один и тот же корабль может несколько раз упоминаться в таблице Outcomes, если
он принимал участие в нескольких сражениях.
В результате второй запрос в объединении даст дубликаты кораблей, если головной
корабль участвовал в нескольких сражениях. Это и делает ошибочным данный
запрос.
С другой стороны, и UNION вместо UNION ALL мы написать не можем по указанной
выше причине.
Вывод. Недостаточно выполнять объединение по одному столбцу numguns.
Приведенные здесь примеры можно выполнить непосредственно на сайте, установив
флажок "Без проверки" на странице с упражнениями
на SELECT.
Перейти к
решению задачи #54
На главную страницу