БАЗЫ ДАННЫХ

1. БАЗЫ ДАННЫХ ЗАНЯТИЕ 7 Создание сложных запросов. Запросы на нескольких таблицах. Печенізька Ю.О. Учитель информатики Харьковская общеобразовательная школа І-ІІІ ступеней № 60 Харьковского городского совета Харьковской области

2. Цель: Изучить возможности обращаться с запросами к нескольким таблицам; возможности использования подзапросов и операторов EXISTS, ANY, SOME и UNION для построения сложных запросов. Содержание: • Соединение таблиц. Эквивалентные и другие виды соединений. • Соединения таблицы самой с собой. • Использование подзапросов. • Операторы EXISTS, ALL, ANY, SOME и UNION. • Интерактивный и вложенный SQL

3. Задание: напишите следующие запросы Запрос № 32: покаждому городу посчитать количество клиентов с рейтингом выше 150. SELECT City, COUNT (SNum) FROM C WHERE Rating>150 GROUP BY City; Запрос № 33: покаждому размеру комиссионных посчитать количество служащих и вывести, если это количество больше 3. SELECT Comm, COUNT (SNum) FROM S GROUP BY Comm HAVING COUNT (SNum)>3;

4. Соединение таблиц /запросы из нескольких таблиц/ SELECTимена полейFROMимена таблицWHEREусловие соединения таблиц; Запрос № 34: вывести имена служащих и клиентов, которых они обслуживают. SELECT SName,CName FROM S, C WHERE S.SNum=C.SNum; Результат:

5. Примеры построения сложных запросов Запрос № 36: вывести имена служащих и клиентов, проживающих в одном городе. SELECT S.City,S.Snum, S.SName, C.CNum, C.CName FROM S, C WHERE S.City=C.City; Запрос № 37: для каждого служащего посчитать количество клиентов, которых он обслуживает. Результат: SELECT S.SNum,S.SName, COUNT (*) FROM S, C WHERE S.SNum=C.SNum GROUP BY S.SNum,S.SName; Запрос № 38: вывести имена служащих, клиентов и суммы операций, которые были осуществлены. Результат: SELECT S.SNum, S.SName,C.CNum, C.CName, Summa FROM S,C,O WHERE S.SNum=C.SNum AND C.CNum=O.CNum;

6. Соединение таблицы самой с собой Запрос № 35: вывести пары имен служащих, имеющих одинаковые комиссионные. S S1 S2 a) SELECT S1.SNum, S1.SName, S2.SNum, S2.SName FROM S S1, S S2 WHERE S1.Comm=S2.Comm; b) SELECT S1.SNum, S1.SName, S2.SNum, S2.SName FROM S S1, S S2 WHERE S1.Comm=S2.Comm AND S1.SNum<S2.SNum; Результат:

7. Использование подзапросов SELECT … (SELECT … ); Внешний запрос Подзапрос (внутренний запрос) • в предложении WHERE: SELECT … WHERE <поле><оператор>(SELECT … ); Запрос № 39: вывести информацию о служащих, комиссионные которых выше среднего. SELECT * FROM S WHERE Comm>(Select AVG (Comm) From S); Результат: Comm>0.12222222

8. Примеры запросов с подзапросами Запрос № 40: вывести информацию о клиентах, рейтинг которых выше максимального рейтинга по городу Харькову. SELECT * FROM C WHERE Rating>(SELECT MAX(Rating) FROM C WHERE City=‘Харьков’); Запрос № 41: вывести имена клиентов, которые живут в тех городах, которые обслуживает служащий номер 101. SELECT C.CNum, C.CName FROM C WHERE City IN (SELECT City From S WHERE SNum=101); • в предложении HAVING: Запрос № 42: вывести города, максимальные комиссионные в которых выше средних комиссионных в городе Москва. SELECT City, MAX(Comm) FROM S GROUP BY City HAVING MAX(Comm)> (SELECT AVG(Comm) FROM S WHERE City=‘Москва’);

9. Использование операторов EXISTS и ALL • EXISTS (использует результат подзапроса, чтобы указать, нужно ли выполнять главный запрос); Запрос № 43: вывести информацию о служащих, если хотя бы один из них проживает в городе Харькове. SELECT * FROM S WHERE EXISTS (SELECT * From S WHERE City=‘Харьков’); • ALL (предикат является верным, если каждое значение выбранное подзапросом удовлетворяет условию в предикате внешнего запроса ): Запрос № 44: вывести информацию о клиентах, которые имеют рейтинг выше, чем рейтинг любого клиента из города Киева. SELECT * FROM C WHERE Rating > ALL (SELECT Rating FROM C WHERE City=‘Киев’);

10. Использование операторов ANY/SOME • ANY / SOME (похожий на EXISTS, но используются с реляционными операторами); Запрос № 45: вывести информацию о клиентах, которые имеют рейтинг выше рейтинга хотя бы одного клиента из города Киева. SELECT * FROM C WHERE Rating > ANY (SELECT Rating FROM C WHERE City=‘Киев’); SELECT * FROM C WHERE Rating > SOME (SELECT Rating FROM C WHERE City=‘Киев’); Запрос № 46: найти служащих, проживающих в городах, где есть клиенты. SELECT Sname, City FROM S WHERE City=ANY (SELECT DISTINCT City FROM C);

11. Оператор UNION Оператор UNION – позволяет объединить результаты нескольких запросов. SELECT… UNION SELECT …. UNION … SELECT …; • Правила объединения запросов: • Объединяемые запросы должны возвращать одинаковое количество полей; • Объединяемые запросы должны возвращать однотипные соответствующие поля.

12. Пример использования оператора UNION В (Ведомость по «ОБД») Запрос № 48: получить статистику о сдаче эксамена по дисциплине в форме: SELECT ‘Оценку 5 имеет’, COUNT(*), ‘человека’ FROM B WHERE Mark = ‘отлично’ UNION SELECT ‘Оценку 4 имеет’, COUNT(*), ‘человека’ FROM B WHERE Mark = ‘хорошо’ UNION SELECT ‘Оценку 3 имеет’, COUNT(*), ‘человека’ FROM B WHERE Mark = ‘удовлетворительно’;

13. Интерактивный и вложенный SQL Различают: интерактивный SQL (используется для функционирования непосредственно в БД); вложенный SQL (помещается внутри программ, написанных на другом языке /в Delphi (Pascal), Builder(C++), Oracle(PL/SQL)/.