Стандартные функции

Запросы, использующие EXIST, ANY, ALL

В языке SQL предикат с квантором существования представляется выражением вида EXISTS (SELECT * FROM...). Такое выражение считается истинным тогда и только тогда, когда существует какая-либо запись в таблице, указанной во фразе FROM подзапроса, которая удовлетворяет условию WHERE этого подзапроса.

Ключевое слово ANY означает, что предикат будет истинен, если хотя бы для одного значения из подзапроса предикат сравнения истинен.

Ключевое слово ALL требует, чтобы предикат сравнения был бы истинен при сравнении со всеми строками подзапроса.

Пример. Вывести список тех, кто должен был сдавать экзамен по БД, но пока еще не сдавал.

SЕLЕСТ ФИО

FROM R2 a, R3

WHERE R2.Fpynna=R3.Группа AND Дисциплина = "БД" AND NOT EXISTS

(SELECT ФИО FROM Rl WHERE ФИО=а.ФИО AND Дисциплина = "БД")

Пример. Вывести студентов, которые сдали все экзамены на оценку не ниже чем «хорошо»:

SELECT R1.ФИО FROM R1 WHERE 4 > = ALL

(SELECT R1.Оценка FROM R1 AS R11 WHERE R1.ФИО = R11.ФИО)

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

SELECT R1.ФИО

FROM R1 WHERE R1.Оценка >= ANY

(SELECT R4.Оценка FROM R4

WHERE R1.Дисциплина = R4. Дисциплина AND R1.ФИО = R4.ФИО)

Пример. Выдать фамилии поставщиков, которые поставляют деталь P2.
SELECT ФАМИЛИЯ FROM S WHERE EXISTS
(SELECT * FROM SP WHERE

НОМЕР_ПОСТАВЩИКА=S.НОМЕР_ПОСТАВЩИКА

AND НОМЕР_ДЕТАЛИ = 'P2');

Пример. Выдать фамилии поставщиков, которые не поставляют деталь Р2.

SELECT ФАМИЛИЯ FROM S WHERE NOT EXIST
(SELECT * FROM SP WHERE НОМЕР_ПОСТАВЩИКА=S.НОМЕР_ПОСТАВЩИКА
AND НОМЕР_ДЕТАЛИ = 'Р2');

Пример. Выдать фамилии поставщиков, которые поставляют все детали.

SELECT ФАМИЛИЯ FROM S WHERE NOT EXISTS
(SELECT * FROM P WHERE NOT EXISTS

(SELECT * FROM SP WHERE НОМЕР_ПОСТАВЩИКА=
S.НОМЕР_ПОСТАВЩИКА AND НОМЕР_ДЕТАЛИ= Р.НОМЕР_ДЕТАЛИ));

В SQL предусматривается ряд специальных стандартных функций:
COUNT (число значений в столбце), SUM (сумма значений по столбцу), AVG (среднее числовое значение в столбце), MAX (самое большое значение в столбце). В общем случае аргументу функции может факультативно предшествовать ключевое слово DISTINCT (различный), указывающее, что избыточные дублирующие значения должны быть исключены перед тем, как будет применяться функция. Для функции COUNT ключевое слово DISTINCT должно быть специфицировано. Специальная функция COUNT (*), для которой использование DISTINCT не допускается, предусмотрена для подсчета всех строк в таблице без исключения каких-либо дубликатов. Если DISTINCT специфицируется, то аргумент должен состоять только из имени столбца. Если DISTINCT не специфицировано, аргумент может представлять собой арифметическое выражение, например ВЕС * 454.

В столбце-аргументе всегда перед применением функции исключаются все неопределенные значения, независимо от того, специфицировано ли DISTINCT, за исключением случая COUNT (*), при котором неопределенные значения обрабатываются точно так же, как и значения, не являющиеся неопределенными. Если оказывается, что аргумент – пустое множество, функция COUNT принимает значение нуль. Все другие функции принимают в этом случае неопределенное значение.

Пример. Выдать общее количество поставщиков.

SELECT COUNT (*) FROM SP

Пример. Выдать общее количество поставщиков, поставляющих в настоящее время детали:

SELECT COUNT (DISTINCT НОМЕР_ПОСТАВЩИКА) FROM SP;

Пример. Выдать количество поставок для детали Р2.
SELECT COUNT (*) FROM SP WHERE НОМЕР_ДЕТАЛИ = 'Р2';

Пример. Выдать общее количество поставляемых деталей Р2.
SELECT SUM (КОЛИЧЕСТВО) FROM SP WHERE НОМЕР_ДЕТАЛИ = 'Р2';

Пример. Выдать номера поставщиков со значением поля СОСТОЯНИЕ меньшим, чем максимальное состояние в таблице S.
SELECT НОМЕР_ПОСТАВЩИКА FROM S
WHERE СОСТОЯНИЕ < (SELECT MAX (СОСТОЯНИЕ) FROM S);

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

SELECT НОМЕР_ПОСТАВЩИКА, СОСТОЯНИЕ, ГОРОД
FROM S SX WHERE СОСТОЯНИЕ > =
(SELECT AVQ (СОСТОЯНИЕ) FROM S SY WHERE SY.ГОРОД = SX.ГОРОД);

Дата добавления: 2014-12-10