Database Oracle SQL - 실습 5

#1번 문제 
select e.last_name, e.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id;

#2
select e.job_id, d.location_id
from employees e, departments d
where e.department_id = 80 
AND e.department_id = d.department_id
group by e.job_id, d.location_id;

#3
select e.last_name, d.department_name, d.location_id, l.city
from employees e, locations l, departments d
where d.department_id = e. department_id
AND l.location_id = d.location_id
AND e.commission_pct IS NOT NULL;

#4
select e.last_name, d.department_name
from employees e, departments d
where e.department_id = d.department_id
AND e.last_name LIKE '%a%';

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

#6
select 
emp.last_name "employee",
emp.employee_id "EMP#",
man.last_name "Manager",
man.employee_id "MGR#"
from (select * from employees) emp
join (select * from employees) man
on emp.manager_id = man.employee_id;

#7
select selected.last_name, 
selected.department_id,
colleague.last_name as "COLLEAGUE"
from 
(select 
last_name, department_id 
from employees 
where last_name = 'Grant') 
selected
join (select last_name,department_id from employees) colleague
on selected.department_id = colleague.department_id
order by colleague.last_name;

#8
select other.last_name, other.hire_date
from 
(select last_name, hire_date 
from employees where last_name = 'Davies') man
join (select last_name, hire_date from employees) other
on man.hire_date < other.hire_date;

#9
select aa.last_name, aa.hire_date,
bb.last_name, bb.hire_date
from (select * from employees) aa
join (select * from employees) bb
on (aa.manager_id = bb.employee_id)
where (aa.hire_date < bb.hire_date);