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

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

HAVING
) только те классы, в которых оказался один корабль.
Таким образом, предполагается, что если в Outcomes имеется головной корабль, то кораблей в данном классе уже минимум два и, следовательно, этот класс не отвечает условиям задачи. В этом и состоит ошибка, так как ниоткуда не следует, что в таблице Ships не может быть головного корабля. Итак, если некий класс имеет один корабль в базе данных, и этот корабль является головным и присутствует в обеих рассматриваемых таблицах, то решение 3.2.2 ошибочно проигнорирует этот класс.

Решение 3.2.3
Посмотрите теперь, как можно более просто написать запрос, содержащий аналогичную ошибку:
1.
SELECT
class
2.
FROM
(
SELECT
class
3.
FROM
Ships
4.
UNION
ALL
5.
SELECT
ship
6.
FROM
Outcomes o
7.
WHERE
o.ship
IN
(
SELECT
class
8.
FROM
Classes
9.
)
10.
)
AS
cl
11.
GROUP
BY
class
12.
HAVING
COUNT
(
class
)
=
1
;
Идея такая. В подзапросе выбираются классы всех кораблей из таблицы Ships и объединяются с головными кораблями из таблицы Outcomes с сохранением дубликатов (UNION ALL). При этом используется тот факт, что имя головного корабля совпадает с именем класса (SELECT Ship) (!!!). То, что дубликаты сохраняются, — это совершенно правильно, так как в противном случае мы получим на класс одну строку для любого количества кораблей в классе. Затем делается группировка по классу, и фильтруются классы, содержащие один корабль. Решение выглядит значительно короче и понятней, чем решение 3.2.2
. Его и исправить будет проще, а исправлять придется, так как решение даст неверный результат, если головной корабль присутствует как в таблице Ships, так и в таблице Outcomes, в результате чего мы его дважды посчитаем.
Внимание:
Стоит обратить внимание на плодотворную идею этого решения
— сначала объединить все корабли, а уже потом выполнять
группировку по классам.
Решение 3.2.4

Подход на основе объединения не является единственно возможным.
Следующее решение использует соединения.
1.
SELECT
Classes.class
2.
FROM
Outcomes
RIGHT
OUTER
JOIN
3. Classes
ON
Outcomes.ship = Classes.class
LEFT
OUTER
JOIN
4. Ships
ON
Classes.class = Ships.class
5.
GROUP
BY
Classes.class
6.
HAVING
(
COUNT
(
COALESCE
(
Outcomes.ship, Ships.name
))
=
1
)
;
Правое соединение таблиц Outcomes и Classes дает нам головные корабли, при этом столбец Outcomes.ship будет содержать NULL-значение, если головного корабля нет в таблице Outcomes. Затем выполняется левое соединение таблиц Classes и Ships по внешнему ключу. Столбец Ships.name будет содержать NULL-значение, если класс не имеет кораблей в таблице
Ships. Полученный набор записей группируется по классу, после чего выполняется фильтрация по предикату
1.
COUNT
(
COALESCE
(
Outcomes.ship, Ships.name
))
=
1
Остановимся подробнее на этом элегантном приеме.
Примечание:
Автор не иронизирует, когда говорит «красивый», «элегантный» и т.
д. о неправильных подходах к решению задачи. Большинство
рассматриваемых здесь запросов писалось профессионалами, в
совершенстве владеющих языком SQL. Анализировать такие решения —
хорошая школа для начинающих. А ошибки в этих решениях как правило
связаны с игнорированием той или иной особенности предметной области,
и зачастую легко исправляются чисто косметическими средствами.
Чтобы все было до конца ясно, приведем примеры четырех возможных вариантов строк (взятых из доступной базы данных кроме последнего случая), которые получаются в результате соединения. Вот они:

