Лабораторная работа 6 Вложенные запросы (подзапросы) в sql oracle Содержание Цели лабораторной работы 1 Теоретические основы 1

Назва	Лабораторная работа 6 Вложенные запросы (подзапросы) в sql oracle Содержание Цели лабораторной работы 1 Теоретические основы 1
Дата	26.10.2012
Розмір	100.74 Kb.
Тип	Лабораторная работа

Зміст

1.Цели лабораторной работы
2.Теоретические основы
2.1.Подзапрос во фразе WHERE
From department
From department
From teacher
2.1.2.Подзапрос в условии сравнения групп
From department
From department
2.1.3.Подзапрос в условии проверки вхождения элемента во множество
2.1.4.Подзапрос в условии EXISTS
2.2.Связанные подзапросы
2.2.1.Связанные подзапросы во фразе WHERE
Where exists (select
2.3.Простые и связанные подзапросы во фразе HAVING
2.4.Простые подзапросы во фразе FROM
4.Контрольные вопросы

Лабораторная работа 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

1.Цели лабораторной работы

Изучить возможности SQL Oracle по формулировке и обработке подзапросов.
Приобрести практический опыт по формулировке и обработке подзапросов с использованием SQL*Plus.

2.Теоретические основы

Запрос – это операция, которая позволяет отыскивать данные из одной или несколько таблиц. При наличии вложенных запросов запрос верхнего уровня называется предложением SELECT, а запрос, вложенный в предложение SELECT называется подзапросом. Таким образом, подзапрос (вложенный запрос) – это запрос, результат которого передается в качестве аргумента в другой запрос. Подзапросы позволяют связывать в единое целое несколько запросов.

Подзапросы используются для:

определения множества строк, который должны быть вставлены в целевую таблицу в предложениях INSERT или CREATE TABLE;
определения одного или более значений, присваиваемых существующим строка в предложении UPDATE;
получения значений для фраз WHERE, HAVING или START WITH в предложениях SELECT, UPDATE, и DELETE;
определения значений указанного столбца в списке INSERT ... VALUES;
определения таблицы, которая используются соответствующим запросом.

Это производится путем размещения подзапроса во фразе FROM соответствующего запроса как если бы это было именем таблицы. Вы можете также использовать таким образом подзапросы вместо таблиц в предложениях INSERT, UDPATE и DELETE.

Используемые таким образом подзапросы могут использовать переменные связывания (correlation variables), однако только такие, которые определены только в самом подзапросе, ссылки на внешние переменные не допустимы. Внешние ссылки (подзапросы с левой корреляцией - left-correlated subqueries) допустимы только во фразе FROM предложения SELECT .

Подзапрос дает ответ на содержательные запросы, имеющие сложную структуру. Например, для определения, кто работает на кафедре Иванова, вы сначала используете подзапрос для определения кафедры, на которой работает Иванов, а затем отвечаете на основной запрос путем формулировки предложения SELECT.

Подзапрос может содержать другие подзапросы. Oracle не ограничивает глубину вложенности подзапросов.

Если таблица в подзапросе имеет такое же имя, что и таблица внешнего запроса, то для ссылки на столбцы внешнего запроса их необходимо уточнять именем таблицы или алиасом таблицы. Чтобы ваши запросы было легче воспринимать, всегда квалифицируйте столбцы в подзапросе именем или алиасом таблицы.

Oracle выполняет корреляционные (связанный) подзапрос, когда подзапрос ссылается на столбец таблицы внешнего запроса. Связанный подзапрос вычисляется для каждой строки, обрабатываемой внешним предложением. Внешним предложением может быть SELECT, UPDATE или DELETE.

Связанный подзапрос дает ответы на такие содержательные запросы, ответы которых требуют вычисления подзапросов для каждой строки внешнего запроса. Например, связанный подзапрос используется для определения преподавателей, которые зарабатывают больше, чем средняя зарплата по кафедре. В этом случае связанный подзапрос для каждого преподавателя вычисляет среднюю зарплату на его кафедре.

Далее мы обсудим использование подзапросов в предложении SELECT.
^

2.1.Подзапрос во фразе WHERE

2.1.1.Подзапрос в простом условии сравнения

Синтаксис:

Описание:

При использовании простых условий сравнения с подзапросом во фразе 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

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 и агрегатные функции. Обратите внимание, что левое значение меньше, чем максимальное значение из множества, задаваемого правым операндом”, а оператор >ANY эквивалентен следующему утверждению “левое значение больше, чем минимальное значение из множества, задаваемого правым операндом”. Поэтому операторы ANY могут быть выражены через функции MAX и MIN в подзапросе.

2. Выдать кафедры, фонд которых больше фонда по крайней мере одного из факультетов:

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);

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.
^

2.2.1.Связанные подзапросы во фразе WHERE

Примеры:

