- •Лабораторная работа 2.1
- •Агрегатные функции
- •Функции одной строки
- •Числовые функции
- •Символьные функции
- •Строковые функции, которые возвращают числовые значения
- •Функции даты
- •Функции преобразования
- •Другие функции одной строки
- •Фразы group by и having
- •Синтаксис:
- •Назначение
- •Фраза order by
- •Синтаксис:
- •Назначение
- •Описание и примеры
- •Агрегатные функции
- •Фразы group by и having
- •Фраза order by
- •Контрольные вопросы
Фразы group by и having
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;