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

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

Упражнение 30
В предположении, что приход и расход денег
на каждом пункте приема фиксируется
произвольное число раз (в обе таблицы
добавлен первичный ключ code), написать
запрос с выходными данными (point, date, out,
inc), в котором каждому пункту за каждую
дату соответствует одна строка.
В этой задаче требуется данные из двух таблиц собрать в одном результирующем наборе; при этом приход и расход денег на пункте приема в один и тот же день должны находиться в одной строке.
Аналогичная задача (
29
) для таблиц Income_o и
Outcome_o, как правило, затруднений не вызывала.
Суть проблемы демонстрирует следующее решение.
Решение 2.1.1
1.
SELECT
Income.point, Income.date,
SUM
(
out
)
,
SUM
(
inc
)
2.
FROM
Income
LEFT
JOIN
3. Outcome
ON
Income.point = Outcome.point
AND
4. Income.date = Outcome.date
5.
GROUP
BY
Income.point, Income.date
6.
UNION
7.
SELECT
Outcome.point, Outcome.date,
SUM
(
out
)
,
SUM
(
inc
)
8.
FROM
Outcome
LEFT
JOIN
9. Income
ON
Income.point = Outcome.point
AND
10.
Income.date = Outcome.date
11.
GROUP
BY
Outcome.point, Outcome.date;
Идея решения такова. Выполняется соединение таблицы, в которой фиксируются приходы денег, с таблицей расходов средств по совпадению номера пункта приема и даты. Левое соединение, которое здесь используется, гарантирует получение результата в том случае, если на пункте приема в некоторые дни есть только приход, но нет расхода (NULL). Далее выполняется объединение с запросом, в котором выполняется обратное левое соединение таблицы расхода с таблицей прихода. Таким образом, учитывается

случай, когда на пункте есть расход, но нет прихода. Исключение дубликатов строк (в случаях, когда есть и приход, и расход) выполняется использованием оператора UNION.
Запрос 2.1.1 дает неверный результат, когда в один день на пункте приема выполняется несколько операций по приходу/расходу денежных средств. В качестве примера возьмем характерный для этого случая день — 24 марта 2001 года. Выполним пару запросов:
1.
SELECT
*
FROM
Income
2.
WHERE
date =
'2001-03-24 00:00:00.000'
AND
point =
1
;
1.
SELECT
*
FROM
Outcome
2.
WHERE
date =
'2001-03-24 00:00:00.000'
AND
point =
1
;
Получим следующий результат:
Приход
code
point
date
inc
3
1 2001-03-24 00:00:00.000 3600.0000
11
1 2001-03-24 00:00:00.000 3400.0000
Расход
code
point
date
out
2
1 2001-03-24 00:00:00.000 3663.0000
13
1 2001-03-24 00:00:00.000 3500.0000

