Информатика и выч. техника \ Базы и банки данных

История развития баз данных. Основные понятия и определения. Архитектура базы данных. Физическая и логическая независимость, страница 17

HAVING AVG (ocen) < (SELECT MAX(ocen) – 1 FROM оценка 02 WHERE 01.opnum =02.opnum)

Результат:

opnum

AVG

003

3,67

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

При использовании вложенных подзапросов в условных выражениях внешнего подзапроса могут использоваться служебные операторы EXISTS, ANY, ALL, SOME.

EXISTS – это оператор, генерирующий значения истинно или ложно. Это значит, что его можно использовать самостоятельно в условиях или комбинировать с логическими выражениями с помощью AND, ORили NOT. Используя подзапрос в качестве аргумента, этот оператор возвращает истину, если результат подзапроса не пуст.

Пример:

Получить фамилии преподавателей, принимавших экзамен с 10.01.01 по 11.01.01.

SELECT pname FROM преподавательWHERE EXISTS (SELECT * FROM оценкаWHERE odate >= 10/01/01 AND odate =< 11/01/01 AND pnum = opnum);

Результат:

pname

П-1

В этом запросе не имеет значения, какие поля заданы в предложении SELECT внутреннего подзапроса. Важно, в принципе, наличие строк в таблице Оценка, удовлетворяющих условию. Эту задачу можно решить и с помощью соединения таблиц.

Пример:

Получить фамилии преподавателей, принимавших экзамен с 10.01.01 по 11.01.01.

SELECT DISTINCT FROM преподаватель, оценка WHERE odate >= 10/01/01 AND =< 11/01/01 AND pnum = opnum;

Результат:

pname

П-1

Данный запрос выглядит короче, но с EXISTS эффективнее работает. По следующим причинам:

  1. Для каждой записи таблицы Преподаватель таблица Оценка просматривается только до первого выполнения условия.
  2. Из выходных данных внешнего запроса не требуется удалять совпадающие значения.

Рассмотрим другой пример использования EXISTS.

Пример:

Получить коды преподавателей, имеющих более одного дипломника.

SELECT DISTINCT spdp FROM студентС1 WHERE EXISTS (SELECT * FROM студентС2 WHERE С1.snum <> С2.snum AND С1.spdp = С2.spdp)

Результат:

spdp

001

EXISTS также можно комбинировать с соединениями.

Пример:

Получить фамилии преподавателей, имеющих более одного дипломника.

SELECT DISTINCT pname FROM преподавательINNER JOIN студентС1 ON pnum = spdp WHERE EXISTS (SELECT * FROM студентС2 WHERE С1.snum <> С2.snum AND С1.spdp = С2.spdp)

Результат:

pname

П-1

Рассмотрим ещё один пример использования вложенных подзапросов.

Пример:

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

SELECT pname FROM преподавательWHERE EXISTS (SELECT * FROM студентWHERE 1.snum < (SELECT count (*) FROM оценкаWHERE snum = osnum AND pnum = opnum));

Результат:

pname

П-3

5. Вложенные и связанные подзапросы. Операторы ANY, ALL, SOME

Операторы ANY, ALL, SOME используют в качестве аргументов подзапросы, но, в отличие от EXISTS, подзапросы используются как аргументы в операциях отношения.

Выражение имя_поля операция_отношения ANY (подзапрос) считается истинным, если оно истинно хотя бы для одного значения результата подзапроса. Если подзапрос не генерирует выходных значений, то выражение с ANY принимает значение ложь независимо от операции отношения.

Скачать файл