Вывести номера продавцов с меткой «сильный продавец» или «слабый». Сильным считается продавец со средней стоимостью сделки больше 500, слабым – меньше 500 (запрос приведен на Лист. 39, результат - Табл. 51):
Лист. 39. Запрос с оператором Union
SELECT N_Продавца, 'Слабый продавец' as Активность FROM Сделки
GROUP BY N_Продавца
HAVING avg(Стоимость)<500
UNION SELECT N_Продавца, 'Сильный продавец' as Активность FROM Сделки
GROUP BY N_Продавца
HAVING avg(Стоимость)>500;
Табл. 51. Результат запроса с оператором Union
33 Union
N_Продавца
Активность
Слабый продавец
Сильный продавец
Сильный продавец
Вывести номера продавцов с меткой «сильный продавец» или «слабый». Сильным считается продавец со средней стоимостью сделки больше 500, слабым – меньше 500, отсортировать по активности (запрос приведен на Лист. 40, результат - Табл. 52):
Лист. 40. Запрос с Union и Order by
SELECT N_Продавца, 'Слабый продавец' as Активность FROM Сделки
GROUP BY N_Продавца
HAVING avg(Стоимость)<500
UNION SELECT N_Продавца, 'Сильный продавец' as Активность FROM Сделки
GROUP BY N_Продавца
HAVING avg(Стоимость)>500
ORDER BY Активность;
Табл. 52. Результат запроса с Union и Order by
34 Union Order by
N_Продавца
Активность
Сильный продавец
Сильный продавец
Слабый продавец
Аналогично работают операторы intersect и except, соответствующие операциям пересечения и разности в реляционной алгебре. Для предметной области «продажи» списки городов покупателей и продавцов могут не совпадать, поэтому мы можем решить разнообразные задачи с этими списками, как показано в Табл. 53.
Табл. 53. Использование операторов соединения однотипных запросов
Задача
Запрос
Вывести все города.
SELECT Город
FROM Покупатели
UNION
SELECT Город
FROM Продавцы;
Вывести города, в которых есть и покупатели и продавцы.
SELECT Город
FROM Покупатели
INTERSECT
SELECT Город
FROM Продавцы;
Вывести города, в которых есть покупатели, но нет продавцов.
SELECT Город
FROM Покупатели
EXCEPT
SELECT Город
FROM Продавцы;
На Лист. 25 был приведен пример запроса, в котором ищутся подчиненные Иванова. Возникает вопрос, как построить всю иерархию подчиненных, а не только его непосредственных подчиненных. Фактически мы должны применить этот же самый запрос Лист. 25 к результату выполнения его самого, затем еще раз и т.д., пока будут находиться подчиненные на всё более низких уровнях иерархии.
Построить иерархию всех подчиненных Иванова в «в длину» (запрос приведен на Лист. 41, результат -Табл. 54).
Рекурсивные запросы можно опознать по ключевому слову WITH RECURSIVE. Чтобы быть вызванным рекурсивно, запрос фактически должен иметь имя (в данном случае - Прод). Запрос состоит из двух частей, объединяемых с помощью UNION. В первой части – начальники, во второй части – их подчиненные. Запрос находит первого подчиненного, затем первого подчиненного первого и т.д. Такие запросы называются запросами на поиск «в длину»
Лист. 41. Рекурсивный запрос «в длину»
WITH RECURSIVE Прод (N, Имя) as
(SELECT N, Имя
FROM Продавцы
WHERE Имя = ‘Иванов’
UNION ALL
SELECT Продавцы. N, Продавцы.Имя
FROM Продавцы, Прод
WHERE Прод.N = Продавцы.N_Начальника);
SELECT * FROM Прод;
Табл. 54. Результат рекурсивного запроса «в длину»
34 Рекурсия «в длину»
N
Имя
Иванов
Петров
Шмидт
Зайцев
Сидоров
Если нам нужно сперва перечислить всех подчиненных Иванова, затем подчиненных подчиненных Иванова и т.д., то нам нужно использовать рекурсивные запросы «в ширину», используя оператор SEARCH BREADTH FIRST. В запросе с помощью оператора SET устанавливается номер итерации.
Построить иерархию всех подчиненных Иванова «в ширину» (запрос приведен на Лист. 42, результат -Табл. 55).
Лист. 42. Рекурсивный запрос «в ширину»
WITH RECURSIVE Прод (N, Имя, N_Начальника) as
(SELECT N, Имя
FROM Продавцы
WHERE Имя = ‘Иванов’
UNION ALL
SELECT Продавцы. N, Продавцы. Имя
FROM Продавцы, Прод
WHERE Прод.N = Продавцы.N_Начальника);
SEARCH BREADTH FIRST BY N_Начальника, N
SET order_column
SELECT * FROM Прод
ORDER BY order_column;
Табл. 55. Результат запроса «в ширину»
35 Рекурсия «в ширину»
N
Имя
Иванов
Петров
Сидоров
Шмидт
Зайцев
При рекурсивном запросе возможна ситуация зацикливания. Хотя этого и нет в нашем примере, можно предположить, что бывают ситуации, когда один коллектив работает над двумя проектами, причем один человек в первом проекте – начальник, а во втором – подчиненный и наоборот, второй человек в первом проекте – подчиненный. А во втором – начальник. В этом случае предыдущие рекурсивные запросы зациклятся.
Построить иерархию всех подчиненных Иванова, учесть возможность зацикливания (Лист. 43).
Лист. 43. Рекурсивный запрос с учетом зацикливания
WITH RECURSIVE Прод (N, Имя) as
(SELECT N, Имя
FROM Продавцы
WHERE Имя = ‘Иванов’
UNION ALL
SELECT Продавцы. N, Продавцы. Имя
FROM Продавцы, Прод
WHERE Прод.N = Продавцы .N_Начальника);
CYRCLE N
SET cyrclemark to “Y” default “N”
USING cyrclepath
SELECT * FROM Прод
ORDER BY N;
Д/З 6. Для примера из Д/З 4 придумайте следующие запросы:
Запрос по одной таблице, вычисляющий агрегатную функцию с использованием операторов where, having, order by.
Запрос соединения нескольких таблиц с использованием оператора where, одна таблица должна использоваться в запросе несколько раз под псевдонимами.
Запрос по правому, левому или полному соединению.
Запрос с двумя вложенными запросами в конструкциях where и having
Запрос с вложенным запросом, используя all, any или exists.
Вложенный запрос с объединением двух таблиц (одна из разновидностей join), опосредованно связанных третьей.
Объединение однотипных запросов.
Рекурсивный запрос.
Вопросы для самопроверки:
1. Чем отличается использование операторов group by, group by rollup, group by rollup cube, grouping, orger by, partition?
2. Чем отличается использование операторов where и having?
3. Какому запросу join … on соответствует оператор where?
4. Какой операции реляционной алгебры соответствует оператор where?
5. Какой операции реляционной алгебры соответствует оператор join?
