oracle的一些sql语句demo

1.取得每个部门最高薪水的人员名称

      第一步：取得每个部门的最高薪水

      select max(sal) from emp group by deptno;

第二步：根据第一步的结果和员工表进行关联，获取人员名称

select a.empno,a.ename,a.sal,a.deptno from emp a join (select deptno, max(sal) max_sal from emp group by deptno ) b on a.deptno= b.deptno and a.sal = b.max_sal;

2.哪些人的薪水在部门的平均薪水之上

      第一步：获取每个部门的平均薪水

      Select deptno, avg(sal) avgSal from emp group bydeptno

      第二步：根据第一步的结果和员工表进行关联。获取人员名称

3.取得部门中（所有人的）平均的薪水等级

第一步：获得所有人的薪水等级

select e.empno,e.ename,e.deptno,g.grade from emp e join salgrade g on e.sal  between g.losal and g.hisal 

第二步：将第一步的结果用部门进行分组。然后获取等级的平均值

select s.deptno ,avg(grade) from (select e.empno,e.ename,e.deptno,g.grade from emp e join salgrade g on e.sal  between g.losal and g.hisal ) s group by s.deptno

4.不准用组函数（Max），取得最高薪水

第一种方法：

第一步：将员工薪水降序排列

select empno,ename,sal from emp order by sal desc

第二步：取得第一条数据

select a.empno,a.ename,a.sal from (select empno,ename,sal from emp order by sal desc) a where rownum < = 1;

第二种方法：

第一步：将emp表当做2张表来处理。使用的笛卡尔乘积的方法进行比较。得到最大值以外的值。
select distinct a.sal from emp a join emp b on a.sal < b.sal 
第二步：获得最大值
select a.sal,a.empno,a.ename from emp a where sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal );
第三种方法：
第一步：将emp表当做2张表来处理。使用的笛卡尔乘积的方法进行比较。从大到小排序
select distinct a.sal from  emp a join emp b on a.sal > b.sal 
第二步：取得第一条数据
select  sal from (select distinct a.sal from  emp a join emp b on a.sal > b.sal ) where rownum <= 1

5,取得平均薪水最高的部门的部门编号。
第一种方法：
第一步：取得各部门的平均薪水。按desc排序。取得最大值
select e.deptno, avg(e.sal) avg_sal from emp e group by e.deptno  order by avg_sal desc ；
第二步：取得第一条数据
select a.deptno, a.avg_sal from (select e.deptno, avg(e.sal) avg_sal from emp e group by e.deptno  order by avg_sal desc ) a where rownum < =1;
第二种方法：
第一步：取得各部门的平均薪水
select avg(sal) avg_sal,e.deptno from emp e group by deptno;
第二步：使用聚合函数获得平均薪水最大值
select max(avg_sal) from (select  avg(sal) avg_sal,e.deptno from emp e group by deptno)
第三步：将第一步和第二步获取的结果进行关联
select m.deptno from 
(select  avg(sal) avg_sal,e.deptno from emp e group by deptno) m 
right join 
(select max(avg_sal) max_sal from (select  avg(sal) avg_sal,e.deptno from emp e group by deptno)) n
on m.avg_sal = n.max_sal
第三种方法：
第一步：获得各部门的最大值
select max(avg(sal)) from emp m group by deptno;
第二步：使用having过滤。获得最大值
select a.deptno,avg(sal) avg_sal from emp a group by deptno   having avg(sal)  = (select max(avg(sal)) from emp m group by deptno);
6.取得平均薪水最高的部门的部门名称

第一种方法：

第一步：取得各部门的平均薪水

select e.deptno ,avg(sal) avg_sal from emp e group by deptno order by avg_sal desc;

第二步：取得各部门的平均薪水最大值

select m.deptno, avg_sal from (select e.deptno ,avg(sal) avg_sal from emp e group by deptno order by avg_sal desc) m where rownum <=1 ;

第三步：跟部门表关联。获得部门名称

select t.deptno,t.dname from dept t 

right join
  (select m.deptno, avg_sal from (select e.deptno ,avg(sal) avg_sal from emp e group by deptno order by avg_sal desc) m where rownum <=1) n
on t.deptno = n.deptno;

第二种方法：

第一步：获得最高部门编号。和平均值最大值

select max(avg(sal)) from emp e  group by deptno;

第二步：关联部门表

