Лабораторная работа 1

Лабораторная работа 5

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

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

LAST_NAME	DEPARTMENT_ID	DEPARTMENT_NAME
Whalen	10	Administration
Hartstein	20	Marketing
Fay	20	Marketing
Mourgos	50	Shipping
Rajs	50	Shipping
Davies	50	Shipping
Matos	50	Shipping
Vargas	50	Shipping

……………..

19 rows selected

select e.last_name,d.department_id,d.department_name from employees as e,departments as d where e.department_id=d.department_id;

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

JOB_ID	LOCATION_ID
SA_MAN	2500
SA_REP	2500

select distinct e.job_id,d.location_id from employees as e , departments as d where e.department_id=80 and d.department_id=80;

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

LAST_NAME	DEPARTMENT_NAME	LOCATION_ID	CITY
Zlotkey	Sales	2500	Oxford
Abel	Sales	2500	Oxford
Taylor	Sales	2500	Oxford

select e.last_name,d.department_name,d.location_id,l.city from (employees as e left join departments as d on e.department_id=d.department_id) left join locations as l on d.location_id=l.location_id where e.salary is not null;

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

LAST_NAME	DEPARTMENT_NAME
Whalen	Administration
Hartstein	Marketing
Fay	Marketing
Rajs	Shipping
Davies	Shipping
Matos	Shipping
Vargas	Shipping
Taylor	Sales
Kochhar	Executive
De Haan	Executive

10 rows selected.
select e.last_name,d.department_name from employees as e,departments as d where e.department_id=d.department_id and e.last_name like '*a*';

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

LAST_NAME	JOB_ID	DEPARTMENT_ID	DEPARTMENT_NAME
Hartstein	MK_MAN	20	Marketing
Fay	MK_REP	20	Marketing

select e.last_name,e.job_id,d.department_id,d.department_name from (employees as e left join departments as d on e.department_id=d.department_id) left join locations as l on d.location_id=l.location_id where l.city='Toronto';

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

Employee	EMP#	Manager	Mgr#
Kochhar	101	King	100
De Haan	102	King	100
Mourgos	124	King	100
Zlotkey	149	King	100

…………….

select e.first_name+' '+e.last_name as Employee,e.employee_id as `Emp#`,f.first_name+' '+f.last_name as Manager,f.employee_id as `Mgr#` from employees as e,employees as f where f.employee_id=e.manager_id;

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

Employee	EMP#	Manager	Mgr#
King	100
Kochhar	101	King	100
De Haan	102	King	100
Hunolds	103	De Haan	102
Ernst	104	Hunold	103
Lorentz	107	Hunold	103

select e.first_name+' '+e.last_name as Employee,e.employee_id as `Emp#`,f.first_name+' '+f.last_name as Manager,f.employee_id as `Mgr#` from employees as e left join employees as f on e.manager_id=f.employee_id;

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

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

DEPARTMENT	EMPLOYEE	COLLEAGUE
20	Fay	Hartstein
20	Hartstein	Fay
50	Davies	Matos
50	Davies	Mourgos
50	Davies	Rajs
50	Davies	Vargas

select e1.department_id as DEPARTMENT ,e1.last_name as EMPLOYEE,e2.last_name as COLLEAGUE from employees as e1 left join employees as e2 on e1.department_id=e2.department_id and e1.employee_id<>e2.employee_id;

…………………

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

Name	Null?	Type
GRADE_LEVEL		VARCHAR2(3)
LOWEST_SAL		NUMBER
HIGHEST_SAL		NUMBER

LAST_NAME	JOB_ID	DEPARTMENT_NAME	SALARY	GRA
Matos	ST_CLERK	Shipping	2600	A
Vargas	ST_CLERK	Shipping	2500	A
Lorentz	IT_PROG	IT	4200	B
Mourgos	ST_MAN	Shipping	5800	B
Rajs	ST_CLERK	Shipping	3500	B
Davies	ST_CLERK	Shipping	3100	B
Whalen	AD_ASST	Administration	4400	B

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

LAST_NAME	HIRE_DATE
Lorentz	07-FEB-99
Mourgos	16-NOV-99
Matos	15_MAR-98
Vargas	09-JUL-98
Zlotkey	29-JAN-00
Taylor	24-MAR-98
Grant	24-MAY-99
Fay	17-AUG-97

select t1.last_name as `NAME`,t1.hire_date as `HIRE DATE` from employees as t1, employees as t2 where t2.last_name='Davies' and t1.hire_date>t2.hire_date;

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

Employee	Emp Hired	Manager	Mgr hired
Whalen	17-SEP-87	Kochhar	21-SEP-89
Hunold	03-JAN-90	De Haan	13-JAN-93
Rajs	17-OCT-95	Mourgos	16-NOV-99
Davies	29-JAN-97	Mourgos	16-NOV-99
Matos	15-MAR-98	Mourgos	16-NOV-99
Vargas	09-JUL-98	Mourgos	16-NOV-99
Abel	11-MAY-96	Zlotkey	29-JAN-00
Taylor	24-MAR-98	Zlotkey	29-JAN-00
Grant	24-MAY-99	Zlotkey	29-JAN-00

select t1.last_name as `Employee`,t1.hire_date as `Emp Hired`,t2.last_name as `Manager`,t2.hire_date as `Mgr Hired` from employees as t1 left join employees as t2 on t1.manager_id=t2.employee_id where t1.hire_date>t2.hire_date;

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

DEPARTMENT_ID	DEPARTMENT_NAME	LOCATION_ID	COUNT(E.EMPLOYEE_ID)
10	Administration	1700	1
20	Marketing	1800	2
50	Shipping	1500	5
60	IT	1400	3
80	Sales	2500	3
90	Executive	1700	3
110	Accounting	1700	2
190	Contracting	1700	0

select e.department_id,d.department_name,d.location_id,count(e.employee_id) from employees as e left join departments as d on e.department_id=d.department_id group by e.department_id,d.department_name,d.location_id;

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

JOB_ID	FREQUENCY
AD_VP	2
AD_ASST	1
AD_PRES	1

select e.job_id,count(e.job_id) as FREQUENCY from employees as e left join departments as d on e.department_id=d.department_id where d.department_name='Administration' or d.department_name='Executive' group by e.job_id order by count(e.job_id) desc;

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

LAST_NAME	MANAGER	SALARY	GRA
Kochhar	King	24000	E
De Haan	King	24000	E
Mourgos	King	24000	E
Zlotkey	King	24000	E
Hartstein	King	24000	E
Whalen	Kochhar	17000	E
Higgins	Kochhar	17000	E
Hunold	De Haan	17000	E

Select t1.last_name as Name,t2.last_name as Manager,t2.salary as Salary from employees as t1,employees as t2 where t2.employee_id=t1.manager_id and t2.salary>15000;