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

Зарегистрируйтесь, чтобы просмотреть полный документ!

Базы данных и информационные системы Подзапросы. Выражение операций РА (объединение, пересечение, разность) средствами языка 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 Сделка Запрос1 WHERE Кол_во = (SELECT Max(Кол_во) Дата FROM Сделка); 11. 10. 2010 Запрещено! WHERE Количество=Max(Количество) 5 ХНУРЕ кафедра Інформатики доц. Яковлева О. В. Кол_во 10

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

Скалярный подзапрос Запрос 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 2 30 6 2 7 ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 20 5 9 45 4 Рисунок 1. 1 – Схема данных (Access), отношение-экземпляр Склад 100 3 а 8 4 8 5 9 0 б

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

Табличные подзапросы { 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; Вариант2 SELECT Название FROM Товар_New WHERE Код. Товара NOT IN (SELECT Код. Товара From Сделка); 12 ХНУРЕ кафедра Інформатики доц. Яковлева О. В. Запрос7 б Название Рамка для фото Шкаф

$Табличные подзапросы {WHERE | HAVING } выражение опер_сравн { ALL | SOME | ANY$

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

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

$Табличные подзапросы {WHERE | HAVING } выражение опер_сравн ANY (подзапрос) Запрос 9 Определить фамилии$

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

$Табличные подзапросы {WHERE | HAVING } выражение опер_сравн ANY (подзапрос) Запрос 10 Определить клиентов,$

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

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

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

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

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

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

Дополнительное задание (5 -10 баллов) 3 лучших результата к каждом соревновании ID_race Competition 1 соревнование 1 2 соревнование 1 3 соревнование 1 4 соревнование 1 5 соревнование 1 6 соревнование 1 7 соревнование 2 8 соревнование 2 9 соревнование 2 10 соревнование 2 11 соревнование 2 Sportsman вася петя коля дедушка папа ваня вася папа дедушка рома бабушка Time 30 28 31 25 28 10 20 34 33 18 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 2 3 4 5 6 7 8 9 Название Стул Стол Стул Диван Стол Рамка для фото Подсвечник Шкаф Тип Сорт мебель мебель интерьер мебель высший первый высший второй высший первый высший Цена 400, 00 р. 200, 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 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 14. 10. 2009 15. 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 ХНУРЕ кафедра Інформатики доц. Яковлева О. В.

Скачать презентацию Базы данных и информационные системы Подзапросы Выражение операций

PresentationSQL_SubQuery2014_10_23_Stud.ppt

Количество слайдов: 27