Предикат like

Предикат in

Пример 15.7. Найти номера отделов, в которых средний размер зарплаты сотрудников равен максимальному размеру зарплаты сотрудников какого-либо другого отдела.

SELECT DEPT.DEPT_NO
FROM DEPT, EMP

WHERE DEPT.DEPT_NO = EMP.DEPT_NO
GROUP BY DEPT.DEPT_NO
HAVING AVG(EMP.EMP_SAL) IN
(SELECT MAX(EMP1.EMP_SAL)
FROM EMP, DEPT DEPT1
WHERE EMP.DEPT_NO = DEPT1.DEPT_NO
AND DEPT1.DEPT_NO <> DEPT.DEPT_NO
GROUP BY DEPT.DEPT_NO);

Этот запрос, помимо прочего, демонстрирует наличие в условии раздела HAVING вложенного подзапроса с корреляцией. Как и раньше, можно избавиться от разделов GROUP BY и HAVING во внешнем запросе (пример 15.7a):

SELECT DEPT.DEPT_NO
FROM DEPT
WHERE (SELECT AVG(EMP_SAL)
FROM EMP
WHERE EMP.DEPT_NO = DEPT.DEPT_NO) IN
(SELECT MAX(EMP1.EMP_SAL)
FROM EMP, DEPT DEPT1
WHERE EMP.DEPT_NO = DEPT1.DEPT_NO
AND DEPT1.DEPT_NO <> DEPT.DEPT_NO
GROUP BY DEPT.DEPT_NO);

Но в данном случае мы не можем отказаться от раздела GROUP BY во втором вложенном запросе, поскольку без этого невозможно получить множество значений результатов вызова агрегатной функции.

Пример 15.8. Во всех отделах найти имена и число сотрудников, у которых в данном отделе имеются однофамильцы, и фамилии которых начинаются с фамилии руководителя отдела.

SELECT EMP_NAME, COUNT(*)

FROM EMP, DEPT

WHERE EMP.DEPT_NO = DEPT.DEPT_NO

GROUP BY DEPT.DEPT_NO, EMP_NAME

HAVING COUNT(*) > 1
AND EMP.EMP_NAME LIKE (SELECT EMP1.EMP_NAME
FROM EMP EMP1
WHERE EMP1.EMP_NO = DEPT.DEPT_MNG) || ‘%’;

Конечно, и в этом случае условие с предикатом LIKE можно переместить из раздела HAVING в раздел WHERE. Этот запрос можно переформулировать в виде, лишенном разделов GROUP BY и HAVING (пример 15.8a), но вряд ли это разумно, поскольку формулировка является менее понятной и существенно более сложной.

SELECT EMP_NAME, (SELECT COUNT(*)
FROM EMP EMP1
WHERE EMP1.DEPT_NO = EMP.DEPT_NO
AND EMP1.EMP_NAME = EMP.EMP_NAME
AND EMP1.EMP_NO <> EMP.EMP_NO) + 1

FROM EMP

WHERE (SELECT COUNT(*)
FROM EMP EMP1
WHERE EMP1.DEPT_NO = EMP.DEPT_NO
AND EMP1.EMP_NAME = EMP.EMP_NAME
AND EMP1.EMP_NO <> EMP.EMP_NO) > 1
AND EMP_NAME LIKE (SELECT EMP1.EMP_NAME
FROM EMP EMP1, DEPT
WHERE EMP.DEPT_NO = DEPT.DEPT_NO
AND EMP1.EMP_NO = DEPT.DEPT_MNG) || ‘%’;

Дата добавления: 2014-01-03