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

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

ALL — (больше или равно), который дает нам максимальное значение x. Перепишем весь запрос с учетом сказанного о группировке в основном запросе:
Решение 3.6.2
1.
SELECT
country, x, 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
x >=
ALL
(
SELECT
COUNT
(
launched
)
8.
FROM
Ships bb, Classes aa
9.
WHERE
bb.class = aa.class
AND
10. s.country=aa.country
11.
GROUP
BY
country, launched
12.
)
;
Все? Не совсем. Если максимум для какой-нибудь страны достигается в разные годы, то мы получим по строке на каждый год. Нам же по условиям задачи требуется в таком случае вывести минимальный год. Как было отмечено выше, это как раз тот самый случай, когда группировка приемлема по смыслу (все значения x для страны одинаковы — максимальны):
Решение 3.6.3. Использование для решения задачи соединения вместо коррелирующего подзапроса.

1.
SELECT
a.country, a.numShips
AS
Qty,
MIN
(
launched
)
AS
Year
2.
FROM
(
SELECT
country,
COUNT
(
*
)
AS
numShips, launched
3.
FROM
Classes
INNER
JOIN
4. Ships
ON
Classes.class = Ships.class
5.
GROUP
BY
country, launched
6.
)
AS
a
INNER
JOIN
7.
(
SELECT
a.country,
MAX
(
a.numShips
)
AS
Qty
8.
FROM
(
SELECT
country,
COUNT
(
*
)
AS
numShips
9.
FROM
Classes
INNER
JOIN
10.
Ships
ON
Classes.class = Ships.class
11.
GROUP
BY
country, launched
12.
)
AS
a
13.
GROUP
BY
country
)
AS
b
14.
ON
a.country = b.country
AND
a.numShips = b.Qty
15.
GROUP
BY
a.country, a.numShips;
В предложении FROM выполняется внутреннее эквисоединение по стране и числу кораблей двух подзапросов. В первом подзапросе определяется страна и число кораблей, спущенных на воду в этой стране в каждом году. Второй подзапрос содержит аналогичный запрос в предложении FROM, выбирая из него только ту пару {страна, число кораблей}, которая содержит максимальное число кораблей, спущенное на воду в течение одного года.
В результате этого соединения пара {страна, максимальное число кораблей} дополняется годом, в котором такое число кораблей было спущено на воду.
Наконец, выполняется аналогичная решению 3.6.2
группировка, чтобы определить минимальный год, если максимум достигался несколько раз для одной и той же страны.
Решение 3.6.4.
Использование предложения HAVING.
1.
SELECT
country, QTY,
MIN
(
launched
)
2.
FROM
(
SELECT
country, launched,
COUNT
(
name
)
QTY
3.
FROM
Classes c
JOIN

4. Ships s
ON
c.class = s.class
5.
GROUP
BY
country,launched
6.
HAVING
COUNT
(
name
)
=
(
SELECT
MAX
(
qty
)
7.
FROM
(
SELECT
country,launched,
COUNT
(
name
)
qty
8.
FROM
Classes c1
JOIN
9. Ships s1
ON
c1.class = s1.class
10.
WHERE
country = c.country
11.
GROUP
BY
country,launched
12.
)
e
13.
)
14.
)
T
15.
GROUP
BY
t.qty, t.country;
В подзапросе предложения FROM сначала определяются строки {страна, год, число кораблей}. Затем в предикате предложения HAVING отбираются только те строки, у которых число кораблей совпадает с максимальным числом кораблей данной страны. Обратите внимание на то, что подзапрос в этом предикате является коррелирующим:
1.
WHERE
country = c.country
Именно поэтому MAX(qty) относится именно к стране из основного запроса, а не представляет собой глобальный максимум, что было бы в противном случае. Наконец, находится минимальный год для каждого сочетания {страна, максимальное число кораблей}.
Можно переходить к следующей задаче? Нет, еще рано. Все рассмотренные варианты решений содержат одну и ту же ошибку, которую автор предлагает найти самостоятельно.
Упражнение 3
(рейтинговый
этап)
Для таблицы Product получить
результирующий набор в виде таблицы со

