第五章 oracle多表连接初级测试

–练习1
1.写一个查询，显示所有员工姓名，部门编号，部门名称。

select e.ename,e.deptno,d.dname
from emp e,dept d
where e.deptno=d.deptno;

2.写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金

select e.ename,d.loc,e.comm
from emp e,dept d
where d.loc='CHICAGO' and e.comm<>0;

3.写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。

select e.ename,d.loc
from emp e,dept d
where e.deptno=d.deptno and e.ename like '%A%';

–练习2
1.查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。

select e.empno,e.ename,e.sal,s.grade,d.loc
from emp e,dept d,salgrade s
where e.deptno=d.deptno and (e.sal between s.losal and s.hisal)
order by e.sal asc;

–思考：查询每个员工的姓名和直接上级姓名？

–练习3
1.查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号。

select worker.ename,worker.empno,manager.ename,worker.mgr
from emp worker,emp manager,dept
where worker.mgr = manager.empno(+)
and worker.deptno = dept.deptno 
and loc in ('NEW YORK','CHICAGO');

2.第上一题的基础上，添加没有经理的员工King，并按照员工编号排序。

select worker.ename,worker.empno,manager.ename,worker.mgr
from emp worker,emp manager,dept
where worker.mgr = manager.empno(+)
and worker.deptno = dept.deptno 
and loc in ('NEW YORK','CHICAGO')
order by worker.empno asc;

3.查询所有员工编号，姓名，部门名称，包括没有部门的员工也要显示出来。

select e.empno,e.ename,d.dname
from emp e,dept d
where e.deptno=d.deptno(+);

–练习4
使用SQL-99写法，完成如下练习
1.创建一个员工表和部门表的交叉连接。

select * from emp cross join dept;

2.使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期

select ename,dname,hiredate
from emp natural join dept
where hiredate>'01-5月-80'

3.使用USING子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点

select e.ename,d.dname,d.loc
from emp e join dept d
using(deptno)
where d.loc in ('CHICAGO')

4.使用ON子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点，薪资等级

select e.ename,d.dname,d.loc,g.grade
from emp e join dept d on e.deptno=d.deptno
join salgrade g on e.sal between g.losal and g.hisal
where d.loc in('CHICAGO')

5.使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。

select wk.ename,mg.ename
from emp wk left outer join emp mg
on wk.mgr=mg.empno;

6.使用右连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。

select wk.ename,mg.ename
from emp mg right outer join emp wk
on wk.mgr=mg.empno;

–课后作业
1.显示员工SMITH的姓名，部门名称，直接上级名称

select wk.ename,d.dname,mg.ename
from emp wk,dept d,emp mg
where wk.deptno=d.deptno and wk.mgr=mg.empno and wk.ename='SMITH';

2.显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级。

select e.ename,d.dname,e.sal,s.grade
from emp e,dept d,salgrade s
where e.deptno=d.deptno and e.sal between s.losal and s.hisal and s.grade>'4';

3.显示员工KING和FORD管理的员工姓名及其经理姓名。

select e.ename KING和FORD管理的员工姓名,mg.ename 经理姓名
from emp e,emp mg
where e.mgr=mg.empno and mg.ename in('KING','FORD')

4.显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早。

select wk.ename,wk.hiredate,mg.ename
from emp wk,emp mg
where wk.mgr=mg.empno and wk.hiredate>mg.hiredate