显示所有雇员的姓名、工作和薪金,按工作降序排序,而工作相同的按薪金升序排序
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;