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