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

o Создание подзапросов для выборки данных по неизвестным критериям

o Использование подзапросов для выявления значений, существующих в одном наборе данных и отсутствующих в другом

1. Создайте запрос для вывода фамилии и даты найма каждого служащего, работающего в одном отделе с Zlotkey. Исключите Zlotkey из выходных данных.

select emp.last_name, emp.hire_date from employees emp where emp.department_id in (select department_id from employees where last_name='Zlotkey') and emp.last_name!='Zlotkey';

1. Создайте запрос для вывода номеров и фамилий всех служащих, оклад которых выше среднего. Отсортируйте данные в порядке увеличения окладов.

select emp.employee_id,emp.last_name from employees emp where emp.salary > (select avg(salary) from employees) order by salary;

51 rows selected

1. Создайте запрос для вывода номеров и фамилий всех служащих, работающих в одном отделе с любым служащим, фамилия которого содержит букву «u». Сохраните свой запрос в тестовом файле lab6_3.sql. Выполните запрос.

select emp.employee_id,emp.last_name from employees emp where emp.department_id in (select department_id from employees where lower(employees.last_name) like '%u%');

1. Создайте запрос для вывода фамилии, номера отдела и должности каждого служащего, идентификатор местоположения отдела которого равен 1700.

select emp.last_name, emp.department_id, emp.job_id from employees emp where emp.department_id in

(select departments.department_id from departments where location_id=1700);

1. Получите список фамилий и окладов всех служащих, подчинённых Кингу.

select emp.last_name, emp.salary from employees emp where emp.manager_id in

(select employee_id from employees where last_name ='King');

1. Получите номер отдела, фамилию и должность для каждого служащего, работающего в администрации (department_name = ‘Executive’).

select emp.department_id, emp.last_name, emp.job_id from employees emp where emp.department_id in

(select department_id from departments where department_name='Executive');

1. Измените lab6_3.sql для вывода номеров, фамилий и окладов всех служащих, которые работают в одном отделе с любым служащим с буквой «u» в фамилии и оклады которых превышают средний. Сохраните новый запрос в файле lab6_7.sql. Выполните его.

select emp.employee_id,emp.last_name, emp.salary from employees emp where emp.department_id in

(select department_id from employees where lower(employees.last_name) like '%u%') and salary > (select avg(salary) from employees);

36 rows selected

Покажите номер отдела с наивысшей средней заработной платой и наименьший оклад работающего в select department_id, minsal from

(select department_id, avg(salary) avgsal, min(salary) minsal from employees group by department_id)

where avgsal=(select max(avg(salary)) from employees group by department_id);

1. Выведите номера , наименования и местоположения отделов, в которых не работают торговые представители (job_id = ‘SA_REP’).

select dep.department_id, dep.department_name, dep.location_id from departments dep

where dep.department_id not in (select nvl(department_id,0) from employees where job_id='SA_REP');