Oracle之再展身手 练习题2（多表）

准备

• 一颗强大的心
• 工具：连接了oracle的SQL developer
• 环境：Oracle的Scott用户

问题

1、列出所有雇员的姓名及直接上级的姓名

select e.ename ename,m.ename mname from emp e join emp m on e.mgr=m.empno;

2、列出部门名称和这些部门的雇员，同时列出那些没有雇员的部门

select d.deptno,e.* from dept d left outer join emp e on e.deptno=d.deptno;

3、所有部门在NEW YORK的员工姓名
子查询法

select ename from emp where deptno=
		(select deptno from dept where loc='NEW YORK');

内连接法

select ename from dept d inner join emp e 
					on e.deptno=d.deptno where loc='NEW YORK';

4、员工SMITH的姓名，部门名称

select e.ename,d.dname from emp e inner join dept d 
	on e.deptno=d.deptno where ename='SMITH';

5、员工姓名、部门名称、工资、工资级别，工资级别大于4级

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

6、King 和 FORD管理的员工姓名及其经理姓名

select m.ename,e.ename from emp e join emp m 
	on e.mgr=m.empno where m.ename in('KING','FORD');

7、员工名，参加工作时间，经理名，参加工作时间

select e.ename,e.hiredate,m.ename,m.hiredate from emp e join emp m
	 on e.mgr=m.empno where e.hiredate<m.hiredate;

8、 部门名称中，带S字符的部门员工的工资总和,部门人数

select sum(sal),count(*)
from emp e where e.deptno in
	(select d.deptno from dept d where d.dname like '%S%')
group by e.deptno;

9、所有员工的年工资，按年薪从低到高排序

select e.*,sal+nvl(comm,0)*12 nx from emp e order by  nx;

10、 各种工作的最低工资以及从事此工作的雇员姓名

select e.ename,e.sal,e.job
from emp e,(select min(sal) losal,job from emp group by job) m
where e.sal=m.losal and e.job=m.job;

11、所有部门的详细信息和部门人数

select d.*,sump
from dept d left outer join
(select count(*) sump,deptno from emp group by deptno)m
on d.deptno=m.deptno;

12、列出所有员工的姓名、部门名称、工资

select e.ename,d.dname,e.sal from emp e join dept d on e.deptno=d.deptno;

13、所有员工的姓名，老板名字

select ename,(select ename from emp where mgr is null) boss
from emp;

14、每个雇员的编号、姓名、工资、部门名称、工资等级

select e.empno,e.ename,e.sal,d.dname,s.grade 
from emp e inner join dept d on e.deptno=d.deptno 
inner join salgrade s on e.sal between s.losal and s.hisal;

15、按职位分组，求每个职位的最高工资、最低工资及平均工资

select max(sal),min(sal),floor(avg(sal)) from emp  group by job;

16、统计平均工资大于2000的部门详情

select * from dept d where d.deptno in
	(select deptno from emp group by deptno having avg(sal)>2000)