Материал: Lab6R-EmbededQueries

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. Выдать преподавателей, которые имеют по крайней мере одну лекцию:

Здесь в условии 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 фонд финансирования той кафедры факультета, которая имеет максимальный фонд.

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

Фраза FROM может содержать не только список имен таблиц, но и подзапросы. Для ссылки на такие таблицыподзапросы следует приписать подзапросу алиас.

Имеется класс запросов, которые не могут быть выражены без подзапроса во фразе FROM. К ним, в частности, относятся такие запросы, которые требуют независимого вычисления двух или более запросов, и затем совместного использования результатов такого запроса.

Пример:

Выдать средний фонд финансирования факультетов и среднюю зарплату преподавателей:

SELECT Fac.AvgFund, Tch.AvgSalary

6

FROM (SELECT AVG(Fund) AS AvgFund FROM FACULTY) Fac,

(SELECT AVG(Salary) AS AvgSalary FROM TEACHER) Tch;

2.5. Подзапросы во фразе SELECT

Во фравзе SELECT можно использовать простые (независимые, несвязанные) и связанные

(коррелированные) запросы. В обоих случаях подзапрос должен возвращать одно значение.

Подзапрос является простым, если в нем не используются атрибуты таблиц, определенных в основном (внешнем запросе). При использовании простого подзапроса он вычисляется однократно и возвращенное им значение вставляется в соответствующее место во все строки, формируемые внешним запросом.

Пример. Для каждого факультета вывести его название, фонд финансирование, а также максимальный и минимальный фонды финансирования среди всех кафедрВУЗа

SELECT Name AS "Факультет",

Fund AS "Фонд факультета",

(SELECT MAX(Fund) FROM DEPARTMENT) AS "МАКС фонд кафедр", (SELECT MIN(Fund) FROM DEPARTMENT) AS "МИН фонд кафедр"

FROM FACULTY

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

Пример. По каждому факультету, расположенному в корпусе 6, вывести:

-название факультета

-количество групп этого факультета с рейтингом, более 20

-количество преподавателей-профессоров

SELECT Name AS "Факультет",

(SELECT COUNT (DISTINCT GrpPK) FROM DEPARTMENT d, SGROUP g

WHERE f.FacPK=d.FacFK AND d.DepPK=g.DepFK AND g.Rating > 20) AS "К-во групп",

WHERE Building= '6';

7

3. Варианты заданий

Далее приводится 15 вариантов заданий. Каждый вариант состоит из 7 запросов, которые относятся к следующим категориям (в порядке их следования):

1)Некоррелируемые подзапросы

2)Коррелируемые (зависимые, связанные) подзапросы

3)Коррелируемые подзапросы и предикат EXISTS

4)Коррелируемые подзапросы и предикат ANY, SOME, ALL

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

6)Подзапросы во фразе FROM

7)Подзапросы во фразе SELECT

ВНИМАНИЕ. В предлагаемых запросах используются константы (имена преподавателей, названия кафедр и факультетов, названия дисциплин), которые могут отсутствовать в вашей базе данных. ЗАМЕНЯЙТЕ ИХ НА ТЕ, КОТОРЫЕ ДЕЙСТВИТЕЛЬНО ИМЕЮТСЯ В ВАШЕЙ БАЗЕ ДАННЫХ!

3.1. Вариант 1

1) По каждой кафедере, расположенной в том же корпусе, что и факультет, деканом которого является Иванов, вывести следующую информацию в столбцах с соответствующими именами:

2) Вывести названия факультетов, которые имеют кафедры в корпусе 6

3) Вывести названия факультетов из корпуса 6 и имена их деканов, на которых имеется хотя бы одна кафедра

4) Вывести названия факультетов, фонды финансирования которых. увеличенные на 200000, больше фондов финансирования любой из их кафедр. Привести два варианта – с оператором ALL и функцией MAX.

5) Вывести такие пары значений: «название дисциплины-имя преподавателя», что

-данный преподаватель преподает эту дисциплину;

-он преподает ее более, чем 2-м группам

-он имеет больше занятий по этой дисциплине, чем преподаватель Иванов по дисциплине СУБД

6) Вывести среднее количество дисциплин на один факультет

7) По каждому факультету вывести:

-название факультета

-количество кафедр

