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

• Соединение таблиц с использованием эквисоединения.
• Выполнение внешних соединений и соединений таблицы с собой
• Включение дополнительных условий.

1. Напишите запрос для вывода названия отдела, местоположения отдела, количества служащих и среднего оклада по этому отделу. Назовите столбцы Name, Location, Number of People и Salary. Округлите средний оклад до ближайшего целого значения.

select department_name "Name", location_id "Location", count(emp.employee_id) "Number of People", round(avg(emp.salary)) "Salary"

from departments dep join employees emp

on(dep.department_id=emp.department_id) group by department_name,location_id order by "Name";

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

……………..

19 rows selected

select emp.last_name, emp.department_id, dep.department_name

from employees emp left join departments dep on (emp.department_id=dep.department_id);

1. Выведите список всех должностей в отделе 80 (должности в списке не должны повторяться) и местоположение отдела.

select distinct emp.job_id, dep.location_id from employees emp join departments dep on (dep.department_id=emp.department_id)

where emp.department_id=80;

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

10 rows selected.

select emp.last_name, dep.department_name from employees emp left join departments dep on (dep.department_id=emp.department_id)

where emp.last_name like'%a%';

1. Напишите запрос для вывода фамилии, должности, номера отдела и названия отдела всех служащих, работающих в городе Toronto.

select emp.last_name, emp.job_id, emp.department_id, dep.department_name from employees emp left join departments dep on (dep.department_id=emp.department_id)

join locations loc on (dep.location_id=loc.location_id) where loc.city='Toronto';

1. Выведите фамилии и номера всех служащих вместе с фамилиями и номерами их менеджеров. Назовите столбцы EMPLOYEE, Emp#, Manager и Mgr#. Сохраните свою команду SQL в файле lab4_6.sql.

…………….

select emp.last_name EMPLOYEE, emp.employee_id "Emp#", emp2.last_name "Manager ", emp2.employee_id "Mgr#" from employees emp left join employees emp2

on (emp.manager_id=emp2.employee_id) order by emp2.employee_id;

1. Измените файл lab4_6.sql так, чтобы получить фамилии всех служащих, включая Кинга, который не имеют менеджера. Упорядочьте результат по возрастанию номера служащего. Сохраните изменённый запрос в текстовом файле lab4_7.sql. Выполните запрос.

…………………………….

select emp.last_name EMPLOYEE, emp.employee_id "Emp#", emp2.last_name "Manager ", emp2.employee_id "Mgr#" from employees emp left join employees emp2

on (emp.manager_id=emp2.employee_id) order by "Emp#";

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

…………………

select emp.department_id DEPARTMENT, emp.last_name EMPLOYEE, emp2.last_name COLLEAGUE from employees emp join employees emp2 on

(emp.department_id=emp2.department_id) where emp.employee_id!=emp2.employee_id order by emp.department_id;

1. Покажите структуру таблицы JOB_GRADES. Создайте запрос для вывода фамилии, должности, названия отдела, оклада и категории (GRADE_LEVEL) всех служащих.

desc JOB_GRADES;

Name Null Type

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

GRADE_LEVEL VARCHAR2(3)

LOWEST_SAL NUMBER

HIGHEST_SAL NUMBER

select em.last_name, em.job_id, dep.department_name, em.salary, gr.grade_level from employees em join departments dep on (em.department_id=dep.department_id)

join job_grades gr on (em.salary between gr.lowest_sal and gr.highest_sal);

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

select emp.last_name, emp.hire_date from employees emp join employees emp2 on (emp.hire_date>emp2.hire_date)

where emp2.last_name='Davies' order by hire_date;

81 rows selected

1. По всем служащим, нанятым раньше своих менеджеров, выведите фамилии и даты найма самих служащих, а также фамилии и даты найма их менеджеров. Назовите столбцы Employee, Emp Hired, Manager и ManagerHired.

select emp.last_name "Employee", emp.hire_date "Emp Hired", emp2.last_name "Manager", emp2.hire_date "ManagerHired" from employees emp join employees emp2 on (emp.manager_id=emp2.employee_id)

where emp.hire_date<emp2.hire_date;

37 rows selected

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

select dep.department_id, dep.department_name, dep.location_id, count(emp.employee_id) from employees emp right join departments dep on (dep.department_id=emp.department_id)

group by dep.department_id, dep.department_name, dep.location_id order by dep.department_id;

1. Какие должности имеются в отделах Administration и Executive, а также сколько сотрудников занимают эти должности? Выведите первыми наиболее часто встречаемые должности.

select emp.job_id, count(emp.job_id) "FREQUENCY" from employees emp join jobs on (emp.job_id=jobs.job_id) join departments dep on (dep.department_id=emp.department_id)

where dep.department_name in ('Administration', 'Executive') group by emp.job_id order by "FREQUENCY" desc;

1. Покажите сотрудников, менеджеры которых имеют оклад выше $15000. выведите следующие сведения: фамилию сотрудника, фамилию менеджера, его оклад и уровень оклада (GRADE_LEVEL).

select emp.last_name,emp2.last_name MANAGER,emp2.salary, grad.grade_level from employees emp join employees emp2 on (emp.manager_id=emp2.employee_id)

join job_grades grad on (emp2.salary between grad.lowest_sal and highest_sal)

where emp2.salary>15000;

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

select emp.last_name, dep.department_name, dep.location_id, loc.city from departments dep right join employees emp on (dep.department_id=emp.department_id)left

join locations loc on (dep.location_id=loc.location_id) where emp.commission_pct>0;