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

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

UNION ALL на UNION не делает решение верным, так как здесь возникает та же ошибка, что и в решении
3.12.4
, когда для любого количества потопленных кораблей класса в результат попадает только 1.
Кстати, решение 3.12.5 на этих данных тоже дает значение 3, но по другой причине, описанной выше.
Упражнение 57
Для классов, имеющих потери в виде
потопленных кораблей и не менее 3 кораблей
в базе данных, вывести имя класса и число
потопленных кораблей.
Эта задача в чем-то подобна задаче 56
, то есть здесь можно допускать те же ошибки в подсчете потопленных кораблей.
Однако ситуация усугубляется еще и определением общего числа кораблей в классе.
Решение 3.13.1
1.
SELECT
c.class,
SUM
(
outc
)
2.
FROM
Classes c
LEFT
JOIN
3. Ships s
ON
c.class = s.class
LEFT
JOIN
4.
(
SELECT
ship,
1
outc
5.
FROM
Outcomes
6.
WHERE
result =
'sunk'
)
o
ON
s.name = o.ship
OR
7. c.class = o.ship
8.
GROUP
BY
c.class
9.
HAVING
COUNT
(
*
)
>
2
AND
10.
SUM
(
outc
)
IS
NOT
NULL
;
Первое левое соединение дает все классы, повторяющиеся столько раз, сколько имеется кораблей в таблице Ships. Если некий класс не имеет кораблей в этой таблице, то он будет упомянут один раз, и это дает нам

возможность учесть головные корабли класса в таблице Outcomes, если таковые имеются.
Далее выполняется еще одно левое соединение с набором потопленных кораблей по предикату
1.
ON
s.name = o.ship
OR
c.class = o.ship
В вычисляемый столбец заносится 1, если имя потопленного корабля совпадает либо с именем корабля, либо с именем класса из полученного ранее набора. Таким образом, мы здесь и пытаемся учесть головные корабли.
Наконец, выполняется группировка по классам с отбором по числу кораблей
(строк) класса и подсчитывается сумма потопленных кораблей (единиц в столбце outs). Автор решения предлагает рациональный способ вычислить в одной группировке и общее число кораблей, и количество потопленных кораблей в классе. Предикат:
1.
SUM
(
outc
)
IS
NOT
NULL
в соответствии с условием задачи убирает из результата классы, не имеющие потопленных кораблей.
Те, кто внимательно читал анализ предыдущих задач, уже догадались, в чем дело. Правильно, проблема в предикате второго соединения. Однако не только в этом.
Рассмотрим следующий вариант данных. Пусть для некоторого класса class_N в таблице Ships имеется два корабля: ship_1 и ship_2. Кроме того, в таблице Outcomes есть потопленный корабль ship_1 и оставшийся на плаву головной — class_N.
Первое соединение даст:

Class
Ship
Class_N ship_1
Class_N ship_2
Выполняем второе соединение:
Class
ship
outs
Class_N ship_1 1
Class_N ship_2 NULL
В результате этот класс вообще не попадет в результирующий набор, так как не будет выполнено условие COUNT(*) > 2, хотя на самом деле корабля 3.
Причина ошибки заключается в том, что мы выполняем соединение только по потопленным кораблям, одновременно подсчитывая общее число кораблей.
Давайте теперь немного изменим данные в примере. Пусть и головной корабль class_N тоже потоплен. Тогда результатом соединения будет:
class
ship
outs
class_N ship_1 1
class_N ship_2 NULL
class_N ship_1 1
class_N ship_2 1