столбцами maker, pc, laptop и printer, в
которой для каждого производителя
требуется указать, производит он (yes) или
нет (no) соответствующий тип продукции.
В первом случае (yes) указать в скобках без
пробела количество имеющихся в наличии
(т.е. находящихся в таблицах PC, Laptop и
Printer) различных по номерам моделей
соответствующего типа.
Если производитель выпускает модели некоторого типа, но ни одной из них нет в наличии, то, согласно формулировке, результат должен быть
yes(0)
а не
no
Многие находят «ошибку» в тестовом решении.
Претензии сводятся к следующему запросу:
1.
SELECT
COUNT
(
*
)
2.
FROM
Product
3.
WHERE
Maker =
'E'
AND
type=
'PC'
; который дает 3 модели ПК для производителя Е, в то время как «правильный ответ» дает только одну модель компьютера для этого производителя.
Вернемся к формулировке, в которой сказано:
«…указать в скобках без пробела количество имеющихся в наличии
(т.е. находящихся в таблицах PC, Laptop и Printer) различных по
номерам моделей соответствующего типа…»
Это в нашем случае означает, что в скобках требуется указать число различных моделей ПК производителя Е в таблице PC. На языке
SQL
это можно записать так:

1.
SELECT
COUNT
(
DISTINCT
pc.model
)
2.
FROM
Product pr
3.
JOIN
PC
ON
pr.model=pc.model
4.
WHERE
Maker =
'E'
; т.е.
1
Подсказки и решения
Упражнение 2 (подсказки и решения)
1.
SELECT
DISTINCT
maker
2.
FROM
Product
3.
WHERE
type =
'Printer'
;
Упражнение 6
(подсказки и
решения)
Соединение таблиц
(а здесь необходимо внутреннее соединение) можно выполнить двумя способами:
1. Через предложение WHERE (единственная возможность до появления стандарта SQL-92)
1.
SELECT
DISTINCT
Product.maker, Laptop.speed
2.
FROM
Product, Laptop
3.
WHERE
Product.model = Laptop.model
4.
AND
Laptop.hd >=
10
;

2. С помощью явной операции соединения JOIN
1.
SELECT
DISTINCT
Product.maker,Laptop.speed
2.
FROM
Product
JOIN
3. Laptop
ON
Product.model = Laptop.model
4.
WHERE
Laptop.hd >=
10
;
Хотя оптимизатор
SQL
Server построит одинаковый план выполнения для обоих запросов, предпочтительным является второй вариант, который позволяет отделить условия соединения таблиц от условий фильтрации строк.
Упражнение
7 (подсказки
и решения)
Вот решение, которое использует соединение вместо объединения:
1.
SELECT
DISTINCT
a.model,
2. ISNULL
(
b.price,
0
)
+ISNULL
(
c.price,
0
)
+ISNULL
(
d.price,
0
)
price
3.
FROM
(((
Product a
LEFT
JOIN
4. PC b
ON
a.model = b.model
5.
)
LEFT
JOIN
6. Laptop c
ON
a.model = c.model
7.
)
LEFT
JOIN
8. Printer d
ON
a.model = d.model
9.
)
10.
WHERE
a.maker =
'B'
;
Здесь применяется три внешних (левых) соединения таблицы Product с каждой из продукционных таблиц.

Отсутствующие значения цены будут заполнены NULL-значениями.
Например, для модели 1232 персонального компьютера цена блокнота и принтера будут NULL. Поэтому только один из трех ценовых столбцов будет содержать значение для каждой строки результирующей выборки. Чтобы не определять, какой это столбец, в списке столбцов предложения SELECT используется конструкция
1. ISNULL
(
b.price,
0
)
+ISNULL
(
c.price,
0
)
+ISNULL
(
d.price,
0
)
, складывающая все три цены, заменяя предварительно NULL-значение нулем.
Последнее необходимо, так как сложение с NULL-значением даст NULL.
Использование в запросе нестандартной функции
ISNULL
(price, 0) не принципиально, так как не менее эффективно всю конструкцию можно заменить стандартным
COALESCE
, даже без суммирования:
1. COALESCE
(
b.price, c.price, d.price,
0
)
И все же представленное решение имеет один недостаток. Представьте, что у производителя B есть модель, скажем 1133, которой нет в продукционной таблице. Тогда результатом выполнения запроса будет строка:
1133 0 с ценой $0. Такой результат дает неправильную информацию, так как продукции по такой цене нет. Чтобы согласовать данное решение с решением на основе объединения, которое не выводит строки с нулевой ценой, нужно добавить еще и условие отбора по цене. Сделайте это самостоятельно и проверьте правильность своего решения

