лекция. Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас

• Название: Учебник по sql если вы хотите узнать, что такое sql этот сайт для вас
• Анкор: лекция
• Дата: 21.12.2022
• Размер: 7.88 Mb.
• Имя файла: Moiseenko_SQL.pdf
• Тип: Учебник; #857633
• страница: 7 из 47

ship
displacement
numGuns
California
NULL
NULL
Kirishima
NULL
NULL
South Dakota NULL
NULL
Washington
NULL
NULL

Правильным же ответом является:
Ship
displacement
numGuns
California
32000 12
Kirishima
32000 8
South Dakota 37000 12
Washington
37000 12
Откуда же берется эта информация? Информация о классе корабля содержится в таблице Ships, то есть нужна еще одна таблица. Итак, если корабль из Outcomes имеется в Ships (Outcomes.ship = Ships.name), то нам известен его класс, и, следовательно, вся необходимая информация о нем может быть взята из таблицы Classes (Ships.class = Classes.class). Рассмотрим решение, которое выполняет нужные соединения:
Решение 3.1.2
1.
SELECT
o.ship, c.displacement, c.numGuns
2.
FROM
Outcomes o
LEFT
JOIN
3. Ships s
ON
o.ship = s.name
LEFT
JOIN
4. Classes c
ON
s.class=c.class
5.
WHERE
o.battle =
'Guadalcanal'
;
На основной базе получаем правильный результат, однако, система не принимает решение. При этом левое соединение гарантирует появление корабля в выходном наборе даже в том случае, если его класс неизвестен
(корабля нет в Ships). В последнем случае будет получена строка типа:
1. Корабль
NULL
NULL

Ошибка заключается в пресловутом «Бисмарке». Не именно в нем, а в той ситуации, когда в Outcomes имеется головной корабль, которого нет в Ships.
Предположим, что «Бисмарк» участвовал в сражении при Гвадалканале.
Рассматриваемый нами запрос вернет такую строку:
1. Bismarck
NULL
NULL
так как этого корабля нет в Ships. Однако его характеристики нам известны, поскольку известен класс корабля (головной корабль!). Правильной строкой будет:
1. Bismarck
8 42000
Строку же
1. Корабль
NULL
NULL
мы получаем только в том случае, если в битве принимал участие неголовной корабль, отсутствующий в таблице Ships. Подобная ситуация могла бы еще возникать и при неизвестном классе корабля в таблице Ships, однако, она исключается ограничением NOT NULL на столбце class в этой таблице.
В заключение приведу еще одно решение, содержащее ту же ошибку, но не использующее внешние соединения:
Решение 3.1.3
1.
SELECT
a.ship, b.displacement, b.numguns
2.
FROM
Outcomes a, Ships c, Classes b
3.
WHERE
a.battle=
'Guadalcanal'
AND
4. a.ship = c.name
AND
5. c.class = b.class

6.
UNION
7.
SELECT
a.ship,
NULL
AS
displacement,
NULL
AS
numguns
8.
FROM
Outcomes a
9.
WHERE
a.battle =
'Guadalcanal'
AND
10. a.ship
NOT
IN
(
SELECT
name
11.
FROM
Ships
12.
)
;
Заметим, что первое рассмотренное нами решение дало бы правильный результат для такого головного корабля. Поэтому чтобы решить эту задачу, нужно второе решение дополнить первым. Как не следует «дополнять», можно посмотреть в главе 4.
Упражнение 51
Найдите названия кораблей, имеющих
наибольшее число орудий среди всех кораблей
такого же водоизмещения (учесть корабли
из таблицы Outcomes).
Решение 3.8.1. Не очень оптимальное решение и, к тому же, содержащее ошибку.
1.
SELECT
name
2.
FROM
(
SELECT
O.ship
AS
name, numGuns, displacement
3.
FROM
Outcomes O
INNER
JOIN
4. Classes C
ON
O.ship = C.class
AND
5. O.ship
NOT
IN
(
SELECT
name
6.
FROM
Ships
7.
)
8.
UNION
9.
SELECT
S.name
AS
name, numGuns, displacement
10.
FROM
Ships S
INNER
JOIN
11.
Classes C
ON
S.class = C.class
12.
)
OS
INNER
JOIN
13.
(
SELECT
MAX
(
numGuns
)
AS
MaxNumGuns, displacement
14.
FROM
Outcomes O
INNER
JOIN
15.
Classes C
ON
O.ship = C.class
AND

16.
O.ship
NOT
IN
(
SELECT
name
17.
FROM
Ships
18.
)
19.
GROUP
BY
displacement
20.
UNION
21.
SELECT
MAX
(
numGuns
)
AS
MaxNumGuns, displacement
22.
FROM
Ships S
INNER
JOIN
23.
Classes C
ON
S.class = C.class
24.
GROUP
BY
displacement
25.
)
GD
ON
OS.numGuns = GD.MaxNumGuns
AND
26.
OS.displacement = GD.displacement;
В предложении FROM данного решения соединяются два подзапроса. В первом из них определяется имя, число орудий и водоизмещение всех имеющихся в базе данных кораблей. Эти корабли собираются по двум таблицам — Ships и Outcomes (головные корабли). При этом выполняется неправильная и излишняя проверка на дубликаты:
1. O.ship
NOT
IN
(
SELECT
name
2.
FROM
Ships
3.
)
Почему неправильная? Да потому, что она все равно оставляет дубликаты, учитывая головной корабль столько раз, сколько раз он участвовал в сражениях. Ну, а излишней она является потому, что предложение UNION все равно устранит дубликаты. Это в данном случае оказалось совсем нелишним, в результате чего подзапрос, хотя и не оптимальный, дает то, что и предполагалось по алгоритму.
Второй подзапрос в соединении определяет максимальное число орудий для каждого значения водоизмещения имеющихся кораблей, при этом опять, как и ранее, эти значения определяются отдельно для кораблей из Ships и головных кораблей из Outcomes с последующим объединением.
Соединение выполняется по совпадению числа орудий и водоизмещения в строках этих подзапросов.
Логика построения решения вполне верная; не верна реализация. Чтобы доказать это, обычно прибегают к контрпримеру. Другими словами, приведем пример данных, на котором этот запрос даст неверное решение задачи. Итак,

