Предикаты сравнения с квантором

Пример 19.11. Найти номера отделов и средний возраст служащих для таких отделов, что найдется хотя бы один другой отдел, средний возраст служащих которого больше, чем в данном.

SELECT DEPT_NO, AVG (CURRENT_DATE – EMP_BDATE)

FROM EMP

WHERE DEPT_NO IS NOT NULL

GROUP BY DEPT_NO

HAVING AVG (CURRENT_DATE – EMP_BDATE)< SOME

(SELECT AVG (CURRENT_DATE – EMP1.EMP_BDATE)

FROM EMP EMP1

WHERE EMP1.DEPT_NO IS NOT NULL

GROUP BY EMP1.DEPT_NO);

Напомним, что «ниладическая» функцияCURRENT_DATEвыдает текущую дату, и, следовательно, значением выраженияCURRENT_DATE – EMP_BDATEявляется интервал, представляющий текущий возраст служащего. На наш взгляд, формулировка этого запроса несколько упрощается, если пользоваться предикатпредикатом EXISTS(пример 19.11.1):

SELECT DEPT_NO, AVG (CURRENT_DATE – EMP_BDATE)

FROM EMP

WHERE DEPT_NO IS NOT NULL

GROUP BY DEPT_NO

HAVING EXISTS (SELECT EMP1.DEPT_NO

FROM EMP EMP1

WHERE EMP1.DEPT_NO IS NOT NULL

GROUP BY EMP1.DEPT_NO

HAVING AVG (CURRENT_DATE – EMP1.EMP_BDATE) >

AVG (CURRENT_DATE – EMP.EMP_BDATE));

Пример 19.12. Найти номера отделов и средний возраст служащих для отделов с минимальным средним возрастом служащих.

SELECT DEPT_NO, AVG (CURRENT_DATE – EMP_BDATE)

FROM EMP

WHERE DEPT_NO IS NOT NULL

GROUP BY DEPT_NO

HAVING AVG (CURRENT_DATE – EMP_BDATE) <= ALL

(SELECT AVG (CURRENT_DATE – EMP_BDATE)

FROM EMP

WHERE DEPT_NO IS NOT NULL

GROUP BY DEPT_NO);

Этот запрос легко формулируется в более понятном виде с использованием предикатаNOT EXISTS(пример 19.12.1):

SELECT DEPT_NO, AVG (CURRENT_DATE – EMP_BDATE)

FROM EMP

WHERE DEPT_NO IS NOT NULL

GROUP BY DEPT_NO

HAVING NOT EXISTS (SELECT EMP1.DEPT_NO

FROM EMP EMP1

WHERE EMP1.DEPT_NO IS NOT NULL

GROUP BY EMP1.DEPT_NO

HAVING AVG (CURRENT_DATE – EMP1.EMP_BDATE) <

AVG (CURRENT_DATE – EMP.EMP_BDATE));

Предикат distinct

Пример 19.13. Найти номера отделов, которые можно отличить от любого другого отдела по дате рождения руководителя и среднему размеру зарплаты.

SELECT DEPT.DEPT_NO

FROM DEPT, EMP EMP1, EMP EMP2

WHERE DEPT.DEPT_NO = EMP1.DEPT_NO AND

DEPT.DEPT_MNG = EMP2.EMP_NO

GROUP BY DEPT.DEPT_NO, EMP2.EMP_BDATE

HAVING (EMP2.EMP_BDATE, AVG (EMP1.EMP_SAL)) DISTINCT FROM

(SELECT EMP2.EMP_BDATE, AVG (EMP1.EMP_SAL)

FROM DEPT DEPT1, EMP EMP1, EMP EMP2

WHERE DEPT1.DEPT_NO = EMP1.DEPT_NO AND

DEPT1.DEPT_MNG = EMP2.EMP_NO AND

DEPT1.DEPT_NO <> DEPT.DEPT_NO

GROUP BY DEPT.DEPT_NO, EMP2.EMP_BDATE);

19.3. Ссылки на порождаемые таблицы в разделе from

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

19.3.1. Еще один способ формулировки запросов

Прежде всего, на простом примере покажем, как использование ссылок на порождаемые таблицы расширяет возможности формулировки запросов.

Пример 19.14. Найти номера отделов и имена руководителей отделов, которые числятся в тех же отделах, которыми руководят, и получают зарплату, размер которой является максимальным для служащих данного отдела.

SELECT MNG.DEPT_NO, MNG.MNG_NAME

FROM (SELECT DEPT.DEPT_NO, EMP.DEPT_NO, EMP_NAME, EMP_SAL

FROM DEPT, EMP

WHERE DEPT.DEPT_MNG = EMP.EMP_NO)

AS MNG (DEPT_NO_1, DEPT_NO_2, MNG_NAME, MNG_SAL)

WHERE DEPT_NO_1 = DEPT_NO_2

AND MNG_SAL = (SELECT MAX (EMP_SAL)

FROM EMP

WHERE EMP.DEPT_NO = DEPT_NO_1);

В этом запросе порождаемая таблица MNGсодержит по одной строке для каждого служащего, являющегося руководителем отдела. Первый столбец этой таблицы –DEPT_NO_1– содержит номер отдела, которым руководит данный служащий. В столбцеDEPT_NO_1хранятся номера отделов, в которых числятся руководители отделов, а в столбцахEMP_NAMEиEMP_SALсодержатся имя служащего-руководителя отдела и размер его заработной платы соответственно.

Конечно, этот запрос можно сформулировать и без использования ссылки на порождаемую таблицу в разделеFROM, например, следующим образом (пример 19.14.1):

SELECT DEPT.DEPT_NO, EMP.EMP_NAME

FROM DEPT, EMP

WHERE DEPT.DEPT_MNG = EMP.EMP_NO

AND DEPT.DEPT_NO = EMP.DEPT_NO

AND EMP.EMP_SAL = (SELECT MAX(EMP_SAL)

FROM EMP

WHERE EMP.DEPT_NO = DEPT.DEPT_NO);

А вот как можно сформулировать тот же запрос с использованием разделаWITH(пример 19.14.2):

WITH MNG (DEPT_NO_1, DEPT_NO_2, MNG_NAME, MNG_SAL) AS

(SELECT DEPT.DEPT_NO, EMP.DEPT_NO, EMP_NAME, EMP_SAL

FROM DEPT, EMP

WHERE DEPT.MNG_NO = EMP.EMP_NO),

MAX_DEPT_SAL (MAX_SAL, DEPT_NO) AS

(SELECT MAX (EMP_SAL), DEPT_NO

FROM EMP

WHERE DEPT_NO IS NOT NULL

GROUP BY DEPT_NO)

SELECT DEPT_NO_1, MNG_NAME

FROM MNG

WHERE DEPT_NO_1 = DEPT_NO_2

AND MNG_SAL = (SELECT MAX_SAL

FROM MAX_DEPT_SAL

WHERE MAX_DEPT_SAL.DEPT_NO = DEPT_NO_1);