【分享Oracle练习二】

-- 【1、列出至少有三个员工的所有部门和部门信息。】

第一种方法:

select deptno from emp group by deptno having count(*)>=3
select deptno from (select deptno,count(1) n from emp group by deptno) t where t.n>=3;

第二种方法:

select * from dept where deptno in (select deptno from emp group by deptno having count(*)>=3);

-- 【2、列出受雇日期早于直接上级的所有员工的编号,姓名,部门名称】

select emp.empno,emp.ename,d.dname from emp emp,emp mgr,dept d
where emp.mgr=mgr.empno and emp.deptno=d.deptno and emp.hiredate<mgr.hiredate;

-- 【3、列出职位为“CLERK”的姓名和部门名称，部门人数：】

select e.ename, d.dname, t.n  from emp e,  dept d, 
(select deptno, count(1) n from emp group by deptno) t
where e.job='CLERK' and e.deptno=d.deptno and e.deptno=t.deptno;

-- 【4、列出和“SCOTT”从事相同工作的所有员工及部门名称：】

第一种方法:

select job from emp where emp.ename='SCOTT';
select e.*,d.dname from emp e ,dept d where e.deptno=d.deptno and e.job='ANALYST';

第二种方法: 

SELECT e.ename , d.dname FROM emp e ,dept d WHERE  e. job = 
(SELECT job FROM emp WHERE ename = 'SCOTT') AND e. deptno =d. deptno ;

-- 【5、列出每个部门工作的员工数量、平均工资和平均服务期限（单位为年）】

select e.deptno, count(1),avg(e.sal), 
       to_char(sysdate, 'yyyy')-round(avg(to_char(e.hiredate, 'yyyy')))
from emp e group by e.deptno;

-- 【6、列出各个部门的MANAGER 的最低薪金：】

select e.deptno, min(e.sal)from emp e where e.job='MANAGER' group by e.deptno;

-- 【7、给任职日期超过10年的人加薪10%；】

update emp t set t.sal=t.sal*1.1
where trunc(months_between(sysdate, t.hiredate)/12) > 10;

-- 【8、查询出和SCOTT工资一样的员工信息】

select * from emp where sal = (select sal from emp where ename = 'SCOTT');

-- 【9、查询出比SCOTT工资高的员工信息】

select * from emp where sal > (select sal from emp where ename = 'SCOTT');

-- 【10、查询出不是领导的员工】

第一种方法：

SELECT e. ename   员工 ,e. deptno   codes ,e. empno  编号,e. mgr ,e. hiredate    日期,e2. ename  领导 ,e2. hiredate  领导日期  
FROM emp e ,emp e2 WHERE e2. empno  =e. mgr  AND e. ename  NOT IN 
(SELECT e.ename FROM emp e ,emp e2 WHERE e. empno  = e2. mgr );

第二种方法：

select * from emp e
where not Exists (select * from emp a where e.empno = a.mgr);

-- 【11、查询出平均工资高于2000的部门编号和该部门平均工资】

select avg(sal),deptno from emp group by deptno having avg(sal)>2000;

select avg(sal) avgSal,deptno from emp  group by deptno; 
select * from (select avg(sal) avgSal,deptno from emp  group by deptno) t where t.avgSal>2000;

-- 【12、查询出平均工资高于2000的部门名称和该部门平均工资】

select d.dname,t.avgSal from dept d join (select avg(sal) avgSal,deptno 
from emp  group by deptno)t  on t.avgSal>2000 and d.deptno=t.deptno;

 

select distinct d.dname , t.salavg  from dept d ,(select avg(sal) salavg,deptno
from emp group by deptno having avg(sal)>2000) t where t.deptno = d.deptno;

-- 【13、查询出有员工的部门【数据量大的时候用exists效率非常高】

select * from dept d1 where exists(select * from emp e1 where e1.deptno = d1.deptno );

-- 【14、找到员工表中薪水大于本部门平均工资的员工。】

select e.ename , e.sal ,e.deptno,t.avgsal from emp e,(select avg(sal) avgsal,deptno 
from emp group by deptno)t where e.sal > t.avgsal and e.deptno =t.deptno;

-- 【15、找到员工表中薪水大于本部门平均薪水的员工。】

select avg(sal),deptno from emp group by deptno;
select e.ename,e.sal,t.avgSal from emp e join (select avg(sal) avgSal,deptno
from emp group by deptno)t on e.deptno=t.deptno and e.sal>t.avgSal;

--【16、 统计每年入职的员工个数】

第一种方法:

