子查询练习

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

方法1：
select e.ename,e.job,m.ename as 上司姓名,m.job as 上司职位,dname from emp e
inner join dept on e.deptno=dept.deptno
inner join emp m on e.mgr=m.empno where e.hiredate=(select min(hiredate) from emp);

方法2：
select ename,job,(select ename from emp where empno=e.mgr) as 上司,
(select dname from dept where deptno=e.deptno)as 部门名称 from emp where
(deptno,hiredate)in
(select deptno,min(hiredate) as minh from emp group by deptno)

方法3：
select t.ename,t.job

2、查询员工姓名、职位、薪水、部门名称、工资等级，按入职日期升序排列

select ename,job,sal,grade,dname,hiredate 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行取第二页的数据

方法1：
select* from(
select t.* ,rownum as rn from(
select ename,job,sal,grade,dname,hiredate from emp
inner join dept on emp.deptno=dept.deptno
inner join salgrade on sal between losal and hisal
order by hiredate asc
)t
)t2 where t2.rn between 5*(2-1)+1 and 5*2;

方法2：
select* from(
select ename,job,sal,grade,row_number() over(order by hiredate asc) as rn ,dname 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、管理人数最多的上司所在的部门名称

方法1：
select dname from dept where deptno=(
select deptno from emp where mgr=
(select mgr from(select count(*) as maxx,mgr from emp group by mgr)t,
(select max(t.maxx) as maxxx from(select count(*) as maxx ,mgr from emp group by mgr)t)t2
where t2.maxxx=t.maxx
)group by deptno);

方法2：
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年）

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、统计每年入职的新员工数量，按年份升序

select count(*),b.a from(select (to_char(hiredate,'yyyy')) as a from emp)b group by b.a order by b.a asc;

8、查询和smith同职同薪的员工姓名和部门名称（同一职位，同一薪水级别）

select ename,dname,grade from emp
inner join dept on emp.deptno=dept.deptno
inner join salgrade on sal between losal and hisal
where (grade,job)=(select grade,job from emp 
inner join salgrade on sal between losal and hisal where ename='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,row_number() over(partition by deptno order by sal desc) as rn from emp
)where rn<=3;

12、查询比自己的上司工资高的员工的姓名和工资

select * from emp
where sal>=
(
select sal from emp m where m.empno=e.mgr
)

decode(表达式)

select ename,decode(job,‘CLERK’,‘科员’,‘MANAGER’,‘经理’,‘XXXX’) from emp;如果job匹配的是’'显示科员，如果job匹配的是MANAGER显示经理，都不匹配，显示XXXX

case

select ename,case

job

when ‘CLERK’ then ‘科员’

when ‘CLERK’ then ‘经理’

else ‘XXXX’

end case from emp;

select ename,case

when sal<=1000 then ‘蓝领’

when sal<=2000 then ‘灰领’

else ‘XXXX’

end case from emp;

select case count(*) from

(

select ename case

when sal<=1000 then ‘蓝领’

when sal<=2000 then ‘灰领’

else ‘XXXX’

end case from emp;

)t group by case;

row_number()

select emp.*,row_number() over(partition by deptno order by sal asc) from emp;