Подзапросы. Выражение операций РА (объединение, пересечение, разность) средствами

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

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

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

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

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

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

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

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

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

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

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

Табличные подзапросы { 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

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

Табличные подзапросы {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

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

Табличные подзапросы {WHERE | HAVING } выражение опер_сравн ANY (подзапрос) Запрос 10 Определить клиентов, для которых среднее количество товаров в сделке (операции) равно максимальному среднему количеству. Вариант1(ALL, HAVING) SELECT КодКлиента, AVG(Кол_во) FROM Сделка GROUP BY КодКлиента 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

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

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

Табличные подзапросы {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

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

Объединение, пересечение, разность Описание: Кол-во столбцов каждого из запросов должно быть одинаковым; Столбцы должны быть совместимы по типам; В результирующем запросе используются имена столбцов, заданные в первом запросе; Предложение ORDER BY применяется к результату соединения, поэтому оно может быть указано только в конце всего составного запроса. ХНУРЕ кафедра Інформатики доц. Яковлева О.В. 21

Объединение Запрос 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 использует ‘’ для литералов ХНУРЕ кафедра Інформатики доц. Яковлева О.В. 22

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

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

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

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

Исходная схема данных БД «Торговля» ХНУРЕ кафедра Інформатики доц. Яковлева О.В. 27 Закрепление (подготовка в самостоятельной работе): Подсчитать общее количество купленного каждой фирмой товара и его стоимость (на какую сумму) до 2009 года. Выводить название фирмы, количество товара, сумму, если количество сделок для фирмы равно 10, 20, 30, 40. Отсортировать по количеству сделок по возрастанию, затем по названию фирм по убыванию. Подсчитать на какую сумму каждая фирма купила каждого товара. Вывести название фирмы, код товара, название товара. Определить на какую сумму продано товара типа «мебель». Определить на какую сумму каждый год продавалось товара типа «мебель». Выводить год и сумму. Результат интересует для тех лет, в которых сумма находится в диапазоне [1 000, 10 000]. Отсортировать результат по годам. Вывести дату сделки, в которой было куплено максимальное количество товара. Вывести ФИО клиентов, которые покупали товар более одного раза (inner, in, exists). Вывести города, где находится либо Товар, либо Клиент.