数据库高级查询

 1、列出员工表中每个部门的员工数和部门编号

     select deptno,count(*)

     from emp

     group by deptno

   

2、列出员工表中每个部门的员工数（员工数必须大于3），和部门名称

     select emp.deptno,dept.dname,count(*)

     from emp,dept

     where emp.deptno=dept.deptno

     group by emp.deptno,dept.dname

     having count(*)>3

      

3、找出工资比JONES多的员工

     select ename,sal

     from emp

     where sal>(select sal from emp where ename='JONES')

       

4、列出所有员工的姓名和其上级的姓名

     select e.ename,b.ename

     from emp e,emp b

     where e.mgr=b.empno

         

5、以职位分组，找出平均工资最高的两种职位

     select job

     from (select job,avg(sal) from emp group by job order by avg(sal) desc)where rownum<=2

         

6、查找出不在部门20，且比部门20中任何一个人工资都高的员工的姓名、部门名称

     select emp.ename,dept.dname

     from emp,dept

     where emp.deptno=dept.deptno and sal>(select max(sal) from emp where deptno=20) and  emp.deptno!=20

        

7、得到平均工资大于2000的工作职种

     select job,avg(sal)

     from emp

     group by job

     having  avg(sal)>2000

     

8、分部门得到工资大于2000的所有员工的平均工资，并且平均工资还要大于2500

     select deptno,avg(sal)

     from emp

     where sal>2000

     group by deptno

     having avg(sal)>2500

         

9、得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置

     方法1

     select dept.deptno,dept.dname,dept.loc

     from (select deptno,sum(sal) from emp group by deptno order by sum(sal) asc) e

     left join dept on  e.deptno=dept.deptno where rownum=1

     

     方法2

     select p.deptno,dept.dname,dept.loc

     from(select e.*,rownum rn from(select sum(sal),deptno from emp group by deptno order by sum(sal) asc) e)p,dept

     where p.deptno=dept.deptno and rn=1

     

10、分部门得到平均工资等级为4级（等级表）的部门编号

     select deptno,avg(sal)

     from emp,salgrade

     group by deptno

     having avg(sal)between (select losal from salgrade where grade=4) and (select hisal from salgrade where grade=4)

        

11、查找出部门10和部门20中，工资最高第3名到第5名的员工的员工名字，部门名字，部门位置    

     select p1.ename,dept.deptno,loc

     from (select p.* ,rownum rn from (select deptno,sal from emp where deptno in(10,20)order by sal desc) p) p1,dept

     where p1.deptno=dept.deptno and rn between 3 and 5

            

12、查找出收入（工资加上奖金），下级比自己上机还高的员工编号，员工名字，员工收入

     select e.empno,e.ename,(e.sal+nvl(e.comm,0))

     from emp e,emp bwhere e.mgr=b.empno and

    (e.sal+nvl(e.comm,0))>(b.sal+nvl(b.comm,0))

    

13、查找出工资等级不为4级的员工的员工名字，部门名字，部门位置

     select emp.ename,dept.dname,dept.loc

     from emp left join dept on dept.deptno=emp.deptno

     where  sal<(select losal from salgrade where grade=4)

     or  sal>(select hisal from salgrade where grade=4)

   

14、查找出职位和‘MARTIN’或者‘SMITH’一样的员工的平均工资

     select avg(sal)

     from emp

     where job=(select job from emp where ename='MARTIN')

     or job=(select job from emp where ename='SMITH')

          

15、查找出不属于任何部门的员工

     select * from emp where deptno is null

     

     方法2

     select *

     from emp

     where deptno not in( select deptno from emp)

      

16、按照部门统计员工数，查出员工数最多的部门的第二名到第五名（列出部门名字，部门位置）

     select dept.dname,dept.loc

     from (select a.*, rownum rn from (select deptno,count(*) from emp group by deptno order by count(*) desc) a  ) b ,dept

     where b.deptno=dept.deptno and rn between 2 and 5

    

17、查出KING所在部门的部门号、部门名称、部门人数

     方法1

     select dept.deptno,dept.dname,count(*)

     from dept

     where deptno=(select deptno from emp where ename='KING')

     group by dept.deptno,dept.dname

      

     方法2

     select a.deptno,dept.dname,count(*)

     from (select deptno from emp where ename='KING') a,dept

     where a.deptno=dept.deptno

     group by a.deptno,dept.dname

     

18、查出KING所在部门的工作年限最大的员工名字  

     select ename from emp

     where hiredate=(select min(hiredate) from emp where deptno in( select deptno from emp where ename='KING' ))

         

19、查出工资成本最高的部门的部门号和部门名称

     select dept.deptno,dname

     from (select deptno,max(sal) from emp group by deptno order by max(sal) asc) p, dept

     where p.deptno=dept.deptno and rownum=1

 

20、创建一查询，显示与Blake在同一部门工作的雇员的姓名和受雇日期，Blake不包含在内

     select ename,hiredate

     from emp

     where deptno=(select deptno from emp where ename='BLAKE') and ename!='BLAKE'

       

21、显示位置在Dallas的部门内的雇员姓名，受雇日期以及工作

     select deptno,ename,hiredate,job

     from emp

     where deptno=(select deptno from dept where loc='DALLAS')

            

22、显示被King直接管理的雇员的姓名以及工资

     select e.ename,e.sal,b.ename

     from emp e,emp b

     where e.mgr=b.empno and b.ename='KING'    

      

23、创建一查询，显示能获得与Scott一样工资和奖金的其他雇员的姓名、受雇日期以及工资。

     select ename,hiredate,(sal+nvl(comm,0))

     from emp

     where (sal+nvl(comm,0))=(select sal+nvl(comm,0) from emp where ename='SCOTT')