sql练习

练习使用oracle sql语法

• 员工表(emp)

字段	说明
eno	员工编号
ename	员工姓名
job	职位
mgr	上级领导编号
hiredate	入职日期
sal	工资
comm	奖金
deptno	部门编号

---

• 部门表(dept)

字段	说明
deptno	部门编号
dname	部门名称

---

---

#1. 找出25年前雇的员工
select * from emp where add_months(sysdate,-12*25) > hiredate

#2. 截取员工姓名中包含r的后一段字符串(如hrose的rose)
select sub(ename,instr(ename,'r')) from emp where instr(ename,'r')<>0

#3. 算出部门30中得到奖金最多的员工的姓名
法1：select ename from emp where deptno=30 and comm=(select max(comm) from emp where deptno=30)

法2：select ename from (select * from emp where deptno=30 order by comm desc) where rownum=1

#4. 得到工资大于自己部门平均工资的员工信息
select * from emp,(select deptno,avg(sal) as asal from emp group by deptno) m where emp.deptno=m.deptno and emp.sal > m.asal

#5. 得到每个月工资总数最少的那个部门的部门名称
select dname from dept where deptno in (
select deptno from (
select deptno,sum(sal) as ss from emp group by deptno order by ss asc) where rownum=1)

#6. 查找出部门10和部门20中，工资最高第三名到工资第五名的员工的员工姓名，部门名称
select ename,dname from dept d join
 (
select ename,deptno,rownum as no from emp where deptno in (10,20) order by sal desc) m
on d.deptno = m.deptno and m.no>3 and m.no<6

#7. 查找出收入(工资+奖金)，下级比自己上级还高的员工姓名，员工收入
select e1.ename,e1.sal+nvl(e1.comm,0) as sc from emp e1 left join emp e2 on e1.mgr = e2.eno where e1.sal+nvl(e1.comm,0) > nvl(e2.sal,0)+nvl(e1.comm,0)

#8. 查找出工资成本最高的部门的部门号和部门名称
select * from dept join (
    select deptno,sum(sal) as ss from emp group by deptno having ss = (select max(sum(sal)) from emp group by deptno)
) t on dept.deptno = t.deptno

#9. 各部门各领导底下的员工占员工总人数的比例
select deptno,mgr,round(count(eno)/(select count(eno) from emp),2)*100 || '%' as percent from emp group by deptno,mgr

-----------------------重要-----------------------------
#10. 如果员工表有1000W条数据，请用最高效的的sql实现：
统计各部门：
    类1：入职时间>3年，工资大于10000
    类2：入职时间>3年，工资小于10000
    类3：入职时间<3年，工资大于10000
    类4：入职时间<3年，工资小于10000
的人员数量

select 
    sum(case when sal>10000 and to_char(sysdate-3,'yyyy')>to_char(hiredate,'yyyy') then 1 else 0 end) as '类1',
    sum(case when sal<10000 and to_char(sysdate-3,'yyyy')>to_char(hiredate,'yyyy') then 1 else 0 end) as '类2',
    sum(case when sal>10000 and to_char(sysdate-3,'yyyy')<to_char(hiredate,'yyyy') then 1 else 0 end) as '类3',
    sum(case when sal<10000 and to_char(sysdate-3,'yyyy')<to_char(hiredate,'yyyy') then 1 else 0 end) as '类4'
from emp group by deptno

统计各部门中职位为'manager','developer','operator'的人员工资成本(列名:deptno man dev ope)
select 
    deptno,
    sum(case when job='manager' then sal else 0 end) as 'manager',
    sum(case when job='developer' then sal else 0 end) as 'developer',
    sum(case when job='operator' then sal else 0 end) as 'operator'
from emo group by deptno
    

#11. 删除人员表中同名人员且只留薪资最高的那一个
法1：
delete from emp where eno not in (
select e.eno from (
    select ename,max(sal) as ms from emp group by ename
)t left join emp e on t.ename=e.ename and t.ms=e.sal
)

法2：
delete from emp where eno e1 not in (
    select max(rowid) from emp e2 where e1.ename = e2.ename and e1.sal > e2.sal
)