Oracle练习题（五）

Oracle练习题（五）

（1） 查询平均工资低于2000的部门及其员工信息

select * from emp where deptno in (select deptno from emp group by deptno having avg(sal)<2000);

（2） 查询每个部门中的员工数量、平均工资和平均工作年限

select deptno,count(*),avg(sal),trunc(avg(months_between(sysdate,hiredate)/12))from emp group by deptno;

（3） 查询各个部门的详细信息以及部门人数、部门平均工资

select d.*,e.c,e.avg_sal from dept d,(select deptno,count(1) c, avg(sal) avg_sal from emp group by deptno) e where d.deptno = e.deptno;

（4） 查询各种工作的最低工资

select job,min(sal) from emp group by job;

（5） 查询部门中不同工种的最高工资

select max(sal) max_sal from emp group by job;

（6） 查询部门人数大于5的部门员工信息

select * from emp where deptno in(select deptno from emp group by deptno having count(1)>5);

（7） 查询所有员工工资都在2000—3000之间的部门的信息

select d.* from dept d,(select deptno from emp group by deptno having ((max(sal) between 2000 and 3000) and (min(sal) between 2000 and 3000)) ) e where d.deptno = e.deptno;

（8） 查询人数最多的部门信息

select * from dept
where deptno = (select deptno
from (select deptno,count(10) from emp group by deptno order by count(10) desc) e
where rownum<2);

（9） 查询所有员工中工资排序在5—10之间的员工信息

select s.,rn
from (select e.,rownum rn
from (select * from emp order by sal desc) e) s
where rn between 5 and 10;

（10） 查询每个部门中工资排名第二的员工信息

select * from (select emp.*,rank()over(partition by deptno order by sal) rn from emp ) t where t.rn = 2;

（11） 以树状结构查询所有员工与其领导之间的层次关系

select level,lpad(ename,length(ename)+(level-1)4,’ '),emp. from emp start with empno = 7839 connect by prior empno = mgr ;

（12） 采用case分支语句编写程序，把emp表的员工工资分成多个等级。

select sal,
(case
when sal <2000 then ‘低’
when sal <3000 then ‘中’
else ‘高’
end) grade from emp;