Oracle连接与嵌套查询

1. 查询最高工资员工的名字，工作岗位
select ename,job,sal from emp where sal=(select max(sal) from emp);
2. 算出部门 30 中得到最多奖金的员工姓名
select ename from emp where deptno=30 and comm=(select max(comm) from emp where deptno=30);
3. 找出工资比ford高的员工的信息
select * from emp where sal>(select sal from emp where ename='FORD');
4. 找出不在部门20，且比部门20的任何一个员工的工资都高的员工的姓名和所在部门名称。
SELECT ename,dname FROM EMP e join dept d on e.deptno=d.deptno WHERE e.deptno<>20 and sal>all(select sal from emp where deptno=20);
5. 查询与SMITH的部门和岗位完全相同的所有雇员。

6. 查询每个部门工资最高的员工
select ename,sal,deptno from emp where (deptno,sal) in(select deptno,max(sal) from emp group by deptno);
7. 显示员工‘FORD’的上级
select E.ename 员工,m.ename 领导 from emp e join emp m on e.mgr=m.empno where e.ename='FORD';
8. 查询工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30);
9. 查询工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号

10. 查询emp表中各部门平均工资的最大值和其所在的部门编号
select max(avg(sal)) from emp group by deptno;
select deptno,avg(sal) from emp group by deptno having avg(sal)=(select max(avg(sal)) from emp group by deptno);
11. 查询emp表中平均工资的最大值和其所在的部门编号及部门名称
select e.deptno,dname,avg(sal) from emp e join dept d on e.deptno=d.deptno group by e.deptno,dname having avg(sal)=(select max(avg(sal)) from emp group by deptno);
12. 查询所有在任职职位上工资最高的员工
select * from emp where (job,sal)in(select job,max(sal) from emp group by job);
13. 查找所有工资高于自己本部门平均工资的员工
select e.*,avgsal from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) avge where e.deptno=avge.deptno and sal>avgsal;
select * from emp e where sal>(select avg(sal) from emp where deptno=e.deptno);
14. 显示每个部门的信息和人员数量
select dept.*, shul from dept,(select deptno,count(ename) shul from emp group by deptno) shul where dept.deptno=shul.deptno(+);
15. 查找工资大于同职位的平均工资的员工信息
select * from emp,(select job,avg(sal) avgsal from emp group by job) j where emp.job=j.job and emp.sal>avgsal;
select * from emp e where sal>(select avg(sal) from emp where job=e.job);
16. 查询员工表中是领导的员工
select * from emp where empno in(select mgr from emp);in or
17. 查询员工表中不是领导的员工
select * from emp where empno not in(select mgr from emp where mgr is not null);