Последние две строки будут получены в результате соединения со строкой потопленного головного корабля, так как предикат c.class = o.ship дает
«истину». Таким образом, мы вместо одной строки для головного корабля получаем по строке на каждый корабль класса из таблицы Ships. Итого, вместо
class
outs
class_N 2 имеем
class
outs
class_N 3
Вы можете попытаться исправить это решение или использовать другой подход на базе внутреннего соединения и объединения.
Как это ни покажется удивительным, но ниже приведены три совсем разных решения, которые содержат одну и ту же ошибку, по крайней мере, они возвращают один и тот же результат на проверочной базе сайта.
Решение 3.13.2
1.
SELECT
class,
SUM
(
sunk
)
2.
FROM
(
SELECT
class,
COUNT
(
*
)
AS
sunk
3.
FROM
Ships a
JOIN
4. Outcomes b
ON
a.name = b.ship
AND
5. a.class <> b.ship
6.
WHERE
result =
'sunk'
7.
GROUP
BY
class
8.
UNION
ALL
9.
SELECT
class,
'1'
10.
FROM
Classes a
JOIN
11.
Outcomes b
ON
a.class = b.ship
12.
WHERE
result =
'sunk'
13.
UNION
ALL
14.
SELECT
class,
'0'
15.
FROM
classes

16.
)
t
17.
-- где классы с числом кораблей больше 2:
18.
WHERE
class
IN
(
SELECT
t1.class
19.
FROM
(
SELECT
a.class
20.
FROM
Classes a
LEFT
JOIN
21.
Ships b
ON
a.class = b.class
22.
)
t1
LEFT
JOIN
(
SELECT
DISTINCT
ship
23.
FROM
Outcomes
24.
WHERE
ship
NOT
IN
(
SELECT
name
25.
FROM
Ships
26.
)
27.
)
t2
ON
t1.class = t2.ship
28.
GROUP
BY
t1.class
29.
HAVING
COUNT
(
*
)
>
2 30.
)
31.
GROUP
BY
class
32.
HAVING
SUM
(
sunk
)
>
0
;
Решение 3.13.3
1.
SELECT
a.class
AS
cls, a.num_sunks
AS
sunk
2.
FROM
(
SELECT
c.class,
COUNT
(
o.ship
)
AS
num_sunks
3.
FROM
Outcomes o
LEFT
JOIN
4. Ships s
ON
o.ship = s.name
LEFT
JOIN
5. Classes c
ON
s.class = c.class
6.
WHERE
o.result =
'sunk'
7.
GROUP
BY
c.class
)
a,
8.
(
SELECT
c1.class
9.
FROM
Ships s1, Classes c1 10.
WHERE
s1.class = c1.class
11.
GROUP
BY
c1.class
12.
HAVING
COUNT
(
name
)
>=
3 13.
)
B
14.
WHERE
a.class = b.class;
Решение 3.13.4
1.
SELECT
class,
COUNT
(
result
)
AS
sunk
2.
FROM
(
SELECT
class, result, name
3.
FROM
Ships
LEFT
JOIN
4. Outcomes
ON
ship=name
AND

5. class
IS
NOT
NULL
AND
6. result =
'sunk'
7.
)
T
8.
GROUP
BY
class
9.
HAVING
COUNT
(
class
)
>
2
AND
10.
COUNT
(
result
)
>
0
;
Проанализируйте тонкости вышеприведенных решений, самым красивым из которых, безусловно, является 3.13.4. Всего лишь одно соединение, для которого сразу подсчитывается как количество потопленных, так и общее число кораблей в классе. У этих решений имеется общая ошибка, о которой шла речь выше: не учтены головные корабли, которые присутствуют в таблице
Outcomes и отсутствуют в таблице Ships.
Упражнение 59
Посчитать остаток денежных средств на
каждом пункте приема для базы данных с
отчетностью не чаще одного раза в день.
Вывод: пункт, остаток.
Решение 2.2.1
1.
SELECT
ss.point, ss.inc - dd.out
2.
FROM
(
SELECT
i.point,
SUM
(
inc
)
AS
inc
3.
FROM
Income_o i
4.
GROUP
BY
i.point
5.
)
AS
ss,
6.
(
SELECT
o.point,
SUM
(
out
)
AS
out
7.
FROM
Outcome_o o
8.
GROUP
BY
o.point
9.
)
AS
dd
10.
WHERE
ss.point = dd.point;
В предложении FROM в каждом из подзапросов определяется сумма соответственно прихода и расхода денежных средств на каждом из пунктов приема. Эти подзапросы соединяются по равенству номеров пунктов приема,

что позволяет построчно вычислить остаток денежных средств на каждом пункте: ss.inc— dd.out.
Казалось бы, все правильно, однако, решение 2.2.1 содержит одну ошибку.
Попробуйте ее найти.
Упражнение 60
Посчитать остаток денежных средств на
начало дня 15.04.2001 на каждом пункте
приема для базы данных с отчетностью не
чаще одного раза в день. Вывод: пункт,
остаток.
Задача 2.3.1
1.
SELECT
i.point,
CASE
inc
2.
WHEN
NULL
3.
THEN
0 4.
ELSE
inc
5.
END
-
6.
CASE
out
7.
WHEN
NULL
8.
THEN
0 9.
ELSE
out
10.
END
11.
FROM
(
SELECT
point,
SUM
(
inc
)
inc
12.
FROM
Income_o
13.
WHERE
'20010415'
> date
14.
GROUP
BY
point
15.
)
AS
I
FULL
JOIN
16.
(
SELECT
point,
SUM
(
out
)
out
17.
FROM
Outcome_o
18.
WHERE
'20010415'
> date
19.
GROUP
BY
point
20.
)
AS
III
ON
III.point = I.point;
Эта задача во многом аналогична предыдущей задаче №59
. По сути, здесь дополнительно используется лишь отбор по дате. В связи с этим мы хотим

