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
)