-суммарный фонд кафедр

-количество студентов

3.2. Вариант 2

1) По каждому преподавателю факультета компьютерных наук, который имеет зарлату (salary+commission) больше, чем зарплата преподавателя Иванова с кафедры ИПО, вывести следующую информацию в столбцах с соответствующими именами:

8

2) Вывести названия факультетов, в которых имеется менее 20 профессоров

3) Вывести названия факультетов и имена их деканов, на которых имеется хотя бы один преподавательпрофессор

4) Вывести названия факультетов, фонды финансирования которых больше фондов финансирования любой из кафедр факультета компьютерных наук

5) Вывести такие тройки значений «имя преподавателя-номер группы-курс группы», что

-этот преподаватель преподает этой группе данного курса

-он преподает более одной дисциплины в этой группе этого курса

-он имеет в этой группе этого курса больше занятий, чем количество занятий преподавателя Иванова в этой же группе этого курса

6) Вывести среднее количество дисциплин на одну кафедру

7) По каждому факультету вывести:

-название факультета

-количество кафедр на факультете

-количество студентов 3-го курса на факультете

3.3. Вариант 3

1) По каждому преподавателю факультета, деканом которого является Иванов, который (преподаватель) поступил на работу позже, чем заведующий кафедры ИПО, вывести следующую информацию в столбцах с соответствующими именами:

2) Вывести названия факультетов, в которых имеется менее 5 групп третьего курса

3) Вывести названия факультетов и имена их деканов, на которых нет ни одной группы пятого курса

4) Вывести названия факультетов, которые расположены в одном из корпусов, в котором расположены ее кафедры

5) Вывести такие тройки значений «имя преподвателя-название дисциплины-номер группы», что

-данный преподаватель преподает данную дисциплину данной группе и

-он проводит занятия в этой группе по этой дисциплине в более, чем 1-й аудитории и

-у него в этой группе по этой дисциплине больше занятий, чем у любого другого преподавателя в этой группе по этой дисциплине

6) Вывести среднее количество студентов на одного преподавателя

7) По каждому факультету вывести

-название факультета

-количество групп на 3-м курсе

-количество преподавателей-доцентов

9

3.4. Вариант 4

1) По каждому преподавателю кафедры, заведующим которой является Иванов, который (преподаватель) поступил на работу в диапазоне от минимальной до максимальной дат поступления на работу преподавателей факультета компьютерных наук, вывести следующую информацию в столбцах с соответствующими именами

2) Вывести названия и корпуса факультетов, фонд финансирования которых меньше более, чем на 1000, суммарного фонда финансирования всех кафедр факультета

3) Вывести названия факультетов, которые расположены не в корпусе 5 и не имеют преподавателей, поступивших на работу в диапазоне 01.01.2000-01.06.2000

4) Вывести названия факультетов и имена их деканов, которые (факультеты) расположены в одном из корпусов, в котором расположены аудитории, в которых проводятся занятия по дисциплине СУБД

5) Вывести такие пары значений «номер группы-название дисциплины», что:

-этой группе преподается эта дисциплина и

-этой группе эту дисциплину преподает более, чем 1 преподаватель

-этой группе эта дисциплина преподается в более, чем одной аудитории

-количество лекций, читаемых этой группе по этой дисциплине, больше, чем среднее количество занятий, проводимых по всем дисциплинам

6) Вывести среднее количество студентов на один факультет

7) По каждому факультету вывести

-название факультета

-количество дисциплин, изучаемых студентами факультета

-количество дисциплин, преподаваемых преподавателями факультета

3.5. Вариант 5

1) По каждому преподавателю факультета компьютерных наук, который имеет зарплату (salary+commission) в диапазоне между минимальной и максимальной зарплатой преподавателей кафедры, заведующим которой является Иванов, вывести следующую информацию в столбцах с соответствующими именами:

2) Вывести названия факультетов и имена их деканов, в которых меньше 100 студентов 3-го курса

3) Вывести названия факультетов и имена их деканов, у которых нет кафедр с фондом финансирования, превышающим фонд финансирования факультета

4) Вывести названия кафедр факультета, деканом которого является Иванов, которые (кафедры), расположены в одном из корпусов, в котором расположены кафедры факультета компьютерных наук.

10