Материал: Lab6R-EmbededQueries
|
|
Лабораторная работа 6 |
|
|
|
Вложенные запросы (подзапросы) в SQL Oracle |
|
|
|
Содержание |
|
1. |
Цели лабораторной работы ................................................................................................................................. |
1 |
|
2. |
Теоретические основы ........................................................................................................................................... |
2 |
|
|
2.1. Подзапрос во фразе WHERE ........................................................................................................................ |
2 |
|
|
2.1.1. Подзапрос в простом условии сравнения............................................................................................................. |
2 |
|
|
2.1.2. Подзапрос в условии сравнения групп ................................................................................................................. |
4 |
|
|
2.1.3. Подзапрос в условии проверки вхождения элемента во множество ................................................................. |
5 |
|
|
2.1.4. Подзапрос в условии EXISTS................................................................................................................................ |
5 |
|
|
2.2. Связанные подзапросы.................................................................................................................................. |
5 |
|
|
2.2.1. Связанные подзапросы во фразе WHERE ............................................................................................................ |
6 |
|
|
2.3. Простые и связанные подзапросы во фразе HAVING............................................................................. |
6 |
|
|
2.4. Простые подзапросы во фразе FROM ........................................................................................................ |
6 |
|
|
2.5. Подзапросы во фразе SELECT..................................................................................................................... |
7 |
|
3. |
Варианты заданий.................................................................................................................................................. |
8 |
|
|
3.1. Вариант 1.......................................................................................................................................................... |
8 |
|
|
3.2. Вариант 2.......................................................................................................................................................... |
8 |
|
|
3.3. Вариант 3.......................................................................................................................................................... |
9 |
|
|
3.4. Вариант 4........................................................................................................................................................ |
10 |
|
|
3.5. Вариант 5........................................................................................................................................................ |
10 |
|
|
3.6. Вариант 6........................................................................................................................................................ |
11 |
|
|
3.7. Вариант 7........................................................................................................................................................ |
11 |
|
|
3.8. Вариант 8........................................................................................................................................................ |
12 |
|
|
3.9. Вариант 9........................................................................................................................................................ |
12 |
|
|
3.10. |
Вариант 10 ........................................................................................................................................... |
13 |
|
3.11. |
Вариант 11 ........................................................................................................................................... |
14 |
|
3.12. |
Вариант 12 ........................................................................................................................................... |
14 |
|
3.13. |
Вариант 13 ........................................................................................................................................... |
15 |
|
3.14. |
Вариант 14 ........................................................................................................................................... |
15 |
|
3.15. |
Вариант 15 ........................................................................................................................................... |
16 |
|
3.16. |
Вариант 16 ........................................................................................................................................... |
16 |
|
3.17. |
Вариант 17 ........................................................................................................................................... |
17 |
|
3.18. |
Вариант 18 ........................................................................................................................................... |
18 |
4. |
Контрольные вопросы.......................................................................................................................................... |
18 |
|
1.Цели лабораторной работы
Изучить возможности SQL Oracle по формулировке и обработке подзапросов.
Приобрести практический опыт по формулировке и обработке подзапросов с использованием
SQL*Plus.
1
2. Теоретические основы
Запрос – это операция, которая позволяет отыскивать данные из одной или несколько таблиц. При наличии вложенных запросов запрос верхнего уровня называется предложением SELECT, а запрос, вложенный в предложение SELECT называется подзапросом. Таким образом, подзапрос (вложенный запрос) – это запрос, результат которого передается в качестве аргумента в другой запрос. Подзапросы позволяют связывать в единое целое несколько запросов.
Подзапросы используются для:
определения множества строк, который должны быть вставлены в целевую таблицу в предложениях
INSERT или CREATE TABLE;
определения одного или более значений, присваиваемых существующим строка в предложении
UPDATE;
получения значений для фраз WHERE, HAVING или START WITH в предложениях SELECT, UPDATE, и
DELETE;
определения значений указанного столбца в списке INSERT ... VALUES;
определения таблицы, которая используются соответствующим запросом.
Это производится путем размещения подзапроса во фразе FROM соответствующего запроса как если бы это было именем таблицы. Вы можете также использовать таким образом подзапросы вместо таблиц в предложениях INSERT, UDPATE и DELETE.
Используемые таким образом подзапросы могут использовать переменные связывания (correlation variables), однако только такие, которые определены только в самом подзапросе, ссылки на внешние переменные не допустимы. Внешние ссылки (подзапросы с левой корреляцией - leftcorrelated subqueries) допустимы только во фразе FROM предложения SELECT .
Подзапрос дает ответ на содержательные запросы, имеющие сложную структуру. Например, для определения, кто работает на кафедре Иванова, вы сначала используете подзапрос для определения кафедры, на которой работает Иванов, а затем отвечаете на основной запрос путем формулировки предложения
SELECT.
Подзапрос может содержать другие подзапросы. Oracle не ограничивает глубину вложенности подзапросов.
Если таблица в подзапросе имеет такое же имя, что и таблица внешнего запроса, то для ссылки на столбцы внешнего запроса их необходимо уточнять именем таблицы или алиасом таблицы. Чтобы ваши запросы было легче воспринимать, всегда квалифицируйте столбцы в подзапросе именем или алиасом таблицы.
Oracle выполняет корреляционные (связанный) подзапрос, когда подзапрос ссылается на столбец таблицы внешнего запроса. Связанный подзапрос вычисляется для каждой строки, обрабатываемой внешним предложением. Внешним предложением может быть SELECT, UPDATE или DELETE.
Связанный подзапрос дает ответы на такие содержательные запросы, ответы которых требуют вычисления подзапросов для каждой строки внешнего запроса. Например, связанный подзапрос используется для определения преподавателей, которые зарабатывают больше, чем средняя зарплата по кафедре. В этом случае связанный подзапрос для каждого преподавателя вычисляет среднюю зарплату на его кафедре.
Далее мы обсудим использование подзапросов в предложении SELECT.
2.1. Подзапрос во фразе WHERE
2.1.1. Подзапрос в простом условии сравнения
Синтаксис:
2
Описание:
При использовании простых условий сравнения с подзапросом во фразе WHERE применяются следующие правила:
Подзапрос должен возвращать единственную строку.
Если левая часть равна expr, то подзапрос должен возвращать единственную строку с единственным значением с типом, совместимым с типом expr.
Если левая часть является списком выражений (expr_list), то подзапрос должен возвращать единственную строку со списком значений, который соответствует по количеству и типу значениям из expr_list. В этом случае оператор сравнения дает TRUE, если каждое значение в expr_list равно (в случае =) или не равно (в случае !=, ^=, <>) каждому значение, возвращаемому подзапросом.
Примеры:
1. Выбрать кафедры, которые располагаются в том же корпусе, что факультет информатики:
SELECT Name
FROM DEPARTMENT
WHERE Building = (SELECT Building
FROM FACULTY
WHERE UPPER(Name) = 'INFORMATICS');
2. Выбрать факультеты, чьи фонды меньше фонда кафедры CAD:
SELECT Name FROM FACULTY
WHERE Fund < (SELECT Fund
FROM DEPARTMENT
WHERE UPPER(Name) = 'CAD');
3. Выбрать преподавателей, у которых salary + commission превышает более чем на 100 половину salary + commission преподавателя Bill:
SELECT Name FROM TEACHER
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
3
FROM TEACHER
WHERE UPPER(Name) = 'BILL');
2.1.2. Подзапрос в условии сравнения групп
Синтаксис:
Описание:
При использовании условий сравнения групп с подзапросом во фразе WHERE применяются следующие правила:
Подзапрос может возвращать ноль или более строк.
Если левая часть равна expr, то подзапрос должен возвращать строки с единственным значением, которые совместимы по типу с expr.
Если левая часть равна expr_list, то подзапрос должен возвращать строки со списком значений, который соответствует по количеству и типу с expr_list.
ANY и SOME эквивалентны и сравнивают значение слева с каждым значением списка справа, возвращаемого подзапросом. Подзапрос может вернуть ноль или более строк. Условие равно TRUE, если по крайней мере одна строка подзапроса удовлетворяет условию (соответствует оператору сравнения) по отношению к значению (списку значений) определенному левым операндом, в противном получаем FALSE. Если подзапрос не возвращает строк, то получаем FALSE.
ALL сравнивают значение слева с каждым значением списка справа, возвращаемого подзапросом. Дает TRUE, если ВСЕ строки, возвращаемые подзапросом, удовлетворяют условию (соответствуют оператору сравнения) по отношению к значению (списку значений) определенному левым операндом, в противном получаем FALSE. Если подзапрос не возвращает строк, то получаем TRUE
Примеры:
1. Выдать кафедры, фонд которых больше фонда по крайней мере одного из факультетов:
SELECT Name
FROM DEPARTMENT
WHERE Fund > ANY (SELECT Fund FROM FACULTY);
ANY, ALL и агрегатные функции. Обратите внимание, что <ANY эквивалентно утверждению: “левое значение меньше, чем максимальное значение из множества, задаваемого правым операндом”, а оператор >ANY эквивалентен следующему утверждению “левое значение больше, чем минимальное значение из множества, задаваемого правым операндом”. Поэтому операторы ANY могут быть выражены через функции MAX и MIN в подзапросе. В свою очередь, <ALL (>ALL) эквивалентно утверждению: «левое значение меньше (больше), чем минимальное (максимальное) значение из множества, задаваемого правым операндом ».
2. Выдать кафедры, фонд которых больше фонда по крайней мере одного из факультетов:
4
SELECT |
Name |
FROM |
DEPARTMENT |
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);
2.1.3. Подзапрос в условии проверки вхождения элемента во множество
Синтаксис:
Описание:
Это условие в таком синтаксисе проверят вхождение элемента (списка элементов) во множество (множество списков), создаваемое подзапросом.
Пример:
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'); |
2.1.4. Подзапрос в условии EXISTS
Синтаксис:
Описание:
Дает TRUE, если подзапрос возвращает по крайней мере одну строку.
Так как EXISTS обычно используется в связанных подзапросах, мы его обсудим подробнее позже.
2.2. Связанные подзапросы
Для того, чтобы связать подзапрос с внешним запросом (предложением), необходимо в подзапросе была ссылка на столбец внешнего запроса. Подзапрос вычисляется для каждой строки, обрабатываемой внешним запросом (предложением). В качестве внешнего предложения могут выступать 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);
5