обратить внимание на ее представление в запросе. Дело в том, что в предикате сравнивается строка с полем типа datetime. В
SQL
Server имеется функция CONVERT, которая позволяет преобразовать строковое представление даты/времени к этому типу, используя различные форматы представления даты. Однако строковое представление в виде год месяц день, которое используется в рассматриваемом решении, всегда будет правильно преобразовываться неявно к типу datetime вне зависимости от настроек сервера [
5
].
Кто разобрался с ошибкой в предыдущей задаче, наверняка, увидел здесь попытку ее исправить. К сожалению, попытку неудачную, что, с другой стороны, дает вам возможность еще раз проверить себя.
База
данных
«Корабли»
Рассматривается база данных кораблей, участвовавших в морских сражениях второй мировой войны. Имеются следующие отношения:
1. Classes
(
class, type, country, numGuns, bore, displacement
)
2. Ships
(
name, class, launched
)
3. Battles
(
name, date
)
4. Outcomes
(
ship, battle, result
)
Корабли в «классах» построены по одному и тому же проекту. Классу присваивается либо имя первого корабля, построенного по данному проекту, либо названию класса дается имя проекта, которое в этом случае не совпадает с именем ни одного из кораблей. Корабль, давший название классу, называется головным.
Атрибутами отношения Classes являются имя класса (class), тип (значение bb используется для обозначения боевого или линейного корабля, а bc для боевого крейсера), страну (country), которой принадлежат корабли данного

класса, число главных орудий (numGuns), калибр орудий (bore — диаметр ствола орудия в дюймах) и водоизмещение в тоннах (displacement).
В отношение Ships записывается информация о кораблях: название корабля
(name), имя его класса (class) и год спуска на воду (launched).
В отношение Battles включены название (name) и дата битвы (date), в которой участвовал корабль.
Отношение Outcomes используется для хранения информации о результатах участия кораблей в битвах, а именно, имя корабля (ship), название сражения
(battle) и чем завершилось сражение для данного корабля (потоплен — sunk, поврежден — damaged или невредим — ok).
Примечание:
В отношение Outcomes могут входить корабли, отсутствующие в
отношении Ships.
Рис. 3.1. Схема базы данных «Корабли»
Отметим несколько моментов, на которые следует обратить внимание при анализе схемы на рис. 3.1
. Таблица Outcomes имеет составной первичный ключ {ship, battle}. Это ограничение не позволит ввести в базу данных дважды один и тот же корабль, принимавший участие в одном и том же сражении.
Однако допустимо неоднократное присутствие одного и того же корабля в данной таблице, что означает участие корабля в нескольких битвах. Класс корабля определяется из таблицы Ships, которая имеет внешний ключ (class) к таблице Classes.
Особенностью данной схемы, которая усложняет логику запросов и служит причиной ошибок при решении задач, является то, что таблицы Outcomes и
Ships никак не связаны, то есть в таблице результатов сражений могут

