博客

1. 求每个部门的平均工资

select d.deptno, d.dname, ep.sal
from dept d, (select avg(e.sal) sal, e.deptno
              from emp e group by e.deptno) as ep
where d.deptno = ep.deptno

2. 分组统计各部门下工资>500的员工的平均工资

select d.deptno, d.dname, ep.sal
from dept d, (select avg(e.sal) sal, e.deptno
              from emp e
              where e.sal > 500
              group by e.deptno) as ep
where d.deptno = ep.deptno

3. 统计各部门下平均工资大于500的部门

select d.deptno, d.dname, ep.sal
from dept d, (select avg(e.sal) sal, e.deptno
              from emp e
              group by e.deptno) as ep
where d.deptno = ep.deptno and ep.sal > 500

4. 算出部门30中得到最多奖金的员工奖金

# 先查询出部门30中的员工，并按照奖金降序排列, 再限制只查询一条
select e.*
from emp e
where e.deptno = 30
order by comm desc
limit 1;

5. 算出每个职位的员工数和最低工资

select e.job, count(*), max(e.sal)
from emp e
group by e.job;

6. 列出员工表中每个部门的员工数，和部门编号

select e.deptno, count(*)
from emp e
group by e.deptno;

7. 得到工资大于自己部门平均工资的员工信息

select e1.* from emp e1,
              (select deptno,avg(sal) as avgsal from emp group by deptno) e2
where
        e1.deptno=e2.deptno and e1.sal > e2.avgsal;

8. 分组统计每个部门下，每种职位的平均奖金（也要算没奖金的人）和总工资(包括奖金)

select deptno,job,avg(nullif(comm,0)),sum(sal+nullif (comm,0))
from emp
group by deptno,job;

9. 列出员工表中每个部门的员工数（员工数必须大于3），和部门名称

select d.deptno, d.dname, ep.num
from dept d, (select e.deptno, count(*) num
              from emp e
              group by e.deptno) as ep
where d.deptno = ep.deptno and ep.num > 3;

10. 找出工资比jones多的员工

select emp.* from emp, (select sal from emp where ename like 'JONES') ep
where emp.sal > ep.sal;

# 参考答案

#--求每个部门的平均工资
select avg(sal) from emp group by deptno;
#--分组统计各部门下工资>500的员工的平均工资
select avg(sal) from emp where sal>500 group by deptno;
#--统计各部门下平均工资大于500的部门
select * from (select deptno , avg(sal) aa from emp group by deptno ) tt where tt.aa>500;
select deptno , avg(sal) aa from emp group by deptno having avg(sal)>500;
#--算出部门30中得到最多奖金的员工奖金
select * from emp where comm=(select max(comm) mm from emp where   deptno=30 ) ;
#--算出每个职位的员工数和最低工资
select count(empno),min(sal) from emp group by job ;
#--列出员工表中每个部门的员工数，和部门编号
select count(empno) ,deptno from emp group by deptno;
#--得到工资大于自己部门平均工资的员工信息
select *
from emp e, (select deptno, avg(sal) aa from emp group by deptno) tt
where e.deptno = tt.deptno
  and e.sal > tt.aa;
#--8分组统计每个部门下，每种职位的平均奖金（也要算没奖金的人）和总工资(包括奖金)
select deptno, job, avg(nullif(comm, 0)), sum(sal)
from emp
group by deptno, job
order by deptno;
#--列出员工表中每个部门的员工数（员工数必须大于3），和部门名称
select d.dname, tt.*
from (select count(e.empno), e.deptno
      from emp e
      group by e.deptno
      having count(e.empno) > 3) tt,
     dept d
where tt.deptno = d.deptno;
#--找出工资比jones多的员工
select *
from emp
where sal > (select sal from emp where ename = upper('jones'));