MySQL练习题（四）

26、列出在每个部门工作的员工数量, 平均工资和平均服务期限(*)

select 
    d.deptno,count(e.ename) ,avg(e.sal),avg(timestampdiff(YEAR,e.hiredate,now()))
from 
    emp e
right join
    dept d
on 
    e.deptno = d.deptno
group by
    d.deptno;

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

select 
    e.ename,d.dname,e.sal
from 
    emp e
join 
    dept d
on
    e.deptno = d.deptno;

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

select 
    *,count(ename)
from
    emp e
right join
    dept d
on
    e.deptno = d.deptno
group by
    d.deptno;

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

（1）查找最低工资的工作

select 
    job,min(sal)
from 
    emp
group by
    job;

（2）将emp表与这个表进行外连接

select 
    e.ename,e.job,e.sal
from
    emp e
join(
    select 
        job,min(sal) minsal
    from 
        emp
    group by
        job
    ) t
on
    e.job = t.job and e.sal = t.minsal;

30、列出各个部门的 MANAGER( 领导) 的最低薪金

select
    deptno,min(sal)
from
    emp
where
    job = 'MANAGER'
group by
    deptno;

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

select 
    ename,(sal  + ifnull(comm,0))* 12 yearsal
from
    emp
order by
    yearsal;

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

（1）求出员工领导的薪水超过3000的

select 
    a.ename '员工',a.sal '员工薪水',b.ename '领导',b.sal '领导工资'
from    
    emp a
join 
    emp b
on
    a.mgr = b.empno
where
    b.sal > 3000;

33、求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数（*）

select 
    d.deptno,sum(ifnull(sal,0)) '工资合计',count(ename) '部门人数'
from
    emp e
right join
    dept d
on
    e.deptno = d.deptno
where
    d.dname like '%S%'
group by
    e.deptno;

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

select 
    ename,timestampdiff(YEAR,hiredate,now()) '任职日期'
from
    emp
where
    timestampdiff(YEAR,hiredate,now()) > 30;

create table emp2 as select * from emp;

update 
    emp2 
set 
    sal = sal*1.1 
where 
    timestampdiff(YEAR,hiredate,now()) > 30;