oracle查询习题（一）

1.   查询平均工资最高的部门的部门编号、部门名称和该部门的平均工资

select d.deptno 部门编号,d.dname 部门名称,ROUND(avg(sal),2) 平均工资 from emp e
       left join dept d on e.deptno=d.deptno group by d.deptno,d.dname
       having avg(sal)=(select max(avg(sal)) from emp group by deptno);

2.   查询所有员工的年薪、所在部门的名称，查询结果按年薪从低往高排序

select  e.sal*12+nvl(e.comm,0) 年薪 ,d.dname 部门名称 from emp e
        left join dept d on e.deptno=d.deptno order by 年薪;

3.   查询每种工作的最低工资，以及领取该工资的员工姓名，查询结果显示工作名称、最低工资、领取该工资的员工姓名

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

4.   查询出管理员工人数最多的人的名字和他管理的人的名字

select a.ENAME 员工, b.ENAME 管理员 from  emp a
       join emp b on a.MGR=b.EMPNO
       where  a.MGR=
              (select MGR from emp group by MGR
                      having count(ename)=
                      (select max(count(ename)) from emp group by MGR));

5.   查询所有员工的编号、姓名，及其上级领导的编号、姓名。显示结果按领导的年薪降序排列

select e1.empno 编号,e1.ename 姓名,e2.empno 领导编号,e2.ename 领导姓名 from emp e1
                left join emp e2 on e1.mgr=e2.empno
                order by e2.sal*12+NVL(e2comm,0) desc nulls last;

6.   查询所有领取奖金和不领取奖金的员工人数、平均工资；查询结果的列名分别为：人数、平均工资；第一行为有奖金的员工，第二行为没有奖金的员工

select  count(empno) 人数,avg(sal) 平均工资 from emp
where comm is not null
union
select  count(empno) 人数,avg(sal) 平均工资 from emp
where comm is null ;

7.   查询工资不超过2500的人数最多的部门名称和该部门工资不超过2500的员工的员工人数

select d.dname,count(e1.empno) from dept d , emp e1
       where d.deptno = e1.deptno  and e1.sal<=2500 group by  d.deptno,d.dname having count(e1.empno) =
       (select max(count(e2.empno)) from emp e2 where e2.sal<=2500 group by e2.deptno);

8.   查询受雇日期早于其直接上级的所有员工的编号，姓名，部门名称

select e.empno,e.ename,d.dname from emp e ,emp m ,dept d
       where e.hiredate <m.hiredate and e.mgr=m.empno and e.deptno=d.deptno;

9.   查询至少有4个员工的部门的部门名称

select d.deptno,dname,count(ename)
    from emp e join dept d
            on e.deptno=d.deptno
            group by d.deptno,dname
            having count(ename)>4;

10. 查询工资比“SMITH”高的员工的基本信息

select * from emp where sal>
       (select sal from emp where ename='SMITH');

11. 查询部门名称中带'S'字符的部门的员工的工资总和部门人数，显示结果为部门名称，部门员工的工资总和，部门人数

select dname,sum(sal),count(ename) from emp e,dept d
    where dname like '%S%' and e.deptno(+)=d.deptno
    group by dname;

12. 查询所有从事"CLERK"工作的雇员所在部门的部门名称、部门里的人数

select dname,count(ename) from emp e left join dept d
        on e.deptno=d.deptno where job='CLERK'
        group by dname;

13. 查询雇员领导的基本信息，要求领导的薪水要超过3000

select distinct m.* from emp e,emp m
           where m.sal >3000 and e.mgr=m.empno;

14. 查询在"sales"部门（销售部）工作的员工的姓名

select ename from emp
            where deptno=
            (select deptno from dept where dname='SALES');

15. 查询工资高于30号部门的所有员工的工资的员工姓名、工资及部门名称

方法一：
select ename,sal,dname from emp join dept on emp.deptno=dept.deptno
        where sal > (select max(sal) from emp where deptno=30);
        
方法二：
select ename,sal,dname from emp join dept on emp.deptno=dept.deptno
        where sal > all(select sal from emp where deptno=30);

16. 查询所有部门的详细信息（部门编号、部门名称）和部门人数

select d.deptno,d.dname,count(e.empno) from emp e left join dept d on e.deptno=d.deptno
       group by d.deptno,d.dname

17. 显示与"BLAKE"同部门的所有员工的基本信息，但不显示"BLAKE"的基本信息

select  a.deptno,  a.job,  a.deptno_job_avg,  b.deptno_avg,  c.job_avg
            from (select deptno,job,round(avg(sal),2) as deptno_job_avg from emp group  by deptno,job) a
                    left join (select deptno,round(avg(sal),2) as deptno_avg from emp group by
                          on   a.deptno = b.deptno
                    left join (select job,round(avg(sal),2) as job_avg from emp group by job) c
                          on    a.job = c.job
            order by deptno;

18. 查询出“KING”所在部门的部门编号、部门名称以及该部门里的员工人数

select * from emp
             where emp.deptno=(select emp.deptno from emp where emp.ename='BLAKE' )
                         and emp.ename<>'BLAKE' ;

19. 查询出"WARD"所在部门的工作年限最大的员工的姓名

select d.deptno,d.dname,count(e.empno) from emp e join dept d on e.deptno=d.deptno group by d.deptno,d.dname
       having d.deptno=
           (select deptno from emp where ename='KING');

20. 查询出没有下属的员工的姓名及他的职位

select ename from emp e,
       (select min(e.hiredate) minhiredate,e.deptno deptno from emp e group by e.deptno
       having e.deptno=
              (select deptno from emp where ename='WARD')) temp
        where e.hiredate=minhiredate and e.deptno=temp.deptno;

21. 查询出员工姓名以A开头的人数最多的部门的部门名称

select a.ename as 姓名, a.job as 职位  from emp m
          left join emp e
          on m.empno=e.MGR
          where e.empno is null;

22. 查询出SMITH所在部门的部门名称、部门工资的平均值（注意平均值保留两位小数）

select  d.dname as 部门名称, round(avg(e.SAL),2) as 部门工资平均值 from emp e join dept  d
           on e.deptno=d.deptno
           where  e.deptno=
                  (select deptno from emp where ename='SMITH')
           group by d.dname;