oracle数据库查询练习

 1. 查询每个部门中入职最早的员工的姓名，职位，上司姓名 和 部门名称

     select ename,job,
          (select ename from emp where empno=e.mgr) as 上司,
          (select dname from dept where deptno=e.deptno) as 部门名称 from emp e where
          (deptno,hiredate)in
          (
           select deptno,min(hiredate) as minhiredate from emp group  by deptno
          )
    或
       select t.ename,t.job,e.ename as 上司,dname from emp e
        inner join
        (
        select ename,job,mgr,deptno
          from emp e where
          (deptno,hiredate)in
          (
           select deptno,min(hiredate) as minhiredate from emp group  by deptno
          )  
        )t on e.empno=t.mgr
        inner join dept
        on t.deptno=dept.deptno;
    
    2. 查询员工的姓名，职位，薪水，部门名称，工资等级，按入职日期升序排列
       select ename,job,sal,dname,grade from emp
       inner join dept on emp.deptno=dept.deptno
       inner join salgrade on sal between losal and hisal
       order by hiredate asc
      
    3. 对查询2的结果分页查询，每页5行取第2页的数据
      select * from
      (
       select t.*,rownum as rn from 
       (
          select ename,job,sal,dname,grade from emp
         inner join dept on emp.deptno=dept.deptno
         inner join salgrade on sal between losal and hisal
         order by hiredate asc
       )t
     )tb where rn between 5*(2-1)+1  and 5*2 ;
     
     或
     select * from
     (
       select ename,job,sal,dname,grade,row_number() over(order by hiredate asc) as rn from emp
         inner join dept on emp.deptno=dept.deptno
         inner join salgrade on sal between losal and hisal
     )t where t.rn between 6 and 10; 
     
    
      
    4. 管理人数最多的上司所在的部门名称
    select dname from dept where deptno in
    (
    select deptno from emp where empno in
    (
     select t1.mgr from
     (
      select   mgr,count(*) as cnt  from emp group by mgr
     )t1 where cnt=
     (
       select max(cnt) from
       (
        select   mgr,count(*) as cnt  from emp group by mgr
       )t
     )
     )
     )
    5  最高等级工资人数最多的部门名称和部门经理的姓名
    select dname,(select ename from emp where deptno=dept.deptno and job='MANAGER') AS 部门经理 from dept where deptno in
    (
     select deptno as cnt from
     (
       select * from emp
       inner join salgrade on emp.sal between losal and hisal
       where grade=
       (
       select max(grade) from salgrade
       )
      )t group by deptno
     having count(*) in
     (
      select max(cnt) from
      (
       select deptno,count(*) as cnt from
       (
         select * from emp
         inner join salgrade on emp.sal between losal and hisal
         where grade=
         (
         select max(grade) from salgrade
         )
        )t group by deptno
        )t1
      )
      )
    6. 1个月之内即将合同到期的员工姓名和部门名称。(入职后每满2年签订一次合同，合同期限2年)
       select ename,(select dname from dept where deptno=emp.deptno)as 部门名称 from emp
         where mod(to_number(to_char(sysdate,'yyyy'))-to_number(to_char(hiredate,'yyyy')),2)=0
           and to_date(to_char(hiredate,'mmdd'),'mmdd') between sysdate and sysdate+30;
    7. 统计每年入职的新员工数量，按年份升序
    8. 查询和smith同职同薪的员工姓名和部门名称（同一职位，同一薪水级别）
    9. 按员工工作年限对员工分类统计，
       1年以内的   
       2-3年        
       3-5年       
       5-8年        
       8年以上
       统计每种分类的人数
       select case,count(*) as 人数 from
       (
       select ename,case 
        when to_number(to_char(sysdate,'yyyy'))-to_number(to_char(hiredate,'yyyy'))<1 then '1年以内'
        when to_number(to_char(sysdate,'yyyy'))-to_number(to_char(hiredate,'yyyy'))<3 then '2-3年'
        when to_number(to_char(sysdate,'yyyy'))-to_number(to_char(hiredate,'yyyy'))<5 then '3-5年'
        when to_number(to_char(sysdate,'yyyy'))-to_number(to_char(hiredate,'yyyy'))<8 then '5-8年'
        else  '8年以上'
         end case from emp
       )t group by case;
    10.公司员工需缴纳个人所得税（工资+补贴 超出3000部分的5%，工资+补贴 低于3000不用缴纳） 查询公司的缴税员工，按缴税费用降序排列       
        select ename,(sal+nvl(comm,0)-3000)*0.05 as 缴税 from emp
          where sal+nvl(comm,0)>3000
           order by 缴税 desc
    11.查询每个部门工资最高的前三名员工的姓名 职位 工资
    select * from
    (
       select ename,job,sal,deptno,row_number() over(partition by deptno order by sal desc) as rn from emp
    ) where rn <=3;   
    12.查询比自己的上司工资高的员工的姓名和工资。 
      select * from emp e
       where sal>=
       (
          select sal from emp m where m.empno=e.mgr
       )