select count(*) Total, 
sum(decode(to_char(hiredate,'YYYY'),'1980','1','0')) "1980",
sum(decode(to_char(hiredate,'YYYY'),'1981','1','0')) "1981", 
sum(decode(to_char(hiredate,'YYYY'),'1982','1','0')) "1982", 
sum(decode(to_char(hiredate,'YYYY'),'1987','1','0')) "1987" 
from emp;

第二种方法：

-- 只显示年
select to_char(hiredate,'yyyy') from emp;
-- 分组统计
select  to_char(hiredate,'yyyy') yy,count(1) cc from emp group by  to_char(hiredate,'yyyy');

select yy
from 
(select  to_char(hiredate,'yyyy') yy,count(1) cc from emp group by  to_char(hiredate,'yyyy')) tt;

select case yy when '1987' then cc end
from 
(select  to_char(hiredate,'yyyy') yy,count(1) cc from emp group by  to_char(hiredate,'yyyy')) tt;

select case yy when '1987' then cc end "1987"
from 
(select  to_char(hiredate,'yyyy') yy,count(1) cc from emp group by  to_char(hiredate,'yyyy')) tt;

-- 去除行记录中的空值
select sum(case yy when '1987' then cc end) "1987"
from 
(select  to_char(hiredate,'yyyy') yy,count(1) cc from emp group by  to_char(hiredate,'yyyy')) tt;

-- 统计员工的总数
select sum(cc) "TOTAL"
from 
(select  to_char(hiredate,'yyyy') yy,count(1) cc from emp group by  to_char(hiredate,'yyyy')) tt;

-- 将1987 和TOTAL 合并在一起
select
      sum(cc) "TOTAL",
      sum(case yy when '1987' then cc end) "1987"
from
      (select  to_char(hiredate,'yyyy') yy,count(1) cc from emp group by  to_char(hiredate,'yyyy')) tt;

-- 显示所有年份的结果
select
      sum(cc) "TOTAL",
      sum(case yy when '1980' then cc end) "1980",
      sum(case yy when '1981' then cc end) "1981",
      sum(case yy when '1982' then cc end) "1982",
      sum(case yy when '1987' then cc end) "1987"
from
      (select  to_char(hiredate,'yyyy') yy,count(1) cc from emp group by  to_char(hiredate,'yyyy')) tt;

--【17、查询员工编号,员工姓名,员工的部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称,经理的工资等级】将工资等级 1,2,3,4 显示成 中文的 一级 二级 三级...

select e1.empno,e1.ename,d1.dname,
       case s1.grade
           when 1 then '一级'
           when 2 then '二级'
           when 3 then '三级'
           when 4 then '四级'
             else '五级'
       end "等级",
       e1.mgr,m1.ename,d2.dname,
       decode(s2.grade,1,'一级',2,'二级',3,'三级',4,'四级','五级') "等级"
from emp e1, emp m1,dept d1,dept d2,salgrade s1,salgrade s2 
where e1.mgr= m1.empno 
    and e1.deptno = d1.deptno
    and m1.deptno = d2.deptno
    and e1.sal between s1.losal and s1.hisal 
    and m1.sal between s2.losal and s2.hisal;

--【18、 查询员工编号,员工姓名,员工的部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称,经理的工资等级】

select e1.empno,e1.ename,d1.dname,s1.grade,e1.mgr,m1.ename,d2.dname,s2.grade
from emp e1, emp m1,dept d1,dept d2,salgrade s1,salgrade s2 
where 
    e1.mgr= m1.empno 
    and e1.deptno = d1.deptno
    and m1.deptno = d2.deptno
    and e1.sal between s1.losal and s1.hisal 
    and m1.sal between s2.losal and s2.hisal; 

--【19、 查询出emp表中工资在第六和第十之间的数据oracle中的分页查询】

第一种方法：

select * from (select e.*,rownum as rowno from (
select * from emp order by nvl(sal,0) desc) e) where rowno between 6 and 10; 

第二种方法：

  

--【20、 统计薪资大于薪资最高的员工所在部门的平均工资和薪资最低的员工所在部门的平均工资的平均工资的员工信息】

--【21、 薪资最高的员工所在部门的平均工资和薪资最低的员工所在部门的平均工资的平均工资】

--【22、 查询部门名称不是research,职位是manager,且薪资大于平均薪资的员工(包含ename hiredate loc三个字段)】

select * from (select d.deptno,e.sal from emp e join dept d on e.deptno=d.deptno
and d.dname<> 'research' and job='manager')t1,
(select deptno ,avg(sal) avgSal from emp group by emp.deptno) t2 
where t1.deptno=t2.deptno and t1.sal>t2.avgSal;