pl/sql多表查询练习题01

--1，多表连接查询时，若两个表有同名的列，必须使用表的别名对列名进行引用，否则出错！

--2，查询公司员工的last_name,department_name,city

select last_name,department_name,city
from departments s,employees e,locations l
where d.department_id=e.department_id and d.locaton_id=l.locaton_id;

--3,查询出 last_name为‘Chen’的manager的信息，(员工的manager是某员工的employee_id)
1)列如：老张的员工工号为：“1001”,我的员工工号为：“1002”，
我的manager_id为“1001”---我的manager是“老张”

2）通过两条sql查询：

select manager_id
from employees
where lower(last_name)='chen'  --反回的结果为108

select *
from employees
where employee_id = 108

3)通过一条sql查询（自连接）：

select m.*
from employees e,employees m
where e.manager_id=m.employee_id and e.last_name='Chen';

4)通过子查询：

select *
from employees
where employee_id=(
                   select manager_id
                   from employees
                   where last_name='Chen'
                   )

--4,查询每个员工的last_name和GRASE_LEADEL(在JOB_GRASES表中).....非等值连接

select lest_name,salary,grade_level,lowest_sal,highest_sal
from employees e, job_grades j
where e.salary>=j.lowest_sal and e.salary<=j.highest_sal;

--5,左外连接和右外连接

select last_name,e.separtment_id,department_name
from employees e,separtments d
where e.department_id=d.department_id(+);

select last_name,e.separtment_id,department_name
from employees e,separtments d
where e.department_id(+)=d.department_id;

理解“（+）”的位置：以左外连接为例，因为左表需要返回更多的记录，
右表需要“加上”更多的记录，所以在右表的链接条件上加上“（+）”