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

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

Решение 3.5.2

1.
SELECT
name
2.
FROM
Ships
3.
WHERE
launched <
1941 4.
UNION
5.
SELECT
ship
6.
FROM
Outcomes, Battles
7.
WHERE
name = battle
AND
8. DATEPART
(
YEAR, date
)
<
1941 9.
UNION
10.
SELECT
ship
11.
FROM
Outcomes
12.
WHERE
ship
IN
(
SELECT
class
13.
FROM
Ships
14.
WHERE
launched <
1941 15.
)
;
Решение 3.5.2 учитывает:
1. Корабли из таблицы Ships c известным годом спуска на воду до 1941 года.
2. Корабли, которые принимали участие в сражениях до 1941 года
(естественно, такие корабли должны были быть спущены на воду до сражения, в котором они принимали участие).
3. Корабли из таблицы Outcomes, имена которых совпадает с именем класса какого-нибудь корабля из Ships, спущенного на воду до 1941 года.
Следует заметить, что возможные дубликаты устраняются использованием объединения посредством UNION.
Последний вариант учитывает и те случаи, когда головной корабль принимал участие в сражениях только после 1941 года, так как более ранние сражения учтены предыдущим запросом. Осталось выяснить, зачем это нужно. Ответ на этот вопрос следует искать в «висящих» головных кораблях. Итак, корабль из
Outcomes, имя которого совпадает с именем одного из классов (головной корабль), отсутствует в таблице Ships или присутствует, но с неизвестным годом спуска на воду. Пусть в таблице Ships есть корабль того же класса с известным годом спуска на воду. Если этот год окажется меньше 1941, то головной корабль следует включать в результирующий набор наряду с упомянутым кораблем. Это следует из того факта, что головной корабль — это

первый корабль в своем классе и, следовательно, должен был быть спущен на воду не позднее любого другого корабля своего класса.
Примечание:
Во втором из объединяемых запросов решения 3.5.2 используется
специфическая для
SQL
Server функция DATEPART. Она необходима, так
как из даты сражения (поле date имеет темпоральный тип данных —
datetime) нужно извлечь год сражения; в противном случае предикат.
1. date <
1941
будет давать сравнение с датой 1905 года, в результате преобразования целого числа к типу дата-время (как число дней прошедших от начала 1900 года, что является точкой отсчета дат для настроек по умолчанию в SQL Server 2000). В этом легко убедиться, если выполнить запрос:
1.
SELECT
CAST
(
1941
AS
DATETIME
)
; результатом которого будет
1.
1905-04-26 00
:
00
:
00.000
Чтобы оставаться в рамках стандарта, можно было бы использовать следующий предикат:
1. date <
'1941'
Тогда неявное преобразование типа дало бы нужный результат. Опять проверим:

1.
SELECT
CAST
(
'1941'
AS
DATETIME
)
; что дает
1.
1941-01-01 00
:
00
:
00.000
Естественно, правильным будет и предикат, содержащий полную дату начала
1941 года (1 января):
1. date <
'19410101'
Однако вернемся к нашему решению. Оно неверно. Как было сказано, здесь не учитывается ситуация, когда головной корабль присутствует только в Ships, и год его спуска на воду неизвестен. Учесть эту ситуацию можно, добавив к объединению еще один запрос.
Решение 3.5.3
1.
SELECT
name
2.
FROM
Ships
3.
WHERE
launched <
1941 4.
UNION
5.
SELECT
ship
6.
FROM
Outcomes, Battles
7.
WHERE
name = battle
AND
8. DATEPART
(
YEAR, date
)
<
1941 9.
UNION
10.
SELECT
ship
11.
FROM
Outcomes
12.
WHERE
ship
IN
(
SELECT
class
13.
FROM
Ships
14.
WHERE
launched <
1941 15.
)
16.
UNION
17.
SELECT
name
18.
FROM
Ships

19.
WHERE
name
IN
(
SELECT
class
20.
FROM
Ships
21.
WHERE
launched <
1941 22.
)
;
Сколько интересной информации можно извлечь из этой задачи, всего лишь поменяв год!
Однако и это еще не все. Предлагаем самостоятельно найти дополнительные корабли, отвечающие условиям задачи. Проверить себя вы сможете, заглянув в
ПиР
Упражнение 124
Среди пассажиров, которые пользовались
услугами не менее двух авиакомпаний,
найти тех, кто совершил одинаковое
количество полётов самолетами каждой
из этих авиакомпаний. Вывести имена
таких пассажиров.
Эта задача порождает массу ошибочных решений, которые я разделяю на две группы. К первой группе относятся решения, связанные с неверным прочтением формулировки.
Например, пытаются найти двух пассажиров, которые летали бы одинаково двумя или большим числом компаний.
Поясню, что рассматривать следует отдельного пассажира и подсчитываеть число полетов, которое он сделал каждой из авиакомпаний, которыми летал.
Что дальше? Рассмотрим теперь пример из второй группы ошибочных решений:

1.
SELECT
DISTINCT
name
2.
FROM
(
SELECT
id_psg, id_comp,
COUNT
(
pt.trip_no
)
AS
CNT
3.
FROM
pass_in_trip pt
JOIN
trip t
ON
pt.trip_no=t.trip_no
4.
GROUP
BY
id_comp,id_psg
)
a,
5.
(
SELECT
id_psg, id_comp,
COUNT
(
pt.trip_no
)
AS
CNT
6.
FROM
pass_in_trip pt
JOIN
trip t
ON
pt.trip_no=t.trip_no
7.
GROUP
BY
id_comp,id_psg
)
b,
8. passenger p
9.
WHERE
a.id_psg=b.id_psg
AND
a.id_comp<>b.id_comp
AND
a.cnt=b.cnt
10.
AND
p.id_psg=b.id_psg;
Сразу отметим ошибочное DISTINCT name, которое устраняет возможных однофамильцев. Однако не это здесь главное. В предложении FROM соединяются два одинаковых запроса
1.
SELECT
id_psg, id_comp,
COUNT
(
pt.trip_no
)
AS
CNT
2.
FROM
pass_in_trip pt
JOIN
trip t
ON
pt.trip_no=t.trip_no
3.
GROUP
BY
id_comp,id_psg; которые, как и сказано выше, подсчитывают для каждого пассажира число полетов, которое он совершил самолетами каждой из компаний.
Соединяются эти запросы по следующим условиям:

пассажир тот же самый;

компании разные;

число полетов совпадает.
Итак, если пассажир совершил, скажем, компанией Aeroflot 3 полета, и также
3 полета он совершил самолетами компании Don_avia, то такой пассажир удовлетворяет условиям соединения и будет выведен в результатах запроса.
Если пассажир летал всего двумя компаниями, то это - правильный результат.
А если компании три?
Если в результате рассмотренного выше подзапроса мы получим

Bruce Willis
Don_avia 2
Bruce Willis
Aeroflot 2
Bruce Willis Dale_avia 1 то пассажир Bruce Willis не отвечает условиям задачи, хотя рассматриваемый запрос выведет его, поскольку в запросе будут соединены первые две строки.
Итак, число полетов пассажира каждой из компаний, которыми он летал, должно находиться в пропорции 1:1:...1.
База данных
«Окраска»
Схема базы данных состоит из трех таблиц:
1. utQ
(
Q_ID int, Q_NAME varchar
(
35
))
;
2. utV
(
V_ID int, V_NAME varchar
(
35
)
, V_COLOR char
(
1
))
;
3. utB
(
B_Q_ID int, B_V_ID int, B_VOL tinyint, B_DATETIME datetime
)
Таблица utQ содержит идентификатор и название квадрата, цвет которого первоначально черный.
Таблица utV содержит идентификатор, название и цвет баллончика с краской.
Таблица utB содержит информацию об окраске квадрата баллончиком: идентификатор квадрата, идентификатор баллончика, количество краски и время окраски.
При этом следует иметь в виду, что:


баллончик с краской может быть одного из трех цветов - красный
V_COLOR='R', зеленый V_COLOR='G', синий V_COLOR='B'
(латинские буквы).

объем баллончика равен 255 и первоначально он полный;

цвет квадрата определяется по правилу RGB, т.е. R=0,G=0,B=0 - черный, R=255, G=255, B=255 - белый;

запись в таблице закрасок utB уменьшает количество краски в баллончике на величину B_VOL и соответственно увеличивает количество краски в квадрате на эту же величину;

значение 0 < B_VOL <= 255;

количество краски одного цвета в квадрате не превышает 255, а количество краски в баллончике не может быть меньше нуля;

время окраски B_DATETIME дано с точностью до секунды, т.е. не содержит миллисекунд.
Рис. Схема базы данных "Окраска"
Некоторые пояснения к схеме.
Идентификаторы черных квадратов отсутствуют в таблице utB. Это следует из того, что B_VOL не допускает NULL-значений и строго больше нуля.
Таким образом, каждая запись в таблице utB описывает факт окраски одного квадрата одним баллоном, черный же квадрат (R=0,G=0,B=0) не подвергался окраске вовсе.
Ограничения схемы допускают одновременную окраску одного квадрата несколькими баллонами, как и одновременную окраску одним баллоном

нескольких квадратов. Однако запрещена одновременная окраска одного квадрата одним и тем же баллоном.
Ошибки в
задачах DML
В этой главе я разберу некоторые ошибки, которые допускаются в задачах DML. Номера задач, как и их формулировки, не приводятся, т.к. пока задачи DML не разделяются на учебные и рейтинговые, ввиду их малочисленности.
Проблема. Требуется определить
максимальную скорость имеющихся CD-ROM.
Ошибка состоит в использовании
1.
SELECT
MAX
(
cd
)
FROM
Дело в том, что скорость CD-ROM хранится в текстовом представлении
(например, '12x'). При сравнении текстовых констант '4x' оказывается больше, чем '24x'. А если потребуется посчитать СРЕДНЮЮ скорость?!
Проблема. Требуется округлить среднее значение года спуска на
воду кораблей.
Типичной ошибкой здесь является, например, такой прием:
1. round
(
AVG
(
launched
)
,
0
)
Дело в том, что среднее значение приводится к типу аргумента. В данном случае оно приводится к целому числу, т.к. столбец launched имеет тип int. При этом SQL Server не округляет результат, а отбрасывает дробную часть. Это легко проверить, выполнив запрос:

1.
SELECT
AVG
(
launched
)
FROM
(
VALUES
(
9
)
,
(
10
)
,
(
10
))
X
(
launched
)
;
Математически 29/3 - это почти 10. Однако получаем 9. В результате функция ROUND здесь совершенно лишняя, т.к. округлять уже нечего.
Так как же здесь следует поступить? Очень просто. Нужно привести аргумент к вещественному типу, по нему считать среднее, а уже затем округлять.
Упражнение 128
Определить лидера по сумме выплат в
соревновании между каждой парой
пунктов с одинаковыми номерами из двух
разных таблиц - outcome и outcome_o - на
каждый день, когда осуществлялся прием
вторсырья хотя бы на одном из них.
Вывод: Номер пункта, дата, текст:-
"once a day", если сумма выплат больше у
фирмы с отчетностью один раз в день;-
"more than once a day", если - у фирмы с
отчетностью несколько раз в день;-
"both", если сумма выплат одинакова.
Основная сложность в понимании условия этой задачи заключается в том, как выполнять сравнение, если в некий день в одной таблице есть строка ( строки), а в другой - нет.
Как написано в описании предметной области, таблицы с суффиксом "_o" и без него - это разные схемы. Т.е. мы можем считать, что они описывают деятельность разных фирм.
Поскольку сравниваются пункты с одинаковыми номерами, то из результата следует исключить вариант, когда в одной таблице есть

пункт с номером, совсем отсутствующим в другой таблице. Поскольку пункта с таким номером на одной из фирм не существует, то мы не можем сравнивать что-то с тем, чего нет.
Например, если в забеге выступал один спортсмен, нельзя утверждать, что он был лучше второго.
Совершенно другую ситуацию мы имеем, когда в определенный день в одной из таблиц строка для некоторого пункта отсутствует, но сам пункт существует (т.е. имеются соответствующие ему записи за другие дни). Такую ситуацию мы можем трактовать так, что пункт просто не работал или работал вхолостую. В этом случае победа аналогичному пункту другой фирмы присуждается закономерно. Возвращаясь к примеру о бегунах, мы можем утверждать, что спортсмен, пришедший к финишу первым был лучше того, который сошёл с дистанции.
Если при проверке на сайте вашего решения вы получили сообщение о большем числе строк, которые вернул ваш запрос, то причина может заключаться как раз в сравнении с несуществующим пунктом.
Упражнение (-2)
Для каждой страны определить год, когда на воду было спущено максимальное количество ее кораблей. В случае, если окажется несколько таких лет, взять минимальный из них. Вывод: страна, количество кораблей, год
Решение 3.6.1. Вот характерное решение для начинающих:
1.
SELECT
country,
MAX
(
x
)
,
MIN
(
launched
)