пусть только в таблице Ships есть корабли водоизмещением 40 000 тонн с максимальным числом орудий 16, и только в таблице Outcomes имеется головной корабль водоизмещения 40 000 тонн и числом орудий 17. Тогда второй подзапрос из соединения даст нам две строки:
16 40000
17 40000 поскольку это не дубликаты, обе эти строки будут присутствовать в результирующем наборе. В результате соединения мы получим не только корабли с максимальным числом орудий для данного водоизмещения — 17, но и корабли, имеющие на вооружении 16 орудий. Узнаете ошибку? Она уже встречалась ранее: сначала нужно делать объединение, а потом группировку.
Упражнение 53
Определите среднее число орудий для
классов линейных кораблей. Получить
результат с точностью до двух десятичных
знаков.
Автор полагал, что в этой задаче лишь одна проблема — округление. Однако как-то поступило следующее решение:
1.
SELECT
SUM
(
sum_g
)
/
SUM
(
count_g
)
2.
FROM
(
SELECT
SUM
(
numGuns
)
AS
sum_g,
COUNT
(
*
)
AS
count_g
3.
FROM
Classes
INNER
JOIN
4. Ships
ON
Classes.class = Ships.class
5.
WHERE
type =
'bb'
6.
UNION
7.
SELECT
SUM
(
numGuns
)
AS
sum_g,
COUNT
(
*
)
AS
count_g
8.
FROM
Classes
INNER
JOIN
9. Outcomes
ON
Classes.class = Outcomes.ship
10.
WHERE
type=
'bb'

11.
)
AS
a;
Богатое для анализа ошибок решение. Начнем с округления. Число орудий
— целое число (по типу столбца, а не по смыслу!). Поэтому и сумма будет числом целым. При делении целых чисел в
SQL
Server всегда получается целое число. Причем результат достигается не округлением, а отбрасыванием дробной части. Выполните, например, следующий запрос
1.
SELECT
2
/
3
;
Результатом будет 0, что подтверждает сказанное. Поэтому, чтобы внести косметические исправления данного запроса, нужно выполнить преобразование хотя бы одного операнда к вещественному типу. Как сказано в пункте 5.9
, можно воспользоваться неявным преобразованием типа:
1.
SELECT
SUM
(
sum_g
)
*
1.0
/
SUM
(
count_g
)
то есть при умножении на вещественную единицу числитель становится вещественным числом.
Теперь, поскольку требуется определить среднее по классам, то, во-первых, не нужно учитывать корабли, а, во-вторых, не нужно учитывать корабли из таблицы Outcomes.
Однако чтобы проанализировать допущенные ошибки, давайте рассмотрим решение в трактовке автора этого запроса, то есть определим среднее значение по всем линейным кораблям из базы данных, а это не что иное, как задача 54.
Эта задача рассматривается в пункте 3.10
, но там решение содержит другую ошибку.
Итак, в подзапросе подсчитывается число орудий и количество отдельно по кораблям из таблицы Ships и головным кораблям из таблицы Outcomes. Затем в основном запросе суммируется число орудий и количество кораблей, полученных по каждой таблице, и делится одно на другое, чтобы получить среднее значение.

