Oracle SQL的练习（转）
--列出员工表中每个部门的员工数，和部门 noselect t.deptno,count(1) from scott.emp t group by t.deptno;--列出员工表中每个部门的员工数（员工数必须大于 3），和部门名称select t.deptno,t1.dname,count(1)from scott.emp tleft join scott.dept t1 on t.deptno=t1.deptnogroup by t.deptno,t1.dnamehaving count(1)>3;--找出工资比 ward 多的员工select a.* from scott.emp a, scott.emp b where a.sal>b.sal and b.ename='ward'; --列出所有员工的姓名和其上级的姓名select a.empno,a.ename,a.mgr,b.empno,b.ename from scott.emp a, scott.emp bwhere a.mgr=b.empno;--以职位分组，找出平均工资最高的两种职位select * from (select job,avg(sal) from scott.emp group by job order by avg(sal) desc )where rownum<3;--查找出不在部门 20，且比部门 20 中任何一个人工资都高的员工姓名、部门名称--比任何人的工资高就是比工资最高的还要高select * from scott.emp where sal>(select max(sal) from scott.emp where deptno='20') and deptno<>'20';--得到平均工资大于 2000 的工作职种select job from scott.emp group by job having avg(sal)>2000;--分部门得到 工资大于 2000 的所有员工的平均工资，并且平均工资还要大于 2500select t.deptno,avg(sal) from scott.emp t where t.sal>2000 group by t.deptno having avg(sal)>2500;--得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置select * from scott.deptwhere deptno = ( select c.deptno from (select deptno,sum(sal) from scott.emp group by deptno order by sum(sal)) c where rownum=1);--分部门得到平均工资等级为 3 级（等级表）的部门编号select b.dno from scott.salgrade a,(select t.deptno as dno,avg(t.sal) as avgsalfrom scott.emp tgroup by t.deptno) bwhere a.grade=3 and b.avgsal between a.losal and a.hisal;--查找出部门 10 和部门 20 中，工资最高第 3 名到工资第 5 名的员工的员工名字，部门名字，部门位置select * from (select rownum no,b.* from (select a.* from scott.emp a where a.deptno in(10,20) order by a.sal desc) b) cwhere c.no>=3 and c.no<=5;select c.ename,d.dname,d.loc from (select rownum no,b.* from (select a.* from scott.emp a where a.deptno in(10,20) order by a.sal desc) b) c,scott.dept dwhere c.deptno=d.deptno and c.no>=3 and c.no<=5;--查找出收入（工资加上奖金），下级比自己上级还高的员工编号，员工名字，员工收入select a.empno,a.ename,a.sal+nvl(a.comm,0) from scott.emp a,scott.emp b where a.mgr=b.empno and (a.sal+nvl(a.comm,0))>(b.sal+nvl(b.comm,0));--查找出工资等级不为 4 级的员工的员工名字，部门名字，部门位置select c.ename,c.deptno,d.loc,c.sal from scott.dept d,(select a.ename,a.deptno,a.sal from scott.emp a,scott.salgrade bwhere b.grade<>4 and a.sal between b.losal and b.hisal) cwhere d.deptno=c.deptno;--查找出职位和'MARTIN' 或者'SMITH'一样的员工的平均工资select avg(sal) from scott.emp where job in (select job from scott.emp where ename in('MARTIN','SMITH')) group by job;--查找出不属于任何部门的员工select * from scott.emp where deptno is null or deptno not in(select deptno from scott.dept);--按部门统计员工数，查处员工数最多的部门的第二名到第五名（列出部门名字，部门位置）select c.dname,c.loc from (select rownum no,a.* from(select t.deptno,count(1) from scott.emp t group by t.deptno order by count(1) desc) a）b ,scott.dept cwhere b.deptno=c.deptno and b.no>=2 and b.no<=5;--查询出 king 所在部门的部门号\部门名称\部门人数select b.deptno,a.dname,b.count from scott.dept a,(select deptno,count(1) as count from scott.emp where deptno in(select deptno from scott.emp where ename = 'KING') group by deptno) bwhere a.deptno=b.deptno;select b.deptno,a.dname,b.count from scott.dept a,(select deptno,t.count from(select deptno,count(1) as count from scott.emp group by deptno) t where t.deptno in(select deptno from scott.emp where ename = 'KING')) bwhere a.deptno=b.deptno;select a.deptno 部门号,a.dname 部门名称,(select count(*) from scott.emp where deptno in (select deptno from scott.emp where ename ='KING')) 部门人数 from scott.dept a,scott.emp b where a.deptno=b.deptno and b.ename='KING'; --查询出 king 所在部门的工作年限最大的员工名字select t.ename from(select rownum no,a.* from(select ename,hiredate from scott.emp where deptno in(select deptno from scott.emp where ename='KING') order by hiredate) a) twhere t.no=1;--查询出工资成本最高的部门的部门号和部门名称select deptno,dname from scott.dept where deptno in(select b.deptno from (select rownum no,a.* from (select deptno,sum(sal) from scott.emp group by deptno order by sum(sal) desc) a) b where b.no=1 );