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

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

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

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

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

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

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

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

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

10 Запрос 46: найти служащих, проживающих в городах, где есть клиенты. ANY / SOME (похожий на EXISTS, но используются с реляционными операторами); SELECT Sname, City FROM S WHERE City=ANY (SELECT DISTINCT City FROM C); Запрос 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=Киев); Использование операторов ANY/SOME

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

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

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