Oracle经典SQL

最近本人整理了一些Oracle sql，现分享给大家，后续还会更新。如果有错误的地方，请指正，共同学习。贴上去的sql都是我测试过的，大家可以粘贴在自己的电脑上试试。

1.查询部门的名称，及最低收入雇员姓名，要进行表关联

select e.deptno,min(e.sal) from emp e group by e.deptno;

select ee.ename, d.dname,ee.sal from emp ee ,dept d where ee.deptno = d.deptno and ee.sal=(select min(sal) from emp);

　　

2.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数

<1>按工作分组，分组条件最低工资大于1500
SELECT job,MIN(sal) FROM emp GROUP BY job  HAVING MIN(sal)>1500;
<2>根据求出来的工作来求员工数
SELECT e.job,COUNT(e.empno) FROM emp e WHERE e.job IN(SELECT job FROM emp  GROUP BY job   HAVING MIN(sal) > 1500) GROUP BY e.job;

3. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级

<1>查询出公司的平均工资
select avg(sal) from emp;  2073.21428571429
<2>
select e.ename,e.empno,e.deptno,m.ename as leader from emp e,emp m where e.sal>(select avg(sal)from emp) and e.mgr = m.empno
<3>
select t.sal,t.ename,d.dname,t.leader,s.grade
from (select e.sal,e.ename,e.empno,e.deptno,m.ename as leader from emp e,emp m where e.sal>(select avg(sal)from emp) and e.mgr = m.empno(+)) t,dept d,salgrade s
where t.deptno = d.deptno and t.sal between s.losal and s.hisal;

网上的答案：
SELECT e.empno,e.ename,s.grade,m.empno leaderNo,m.ename as leaderName,d.deptno,d.dname,d.loc 
FROM emp e,dept d,emp m,salgrade s 
WHERE e.sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr=m.empno(+) AND e.sal BETWEEN s.losal AND s.hisal;

 

Oracle 经典SQL

1.    查询平均工资最低的部门及工资

步骤：查询平均工资及其部门编号
select deptno, avg(sal) from emp group by deptno

select e.deptno, avg(sal) avgsal from emp e,dept d where e.deptno = d.deptno group by e.deptno order by avgsal

select a.deptno,a.avgsal from (select e.deptno, avg(sal) avgsal from emp e,dept d where e.deptno = d.deptno group by e.deptno order by avgsal) a where rownum = 1

2.    查询大于本部门平均工资的员工

方法一:
select deptno, avg(sal) avgsal from emp group by deptno;

select e.*,t.avgsal from emp e ,(select deptno, avg(sal) avgsal from emp group by deptno) t 
where e.deptno = t.deptno and e.sal > t.avgsal
方法二：
select * from emp e where e.sal>(select avg(sal) from emp where e.deptno = deptno)

3.    查询各部门管理者的分布情况

select count(distinct(mgr)), deptno from emp where mgr is not null group by deptno

4.    查询表中的重复记录

create table emp1 as select * from emp;

insert into emp1 select * from emp;
commit;

select a.rowid,a.* from emp1 a 
       where a.rowid > 
          (select min(b.rowid) from emp1 b   --注意这里是组函数。如果写成了b.rowid 的话，就会报“ORA-01427单行子查询返回多个行”。
              where 
                  a.empno = b.empno 
                  and a.ename = b.ename 
                  and a.job = b.job 
                  and nvl(a.mgr,1) = nvl(b.mgr,1)
                  and a.hiredate = b.hiredate
                  and a.sal = b.sal
                  and nvl(a.comm,1) = nvl(b.comm,1)
                  and a.deptno = b.deptno
           )

5.    删除表中的重复记录（删除和上面的查询差不多）

delete from emp1 a 
       where a.rowid > 
             (select min(b.rowid) from emp1 b 
                     where 
                          a.empno = b.empno 
                          and a.ename = b.ename 
                          and a.job = b.job 
                          and nvl(a.mgr,1) = nvl(b.mgr,1)
                          and a.hiredate = b.hiredate
                          and a.sal = b.sal
                          and nvl(a.comm,1) = nvl(b.comm,1)
                          and a.deptno = b.deptno
             )

6.    查询emp表的工资排名前十的员工信息

方法一：
select * from (select * from emp e order by sal desc) 
            where rownum < 11 

方法二：(百度)
select * from (select a.*,row_number() over(order by sal desc) rn from emp a )where rn < 11

7.    查询每个部门工资最高的2人

注意：这道题是求每个部门的，一想到的可能就是按部门编号分组(group by deptno )这样一来的话，后面的将会非常难写，反正我是没有写出来。
方法一：（使用ROW_NUMBER()进行排位分组 按照deptno分组）
select * from (select e.*, row_number() over(partition by deptno order by sal desc)rn from emp e) where rn <=2

方法二：（这里的emp表要经过order by sal desc 先排序，直接写emp查询的数据将是错误的。这个方法可以针对empno不相等的情况）

select * from emp a 
       where 
           a.empno in
           (select b.empno from (select * from emp order by sal desc) b 
                 where b.deptno = a.deptno and  rownum <=2 
           )
方法三：（这个方法可以针对empno不相等的情况）
select * from emp a where 2>(select count(*) from emp b where b.deptno = a.deptno and b.sal>a.sal)

方法四：（通用查询：MySQL也适用）
SELECT  *
FROM emp t1     
WHERE (SELECT COUNT(1) FROM emp t2 WHERE t2.deptno=t1.deptno AND t2.sal >= t1.sal) <=2
ORDER BY deptno ASC,sal DESC

8.    查询工资比其他领导高的员工

select a.empno,a.ename,a.sal,b.empno 领导员工号,b.ename 领导姓名,b.sal 领导工资 
   from emp a ,emp b 
      where a.sal>b.sal and a.mgr = b.empno;

9.    工资由高到低拍名词

这个主要涉及了两个函数rank() 和dense_rank() 结合over(XXX) 开窗函数
select e.ename,e.sal,rank() over(order by sal desc)rank_,dense_rank() over(order by sal desc)drank from emp e

10.    查询下属个数在两个以上的员工信息(即:至少有两个员工的领导)

select m.* from emp m where (select count(*) from emp where mgr = m.empno)>2
由此题可以提高一些难度如下

11.查询每个部门中下属个数在两个以上的员工信息(即:至少有两个员工的领导)

答案就是还是上面的答案喽。因为每个员工信息中包含了部门编号(deptno)顶多在根据deptno排序就可以了。

12.查询大于部门总平均工资的部门

select d.* ,sum_sal 
from dept d,
      (select sum(sal) sum_sal,deptno 
           from emp group by deptno
       ) e 
         where 
             d.deptno = e.deptno 
             and e.sum_sal > (select avg(sum(sal)) 
                                     from emp group by deptno
                                 )

13.1981年各月入职的员工数（此题未完）

方法一:
select extract(month from hiredate) month ,count(*) from emp 
      where extract(year from hiredate) = 1981 group by extract(month from hiredate) order by month;

14.统计每年的入职的员工数（此题未完）

方法一：
select extract(year from hiredate) year ,count(*) from emp group by extract(year from hiredate) order by year

15.列出至少有一个员工的所有部门

select dname from dept where deptno in(select deptno from emp group by deptno having count(deptno) >=1);
或者
select d.* from dept d, (select deptno ,count(deptno) cou from emp group by deptno)t where d.deptno = t.deptno and cou>=5

 

2016年6月3日17:36:48 未完待续......