II.2.1. Подзапросы

Q2_1 Найти служащих , получающих минимальную зарплату

demo@10g> select last_ name,salary from EMPLOYEE

where salary=(select min(salary) from EMPLOYEE);

В запросе использована агрегатная функция min(salary), которая возвращает минимальное значение столбца «salary».

Q2_2 Найти сотрудников отделов продаж (SALES) , занимающих ту же должность , что и служащий SMITH

select employee_id,last_name,job_id,salary

from EMPLOYEE

where job_id=(select job_id

from EMPLOYEE

where last_name='SMITH')

and department_id in (select department_id

from DEPARTMENT

where name='SALES');

Q2_3 Найти служащих, получающих минимальный оклад в своем отделе

demo@10g> select DEPARTMENT_ID dept,last_name name,salary

from EMPLOYEE

where (salary,DEPARTMENT_ID) in (

select min(salary),DEPARTMENT_ID

from EMPLOYEE

group by DEPARTMENT_ID);

Подзапрос «select min(salary),DEPARTMENT_ID from EMPLOYEE group by DEPARTMENT_ID» рассмотрим отдельно, так как здесь впервые в приводимых примерах использована опция «group by».

demo@10g> select min(salary),DEPARTMENT_ID

from EMPLOYEE

group by DEPARTMENT_ID ;

MIN(SALARY) DEPARTMENT_ID

----------- -------------

950 30

750 13

1800 14

800 20

750 23

1300 10

800 12

7 строк выбрано.

Казалось бы, добавить к перечню извлекаемых столбцов «last_name», и - нужный результат будет получен

demo@10g> select last_name, min(salary),DEPARTMENT_ID

from EMPLOYEE

group by DEPARTMENT_ID;

demo@10g> /

select last_name, min(salary),DEPARTMENT_ID

*

ошибка в строке 1:

ORA-00979: выражение не является выражением GROUP BY

Но получено сообщение об ошибке, так как если требуется выводить минимум значения зарплаты по сгруппированным строкам, в опции GROUP BY надо указывать все столбцы (кроме столбца salary, конечно, по которому ищется минимум), составляющие выводимые строки. Подкорректируем запрос с учетом полученной ошибки

demo@10g> select last_name, min(salary),DEPARTMENT_ID

from EMPLOYEE

group by DEPARTMENT_ID,last_name;

Вот так! Фамилии-то в отделах разные, поэтому мы получили 32 группы строк по одной строке в каждой из них. Для одной строки минимум и выводится, совпадающий со значением зарплаты в этой строке. Изменим значение столбца «last_name», установив в нем одинаковое значение для всех 32-х строк

demo@10g> update employee set last_name='Иванов';

и повторим предыдущий запрос

select last_name, min(salary),DEPARTMENT_ID

from EMPLOYEE

group by DEPARTMENT_ID,last_name;

Вот теперь, так как фамилии в отделах одинаковые, запрос извлекает семь групп

одинаковых строк, «принадлежащих» каждая одному отделу. И уже внутри каждой из таких групп строк ищется min(salary).

!!!Вернем столбцу last_name в таблице EMPLOYEE прежние значения

demo@10g> rollback;

Q2_4 Получить перечень сотрудников отдела 14, получающих оклад больше минимального в отделе 12

demo@10g> select last_name,salary,job_id,department_id dept

from EMPLOYEE

where department_id=14 and

salary > (select min(salary) from EMPLOYEE

where department_id=12)

order by salary,last_name;

Q2_5 Получить перечень сотрудников отдела 12, получающих оклад больше любого в отделе 14

demo@10g> select last_name,salary,job_id,department_id dept

from EMPLOYEE

where department_id=12 and

salary > any(select distinct salary from EMPLOYEE

where department_id=14)

order by salary,last_name;

Часть запроса « …salary > any()…» означает условие: зарплата больше любого значения в скобках.

Q2_6 Получить отделы, в которых число сотрудников больше 2-х и средний оклад больше среднего оклада отдела 14

demo@10g> select department_id dept,round(avg(salary),0) from EMPLOYEE

group by department_id

having count(employee_id) > 2

and avg(salary) > (select avg(salary) from EMPLOYEE

where department_id = 14);

Здесь использованы агрегатные функции avg() и count(), а также – опция having, смысл коорой понятен из текста запроса.

Q2_7 Определить год когда в компанию было зачислено наибольшее количество служащих

demo@10g> select to_char(hire_date,'YYYY') year,count(employee_id) num

from EMPLOYEE

group by to_char(hire_date,'YYYY')

having count(employee_id) = (select max(count(employee_id))

from EMPLOYEE

group by to_char(hire_date,'YYYY'));