Пример 1. Требуется определить количество студентов, сдавших экзамен по каждой дисциплине. Необходимо сгруппировать записи по дисциплинам, предварительно исключив из представления записи с пустыми полями в столбце Оценка.
SELECT Сессия.Дисциплина, COUNT(*)
FROM Сессия
WHERE Сессия.Оценка IS NOT NULL
GROUP BY Сессия.Дисциплина
Пример 2. Определить для каждой группы и каждой дисциплины количество студентов, успешно сдавших экзамен, и средний балл по дисциплине.
Пример 4. Найти суммарные остатки на счетах в филиалах, которые превышают 5000 руб.
SELECT Фил.Филиал, Sum(Фил.Остаток)
FROM Фил
GROUP BY Фил.Филиал
HAVING (SUM(Фил.Остаток)>5000);
Пример 5. Найти суммарный остаток в филиалах по счетам, которые были открыты 27 декабря 2002 года.
SELECT Фил.Филиал, Sum(Фил.Остаток) AS [Sum-Остаток]
FROM Фил
WHERE Фил.ДатаОткрытия=#12/27/2002#
GROUP BY Фил.Филиал;
Пример 6. Найти суммарный остаток на счетах филиалов в Иркутске, Ангарске и Зиме.
SELECT Фил.Филиал, Sum(Фил.Остаток)
FROM Фил
GROUP BY Фил.Филиал
HAVING Филиал IN («Иркутск», «Ангарск», «Зима»);
Рассмотрим примеры применение вложенных запросов в операторе выбора. Пусть имеется база данных Сессия, состоящая из трех отношений R1, R2 и R3. Атрибуты таблиц следующие:
R1 = (ФИО, Дисциплина, Оценка);
R2 = (ФИО, Группа);
R3 = (Группа, Дисциплина).
Пример 7. Требуется получить список тех студентов, кто сдал все положенные в сессию экзамены.
SELECT ФИО
FROM R1 AS A
WHERE Оценка>2
GROUP BY [ФИО]
HAVING (((Count(*))=(SELECT COUNT(*)
FROM R2, R3
WHERE R2.Группа=R3.Группа
AND ФИО=A.ФИО)));
Пример 8. Требуется получить список тех студентов, кто должен был сдавать экзамен по Базам данных, но пока еще не сдавал.
SELECT ФИО
FROM R2 A, R3
WHERE A.Группа =R3.Группа AND Дисциплина = "БД" AND NOT EXISTS (SELECT ФИО
FROM R1
WHERE ФИО = A.ФИО AND Дисциплина = "БД");
Пример 9. Требуется найти поставщиков, которые поставляют все виды деталей (Найти поставщиков таких, что не существует детали, которую бы они не поставляли).
SELECT DISTINCT [S].[Имя]
FROM S
WHERE (((Exists
(SELECT *
FROM P
WHERE NOT EXISTS
(SELECT *
FROM SP
WHERE [SP].[П#]=[S].[П#] AND [SP].[Д#]=[P].[Д#])))=False));
Или
SELECT DISTINCT [S].[Имя]
FROM S
WHERE (((NOT Exists
(SELECT *
FROM P
WHERE NOT EXISTS
(SELECT *
FROM SP
WHERE [SP].[П#] =[S].[П#] AND [SP].[Д#] =[P].[Д#])))));
Или
SELECT DISTINCT [П#]
FROM SP
GROUP BY [П#]
HAVING COUNT ( [Д#]) =
(SELECT COUNT ([Д#])
FROM P);
Или
SELECT DISTINCT [S].[ИМЯ]
FROM SP, S
WHERE[S].[П#] = [SP].[П#]
GROUP BY [S].[ИМЯ]
HAVING COUNT ([SP].[Д#]) =
(SELECT COUNT ([P].[Д#])
FROM P);
Рассмотрим примеры применения внутреннего и внешнего объединения.
В операнде FROM могут содержаться выражения объединений следующего вида:
1. Выражение естественного объединения:: =
<имя таблицы_1> NATURAL {INNER| FULL [OUTER] |
LEFT [OUTER] | RIGHT [OUTER] } JOIN <ИМЯ_ТАБЛИЦЫ_2>
2. Выражение перекрестного объединения :: =
CROSS JOIN <ИМЯ ТАБЛИЦЫ_2>
3. Выражение запроса на объединение :: =
<имя_таблицы_1> UNION JOIN <имя_таблицы_2>
4. Выражение объединения:: =
< имя таблицы_1> { INNER| FULL [OUTER] |
LEFT [OUTER] | RIGHT [OUTER] } JOIN {ON условие | [USING (список столбцов)]} <имя_таблицы_2>
INNER |FULL }
Здесь INNER – внутреннее объединение; LEFT – левое объединение, т.е. в результат входят все строки таблицы 1, а части результирующих кортежей, для которых не было соответствующих значений в таблице 2, дополняются значениями NULL; RIGHT – правое объединение, в результирующее отношение включаются все строки таблицы 2, а недостающие части таблицы 1 дополняются неопределенными значениями; FULL – полное внешнее объединение и левое и правое; OUTER – означает внешнее.
Пример 10. Требуется получить список, содержащий кортежи по всем студентам, по всем сдаваемым ими дисциплинам, со всеми полученными или не полученными оценкам (общая ведомость).
SELECT R1.ФИО, R1.Дисциплина, R1.Оценка
FROM (R2 NATURAL INNER JOIN R3) LEFT JOIN R1 USING (ФИО.Дисциплина)
Рассмотрим примеры определения привилегий.
Формат оператора:
GRANT <привилегии> ON <имя таблицы>
TO <имя пользователя> [{,<имя пользователя>}…]
[WITH GRANT OPTION]
К привилегиям относятся команды: SELECT – разрешение на выполнение запросов; INSERT – вставка значений; UPDATE – изменение значений с указанием множества полей таблицы; DELETE – удаление записей; ALL PRIVILEGES (ALL) – передача всех привилегий для таблицы пользователю; PUBLIC – определяет объект как «общедоступный объект».
Примеры.
1. GRANT SELECT, INSERT ON Сотрудники TO Иванов, Степанов; - Иванову и Степанову разрешено выполнять запросы и вставлять значения в табл. Сотрудники.
2. GRANT UPDATE (зарплата) ON Сотрудники TO Бухгалтер; - Пользователю Бухгалтер разрешено изменять значения в поле Зарплата таблицы Сотрудники.
3. GRANT ALL ON Сотрудники TO Директор; - передача всех привилегий по таблице пользователю Директор.
4. GRANT SELECT ON Сотрудники TO PUBLIC; - устанавливается, что таблица Сотрудники является общедоступной.
5. GRANT SELECT, INSERT ON Зарплата.Сотрудники TO Директор
WITH GRANT OPTION; - Директор имеет право передать привилегии на поле Зарплата таблицы Сотрудники другому пользователю.
Рассмотрим примеры отмены привилегий.
Формат оператора:
REVOKE <список привилегий> ON <имена таблиц>
FROM <имена пользователей>; -
Пример.
REVOKE INSERT, DELETE ON Сотрудники
FROM Иванов, Степанов; - Иванову и Степанову отменяют установленные ранее привилегии. Это может сделать тот пользователь, который привилегии ранее устанавливал.
Пример. Установить пользователю Петров (руководитель отдела «Игрушки») разрешение на изменение данных о продавцах отдела, без возможности изменять записи по другим отделам.
