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

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

HAVING COUNT(ship) > 3. Использование правильного предиката с условием >=3 делало запрос неверным, каким он и является. Подгонка решения позволила обнаружить огрех в проверке, который и был устранен.
Итак, запрос соединяет классы с кораблями из таблицы Ships, чтобы определить страну корабля. Левое соединение (LEFT JOIN) используется для того, чтобы не потерять класс, если кораблей этого класса нет в таблице Ships.
Такой (и не только) класс понадобится для того, чтобы учесть головные корабли из таблицы Outcomes, что и делается в следующем (внутреннем) соединении. Предикат этого соединения
1.
ON
o.ship = s.name
OR
c.class = o.ship сформирует строку, в столбце ship которой будет находиться имя корабля, принимавшего участие в сражениях, если его имя совпадает с именем корабля известного класса в таблице Ships или если его имя совпадает с именем класса
(головной корабль). Если корабль не принимал участия в сражении, то значением в столбце ship будет NULL. Затем выполняется группировка по паре атрибутов {battle, country} с предложением HAVING COUNT(ship) >= 3, что позволяет отобрать только те страны, которые участвовали в битве более чем двумя кораблями.
Заметим, что функция COUNT корректно обработает NULL-значения в столбце ship.
Внимание:
О разнице в использовании COUNT(*) и COUNT(имя столбца) можно
почитать в
пункте 5.5.
В этом «или» предиката (1) и заключается основная ошибка этого запроса.
Если один и тот же головной корабль имеется и в таблице Outcomes, и в таблице Ships, то он будет учтен дважды для одной и той же битвы. Это можно увидеть из следующего запроса:
1.
SELECT
battle, country, ship,
COUNT
(
*
)
qty
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, ship;

Приведем здесь только одну неправильную строку результата:
Battle
country
ship
qty
Surigao Strait USA
Tennessee 2
Явная ошибка, так как один и тот же корабль не может дважды упоминаться для одной и той же битвы (Surigao Strait), что запрещено первичным ключом на таблице Outcomes.
Отметим, что рассматриваемый запрос, как и решения 3.7.2 и 3.7.1, содержит еще одну ошибку, встречающуюся настолько часто, что она даже описана в
FAQ на сайте. Эта ошибка заключается в том, что поскольку группировка выполняется по паре атрибутов {battle, country}, то битва будет выводиться неоднократно, если в ней принимало участие минимум по 3 корабля от каждой участвовавшей в битве страны.
Остается один вопрос. Почему же при трех отмеченных ошибках (>3 вместо
>=3, ошибочное соединение и возможное появление дубликатов) запрос принимался системой?
Попробуем разобраться. В основной базе не было ни одной битвы, для которой бы выполнялось условие задачи. Правильное решение показывало пустой набор записей. Поэтому ошибочное увеличение числа кораблей не работало с правильным критерием (>=3), так как запрос выдавал битву Surigao
Strait, хотя в ней реально принимало участие 2 корабля из USA. А вот условие
>3 опять давало пустой набор.
В проверочной базе для блокировки решения с неисключенными дубликатами для одной битвы было два набора по 3 и более корабля разных стран. При этом в одном наборе головной корабль присутствовал в обеих таблицах (Outcomes и Ships). Для этого набора рассматриваемым запросом ошибочно считалось 4 корабля, а для второго правильно — 3. Поэтому условие в предикате HAVING – > 3 и давало только одну битву, разрешая самым неожиданным образом проблему с дубликатами.
Мир полон неожиданностей; чем больше делается ошибок, тем больше вероятность совпадения результатов.

Пустой набор результата решения этой задачи на основной базе неоднократно вызывал нарекания. Поэтому автор, попутно блокируя рассмотренное неверное решение, добавил данных и в основную базу.
Решение 3.7.4
1.
SELECT
DISTINCT
battle
2.
FROM
(
SELECT
battle, country
3.
FROM
(
SELECT
battle, country
4.
FROM
Outcomes
INNER
JOIN
5. Classes
ON
ship = class
6.
UNION
7.
SELECT
battle, country
8.
FROM
Outcomes o
INNER
JOIN
9. Ships s
ON
o.ship = s.name
INNER
JOIN
10.
Classes c
ON
s.class = c.class
11.
)
x
12.
GROUP
BY
battle, country
13.
HAVING
COUNT
(
*
)
>
2 14.
)
y;
Во внутреннем подзапросе объединяются два запроса. В первом из них
1.
SELECT
battle, country
2.
FROM
Outcomes
INNER
JOIN
3. Classes
ON
ship = class; определяется страна и сражения, в которых принимали участие головные корабли из этой страны. Во втором запросе
1.
SELECT
battle, country
2.
FROM
Outcomes o
INNER
JOIN
3. Ships s
ON
o.ship = s.name
INNER
JOIN
4.
Classes c
ON
s.class = c.class;

