Базы данных и информационные системы Подзапросы.

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

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

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

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

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

Скалярный подзапрос Запрос 2 а. Подсчитать среднее количество товара в сделках. Запрос2 а Expr 1000 3, 27272727 Запрос 2 б. Определить даты сделок, превысивших по количеству товара среднее значение и указать для этих сделок превышение над средним уровнем. SELECT Дата, Кол_во - (SELECT Avg(Кол_во) FROM Сделка) AS Превышение FROM Сделка WHERE Кол_во > (SELECT Avg(Кол_во) FROM Сделка); Запрос2 б Дата Кол_во Превышение 11. 10. 2010 6, 72727273 15. 10. 2009 5 1, 72727273 17. 10. 2009 4 0, 727272727 18. 10. 2009 5 1, 72727273 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 8 2 100 3 45 4 20 5 30 6 2 7 4 8 5 а 9 0 Рисунок 1. 1 – Схема данных (Access), отношение-экземпляр Склад б 9 ХНУРЕ кафедра Інформатики доц. Яковлева О. В.

Табличные подзапросы { 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 ХНУРЕ кафедра Інформатики доц. Яковлева О. В.

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

Табличные подзапросы { WHERE | HAVING } выражение [ NOT ] IN (подзапрос); Запрос 7 б Вывести название товаров, которые еще ни разу не покупались (отсутствуют в таблице Сделка) Вариант1 SELECT Название FROM Товар_New LEFT JOIN Сделка ON Товар_New. Код. Товара = Сделка. Код. Товара WHERE Сделка. Код. Товара IS NULL; Запрос7 б Вариант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) Запрос8 SELECT Фамилия Кол_во Иванов 10 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) Запрос9 Фамилия Кол_во SELECT Фамилия, Кол_во Иванов 10 FROM Клиент INNER JOIN Сделка ON Иванов 2 Клиент. Код. Клиента=Сделка. Код. Клиента Иванов 2 WHERE Кол_во > ANY (SELECT Кол_во FROM Сделка); Климов 5 Абрамов 2 Вариант2(ALL) Абрамов 3 Семенов 4 Поиск не минимального значения Абрамов 5 WHERE NOT Кол_во <= ALL (SELECT Кол_во FROM Сделка); Вариант3(min) WHERE Кол_во > (SELECT min(Кол_во) FROM Сделка); 15 ХНУРЕ кафедра Інформатики доц. Яковлева О. В.

Табличные подзапросы {WHERE | HAVING } выражение опер_сравн ANY (подзапрос) Запрос 10 Определить клиентов, для которых среднее количество товаров в сделке (операции) равно максимальному среднему количеству. Вариант1(ALL, HAVING) SELECT Код. Клиента, AVG(Кол_во) Запрос10 FROM Сделка Код. Клиента Expr 1001 GROUP BY Код. Клиента 4 5 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 а Запрос 11 а Найти фирму, купившую товаров на сумму, Фирма Общ_стоимость превышающую 10000 грн ООО Буд 11 200, 00 р. ООО Ух 57 800, 00 р. Запрос11 б Фирма Общ_стоимость ООО Ух 57 800, 00 р. Запрос 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 Клиент. Код. Клиента, Клиент. Фамилия; Запрос14 Код. Клиента Фамилия Sum. Кол_во 1 Иванов 14 2 Петров 2 3 Сидоров 1 4 Климов 5 Абрамов 10 Вариант 2 6 Семенов 4 SELECT Клиент. Код. Клиента, 7 Бобырь Клиент. Фамилия, (SELECT SUM(Кол_во) AS Sum. Кол_во FROM Сделка WHERE Сделка. Код. Клиента=Клиент. Код. Клиента) FROM Клиент; 20 ХНУРЕ кафедра Інформатики доц. Яковлева О. В.

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

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

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

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

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

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

Исходная схема данных БД «Торговля» Сделка Товар Код. Сделки Код. Товара Код. Клиента Кол_во Дата Код Название Тип Сорт Цена Остаток Город. Товара 1 1 1 10 11. 10. 2010 Товара 2 2 1 2 13. 10. 2009 1 Стул мебель высший 400, 00 р. 10 Харьков 3 1 2 1 13. 10. 2009 2 Стол мебель первый 200, 00 р. 20 Киев 4 2 2 1 14. 10. 2009 3 Стул мебель высший 400, 00 р. 1 Киев 5 1 1 2 15. 10. 2009 4 Диван мебель второй 4 000, 00 р. 3 Харьков 6 3 4 5 15. 10. 2009 5 Диван мебель высший 8 000, 00 р. 1 Киев 7 4 3 1 15. 10. 2009 6 Стол мебель второй 400, 00 р. 2 Москва 8 5 5 2 16. 10. 2009 7 Рамка для фото интерьер высший 150, 00 р. 10 Москва 9 6 5 3 16. 10. 2009 8 Подсвечник интерьер первый 40, 00 р. 10 Харьков 10 8 6 4 17. 10. 2009 9 Шкаф мебель высший 10 000, 00 р. 2 Киев 11 5 5 5 18. 10. 2009 Закрепление (подготовка в самостоятельной работе): 1. Подсчитать общее количество купленного каждой фирмой товара и его стоимость (на какую сумму) до 2009 года. Выводить название фирмы, количество товара, сумму, если количество сделок для фирмы равно 10, 20, 30, 40. Отсортировать по количеству сделок по возрастанию, затем по названию фирм по убыванию. 2. Подсчитать на какую сумму каждая фирма купила каждого товара. Вывести название фирмы, код товара, название товара. 3. Определить на какую сумму продано товара типа «мебель» . 4. Определить на какую сумму каждый год продавалось товара типа «мебель» . Выводить год и сумму. Результат интересует для тех лет, в которых сумма находится в диапазоне [1 000, 10 000]. Отсортировать результат по годам. 5. Вывести дату сделки, в которой было куплено максимальное количество товара. 6. Вывести ФИО клиентов, которые покупали товар более одного раза (inner, in, exists). 7. Вывести города, где находится либо Товар, либо Клиент. 27 ХНУРЕ кафедра Інформатики доц. Яковлева О. В.