В данном случае, когда есть и приход, и расход, внешнее соединение эквивалентно внутреннему соединению, то есть каждая строка из одной таблицы соединяется с каждой строк из другой таблицы, если в этих строках совпадают и дата, и номер пункта приема. Поэтому перед группировкой будет получен следующий результат (показаны только столбцы прихода и расхода):
inc
out
3600.0000 3663.0000
3600.0000 3500.0000
3400.0000 3663.0000
3400.0000 3500.0000
После группировки и суммирования мы получаем удвоение результата, как для прихода, так и для расхода. Если бы прихода было три, то мы бы получили утроение расхода и т. д.
И дубликаты здесь ни при чем, так как каждый из объединяемых запросов дает аналогичный результат, то есть остается одна строка на каждую пару значений {пункт, дата}.
Упражнение 32
Одной из характеристик корабля является
половина куба калибра его главных орудий
(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 по описанным выше причинам, но, тем не менее, исправить теперь этот запрос вам будет несложно.
Разбирая ошибки наших посетителей, автор обычно указывает те варианты данных, на которых рассматриваемые запросы возвращают неверные данные.
Советуем вам наполнять свою базу аналогичными данными, тогда тестирование ваших запросов также и на других задачах будет более эффективным.
Вот еще один запрос, кстати говоря, опубликованный на форуме сайта:
1.
SELECT
Country,
AVG
(
Wght
)
Wght, ISNULL
(
AVG
(
Wght
)
,
0
)
2.
FROM
(
SELECT
DISTINCT
Country, name, ship,
3.
CASE
WHEN
(
Outcomes.Ship
IS
NULL
AND
4. Ships.Class
IS
NULL
)
5.
THEN
NULL
6.
ELSE
POWER
(
Bore,
3
)
/
2 7.
END
Wght
8.
FROM
Outcomes
FULL
OUTER
JOIN
9. Ships
ON
Outcomes.Ship = Ships.Name
RIGHT
OUTER
JOIN

10.
Classes
ON
Outcomes.Ship = Classes.Class
OR
11.
Ships.Class = Classes.Class
12.
)
s
13.
GROUP
BY
Country;
Это решение выполнено иначе. Здесь есть группировка по странам, полное внешнее соединение и использование нестандартных функций (SQL Server):
ISNULL(var, sub) — возвращает вместо var значение sub, если var есть NULL;
POWER(var, N) — возвращает значение var в степени N (возведение в степень).
Несмотря на явные отличия, этот запрос содержит ту же ошибку, что и предыдущий, то есть один и тот же корабль учитывается неоднократно, если он принимал участие в нескольких сражениях. Попробуйте исправить эту ошибку, не меняя логику самого решения запроса.
Ниже представлены три решения, содержащие разные ошибки, то есть для каждого из этих решений существует такой вариант данных, на котором данное решение дает неповторяющийся в других рассмотренных здесь решениях результат. Поэтому остановимся, чтобы не повторяться, только на этих «уникальных» для каждого решения ошибках.
Решение 3.14.3
1.
SELECT
country,
AVG
(
bore*bore*bore/
2
)
2.
FROM
Ships s
FULL
JOIN
3. Outcomes o
ON
s.name = o.ship
LEFT
JOIN
4. Classes c
ON
c.class = ISNULL
(
s.class, o.ship
)
5.
WHERE
c.class
IS
NOT
NULL
6.
GROUP
BY
country;
Ошибка заключается в соединении
1. Ships s
FULL
JOIN
Outcomes o
ON
s.name = o.ship

Она проявится в том случае, если какой-либо корабль участвовал в нескольких сражениях, поскольку тогда он будет учтен несколько раз в результирующем наборе. При этом мы не утверждаем, что здесь вообще нельзя использовать полное внешнее соединение. Конечно, нет. Однако нужно позаботиться об исключении дубликатов кораблей.
Решение 3.14.4
1.
SELECT
country,
AVG
(
bore*bore*bore/
2
)
2.
FROM
(
SELECT
country, bore, name
3.
FROM
Classes
LEFT
JOIN
4. Ships
ON
Ships.class = Classes.class
5.
UNION
6.
SELECT
DISTINCT
country, bore, ship
7.
FROM
Classes C
LEFT
JOIN
8. Outcomes O
ON
O.ship = C.class
9.
WHERE
NOT
EXISTS
(
SELECT
name
10.
FROM
SHips
11.
WHERE
name = O.ship
)
AND
12.
NOT
(
ship
IS
NULL
)
13.
)
ABC
14.
GROUP
BY
country;
Рассмотрим ситуацию, когда есть класс (допустим class_1, калибр орудий
12), но нет кораблей этого класса в базе данных, и есть класс (class_2, калибр орудий 14), у которого в базе данных есть только головной корабль, упомянутый в таблице Outcomes. При этом оба класса принадлежат одной стране, скажем, country_1. Тогда первый запрос в объединении, если добавить для наглядности столбец class, в предложении FROM даст:
country
bore
Name
class
country_1 12
NULL class_1
country_1 14
NULL class_2

в то время как из второго запроса получим правильный результат:
country
bore
Name
class
country_1 14 class_2 class_2
Как видно, мы учтем в результирующем наборе две лишних строки.
Решение 3.14.5
1.
SELECT
Country,
AVG
(
bore*bore*bore
)
/
2 2.
FROM
(
SELECT
c.country, bore
3.
FROM
Classes C,
4. Ships S
5.
WHERE
S.class = C.Class
AND
6.
NOT
bore
IS
NULL
7.
UNION
ALL
8.
SELECT
country, bore
9.
FROM
Classes C,
10.
OutComes O
11.
WHERE
O.Ship = C.Class
AND
12.
NOT
EXISTS
(
SELECT
1 13.
FROM
Ships S
14.
WHERE
s.Name = O.Ship
15.
)
AND
16.
NOT
bore
IS
NULL
17.
GROUP
BY
country, bore
18.
)
AS
Q1 19.
GROUP
BY
country;
В подзапросе объединяются двухатрибутные отношения {страна, калибр}.
Предикат второго запроса:
1.
NOT
EXISTS
(
SELECT
1 2.
FROM
Ships S

3.
WHERE
s.Name = O.Ship
4.
)
исключает возможность неоднократного учета корабля, если он присутствует в обеих таблицах — Ships и Outcomes, — что как бы оправдывает использование для объединения оператора UNION ALL. Дубликаты же в таблице Outcomes, которые могут появиться в случае участия корабля в нескольких сражениях, устраняются, по мнению автора решения, группировкой по стране и калибру.
Однако если у страны имеется несколько классов кораблей, имеющих на вооружении орудия одинакового калибра, то возможна ситуация, когда головные корабли этих классов будут присутствовать в таблице Outcomes. В результате вместо нескольких таких кораблей учтен будет только один, что и делает данное решение ошибочным.
Упражнение 37
Найдите классы, в которые входит только
один корабль из базы данных (учесть также
корабли в Outcomes).
Решение 3.2.1
Вот один из запросов, которые отвергает система проверки:
1.
SELECT
class
2.
FROM
Ships
3.
GROUP
BY
class
4.
HAVING
COUNT
(
name
)
=
1 5.
UNION
6.
SELECT
class
7.
FROM
Classes c, Outcomes o
8.
WHERE
c.class = o.ship
AND
9.
NOT
EXISTS
(
SELECT
'x'
10.
FROM
Ships s
11.
WHERE
o.ship = s.class
12.
)
;

Первый запрос в объединении подсчитывает корабли каждого класса из таблицы Ships, оставляя в результирующем наборе только те классы, которые имеют в этой таблице только один корабль. Второй запрос определяет классы, у которых головной корабль находится в таблице Outcomes при условии, что кораблей такого класса нет в таблице Ships.
Рассмотрим следующий пример данных, для которых этот запрос будет давать неправильный результат.
Каждый, кто решал задачи по схеме данных «Корабли», знает, что такое
«Бисмарк» (Bismarck). Это головной корабль, которого нет в таблице Ships.
Теперь представим себе, что другой корабль класса «Бисмарк» имеется в таблице Ships, скажем, «Тирпиц» (Tirpitz).
Тогда первый запрос вернет класс «Бисмарк», так как в таблице Ships имеется один корабль этого класса. Второй запрос класс «Бисмарк» не вернет, так как предикат:
1.
NOT
EXISTS
(
SELECT
'x'
2.
FROM
Ships s
3.
WHERE
o.ship = s.class
4.
)
для корабля «Бисмарк» в таблице Outcomes будет оценен как FALSE. В результате объединения этих запросов получим класс «Бисмарк» в выходных данных всего запроса.
Всякому, кто внимательно следил за ходом рассуждений, понятно, что в базе данных имеется два корабля класса «Бисмарк». То есть этот класс не должен присутствовать в результатах выполнения запроса.
Чтобы проверить это, добавьте в основную базу данных следующую строку:
1.
INSERT
INTO
Ships
VALUES
(
'Tirpitz'
,
'Bismark'
,
1940
)
;
Совет:
Все основные базы данных можно загрузить с http://www.sql-
ex.ru/db_script_download.php.

Решение 3.2.2
Следующее решение было построено одним из посетителей сайта после получения приведенных выше объяснений. Оно также дает правильный результат на основной базе данных.
1.
SELECT
class
2.
FROM
Ships sh
3.
WHERE
NOT
EXISTS
(
SELECT
ship
4.
FROM
Outcomes
5.
WHERE
ship = sh.class
6.
)
7.
GROUP
BY
class
8.
HAVING
COUNT
(
*
)
=
1 9.
UNION
10.
SELECT
ship
11.
FROM
Outcomes s
12.
WHERE
EXISTS
(
SELECT
class
13.
FROM
Classes
14.
WHERE
class = s.ship
15.
)
AND
16.
NOT
EXISTS
(
SELECT
class
17.
FROM
Ships
18.
WHERE
class = s.ship
19.
)
;
Здесь объединяются два запроса. Второй запрос отбирает из таблицы
Outcomes головные корабли при условии, что в таблице Ships нет других кораблей класса данного головного корабля.
В первом же запросе выбираются все корабли из таблицы Ships кроме тех, для которых в таблице Outcomes имеется головной корабль. Далее выполняется группировка по классу и отфильтровываются (