Рассмотрим пример. Пусть в Ships есть 2 корабля с 11 и 8 орудиями, а в
Outcomes — один корабль с 11 орудиями. Итого получаем 3 корабля и 30 орудий. Среднее 30/3 = 10. Правильно? Нет, то есть правильно, но не во всех случаях. Нам же нужно написать запрос, который будет верен на любых данных. Я вижу здесь несколько контрпримеров.
Первый контрпример. А если в Outcomes не будет головного корабля, отвечающего условиям задачи? Тогда второй подзапрос даст: кораблей — 0, число орудий — NULL. В результате вычисление среднего в рассматриваемом запросе даст
1.
(
19
+
NULL
)
/
(
2+0
)
=
NULL
вместо 19/2.
Второй контрпример. Пусть головной корабль класса bb есть как в таблице
Ships, так и в таблице Outcomes, то есть это один и тот же корабль. Тогда в результате мы должны получить не 30/3, что нам дает представленное решение, а 19/2.
Третий контрпример. А если в предыдущей ситуации по кораблям головной корабль дважды принимал участие в сражениях? Тогда мы получим вместо тех же 19/2 — (19 + 22)/(2+2) = 41/4.
Четвертый контрпример… Придумайте сами. Вот так и формируется проверочная база сайта.
Упражнение 54
С точностью до двух десятичных знаков
определите среднее число орудий всех
линейных кораблей (учесть корабли из
таблицы Outcomes).
Решение 3.10.1
1.
SELECT
ROUND
(
SUM
(
ng
)
/
SUM
(
cnt
)
,
2
)
res

2.
FROM
(
SELECT
SUM
(
numGuns
)
ng,
COUNT
(
*
)
cnt
3.
FROM
Classes c,Ships s
4.
WHERE
c.class = s.class
AND
5. c.type =
'bb'
6.
UNION
ALL
7.
SELECT
SUM
(
numGuns
)
ng,
COUNT
(
*
)
cnt
8.
FROM
Classes c, Outcomes o
9.
WHERE
c.class = o.ship
AND
10. c.type =
'BB'
AND
11.
NOT
EXISTS
(
SELECT
name
FROM
Ships s
12.
WHERE
s.name = o.ship
)
13.
)
x;
В этом решении сделана попытка вручную посчитать среднее как сумму значений, деленную на их количество. Однако специфика арифметических операций в
SQL
Server состоит в том, что результат всегда приводится к типу аргумента. Поскольку число орудий — целое число (тип INTEGER для столбца numGuns), то дробная часть числа, полученного при делении, будет попросту отбрасываться, что заведомо даст неправильный результат.
Внимание:
Использование функции AVG для вычисления среднего не меняет
ситуацию, так как приведение типа проводится по тем же
правилам. Это легко проверить, если выполнить запрос
1.
SELECT
AVG
(
3
/
2
)
; который даст 1, а не 2, если бы выполнялось округление.
Если вы будете выполнять аналогичные запросы на сайте, поставьте флажок
«Без проверки» на странице с упражнениями, чтобы система не выполняла бесполезного сравнения результата с эталонным решением соответствующего упражнения.
Для получения «точного» результата деления целых чисел нужно привести операнд (хотя бы один) к вещественному типу. Это можно сделать с помощью функции приведения типа
CAST
или простым умножением на вещественную единицу, как мы и поступим:

1.
SELECT
SUM
(
numGuns*
1.0
)
ng
Теперь поговорим об округлении, которое использует функцию
T-
SQL
ROUND. Опять обратимся к простому примеру (округление до двух цифр после десятичной точки):
1.
SELECT
ROUND
(
AVG
(
5.0
/
3
)
,
2
)
; который даст нам 1.670000 в качестве результата. То есть округление выполнено правильно, но сохранены незначащие нули, количество которых соответствует числу значащих цифр, используемых по умолчанию для представления вещественного числа. Это число, естественно, зависит от реализации, поэтому в данном случае мы говорим лишь об SQL Server. Здесь уместно заметить, что при сравнении результатов с «правильным» решением значения 1.67 и 1.670000 будут считаться разными. Поэтому нужно позаботиться еще и об удалении этих нулей. Отложим этот вопрос до анализа следующего решения, так как там эта проблема, как и проблема округления, решена верно. Там же мы рассмотрим и логическую ошибку, которую содержит решение 3.10.1.
Решение 3.10.2
1.
SELECT
CAST
(
AVG
(
numGuns*
1.0
)
AS
NUMERIC
(
10
,
2
))
2.
FROM
(
SELECT
numguns
3.
FROM
Classes c
JOIN
4. Ships s
ON
c.class = s.class
5.
WHERE
type =
'bb'
6.
UNION
ALL
7.
SELECT
numguns
8.
FROM
Classes
]
c
JOIN
9. Outcomes o
ON
c.class = o.ship
10.
WHERE
type=
'bb'
AND
11. o.ship
NOT
IN
(
SELECT
name
12.
FROM
Ships
13.
)
14.
)
t;

Обратите внимание на приведение типа к числу с фиксированной точкой, которое и выполняет требуемое округление результата.
В подзапросе объединяются (UNION ALL) два запроса. Первый определяет число орудий для кораблей в таблице Ships, принадлежащих классам линейных кораблей (тип bb). Второй учитывает головные корабли соответствующих классов при условии, что их нет в таблице Ships.
Таким образом, сделана попытка учесть каждый корабль в БД только один раз. Поскольку для объединения используется UNION ALL, то дубликаты устраняться не будут. Это совершенно справедливо, так как многие корабли будут иметь одинаковое число орудий, а в предложении SELECT подзапроса выводится только этот столбец.
И все же ошибка связана именно с использованием UNION ALL. Поступим формально, то есть не будем домысливать предметную область, а обратимся к схеме (рис. 3.1). В таблице Ships первичным ключом является имя корабля, поэтому первый запрос в объединении даст нам по одной строке на каждый корабль известного класса. В таблице же Outcomes ключом является пара
{ship, battle}, то есть уникальность обеспечивается для комбинации имени корабля и сражения, в котором он принимал участие. Отсюда следует, что один и тот же корабль может несколько раз упоминаться в таблице Outcomes, что соответствует участию данного корабля в нескольких сражениях.
В результате второй запрос в объединении даст дубликаты кораблей, если головной корабль участвовал в нескольких сражениях. Это и делает ошибочным представленное решение.
С другой стороны, и UNION вместо UNION ALL мы написать не можем по указанной выше причине.
Упражнение 55
Для каждого класса определите год, когда был
спущен на воду первый корабль этого класса. Если
год спуска на воду головного корабля неизвестен,
определите минимальный год спуска на воду
кораблей этого класса. Вывести: класс, год.
Видимо, ошибки вызваны наличием ловушек в предыдущих задачах. При решении задач со сложностью 1 делаются попытки учесть все и вся.

Сам по себе учет излишних фактов не делает решение неверным, разве что увеличивает стоимость выполнения запроса, но дело в том, что при этом допускались столь характерные ошибки, что автор решил рассмотреть несколько подобных решений.
Что же лишнего пытаются учесть при решении этой задачи? Это — головные корабли из таблицы
Outcomes. Таблица Outcomes вообще не нужна для решения этой задачи. Ведь нам нужно определить год, который является атрибутом таблицы Ships.
Поэтому даже если в таблице Outcomes есть головной корабль, отсутствующий в таблице Ships, то мы все равно не знаем года его спуска на воду. В случае же отсутствия в БД других кораблей этого класса наличие такого корабля все равно ничего не дает, так как результат должен выглядеть следующим образом:
Класс NULL
поскольку в задании сказано «для каждого класса». Тем самым утверждается, что для данного класса год спуска первого корабля неизвестен. Но такую строку в результирующем наборе мы можем получить и без таблицы Outcomes, выполнив внешнее соединение таблицы Classes с таблицей
Ships. Перейдем к анализу решений, в которых были допущены ошибки при учете кораблей из таблицы Outcomes.
Решение 3.11.1
1.
SELECT
C.class , launched
2.
FROM
Classes C ,
3.
(
SELECT
name , class , launched
4.
FROM
Ships
5.
UNION
6.
SELECT
ship , ship ,
NULL
7.
FROM
Outcomes O
8.
WHERE
NOT
EXISTS
(
SELECT
*

9.
FROM
Ships S
10.
WHERE
S.class = O.ship
11.
)
12.
UNION
13.
SELECT
ship , ship ,
MIN
(
launched
)
14.
FROM
Ships S ,
15.
Outcomes O
16.
WHERE
S.class = O.ship
17.
GROUP
BY
ship
18.
)
S
19.
WHERE
C.class = S.name;
Рассмотрим подзапрос S, в котором объединяются три запроса, результирующий набор каждого из которых содержит три столбца {имя корабля, класс, год спуска на воду}. В первом из них выбираются все корабли из таблицы Ships. Во втором выбираются корабли из Outcomes, имя которых не совпадает ни с одним классом кораблей из таблицы Ships. При этом в качестве года спуска на воду используется