Фразы GROUP BY и HAVING. GROUP BY и агрегатные функции в списке select.

GROUP BY и агрегатные функции в списке select.

Пример. Сколько кафедр в каждом из корпусов:

SELECT Building, COUNT(*)

FROM DEPARTMENT

GROUP BY Building;

Example. Какова сумма зарлпты (Salary+Commission) по всем имеющимся должностям:

SELECT Post, SUM(Salary + Commission)

FROM TEACHER

GROUP BY Post;

Группирование и фраза WHERE. Если запрос содержит фразы WHERE и GROUP BY, то фразаWHERE обрабатывается первой, а затем применяется группирование.

Пример. Для каждого корпуса подсчитать количество аудиторий вместимостью более 50:

SELECT Building, COUNT(*)

FROM ROOM

WHERE Seats > 50

GROUP BY Building;

Пример. Для корпусов 5, 7 и 12 подсчитать количество аудиторий вместимостью более 50:

SELECT Building, COUNT(*)

FROM ROOM

WHERE UPPER(Building) IN ('5', '7', '12') AND Seats > 50

GROUP BY Building;

Группирование по многим столбцам. Можно группировать по многим столбцам.

Пример. Для каждой недели и дня недели подсчитать количество лекций типа “lab”:

SELECT Day, Week, COUNT(*)

FROM LECTURE

WHERE UPPER(Type) = 'LAB'

GROUP BY Week, Day;

Группирование и соединение различных таблиц. Имеется возможность соединить две или более таблиці и зптем произвести группирование по соединенной таблице.

Пример. По каждому факультету выдать количество кафедр:

SELECT f.Name, COUNT(*)

FROM FACULTY f, DEPARTMENT d

WHERE f.FacNo = d.FacNo

GROUP BY f.Name;

Пример. По каждому факультету выдать количество профессоров:

SELECT f.Name, COUNT(*)

FROM FACULTY f, DEPARTMENT d, TEACHER t

WHERE f.FacNo = d.FacNo AND d.DepNo = t.DepNo AND UPPER(t.Post) = 'PROFESSOR'

GROUP BY f.Name;

Пример. Для каждой кафедры каждого факультета подсчитать количество профессоров:

SELECT f.Name, d.Name, COUNT(*)

FROM FACULTY f, DEPARTMENT d, TEACHER t

WHERE f.FacNo = d.FacNo AND d.DepNo = t.DepNo AND UPPER(t.Post) = 'PROFESSOR'

GROUP BY f.Name, d.Name;

Использование фразы HAVING. Она задает условие на группу. Фразы HAVING обычно содержат агрегатную функцию.

Example. Вывести корпуса, в которых суммарное количество мест в аудиториях меньше 1000:

SELECT Building

FROM ROOM

GROUP BY Building

HAVING SUM(Seats) < 1000;

Фраза ORDER BY

Она испольуется для упорядочения результатов запроса. Можно упорядочивать

· по любому столбцу таблицы,

· по выражению над столбцами,

· по списку столбцов или выражений.

Упорядочение по столбцу из списка select.

Пример. Выдать назвыание факультета и его декана, упорядочив результат по факультетам:

SELECT Name, Dean

FROM FACULTY

ORDER BY Name;

Упорядочение по столбцу таблицы. Можно упорядочивать по столбцу таблицы, даже если он не присутствует в списке select. Эта возможность не поддерживается стандартом ANSI, но допустима в SQL Oracle.

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

SELECT Name, Salary

FROM TEACHER

ORDER BY Post;

Упорядочение по списку столбцов. Используя список столбцов можно произвести многоуровневое упорядочение.

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

SELECT Course, Num, Quantity

FROM SGROUP

ORDER BY Course, Num;

Упорядочение по выражению над столбцами. Можно упорядочивать по выражению над столбцами.

Example. Вывести имя ставку и надбавку преподавателей. Упорядочить результат по выражению Salary+Commission:

SELECT Name, Salary, Commission

FROM TEACHER

ORDER BY Salary + Commission ASC;

Использование порядкового номера столбца в списке select. Можно использовать порядковый номер столбца в списке select для ссылки на него во фразе ORDER BY. Это может оказаться удобным, когда список select содержит выражения.

Пример. Вывести имя преподавателя и его зарплату (Salary+Commission). Упорядочить результат по выражению Salary+Commission в порядке убывания:

SELECT Name, Salary + Commission

FROM TEACHER

ORDER BY 2 DESC;

Использование алиаса столбца из списка select. Вы можете воспользоваться алиасом столбца из списка select для ссылки на него во фразе ORDER BY.

Пример. Вывести имена преподавателей и даты их поступления на работу. Если дата не определена, то вывести строку “not defined”. Упорядочить результат по дате в убывающем порядке:

SELECT Name, NVL(TO_CHAR(hiredate,'yyyy-mm-dd'),'not defined') AS Teacher_hiredate

FROM TEACHER

ORDER BY Teacher_hiredate DESC;

Соединение таблиц и упорядочение. Если вы соединяете таблицы, то можно упорядочивать по любому столбцу соединенной таблицы.

Пример. Вывести имна преподавателей и их зарплаты факультета инфоматики. Упорядочить результат по зарплате в убывающем порядке.

SELECT t.Name, Salary + Commission

FROM FACULTY f, DEPARTMENT d, TEACHER t

WHERE f.FacNo = d.FacNo AND d.DepNo = t.DepNo AND UPPER(f.Name) = 'INFORMATICS'

ORDER BY 2 DESC;

Группирование и упорядочение. Для упорядочение групп необходимо удовлетворить ограничения, описанные в разделе “2.3. Фраза ORDER BY”.

Пример. Вывести количество студентов на каждом курсе. Расположить результат в порядке возрастания курса.

SELECT Course, SUM(Quantity)

FROM SGROUP

GROUP BY Course

ORDER BY Course ASC;

Контрольные вопросы

Дайте ответы на следующие вопросы:

1. Какие типы фукций SQL вы знаете?

2. Какая область действия агрегатных функций при наличии и отсутствии фразы GROUP BY?

3. В каких фразах предложения SELECT могут использоваться агрегатные функции?

4. Что означают фразы DISTINCT и ALL в агрегатных функциях?

5. Как агрегатные функции оперируют со значениями null?

6. Как SQL Oracle оперирует с датами?

7. Какие выражения может содержать список select, если присутствует фраза GROUP BY?

8. Каковы цели фраз GROUP BY и HAVING?

9. Каковы ограничения на выражения столбцов во фразе ORDER BY, если присутствует фраза GROUP BY?

10. Как Oracle упорядочивает значения null?

Не нашли, что искали? Воспользуйтесь поиском: