mysql 12: 34道作业题

34道作业题(有时间就写几道)

1. 每个部门最高薪水的人员名单

• step1：每个部门最高薪水

  select deptno, max(sal) as maxsal from emp group by deptno;
  

• step2：将上述结果作为临时表t，t表和emp e表进行连接，条件是：t.deptno = e.deptno and t.maxsal = e.sal;

  select
  	e.ename, t.*
  from
  	(select deptno, max(sal) as maxsal from emp group by deptno) t
  join
  	emp e
  on
  	t.deptno = e.deptno and t.maxsal = e.sal;
  

2. 哪些人的薪水在部门的平均薪水之上

• step1：

  select deptno, avg(sal) as avgsal from emp group by deptno;
  

• step2：将上述结果作为临时表t，t表和emp e表进行连接，条件是：t.deptno = e.deptno and e.sal > t.avgsal;

  select
  	e.ename, t.*
  from
  	(select deptno, avg(sal) as avgsal from emp group by deptno) t
  join
  	emp e
  on
  	e.deptno = t.deptno and e.sal > t.avgsal;
  

3. 部门中(所有人的)平均的薪水等级

• 平均的薪水等级

  • step1：找出每个人的薪水等级

    select 
    	e.ename, e.sal, e.deptno, s.grade
    from
    	dept e
    join
    	salgrade s
    on 
    	e.sal between s.losal and s.hisal;
    

  • step2：根据部门分组，求每个部门薪水等级的平均值

    select 
    	e.deptno, avg(s.grade)
    from
    	dept e
    join
    	salgrade s
    on 
    	e.sal between s.losal and s.hisal
    group by
    	e.deptno;
    

4. 不准用组函数 (Max), 取得最高薪水, (两种方法)

• 方法1：desc, limit 1

  select ename, sal from emp order by sal desc limit 1; 
  

• 方法2：表的自连接

  select sal from emp where sal not in (
  select
  	distinct a.sal
  from
  	emp a
  join
  	emp b
  on a.sal < b.sal);
  

5. 平均薪水最高的部门编号

• 方法1：

  step1: select deptno, avg(sal) avgsal from emp group by deptno;		//求平均值
  

  step2: select deptno, avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1;
  

• 方法2：

  step1: select deptno, avg(sal) avgsal from emp group by deptno;		//求平均值
  

  step2: select max(avgsal) from (select deptno, avg(sal) avgsal from emp group by deptno);		//求最大值
  

  step3:
  select
  	deptno, avg(sal) as avgsal
  from
  	emp e
  group by
  	deptno
  having
  	avgsal = select max(t.avgsal) from (select deptno, avg(sal) avgsal from emp group by deptno t);
  

6. 平均薪水最高的部门的部门名称

select 
	d.dname, avg(e.sal) avgsal 
from 
	emp e
join
	dept d
on
	e.deptno = d.deptno
group by 
	d.dname
order by 
	avgsal desc 
limit 
	1;

7. 平均薪水的等级最低的部门的部门名称

• step1：找出每个部门平均薪水的等级

  select
  	t.*, s.grade
  from
  	(
          select 
          	d.dname, avg(e.sal) avgsal 
       	from 
          	emp e
          join
          	dept d
          on
          	e.deptno = d.deptno
       	group by 
          	d.dname
      ) 
      
      t
  join
  	salgrade s
  on
  	t.avgsal between s.losal and s.hisal;
  

• step2：找出最低平均工资

  select
  	avg(e.sal) avgsal
  from
  	emp e
  group by
  	e.deptno
  order by
  	avgsal asc
  limit 
  	1;
  

• step3：找出最低平均工资对应的等级，相当于找出最低等级

  select
  	grade
  from
  	salgrade
  where
  	(select
  	avg(e.sal) avgsal
  from
  	emp e
  group by
  	e.deptno
  order by
  	avgsal asc
  limit 
  	1) between losal and hisal;
  

• step4：将step3作为过滤条件追加到step1的后面，从step1中过滤出最低等级

  select
  	t.*, s.grade
  from
  	(
          select 
          	d.dname, avg(e.sal) avgsal 
       	from 
          	emp e
          join
          	dept d
          on
          	e.deptno = d.deptno
       	group by 
          	d.dname
      ) 
      
      t
  join
  	salgrade s
  on
  	t.avgsal between s.losal and s.hisal
  where
  	s.grade = (
  		select
  			grade
  		from
  			salgrade
  		where
  			(select
  				avg(e.sal) avgsal
  			 from
  				emp e
  			group by
  				e.deptno
  			order by
  				avgsal asc
  			limit 
  				1) 
          between losal and hisal
  );