находиться корабли, отсутствующие в таблице Ships. На основании этого, казалось бы, можно сделать вывод о том, что для таких кораблей их класс неизвестен, а, следовательно, неизвестны и все технические характеристики.
Это не совсем так. Как следует из описания предметной области, имя головного корабля совпадает с именем класса, родоначальником которого он является. Поэтому если имя корабля из таблицы Outcomes совпадает с именем класса в таблице Classes, то однозначно можно сказать, что это головной корабль, и, следовательно, все его характеристики нам известны.
Каждый знает, как улучшить эту «плохую» схему: связать таблицы Ships и
Outcomes по имени корабля, при этом столбец ship в Outcomes становится внешним ключом к таблице Ships. Безусловно, это так, однако не следует забывать, что в реальной ситуации не вся информация может быть доступна.
Например, имеется архивная информация о кораблях, участвовавших в том или ином сражении, без указания классов этих кораблей. При наличии обсуждаемой связи сначала будет необходимо внести такой корабль в таблицу
Ships, при этом столбец class должен допускать NULL-значения.
С другой стороны, что нам мешает ввести головной корабль, который попал в таблицу Outcomes, также и в таблицу Ships? В принципе ничего, так как год спуска на воду не является обязательной информацией. По этому поводу следует заметить, что администратор базы данных и разработчик приложения, как правило, разные люди. Не всегда разработчик приложения и его пользователи имеют права на модификацию данных.
Плохая структура еще не означает, что из нее нельзя извлечь достоверную информацию, чем собственно мы и занимаемся, решая предлагаемые задачи.
Что касается учебных целей, то работа с такой структурой даст значительно больше в освоении языка, чем структура «хорошая», так как заставит писать более сложные запросы и научит учитывать дополнительные обстоятельства, накладываемые схемой. Этим видимо и руководствовались авторы этой схемы данных
[2]
. Кроме того, запросы, написанные для «плохой» схемы, будут давать правильные результаты и после улучшения структуры (хотя и станут менее эффективными), то есть тогда, когда вся информация станет доступной, и мы сможем установить связь между таблицами Ships и Outcomes.
Наконец, стоит обратить внимание на то, что столбец launched в таблице
Ships допускает NULL-значения, то есть нам может быть неизвестен год спуска на воду того или иного корабля. То же самое мы можем сказать о кораблях из Outcomes, отсутствующих в Ships.
Что ж, перейдем к решению задач. Заметим лишь, что в настоящей главе мы уже не рассматриваем совсем простые задачи (хотя они имеются и для этой схемы), которых должно было хватить вам в первой главе.

Упражнение 70
Укажите сражения, в которых
участвовало, по меньшей мере, три корабля
одной и той же страны.
Решение 3.7.1
1.
SELECT
AA.name
AS
bat
2.
FROM
(
SELECT
O.battle
AS
name, C.country,
COUNT
(
O.ship
)
AS
cnt
3.
FROM
Outcomes O, Ships S, Classes C
4.
WHERE
O.ship = S.name
AND
5. C.class = S.class
6.
GROUP
BY
O.battle, C.country
7.
)
AA
8.
WHERE
AA.cnt >=
3
;
Можно назвать этот запрос «первым приближением» к решению.
Соединяются все необходимые таблицы через предложение WHERE, в результате чего определяется битва и страна (из таблицы Classes) для кораблей из таблицы Outcomes. Далее выполняется группировка по стране и сражению с последующим отбором по числу кораблей.
Ошибочным здесь является то, что мы никак не учитываем корабли, отсутствующие в таблице Ships, так как используются внутренние соединения.
Читатель уже, наверное, вник в используемую схему и понимает, что здесь нет учитываются головные корабли, класс которых может быть определен не только через таблицу Ships, но и непосредственно с помощью таблицы Classes, а, следовательно, может быть определена и владеющая кораблем страна.
Теперь рассмотрим решения, в которых была сделана попытка учесть эту особенность схемы данных.
Решение 3.7.2

1.
SELECT
bat
2.
FROM
(
SELECT
DISTINCT
d.battle
AS
bat, a.country,
COUNT
(
d.ship
)
AS
s
3.
FROM
Outcomes d, Ships b, Classes a
4.
WHERE
d.ship = b.name
AND
5. b.class=a.class
6.
GROUP
BY
d.battle, a.country
7.
UNION
8.
SELECT
DISTINCT
d.battle
AS
bat, a.country,
COUNT
(
d.ship
)
AS
s
9.
FROM
Outcomes d, Classes a
10.
WHERE
d.ship = a.class
AND
11. d.ship
NOT
IN
(
SELECT
name
12.
FROM
Ships
13.
)
14.
GROUP
BY
d.battle, a.country
15.
)
AS
t1 16.
WHERE
s >
2
;
Ошибка, характерная для начинающих, состоит в том, что сначала выполняется группировка, а потом объединение. И хотя здесь отсутствует ошибка решения 3.7.1 (во втором запросе объединения учтены головные корабли, которых нет в Ships), решение не даст нам страну, у которой в сражении участвовало 3 корабля, два из которых присутствуют в таблице
Ships, а один (головной) — только в таблице Outcomes.
Одно время на сайте системой проверки принималось заведомо неправильное решение:
Решение 3.7.3
1.
SELECT
battle
2.
FROM
Classes c
LEFT
JOIN
3. Ships s
ON
c.class = s.class
INNER
JOIN
4. Outcomes o
ON
o.ship = s.name
OR
5. c.class = o.ship
6.
GROUP
BY
battle, country
7.
HAVING
COUNT
(
ship
)
>
3
;

Обратите внимание на