loading..
Русский    English
18:50
листать

Упражнение 32 стр. 1

Одной из характеристик корабля является половина куба калибра его главных орудий (mw). С точностью до 2 десятичных знаков определите среднее значение mw для кораблей каждой страны, у которой есть корабли в базе данных.

Калибр орудий, как и страна, является атрибутом таблицы Classes. Таким образом, здесь нужно найти все корабли в базе данных, для которых известен класс. Замечание об учете кораблей из таблицы Outcomes означает, как обычно, что класс головного корабля может быть известен, даже если его нет в таблице Ships.

Затем следует добавить вычисляемый столбец для определения веса снаряда и посчитать среднее значение этого веса, сгруппировав корабли по странам.

Рассмотрим следующий запрос, отбраковываемый системой.

Решение 3.14.1

Консоль
Выполнить
  1. SELECT DISTINCT Classes.country,
  2. (SELECT AVG( pen.p )
  3. FROM (SELECT (c1.bore*c1.bore*c1.bore)/2 AS p
  4. FROM Classes AS c1, Ships AS s1
  5. WHERE c1.class = s1.class AND
  6. c1.country = Classes.country AND
  7. c1.bore IS NOT NULL
  8. UNION ALL
  9. SELECT (c2.bore*c2.bore*c2.bore)/2
  10. FROM Classes AS c2, Outcomes
  11. WHERE c2.country = Classes.country AND
  12. c2.class = Outcomes.ship AND
  13. c2.bore IS NOT NULL AND
  14. Outcomes.ship NOT IN (SELECT ss.name
  15. FROM Ships AS ss
  16. )
  17. ) AS pen
  18. WHERE pen.p IS NOT NULL
  19. ) AS weight
  20. FROM Classes
  21. WHERE Classes.country IS NOT NULL;

Запрос интересен тем, что в нем не используется группировка, а среднее значение по стране определяется с помощью коррелирующего подзапроса, выполняемого для каждой страны из таблицы Classes. Кроме того, он выполнен в полном соответствии со стандартом. Можно сразу сделать замечание относительно эффективности выполнения этого запроса, так как если у страны несколько классов кораблей (что не является для нас большой неожиданностью), то фактически подзапрос будет выполняться для каждого класса, что явно излишне. Появляющиеся при этом дубликаты записей устраняются при помощи DISTINCT, что тоже скажется на производительности. Но нас интересует другой вопрос, а именно, почему этот запрос неверен. Чтобы это понять, давайте рассмотрим его по частям.

Начнем с подзапроса, в котором объединяются (UNION ALL) два запроса:

(1)

  1. SELECT (c1.bore*c1.bore*c1.bore)/2 AS p
  2. FROM Classes AS c1, Ships AS s1
  3. WHERE c1.class = s1.class AND
  4. c1.country = Classes.country AND
  5. c1.bore IS NOT NULL

и

(2)

  1. SELECT (c2.bore*c2.bore*c2.bore)/2
  2. FROM Classes AS c2, Outcomes
  3. WHERE c2.country = Classes.country AND
  4. c2.class=Outcomes.ship AND
  5. c2.bore IS NOT NULL AND
  6. Outcomes.ship NOT IN (SELECT ss.name
  7. FROM Ships AS ss
  8. )

В запросе (1) вычисляется вес снарядов кораблей из таблицы Ships для страны, передаваемой из внешнего запроса (коррелирующий подзапрос). Условие c1.bore IS NOT NULL, на наш взгляд, совершенно излишне, так как даже если и есть классы с неизвестным калибром, такие значения автоматически будут исключены при вычислении среднего значения с помощью функции AVG. Но это не ошибка в решении задачи.

В запросе (2) аналогичные вычисления делаются для головных кораблей из Outcomes, которые отсутствуют в Ships.

Далее объединение с помощью UNION ALL позволяет сохранить все дубликаты веса, что необходимо, так как, по крайней мере, корабли одного класса имеют снаряды одного калибра (веса).

Во внешнем запросе вычисляется среднее значение по стране, отфильтровывая случай, когда калибр неизвестен для всех кораблей некоторой страны (WHERE pen.p IS NOT NULL). Это объясняется тем, что если AVG применяется к пустому набору записей, то результат вычисления будет NULL.

Наконец, в основном запросе выводим требуемые по условиям задачи данные.

Вы уже нашли ошибку? Если нет, то нам помогут знания предметной области. Что за таблица Outcomes? Здесь хранятся данные об участии кораблей в сражениях. А корабль, если он не был потоплен, может принимать участие в нескольких сражениях. Таким образом, мы потенциально учитываем головной корабль несколько раз. Если же рассуждать формально, то первичный ключ на этой таблице {корабль, сражение} допускает появление одного и того же корабля неоднократно.

При этом мы не можем вместо UNION ALL использовать UNION по описанным выше причинам, но, тем не менее, исправить теперь этот запрос вам будет несложно.

Разбирая ошибки наших посетителей, автор обычно указывает те варианты данных, на которых рассматриваемые запросы возвращают неверные данные. Советуем вам наполнять свою базу аналогичными данными, тогда тестирование ваших запросов также и на других задачах будет более эффективным.


Bookmark and Share
Страницы: 1 2 3 4
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INNER JOIN insert INTERSECT IS NOT NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker MAX MIN MONTH Больше тэгов
Учебник обновлялся
несколько дней назад
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100