Использование агрегатных функций в подзапросах
Запрос, использующий единственную агрегатную функцию без предложения GROUP BY, дает в результате единственное значение. Это значение можно использовать в основном предикате.
Пример 2.
Для заданной дисциплины узнать фамилии студентов получивших оценку, большую средней оценки по данной дисциплине.
SELECT фамилия
FROM дисциплины INNER JOIN(студенты INNER JOIN сессия ON студенты.код = сессия.код_студ) ON дисциплины.код = сессия.код_дисц
WHERE название = [введите название дисциплины] and оценка > (select AVG(оценка) FROM сессия)
В данном случае подзапрос выполняется только один раз. Возвращенное им значение используется во внешнем запросе.
Пример 3.
Показать дисциплину, по которой студенты получили максимальное количество двоек.
Агрегатные функции нельзя вкладывать друг в друга, поэтому выражение Max(Count(оценка)) будет неправильным.
Для создания этого запроса нужно поступать следующим образом:
Создать запрос, который будет возвращать количество двоек для каждой группы.
SELECT название, count(оценка) AS двоек
FROM сессия INNER JOIN дисциплины
ON сессия.код_дисц = дисциплины.код
WHERE оценка=2
GROUP BY название
2. Сохраните запрос с названием, например, дисциплины_двойки.
Результат данного запроса:
Рис. 5.2. Количество двоек для каждой дисциплины
Используйте полученный результат в качестве входной таблицы в другом запросе, который будет выбирать название дисциплины, с максимальным количеством двоек.
SELECT название
FROM дисциплины_двойки
WHERE двоек = (SELECT MAX(двоек)
FROM дисциплины_двойки);
Подзапросы с exists и not exists
EXISTS означает примерно "если подмножество не пусто", соответственно NOT EXISTS означает "если подмножество пусто". Используются в подзапросах.
Пример 4.
Выполнить пример 1, используя оператор EXISTS.
SELECT фамилия, имя, отчество, название
FROM студенты INNER JOIN группы
ON студенты.код_группы = группы.код
WHERE EXISTS
( SELECT *
FROM сессия
WHERE оценка = 2 AND студенты.код= сессия.код_студ)
Во внешнем запросе перебираются строки таблицы, полученной путем объединения таблиц студенты и группы, и для каждой записи этой таблицы выполняется подзапрос (так называемый связанный подзапрос) – ищутся строки в таблице сессия с кодом данного студента. Если внутренний запрос возвращает одну или более записей, то внешний запрос заносит строку с информацией о данном студенте в результат.
Пример 5.
Найти всех студентов из группы Гр2, которые сдали сессию без двоек и троек.
SELECT *
FROM студенты
WHERE код_группы =
( SELECT код FROM Группы WHERE название =‘Гр2’ )
AND NOT EXISTS
(SELECT *
FROM сессия
WHERE студенты.код = сессия.код_студ AND оценка <=3 )
Во внешнем запросе для каждого студента проверяются два условия. В каждом условии присутствует подзапрос. Первый подзапрос возвращает таблицу, состоящую из одной ячейки, в которой записан код группы “Гр2”. Первое условие проверяет равен ли код группы данного студента коду группы “Гр2”.
Второй подзапрос возвращает записи таблицы сессия, в которых код студента равен коду проверяемого студента и оценка <= 3. Второе условие истинно, если второй подзапрос вернет пустую таблицу.
Если оба условия для данного студента выполняются, то внешний запрос заносит информацию о студенте в результирующую таблицу:
Рис. 5.3. Студенты группы Гр2, сдавшие сессию без троек.