Oracle高级查询练习题

--1. 列出至少有一个员工的部门名称select d.dname from dept d inner join (
       select count(ename),deptno from emp group by deptno having count(ename)>0
)t on d.deptno=t.deptno;
--2. 列出月薪比“SMITH”多的所有员工。
select * from emp where sal>(
       select sal from emp where ename='SMITH'
);
--3. 列出所有员工的姓名及其直接上级的姓名。
select e1.ename,e2.ename from emp e1 inner join emp e2 on e1.mgr=e2.empno;
--4. 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称。
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;

--5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select d.dname,e.* from dept d left outer join emp e on e.deptno=d.deptno;

--6. 列出所有“CLERK”（办事员）的姓名及其部门名称、部门的人数。
select m.ename,m.job, p.dname, p.renshu from emp m
inner join (
      select d.deptno, d.dname, count(e.empno) renshu from emp e inner join dept d on e.deptno = d.deptno group by d.dname, d.deptno
) p on m.deptno = p.deptno where m.job = 'CLERK';
--7. 列出最低月薪大于1500的各种工作及从事此工作的全部雇员人数。
select job,sal from emp;
select t1.job,t2.renshu from (
       select job from emp group by job having min(sal)>1500
)t1 inner join (
    select job,count(ename) from emp group by job
)t2 on t1.job=t2.job;

--8. 列出在部门“SALES”（销售部）工作的员工的姓名。
select e.ename from emp e inner join dept d on e.deptno=d.deptno where d.dname='SALES';

--9. 列出月薪高于公司平均月薪的所有员工，所在部门，上级领导，工资等级。
select e1.ename,d.dname,e2.ename,s.grade from emp e1 left outer join dept d on e1.deptno=d.deptno
left outer join emp e2 on e1.mgr=e2.empno 
left outer join salgrade s on (e1.sal+nvl(e1.comm,0)) between s.losal and s.hisal
where (e1.sal+nvl(e1.comm,0))>(
     select round(avg(e.sal + nvl(e.comm, 0))) from emp e 
);

--10. 列出与“SCOTT”从事相同工作的所有员工及部门名称。
select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno where e.job=(
       select job from emp where ename='SCOTT'
) and e.ename <> 'SCOTT';

--11. 列出月薪等于部门30中员工的月薪的所有员工的姓名和月薪。
select ename,sal from emp e where sal in (
       select distinct(sal) from emp where deptno=30
);
--12. 列出月薪高于在部门30工作的所有员工的月薪的员工的姓名、月薪、部门名称。
select e.ename,e.sal,d.dname from emp e  inner join dept d on e.deptno=d.deptno where sal >all (
       select distinct(sal) from emp where deptno=30
);
--13. 列出每个部门工作的员工的数量、平均工资和平均服务期限。
select count(ename) 员工数量，floor(avg(sal+ nvl(comm, 0))) as 平均工资,floor(avg(sysdate-hiredate)) as 平均服务期限 from emp group by deptno;
--14. 列出所有员工的姓名、部门名称和部门平均工资。
select m.ename, t.dname, t.b_avg
  from emp m
  left outer join (select d.deptno,
                          d.dname,
                          round(avg(e.sal + nvl(e.comm, 0))) b_avg
                     from emp e
                    inner join dept d
                       on e.deptno = d.deptno
                    group by d.deptno, d.dname) t
    on m.deptno = t.deptno;
--15. 列出所有部门的详细信息和部门人数。
select d.*,t.renshu from dept d inner join (
       select deptno,count(ename) as renshu from emp group by deptno
)t on d.deptno=t.deptno ;
--16. 列出各种工作的最低工资及从事此工作的员工姓名。
select e.ename,t.minsal from emp e inner join (
       select job,min(sal+ nvl(comm, 0)) minsal from emp group by job
)t on e.job=t.job;

--17. 列出各个部门的MANAGER(经理)的最低月薪。
select deptno,min(sal) from emp where job='MANAGER' group by deptno;
--18. 列出所有员工的年总收入，按年总收入从低到高排序。
select ename,(sal+nvl(comm,0))*12 from emp order by (sal+nvl(comm,0))*12;
--19. 列出员工的上级主管信息，并要求这些主管的月薪超过3000。
select * from emp where empno in(
       select e1.mgr from emp e1 inner join emp e2 on e1.mgr=e2.empno group by e1.mgr
) and sal>3000;
--20. 列出部门名称中带“S”字符的部门员工的月薪合计、部门人数。
select d.dname,t.heji,t.renshu from dept d inner join (
       select deptno,count(ename) renshu,sum(sal) heji from emp group by deptno
)t on d.deptno=t.deptno where instr(d.dname,'S',1)>0;

--21. 求出部门平均月薪最高的部门名和平均月薪。
select d.dname,t.pingjun from dept d inner join (
       select deptno,floor(avg(sal+ nvl(comm, 0))) pingjun from emp group by deptno 
)t on d.deptno=t.deptno where t.pingjun=(
   select max(floor(avg(sal))) pingjun from emp group by deptno 
);