Московский государственный университет имени М.В.Ломоносова
Опубликован: 10.10.2005 | Доступ: свободный | Студентов: 7916 / 387 | Оценка: 3.85 / 3.50 | Длительность: 22:03:00
Лекция 5:

Группировка и условия раздела HAVING, порождаемые и соединенные таблицы

Предикат exists
SELECT DEPT.DEPT_NO
FROM DEPT, EMP
WHERE DEPT.DEPT_NO = EMP.DEPT_NO
GROUP BY DEPT.DEPT_NO
HAVING EXISTS (SELECT *
	FROM EMP EMP1
	WHERE EMP1.DEPT_NO <> DEPT.DEPT_NO
	GROUP BY EMP1.DEPT_NO 
	HAVING MAX (EMP1.EMP_SAL)= AVG (EMP.EMP_SAL));
15.9. Найти номера отделов, в которых средний размер зарплаты служащих равен максимальному размеру зарплаты служащих какого-либо другого отдела (другая формулировка для примера 15.7).

В этой формулировке основной интерес представляет подзапрос, в котором корреляция с внешним запросом происходит через вызов агрегатной функции от группы строк внешнего запроса. Здесь также можно избавиться от разделов GROUP BY и HAVING во внешнем запросе ( пример 15.9.1):

SELECT DEPT.DEPT_NO
FROM DEPT
WHERE EXISTS (SELECT EMP.DEPT_NO
	FROM EMP
	WHERE EMP.DEPT_NO <> DEPT.DEPT_NO
	GROUP BY EMP.DEPT_NO 
	HAVING MAX (EMP.EMP_SAL)= 
	  (SELECT AVG (EMP1.EMP_SAL)
	    FROM EMP EMP1
	    WHERE EMP1.DEPT_NO = DEPT.DEPT_NO));
15.9.1.
Предикат unique
SELECT DEPT.DEPT_NO, AVG (EMP.EMP_SAL)
FROM DEPT, EMP
WHERE DEPT.DEPT_NO = EMP.DEPT_NO
GROUP BY DEPT.DEPT_NO
HAVING UNIQIUE (SELECT AVG (EMP1.EMP_SAL)
	FROM EMP EMP1
	WHERE EMP1.DEPT_NO IS NOT NULL
	GROUP BY EMP1.DEPT_NO 
	HAVING AVG (EMP1.EMP_SAL) = AVG (EMP.EMP_SAL));
15.10. Найти номера отделов и средний размер зарплаты служащих для таких отделов, где средний размер зарплаты служащих отличается от среднего размера зарплаты всех других отделов.

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

SELECT DEPT.DEPT_NO, AVG (EMP.EMP_SAL)
FROM DEPT, EMP
WHERE DEPT.DEPT_NO = EMP.DEPT_NO
GROUP BY DEPT.DEPT_NO
HAVING NOT EXISTS (SELECT EMP1.DEPT_NO
	FROM EMP EMP1
	WHERE EMP1.DEPT_NO <> DEPT.DEPT_NO
	GROUP BY EMP1.DEPT_NO 
	HAVING AVG (EMP1.EMP_SAL)= AVG (EMP.EMP_SAL));
15.10.1.
Предикаты сравнения с квантором
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);
15.11. Найти номера отделов и средний возраст служащих для таких отделов, что найдется хотя бы один другой отдел, средний возраст служащих которого больше, чем в данном.

Напомним, что "ниладическая" функция CURRENT_DATE выдает текущую дату, и, следовательно, значением выражения CURRENT_DATE - EMP_BDATE является интервал, представляющий текущий возраст служащего. На наш взгляд, формулировка этого запроса несколько упрощается, если пользоваться предикат предикатом EXISTS ( пример 15.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));
15.11.1.
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);
15.12. Найти номера отделов и средний возраст служащих для отделов с минимальным средним возрастом служащих.

Этот запрос легко формулируется в более понятном виде с использованием предиката NOT EXISTS ( пример 15.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));
15.12.1.
Предикат distinct
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);
15.13. Найти номера отделов, которые можно отличить от любого другого отдела по дате рождения руководителя и среднему размеру зарплаты.
Александра Каева
Александра Каева
Антон Честиков
Антон Честиков

Не вижу список тем из плана занятий. Проблема известна?

Семен Дядькин
Семен Дядькин
Беларусь, Минск, БГУ, 2003
Светлана Кудинова
Светлана Кудинова
Россия