多表查询的练习题

?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.hiredate5、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
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;