определяется страна и сражения для тех кораблей, которые имеются в таблице Ships. Соединение с таблицей Classes необходимо, чтобы узнать страну, владеющую кораблем.
Использование для объединения предложения UNION устраняет дубликаты.
С одной стороны, это кажется правильным, так как головной корабль может находиться как в таблице Outcomes, так и в таблице Ships. С другой стороны, после удаления дубликатов в результирующем наборе останется только одна уникальная пара {сражение, страна}, а это означает, что для любого числа кораблей из одной страны, останется лишь одна строка для каждого из сражений. В результате последующая группировка попросту излишней, как предложение HAVING.
Первое, что приходит в голову, — написать UNION ALL вместо UNION, то есть учесть все дубликаты. Но, как уже понятно из предыдущего обсуждения, тогда для одного головного корабля, участвующего в некотором сражении, мы получим две строки, если этот корабль присутствует еще и в таблице Ships.
Как поступить? Автор предлагает два подхода. При первом подходе мы оставляем UNION, но подсчитываем не страны, а корабли. Тогда устранение дубликатов будет правильным. При втором подходе автор предполагает использование UNION ALL, но тогда нужно в одном из объединяемых запросов проверять, чтобы учитываемый корабль не присутствовал в другой таблице, тем самым, подсчитывая его один раз.
Какой из способов предпочесть, зависит не только от наших предпочтений, но и от стоимости плана выполнения запроса. Предлагаем самостоятельно оценить планы, предварительно решив задачу двумя описанными способами.
Упражнение 71
Найти тех производителей ПК, все модели
ПК которых имеются в таблице PC
Вот типичный неверный запрос
1.
SELECT
DISTINCT
maker
2.
FROM
Product
3.
WHERE
model
IN
(
SELECT
model
FROM
PC
)
;

который сопровождается следующим вопросом: "Производитель E с моделью 1260 присутствует и в таблице PC, а правильный результат его не содержит. Почему?"
Ключевым моментом формулировки является слово "ВСЕ". Давайте посмотрим на модели производителя E. Модели ПК, которые выпускает производитель E, дает следующий запрос:
1.
SELECT
model
2.
FROM
Product
3.
WHERE
maker=
'E'
AND
type=
'PC'
;
Результат:
model
1260
2111
2112
А теперь проверим, какие из этих моделей имеются в таблице PC:
1.
SELECT
DISTINCT
model
2.
FROM
PC
3.
WHERE
model
IN
(
1260
,
2111
,
2112
)
;
Оказывается, что из трех моделей только одна - 1260 - имеется в таблице PC.
По условию же задачи там должны находиться ВСЕ три модели производителя
E.

Собственно, решение этой задачи сводится к операции реляционного деления, только для каждого производителя у нас свой делитель (его модели).
В упрощенном виде операцию реляционного деления можно записать так:
1. A
(
a, b
)
DIVIDEBY B
(
b
)
где делимое (А) представляет собой бинарное (двухатрибутное) отношение, а делитель (B) - унарное. Результатом являются такие значения из первого атрибута отношения A, для каждого из которых значения второго атрибута содержат ВСЕ значения делителя.
Операция реляционного деления не является примитивной. Это означает, что эту операцию можно выразить через другие (примитивные) реляционные операции. Избыточность реляционной алгебры, предложенной Коддом, обусловлена ориентаций на практическое применение. Язык
SQL
тоже избыточен, в чем нас убеждает каждая задача, которую можно решить разными способами. Несмотря на это, аналога операции реляционного деления в нем нет.
В заключение приведу представление реляционного деления, выраженного через другие операции.
1. A DIVIDEBY B :=
2. A
[
a
]
EXCEPT
((
A
[
a
]
TIMES B
)
EXCEPT
A
)
[
a
]
Здесь A[a] означает проекцию отношения A на атрибут a; TIMES - декартово произведение. "Подстрочный" перевод на язык SQL может выглядеть следующим образом:
1.
SELECT
a
FROM
A
2.
EXCEPT
3.
SELECT
a
FROM
(
4.
SELECT
A.a, B.b
FROM
A, B
5.
EXCEPT
6.
SELECT
a,b
FROM
A
)
X;

