MySQL把数量平均n人_MySQL-34道题（3）

26、列出每个部门的员工数量、平均工资、会让服务期限

第一步：将员工表emp e 和部门表dept d进行连接，将部门表的数据全部显示

select d* ,e*

from emp e right join dept d on d.deptno=e.deptno

第二步：以上查询结果的基础上按照d-deptno分组，按照e.ename计数。

select d.deptno ,count(e.ename)

from emp e right join dept d

on d.deptno=e.deptno

group by d.deptno

第三步：以上基础上计算平均工资( 函数ifnull如果是null的话就计数0)

select d.deptno ,count(e.ename) as tatleEmp，ifnull (avg(e.sal),0) as avgsal

from emp e right join dept d

on d.deptno=e.deptno

group by d.deptno

第四步：再以上的基础上基础计算服务期限

一个函数to_days(now())把日期转换成天

select d.deptno ,count(e.ename) as tatleEmp，ifnull (avg(e.sal),0) as avgsal， ifnull(avg((to_days(now())-to_days(hiredate))/365),0) as avgtime

from emp e right join dept d

on d.deptno=e.deptno

group by d.deptno

27、列出所有员工姓名、部门名称和工资

select e.ename,e.sal d.dname

from emp e

join dept d

on e.deptno=d.deptno

28、列齿所有部门的详细信息和人数

select d.deptno,d.dname,d.loc,count(e.ename)

from emp e

right join dept d

on d.deptno=e.deptno

group by d.deptno,d.dname,d.loc,

29、列出各种工作的最低工资及从事该工作的雇员姓名

select min(sal) as minsal ,job

from emp

group by job;

select t*,e.ename

from emp e

join (select min(sal) as minsal ,job

from emp

group by job)t

on t.job=e.job and t.minsal=e.sal

30、列出ge各个部门的MANAGER的最低薪金

select deptno,min(sla)

from emp

where job =’MANAGER’

group by deptno;

31、列出所有员工的年工资，按年薪从低到高排序

select ename,((sal+ifnull(comm,0))*12) as yearsal

from emp

order by yearsal desc;

32、求出员工领导的薪水超过3000的员工名称与领导米名称

select e.ename as empname, e.sal,b.ename leadern,b.sal

from emp e

join dept d

on a.mgr=b.empno

where b.sal>3000

33、求出部门名称中，带“S“字符的部门员工的工资合计、部门人数

select d.dname,ifnull(sum(e.sal)),0) as sumsal ,count(e.ename)as totalEmp

from emp e

right join

dept d

on e.deptno=d.deptno

where d.dname like ‘%S%’

group by d.dname;

34、给任职日期超过30年的员工加薪10%

create table emp_bal as select* from emp

update emp_bak set sal=sal*1.1

where (to_days(now())-todays(hiredate))/365>30