FROM DEPARTMENT;
Агрегатные функции с фразой WHERE. При наличии фразы WHERE областью действия агрегатной функции являются те строки, которые удовлетворяют условию фразы WHERE.
Пример. Какова средняя ставка ассистентов, которые не имеют надбавки.
SELECT AVG(Salary) FROM TEACHER
WHERE UPPER(Post) = 'ASSISTANT' AND Commission IS NULL;
Пример. Какой максимальный фонд среди кафедр факультета информатики?
SELECT MAX(DEPARTMENT.Fund)
FROM DEPARTMENT, FACULTY
WHERE DEPARTMENT.FacNo = FACULTY.FacNo AND UPPER(FACULTY.Name) = 'INFORMATICS';
Выражения в агрегатной функции. Аргументы агрегатных функций могут содержать выражения над столбцами таблицы.
Премер. Какова максимальная величина Salary+Commission?
SELECT MAX(Salary + Commission) FROM TEACHER;
Пример. Какова разница между максимальной и минимальной ставками?
SELECT MAX(Salary) – MIN(Salary) FROM TEACHER;
DISTINCT в агрегатных функциях. Ключевое слово DISTINCT означает, что в расчет следут брать только уникальные значения столбца (выражения) аргумента.
Пример. Сколько номеров телеыонов в таблице TEACHER:
SELECT COUNT(DISTINCT Tel) AS Number_of_telephones
FROM TEACHER;
Пример. Сколько должностей в таблице TEACHER:
SELECT COUNT(DISTINCT Post) FROM TEACHER;
Множество агрегатных функций в списке select. Список Select может содержать множество агрегатных функций.
Example. Сколько профессоров в Вузе и какова их средняя ставка:
SELECT COUNT(*), SUM( Salary ) FROM TEACHER
WHERE UPPER(Post) = 'PROFESSOR';
Агрегатные функции с фразой GROUP BY. См. примеры фразы использования фразы GROUP BY.
3.2. Фразы 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(*)
11
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;
3.3. Фраза ORDER BY
Она испольуется для упорядочения результатов запроса. Можно упорядочивать
по любому столбцу таблицы,
по выражению над столбцами,
по списку столбцов или выражений.
Упорядочение по столбцу из списка select.
Пример. Выдать назвыание факультета и его декана, упорядочив результат по факультетам:
SELECT Name, Dean FROM FACULTY ORDER BY Name;
Упорядочение по столбцу таблицы. Можно упорядочивать по столбцу таблицы, даже если он не присутствует в списке select. Эта возможность не поддерживается стандартом ANSI, но допустима в SQL
Oracle.
12
Пример. Выдать имена преподавателей и их ставки, упорядочив результат по должностям:
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;
4. Варианты заданий
Далее приводится 18 вариантов заданий. Каждый вариант состоит из 7 запросов, которые относятся к следующим категориям (в порядке их следования):
13
1)Агрегатные функции на одной таблице
2)Агрегатные функции с учетом нескольких таблиц
3)NULL в агрегатных функциях, фраза DISTINCT, выражения в аргументе агрегатной ф-ции
4)GROUP BY (группирование по одному или нескольким столбцам)
5)Выражения в GROUP BY
6)Фраза HAVING (без вложенных запросов)
7)Фраза ORDER BY
ВНИМАНИЕ. В предлагаемых запросах используются константы (имена преподавателей, названия кафедр и факультетов, названия дисциплин), которые могут отсутствовать в вашей базе данных. ЗАМЕНЯЙТЕ ИХ НА ТЕ, КОТОРЫЕ ДЕЙСТВИТЕЛЬНО ИМЕЮТСЯ В ВАШЕЙ БАЗЕ ДАННЫХ!
14
4.1. Вариант 1
1) Сколько дисциплин приведено в таблице SUBJECT. Столбец результирующей таблицы должен иметь имя «К-во дисциплин»
2) Сколько дисциплин читается студентам на факультете 'компьютерные науки' |
|
3) Вывести дважды среднее количество студентов в группах факультета 'компьютерные науки'. |
|
В первом случае при подсчете среднего значения NULL игнорируются. Во втором случае |
NULL |
учитываются как значение 0. Почему эти значения могут отличаться? |
|
4) Для каждого факультета вывести его название и разницу между ее фондом и суммарными фондами финансирования всех его кафедр.
5)По каждой зарплате (salary+commission), выплачиваемой преподавателям факультета, деканом которого является Иванов, вывести эту зарплату, количество преподавателей с этой зарплатой и количество кафедр, на которых работают эти преподаватели. Соответствующие столбцы имеют названия: «Зарплата», «Кол-во преподавателей» и «Кол-во кафедр»
6)По каждому преподавателю факультета компьютерных наук вывести:
-его имя,
-должность и
-количество дисциплин, которые он преподает, при условии, что:
-он проводит занятия не более, чем в 3-х группах и
-он проводит занятия в не более, чем 2-х аудиториях
7) По каждой аудитории корпуса 6 вывести ее номер и названия кафедр, на которых проволятся занятия в этой аудитории студентам 3курса. Результат упорядочить по номеру аудитории в возрастающем порядке и по названию кафедры в убывающем порядке
4.2. Вариант 2
1) Вывести значение следующего выражения: суммарную зарплату (salary+commission) по всем преподавателям умножить на количество должностей и разделить на количество преподавателей. Столбец результирующей таблицы должен иметь имя «Выражение»
2)Сколько студентов и сколько групп на факультете 'компьютерные науки'
3)Вывести дважды среднее количество зарплаты (salary+commission) преподавателей факультета 'компьютерные науки'. В первом случае при подсчете среднего значения возможные значения NULL для salary и commission игнорируются. Во втором случае NULL учитываются как значение 0. Почему эти значения могут отличаться?
4) Для каждого преподавателя факультета 'компьютерные науки' вывести следующую информацию под столбцами с соответствующими именами
- имя |
- Преподаватель, |
- количество лекций, которые он имеет, и |
- Кол-во лекций |
- количество дисциплин, которые он читает. - Кол-во дисциплин
15