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

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

NULL, что правильно, а имя корабля ассоциируется с именем класса (SELECT ship, ship, NULL).
Последнее не является здесь ошибкой, так как в последующем будут отбираться только головные корабли:
1.
WHERE
C.class = S.name
Наконец, в третьем запросе определяется минимальный год для классов кораблей, у которых головной корабль имеется в таблице Outcomes.
Принципиальная ошибка заключается в наличии ситуации, когда головной корабль из Outcomes присутствует также и в таблице Ships с неизвестным годом спуска на воду. Кроме того, имеются и другие корабли того же класса с известным годом спуска на воду. Тогда первый запрос даст строку с годом NULL, а третий — с минимальным годом по этому классу. В итоге получим две строки, которые не являются дубликатами и, следовательно, не будут исключены использованием UNION.
Наконец, условие отбора только по головным кораблям исключит из рассмотрения классы, вообще не имеющие головных кораблей.
Решение 3.11.2

1.
SELECT
DISTINCT
class,
MIN
(
launched
)
2.
FROM
Ships
GROUP
BY
Class
3.
UNION
4.
SELECT
DISTINCT
Ship
AS
class,
NULL
5.
FROM
Outcomes
6.
WHERE
ship
IN
(
SELECT
class
7.
FROM
Classes
8.
)
AND
9. ship
NOT
IN
(
SELECT
name
10.
FROM
Ships
11.
)
;
Первый запрос в объединении считает минимальный год спуска на воду по классам кораблей из таблицы Ships. Во втором запросе выбираются те головные корабли из таблицы Outcomes (предикат IN), которых нет в таблице
Ships (предикат NOT IN). Здесь, как и в решении 3.11.1, такой корабль (класс) учитывается с NULL в качестве года спуска на воду. Однако ошибка здесь уже другая. Суть ее заключается в ситуации, когда в Outcomes есть головной корабль, которого нет в таблице Ships (пусть это будет корабль «Бисмарк»), но в Ships есть другой корабль класса «Бисмарк» с известным годом спуска на воду. В результате мы опять получаем две строки на класс, с известным и неизвестным годом спуска на воду.
По поводу решения 3.11.2 следует отметить совершенно излишнее, и даже вредное с точки зрения производительности, задействование DISTINCT. В первом из объединяемых запросов группировка делает появление дубликатов невозможным. Во втором запросе, если и будут дубликаты (участие корабля в нескольких сражениях), они устраняются использованием при объединении предложения UNION.
Теперь рассмотрим решения, где не применяется таблица Outcomes, но, тем не менее, допускается логическая ошибка.
Решение 3.11.3 (с комментариями автора решения)
1.
/*
2.
Год спуска на воду головных кораблей
3.
*/
4.
SELECT
class, launched
AS
year

5.
FROM
Ships
6.
WHERE
name = class
7.
UNION
8.
/*
9.
Минимальный год спуска на воду кораблей по классам,
10.
у которых нет данных по головным кораблям в таблице
Ships
11.
*/
12.
SELECT
class,
MIN
(
launched
)
13.
FROM
Ships
14.
WHERE
class
NOT
IN
(
SELECT
class
15.
FROM
Ships
16.
WHERE
name = class
17.
)
18.
GROUP
BY
class
19.
UNION
20.
/*
21.
Выводим NULL в качестве года спуска на воду для
классов,
22.
кораблей которых нет в Ships
23.
*/
24.
SELECT
class,
NULL
25.
FROM
classes
26.
WHERE
class
NOT
IN
(
SELECT
class
27.
FROM
ships
28.
)
;
В этом решении в соответствии с условием задачи учтены все классы, включая те, которые не имеют кораблей в БД (последний запрос в объединении). По-видимому, допущенная здесь ошибка связана с попыткой отдельного учета головных кораблей (которые являются первыми кораблями в классе и, следовательно, имеют наименьший год спуска на воду) и классов, не имеющих головных кораблей в базе данных.
Представим ситуацию, когда год спуска головного корабля неизвестен
(NULL), но при этом он имеется в таблице Ships. Там же находится другой корабль аналогичного класса с известным годом спуска на воду. Тогда именно этот год должен по условию задачи фигурировать в выходном наборе.
Данное же решение даст (первый запрос в объединении) NULL, в то время как корабль с нужным годом будет проигнорирован во втором запросе объединения в силу следующей фильтрации

