Oracle练习与答案.doc

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.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. 雇员中有哪些人是经理人select distinct e2.ename manager from emp e1 join emp e2 on e1.mgr = e2.empno;select ename from emp where empno in (select mgr from emp);5. 不准用组函数，求薪水的最高值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 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