select  t.dname,a.sal from dept t right join emp a on t.deptno = a.deptno where a.sal = (select max(avg(sal)) max_sal from emp e  group by deptno);

第三种方法：

第一步：获取平均薪水最高的部门编号

select e.deptno from emp e group by deptno having avg(sal) = (select max(avg(sal)) from emp group by deptno);

第二步：跟部门表关联

select t.dname,t.deptno from dept t where t.deptno = (select e.deptno from emp e group by deptno having avg(sal) = (select max(avg(sal)) from emp group by deptno));

7.求平均薪水的等级最低的部门的部门名称。

第一种方法：

第一步：获得平均薪水最低的部门编号,升序排列

select m.deptno from (   select e.deptno, avg(sal) avg_sal from emp e group by deptno order by avg_sal asc) m where rownum <= 1;

第二步：获得平均薪水级别最低的 部门编号 及平均薪水 及等级 

select s.grade,m.deptno,avg_sal from salgrade s

right join 

( select e.deptno, avg(sal) avg_sal from emp e group by deptno order by avg_sal asc) m on avg_sal 

between s.losal and s.hisal

where rownum < = 1

第三步：跟部门表关联。获得部门名称

select t.dname ,t.deptno ,avg_sal from dept t
 right join 
  ( select s.grade,m.deptno,avg_sal from salgrade s right join ( select e.deptno, avg(sal) avg_sal from emp e group by deptno order by avg_sal asc) m on avg_sal

between s.losal and s.hisal where rownum < = 1)  n on t.deptno = n.deptno;

8.取得比普通员工（员工代码没有在mgr字段上出现的）的最高薪水还要要的经理人姓名

第一步：获得经理人编号

select distinct mgr from emp  where mgr is not null;

第二步：普通员工薪水不在mgr字段里边的

select max(sal) max_sal from emp e where e.empno not in (select distinct mgr from emp  where mgr is not null);

第三步：获得还要高的薪水经理人的姓名

select sal ,ename from emp s where  s.sal >(select max(sal) max_sal from emp e where e.empno not in (select distinct mgr from emp  where mgr is not null))

9.取得薪水最高的前五名员工

第一种方法：使用rownum排序

select * from (

select rownum r, t.*

from

  (

   select ename, sal from emp order by sal desc

  )    t

  where rownum <=5

)wherer>  0

第二种方法：

第一步：取得员工的最高薪水

select sal from emp order by sal desc ;

第二步：获得前5名薪水

select sal from (select sal from emp order by sal desc ) where rownum <= 5;

第三步：跟员工表自关联 获得名称

select e.ename,e.sal from emp e where e.sal in (select sal from (select sal from emp order by sal desc ) where rownum <= 5) order by sal desc;

10.取得薪水最高的第六到第十名员工

第一步：取得全部薪水

select sal,ename from emp order by sal desc

第二步：获得薪水第1名到第10名的员工薪水和姓名

select rownum r , ename ,sal from 
(select sal,ename from emp order by sal desc)
where rownum <= 10

第三步：排除前5个

select t.* from 
(
select rownum r , ename ,sal from 
(select sal,ename from emp order by sal desc)
where rownum <= 10
) t where r>5

11.取得最后入职的5名员工

第一步：降序排序最后入职

select ename,hiredate from emp order by hiredate desc；

第二步：获得最后5名员工

select ename,hiredate from (select ename,hiredate from emp order by hiredate desc) where rownum <= 5;

12.取得每个薪水等级有多少员工

第一步：取得每个员工的薪水等级

select s.grade,t.ename,t.sal from salgrade s join (select ename,sal from emp) t on t.sal between s.losal and s.hisal

第二步：获得每个薪水等级的员工个数

select count(*),m.grade from (select s.grade,t.ename,t.sal from salgrade s join (select ename,sal from emp) t on t.sal between s.losal and s.hisal
) m group by m.grade

13.列出所有员工及直接上级的姓名
Select e.ename, nvl (m.ename, '没有上级') mname from emp e left join emp m on e.mgr = m.empno


14.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
select e.empno,e.ename,t.dname from emp e join emp a on e.hiredate < a.hiredate and e.mgr = a.empno join dept  t on e.deptno = t.deptno;


15.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.
select a.*,t.dname  from emp a right join dept t on t.deptno = a.deptno;


16.列出至少有一个员工的所有部门
Select dname, count(*) from emp e join dept d on e.deptno = d.deptno group by dname