1.
WHERE
class
NOT
IN
(
SELECT
class
2.
FROM
Ships
3.
WHERE
name = class
4.
)
Аналогичная ошибка содержится и в нижеследующем варианте, который предлагается проанализировать самостоятельно.
Решение 3.11.4
1.
SELECT
class,
2.
(
SELECT
launched
3.
FROM
(
SELECT
launched
4.
FROM
Ships sh
5.
WHERE
cl.class = sh.name
6.
UNION
7.
SELECT
launched
8.
FROM
Ships sh
9.
WHERE
launched =
(
SELECT
MIN
(
launched
)
10.
FROM
ships sh2 11.
WHERE
class = cl.class
AND
12.
NOT
EXISTS
(
SELECT
launched
13.
FROM
ships sh
14.
WHERE
cl.class = sh.name
15.
)
16.
)
17.
)
tab
18.
)
year
19.
FROM
classes cl;
Упражнение 56
Для каждого класса определите число
кораблей этого класса, потопленных в
сражениях. Вывести: класс и число
потопленных кораблей.
Решение 3.12.1

1.
SELECT
aa.class,
SUM
(
aa.sunks
)
Sunks
2.
FROM
(
3.
-- 1
4.
SELECT
c.class,
COUNT
(
a.ship
)
sunks
5.
FROM
Outcomes a
INNER
JOIN
6. Ships b
ON
a.ship = b.name
INNER
JOIN
7. Classes c
ON
b.class = c.class
8.
WHERE
a.result =
'sunk'
9.
GROUP
BY
c.class
10.
UNION
11.
-- 2
12.
SELECT
c.class,
COUNT
(
a.ship
)
13.
FROM
Outcomes a
INNER
JOIN
14.
Classes c
ON
a.ship = c.class
15.
WHERE
a.result =
'sunk'
16.
GROUP
BY
c.class
17.
UNION
18.
-- 3
19.
SELECT
c.class,
0 20.
FROM
Classes c
21.
)
aa
22.
GROUP
BY
aa.class;
В подзапросе предложения FROM объединяются три таблицы:
Класс и число потопленных кораблей, которые есть в таблице Ships.
Класс и число потопленных головных кораблей класса. Здесь уже есть
«излишество», а именно: нет необходимости использовать группировку и соответственно функцию COUNT, так как у класса может быть только один головной корабль, да и потоплен корабль может быть только однажды.
Каждый класс с нулевым количеством потопленных кораблей. Это позволяет учесть те классы, которые не имеют потопленных кораблей и, следовательно, не попадают в предыдущие два набора записей.
Объединение с использованием UNION устраняет дубликаты, что, по мнению автора решения, позволяет корректно обработать ситуацию, когда потопленный головной корабль присутствует в таблице Ships. Наконец, выполняется группировка по классам с суммированием. При этом последний набор не дает вклада в окончательный результат, если в классе имеются потопленные корабли, что правильно.

Однако ошибка кроется в том, что объединяются двухатрибутные кортежи
{класс, число потопленных кораблей}. Поэтому если в некотором классе
(опять «Бисмарк») имеется два потопленных корабля, причем головной корабль отсутствует в Ships, то объединяться будут два одинаковых кортежа
Бисмарк 1
Тогда после устранения дубликатов мы получаем один потопленный корабль вместо двух.
Но это еще не все. Даже головной корабль мы можем посчитать дважды, если он присутствует в Ships. Это справедливо для случая, когда есть и другие корабли этого класса, потопленные в сражениях. Давайте опять возьмем для примера «Бисмарк», только теперь он присутствует также в таблице Ships.
Пусть есть и еще один потопленный корабль (естественно, не головной) этого класса. Тогда первый набор даст:
Бисмарк 2 а второй:
Бисмарк 1
В результате мы получим
Бисмарк 3 хотя на самом деле корабля всего два.