Ship
class
name
Bismarck
Bismarck
NULL
Tennessee Tennessee California
Tennessee Tennessee Tennessee
NULL
Yamato
Musashi
NULL
Yamato
Yamato
NULL
Class_1
NULL
NULL в столбце name для класса Bismarck означает, что головной корабль имеется только в таблице Outcomes. Корабли California и Tennessee класса
Tennessee имеются в таблице Ships, при этом головной корабль есть также в таблице Outcomes. В третьем случае два корабля класса Yamato присутствуют в таблице Ships, в таблице же Outcomes нет головного корабля данного класса.
Для четвертого случая класс Class_1 не имеет кораблей в базе данных.
Вернемся к предикату. Функция COALESCE (Outcomes.ship, Ships.name) вернет первое не NULL значение своих аргументов или NULL, если оба аргумента есть NULL-значение. Подробнее о функции COALESCE можно почитать в пункте 5.10
Агрегатная функция COUNT, имеющая аргумент, вернет количество не NULL-значений аргумента в группе. Поэтому для класса Bismarck мы получим 1, для Tennessee и Yamato — 2 и, наконец, для Class_1 — 0. В результирующий набор из этих четырех классов попадает только Бисмарк, так как только он отвечает предикату.
Это решение не содержит ошибки предыдущего решения 3.2.3
, то есть головной корабль, присутствующий как в таблице Ships, так и в таблице
Outcomes, будет учтен только один раз. Это обусловлено тем, что при используемом соединении в результирующем наборе будет только одна строка.
Внимание:

Всегда ли справедливо для нашей базы данных последнее
утверждение? Приведите пример данных, когда это будет не так. В
этом, кстати, состоит еще одна ошибка данного решения.
Кроме того, в этом решении содержится та же ошибка, что и в примере 3.2.1
Если добавить в базу данных указанную там строку, то в результате соединения мы получим только одну строку на два корабля класса Бисмарк:
Ship
Class
name
Bismarck Bismarck Tirpitz
Если вы еще не обнаружили ошибки, упомянутой на врезке, загляните в
ПиР
Упражнение 38
Найдите страны, владевшие когда-либо как
обычными кораблями, так и крейсерами.
Таким образом, нужно найти страны, которые имели корабли типа bc и bb. Слова «владевшие когда-либо» должно, по мнению автора, задействовать следующую логическую цепочку рассуждений:

В текущем состоянии БД может не быть корабля какого-либо класса, хотя страна могла их иметь.

Тогда откуда мы можем узнать, что такие корабли были? Только по имеющимся в БД классам, поскольку только в таблице Classes имеется информация о типе и стране.

Если есть класс, скажем, типа bc (крейсер), то были и корабли этого класса, даже если их нет в таблицах Ships и Outcomes, поскольку

информация о нереализованном проекте вряд ли станет доступной.
Вывод: для решения этой задачи нужно рассматривать только таблицу Classes. В результате получаем достаточно простую задачу.
Автор не писал бы это объяснение, если бы ему не присылали подобные нижеприведенному решения с просьбой объяснить причину, по которой их не принимает система. Вот это решение:
1.
SELECT
DISTINCT
c1.country
2.
FROM
Classes c1
INNER
JOIN
3. Classes c2
ON
c1.country = c2.country
INNER
JOIN
4. Ships s1
ON
c1.class = s1.class
INNER
JOIN
5. Ships s2
ON
c2.class = s2.class
6.
WHERE
c1.type =
'bb'
AND
7. c2.type =
'bc'
8.
UNION
9.
SELECT
DISTINCT
c1.country
10.
FROM
Classes c1
INNER
JOIN
11.
Classes c2
ON
c1.country = c2.country
INNER
JOIN
12.
Ships s1
ON
c1.class = s1.class
INNER
JOIN
13.
Outcomes s2
ON
c2.class = s2.ship
14.
WHERE
c1.type =
'bb'
AND
15. c2.type =
'bc'
OR
16. c2.type =
'bb'
AND
17. c1.type =
'bc'
;
Какой формулировке соответствует это решение? Найти страны, у которых в
БД имеются корабли обоих типов? Если ответ «да», то это решение все равно не является правильным.
В первом запросе объединения определяются страны, которые в таблице
Ships имеют корабли обоих типов. Во втором запросе определяются страны, которые имеют в таблице Ships корабль одного типа, а в таблице Outcomes — другого.

