Oracle-練習與答案

1、Oracle_練習與答案1. 求部門中薪水最高的人select ename,sal,emp.deptno from empjoin (select deptno,max(sal) max_sal from emp group by deptno) ton (emp.deptno = t.deptno and emp.sal = t.max_sal);2. 求部門平均薪水的等級select deptno, avg_sal, grade from(select deptno,avg(sal) avg_sal from emp group by deptno) tjoin salgradeon (t

2、.avg_sal between salgrade.losal and salgrade.hisal);3. 求部門平均的薪水等級select deptno, avg(grade) avg_sal_grade from(select deptno, grade from empjoin salgrade on emp.sal between salgrade.losal and salgrade.hisal)group by deptno;4. 雇員中有哪些人是經(jīng)理人select distinct e2.ename manager from emp e1 join emp e2 on e1.m

3、gr = e2.empno;select ename from emp where empno in (select mgr from emp);5. 不準用組函數(shù)，求薪水的最高值select distinct sal max_sal from empwhere sal not in(select e1.sal e1_sal from emp e1 join emp e2 on e1.sal (select max(sal) from(select e2.sal fromemp e1 right join emp e2 on e1.mgr = e2.empnowhere e1.ename is

4、 null) t);select ename from emp where empnoin (select mgr from emp) and sal (select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);/NOT IN遇到NULL則返回NULL，必須排除NULL值11. 求薪水最高的前5名雇員select empno,ename from(select * from emp order by sal desc)where rownum=6 and r=10;13. 求最后入職的5名員工select ename, to_char(hiredate,YYYY年MM月DD日) hiredate from(select t.*,rownum r from(select * fr