Практическое занятие 4

• Использование групповых функций
• Вывод данных по группам с помощью предложения GROUPBY
• Включение и исключение групп с помощью предложения HAVING

1. Напишите запрос для вывода самого высокого, самого низкого и среднего оклада по всем служащим, а также суммы всех окладов. Назовите столбцы Maximum, Minimum, Average и Sum. Округлите средний оклад до ближайшего целого значения. Сохраните свой запрос в файле lab5_6.sql.

select max(salary) "Maximum", min(salary) "Minimum", sum(salary) "Sum", round(avg(salary)) "Average" from employees;

1. Измените запрос в lab5_6.sql. так, чтобы получить самый низкий, самый высокий и средний оклады, а также сумму окладов по каждой должности. Сохраните изменённый запрос в файле lab5_5.sql. Выполните запрос из lab5_5.sql.

…………….

select job_id, max(salary) "Maximum", min(salary) "Minimum", sum(salary) "Sum", round(avg(salary)) "Average"

from employees group by job_id;

1. Напишите запрос для вывода должности и количества служащих, занимающих каждую должность.

select job_id, count(employee_id) CNT

from employees group by job_id;

1. Получите количество служащих, имеющих подчинённых, без их перечисления. Назовите столбец NumberofManagers. Используйте столбец MANAGER_ID для определения числа менеджеров.

select count(distinct(manager_id)) "Number of Managers" from employees;

1. Напишите запрос для вывода разности между самым высоким и самым низким окладами. Назовите столбец DIFFERENCE

select max(salary)-min(salary) "DIFFERENCE" from employees;

1. Напишите запрос для вывода номера каждого менеджера, имеющего подчинённых, и заработную плату самого низкооплачиваемого из его подчинённых. Исключите менеджеров для которых неизвестны их менеджеры. Исключите все группы, где минимальный оклад составляет менее $6000. Отсортируйте выходные строки в порядке убывания оклада.

select MANAGER_ID, min(salary) "MIN_SALARY" from employees where manager_id is not null group by MANAGER_ID

having min(salary)>=6000

order by "MIN_SALARY" desc;

9 rows selected

1. Напишите запрос для вывода общего количества служащих и количества служащих, нанятых в 2005, 2006, 2007 и 2008 годах. Дайте соответствующие заголовки столбцам.

select

count(employee_id) TOTAL,

count(decode(to_char(hire_date,'YYYY'),'2005',2005)) "2005",

count(decode(to_char(hire_date,'YYYY'),'2006',2006)) "2006",

count(decode(to_char(hire_date,'YYYY'),'2007',2007)) "2007",

count(decode(to_char(hire_date,'YYYY'),'2008',2008)) "2008"

from employees;

1. Напишите матричный запрос для вывода всех должностей и суммы заработной платы служащих, работающих в этой должности в отделах 20, 50, 80 и 90. Последний столбец должен содержать сумму заработной платы служащих этих отделов, занимающих каждую конкретную должность. Дайте столбцам соответствующие заголовки.

……………………………

select job_id "Job",

nvl(sum(decode(department_id,20,salary)),0) "Dept20",

nvl(sum(decode(department_id,50,salary)),0) "Dept50",

nvl(sum(decode(department_id,80,salary)),0) "Dept80",

nvl(sum(decode(department_id,90,salary)),0) "Dept90",

nvl(sum(decode(department_id,20,salary)),0)+nvl(sum(decode(department_id,50,salary)),0)+nvl(sum(decode(department_id,80,salary)),0)+

nvl(sum(decode(department_id,90,salary)),0) "Total" from employees group by job_id;

19 rows selected

Индивидуальное задание:

1.7. В названии отдела вывести только второе слово, если

название состоит из двух или более слов. Иначевывестипервое

слово.

select DEPARTMENT_NAME,

case instr(department_name,' ')

when 0 then department_name

else substr( substr(department_name,(instr(department_name,' '))+1, length(department_name))||' ', 1, instr(substr(department_name,(instr(department_name,' '))+1, length(department_name))||' ', ' '))

end result from departments;