Упражнение 8
(подсказки и
решения)
Сначала пара «естественных» решений, которые отличаются лишь предикатом, проверяющим отсутствие у поставщика модели портативного компьютера.
Решение 4.4.1. Предикат NOT IN
1.
SELECT
DISTINCT
maker
2.
FROM
Product
3.
WHERE
type =
'PC'
AND
4. maker
NOT
IN
(
SELECT
maker
5.
FROM
Product
6.
WHERE
type =
'Laptop'
7.
)
;
Решение 4.4.2. Предикат EXISTS (что обычно для этого предиката,
подзапрос является коррелирующим)
1.
SELECT
DISTINCT
maker
2.
FROM
Product
AS
pc_product
3.
WHERE
type =
'pc'
AND
4.
NOT
EXISTS
(
SELECT
maker
5.
FROM
Product
6.
WHERE
type =
'laptop'
AND
7. maker = pc_product.maker
8.
)
;
3. Теперь приведу несколько оригинальных решений.
Решение 4.4.3. Использование коррелирующих запросов с группировкой
1.
SELECT
DISTINCT
maker
2.
FROM
Product
AS
p
3.
WHERE
(
SELECT
COUNT
(
1
)
4.
FROM
Product pt

5.
WHERE
pt.type =
'PC'
AND
6. pt.maker = p.maker
7.
)
>
0
AND
8.
(
SELECT
COUNT
(
1
)
9.
FROM
Product pt
10.
WHERE
pt.type =
'Laptop'
AND
11. pt.maker = p.maker
12.
)
=
0
;
В подзапросах проверяется, что число моделей ПК поставщика из основного запроса больше нуля, в то время как число моделей портативных компьютеров этого же поставщика равно нулю.
Следует обратить внимание на аргумент функции COUNT(1). Стандарт определяет два типа аргументов этой функции: «*» и выражение.
Применение «*» приводит к подсчету числа строк, отвечающих запросу.
Использование выражения дает число строк, для которых выражение имеет значение, то есть не является NULL. В качестве выражения обычно служит имя столбца, поэтому задействование константы может вызвать удивление у тех, кто еще недостаточно хорошо знаком с языком. Поскольку константа (в рассматриваемом запросе 1) не может быть NULL, то такое выражение вполне эквивалентно COUNT(*).
На примере этой несложной задачи можно продемонстрировать многообразие решений, которое обусловлено гибкостью языка SQL.
Решение 4.4.4. Внешнее самосоединение
1.
SELECT
DISTINCT
p.maker
2.
FROM
Product p
LEFT
JOIN
3. Product p1
ON
p.maker = p1.maker
AND
4. p1.type =
'Laptop'
5.
WHERE
p.type =
'PC'
AND
6. p1.maker
IS
NULL
;
Левое соединение таблицы Product с собой при условии, что производитель один и тот же, а тип продукции из второй таблицы есть блокнот. Тогда в столбце p1.maker будет находиться NULL, если у поставщика нет моделей портативных компьютеров, что и используется в предикате

предложения WHERE наряду с условием, что в той же строке типом продукции является ПК.
Решение 4.4.5. Группировка
1.
SELECT
maker
2.
FROM
(
SELECT
DISTINCT
maker, type
3.
FROM
Product
4.
WHERE
type
IN
(
'PC'
,
'Laptop'
)
5.
)
AS
a
6.
GROUP
BY
maker
7.
HAVING
COUNT
(
*
)
=
1
AND
8.
MAX
(
type
)
=
'PC'
;
В подзапросе выбираются уникальные пары {поставщик, тип}, если типом является ПК или портативный компьютер. Затем выполняется группировка по поставщику, при этом сгруппированные строки должны отвечать следующим условиям:
COUNT(*) = 1 — то есть поставщик должен выпускать только один тип продукции из оставшихся (поскольку мы уже отсекли принтеры, то остается либо ПК, либо портативный компьютер);
MAX(type) = 'PC' — этим типом продукции является ПК. Поскольку в предложении HAVING не могут присутствовать ссылки на столбцы без агрегатных функций, то используется MAX(type), хотя с тем же успехом можно было написать и MIN(type).
При таком обилии подходов естественен вопрос об эффективности, то есть какой из представленных запросов будет выполняться быстрее. Лидером здесь, как по числу операций, так и по оценке времени исполнения, является решение 4.4.5. Наихудшие показатели у третьего варианта. Остальные примерно в два раза по времени уступают лидеру.
Примечание:
Оценку времени, а также процедурный план выполнения запроса в
текстовом представлении можно получить в Query Analyzer (SQL Server),
выполнив сначала команду. SET SHOWPLAN_ALL ON; а затем выполняя
интересующие нас запросы. Чтобы вернуться к обычному режиму
выполнения запросов, нужно в том же подключении дать команду SET
SHOWPLAN_ALL OFF;

