oracle复杂查询练习题

1、列出至少有一个员工的所有部门

select dname
from dept 
where deptno in(select deptno from emp group by deptno having count(deptno)>=1);

2、列出薪金比“SMITH”多的所有员工

select * from emp where sal>(select sal from emp where ename='SMITH')`;

3、列出所有员工的姓名以及其直接上级的姓名

select a.ename,(select ename from emp b where b.empno=a.mgr)as bossname from emp a;

4、列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称

select a.empno,a.ename,dname from emp a,dept d where a.hiredate<(select hiredate from emp b where b.empno=a.mgr)and a.deptno=d.deptno;

5、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

select a.dname,b.* from dept a left join emp b on a.deptno=b.deptno;

6、列出所有“CLERK”的姓名及其部门名称，部门的人数
要求部门的人数，要使用分组统计来完成

select a.dname,b.* from dept a left join emp b on a.deptno=b.deptno;
select e.ename,e.deptno,d.dname,t.cou from emp e,dept d,
(select deptno,count(*)cou from emp group by deptno)t
where e.deptno=d.deptno and e.job='CLERK'and e.deptno=t.deptno;

7、列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数

select job,count(*) from emp group by job having min(sal)>1500;

8、列出在部门“sales”（销售部）工作的员工的姓名，假定不知道销售部的部门编号

select ename from emp e,dept d where e.deptno=d.deptno and d.dname='SALES';

9、列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，员工的工资等级
求出公司的平均工资

select avg(sal) from emp;

求出薪金高于平均工资的所有的员工

select * from emp where sal>(select avg(sal) from emp);

与部门表关联

select e.*,d.dname from emp e,dept d where sal>(select avg(sal)from emp) and e.deptno=d.deptno;

要查询上级领导emp

select e.*,d.dname,e1.ename from emp e,dept d,emp e1 
where e.sal>(select avg(sal)from emp) and e.deptno=d.deptno and e.mgr=e1.empno;

要查询员工的工资等级 salgrade

select e.*,d.dname,e1.ename,sg.grade from emp e,dept d,emp e1,salgrade sg
where e.sal>(select avg(sal)from emp) and e.deptno=d.deptno and e.mgr=e1.empno 
and e.sal between sg.losal and sg.hisal;

10、列出与“scott”从事相同工作的所有员工及部门名称

select e.*,d.dname 
from emp e,dept d 
where e.job=(select job from emp where ename='SCOTT')and e.deptno=d.deptno and e.ename<>'SCOTT';

要查询出scott这个人的工作

select job from emp where ename='SCOTT'

列出工作是analysis的所有员工，排除scott

select e.* 
from emp e 
where e.job=(select job from emp where ename='SCOTT') and e.ename<>'SCOTT';

列出工作是analysis的所有员工和部门名称，排除scott

select e.*,d.dname 
from emp e,dept d 
where e.job=(select job from emp where ename='SCOTT')and e.deptno=d.deptno and e.ename<>'SCOTT';

11、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金
部门30员工的薪金

select sal from emp where deptno=30;

把上面的查询作为一个子查询的条件

select ename,sal 
from emp 
where sal in(select sal from emp where deptno=30);

12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金，部门名称

select ename,sal,dname 
from emp e,dept d 
where sal >(select max(sal)from emp where deptno=30)and        e.deptno=d.deptno;

13、列出在每个部门工作的员工数量、平均工资和平均服务期限

select count(empno),avg(sal)
from emp 
group by deptno;

14、列出所有员工的姓名、部门名称和工资

 select e.ename,d.dname,e.sal 
    from emp e,dept d 
    where e.deptno=d.deptno;

15、列出所有部门的详细信息和部门人数
列出所有部门的人数

select d.*,
(select count(*)from emp where deptno=d.deptno)部门人数 
from dept d;

16、列出各种工作的最低工资以及从事此工作的雇员姓名

select e.ename,e.sal
from emp e,(select job,min(e.sal)minsal from emp e group by job)temp
where temp.job=e.job and e.sal=temp.minsal;

17、列出各个部门的经理的最低薪金

select deptno,min(sal)
from emp 
where job='MANAGER'
group by deptno;

18、列出所有员工的年工资，按年薪从低到高排序

select d.dname,(e.sal+nvl(e.comm,0))*12 yearsal
from dept d,emp e
where d.deptno=e.deptno
order by yearsal

19、查出某个员工的上级主管，并要求出这些主管中的薪水超过3000

select e1.ename,e1.sal
from emp e,emp e1
where e.mgr=e1.empno and e1.sal>3000;

20、求出部门名称中，带’S’字符的部门员工的工资总和 、部门人数

select sum(e.sal),count(e.empno)
from emp e,dept d
where d.dname like '%S%' and e.deptno=d.deptno
group by d.dname;