oracle复杂查询小练习

习题1：列出至少有一个员工的部门的信息
习题2：列出工资比SMITH高的雇员信息
习题3：列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称，上级的姓名
习题4：列出所有部门名称和这些部门的信息（人数。平均工资、平均服务年限、最高及最低工资），同时列出那些没有员工的部门 months_between(sysdate,hiredate)
习题5：列出所有办事员的姓名及其部门名称，部门的人数
习题6：列出最低工资大于1500的各种工作及此工作的全部雇员人数
习题7：在部门SALES工作的员工的姓名、工资
习题8：列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级与具备此等级的雇员人数（薪金=工资+佣金 ）avg(sal+nvl(comm,0))
习题9：列出与SCOTT从事相同工作的所有员工信息及部门名称(e.* ，d.)省事
习题10：列出工资等于部门30中员工的薪金的所有员工的姓名和薪金、部门名称。
习题11：列出在每个部门工作的员工数量、平均工资和平均服务年限
习题12：列出各种工作的最低工资以及拿这最低工资的雇员姓名
习题13: 列出各个部门的办事员的最低薪金
习题14：列出部门名称中带S字符的部门员工姓名、工资合计、部门人数
答案：
1：select d.dname,d.deptno,temp.count
from dept d,
(select deptno,count(empno) count
from emp
group by deptno
having count(empno)>0) temp
where d.deptno=temp.deptno;
2：
3：select e.empno,e.ename, d.dname,m.ename
from emp e,emp m,dept d
where e.mgr=m.empno(+) and e.hiredate<m.hiredate and e.deptno=d.deptno;
4：select d.deptno,d.dname,d.loc,t.count,t.avg,t.year,t.max,t.min
from dept d,
(select e.deptno,count(e.empno) count,avg(e.sal) avg,
trunc(avg(months_between(sysdate,hiredate)/12)) year,max(e.sal) max,min(e.sal) min
from emp e
group by e.deptno) t
where d.deptno(+)=t.deptno;
上面积小（为了性能尽量用from子函数）或者下面积大
select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal),trunc(avg(months_between(sysdate,hiredate)/12)),max(e.sal),min(e.sal)
from emp e,dept d
where e.deptno=d.deptno(+)
group by d.deptno,d.dname,d.loc;
5：select e.ename,d.dname,t.count
from emp e,dept d,(
select m.deptno,count(empno) count
from emp m
group by m.deptno) t
where e.job=‘CLERK’ and e.deptno=d.deptno and e.deptno=t.deptno;
6：select distinct e.job,t.count
from emp e,(
select job,count(empno) count
from emp
group by job) t
where e.sal>1500 and e.job=t.job;
7：select e.ename,e.sal
from emp e
where deptno=(
select deptno
from dept
where dname=‘SALES’);
8：select e.ename,d.dname,m.ename,s.grade,t.count
from emp e,dept d,emp m,salgrade s,
(select s1.grade grade,count(e1.empno) count
from emp e1,salgrade s1
where e1.sal between s1.losal and s1.hisal
group by s1.grade) t
where e.sal>(
select avg(sal+nvl(comm,0))from emp)
and e.deptno=d.deptno
and e.mgr=m.empno(+)
and e.sal between s.losal and s.hisal
and s.grade=t.grade;
9：select e.,d.*
from emp e,dept d
where job=(
select job
from emp
where ename=‘SCOTT’) and e.ename!=‘SCOTT’ and e.deptno=d.deptno;
10：select e.ename,e.sal+nvl(e.comm,0),d.dname
from emp e,dept d
where e.sal+nvl(e.comm,0)=(
select avg(sal+nvl(comm,0))
from emp
where deptno=30
group by deptno) and e.deptno=d.deptno;
11：select d.dname,t.count,t.avg,t.year
from dept d,(
select deptno,count(empno) count,avg(sal) avg,trunc(avg(months_between(sysdate,hiredate)/12)) year
from emp
group by deptno) t
where d.deptno=t.deptno;
12：select t.job,t.min,e.ename
from emp e,(
select job,min(sal) min
from emp
group by job) t
where e.sal=t.min;
13：select deptno,min(sal)
from emp
where job=‘CLERK’
group by deptno;
14：不用in
select e.ename, t1.sum,t1.count
from emp e,(
select deptno
from dept
where dname like’%S%’
group by deptno) t,(select deptno,sum(sal) sum,count(empno) count from emp group by deptno) t1
where e.deptno=t.deptno and t.deptno=t1.deptno;
用in
select e.ename,t.sum,t.count
from emp e,(
select deptno,sum(sal) sum,count(empno) count
from emp
group by deptno) t
where e.deptno in(
select deptno
from dept
where dname like’%S%’
group by deptno) and e.deptno=t.deptno;