17.列出薪金比"SMITH"多的所有员工信息.
select * from emp where sal > (select sal from emp where ename = 'SMITH');


18.列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.
1.首先获得"CLERK"(办事员)的姓名、部门编号
select e.deptno,e.ename,e.job from emp e where e.job = 'CLERK';
2.其次获得相对应部门名称
select t.deptno,t.dname,n.job,n.ename from dept t right join (select e.deptno,e.ename,e.job from emp e where e.job = 'CLERK') n on  n.deptno = t.deptno
3.其次各部门的人数
select count(*) tt ,j.dname from emp v join dept j on v.deptno = j.deptno group by j.dname
4.最后查询出结果
select t1.ename,t1.deptno,t1.dname, t2.tt from
(select t.deptno,t.dname,n.job,n.ename from dept t join (select e.deptno,e.ename,e.job from emp e where e.job = 'CLERK') n on  n.deptno = t.deptno) t1
 join 
(select count(*) tt ,j.dname from emp v join dept j on v.deptno = j.deptno group by j.dname ) t2
on
t1.dname = t2.dname;


19.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数.
1.获得薪水大于1500 的工作名称 
select e.job from emp e group by e.job having min(sal) > 1500
2.获得每个员工的工作人数
select count(*) cc from emp group by job 
3.获得结果
select t1.job,t2.cc from (select e.job from emp e group by e.job having min(sal) > 1500) t1 join (select count(*) cc ,job from emp group by job 
) t2 on t1.job = t2.job


20.列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号.
1.首先要获得销售部的部门编号
select deptno from dept where dname = 'SALES'
2.根员工表关联。获得员工的姓名
select * from emp where deptno = (select deptno from dept where dname = 'SALES')


21.列出与"SCOTT"从事相同工作的所有员工及部门名称.
第一种方法：逐步分析
1.列出所有"SCOTT"的部门编号及从事的工作
select job from emp where ename = 'SCOTT'
2.列出从事‘SCOTT’的工作一样的员工
select * from emp  where job = (select job from emp where ename = 'SCOTT') and ename != 'SCOTT'
3.关联部门表。获得部门名称.并且排除SCOTT
select dname,t1.* from dept t  right join (select * from emp  where job = (select job from emp where ename = 'SCOTT') and ename != 'SCOTT') t1 on t.deptno = t1.deptno 


第二种方法：比较简洁（推荐使用）
Select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno and e.job = (Select job from emp where ename = 'SCOTT') and e.ename != 'SCOTT'


22.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金.
Select ename, sal from emp where sal in (Select distinct sal from emp where deptno = 30)


23.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称.
第一种方法：
1.首先获得部门30工作的所有员工的薪水.获得最大值
select max(sal) from emp where deptno = '30'
2.获得结果
select e.ename,e.sal,t.dname from emp e,dept t where e.sal > (select max(sal) from emp where deptno = '30') and e.deptno =t.deptno
第二种方法：
Select ename, sal from emp where sal > (Select max(sal) maxSal from emp where deptno = 30) and deptno != 30


第三种方法：
Select e.ename, e.sal, d.dname from emp e join dept d on e.deptno = d.deptno where e.sal > (Select max(sal) maxSal from emp where deptno = 30) and e.deptno != 30

24.列出在每个部门工作的员工数量,平均工资和平均服务期限.
Select d.dname 部门名称, count(e.empno) 员工数量, avg(e.sal) 平均工资 from  emp e, dept d where e.deptno = d.deptno group by d.dname
Select d.dname 部门名称, count(e.empno) 员工数量, round(avg(e.sal),2) 平均工资,  round (avg( (sysdate-hiredate)/365), 0) 平均服务期限 from  emp e, dept d where e.deptno = d.deptno group by d.dname


25.列出所有员工的姓名、部门名称和工资。
select e.ename , t.dname , e.sal  from emp e , dept t where e.deptno = t.deptno 


26.列出所有部门的详细信息和人数
select t.* , (select count(e.empno) from emp e where e.deptno =t.deptno ) 人数  from dept t


27.列出各种工作的最低工资及从事此工作的雇员姓名
select job, min(sal) minsal from emp a group by job


select e.* from emp e join (
select job, min(sal) minsal from emp a group by job
) m
on e.job = m.job and e.sal = m.minsal


28。列出各个部门的MANAGER(经理)的最低薪金
select  min(e.sal) 最低薪水 ,e.deptno from emp e ,emp k where e.mgr = k.empno group by e.deptno


