一 举例用表为:scott模式的EMP表、DEPT表、SALGRADE表、 BONUS表
1:查询所有的管理者编号及姓名。
select distinct e2.empno 领导编号, e2.ename 领导姓名 from emp e2
join emp e1
on e1.mgr=e2.empno
order by e2.empno
或者
select distinct e2.empno 领导编号, e2.ename 领导姓名 from emp e2
join emp e1
on e1.mgr=e2.empno
order by e2.empno
2: 查询所有管理者管理的下属员工信息。
select distinct e2.empno 领导编号, e2.ename 领导姓名 from emp e2
join emp e1
on e1.mgr=e2.empno
order by e2.empno
3:查询工资大于同职位的平均工资的员工信息。
select e.ename,e.sal,e.job,e1.avg_sal
from emp e
join
(select job,avg(sal) avg_sal from emp group by job) e1
on e1.job=e.job and e.sal>e1.avg_sal
order by e.job
4:查询每个部门薪水最高的员工信息。
select e.ename,e.sal ,e.deptno,e1.max_sal
from emp e
join
(select max(sal) max_sal,deptno from emp group by deptno)e1
on e1.deptno=e.deptno and e1.max_sal=e.sal
order by e.deptno
5:查询每个部门的平均薪水的薪水等级
select e.deptno,e.avg_sal,s.grade
from
(select avg(sal) avg_sal,deptno from emp group by deptno)e
join salgrade s
on e.avg_sal between s.losal and s.hisal
order by e.deptno
6:查询每个部门平均的薪水等级
select e1.deptno,avg(e1.grade)
from
(select e.deptno,s.grade
from emp e
join salgrade s
on e.sal between s.losal and s.hisal)e1
group by e1.deptno
order by e1.deptno
7:查询哪些是经理
select distinct e.ename from emp e
join emp e1
on e1.mgr=e.empno
或者
select e.ename from emp e
where e.empno in
(select distinct mgr from emp)
8:不用聚合函数求薪水最高的人
select a.ename,a.sal from (select e.ename,e.sal,ROWNUM rn from(select emp.ename,emp.sal from emp order by emp.sal desc)e )a where rn<2
9:求平均薪水最高的部门编号
select deptno from
(select e.deptno,avg(sal) avg_sal
from emp e
group by e.deptno)b
where b.avg_sal=
(select max(avg_sal) from
(select e.deptno,avg(sal) avg_sal
from emp e
group by e.deptno) a)
10:求平均薪水最高的部门名称
select d.dname from
dept d
join
(select deptno from
(select e.deptno,avg(sal) avg_sal
from emp e
group by e.deptno)b
where b.avg_sal=
(select max(avg_sal) from
(select e.deptno,avg(sal) avg_sal
from emp e
group by e.deptno) a))c
on d.deptno=c.deptno
10:求平均薪水的等级最低的部门名称
select dname from dept
where dept.deptno=
(select deptno from
(select a.deptno,s.grade from
(select e.deptno,avg(e.sal) avg_sal
from emp e
group by e.deptno)a
join salgrade s
on a.avg_sal between s.losal and s.hisal)c
where c.grade=
(select min(grade) from
(select a.deptno,s.grade from
(select e.deptno,avg(e.sal) avg_sal
from emp e
group by e.deptno)a
join salgrade s
on a.avg_sal between s.losal and s.hisal)b))
11:求部门经理中平均薪水最低的部门名称
select d.dname from
dept d
where d.deptno=
(select deptno from
(select avg(sal) avg_sal,deptno
from emp e
where e.empno in
(select distinct mgr from emp)
group by e.deptno)a
where a.avg_sal=
(select min(avg_sal) from
(select avg(sal) avg_sal,deptno
from emp e
where e.empno in
(select distinct mgr from emp)
group by e.deptno)))
12:比普通员工的最高薪水还要高的领导名称
select ename,sal from
emp e2
where e2.empno in
(select distinct mgr from emp where mgr is not null)
and e2.sal>
(select max(sal) from
(select e.sal
from emp e
where e.empno not in
(select distinct mgr from emp where mgr is not null))) 注释:用关联查询,查普通员工时候会把既是领导又是普通员工的角色的薪水也查出来,会把薪水算到普通员工里面,由于题目要求只要是领导的就不能再算普通员工,所以不适用关联查询的方式而使用 查询mgr,再通过empno编号在里面是领导,不在里面是普通员工的方式计算。