1. Выдать преподавателей, которые имеют по крайней мере одну лекцию:

SELECT Name

FROM TEACHER

^ WHERE EXISTS (SELECT *

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);
^

2.3.Простые и связанные подзапросы во фразе HAVING

Вы можете использовать простые и связанные подзапросы во фразе 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);
^

2.4.Простые подзапросы во фразе FROM

Фраза 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, которые соответствуют следующим содержательным запросам:

Простые и вложенные запросы:

Выдать кафедры, которые располагаются в том же корпусе, что и факультет информатики
Выдать факультеты, которые имеют фонд финансирования меньше, чем фонд финансирования кафедры SE.

Одна и та же таблица во внешнем и вложенном запросе:

Выдать преподавателей, у которых Salary+Commission больше, чем половина Salary + Commission преподавателя Andrew.

Связанные запросы во фразе WHERE

Выдать факультеты, которые имеют кафедры в корпусе 5.
Выдать преподавателей, которые имеют менее 3 лекций на 1-й неделе.
Выдать корпуса, в которых располагается только один факультет.

EXISTS во фразе WHERE:

Выдать преподавателя, который имеет по крайней мере одну лекцию в понедельник первой недели.
Выдать преподавателей-профессоров, которые не являются кураторами групп первого курса.

ANY, SOME и ALL во фразе WHERE

Выдать преподавателей кафедры CAD, у которых ставка меньше по крайней мере одного из преподавателей кафедры SE (использовать оператор < ANY).
Выдать кафедры, у которых фонд меньше на 20000 фонда по крайней мере одного из факультетов (привести два варианта: с оператором
Выдать группы, у которых рейтинг больше, чем во всех группах пятого курса кафедры (привести два варианта: с оператором >ANY и с оператором EXISTS)

Агрегатные функции в подзапросе

Выдать преподавателей кафедры SE, у которых Salary+Commission больше, чем среднее Salary+Commission в вузе.
Выдать факультеты, у которых менее 3 кафедр.
Выдать преподавателей, которые имеют более 2 лекций на 1-й неделе.

Подзапросы во фразе HAVING

Выдать преподавателей кафедры SE, которые имеют больше лекций, чем любой из преподавателей кафедры DBMS.

4.Контрольные вопросы

Ответьте на следующие вопросы:

В каких фразах предложения SELECT может использоваться подзапрос?
Что такое связанный подзапрос? Как подзапрос становится связанным? Как он вычисляется?
С какими операторами может использоваться подзапрос во фразе WHERE?
Какие правила использования подзапроса в простых условий сравнения?
Какие правила связывания подзапроса во фразе HAVING?
Приведите пример, когда запрос не может быть выражен иначе, чем использование подзапроса во фразе FROM.

Схожі:

	Лабораторная работа 3 Манипулирование данными в sql oracle Содержание Цели лабораторной работы 1 Теоретические основы 1 Изучить возможности sql oracle по вставке, обновлению и удалению строк в таблице		Лабораторная работа 2 Ограничения целостности в sql oracle Содержание Цели лабораторной работы 1 Теоретические основы 1 Синтаксис 2 Изучить возможности sql oracle по описанию и поддержанию ограничений целостности
	Лабораторная работа 4 Предложение select в sql oracle. Основные возможности Содержание Цели лабораторной работы 2 Теоретические основы 2 Приобрести практический опыт по поиску данных в таблицах базы данных с использованием sql*Plus		Лабораторная работа 5 Функции sql oracle. Дополнительные фразы предложения select содержание Цели лабораторной работы 1 Теоретические основы 1 Изучить функции sql oracle, а также фразы group by, having, order by предложения select
	Лабораторная робота 1 Создание, изменение и удаление таблиц в sql oracle Содержание Приобрести практический опыт по созданию, изменению и удалению таблиц в sql*Plus		Тема лабораторной работы Лабораторная работа №2 (это пример выполнения лаб работы, индивидуальное задание находится в пункте 4)
	Access вложенные запросы Основные и вложенные запросы		Лабораторная работа Использование редактора запросов sql borland sql explorer(TM) для создания запросов к данным с использованием языка sql. Требования к программному обеспечению Лабораторная работа Использование редактора запросов sql borland sql explorer(TM) для создания запросов к данным с использованием...
	Лабораторная работа Использование редактора запросов sql borland sql explorer(TM) для знакомства с основными объектами баз данных. Требования к программному обеспечению ...		Методические указания для самостоятельной работы студентов при подготовке к лабораторной работе Снятие спектральной характеристики уха на пороге слышимости. Лабораторная работа №3

Додайте кнопку на своєму сайті:
Документи

База даних захищена авторським правом ©zavantag.com 2000-2013
При копіюванні матеріалу обов'язкове зазначення активного посилання відкритою для індексації.
звернутися до адміністрації