pl/sql多表查询练习题02

--1.显示所有员工的姓名，部门号和部门名称

select last_name,department_id,department_name
from employees e,departments d
where e.department_id=d.department_id(+)


select last_name,e.department_id,department_name
from employees e left outer join departments d
on e.department_id =d.department_id

--2.查询90号部门员工的job_id和90号部门的location_id

select  distinct job_id,location_id
from employees e left outer join department d
on e.department_id=d.department_id
where d.department_id=90

--3.选择所有有奖金的员工的信息

select last_name,department_name,d.location_id,city
from employees e join departments d
on e.department_id=d.department_id
join lacation l
on d.location_id=l.location_id
where e.commission_pct is not null;

--4,选择city在toronto工作的员工的

select last_name,job_id,department_id,department_id
from employees e ,departments d,locations l
where e.department_id=d.department_id and l.city='toronto'
and d.location_id=l.lacation_id;