Базы данных и информационные системы. Подзапросы. Выражение операций РА

1.

Базы данных и
информационные системы
Подзапросы.
Выражение операций РА
(объединение, пересечение, разность)
средствами языка SQL.
Лекции 11,12

2.

План занятия
Общая информация (введение)
1.Подзапросы
-Скалярные подзапросы;
-Табличные подзапросы:
-{ WHERE | HAVING } выражение [ NOT ] IN (подзапрос);
-{WHERE | HAVING } выражение оператор_сравнения { ALL | SOME | ANY }(подзапрос);
-{WHERE | HAVING } [ NOT ] EXISTS (подзапрос);
-Подзапрос в предложении FROM.
2. Коррелирующий подзапрос;
3. Выражение операций РА (объединение, пересечение, разность) средствами SQL;
4. Примеры;
Заключение
2
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

3.

Подзапрос
Подзапрос – это инструмент создания временной таблицы, содержимое
которой извлекается и обрабатывается внешним оператором.
К подзапросам применяются следующие правила:
подзапросы могут быть помещены непосредственно после операторов:
сравнения (=, <, >, <=, >=, <>), IN, ANY, SOME, ALL в предложениях WHERE,
HAVING,
в строке SELECT внешнего оператора SELECT;
в строке FROM;
текст подзапроса должен быть заключен в скобки;
по умолчанию имена столбцов в подзапросе относятся к таблице, имя которой
указано в предложении FROM подзапроса. Для ссылки на столбцы таблицы,
указанной во фразе FROM внешнего запроса используется точечная нотация;
3
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

4.

Подзапрос
Существует два типа подзапросов:
Скалярный подзапрос возвращает единственное значение.
Может использоваться везде, где требуется указать единственное
значение.
Табличный подзапрос возвращает множество значений, т.е. значения
одного или нескольких столбцов таблицы, размещенные в более чем одной
строке.
Он возможен везде, где допускается наличие таблицы.
4
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

5.

Скалярный подзапрос
Используется в строках:
WHERE, HAVING;
SELECT.
Запрос 1a. Определите максимальную партию товара (сделку с максимальным
количеством товара).
Запрос 1б. Определите дату продажи максимальной партии товара.
SELECT Дата, Кол_во FROM Сделка
Запрос1
WHERE Кол_во = (SELECT Max(Кол_во)
Дата
FROM Сделка);
11.10.2010
Запрещено!
WHERE Количество=Max(Количество)
5
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.
Кол_во
10

6.

Скалярный подзапрос
Запрос 2а. Подсчитать среднее количество товара в сделках.
Запрос2а
Expr1000
3,27272727272727
Запрос 2б. Определить даты сделок, превысивших по количеству товара
среднее значение и указать для этих сделок превышение над средним уровнем.
SELECT Дата, Кол_во, Кол_во - (SELECT Avg(Кол_во)
FROM Сделка) AS Превышение
FROM Сделка
WHERE Кол_во > (SELECT Avg(Кол_во) FROM Сделка);
Запрос2б
Дата
6
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.
Кол_во
Превышение
11.10.2010
10
6,72727272727273
15.10.2009
5
1,72727272727273
17.10.2009
4
0,727272727272727
18.10.2009
5
1,72727272727273

7.

Скалярный подзапрос
Запрос 3 (подзапрос при выборе данных из разных таблиц)
Определить клиентов, совершивших сделки с максимальным
количеством товара. Выводить фамилию.
Запрос 4 Определить клиентов, в сделках которых количество товара
отличается от максимального не более чем на 10%.
7
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

8.

Скалярный подзапрос
Использование подзапроса в HAVING
Запрос 5а Определить даты, когда среднее количество проданного за день
товара оказалось больше 20 единиц.
Запрос 5б Определить даты, когда среднее количество проданного за день
товара оказалось больше среднего показателя по всем сделкам вообще.
SELECT Дата, Avg(Кол_во) AS Среднее_за_день
FROM Сделка
GROUP BY Дата
HAVING Avg(Сделка.Кол_во) > (SELECT Avg(Кол_во) FROM Сделка);
8
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

9.

