多表查询的练习题
?1、列出至少有一个员工的所有部门
count(*)>=1
select deptno,count(*) from emp group by deptno having count(*)>=1
2、列出薪金比“SMITH”多的所有员工
select sal from emp where ename='SMITH';
select * from emp where sal>(select sal from emp where ename='SMITH');
3、列出所有员工的姓名以及其直接上级的姓名
select e.ename 雇员的姓名,m.ename 领导的姓名
from emp e,emp m
where e.mgr=m.empno(+);
4、列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称
select e.empno,e.ename,d.dname
from emp e,emp m,dept d
where e.mgr=m.empno and e.deptno=d.deptno and e.hiredate
select d.*,e.*
from emp e,dept d
where e.deptno(+)=d.deptno;
6、列出所有“CLERK”的姓名及其部门名称，部门的人数
select deptno,count(*) from emp group by deptno;
select e.ename,d.dname,temp.cou
from (select deptno,count(*) cou from emp group by deptno) temp,emp e,dept d
where temp.deptno=e.deptno and e.deptno=d.deptno and job='CLERK';
7、列出最低薪金大于1500的各种工作及此从事此工作的全部雇员人数
select job,min(sal) from emp group by job having min(sal)>1500;
select count(*)
from emp
where job in (select job from emp group by job having min(sal)>1500)
group by job;
8、列出在部门“sales”（销售部）工作的员工的姓名，假定不知道销售部的部门编号
select deptno from dept where dname=upper('sales');
select ename from emp where deptno=(
select deptno from dept where dname=upper('sales'));
9、列出薪金高于公司平均薪金的所有员工，所在部门，上级领导等级，公司的工资等级
select avg(sal) from emp;
sal>(select avg(sal) from emp)
select e.*,d.dname,d.loc,ms.grade 上级领导等级,es.grade 雇员等级
from emp e,dept d,emp m,salgrade ms,salgrade es
where e.deptno=d.deptno and e.mgr=m.empno(+) and e.sal between es.losal and es.hisal
and m.sal between ms.losal and ms.hisal and e.sal>(select avg(sal) from emp);
10、列出与“scott”从事相同工作的所有员工及部门名称
select job from emp where ename=upper('scott');
select e.*,d.dname
from emp e,dept d
where e.deptno=d.deptno and ename!=upper('scott') and job=(select job from emp where ename=upper('scott'));
11、列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金
select sal from emp where deptno=30;
select ename,sal
from emp where sal in (select sal from emp where deptno=30) and deptno!=30;
12、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金，部门名称
select e.ename,e.sal,d.dname
from emp e,dept d
where e.deptno=d.deptno and e.sal>all(select sal from emp where deptno=30);
13、列出在每个部门工作的员工数量、平均工资和平均
服务期限
select deptno,count(*),round(avg(sal),2),trunc(avg(months_between(sysdate,hiredate)/12))
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 deptno,count(*) cou from emp group by deptno;
select d.*,temp.cou
from (select deptno,count(*) cou from emp group by deptno) temp,dept d
where temp.deptno(+)=d.deptno;
16、列出各种工作的最低工资以及从事此工作的雇员姓名
select job,min(sal) from emp group by job;
select ename,sal from emp where (job,sal) in(select job,min(sal) from emp group by job);
17、列出各个部门的经理的最低薪金
select deptno,min(sal)
from emp
where job='MANAGER'
group by deptno;
18、列出所有员工的年工资，按年薪从低到高排序
select (nvl(comm,0)+sal)*12 income from emp order by income;
19、查出某个员工的上级主管，并要求出这些主管中的薪水超过3000
select distinct m.*
from emp e,emp m
where e.mgr=m.empno and m.sal>3000;
20、求出部门名称中，带'S'字符的部门员工的工资总和 、部门人数
select deptno from dept where dname like '%S%';
select sum(sal),count(*)
from emp
where deptno in (select deptno from dept where dname like '%S%')
group by deptno;