Использование агрегатных функций в подзапросе

Агрегатные функции (COUNT, SUM, AVG, MAX, MIN) автоматически выдают единственное значение для любого количества строк, которое используется в предикате.

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

SELECT * FROM Spisok WHERE slip < (SELECT AVG(stip) FROM Spisok WHERE kurs = 1);

В подзапросах можно использовать оператор IN в предложении WHERE или HAWING внешнего запроса. Подзапросы могут быть вложены в предложения SELECT, UPDATE, DELETE, INSERT.

Оператор EXISTS проверяет только наличие в таблице результатов вложенного запроса хотя бы одной строки. Он используется для образования предиката, который фиксирует, будет ли подзапрос генерировать выходные данные. Оператор EXISTS генерирует значение “истина” или “ложь”. Его можно применять в комбинации с операторами AND, OR, NOT. В операторе EXISTS подзапрос используется в качестве аргумента; подзапрос не может принимать значение “неизвестно”.

Пример. Вывести с таблицы Spisok список академических групп, если ни один студент не получает повышенной стипендии (>200 000).

SELECT kurs, grupa FROM Spisok WHERE EXISTS (SELECT * FROM Spisok WHERE slip > 200 000);

Оператор EXISTS фиксирует наличие выходных данных подзапроса.

Главный запрос последовательно просматривает все строки таблицы Spisok, и для каждой академической группы выполняется вложенный запрос. Результатом вложенного запроса является столбец данных, содержащий названия академических групп и студентов, получающих стипендию, превышающую 200 000 руб.

Операторы ANY и ALL. Для многократного сравнения в SQL имеются операторы ANY и ALL. В проверке этих операторов используется один из шести операторов (=, О, <, <=, >, >=). При сравнении строк в Access регистр не учитывается. В операторе IN проверяется, не равно ли некоторое значение одному из множества значений в столбце результатов вложенного запроса.

Объединение результатов нескольких запросов

Результатная информация находится в структурной и смысловой взаимосвязи. Результаты двух запросов и более можно объединить в одну таблицу командой UNION следующего формата (общий вид):

<запрос> {UNION [ALL] <запрос> }...; Синтаксис оператора UNION в развернутом виде:

Оператор_SЕLЕСТ

UNION [All] Оператор_SЕLЕСТ

UNION [All] Оператор_SELECT

Команда UNION объединяет результаты нескольких запросов. Каждый из них должен содержать одинаковое число элементов в выражении, содержащем значение> в предложении SELECT, а элементы, стоящие в одном и том же месте в <выражениях, содержащих значения^ должны быть совместимыми по типу данных и размеру. В объединении подзапросы не могут управлять друг другом. Подзапросы объединения выполняются независимо, а затем их выходные данные объединяются. Команда UNION объединяет выходные данные двух запросов и более в единый выходной документ (единое множество строк и столбцов). По умолчанию оператор UNION автоматически устраняет повторяющиеся строки из результата объединения₍

Пример. Вывести информацию о всех студентах (таблица Spisok), играющих в футбол (таблица Sport), в виде (запрос):

Фамилия

Группа

SELECT fio, grupa FROM Spisok WHERE futbol = 1 UNION SELECT fio, grupa FROM Sport WHERE futbol = 1;

Функция команды UNION приведена на рис. 4.

Результат объединения выглядит так, как будто он получен одним запросом. Столбцы в выходных данных не поименованы. Обратите внимание: только другой запрос заканчивается точкой с запятой — после первого запроса даный знак отсутствует.

Рис. 4. Оператор UNION для объединения результатов запросов

ПРЕДСТАВЛЕНИЯ

Основу базы данных составляют базовые таблицы, т. е. таблицы с реальными данными. На основе данных базовых таблиц по запросам результаты представляются в таблицах, которые не содержат собственных данных. Представление (view) — это альтернативный способ просмотра данных из одной или нескольких таблиц; это виртуальная таблица, которая не содержит собственных данных; ее содержимое берется или выводится из других таблиц. Представления:

1) позволяют пользователям видеть данные базы данных по-разному, с позиций своих интересов;

2) упрощают доступ к базе данных: каждый пользователь видит данные определенной структуры, хранимые в базе данных;

3) являются одним из средств ограничения доступа к данным (пользователь видит не все столбцы и строки). Создается представление командой CREATE VIEW:

CREATE VIEW <имя_представления>

AS SELECT предикат [WITH CHECK OPTIONS];

На протяжении выполнения команды CREATE VIEW выполняется запрос, по результатам которого формируется содержимое таблицы-представления. Если указано WITH CHECK OPTIONS, то эти обновления должны удовлетворять условию запроса.

Пример. Создать представление с именем SportGimnast, отображающее пловцов и гимнастов 1-го курса:

CREATE VIEW SportGimnast AS SELECT plovec, gimnast FROM Sport WHERE kurs = 1;

Групповое представление содержит предложение GROUP BY или базируется на других групповых представлениях. Например, деканаты ежедневно отслеживают пропуски занятий студентами. Можно

получить соответствующую информацию по запросу, а можно создать представление и получать нужную информацию:

CREATE VIEW Propuski AS SELECT date, COUNT (propuski), SUM () FROM Spisok, GROUP BY date;

После этого можно получить необходимую информацию с помощью запроса:

SELECT * FROM Propuski;