Но есть же еще один вариант, когда имеются только головные корабли в
Outcomes, причем обоих типов. Добавьте, например, в свою базу данных следующие строки:
1.
INSERT
INTO
Classes
2.
VALUES
(
'c_bb'
,
'bb'
,
'AAA'
,
10
,
15
,
35000
)
;
3.
INSERT
INTO
Classes
4.
VALUES
(
'c_bc'
,
'bc'
,
'AAA'
,
6
,
15
,
45000
)
;
5.
INSERT
INTO
Outcomes
6.
VALUES
(
'c_bb'
,
'Guadalcanal'
,
'ok'
)
;
7.
INSERT
INTO
Outcomes
8.
VALUES
(
'c_bc'
,
'Guadalcanal'
,
'ok'
)
;
Страна ААА имеет корабли обоих типов. Однако вышеприведенный запрос не выведет эту страну, как это и следовало ожидать.
Замечу также, что предложение DISTINCT в обоих запросах совершенно излишне, так как UNION устранит возможные дубликаты. С точки зрения логики это замечание не является существенным. Однако с точки зрения оптимизации это достаточно важный момент. Сервер тратит значительные ресурсы на удаление дубликатов, поэтому не стоит это делать несколько раз.
Сравните планы выполнения запросов с DISTINCT и без него.
А вот пример половинчатого решения, принимаемого системой на момент его написания:
1.
SELECT
DISTINCT
country
2.
FROM
Classes
RIGHT
JOIN
3.
(
SELECT
DISTINCT
COALESCE
(
ship, name
)
AS
name, class
4.
FROM
Outcomes
FULL
OUTER
JOIN
5. Ships
ON
ship = name
6.
)
AS
z
ON
z.name = Classes.class
OR
7. z.class = Classes.class
8.
WHERE
type =
'bb'
AND
9. country
IN
(
SELECT
country
10.
FROM
classes
11.
WHERE
type =
'bc'
12.
)
;

Здесь берутся все корабли из обеих таблиц — Ships и Outcomes. Далее результат соединяется с таблицей Classes, определяется класс кораблей, и отбираются только те из них, которые имеют тип bb (боевые корабли).
Наконец, проверяется, что страна найденных кораблей имеет также классы bc. Решение оказалось правильным только потому, что страны, имеющие классы обоих типов, имеют в текущем состоянии БД корабли типа ‘bb’.
Заблокировать подобные решения очень просто: достаточно добавить в таблицу Classes два класса (типа ‘bc’ и ‘bb’) для страны, которая вообще не имеет кораблей в БД. Однако лучше уточнить формулировку, скажем, так:
Найдите страны, имеющие классы как обычных боевых кораблей
('bb'), так и крейсеров ('bc').
Изменение формулировки уже выполнено на сайте. Тем не менее, надеюсь, что проведенный анализ решений оказался полезным для вас.
Упражнение 39
Найдите корабли, «сохранившиеся для
будущих сражений»; то есть выведенные из
строя в одной битве (damaged), они
участвовали в другой.
Вот пример неправильно понятого условия:
1.
SELECT
DISTINCT
ship
FROM
Outcomes os
2.
WHERE
EXISTS
(
SELECT
ship
3.
FROM
Outcomes oa
4.
WHERE
oa.ship = os.ship
AND
5. result =
'damaged'
6.
)
AND
7.
EXISTS
(
SELECT
SHIP
8.
FROM
Outcomes ou
9.
WHERE
ou.ship=os.ship
10.
GROUP
BY
ship
11.
HAVING
COUNT
(
battle
)
>
1 12.
)
;

Это решение исполнено в стиле реляционного исчисления, а именно, разыскиваются такие корабли в таблице Outcomes, которые были повреждены
(первый предикат EXISTS) и которые участвовали более чем в одной битве
(второй предикат EXISTS).
Ошибка здесь состоит в том, что проигнорировано условие «сохранившиеся для будущих сражений», которое означает, что после битвы, в которой корабль получил повреждение, он принимал участие в более позднем сражении. Таким образом, для получения правильного решения этой задачи нужно анализировать даты сражений, которые содержатся в таблице сражений
Battles.
Решение 3.4.2. Тот же результат даст и решение, использующее самосоединение:
1.
SELECT
DISTINCT
o.ship
2.
FROM
Outcomes
AS
o, Outcomes
AS
o2 3.
WHERE
(
o.result =
'damaged'
OR
4. o2.result =
'damaged'
5.
)
AND
6. o.battle <> o2.battle
AND
7. o.ship = o2.ship;
Здесь применяется соединение таблицы Outcomes с самой собой при условии, что корабль тот же самый, а битвы разные. Кроме того, в одной из битв корабль был поврежден. Как легко увидеть, отсутствует проверка на более раннюю дату сражения, в котором корабль был поврежден.
Решение 3.4.3
Как это ни покажется странным, но нижеприведенный запрос некоторое время принимался системой проверки.
1.
SELECT
s.name
2.
FROM
Ships s
JOIN
3. Outcomes o
ON
s.name = o.ship
JOIN

