Oracle复杂语句查询

显示所有雇员的姓名、工作和薪金，按工作降序排序，而工作相同的按薪金升序排序

select ename,job,sal from emp order by job desc,sal asc;

列出所有CLERK的姓名及其部门名称

select ename,dname from EMP,dept where emp.deptno=dept.deptno and job='CLERK';

列出从事同一种工作但不属于同一部门的这些员工

select distinct e1.*,e2.* from emp e1,emp e2 where e1.job=e2.job and e1.deptno!=e2.deptno;

列出所有入职日期早于其直接上级的所有雇员

select * from emp e1,emp e2 where e1.mgr=e2.empno and e1.hiredate<e2.hiredate;

列出薪金水平处于前四位的雇员

select e.*,rownum from (select * from emp order by sal desc) e where rownum<=4;

查询出每个部门薪水最高的人的姓名、薪水、部门编号，并且按照部门编号升序排列

select e.ename,e.sal,e.deptno from (select deptno,max(sal) m from emp group by deptno) t,

emp e where e.deptno=t.deptno and t.m=e.sal;

查询员工的姓名，部门名称，地理位置 和 薪水等级             

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

求平均薪水最高的部门的部门名字?

select dname from dept where deptno=(select deptno from emp group by deptno having avg(sal)=(select max(a) m from (select deptno,avg(sal) a from emp group by deptno)));

求比普通员工的最高薪水还要高的经理人名称

select ename from emp where sal>(select max(sal) from emp where job=’CLERK’) and job='MANAGER';

求部门平均薪水的等级

select s1.deptno,s1.a,s2.grade from (select deptno,avg(sal) a from emp group by deptno) s1,salgrade s2 where s1.a between s2.losal and s2.hisal;