查询语句求解答

Question

一、数据结构 
(1)、员工信息表 EMP 
字段名称  注释   数据类型 是否主键 是否唯一索引 字典项 
EMPNO  员工编号  varchar2(10)  是 是 
ENAME    员工姓名  VARHCHAR2(60)  
JOB  岗位  VARHCHAR2(9)  
MGR  主管编号  varchar2(10)  
HIREDATE  入职时间  DATE  
SAL  工资  NUMBER(7,2)  
COMM  通讯费  NUMBER(7,2)  
DEPTNO  部门编号  varchar2(60)  
(2)、部门信息表 DEPT 
字段名称 注释 数据类型 是否主键 是否唯一索引 字典项 
DEPTNO  部门编号  varchar2(60)   是 是
DNAME  部门名称  VARHCHAR2(128)  
LOC  位置  VARHCHAR2(13)  
二、题目
(1)、统计出平均工资高于3000的部门有哪些； (2)、统计在2000年4月至9月间入职了多少员工； (3)、共有多少姓“李”的员工；      
(4)、按部门统计各部门的下的员工数、通讯费总和、工资总和、通讯费最高的员工、工资贵高的员工； (5)、展示每个部门收入排名前三位的员工信息（员工编号、员工姓名、部门名称、收入总和），收入=工资+通讯费； (6)、展示所有没有通讯费的员工信息（显示员工编号、员工姓名）；  (7)、统计员工人数高于各部门平均人数的部门； 
(8)、实现依据员工编号查询出所有上级信息以及所有下级信息；

Answer 1

1. select d.DEPTNO,max(d.DNAME),round(avg(sal),2) from emp e,dept d where e.DEPTNO=d.DEPTNO group by d.deptno order by deptno;
2. select e.* from emp e where to_char(e.HIREDATE,'yyyy-mm-dd')>='2000-04-01' and to_char(e.HIREDATE,'yyyy-mm-dd')<='2000-09-31' order by deptno,ename;
3. select count(*) from emp e where e.ENAME like '%李%';
(4)、按部门统计各部门的下的员工数、通讯费总和、工资总和、通讯费最高的员工、工资贵高的员工； 
select e.deptno,max(d.DNAME) dname,count(*) 员工数,sum(e.comm) 通讯费总和,sum(e.sal) 工资总和
from emp e,dept d where e.DEPTNO=d.DEPTNO group by e.DEPTNO(5)、展示每个部门收入排名前三位的员工信息（员工编号、员工姓名、部门名称、收入总和），收入=工资+通讯费； 
select EMPNO 员工编号,ENAME 员工姓名,DNAME 部门名称,nvl(SAL,0)+nvl(comm,0) 收入总和 from
(select e.*,d.DNAME,row_number() over(partition by e.DEPTNO order by (nvl(e.SAL,0)+nvl(e.comm,0))) rm
from emp e,dept d 
where e.DEPTNO=d.DEPTNO)
where rm<=3(6)、展示所有没有通讯费的员工信息（显示员工编号、员工姓名）； 
select e.EMPNO 员工编号,e.ENAME 员工姓名 from emp e where nvl(e.comm,0)=0
 
(7)、统计员工人数高于各部门平均人数的部门；
select d.DEPTNO,max(d.DNAME) dname,count(e.ename) 
from emp e, dept d 
where e.DEPTNO=d.DEPTNO
group by d.DEPTNO
having count(e.ename)>(select (count(distinct e1.ename)/count(distinct d1.deptno)) d_avg from emp e1,dept d1)
order by d.deptno (8)、实现依据员工编号查询出所有上级信息以及所有下级信息；
--所有员工的下级信息
select sys_connect_by_path(ename,'/') tree,level from emp  connect by  prior empno=  mgr;  
--所有员工的上级信息
select  sys_connect_by_path(ename,'/') tree,level from emp start with ename='CLARK' connect by prior mgr=  empno ;