Результат реализации запроса:
Подзапрос вычисляет среднюю оценку и подставляет высчитанное значение в предложение WHERE внешнего запроса.
Коррелированные подзапросы на уровне предложения WHERE
Коррелированные подзапросы – это вложенные подзапросы. Они выполняются для каждой строки главного запроса.
Последовательность выполнения коррелированного подзапроса (см. Рисунок 3):
· внешний запрос выбирает строку;
· выполняется внутренний запрос, используя значение строки внешнего запроса;
· результат выполнения внутреннего запроса возвращается во внешний запрос, где проверяется его соответствие выбранной строке;
· выбирается следующая строка внешнего запроса.
При задании вложенных запросов допускается применение операторов АNY, EXISTS, ALL и логических операторов.
Пример 72
Задача.
Вывести имена студентов, чьи оценки выше, чем средняя оценка в их группе.
Решение:
SELECT DISTINCT SName,Mark
FROM Student s,Progress p
WHERE S.NRecordBook=P.NRecordBook
AND Mark>(SELECT AVG(Mark)
FROM Progress P1,Student S1
WHERE S1.IDGroup=S.IDGroup
AND S1.NRecordBook=P1.NRecordBook)
Результат реализации запроса:
Из примера становится очевидным, что коррелированные запросы следует применять только в случае крайней необходимости, так как производительность их мала. Например, при реализации запроса средняя оценка по группе вычисляется столько раз, сколько раз информация о группе встречается в первом запросе.
Задание 36
Вывести имя студента, название предмета и оценку студентов для тех студентов, у которых оценка по той или иной дисциплине выше средней оценки по этой самой дисциплине.
Задание 37
Вывести имя студента, название предмета и оценку студентов для тех студентов, у которых оценка по той или иной дисциплине выше средней оценки по группе, в которой они обучаются.
Задание 38
Вывести имена студентов, у которых средняя оценка равна средней оценке по группе, в которой учится студент.
Рисунок 3
Подзапрос на уровне предложения HAVING
Пример 73
Задача.
Вывести имена студентов, у которых средняя оценка выше средней по университету.
Решение.
Было бы ошибочно использовать следующий синтаксис команды. Выше уже говорилось о том, что отбор групп по условию возможен только в предложении HAVING.
SELECT SName, AVG(Mark)
FROM Student s,Progress p
WHERE s.NRecordBook =p. NRecordBook
AND AVG(Mark) >(SELECT AVG(Mark)