4. Battles b
ON
o.battle = b.name
5.
GROUP
BY
s.name
6.
HAVING
COUNT
(
s.name
)
=
2
AND
7.
(
MIN
(
result
)
=
'damaged'
OR
8.
MAX
(
result
)
=
'damaged'
9.
)
10.
UNION
11.
SELECT
o.ship
12.
FROM
Classes c
JOIN
13.
Outcomes o
ON
c.class = o.ship
JOIN
14.
Battles b
ON
o.battle = b.name
15.
WHERE
o.ship
NOT
IN
(
SELECT
name
16.
FROM
Ships
17.
)
18.
GROUP
BY
o.ship
19.
HAVING
COUNT
(
o.ship
)
=
2
AND
20.
(
MIN
(
result
)
=
'damaged'
OR
21.
MAX
(
result
)
=
'damaged'
22.
)
;
Во-первых, объединяются запросы, которые выполняют соединение участвующих в сражениях кораблей (таблица Outcomes) с таблицами Ships и
Classes соответственно. Кстати говоря, предикат
1. o.ship
NOT
IN
(
SELECT
name
FROM
Ships
)
во втором запросе явно лишний, так как UNION исключит возможные дубликаты.
Эти соединения не просто избыточны, они ошибочны, так как в описании базы данных сказано, что в таблице Outcomes могут быть корабли, отсутствующие в Ships. То есть если найдется не головной корабль, которого нет в таблице Ships и который отвечает условиям задачи, то он не попадет в результирующий набор вышеприведенного запроса.
Во-вторых, предикат
1.
HAVING
COUNT
(
o.ship
)
=
2

ограничивает возможные варианты только двумя сражениями корабля. А почему корабль не может принимать участие более чем в двух сражениях?
Он же не обязательно был потоплен после того, как получил повреждение.
Причем он мог участвовать в сражениях и до повреждения (например, с результатом ok). Тогда, если в следующем и последним для корабля сражении он был поврежден, то запрос 3.4.3 выведет этот корабль, хотя это и не отвечает условиям задачи.
В-третьих, автору не вполне понятно условие:
1.
(
MIN
(
result
)
=
'damaged'
OR
MAX
(
result
)
=
'damaged'
)
Примечание:
В связи с последним предикатом хочется напомнить
читателям, что запросы, которые мы анализируем, были
написаны посетителями сайта. Допускаемые ошибки не
надуманы, а являются естественным следствием процесса
обучения, когда формальное знание языка программирования
применяется к решению конкретных задач. Собственно, эта книга
и написана для того, чтобы облегчить переход от формального
знания к практическому умению.
Однако вернемся к условию. В соответствии с описанием предметной области корабль может быть:

поврежденным (damaged);

остаться невредимым (ok);

быть потопленным (sunk).
Поэтому условие MIN(result) = 'damaged' будет выполнено, если в одной из битв корабль был поврежден (при естественной сортировке текстовых строк буква «d» идет раньше, чем буквы «o» и «s»). Однако это совсем не означает, что поврежден он был прежде, чем принял участие в следующем сражении, что требуется по условиям задачи. Здесь нужно оценивать даты сражений. Что же касается MAX(result) = 'damaged', то это условие не будет выполняться, если результаты сражений были разные; если же они были одинаковые, то это не даст ничего нового по сравнению с первым условием на минимум.
Вот такое наложение ошибок давало правильный результат на обеих проверочных базах. Меры уже приняты: добавлены проверочные данные, на которых данное решение дает неверный результат. Как это и должно быть по логике этого запроса.

Упражнение 46
Укажите названия, водоизмещение и число
орудий, кораблей участвовавших в сражении
при Гвадалканале (Guadalcanal).
Все нужные нам корабли, принимавшие участие в сражении при Гвадалканале, находятся в таблице
Outcomes, а требуемые характеристики — в таблице Classes. Поэтому первое, что приходит в голову, — это соединить эти таблицы для получения нужного результата:
Решение 3.1.1
1.
SELECT
Outcomes.ship,
Classes.displacement,
Classes.numGuns
2.
FROM
Classes
RIGHT
JOIN
3. Outcomes
ON
Classes.class = Outcomes.ship
4.
WHERE
Outcomes.battle =
'Guadalcanal'
;
Внешнее соединение здесь используется законно, так как поскольку в задании сказано о кораблях, участвовавших в сражении, то выводить нужно все такие корабли вне зависимости от того, совпадает его имя с именем класса или нет. Заметим, что внутреннее соединение вернет пустой набор записей, так как в основной базе данных не оказалось головных кораблей, участвовавших в этом сражении. А так мы имеем: