Oracle高级查询练习题2

--1.查询平均工资最高的部门的部门编号，部门名称，和该部门的平均工资
select d.deptno,d.dname,t.pingjun from dept d inner join(
       select deptno,round(avg(sal+nvl(comm,0))) pingjun from emp group by deptno
)t on d.deptno=t.deptno where t.pingjun=(
   select max(round(avg(sal+nvl(comm,0))))from emp group by deptno
);
--2 查询所有员工的年薪，所在部门的名称，结果按年薪低到高排列
select e.ename,d.dname,round((sal+nvl(comm,0))*12) as nianxin from emp e inner join dept d on e.deptno=d.deptno order by nianxin desc;
--3查询每种工作的工作名称，最低工资，领取该最低工资员工的姓名
select t.job,t.minsal,e.ename,e.sal from (
      select job,min(sal) as minsal from emp group by job 
)t inner join emp e on t.job=e.job where e.sal=t.minsal;
--4查询出管理员工人数最多的人和他管理的人的名字
select e2.ename, e1.ename, t.人数
  from (select count(e.ename) as 人数, e.mgr from emp e group by e.mgr) t
 inner join emp e1
    on t.mgr = e1.empno
 inner join emp e2
    on e2.mgr = t.mgr
 where t.人数 =
       (select max(count(e1.ename)) as max_num from emp e1 group by e1.mgr)

--5查询所有员工的编号、姓名，及其上级领导的编号、姓名。显示结果按领导的年薪降序排列
select e1.empno,e1.ename,e2.empno,e2.ename,(e2.sal+nvl(e2.comm,0))*12 nianxin from emp e1 inner join emp e2 on e1.mgr=e2.empno order by nianxin desc;
--6查询所有领取奖金和不领取奖金的员工人数、平均工资；查询结果的列名分别为：人数、平均工资；第一行为有奖金的员工，第二行为没有奖金的员工
select count(ename) 人数,avg(sal+nvl(comm,0)) from emp where nvl(comm,0)!=0 
union
select count(ename) 人数,avg(sal+nvl(comm,0)) from emp where nvl(comm,0)=0 
--7查询工资不超过2500的人数最多的部门名称和该部门工资不超过2500的员工的员工人数
select d1.dname,t.renshu from dept d1 inner join (
       select deptno,count(ename) renshu from emp where (sal+nvl(comm,0))<2500 group by deptno
)t on d1.deptno=t.deptno where t.renshu=(
   select max(count(ename)) renshu from emp where (sal+nvl(comm,0))<2500 group by deptno
);
--8查询受雇日期早于其直接上级的所有员工的编号，姓名，部门名称
select e1.empno,e1.ename,d.dname from emp e1 
inner join emp e2 on e1.mgr=e2.empno
inner join dept d on e1.deptno=d.deptno where e1.hiredate<e2.hiredate;
--10查询工资比“SMITH”高的员工的基本信息
select * from emp where sal>(
       select sal from emp where ename='SMITH'
);
--9查询至少有4个员工的部门的部门名称
select d.dname from emp e inner join dept d on e.deptno=d.deptno group by e.deptno,d.dname having count(ename)>3; 
--11查询部门名称中带'S'字符的部门的员工的工资总和部门人数，显示结果为部门名称，部门员工的工资总和，部门人数
--不推荐使用d.dname分组，因为可能dname不唯一
select d.dname 部门名称,sum(e.sal+nvl(e.comm,0)) 工资总和，count(e.ename) 部门人数 from dept d left outer join emp e on d.deptno=e.deptno where d.dname like '%S%' group by d.dname;

--更优化
select d.dname, b.r, b.s
  from dept d
  left join (select count(rowid) r, sum(sal+nvl(comm,0)) s, deptno
               from emp
              group by deptno) b
    on d.deptno = b.deptno
 where d.dname like '%S%';
--12查询所有从事"CLERK"工作的雇员所在部门的部门名称、部门里的人数
select d.dname,count(ename) from dept d inner join emp e on d.deptno=e.deptno where d.deptno in(
    select distinct(deptno) from emp where job='CLERK'   
) group by d.dname; 
--13查询雇员领导的基本信息，要求领导的薪水要超过3000
select * from emp where empno=(
       select e1.mgr from emp e1 inner join emp e2 on e1.mgr=e2.empno where e2.sal>3000 group by e1.mgr
);
--14查询在"sales"部门（销售部）工作的员工的姓名
select e.* from emp e inner join dept d on e.deptno=d.deptno where d.dname='SALES';
--15查询工资高于30号部门的所有员工的工资的员工姓名、工资及部门名称
select e.ename,(e.sal+nvl(comm,0)),d.dname from emp e inner join dept d on e.deptno=d.deptno where (e.sal+nvl(comm,0))>all(
       select sal from emp where deptno=30
); 
--16查询所有部门的详细信息（部门编号、部门名称）和部门人数
select d.deptno,d.dname,count(e.ename) from dept d inner join emp e on d.deptno=e.deptno group by d.deptno,d.dname;
--17显示每个部门中每个岗位的平均工资、每个部门的平均工资、每个岗位的平均工资（没看懂题目要求，好像有点问题）

--18显示与"BLAKE"同部门的所有员工的基本信息，但不显示"BLAKE"的基本信息
select * from emp where deptno =(
       select deptno from emp where ename='BLAKE'
) and ename<>'BLAKE';
--19查询出“KING”所在部门的部门编号、部门名称以及该部门里的员工人数
select t.deptno,d.dname,t.renshu from(
       select e.deptno,count(e.ename) renshu from emp e group by e.deptno
)t left join dept d on t.deptno=d.deptno where t.deptno=(
       select deptno from emp where ename='KING'
)
--20查询出"WARD"所在部门的工作年限最大的员工的姓名
select t.ename from( 
  select ename,round(sysdate-hiredate) worktime from emp where deptno=(
         select deptno from emp where ename='WARD'
  )
)t where t.worktime=(
   select max(round(sysdate-hiredate)) worktime from emp where deptno=(
         select deptno from emp where ename='WARD'
  )
)
--21查询出没有下属的员工的姓名及他的职位
select e1.ename,e1.job from emp e1 left outer join emp e2 on e1.empno=e2.mgr where e2.empno is null;
--22查询出员工姓名以A开头的人数最多的部门的部门名称
select * from dept d inner join (
       select deptno,count(ename) renshu from emp where ename like 'A%' group by deptno
)t on d.deptno=t.deptno where t.renshu=(
   select max(count(ename)) from emp where ename like 'A%' group by deptno
)
--23查询出SMITH所在部门的部门名称、部门工资的平均值
select e.deptno,round(avg(e.sal+nvl(e.comm,0))) pingjun from emp e where d.deptno=(
      select deptno from emp where ename='SMITH'
) group by d.deptno;