最近本人整理了一些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 未完待续......