oracle自来水boss建表语句,oracle sql语句练习题

1.列出员工表中每个部门的员工数，和部门no

select deptno,count(*) from emp group by deptno;

2.列出员工表中每个部门的员工数(员工数必须大于3)，和部门名称

select d.dname,t.cou

from

dept d,

(select deptno,count(*) cou from emp group by deptno having count(*)>3) t

where

d.deptno = t.deptno;

3.找出工资比jones多的员工

select * from emp where sal>(select sal from emp where lower(ename)='jones');

select e.*

from emp e,(select * from emp where lower(ename)='jones') t

where e.sal>t.sal;

4.列出所有员工的姓名和其上级的姓名

select xd.ename ,boss.ename boss_name from emp xd,emp boss where xd.mgr=boss.empno;

5.以职位分组，找出平均工资最高的两种职位

select t.*

from

(select job,avg(sal) from emp group by job order by avg(sal) desc) t

where

rownum<=2;

6.查找出不在部门20，且比部门20中任何一个人工资都高的员工姓名、部门名称

select ename,dname

from

dept,

(select ename,deptno from emp where

deptno!=20 and sal>all(select sal from emp where deptno=20))t

where

t.deptno=dept.deptno;

7.得到平均工资大于2000的工作职种

select job from emp group by job having avg(sal)>2000;

8.分部门得到工资大于2000的所有员工的平均工资，并且平均工资还要大于2500

select deptno,avg(sal) from emp where sal>2000 group by deptno having avg(sal)>2500;

9.得到每个月工资总数最少的那个部门的部门编号，部门名称，部门位置

select d.*

from

dept d,

(select * from

(select deptno,sum(sal) as sum_sal from emp group by deptno order by sum(sal))

where rownum=1) t

where

d.deptno = t.deptno;

10.分部门得到平均工资等级为2级(等级表)的部门编号

select t.deptno

from

salgrade s,

(select deptno,avg(sal) avg_sal from emp group by deptno) t

where

t.avg_sal between s.losal and s.hisal and

s.grade = 2;

select e1.deptno from

(select deptno,avg(sal) avg_sal from emp group by deptno) e1,

(select hisal,losal from salgrade where grade=2) g1

where e1.avg_sal between g1.losal and g1.hisal;

select deptno from emp group by deptno having avg(sal) between

(select losal from salgrade where grade=2) and

(select hisal from salgrade where grade=2)

11.查找出部门10和部门20中，工资最高第3名到工资第5名的员工的员工名字部门名字部门位置

select t2.ename,d.dname,d.loc

from

dept d,

(select *

from

(select rownum no,t.* from

(select * from emp where deptno in (10,20) order by sal desc) t

) t1

where

t1.no>=3 and t1.no<=5) t2

where

d.deptno = t2.deptno;

12.查找出收入(工资加上奖金)，下级比自己上级还高的员工编号，员工名字，员工收入

select e.empno,e.ename,e.sal+nvl(http://www.doczj.com/doc/bdd129e4c9d376eeaeaad1f34693daef5ef713af.htmlm,0)

from

emp e,

emp boss

where

e.mgr = boss.empno and

e.sal+nvl(http://www.doczj.com/doc/bdd129e4c9d376eeaeaad1f34693daef5ef713af.htmlm,