-- 求每个雇员的姓名，工资，工资等级

?

select a.ename, a.sal, b.grade from emp a, salgrade b
where a.sal between b.losal and b.hisal;

?

-- 求每个员工的姓名，部门名，部门号

?

select a.ename, b.dname, b.deptno
  from emp a, dept b
 where a.deptno = b.deptno;

?

-- 求每个人的名字和他的经理人的名字，用外连接把KING也取出来

?

select a.ename, b.ename mgrname from emp a, emp b
where a.mgr = b.empno;

?

?

select a.ename, b.ename mgrname from emp a, emp b
where a.mgr = b.empno(+);

?

-- 求公司中谁的薪水最高

?

select * from emp a where a.sal = (select max(sal) from emp);

?

-- 求薪水在部门的平均薪水之上的雇员姓名和及薪水

?

select a.ename, a.sal, a.deptno
  from emp a, (select avg(sal) avgsal, deptno from emp group by deptno) b
 where a.sal > b.avgsal
   and a.deptno = b.deptno;

?

第一步，求每个部门的最高薪资

?

select max(sal) maxsal, deptno from emp group by deptno;

?

第二步，匹配每个部门的最高工资，查询最高工资员工姓名

select a.empno, a.ename, b.deptno, c.dname
  from emp a,
       (select max(sal) maxsal, deptno from emp group by deptno) b,
       dept c
 where a.deptno = b.deptno
   and b.deptno = c.deptno
   and a.sal = b.maxsal;

?

-- 求部门平均薪水的等级

?

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

?

-- 求部门平均的薪水等级

后续补上sql

-- 雇员中有哪些人是经理人

?

select * from emp where job in ('MANAGER', 'PRESIDENT', 'ANALYST');

?

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

?

select ename, sal
  from emp
 where sal > (select max(sal)
                from emp
               where job not in ('MANAGER', 'PRESIDENT', 'ANALYST'))
   and job in ('MANAGER', 'PRESIDENT', 'ANALYST');

?

?

select c.ename, c.sal
  from (select b.ename, b.sal, rownum rn
          from (select a.ename, a.sal from emp a order by sal desc) b) c
 where c.rn <= 5;

?

?

select c.ename, c.sal
  from (select b.ename, b.sal, rownum rn
          from (select a.ename, a.sal from emp a order by sal desc) b) c
 where c.rn between 6 and 10;

?

-- 求最后入职的5名员工

select c.ename, c.hiredate
  from (select b.ename, b.hiredate, rownum rn
          from (select a.ename, a.hiredate from emp a order by hiredate desc) b) c
 where c.rn <= 5;

?

-- 求每个部门中薪水最高的前两名雇员

?

select * from emp e 
where ( select count(*) from emp where sal > e.sal 
and deptno = e.deptno ) < 2
order by e.deptno,e.sal desc;

?

-- 不用组函数求最高工资

?

select a.sal from (select * from emp order by sal desc) a where rownum = 1;
select e.sal from emp e where e.sal not in(select e2.sal from emp e1,emp e2 where e1.sal>e2.sal);
select distinct sal from emp
minus
select distinct e2.sal from emp e1,emp e2 where e1.sal>e2.sal;

?

-- 求平均薪水最高的部门名称

?

select a.dname, b.deptno
  from dept a,
       (select avg(sal) avgsal, deptno from emp group by deptno) b,
       (select max(a.avgsal) maxsal
          from (select avg(sal) avgsal, deptno from emp group by deptno) a) c
 where a.deptno = b.deptno
   and b.avgsal = c.maxsal;

?

以上题目均自己所做，如有错误，请及时指正，谢谢。