oracle根据工作进行查询,oracle查询练习题

1.查询平均工资最高的部门的部门编号、部门名称和该部门的平均工资

select t.deptno,t.num,d.dname from

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

dept d

where t.deptno=d.deptno and t.num=

(select max(avg(sal)) num from emp group by deptno);

2.查询所有员工的年薪、所在部门的名称，查询结果按年薪从低往高排序

方法一

select sal*12+nvl(comm,0) sal_year,dname from emp e,

dept d where e.deptno=d.deptno order by sal_year ;

方法二

select e.sal*12+nvl(e.comm,0) ,d.dname from emp e

left join dept d on e.deptno=d.deptno order by e.sal*12+nvl(e.comm,0);

3.查询每种工作的最低工资，以及领取该工资的员工姓名，查询结果显示工作名称、最低工资、领取该工资的员工姓名

select e.job,t.sal_min,e.ename from emp e,

(select job,min(sal) sal_min from emp group by job) t

where e.sal=t.sal_min and e.job=t.job;

4.查询出管理员工人数最多的人的名字和他管理的人的名字

方法一

select e1.ename,wm_concat(e2.ename) from emp e1,emp e2 where e1.empno=e2.mgr and e1.empno=(

select t.mgr from (

select mgr, count(*) num from emp group by mgr

)t

where t.num = (

select max (count(*)) num from emp group by mgr

))group by e1.ename;

方法二

select * from emp where empno=(

select t.mgr from(

select mgr,count(*) num from emp group by mgr

) t

where t.num=

(select max(count(*)) num from emp group by mgr));

5.查询所有员工的编号、姓名，及其上级领导的编号、姓名。显示结果按领导的年薪降序排列

select e1.empno,e1.ename,e2.empno,e2.ename,e1.sal*12+nvl(e1.comm,0) sal_year

from emp e1,emp e2

where e1.empno=e2.mgr order by sal_year desc;

6.查询所有领取奖金和不领取奖金的员工人数、平均工资:查询结果的列名分别为:

人数、平均工资:第一行为有奖金的员工，第二行为没有奖金的员工

select count(*) num,avg(sal) sal_avg from emp where comm is not null

union all

select count(*) num,avg(sal) sal_avg from emp where comm is null

7.查询工资不超过2500的人数最多的部门名称和该部门工资不超过2500的员工的员工人数

select d.dname, t.num from (

select deptno, count(*) num from emp where sal<2500 group by deptno

)t ,dept d where t.deptno=d.deptno and num= (

select max (count(*)) num from emp where sal<2500 group by deptno);

8.查询受雇日期早于其直接上级的所有员工的编号，姓名，部门名称

方法一

select e2.ename,e2.empno,d.dname from emp e1,emp e2,dept d

where e1.empno=e2.mgr and e2.hiredate

and e2.deptno=d.deptno

方法二

select e2.ename,e2.empno,d.dname from emp e1

inner join emp e2

on e1.empno=e2.mgr and e2.hiredate

inner join dept d

on e2.deptno=d.deptno

9.查询至少有4个员工的部门的部门名称

select d.dname,count(*) nums from emp e ,dept d

where e.deptno=d.deptno group by d.dname having count(*)>=4

10.查询工资比“SMITH"高的员工的基本信息

select * from emp where sal>(

select sal from emp where ename='SMITH'

)

11.查询部门名称中带’S’字符的部门的员工的工资总和部门人数，显示结果为部门名称，部门员工的工资总和，部门人数

select dname,count(*) num ,sum(sal) from emp e,dept d

where e.deptno=d.deptno and d.dname like'%S%' group by dname

12.查询所有从事"CLERK"工作的雇员所在部门的部门名称、部门里的人数

select d.dname,count(*) num from emp e,dept d where e.deptno=d.deptno and e.deptno in(

select distinct deptno from emp where job='CLERK'

) group by d.dname

13.查询雇员领导的基本信息，要求领导的薪水要超过3000

select distinct e1.* from emp e1

inner join emp e2

on e1.empno=e2.mgr and e1.sal>3000

14.查询在"sales"部门( 销售部)工作的员工的姓名

select ename from emp e inner join dept d on e.deptno=d.deptno and d.dname='SALES'

15.查询工资高于30号部门的所有员工的工资的员工姓名、工资及部门名称

select ename,sal,dname from emp e,dept d where e.deptno=d.deptno and sal>all(

select sal from emp where deptno=30)

16.查询所有部门的详细信息(部门编号、部门名称)和部门人数

select d.deptno,dname,count(e.ename)num from dept d

left join emp e

on d.deptno=e.deptno group by d.deptno,dname

显示每个部门中每个岗位的平均工资、–每个部门的平均工资、每个岗位的平均工资

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

18.显示与"BLAKE"同部门的所有员工的基本信息，但不显示"BLAKE"的基本信息

select * from emp where deptno=(

select deptno from emp where ename='BLACK')

and ename!='BLACK'

19.查询出"KING"所在部门的部门编号、部门名称以及该部门里的员工人数

方法一

select d.deptno,dname,count(*) from emp e

inner join dept d

on e.deptno=d.deptno and e.deptno=(select deptno from emp where ename='KING')

group by d.deptno,dname

方法二

select max(d.deptno),max(dname),count(*) from emp e

inner join dept d

on e.deptno=d.deptno and e.deptno=(select deptno from emp where ename='KING')

20.查询出"WARD"所在部门的工作年限最大的员工的姓名

select * from emp where deptno=(select deptno from emp where ename='WARD')

and hiredate=(select min(hiredate) from emp where deptno=(select deptno from emp where ename='WARD'))

21.查询出没有下属的员工的姓名及他的职位

select * from emp where empno not in(select distinct mgr from emp where mgr is not null )

22.查询出员工姓名以A开头的人数最多的部门的部门名称

select dname from dept d,(

select * from (

select deptno,count(*) num from emp where ename like 'A%' group by deptno

) where num =(select max(count(*)) num from emp where ename like 'A%' group by deptno)

) t where d.deptno=t.deptno

23.查询出SMITH所在部门的部门名称、部门工资的平均值(注意平均值保留两位小数)

select dname,to_char(avg(sal),'9999.99') from emp e,dept d where e.deptno=d.deptno

and e.deptno=(select deptno from emp where ename='SMITH')

group by dname