Вот еще одно решение задачи, в котором не используется объединение, но содержится другая ошибка:
Решение 3.12.2
1.
SELECT
classes.class,
COUNT
(
ship
)
sunked
2.
FROM
Class_Name_Class_1_A_Class_1'>Classes
FULL
JOIN
3. Ships
ON
classes.class = ships.class
LEFT
JOIN
4.
(
SELECT
ship
5.
FROM
Outcomes
6.
WHERE
result =
'sunk'
7.
)
s
ON
s.ship = ships.name
OR
8. s.ship = classes.class
9.
GROUP
BY
classes.class;
Первое (полное) соединение:
1. Classes
FULL
JOIN
2. Ships
ON
classes.class = ships.class будет содержать все возможные классы кораблей. Заметим, что здесь можно было ограничиться левым (LEFT) соединением, так как согласно связи между таблицами в Ships не может быть корабля, класс которого отсутствует в таблице Classes.
Далее выполняется левое соединение с потопленными кораблями из таблицы
Outcomes по следующему предикату (множество s содержит все потопленные корабли):
1.
ON
s.ship = ships.name
OR
s.ship = classes.class
То есть мы включаем в результирующий набор корабль, если имя его совпадает с именем потопленного корабля или если класс совпадает с именем

потопленного корабля. На рассмотренных выше примерах данных этот запрос будет работать правильно, в отличие от первого рассмотренного решения.
Действительно, если в классе «Бисмарк» имеется два потопленных корабля, один из которых является головным и отсутствует в Ships, то оба они будут учтены согласно рассмотренному выше предикату. Если же головной корабль присутствует в таблице Ships, то это ничего не меняет, так как предикат все равно будет выполнен.
В чем же здесь ошибка? Ошибка заключается как раз в предикате последнего соединения. Пусть в таблице Ships имеются корабли некоторого класса
(например, два корабля с именами А и В класса Class_1), которые не были потоплены. И пусть в Outcomes имеется потопленный головной корабль этого же класса. Тогда соединяться будут следующие два отношения (приводим здесь только важные для анализа атрибуты):
Class
Name
Class_1 A
Class_1 B и
Ship (отношение s) Class_1 по предикату s.ship = classes.class
В результате будет получено отношение, содержащее корабли, которые не были потоплены, но учитываются этим решением:
Class
Name
Ship
Class_1 A
Class_1
Class_1 B
Class_1

Можно сказать иначе, а именно, потопленный головной корабль учтен здесь столько раз, сколько кораблей этого класса имеется в таблице Ships (как потопленных, так и нет). Так или иначе, но COUNT(ship) = 2, что неверно, так как потоплен был всего один корабль.
Кстати, из сказанного становится очевидным, как исправить данное решение; причем сделать это очень просто. Можно просто добавить 8 символов.
Попробуйте
Решение 3.12.3
1.
SELECT
class,
SUM
(
CASE
2.
WHEN
result =
'sunk'
3.
THEN
1
ELSE
0 4.
END
)
5.
FROM
(
SELECT
c.class, sh.name, o.ship, o.result
6.
FROM
Classes c
LEFT
JOIN
7. Ships sh
ON
c.class = sh.class
LEFT
JOIN
8. Outcomes o
ON
ISNULL
(
sh.name, c.class
)
= o.ship
9.
)
t
10.
GROUP
BY
class;
Оставим без внимания подсчет количества потопленных кораблей. Ошибка не в этом, а в том, как формировался набор записей для этого подсчета.
Итак, левое соединение таблицы Classes с таблицей Ships по столбцу class позволяет нам учесть также классы, которые не имеют кораблей в таблице
Ships. Это правильно, так как нам следует выводить данный класс со значением 0 в качестве количества потопленных кораблей, если таковые отсутствуют.
Далее выполняется левое соединение с таблицей Outcomes, которая и содержит информацию о результатах сражений. Предикат соединения использует специфическую для
SQL
Server функцию ISNULL, которая возвращает первый аргумент, если он не является NULL-значением, и второй
— в противном случае:
1. ISNULL
(
sh.name, c.class
)
= o.ship

То есть имя корабля в таблице Outcomes сравнивается с именем корабля, полученным из таблицы Ships или именем класса, если имя корабля содержит NULL-значение. Это значение возникает в предыдущем соединении тогда, когда класс не имеет кораблей в Ships; и только в этом случае!
Опять рассмотрим случай, когда в Ships имеется корабль A некоторого класса
Class_1, а в таблице Outcomes содержится как этот корабль, так и головной корабль класса Class_1 (имя совпадает с именем класса). Пусть оба эти корабля были потоплены. Тогда первое соединение даст:
Class_1 А
Второе же соединение будет искать в таблице Outcomes строки, удовлетворяющие вышеприведенному предикату. Такой строкой будет всего одна:
Class_1 А A так как будет выполнено сравнение только по имени корабля (А), но не по классу!
Решение 3.12.4
1.
SELECT
class,
SUM
(
sunks
)
sunks
2.
FROM
(
SELECT
cl.class,
1
sunks
3.
FROM
Classes cl
LEFT
JOIN
4. Ships sh
ON
cl.class = sh.class
INNER
JOIN
5. Outcomes ou
ON
ou.ship = sh.name
OR
6. ou.ship = cl.class
7.
WHERE
result=
'sunk'
8.
UNION
9.
SELECT
DISTINCT
class,
0
sunks
10.
FROM
classes
11.
)
tab

