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

Oracle经典SQL

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;

Oracle经典SQL

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

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

Oracle经典SQL

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 未完待续......

上一篇:【wireshark】插件开发(五):C插件


下一篇:用BigDecimal类实现Fibonacci算法