Oracle经典练习题--进阶篇

-- 列出至少有三个员工的所有部门和部门信息。
 select count(empno) cotemp,deptno from emp group by deptno having count(empno)>2;
 select d.deptno,d.dname,d.loc from dept d join ( select count(empno) cotemp,deptno from emp group by deptno having count(empno)>2) t on d.deptno=t.deptno;
-- 2.列出受雇日期早于直接上级的所有员工的编号，姓名，部门名称
select e1.empno,e1.ename,d.dname 
from emp e1 ,emp e2 ,dept d
where e1.mgr=e2.empno and e1.deptno=d.deptno and e1.hiredate<e2.hiredate;
select * from emp;
   
-- 3.列出职位为“CLERK”的姓名和部门名称，部门人数：
/*
select e.ename,e.deptno,count(e.empno),d.dname from emp e join dept d on e.deptno=d.deptno 
where e.job='CLERK' group by e.deptno,d.dname,e.ename;
*/
select deptno,count(1) n from emp group by deptno;
select e.ename,d.dname,t.n from emp e join dept d on e.deptno=d.deptno join 
(select deptno,count(1) n from emp group by deptno) t on
d.deptno=t.deptno where e.job='CLERK';
      

-- 4.列出和“SCOTT”从事相同工作的所有员工及部门名称：
select e.job from emp e where e.ename='SCOTT';
select e1.empno,e1.ename,d.dname from emp e1 join dept d on e1.deptno=d.deptno where e1.job=(select e.job from emp e where e.ename='SCOTT');

-- 5.列出每个部门工作的员工数量、平均工资和平均服务期限（单位为年）
select count(e.empno),avg(sal),avg((sysdate-hiredate)/360),deptno from emp e group by e.deptno;

-- 6、列出各个部门的MANAGER 的最低薪金：
select sal from emp where job='MANAGER';
select min(sal),deptno from emp where job='MANAGER' group by deptno;

-- 7、给任职日期超过10年的人加薪10%；
select empno,ename,sal,hiredate from emp where ((sysdate-hiredate)/365)>10;
update emp set sal=sal*1.1 where (((sysdate-hiredate)/365)>10);
select * from emp;

-- 8,查询出和SCOTT工资一样的员工信息
select sal from emp where ename='SCOTT';
select empno,ename,sal,hiredate from emp where sal=(select sal from emp where ename='SCOTT');

-- 9,查询出比SCOTT工资高的员工信息
select empno,ename,sal,hiredate from emp where sal>(select sal from emp where ename='SCOTT');


-- 10.查询出不是领导的员工
-- 查询是领带的员工

select e1.ename from emp e1 join emp e2 on e1.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;

-- 12,查询出平均工资高于2000的部门名称和该部门平均工资
select d.dname,t.avgsal from dept d join (select avg(sal) avgsal,deptno from emp group by deptno having avg(sal)>2000) t on d.deptno = t.deptno;

-- 13,查询出有员工的部门【数据量大的时候用exists效率非常高】
select * from dept d where  exists (select * from emp e where e.deptno=d.deptno);


-- 14,找到员工表中薪水大于本部门平均工资的员工。
select avg(sal) from emp group by deptno ;
select ename,sal,t.avgsal,e1.deptno from emp e1 join (select avg(sal) avgsal,deptno from emp group by deptno) t on e1.deptno=t.deptno where e1.sal>t.avgsal;

-- 15,统计每年入职的员工个数
select count(*) ,to_char(hiredate,'yyyy') from emp group by to_char(hiredate,'yyyy');


-- 16,查询出emp表中工资在第六和第十之间的数据oracle中的分页查询【rownum】
select ename,sal from emp order by sal desc;
select * from(select ename,sal,rownum id from emp order by sal desc)t;

select t.sal,t.id from (select  empno ,ename ,job , mgr ,hiredate ,sal ,comm , deptno ,
rownum id from emp order by sal ) t where t.id < 10 and t.id>6;

-- 17,统计薪资大于薪资最高的员工所在部门的平均工资和薪资最低的员工所在部门的平均工资的平均工资的员工信息。
select * from emp where sal >(select max(t.avgsal) from (select avg(sal) avgsal from emp group by(deptno))t) 
union
select * from emp where sal = ( select min(sal) from emp );

-- 18,薪资最高的员工所在部门的平均工资和薪资最低的员工所在部门的平均工资的平均工资
select max(sal) from emp;
select deptno from emp where sal=(select max(sal) from emp);
select avg(sal) from emp where deptno=(select deptno from emp where sal=(select max(sal) from emp))
union
select avg(sal) from emp where deptno=(select deptno from emp where sal=(select min(sal) from emp));

-- 19,查询部门名称不是research,职位是manager,且薪资大于平均薪资的员工(包含ename hiredate loc三个字段)
select e.ename,e.hiredate,d.loc from emp e join dept d on e.deptno=d.deptno where d.dname != 'research' and e.job='MANAGER' and e.sal>(select avg(sal) from emp);