2.
FROM
(
SELECT
country,
COUNT
(
*
)
x , launched
3.
FROM
Ships b, Classes a
4.
WHERE
a.class = b.class
5.
GROUP
BY
country, launched
6.
)
s
7.
WHERE
launched = ANY
(
SELECT
MIN
(
launched
)
8.
FROM
Ships bb, Classes aa
9.
WHERE
bb.class = aa.class
10.
GROUP
BY
country, launched
11.
)
12.
GROUP
BY
country;
Подзапрос в предложении FROM определяет количество строк для каждой уникальной пары значений {страна, год спуска на воду}. На языке предметной области это означает, что определяется число кораблей, спущенных на воду каждой страной в каждом году. Пусть результатом выполнения подзапроса s будет следующая таблица:
country
x
launched
Gt.Britain 6 1916
Gt.Britain 1 1917
Japan
1 1913
Japan
2 1914
Japan
2 1915
Japan
1 1916
Japan
1 1941
Japan
1 1942
USA
1 1920

USA
1 1921
USA
3 1941
USA
2 1943
USA
2 1944
Далее (в предложении WHERE) отбираются только те строки, у которых год спуска на воду совпадает хотя бы с одним годом, определяемым следующим подзапросом:
1.
SELECT
MIN
(
launched
)
2.
FROM
Ships bb, Classes aa
3.
WHERE
bb.class = aa.class
4.
GROUP
BY
country, launched;
Что же дает этот подзапрос? Все годы из каждой строки приведенной выше таблицы, так как группировка опять таки выполняется по стране и году. В результате используемый подзапрос никак не ограничивает выборку, и, следовательно, является ненужным.
Видимо, подразумевалось удовлетворение условия задачи по минимальному году. Однако речь идет о минимальном годе из тех лет, когда на воду было спущено максимальное количество кораблей данной страны.
Чтобы исправить эту ошибку, недостаточно убрать из группировки столбец launched:
1.
SELECT
MIN
(
launched
)
2.
FROM
Ships aa, Classes bb
3.
WHERE
bb.class = aa.class
4.
GROUP
BY
country; в результате чего будут получены минимальные годы по каждой стране:

1916
1913
1920
Тогда строки, удовлетворяющие предикату, будут выглядеть так:
country
x
launched
Gt.Britain 6 1916
Japan
1 1913
Japan
1 1916
USA
1 1920
Таким образом, мы уже потеряли правильные строки для Японии и США.
Следует обратить внимание еще и на то, что мы получили строку для Японии:
Japan 1 1916 только на основании того, что год 1916 совпал с минимальным годом для
США. Дальнейший код не имеет смысла. Однако и там имеется принципиальная ошибка. В основном запросе
1.
SELECT
country,
MAX
(
x
)
,
MIN
(
launched
)
2. …
3.
GROUP
BY
country выполняется группировка по стране с определением двух агрегатных показателей — максимума по количеству кораблей и минимума по году.

Использование в данном случае группировки ошибочно. Действительно, нужные нам строки уже находятся в таблице, получаемой в предложении
FROM (например, строка {Gt.Britain, 6, 1916}). Зачем же здесь группировка, когда требуется лишь критерий, который поставит фильтр, отсекающий лишние строки. В результате же группировки образуется одна строка, содержащая статистические показатели для всей группы. При этом максимум и минимум в общем случае достигаются в разных строках группы. Это хорошо видно из таблицы на примере кораблей США, когда минимальному году отвечает далеко не максимальное значение (x=1), а максимальное значение (x=3) достигается совсем в другом году (1941). Поэтому такая группировка может дать правильный результат (в смысле условия задачи) только в том случае, если все значения x для страны совпадают.
Все в этом решении поставлено с ног на голову. Тем не менее, выяснив причины ошибок и заблуждений, попытаемся исправить его без радикальной переработки.
Чтобы все же связать год со страной, можно использовать коррелирующий подзапрос в предложении WHERE (AND aa.country = s.country):
1.
WHERE
launched = ANY
(
SELECT
MIN
(
launched
)
2.
FROM
Ships bb, Classes aa
3.
WHERE
aa.class = bb.class
AND
4. aa.country = s.country
5.
GROUP
BY
country
6.
)
Это правильно, но пока ничего не меняет, разве что исключит неправильную строку:
Japan 1 1916
Чтобы двигаться дальше, нужно вычислять минимальный год среди лет с максимальным количеством кораблей для каждой страны. Здесь первичным является максимальное количество кораблей. Ведь выбирая лишь

минимальный год, мы можем потерять правильные строки. Поэтому в предикате нужно оценивать не год, а количество кораблей:
1.
WHERE
x >=
ALL
(
SELECT
COUNT
(
launched
)
2.
FROM
Ships bb, Classes aa
3.
WHERE
bb.class = aa.class
AND
4. s.country=aa.country
5.
GROUP
BY
country, launched
6.
)
Обратите внимание на предикат >=