Не следует использовать этот подстрочник как руководство к действию; есть более простые способы решить задачу. Впрочем, я не настаиваю.
Упражнение 77
Определить дни, когда было выполнено
максимальное число рейсов из Ростова
('Rostov'). Вывод: число рейсов, дата.
Мне казалось, что формулировка предельно понятна. Тем более, что когда эта задача еще находилась на втором рейтинговом этапе, она не вызывала никаких вопросов. Однако сказалась разница в классе :) . Отвечать на аналогичные вопросы приходилось столь часто, что мне пришлось написать это объяснение.
Вот типичный пример неправильного запроса:
1.
SELECT
MAX
(
superden.qty
)
, superden.date
2.
FROM
3.
(
SELECT
COUNT
(
den.trip_no
)
AS
qty, den.date
4.
FROM
5.
(
SELECT
DISTINCT
trip_no, date
FROM
Pass_in_trip
)
AS
den,
6. Trip
WHERE
trip.trip_no=den.trip_no
AND
7. trip.town_from=
'Rostov'
8.
GROUP
BY
den.date
)
AS
superden
9.
GROUP
BY
superden.date;
Подзапрос
1.
SELECT
DISTINCT
trip_no, date
FROM
Pass_in_trip;

определяет совершенные рейсы. DISTINCT здесь вполне уместен, т.к. для пассажиров, летевших в одном самолете, комбинация {trip_no, date} совпадает. Подзапрос соединяется с таблицей Trip, чтобы отобрать только ростовские рейсы: trip.town_from='Rostov'.
Группировка по дате позволяет подсчитать распределение количества ростовских рейсов по дням. Пока все верно, но последний шаг лишён смысла.
Зачем еще одна группировка по дате, если все уже сгруппировано, т.е. для каждой даты в результирующем наборе и так есть только одна строка?
Кажется, что таким образом автор решения пытался найти максимум, но получил тот же самый набор. Пусть распределение количества по датам будет таким:
2007-08-19 2
2007-08-20 2
2007-08-21 3
По условию задачи мы должны получить лишь одну строку:
2007-08-21 3 т.к. максимальное число полётов – 3 – достигается 2007-08-21, но в результате последней группировки по дате мы получим те же самые 3 строки.
Надеюсь, что теперь понятно, как следует решать эту задачу, и поддержке сайта не придется больше отвечать на письма по этому поводу.
Упражнение 78
Для каждого сражения определить первый
и последний день месяца, в котором оно

состоялось.Вывод: сражение, первый день
месяца, последний день месяца.
Характерной ошибкой в этой задаче является неправильное определение високосного года.
Следует иметь в виду, что если год делится нацело на 100, но при этом не делится на 400, то такой год не является високосным.
Следовательно, високосным, например, не является 1900 год, в то время как 2000 год – високосный.
База данных
«Аэрофлот»
Схема БД состоит из четырех таблиц:
1. Company
(
ID_comp, name
)
2. Trip
(
trip_no,
ID_comp, plane, town_from, town_to, time_out, time_in
)
3. Passenger
(
ID_psg, name
)
4. Pass_in_trip
(
trip_no, date, ID_psg, place
)
Таблица Company содержит идентификатор и название компании, осуществляющей перевозку пассажиров. Таблица Trip содержит информацию о рейсах: номер рейса, идентификатор компании, тип самолета, город отправления, город прибытия, время отправления и время прибытия. Таблица
Passenger содержит идентификатор и имя пассажира. Таблица Pass_in_trip содержит информацию о полетах: номер рейса, дата вылета (день), идентификатор пассажира и место, на котором он сидел во время полета. При этом следует иметь в виду, что
- рейсы выполняются ежедневно, а длительность полета любого рейса менее суток;
- время и дата учитывается относительно одного часового пояса;