Если у вас не установлен
SQL
Server, вы можете получить план выполнения запроса непосредственно на сайте: http://www.sql-ex.ru/perfcon.php
Упражнение 10
(подсказки и
решения)
Решить задачу без использования подзапроса можно.
Правда, для этого используются нестандартные средства. Метод основывается на конструкции TOP N
(SQL
Server), которая позволяет отобрать из отсортированного набора первые N строк. Аналогичные конструкции имеются в диалектах
SQL
многих СУБД. В комитете по разработке стандартов было даже зафиксировано предложение о включение подобной конструкции в стандарт языка. Так что не исключено, что к моменту выхода этой книги в свет данная конструкция уже будет стандартизована. А вот и решение:
1.
SELECT
TOP
1
WITH
TIES model, price
2.
FROM
Printer
3.
ORDER
BY
price
DESC
;
Итак, выполняется сортировка по убыванию цены. В результирующий набор попадает одна (первая — TOP 1) строка. Однако остается проблема, когда несколько принтеров из таблицы будут иметь одинаковую максимальную цену. Проблема решается при помощи предложения WITH TIES, которое включит в результирующий набор не только N строк (в нашем случае одну), но и все ниже идущие строки, у которых значения в полях сортировки (у нас
— price) совпадают со значениями N-ой строки (у нас — первой).
В PostgreSQL/MySQL для ограничения количества строк, возвращаемых запросом, используется конструкция (следующая после ORDER BY)

1.
LIMIT
N
[
OFFSET
M
]
где
N - число первых строк, возвращаемых запросом в порядке, заданном сортировкой;
M - число строк, пропускаемых перед началом вывода.
Если предложение OFFSET отсутствует, то выводиться будут N строк, начиная с первой, в противном случае - N строк, начиная с M+1.
Конструкций, подобных WITH TIES, в этих СУБД нет. Поэтому для решения рассматриваемой задачи способом через сортировку так или иначе придется использовать подзапрос:
1.
SELECT
model, price
2.
FROM
Printer
WHERE
price =
3.
(
SELECT
price
FROM
Printer
ORDER
BY
price
DESC
LIMIT
1
)
;
Заметим, что в отличие от
MySQL, в
PostgreSQL предложение OFFSET может использоваться и при отсутствии предложения
LIMIT. В этом случае возвращаться будут все строки запроса кроме первых M.
Так, например, чтобы вывести все строки кроме первой строки с максимальной ценой, можно написать:
1.
SELECT
model, price
2.
FROM
Printer
3.
ORDER
BY
price
DESC
OFFSET
1
;
model
price
1434
290.00

1433
270.00
1408
270.00
1401
150.00
При использовании SQL Server эту же задачу можно решить так:
1.
SELECT
model, price
2.
FROM
Printer
WHERE
code
NOT
IN
(
3.
SELECT
TOP
1
code
4.
FROM
Printer
5.
ORDER
BY
price
DESC
)
;
Т.е. мы выбираем все строки, кроме той, которая идёт первой при сортировке цены по убыванию.
А теперь попробуйте решить эту задачу под MySQL.
При решении последней задачи следует обратить внимание на неточность ее постановки. В результате чего представленное решение на одних и тех же данных может давать разные результаты при разных запусках. В частности, у нас имеется две строки с максимальной ценой, и какая из них будет выводиться зависит от порядка, в котором СУБД будет извлекать строки. А этот порядок может меняться в зависимости от выбранного оптимизатором плана. Чтобы сделать постановку и результат! однозначными, следует указать в условии задачи однозначный порядок сортировки. Эту однозначность всегда обеспечит включение первичного ключа в конец списка столбцов сортировки, например:
1.
ORDER
BY
price
DESC
, code или
1.
ORDER
BY
price
DESC
, model, code