Примеры запросов с использованием предиката сравнения с квантором

Пример 18.21. Найти номера служащих отдела номер 65, зарплата которых в этом отделе не является минимальной.

SELECT EMP_NO

FROM EMP

WHERE DEPT_NO = 65

AND EMP_SAL > SOME (SELECT EMP1.EMP_SAL

FROM EMP EMP1

WHERE EMP.DEPT_NO = EMP1.DEPT_NO);

Одна из возможных альтернативных формулировок этого запроса может основываться на использовании предикатаEXISTS(пример 18.21.1):

SELECT EMP_NO

FROM EMP

WHERE DEPT_NO = 65

AND EXISTS(SELECT *

FROM EMP EMP1

WHERE EMP.DEPT_NO = EMP1.DEPT_NO

AND EMP.EMP_SAL > EMP1.EMP_SAL);

Вот альтернативная формулировка этого запроса, основанная на использовании агрегатной функцииMIN(пример 18.21.2):

SELECT EMP_NO

FROM EMP

WHERE DEPT_NO = 65 AND

EMP_SAL > (SELECT MIN(EMP1.EMP_SAL)

FROM EMP EMP1

WHERE EMP.DEPT_NO = EMP1.DEPT_NO);

Пример 18.22. Найти номера и имена служащих отдела 65, однофамильцы которых работают в этом же отделе.

SELECT EMP_NO, EMP_NAME

FROM EMP

WHERE DEPT_NO = 65 AND

EMP_NAME = SOME (SELECT EMP1.EMP_NAME

FROM EMP EMP1

WHERE EMP.DEPT_NO = EMP1.DEPT_NO

AND EMP.EMP_NO <> EMP1.EMP_NO);

Заметим, что эта формулировка эквивалентна следующей формулировке (пример 18.22.1):

SELECT EMP_NO, EMP_NAME

FROM EMP

WHERE DEPT_NO = 65 AND

EMP_NAME IN (SELECT EMP1.EMP_NAME

FROM EMP EMP1

WHERE EMP.DEPT_NO = EMP1.DEPT_NO

AND EMP.EMP_NO <> EMP1.EMP_NO);

Возможна формулировка с использованием агрегатной функцииCOUNT(пример 18.22.2):

SELECT EMP_NO, EMP_NAME

FROM EMP

WHERE DEPT_NO = 65 AND

(SELECT COUNT(*)

FROM EMP EMP1

WHERE EMP.DEPT_NO = EMP1.DEPT_NO

AND EMP.EMP_NO <> EMP1.EMP_NO ) >= 1;

Наиболее лаконичным образом этот запрос можно сформулировать с использованием соединения ( пример 18.22.3):

SELECT DISTINCT EMP.EMP_NO, EMP.EMP_NAME

FROM EMP, EMP EMP1

WHERE EMP.DEPT_NO = 65

AND EMP.EMP_NAME = EMP1.EMP_NAME

AND EMP.DEPT_NO = EMP1.DEPT_NO

AND EMP.EMP_NO <> EMP1.EMP_NO;

В последней формулировке мы вынуждены везде использовать уточненные имена столбцов, потому что на одном уровне используются два вхождения таблицы EMP.

Пример 18.23. Найти номера служащих отдела номер 65, зарплата которых в этом отделе является максимальной.

SELECT EMP_NO

FROM EMP

WHERE DEPT_NO = 65

AND EMP_SAL >= ALL(SELECT EMP1.EMP_SAL

FROM EMP EMP1

WHERE EMP.DEPT_NO = EMP1.DEPT_NO);

Одна из возможных альтернативных формулировок этого запроса может основываться на использовании предикатаNOT EXISTS(пример 18.23.1):

SELECT EMP_NO

FROM EMP

WHERE DEPT_NO = 65

AND NOT EXISTS (SELECT *

FROM EMP EMP1

WHERE EMP.DEPT_NO = EMP1.DEPT_NO

AND EMP.EMP_SAL < EMP1.EMP_SAL);

Можно сформулировать этот же запрос с использованием агрегатной функцииMAX(пример 18.23.2):

SELECT EMP_NO

FROM EMP

WHERE DEPT_NO = 65

AND EMP_SAL = (SELECT MAX(EMP1.EMP_SAL)

FROM EMP EMP1

WHERE EMP.DEPT_NO = EMP1.DEPT_NO);

Пример 18.24. Найти номера и имена служащих, не имеющих однофамильцев.

SELECT EMP_NO, EMP_NAME

FROM EMP

WHERE EMP_NAME <> ALL (SELECT EMP1.EMP_NAME

FROM EMP EMP1

WHERE EMP1.EMP_NO <> EMP.EMP_NO);

Этот запрос можно переформулировать на основе использования предиката NOT EXISTSили агрегатной функцииCOUNT(по причине очевидности мы не приводим эти формулировки), но, в отличие от случая впримере 18.22.3, формулировка в виде запроса с соединением здесь не проходит. Формулировка запроса

SELECT DISTINCT EMP_NO, EMP_NAME

FROM EMP, EMP EMP1

WHERE EMP.EMP_NAME <> EMP1.EMP_NAME

AND EMP1.EMP_NO <> EMP.EMP_NO);

эквивалентна формулировке

SELECT EMP_NO, EMP_NAME

FROM EMP

WHERE EMP_NAME <> SOME (SELECT EMP1.EMP_NAME

FROM EMP EMP1

WHERE EMP1.EMP_NO <> EMP.EMP_NO);

Очевидно, что этот запрос является бессмысленным («Найти служащих, для которых имеется хотя бы один не однофамилец»).