29.列出所有员工的年工资,按年薪从低到高排序
select ename,(sal+nvl(comm,0))*12 sal_year from emp order by sal_year 


30.查出某个员工的上级主管，并要求出这些主管中的薪水超过3000.
select e.ename,e.empno,e.mgr,e.sal,f.ename,f.sal from emp e left join emp f on e.mgr = f.empno where f.sal>3000


31.求出部门名称中,带'S'字符的部门员工的工资合计、部门人数.
第一种方法：
1.求出部门员工的工资合计和部门人数
select sum(e.sal),count(e.empno) from emp e group by e.deptno
2.跟部门表关联
select t.dname 部门名称 ,sumsal 工资合计 ,tt 部门人数 from dept t left join 
(
select e.deptno, sum(e.sal) sumsal ,count(e.empno) tt from emp e group by e.deptno
) n
on t.deptno =n.deptno where t.dname like '%S%';
第二种方法：
Select d.dname, sum(e.sal), count(e.empno) from emp e right join dept d on e.deptno = d.deptno where d.dname like '%S%' group by d.dname


32.给任职日期超过25年的员工加薪10%.
Select * from emp where months_between(sysdate, hiredate)/12 > 25
Update emp set sal = sal*1.1 where months_between(sysdate, hiredate)/12 > 25

33.选择部门30中的所有员工
select * from emp where deptno = '30'


34.列出所有办事员(CLERK)的姓名，编号和部门编号
select e.empno, e.ename, e.deptno from emp e join dept t on e.deptno =t.deptno where e.job = 'CLERK'


35.找出佣金高于薪金的员工.
select empno,ename from emp where comm > sal


36.找出佣金高于薪金的60%的员工.
select empno,ename from emp where comm > sal*0.6


37.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料.
select * from emp where (deptno = '10' and job = 'MANAGER') or(deptno = '20' and job='CLERK')


38.找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.
select * from emp where (( deptno = '10' or deptno = '20')  and (job <> 'MANAGER' and job <>'CLERK') ) and (sal >= 2000)


39.找出收取佣金的员工的不同工作，按降序排列
select distinct * from  emp where nvl(comm,0) > 0 order by comm desc


40.找出不收取佣金或收取的佣金低于100的员工
select * from emp where nvl(comm,0) < 100


41.找出各月倒数第3天受雇的所有员工.并且按月降序排序
select empno,ename,last_day(hiredate)-3 from emp order by  to_char(hiredate,'mm') desc


42。找出早于12年前受雇的员工. 并且按年来分组，倒序排序
select * from emp where months_between(sysdate,hiredate)/12 >12 order by to_char(hiredate,'yyyy') desc


43.以首字母大写的方式显示所有员工的姓名.
select empno, initcap(ename) from emp


44.显示正好为5个字符的员工的姓名
select ename from emp where length(ename) = 5


45.显示不带有"R"的员工的姓名.
select ename from emp where ename not like '%R%';


46.显示所有员工姓名的前三个字符.
select substr(ename,1,3) from emp;


47.显示所有员工的姓名,用a替换所有"A"
select replace(ename,'A','a') from emp 


48.显示满10年服务年限的员工的姓名和受雇日期.
select ename, hiredate from emp where months_between(sysdate,hiredate)/12 >= 10


49.显示员工的详细资料,按姓名排序.
select * from emp order by ename 


50.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.
select ename,hiredate from emp order by months_between(sysdate,hiredate)/12 desc


51.显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序.
select ename ,job,sal from emp order by job,sal asc


52.显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面.
select ename ,to_char(hiredate,'yyyy') year , to_char(hiredate,'mm') months from emp order by months , year asc ;


53.显示在一个月为30天的情况所有员工的日薪金,保留2位小数.
select ename,round(sal/30,2 )dailysal from emp


54.找出在(任何年份的)2月受聘的所有员工。
select * from emp where to_char(hiredate,'mm') = 2


55.对于每个员工,显示其加入公司的天数,不带余数
select ename,round(sysdate-hiredate,0) 天数 from emp


56.显示姓名字段的任何位置包含"A"的所有员工的姓名,如果相同位置的按名字排序
select ename from emp where ename like '%A%'order by ename


57.以年月日的方式显示所有员工的服务年限.
select ename,to_char(hiredate,'yyyy/mm/dd') from emp