Oracle数据库练习

多表查询练习

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(+);