Табличные подзапросы
Используется в строках:
WHERE, HAVING;
FROM.
Использование в строках WHERE, HAVING:
{ WHERE | HAVING } выражение [ NOT ] IN (подзапрос);
{WHERE | HAVING } выражение оператор_сравнения { ALL | SOME | ANY }(подзапрос)
{WHERE | HAVING } [ NOT ] EXISTS (подзапрос).
Склад
КодТовара
а
Рисунок 1.1 – Схема данных (Access), отношение-экземпляр Склад
9
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.
Кол_во_Склад
1
8
2
100
3
45
4
20
5
30
6
2
7
4
8
5
9
0
б

10.

Табличные подзапросы
{ WHERE | HAVING } выражение [ NOT ] IN (подзапрос);
Оператор IN используется для сравнения некоторого значения со списком значений
и может использоваться с подзапросами, возвращающими один столбец.
Запрос 6 Вывести название товаров, которых на складе > 10
Вариант1
SELECT Название
FROM Товар_New INNER JOIN Склад
ON Товар_New.КодТовара = Склад.КодТовара
WHERE Остаток>10;
Запрос6
Название
Вариант2
Стол
Стул
SELECT Название
Диван
FROM Товар_New
Диван
WHERE КодТовара IN
(SELECT КодТовара From Склад WHERE Остаток>10);
10
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

11.

Табличные подзапросы
{ WHERE | HAVING } выражение [ NOT ] IN (подзапрос);
Запрос 7а Вывести без повторов название товаров, которые уже покупались
(присутствуют в таблице Сделка)
Вариант1
Запрос7а
Название
Вариант2
Диван
Подсвечник
Стол
Стул
11
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

12.

Табличные подзапросы
{ WHERE | HAVING } выражение [ NOT ] IN (подзапрос);
Запрос 7б Вывести название товаров, которые еще ни разу не покупались
(отсутствуют в таблице Сделка)
Вариант1
SELECT Название
FROM Товар_New LEFT JOIN Сделка
ON Товар_New.КодТовара = Сделка.КодТовара
WHERE Сделка.КодТовара IS NULL;
Вариант2
SELECT Название
FROM Товар_New
WHERE КодТовара NOT IN
(SELECT КодТовара From Сделка);
12
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.
Запрос7б
Название
Рамка для фото
Шкаф

13.

Табличные подзапросы
{WHERE | HAVING } выражение опер_сравн { ALL | SOME | ANY }(подзапрос)
Использование ключевых слов ANY, SOME и ALL:
Ключевые слова ANY, SOME и ALL могут использоваться с подзапросами,
возвращающими один столбец значений;
Ключевое слово ALL- условие сравнения считается выполненным, только
когда оно выполняется для всех значений в результирующем столбце
подзапроса.
Ключевое слово ANY, SOME - условие сравнения считается выполненным,
когда оно выполняется хотя бы для одного из значений в результирующем
столбце подзапроса.
Если результат выполнения подзапроса не содержит строк, то для ключевого
слова ALL условие сравнения будет считаться выполненным (TRUE), а для
ключевого слова ANY – невыполненным (FASLE).
Если результат выполнения подзапроса содержит NULL, то результатом
сравнения и для ALL, и для ANY будет UNKNOWN.
13
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

14.

Табличные подзапросы
{WHERE | HAVING } выражение опер_сравн ALL (подзапрос)
Запрос 8 Определить фамилии клиентов, совершивших сделки с
максимальным количеством товара (экв-но запросу 3)
Вариант1 (max)
SELECT Фамилия
FROM Клиент INNER JOIN Сделка ON
Клиент.КодКлиента=Сделка.КодКлиента
WHERE Кол_во=(SELECT max(Кол_во) FROM Сделка);
Вариант2 (ALL)
SELECT Фамилия
FROM Клиент INNER JOIN Сделка ON
Клиент.КодКлиента=Сделка.КодКлиента
WHERE Кол_во>=ALL(SELECT Кол_во FROM Сделка);
Поиск минимального значения
WHERE Кол_во <= ALL (SELECT Кол_во FROM Сделка);
14
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.
Запрос8
Фамилия
Иванов
Кол_во
10

15.