12.
GROUP
BY
class;
В первом из объединяемых запросов предложения FROM для каждого потопленного корабля в результирующий набор попадает строка:
Класс 1
В основном запросе эти
«единички» суммируются.
Но поскольку UNION устраняет дубликаты, то для любого количества потопленных кораблей в классе мы, в конечном итоге получим либо 1 (из этого первого запроса), либо 0 из второго, который учитывает классы, не имеющие потопленных кораблей.
Если же выполнить объединение при помощи UNION ALL, то придем к решению 3.12.2
, которое содержит аналогичную ошибку.
Решение 3.12.5
1.
SELECT
t1.class,
COUNT
(
*
)
AS
cnt
2.
FROM
(
SELECT
a.class, b.name
3.
FROM
Classes a
LEFT
JOIN
4.
-- соединение с Ships без головных кораблей:
5. Ships b
ON
a.class = b.class
AND
6. a.class <> b.name
7.
)
AS
t1
JOIN
8.
-- соединение либо по классу для головных кораблей, либо
по имени
9. Outcomes t2
ON
t1.class = t2.ship
OR
10. t1.name = t2.ship
11.
WHERE
result =
'sunk'
12.
GROUP
BY
t1.class
13.
-- Выбираются классы кораблей, которые не попали в
первый запрос.
14.
-- Это классы, не имеющие потопленных кораблей.
15.
UNION
16.
SELECT
class,
'0'
17.
FROM
Classes
18.
WHERE
class
NOT
IN
(
SELECT
DISTINCT
t1.class
19.
FROM
(
SELECT
a.class, b.name
20.
FROM
Classes a
LEFT
JOIN
21.
Ships b
ON
a.class
= b.class
AND

22. a.class
<> b.name
23.
)
AS
t1
JOIN
24.
Outcomes t2
ON
t1.class
= t2.ship
OR
25. t1.name = t2.ship
26.
WHERE
result =
'sunk'
27.
)
;
Решение 3.12.6
1.
SELECT
d.class class,
(
SELECT
COUNT
(
f.result
)
2.
FROM
(
SELECT
c.result
3.
FROM
Ships b
LEFT
OUTER
JOIN
4. Outcomes c
ON
(
b.name
= c.ship
)
5.
WHERE
c.result =
'sunk'
AND
6. d.class = b.class
7.
UNION
ALL
8.
SELECT
c.result
9.
FROM
Outcomes c
10.
WHERE
c.result
=
'sunk'
AND
11. d.class
= c.ship
12.
)
f
13.
)
Sunks
14.
FROM
Classes d;
Для анализа двух последних решений — 3.12.5 и 3.12.6 — рассмотрим следующие варианты данных. В таблице Ships (показаны только принципиальные для анализа столбцы):
name
class
ship1_class_1 class_1
ship2_class_1 class_1

В таблице Outcomes:
ship
result
ship1_class_1 sunk
class_1
sunk
Тогда согласно предикату соединения в решении 3.12.5 1.
ON
t1.class = t2.ship
OR
2. t1.name = t2.ship в результирующий набор дважды попадет корабль ship1_class_1 из таблицы
Ships, так как для первой строки в таблице Outcomes у него совпадает имя корабля, а для второй — название класса. В результате получим 3 потопленных корабля, хотя на самом деле их только 2.
Решение задачи 3.12.6 даст нам здесь правильный результат, поскольку первый запрос в объединении (соединение по имени корабля) даст только ship1_class_1, а второй — только class_1. Однако это решение тоже не верно, что будет продемонстрировано на другом варианте данных.
В таблице Ships
name class ship1_class_2 class_2 class_2 class_2
В таблице Outcomes:
ship result ship1_class_2 sunk class_2 sunk
Первый запрос в объединении даст нам оба потопленных корабля класса class_2, а второй — головной корабль этого класса. Поскольку при

объединении используется UNION ALL, то головной корабль дважды будет учтен в результирующем наборе, в результате чего мы опять получаем 3 вместо 2. Косметическое исправление