多表查询练习
1.显示所有员工的姓名,部门号和部门名称。
select last_name,e.department_id,department_name from employees e,departments d where 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 departments d on e.department_id = d.department_id where d.department_id = 90;
3.选择所有有奖金的员工的last_name , department_name , location_id , city
select last_name,department_name,d.location_id,city from employees e join departments d
on e.department_id = d.department_id join locations l on d.location_id = l.location_id
where e.commission_pct is not null;
4.选择city在Toronto工作的员工的last_name , job_id , department_id , department_name
select last_name , job_id , e.department_id , department_name
from employees e ,departments d,locations l
where e.department_id = d.department_id and l.city = ‘Toronto’ and d.location_id = l.location_id;
5.选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似于下面的格式
employees Emp# manager Mgr#
kochhar 101 king 100
select e1.last_name “employees”,e1.employee_id “Emp#”,e2.last_name"Manger",e2.employee_id “Mgr#”
from employees e1,employees e2
where e1.manager_id = e2.employee_id(+);