Книги и статьи по SQL Rambler's Top100 Switch language to: English 29 октября 2020 г. 5:15:52


www.sql-ex.ru
Skip Navigation Links  

 

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

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

Характерные ошибки при решении упражнений. Задача 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

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

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


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

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