oracle子连接查询,oracle之连接查询及子查询举例

一 举例用表为：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编号在里面是领导，不在里面是普通员工的方式计算。