几个sql语句例子

--【1】查询出每个部门的编号、名称、位置、部门人数、平均工资
---多字段分组统计
1400*400=560000
select d.deptno,dname,loc,count(empno),avg(sal)
from emp e ,dept d
where e.deptno(+)=d.deptno
group by d.deptno,dname,loc;
---统计操作都放在子查询中
【1】数据源
dept  temp
---子查询
dept----400
emp----1400
---子查询操作的数据量是1400
300*400=120000
120000+1400=121400
select d.deptno,dname,loc,temp.c,temp.a
from dept d,(select deptno,count(empno) c,avg(sal) a from emp group by deptno) temp
where d.deptno=temp.deptno(+);

--【2】查询出所有在部门SALES(销售部)工作的员工的编号、姓名、基本工资、奖金、职位、雇佣日期、部门的最高和最低工资
【1】先确定数据源
dept:销售部的名称，部门编号
emp:工的编号、姓名、基本工资、奖金、职位、雇佣日期
emp:部门的最高和最低工资
【2】
先写子查询
select deptno,min(sal),max(sal) from emp group by deptno;
【3】查询的基本框架
select empno,ename,sal,comm,job,hiredate,统计信息
from emp,()temp
where emp.deptno=(销售部的部门编号)
and
emp.deptno=temp.deptno
;
【4】
select empno,ename,sal,comm,job,hiredate,temp.min,temp.max
from emp,(select deptno,min(sal) min,max(sal) max from emp group by deptno)temp
where emp.deptno=(select deptno from dept where dname='SALES')
and
emp.deptno=temp.deptno
;
--【3】查询出所有薪金高于公司平均薪金的员工的编号、姓名、基本工资、职位、雇佣日期，
--所在部门名称、位置，上级领导姓名，公司的工资等级，部门人数、平均工资、平均服务年限
【1】先确定数据源
emp：员工的编号、姓名、基本工资、职位、雇佣日期---e
dept:部门名称、位置
emp:上级领导姓名----m
salgrade:工资等级---s
emp:deptno 部门人数、平均工资、平均服务年限--temp
【2】确定已知的关联字段
e.deptno=dept.deptno
e.empno=m.mgr
e.sal between s.losal and s.hisal
temp.deptno=e.deptno
【3】
先写子查询
select deptno,count(empno),avg(sal),avg(months_between(sysdate,hiredate)/12) from emp group by deptno;
【4】查询的基本框架
select e.empno,e.ename,e.sal,e.job,e.hiredate,dname,loc,m.ename,s.grade,统计信息
from emp e,dept d,emp m,salgrade s,()
where e.sal>(select avg(sal) from emp)
and
and
and
and
【5】
select e.empno,e.ename,e.sal,e.job,e.hiredate,dname,loc,m.ename,s.grade,temp.c,temp.a,temp.ann
from emp e,dept d,emp m,salgrade s,
(select deptno,count(empno) c,avg(sal) a,avg(months_between(sysdate,hiredate)/12) ann
from emp group by deptno
 )temp
where e.sal>(select avg(sal) from emp)
and e.deptno=d.deptno
and e.mgr=m.empno(+)
and e.sal between s.losal and s.hisal
and temp.deptno=e.deptno;
--【4】列出薪金比ALLEN或CLERK多的所有员工的编号、姓名、基本工资、部门名称、其领导姓名，部门人数
【1】先确定数据源
emp:编号、姓名、基本工资
dept:部门名称
emp:领导姓名
emp:部门人数----temp

【2】确定已知的关联字段
e.deptno=dept.deptno
e.mgr=m.empno
e.deptno=temp.deptno
【3】
先写子查询
select deptno,count(empno) from emp group by deptno;----temp
select sal from emp where ename in('ALLEN','CLARK');

【4】
select e.empno,e.ename,e.sal,dname,m.ename,temp.c
from emp e,dept d,emp m,(select deptno,count(empno) c from emp group by deptno) temp
where e.sal>ANY(select sal from emp where ename in('ALLEN','CLARK'))
and e.deptno=d.deptno
and e.mgr=m.empno(+)
and e.deptno=temp.deptno
and e.ename not in('ALLEN','CLARK');



--【5】列出公司各个部门的经理（假设每个部门只有一个经理，job为MANAGER）的姓名、薪金、
--部门名称、部门人数、部门平均工资
【1】先确定数据源
emp:经理（假设每个部门只有一个经理，job为MANAGER）的姓名、薪金
dept:部门名称
emp:部门人数、部门平均工资
【2】确定已知的关联字段
emp.deptno=dept.deptno
temp.deptno=emp.deptno
【3】
select ename,sal,dname,temp.c,temp.a
from emp e,dept d,(
     select deptno,count(empno) C,avg(sal) A from emp group by deptno
) temp
where job='MANAGER'
AND
e.deptno=d.deptno
AND
temp.deptno=e.deptno;