Табличные подзапросы
{WHERE | HAVING } выражение опер_сравн ANY (подзапрос)
Запрос 9 Определить фамилии клиентов, в сделках которых количество товаров
превышает количество товаров хотя бы в одной сделке (больше минимального значения)
Вариант1(ANY)
Запрос9
SELECT Фамилия, Кол_во
Фамилия
FROM Клиент INNER JOIN Сделка ON
Клиент.КодКлиента=Сделка.КодКлиента
WHERE Кол_во > ANY (SELECT Кол_во FROM Сделка);
Вариант2(ALL)
Поиск не минимального значения
WHERE NOT Кол_во <= ALL (SELECT Кол_во FROM Сделка);
Вариант3(min)
WHERE Кол_во > (SELECT min(Кол_во) FROM Сделка);
15
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.
Кол_во
Иванов
10
Иванов
2
Иванов
2
Климов
5
Абрамов
2
Абрамов
3
Семенов
4
Абрамов
5

16.

Табличные подзапросы
{WHERE | HAVING } выражение опер_сравн ANY (подзапрос)
Запрос 10 Определить клиентов, для которых среднее количество товаров в сделке
(операции) равно максимальному среднему количеству.
Вариант1(ALL, HAVING)
SELECT КодКлиента, AVG(Кол_во)
Запрос10
FROM Сделка
КодКлиента
GROUP BY КодКлиента
4
HAVING AVG(Кол_во)>=ALL (SELECT AVG(Кол_во)
FROM Сделка GROUP BY КодКлиента);
Подзапрос в предложении FROM
Вариант2(ALL, HAVING, FROM)
SELECT КодКлиента, AVG(Кол_во)
FROM Сделка GROUP BY КодКлиента
HAVING AVG(Кол_во) = (SELECT MAX(T.AVG_for_Клиент)
FROM (SELECT AVG(Кол_во) AS AVG_for_Клиент
FROM Сделка
GROUP BY КодКлиента) AS T);
16
Expr1001
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.
5

17.

Табличные подзапросы
Закрепление (подготовка к самостоятельной работе)
Запрос 11а Найти фирму, купившую товаров на сумму,
превышающую 10000грн
Запрос11а
Фирма
Общ_стоимость
ООО Буд
11 200,00р.
ООО Ух
57 800,00р.
Запрос11б
Фирма
Общ_стоимость
ООО Ух
57 800,00р.
Запрос 11б Найти фирму, которая приобрела товаров на самую большую сумму (ALL, HAVING)
17
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

18.

Табличные подзапросы
{WHERE | HAVING } [ NOT ] EXISTS (подзапрос).
Использование ключевых слов (предикатов) EXISTS и NOT EXISTS :
предназначены для использования только совместно с подзапросами;
EXISTS принимает значение TRUE, если подзапрос содержит любое количество строк, иначе
FALSE; Для NOT EXISTS – наоборот;
Никогда не возвращает значение UNKOWN;
Подзапрос может содержать любое количество столбцов;
Обычно EXISTS используется в зависимых (коррелирующих подзапросах);
КОРРЕЛИРУЮЩИЙ ПОДЗАПРОС
Подзапрос, имеющих внешнюю ссылку, связанную со значением в основном запросе.
Результат подзапроса зависит от значение во внешнем запросе и оценивается отдельно для
каждой строки внешнего запроса. Следовательно, предикат EXISTS может иметь разные значения
для разных строк основного запроса.
18
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

19.

Табличные подзапросы
{WHERE | HAVING } [ NOT ] EXISTS (подзапрос).
Запрос 12 Определить название товаров, которые уже покупались
(Экв-но запр. 7а) Вариант3
SELECT DISTINCT Название
FROM Товар_New
WHERE EXISTS (SELECT КодТовара From Сделка
WHERE Сделка.КодТовара= Товар_New.КодТовара);
Запрос 13 Определить название товаров, которые еще ни разу не покупались
(Экв-но запр. 7б) Вариант3
SELECT DISTINCT Название
FROM Товар_New
WHERE NOT EXISTS (SELECT КодТовара From Сделка
WHERE Сделка.КодТовара= Товар_New.КодТовара);
19
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

20.

