Базы данных и информационные системы Выражение операций РА средствами одного оператора SELECT (выборка, проекция, декартовое произведение, соединение) Лекция 9, 10
План занятия 2 ХНУРЕ кафедра Інформатики доц. Яковлева О. В.
Исходные отношения Для иллюстрации теоретико-множественных операций над отношениями введем абстрактные отношения (таблицы) с некоторыми атрибутами (полями) Отношение R a с A 1 A 2 B 1 B 3 B 4 Отношение S c b 1 h 2 g 3 h 3 CREATE TABLE R (a CHAR(1), c INT, PRIMARY KEY(a, c)) CREATE TABLE S (c INT PRIMARY KEY, b CHAR(1)) ХНУРЕ кафедра Інформатики доц. Яковлева О. В.
Операция выборки Операция унарная РА SQL SELECT * FROM R WHERE c=1; SELECT a, c FROM R WHERE c=1; Результат: a A B 4 с 1 1 ХНУРЕ кафедра Інформатики доц. Яковлева О. В.
Операция проекция Операция унарная РА SQL SELECT DISTINCT с FROM R; SELECT с FROM R; 5 ХНУРЕ кафедра Інформатики доц. Яковлева О. В. Результат с 1 2 3 с 1 2 1 3 4
Декартовое произведение Операция бинарная РА Замечания SQL R S SELECT * В SQL Server – будут Результат a R. c S. c b 1 1 h В Access – автоматически A 1 2 g применится точечная нотация; A 1 3 h A 2 1 h A 2 2 g 2 3 h B 1 1 h B 1 2 g B 1 3 h B 3 1 h B 3 2 g B 3 3 h B 4 1 h B 4 2 g B SELECT a, R. c, S. c, b A A FROM R, S; повторяться названия столбцов; 4 3 h FROM R, S; SELECT R. *, S. * FROM R, S В SQL Server – поддерживается; SELECT * FROM R CROSS JOIN S 6 В Access – не поддерживается; ХНУРЕ кафедра Інформатики доц. Яковлева О. В.
Операции соединения 7 ХНУРЕ кафедра Інформатики доц. Яковлева О. В.
Тета, экви- соединение (Внутренние соединения) РА SQL Результат SELECT * a FROM R, S A SELECT * b 1 1 h 2 2 g B Стандарт ANSI S. c A WHERE R. c=S. c; R. c 1 1 h B 3 3 h FROM R INNER JOIN S ON R. c=S. c; SELECT * Стандарт ANSI: по умолчанию FROM R JOIN S ON R. c=S. c; В Access – не поддерживается; Соединение можно использовать со знаками >, <, >=, <>… Например, SELECT * FROM R INNER JOIN S ON R. c > S. c; a R. c S. c - INNER b 8 ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 1 h B 3 1 h 4 1 h B 3 2 g B 4 2 g B SELECT * FROM R, S WHERE R. c > S. c; 2 B или A 4 3 h
Естественное соединение (Внутренние соединения) РА SQL Результат a R. c или S. c b A 1 h FROM R, S A 2 g WHERE R. c=S. c; B 1 h B 3 h SELECT a, R. c, b (или SELECT a, S. c, b) SELECT a, R. c, b Стандарт ANSI FROM R INNER JOIN S ON R. c=S. c; SELECT a, R. c, b FROM R JOIN S ON R. c=S. c; Стандарт ANSI: по умолчанию - INNER В Access – не поддерживается; SELECT * FROM R JOIN S USING c; SELECT * FROM R NATURAL JOIN S; 9 Стандарт ANSI В SQL Server, Access – не поддерживается; Oracle 9 и выше - поддерживается; USING, NATURAL JOIN – предполагается равенство двух или более столбцов, имеющихся в обеих таблицах (повторяющийся столбец отбрасывается); ХНУРЕ кафедра Інформатики доц. Яковлева О. В.
Исходная схема данных БД «Торговля» Клиент (Код. Клиента, Фамилия, Имя, Отчество, Фирма, Город. Клиента, Телефон) Товар (Код. Товара, Название, Тип, Сорт, Цена, Остаток, Город. Товара) Сделка (Код. Сделки, Кол_во, Дата, Код. Товара, Код. Клиента) Товар Код Товара 1 2 3 4 5 6 7 8 9 10 Название Тип Сорт Цена Стул Стол Стул Диван Стол Рамка для фото Подсвечник Шкаф мебель мебель интерьер мебель высший первый высший второй высший первый высший 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 10 11. 10. 2010 2 2 13. 10. 2009 3 1 2 1 13. 10. 2009 4 2 2 1 14. 10. 2009 5 1 1 2 15. 10. 2009 6 3 4 5 15. 10. 2009 7 4 3 1 15. 10. 2009 8 5 5 2 16. 10. 2009 9 6 5 3 16. 10. 2009 10 8 6 4 17. 10. 2009 11 5 5 5 18. 10. 2009
Внутренние соединения (примеры) Запрос1 а. Вывести информацию о сделках с указанием всей информации о товаре Вариант1: SELECT * FROM Сделка INNER JOIN Товар ON Сделка. Код. Товара= Товар. Код. Товара; Вариант2: SELECT * FROM Сделка, Товар WHERE Сделка. Код. Товара=Товар. Код. Товара; Запрос1 а Код. Сдел ки Сделка. Код Товара Код. Клие нта 1 10 11. 10. 2010 3 1 2 1 5 1 1 2 2 1 4 2 6 Дата Товар. Код. Товар а Название Тип Сорт 1 Стул мебель высший 400, 00 р. 10 Харьков 13. 10. 2009 1 Стул мебель высший 400, 00 р. 10 Харьков 2 15. 10. 2009 1 Стул мебель высший 400, 00 р. 10 Харьков 2 13. 10. 2009 2 Стол мебель первый 200, 00 р. 20 Киев 2 1 14. 10. 2009 2 Стол мебель первый 200, 00 р. 20 Киев 3 4 5 15. 10. 2009 3 Стул мебель высший 400, 00 р. 1 Киев 7 4 3 1 15. 10. 2009 4 Диван мебель второй 4 000, 00 р. 3 Харьков 8 5 5 2 16. 10. 2009 5 Диван мебель высший 8 000, 00 р. 1 Киев 11 5 5 5 18. 10. 2009 5 Диван мебель высший 8 000, 00 р. 1 Киев 9 6 5 3 16. 10. 2009 6 Стол мебель второй 400, 00 р. 2 Москва 10 11 Кол_во 8 6 4 17. 10. 2009 8 Подсвечник интерьер первый 40, 00 р. 10 Харьков ХНУРЕ кафедра Інформатики доц. Яковлева О. В. Цена Остаток Город. Товара
Внутренние соединения (примеры) Запрос1 б. Вывести информацию о сделках с указанием названия товара, его сорта и цены за ед. Запрос1 б Код. Сделки Код. Товара Код. Клиента Кол_во Дата Название Сорт Цена 1 1 10 11. 10. 2010 Стул высший 400, 00 р. 3 1 2 1 13. 10. 2009 Стул высший 400, 00 р. 5 1 1 2 15. 10. 2009 Стул высший 400, 00 р. 2 2 13. 10. 2009 Стол первый 200, 00 р. 4 2 2 1 14. 10. 2009 Стол первый 200, 00 р. 6 3 4 5 15. 10. 2009 Стул высший 400, 00 р. 7 4 3 1 15. 10. 2009 Диван второй 4 000, 00 р. 8 5 5 2 16. 10. 2009 Диван высший 8 000, 00 р. 11 5 5 5 18. 10. 2009 Диван высший 8 000, 00 р. 9 6 5 3 16. 10. 2009 Стол второй 400, 00 р. 10 12 1 8 6 4 17. 10. 2009 Подсвечник первый 40, 00 р. ХНУРЕ кафедра Інформатики доц. Яковлева О. В.
Внутренние соединения (примеры) Запрос1 в. Вывести информацию о товарах, которые покупались Вариант1: Вариант2: Запрос_1 и Код. Товара Тип Сорт 1 Стул мебель высший 400, 00 р. 10 Харьков 2 Стол мебель первый 200, 00 р. 20 Киев 3 Стул мебель высший 400, 00 р. 1 Киев 4 Диван мебель второй 4 000, 00 р. 3 Харьков 5 Диван мебель высший 8 000, 00 р. 1 Киев 6 Стол мебель второй 400, 00 р. 2 Москва 8 13 Название Подсвечний интерьер первый 40, 00 р. 10 Харьков ХНУРЕ кафедра Інформатики доц. Яковлева О. В. Цена Остаток Город. Товара
Запрос_сл 2 Внутренние соединения (примеры) Название Стул 11. 10. 2010 10 ООО Буд Пример соединения более 2 -х таблиц Стол 13. 10. 2009 2 ООО Буд Стул 13. 10. 2009 1 ООО Ух Стол 14. 10. 2009 1 ООО Ух Стул 15. 10. 2009 2 ООО Буд Стул 15. 10. 2009 5 ООО Буд Диван 15. 10. 2009 1 ООО Буд Диван 16. 10. 2009 2 ООО Ух 3 ООО Ух 4 ООО Уют 5 ООО Ух Запрос2. Получить информацию о сделках в следующем виде: название товара, дата сделки, количество проданного товара в сделке и фирму покупателя. Дата Вариант1: Стол 16. 10. 2009 SELECT Название, Дата, Кол_во, Фирма Подсвечний 17. 10. 2009 FROM Клиент INNER JOIN Диван 18. 10. 2009 (Товар INNER JOIN Сделка ON Товар. Код. Товара=Сделка. Код. Товара) ON Клиент. Код. Клиента=Сделка. Код. Клиента; Кол_во Фирма или SELECT Название, Дата, Кол_во, Фирма FROM (Товар INNER JOIN Сделка ON Товар. Код. Товара=Сделка. Код. Товара) INNER JOIN Клиент ON Клиент. Код. Клиента=Сделка. Код. Клиента; Замечание! В SQL Server можно без () Вариант2: SELECT Название, Дата, Кол_во, Фирма FROM Клиент, Товар, Сделка WHERE Товар. Код. Товара=Сделка. Код. Товара AND Клиент. Код. Клиента=Сделка. Код. Клиента; 14 ХНУРЕ кафедра Інформатики доц. Яковлева О. В.
Внутренние соединения (примеры) Точное указание принадлежности поля Запрос3_а. Определить, на какую сумму было продано каждого товара. Выводить код товара, сумму. Запрос_сл 3 а Код. Товара Стоимость 1 2 4 000, 00 р. 5 56 000, 00 р. 6 1 200, 00 р. 8 FROM Товар INNER JOIN Сделка ON Товар. Код. Товара=Сделка. Код. Товара GROUP BY Товар. Код. Товара; Вариант2: 2 000, 00 р. 4 SELECT Товар. Код. Товара, SUM(Цена*Кол_во) AS Стоимость 600, 00 р. 3 Вариант1: 5 200, 00 р. 160, 00 р. SELECT Сделка. Код. Товара, SUM(Цена*Кол_во) AS Стоимость FROM Товар INNER JOIN Сделка ON Товар. Код. Товара=Сделка. Код. Товара GROUP BY Сделка. Код. Товара; Запрос_сл 3 б Двойная группировка Запрос3_б. Определить, на какую сумму было продано каждого товара. Код. Това Название ра Выводить код товара, наименование, сумму. 1 Стул Стоимость 5 200, 00 р. SELECT Товар. Код. Товара, Название, SUM(Цена*Кол_во) AS Стоимость 2 Стол 600, 00 р. FROM Товар INNER JOIN Сделка ON Товар. Код. Товара=Сделка. Код. Товара 3 Стул 2 000, 00 р. 4 Диван 4 000, 00 р. 5 Диван 56 000, 00 р. 6 Стол 1 200, 00 р. 8 Подсвечник GROUP BY Товар. Код. Товара, Название; 15 ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 160, 00 р.
Внутренние соединения (примеры) Запрос_сл 4 а Запрос4 а. Определить среднюю цену товара (из таблицы Товар) Средняя_Цена 2 040, 00 р. Запрос4 б. Определить среднюю стоимость сделки Запрос5 а. Подсчитать общую стоимость проданных товаров Запрос_сл 5 а Стоимость 69 160, 00 р. Запрос5 б. Подсчитать общую стоимость проданных товаров. Выводить сумму, если она меньше 5000. 16 ХНУРЕ кафедра Інформатики доц. Яковлева О. В.
Внутренние соединения (примеры) Запрос6 а. Определить, на какую сумму было продано каждого товара. Выводить код товара, его название, подсчитанную сумму, если сумма менее 5000 грн. Отсортировать по сумме по убыванию. Запрос6 а Код Товара Название Стоимость 56 000, 00 р. 4 Диван 4 000, 00 р. Стул 5 200, 00 р. 3 Стул 2 000, 00 р. 4 Диван 4 000, 00 р. 6 Стол 1 200, 00 р. 3 Стул 2 000, 00 р. 2 Стол 600, 00 р. 6 Стол 1 200, 00 р. 8 Подсвечник 160, 00 р. 5 Диван 1 Запрос6 б. Определить, на какую сумму было 2 Стол 600, 00 р. продано товаров каждого наименования. Выводить название, 8 Подсвечник 160, 00 р. подсчитанную сумму, если сумма менее 5000 грн. (Код товара не выводить). Отсортировать по сумме по убыванию. Название Стоимость SELECT Название, SUM(Цена*Кол_во) AS Стоимость Диван 60 000, 00 р. FROM Товар INNER JOIN Сделка ON Товар. Код. Товара=Сделка. Код. Товара Подсвечник 160, 00 р. GROUP BY Название Стол 1 800, 00 р. HAVING SUM(Цена*Кол_во) <5000 Стул 7 200, 00 р. ORDER BY Стоимость DESC; Запрос6 в. Определить, на какую сумму было продано каждого товара. Выводить название товара, подсчитанную сумму, если сумма менее 5000 грн. (Код товара не выводить). Отсортировать по сумме по убыванию. SELECT Название, SUM(Цена*Кол_во) AS Стоимость FROM Товар INNER JOIN Сделка ON Товар. Код. Товара=Сделка. Код. Товара GROUP BY Товар. Код. Товара, Название HAVING SUM(Цена*Кол_во) <5000 ORDER BY Стоимость DESC; Замечание! Access – псевдонимы в ORDER BY не поддерживает 17 ХНУРЕ кафедра Інформатики доц. Яковлева О. В. Запрос_сл 6 б Название Стол Подсвечник Стоимость 1 800, 00 р. 160, 00 р. Запрос6 в Название Диван Стоимость 56 000, 00 р. Название Стоимость Диван 4 000, 00 р. Стул 5 200, 00 р. Стул 2 000, 00 р. Диван 4 000, 00 р. Стол 1 200, 00 р. Стул 2 000, 00 р. Стол 600, 00 р. Стол 1 200, 00 р. Подсвечник 160, 00 р. Стол 600, 00 р. Подсвечник 160, 00 р.
Внутренние соединения (примеры) Пример соединения более 2 -х таблиц Псевдонимы SELECT Название, Дата, Кол_во, Фирма FROM (Товар AS Т INNER JOIN Сделка AS С ON Т. Код. Товара=С. Код. Товара) INNER JOIN Клиент AS К ON К. Код. Клиента=С. Код. Клиента; SELECT Т. Название, С. Дата, С. Кол_во, К. Фирма FROM (Товар Т INNER JOIN Сделка С ON Т. Код. Товара=С. Код. Товара) INNER JOIN Клиент К ON К. Код. Клиента=С. Код. Клиента Замечание! Нельзя использовать старые названия таблиц после введения псевдонимов: SELECT Товар. Название, Сделка. Дата, Сделка. Кол_во, Клиент. Фирма FROM (Товар AS Т INNER JOIN Сделка AS С ON Т. Код. Товара=С. Код. Товара) INNER JOIN Клиент AS К ON К. Код. Клиента=С. Код. Клиента; 18 ХНУРЕ кафедра Інформатики доц. Яковлева О. В.
Внешние соединения РА SQL Результат ACCESS SELECT * SQL SERVER a A b a 1 h A 1 1 h 2 2 g A 2 2 g B 1 1 h B 3 3 h B Левым внешним соединением называется соединение, при котором кортежи отношения R, не имеющие совпадающих значений в общих столбцах отношения S, также включаются в результирующее отношение. 1 A FROM R LEFT JOIN S ON R. c=S. c; R. c S. c 4 B 4 NULL c c b NULL SELECT * FROM R RIGHT JOIN S ON R. c=S. c; SELECT * FROM R FULL JOIN S ON R. c=S. c; В Access – не поддерживается; Замечания! В случае 3 и более соединений можно использовать: Access: комбинации LEFT и INNER; RIGHT и INNER; либо все LEFT, либо все RIGHT; 19 SQL Server: Можно применять несколько FULL; Любые комбинации LEFT, RIGHT; ХНУРЕ кафедра Інформатики доц. Яковлева О. В.
Внешние соединения (примеры) Запрос7 а. Вывести информацию о сделках (с полной информацией о товаре), а также товары, которые еще ни разу не продавались. Запрос7 а Товар. Код. Товара Остаток Сорт Стул мебель высший 400, 00 р. 10 Харьков 1 10 11. 10. 2010 1 1 1 Стул мебель высший 400, 00 р. 10 Харьков 3 1 13. 10. 2009 1 2 1 Стул мебель высший 400, 00 р. 10 Харьков 5 2 15. 10. 2009 1 1 2 Стол мебель первый 200, 00 р. 20 Киев 2 2 13. 10. 2009 2 1 2 Стол мебель первый 200, 00 р. 20 Киев 4 1 14. 10. 2009 2 2 3 Стул мебель высший 400, 00 р. 1 Киев 6 5 15. 10. 2009 3 4 4 Диван мебель второй 4 000, 00 р. 3 Харьков 7 1 15. 10. 2009 4 3 5 Диван мебель высший 8 000, 00 р. 1 Киев 8 2 16. 10. 2009 5 5 5 Диван мебель высший 8 000, 00 р. 1 Киев 11 5 18. 10. 2009 5 5 6 Стол мебель второй 400, 00 р. 2 Москва 9 3 16. 10. 2009 6 5 7 Рамка для фото интерьер высший 150, 00 р. 10 Москва 8 Подсвечник интерьер первый 40, 00 р. 10 Харьков 10 4 17. 10. 2009 8 6 9 Шкаф мебель высший 10 000, 00 р. 2 ХНУРЕ кафедра Інформатики доц. Яковлева О. В. Киев Код. Сделки Кол_во Дата Сделка. Код. Товара Тип 1 20 Цена Город Товара Название Код. Клиента
Внешние соединения (примеры) Запрос7 б. Вывести информацию о товарах нашего ассортимента, которые еще не продавались. Запрос7 б 7 Рамка для фото интерьер высший 150, 00 р. 9 Шкаф высший 10 000, 00 р. мебель 10 Москва Запрос7 в. Подсчитать количество товаров нашего ассортимента, которые еще не продавались Запрос7 в Кол_товаров 2 21 ХНУРЕ кафедра Інформатики доц. Яковлева О. В. 2 Киев
Исходная схема данных БД «Торговля» Товар Код Товара 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. Подсчитать общее количество купленного каждой фирмой товара и на какую сумму. Выводить название фирмы и 2. 3. 4. 5. 6. 7. 8. 9. 22 подсчитанное количество. Подсчитать на какую сумму каждая фирма купила каждого товара. Определить количество проданного товара за каждый год Определить количество проданного товара за каждый месяц каждого года, кроме 2009 г. Отсортировать по году, затем по месяцу по возрастанию. Определить на какую сумму продано товара первого сорта. Определить на какую сумму каждый год продавалось товара первого сорта. Выводить год и сумму. Результат интересует для тех лет, в которых сумма находится в диапазоне [1 000, 10 000]. Отсортировать результат по годам. Вывести ФИО клиентов, которые покупали товар более одного раза. ХНУРЕ кафедра Інформатики доц. Яковлева О. В.
Вопросы 23 ХНУРЕ кафедра Інформатики доц. Яковлева О. В.
Скачать презентацию Базы данных и информационные системы Выражение операций РА
Presentation4_SQL_diff2014_10_22_Stud_hole.pptx