- время отправления и прибытия указывается с точностью до минуты;
- среди пассажиров могут быть однофамильцы (одинаковые значения поля name, например, Bruce Willis);
- номер места в салоне – это число с буквой; число определяет номер ряда, буква (a – d) – место в ряду слева направо в алфавитном порядке;
- связи и ограничения показаны на схеме данных.
Рис. Схема базы данных «Аэропорт»
Нередко задают такой вопрос: "Почему в таблице Trip днём отправления/прибытия является 1900-01-01?"
В таблице Trip содержится только время отправления/прибытия, поскольку, согласно описанию предметной области, рейсы выполняются ежедневно.
Присутствие даты объясняется тем, что в ранних версиях
SQL
Server не было отдельных типов данных для даты (DATE) и времени (TIME), которые появились только в версии 2008 года. Поэтому использовался тип DATETIME, соответствующий стандартному
TIMESTAMP, включающему все составляющие метки времени.
Что же касается конкретно даты 1900-01-01, то эта дата соответствует началу отсчета времени, т.е. нулю. Выполните запрос:
1.
SELECT
CAST
(
0
AS
DATETIME
)
;

и вы получите
1900-01-01 00:00:00.000
Т.е. если ввести в столбец типа DATETIME только время, то датой этого значения станет 1900-01-01. В этом можно убедиться, явно приведя значение времени к типу DATETIME, например:
1.
SELECT
CAST
(
'13:44:00'
AS
DATETIME
)
;
1900-01-01 13:44:00.000
Упражнение 93
Для каждой компании, перевозившей
пассажиров, подсчитать время, которое
провели в полете самолеты с пассажирами.
Вывод: название компании, время в минутах.
Проблемы, возникающие при решении этой задачи, можно проиллюстрировать таким сообщением одного из участников. Вот что он пишет:
"Если выполнить запрос
1.
SELECT
Trip.time_out, Trip.time_in
2.
FROM
Trip
3.
WHERE
Trip.id_comp=
2
;

time_out
time_in
1900-01-01 09:35:00.000 1900-01-01 11:23:00.000
1900-01-01 17:55:00.000 1900-01-01 20:01:00.000
то получается, что компания Аэрофлот (id_comp=2) произвела два полёта,
первый продолжительностью в 1 час 48 минут, второй -
продолжительностью 2 часа 6 минут. Итоговая продолжительность
полётов получается 108 + 126= 234 минуты, а никак не 216 минут, как
указано в "правильном результате".
Непонимание вызвано недостаточным изучением схемы БД и её описанием.
Таблица Trip представляет собой расписание полетов, которые выполняются ежедневно. А вот в таблице Pass_in_trip содержится информация о полетах с пассажирами. Давайте посмотрим, какие рейсы компании с id_comp=2 были выполнены:
1.
SELECT
pt.trip_no, date, time_out, time_in
2.
FROM
pass_in_trip pt
3.
JOIN
4.
(
SELECT
trip_no,time_out,time_in
FROM
trip
WHERE
id_comp=
2
)
t
5.
ON
t.trip_no=pt.trip_no
6.
GROUP
BY
pt.trip_no, date, time_out, time_in;
Вот результат вышеприведенного запроса:
trip_no
date
time_out
time_in
1145
2003-04-05 00:00:00.000 1900-01-01 09:35:00.000 1900-01-01 11:23:00.000
1145
2003-04-25 00:00:00.000 1900-01-01 09:35:00.000 1900-01-01 11:23:00.000

Итак, первый рейс был выполнен дважды, а второй - ни разу, т.е. 108*2 = 216.
Упражнение 121
Найдите названия всех тех кораблей из
базы данных, о которых можно определенно
сказать, что они были спущены на воду до
1941 г.
Несмотря на коэффициент сложности 2, решение этой задачи вызывало, по-видимому, наибольшие затруднения. Причем связано это не со сложностью в построении запроса, а с логикой решения задачи.
Эта задача заменила более простую задачу, которая звучала так: «Найдите названия всех кораблей из базы данных, спущенных на воду до
1918 г». Казалось бы, какая разница? Разве следующее решение не является правильным?
Решение 3.5.1
1.
SELECT
name
AS
shipName
2.
FROM
Ships
3.
WHERE
launched <
1941
;
Нет. Как справедливо было замечено посетителями сайта, здесь никак не учитываются даты сражений. Действительно, если год спуска на воду корабля неизвестен, и при этом он участвовал в сражении, которое произошло до 1941 года, то такой корабль следует включать в результат. В первоначальной формулировке этой задачи, где фигурировал 1918 год, об этом можно было не заботиться, так как формально база данных содержит информацию о сражениях второй мировой войны, которая началась в 1939 году.