Коррелирующий подзапрос в предложении FROM
Запрос 14 Подсчитать сколько каждый клиент купил товара. Вывести клиентов (код и
фамилию), подсчитанное количество. Если клиент не покупал еще товаров, он тоже
должен быть в списке.
Вариант 1
SELECT Клиент.КодКлиента, Клиент.Фамилия, Sum(Кол_во) AS SumКол_во
FROM Клиент LEFT JOIN Сделка ON Клиент.КодКлиента = Сделка.КодКлиента
GROUP BY Клиент.КодКлиента, Клиент.Фамилия;
Вариант 2
SELECT Клиент.КодКлиента,
Запрос14
КодКлиента
Фамилия
SumКол_во
1
Иванов
14
2
Петров
2
3
Сидоров
1
4
Климов
5
5
Абрамов
10
6
Семенов
4
7
Бобырь
Клиент.Фамилия, (SELECT SUM(Кол_во) AS SumКол_во
FROM Сделка
WHERE Сделка.КодКлиента=Клиент.КодКлиента)
20
FROM Клиент;
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

21.

Объединение, пересечение, разность
Операция
РА
SQL
Объединение
1
r s
Запрос1
MS Access
UNION [ALL]
MS SQL Server
Запрос2
Oracle
Запрос1
MS SQL Server
INTERSECT
Oracle
Пересечение
2
r s
Платформы
Запрос2
Разность
1
r-s
Описание:
Запрос1
MS SQL Server
EXCEPT
Oracle (MINUS)
Запрос2
Кол-во столбцов каждого из запросов должно быть одинаковым;
Столбцы должны быть совместимы по типам;
В результирующем запросе используются имена столбцов, заданные в первом запросе;
Предложение ORDER BY применяется к результату соединения, поэтому оно может быть
указано только в конце всего составного запроса.
21
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

22.

Объединение
Запрос 15 Вывести отсортированный список городов, в которых находятся клиенты или откуда
поставляются товары.
Запрос15
SELECT ГородКлиента AS Город
FROM Клиент
UNION
SELECT ГородТовара
FROM Товар
ORDER BY 1;
Город
Киев
Москва
Запрос16
Харьков
СтатусГорода
ГородКлиента
ГородПоставщика
Харьков
ГородПоставщика
Харьков
ГородПоставщика
Харьков
ГородКлиента
Харьков
ГородКлиента
Харьков
с указанием статуса города (ГородКлиента или ГородПоставщика) ГородКлиента
Харьков
ГородКлиента
Харьков
SELECT 'ГородКлиента' AS СтатусГорода, ГородКлиента
FROM Клиент
UNION ALL
SELECT 'ГородПоставщика', ГородТовара
FROM Товар
ORDER BY 2 DESC;
ГородПоставщика
Москва
ГородПоставщика
Москва
ГородКлиента
Киев
ГородКлиента
Киев
ГородПоставщика
Киев
ГородПоставщика
Киев
Замечание! MS SQL Server использует ‘’ для литералов
ГородПоставщика
Киев
ГородПоставщика
Киев
ГородКлиента
Киев
Запрос 16 Вывести отсортированный список городов,
в которых находятся клиенты или откуда поставляются товары
22
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

23.

Пересечение
Запрос 17 Вывести отсортированный список городов, в которых находятся и клиенты, и откуда
поставляются товары.
Вариант 5 (ANY)
Вариант 1
SELECT ГородКлиента
FROM Клиент
INTERSECT
SELECT ГородТовара
FROM Товар
ORDER BY 1;
Запрос17
ГородКлиента
Киев
Харьков
Вариант 2 (INNER JOIN)
Вариант 3 (EXISTS)
Вариант 4 (IN)
23
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.
Вариант 6 (×)

24.

Разность
Запрос 18 Вывести список городов, откуда поставляются товары, но где не проживают клиенты.
Вариант 1
SELECT ГородТовара
FROM Товар
EXCEPT
SELECT ГородКлиента
FROM Клиент;
Запрос18
ГородТовара
Москва
Вариант 2 (LEFT JOIN)
Вариант 3 (EXISTS)
Вариант 4 (IN)
24
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.
Вариант 5 (ALL)

25.

