Скачати 100.74 Kb.
|
Лабораторная работа 6 Вложенные запросы (подзапросы) в SQL Oracle Содержание 1. Цели лабораторной работы 1 2. Теоретические основы 1 2.1. Подзапрос во фразе WHERE 2 2.1.1. Подзапрос в простом условии сравнения 2 2.1.2. Подзапрос в условии сравнения групп 3 2.1.3. Подзапрос в условии проверки вхождения элемента во множество 4 2.1.4. Подзапрос в условии EXISTS 5 2.2. Связанные подзапросы 5 2.2.1. Связанные подзапросы во фразе WHERE 5 2.3. Простые и связанные подзапросы во фразе HAVING 6 2.4. Простые подзапросы во фразе FROM 6 3. Задание 6 4. Контрольные вопросы 7 ^
Запрос – это операция, которая позволяет отыскивать данные из одной или несколько таблиц. При наличии вложенных запросов запрос верхнего уровня называется предложением SELECT, а запрос, вложенный в предложение SELECT называется подзапросом. Таким образом, подзапрос (вложенный запрос) – это запрос, результат которого передается в качестве аргумента в другой запрос. Подзапросы позволяют связывать в единое целое несколько запросов. Подзапросы используются для:
Это производится путем размещения подзапроса во фразе FROM соответствующего запроса как если бы это было именем таблицы. Вы можете также использовать таким образом подзапросы вместо таблиц в предложениях INSERT, UDPATE и DELETE. Используемые таким образом подзапросы могут использовать переменные связывания (correlation variables), однако только такие, которые определены только в самом подзапросе, ссылки на внешние переменные не допустимы. Внешние ссылки (подзапросы с левой корреляцией - left-correlated subqueries) допустимы только во фразе FROM предложения SELECT . Подзапрос дает ответ на содержательные запросы, имеющие сложную структуру. Например, для определения, кто работает на кафедре Иванова, вы сначала используете подзапрос для определения кафедры, на которой работает Иванов, а затем отвечаете на основной запрос путем формулировки предложения SELECT. Подзапрос может содержать другие подзапросы. Oracle не ограничивает глубину вложенности подзапросов. Если таблица в подзапросе имеет такое же имя, что и таблица внешнего запроса, то для ссылки на столбцы внешнего запроса их необходимо уточнять именем таблицы или алиасом таблицы. Чтобы ваши запросы было легче воспринимать, всегда квалифицируйте столбцы в подзапросе именем или алиасом таблицы. Oracle выполняет корреляционные (связанный) подзапрос, когда подзапрос ссылается на столбец таблицы внешнего запроса. Связанный подзапрос вычисляется для каждой строки, обрабатываемой внешним предложением. Внешним предложением может быть SELECT, UPDATE или DELETE. Связанный подзапрос дает ответы на такие содержательные запросы, ответы которых требуют вычисления подзапросов для каждой строки внешнего запроса. Например, связанный подзапрос используется для определения преподавателей, которые зарабатывают больше, чем средняя зарплата по кафедре. В этом случае связанный подзапрос для каждого преподавателя вычисляет среднюю зарплату на его кафедре. Далее мы обсудим использование подзапросов в предложении SELECT. ^ 2.1.1.Подзапрос в простом условии сравненияСинтаксис: ![]() Описание: При использовании простых условий сравнения с подзапросом во фразе WHERE применяются следующие правила:
Примеры: 1. Выбрать кафедры, которые располагаются в том же корпусе, что факультет информатики: SELECT Name ^ WHERE Building = (SELECT Building FROM FACULTY WHERE UPPER(Name) = 'INFORMATICS'); 2. Выбрать факультеты, чьи фонды меньше фонда кафедры CAD: SELECT Name FROM FACULTY WHERE Fund < (SELECT Fund ^ WHERE UPPER(Name) = 'CAD'); 3. Выбрать преподавателей, у которых salary + commission превышает более чем на 100 половину salary + commission преподавателя Bill: SELECT Name ^ WHERE Salary + Commission + 100 > (SELECT (Salary + Commission) / 2 FROM TEACHER WHERE UPPER(Name) = 'BILL'); 4. Выбрать преподавателей, которые работают на той же кафедре, что и Bill и занимают ту же должность, что и Bill: SELECT Name FROM TEACHER WHERE (DepNo, Post) = (SELECT DepNo, Post FROM TEACHER WHERE UPPER(Name) = 'BILL'); ^ Синтаксис: ![]() Описание: При использовании условий сравнения групп с подзапросом во фразе WHERE применяются следующие правила:
ANY и SOME эквивалентны и сравнивают значение слева с каждым значением списка справа, возвращаемого подзапросом. Подзапрос может вернуть ноль или более строк. Условие равно TRUE, если по крайней мере одна строка подзапроса удовлетворяет условию (соответствует оператору сравнения) по отношению к значению (списку значений) определенному левым операндом, в противном получаем FALSE. Если подзапрос не возвращает строк, то получаем FALSE. ALL сравнивают значение слева с каждым значением списка справа, возвращаемого подзапросом. Дает TRUE, если ВСЕ строки, возвращаемые подзапросом, удовлетворяют условию (соответствуют оператору сравнения) по отношению к значению (списку значений) определенному левым операндом, в противном получаем FALSE. Если подзапрос не возвращает строк, то получаем TRUE Примеры: 1. Выдать кафедры, фонд которых больше фонда по крайней мере одного из факультетов: SELECT Name ^ WHERE Fund > ANY (SELECT Fund FROM FACULTY); ANY и агрегатные функции. Обратите внимание, что 2. Выдать кафедры, фонд которых больше фонда по крайней мере одного из факультетов: SELECT Name ^ WHERE Fund > ANY (SELECT Fund FROM FACULTY); SELECT Name FROM DEPARTMENT WHERE Fund > (SELECT MIN(Fund) FROM FACULTY); 3. Выдать группы, которые имеют рейтинг больше, чем рейтинг всех групп пятого курса кафедры “DBMS”: SELECT Num FROM SGROUP WHERE Rating >ALL (SELECT Rating FROM SGROUP, DEPARTMENT WHERE SGROUP.DepNo = DEPARTMENT.DepNo AND UPPER(DEPARTMENT.Name) = 'DBMS' AND SGROUP.Course = 5); ^ Синтаксис: ![]() Описание: Это условие в таком синтаксисе проверят вхождение элемента (списка элементов) во множество (множество списков), создаваемое подзапросом. Пример: 1. Выбрать преподавателей, которые имеют лекции по крайней мере одному такому предмету, по которым читает лекции преподаватель Bill: SELECT Name FROM TEACHER T, LECTURE L WHERE T.TchNo = L.TchNo AND SbjNo IN (SELECT SbjNo FROM TEACHER TCH, LECTURE LEC WHERE TCH.TchNo = LEC.TchNo AND UPPER(TCH.Name) = 'BILL'); ^ Синтаксис: ![]() Описание: Дает TRUE, если подзапрос возвращает по крайней мере одну строку. Так как EXISTS обычно используется в связанных подзапросах, мы его обсудим подробнее позже. ^ Для того, чтобы связать подзапрос с внешним запросом (предложением), необходимо в подзапросе была ссылка на столбец внешнего запроса. Подзапрос вычисляется для каждой строки, обрабатываемой внешним запросом (предложением). В качестве внешнего предложения могут выступать SELECT, UPDATE или DELETE. Следующие примеры дают общий синтаксис использования связанных подзапросов: SELECT select_list FROM table1 t_alias1 WHERE expr operator (SELECT column_list FROM table2 t_alias2 WHERE t_alias1.column operator t_alias2.column); UPDATE table1 t_alias1 SET column = (SELECT expr FROM table2 t_alias2 WHERE t_alias1.column = t_alias2.column); DELETE FROM table1 t_alias1 WHERE column operator (SELECT expr FROM table2 t_alias2 WHERE t_alias1.column = t_alias2.column); Далее мы обсудим использование связанных подзапросов во фразе WHERE предложения SELECT. ^ Примеры: 1. Выдать преподавателей, которые имеют по крайней мере одну лекцию: SELECT Name FROM TEACHER ^ FROM LECTURE WHERE LECTURE.TchNo = TEACHER.TchNo); Здесь в условии LECTURE.TchNo = TEACHER.TchNo подзапроса мы ссылаемся на внешний запрос. Поэтому подзапрос является связанным. 2. Выдать преподавателей, которые не имеют ни одной лекции: SELECT Name FROM TEACHER WHERE NOT EXISTS (SELECT * FROM LECTURE WHERE LECTURE.TchNo = TEACHER.TchNo); ^ Вы можете использовать простые и связанные подзапросы во фразе HAVING. Если вы используете связанный подзапрос в фразе HAVING, то в подзапросе можно ссылаться на те столбцы внешнего запроса, которые могут использоваться в фразе HAVING (обычно это столбцы, по которым производится группирование). Примеры: 1. Перечислить факультеты, у которых сумма фондов финансирования всех их кафедр превышает более чем на 20000 фонд финансирования той кафедры факультета, которая имеет максимальный фонд. SELECT F1.Name FROM FACULTY F1, DEPARTMENT D1 WHERE F1.FacNo = D1.FacNo GROUP BY F1.Name HAVING SUM(D1.Fund) > (SELECT 200000 + MAX(D2.Fund) FROM FACULTY F2, DEPARTMENT D2 WHERE F2.FacNo = D2.FacNo AND F1.Name = F2.Name); ^ Фраза FROM может содержать не только список имен таблиц, но и подзапросы. Для ссылки на такие таблицы-подзапросы следует приписать подзапросу алиас. Имеется класс запросов, которые не могут быть выражены без подзапроса во фразе FROM. К ним, в частности, относятся такие запросы, который требуют независимого вычисления двух или более запросов, и затем совместного использования результатов такого запроса. Пример: Выдать средний фонд финансирования факультетов и среднюю зарплату преподавателей: SELECT Fac.AvgFund, Tch.AvgSalary FROM (SELECT AVG(Fund) AS AvgFund FROM FACULTY) Fac, (SELECT AVG(Salary) AS AvgSalary FROM TEACHER) Tch; 3.ЗаданиеСформулируйте предложения SQL SELECT, которые соответствуют следующим содержательным запросам: Простые и вложенные запросы:
Одна и та же таблица во внешнем и вложенном запросе:
Связанные запросы во фразе WHERE
EXISTS во фразе WHERE:
ANY, SOME и ALL во фразе WHERE
Агрегатные функции в подзапросе
Подзапросы во фразе HAVING
Ответьте на следующие вопросы:
|