Дополнительное задание (5-10 баллов)
3 лучших результата к каждом соревновании
ID_race
Competition Sportsman
1 соревнование1 вася
2 соревнование1 петя
3 соревнование1 коля
4 соревнование1 дедушка
5 соревнование1 папа
6 соревнование1 ваня
7 соревнование2 вася
8 соревнование2 папа
9 соревнование2 дедушка
10 соревнование2 рома
11 соревнование2 бабушка
Time
30
28
31
25
28
10
20
34
33
18
33
Задание:
Найти в каждом соревновании призеров (три лучших результата),
занявших 1,2,3 места. Вывести ID_race, Competition, Sportsman, Time, Место
25
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

26.

Задание на самостоятельную проработку материала:
Закрепление (подготовка в самостоятельной работе):
Необходимо выполнить в СУБД Access для БД «Торговля» (файл Trade_SubQuery_2013_11_16) все запросы из
презентации «Подзапросы. Выражение операций РА (объединение, пересечение, разность) средствами языка
SQL». Особое внимание на выполнение запросов:
-запрос11б;
-запрос17 (варианты 2-6);
-запрос18 (варианты 2-5);
-а также запросы со слайда 27 (подготовка к сам. работе по темам «Соединение таблиц» и «Подзапросы»).
26
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.

27.

Исходная схема данных БД «Торговля»
Клиент
КодКлиента Фамилия
Имя
1 Иванов
2 Петров
3 Сидоров
Иван
Петр
Сидор
Отчество
Фирма
ГородКлиента
Телефон
Иванович ООО Буд
Петрович
ООО Ух
Сидорович ООО Буд
Харьков
Киев
Харьков
050-789 45 56
067- 786 34-87
050-711 65 88
4 Климов
Кузьма Васильевич ООО Буд
5 Абрамов Алексей Федорович ООО Ух
6 Семенов Василий Степанович ООО Уют
Киев
Харьков
Харьков
098-777 45 22
050-232 11 45
098-34522 65
7 Бобырь
Киев
050-555 22 44
Алексей Иванович
ООО Уют
Товар
Код
Товара
1
2
3
4
5
6
7
8
9
Название
Стул
Стол
Стул
Диван
Диван
Стол
Рамка для фото
Подсвечник
Шкаф
Тип
Сорт
Цена
мебель
мебель
мебель
мебель
мебель
мебель
интерьер
интерьер
мебель
высший
первый
высший
второй
высший
второй
высший
первый
высший
400,00р.
200,00р.
400,00р.
4 000,00р.
8 000,00р.
400,00р.
150,00р.
40,00р.
10 000,00р.
Остаток
10
20
1
3
1
2
10
10
2
ГородТовара
Харьков
Киев
Киев
Харьков
Киев
Москва
Москва
Харьков
Киев
КодСделки
1
2
3
4
5
6
7
8
9
10
11
КодТовара
1
2
1
2
1
3
4
5
6
8
5
Сделка
КодКлиента
1
1
2
2
1
4
3
5
5
6
5
Кол_во
10
2
1
1
2
5
1
2
3
4
5
Дата
11.10.2010
13.10.2009
13.10.2009
14.10.2009
15.10.2009
15.10.2009
15.10.2009
16.10.2009
16.10.2009
17.10.2009
18.10.2009
Закрепление (подготовка в самостоятельной работе):
1.Подсчитать общее количество купленного каждой фирмой товара и его стоимость (на какую сумму) до 2009 года.
Выводить название фирмы, количество товара, сумму, если количество сделок для фирмы равно 10, 20, 30, 40.
Отсортировать по количеству сделок по возрастанию, затем по названию фирм по убыванию.
2.Подсчитать на какую сумму каждая фирма купила каждого товара. Вывести название фирмы, код товара, название
товара.
3.Определить на какую сумму продано товара типа «мебель».
4.Определить на какую сумму каждый год продавалось товара типа «мебель». Выводить год и сумму. Результат
интересует для тех лет, в которых сумма находится в диапазоне [1 000, 10 000]. Отсортировать результат по годам.
5.Вывести дату сделки, в которой было куплено максимальное количество товара.
6.Вывести ФИО клиентов, которые покупали товар более одного раза (inner, in, exists).
7.Вывести города, где находится либо Товар, либо Клиент.
27
ХНУРЕ кафедра Інформатики доц. Яковлева О.В.