动力节点MySQL34题精炼（带答案解析）（下）

24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.
select  a.ename,a.sal
from emp a
left join (SELECT DISTINCT sal from emp where deptno=30) b
ON a.sal=b.sal
WHERE deptno<>30

25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称
先找到部门30的最高薪金：SELECT max(sal) from emp where deptno='30'

SELECT a.ename,a.sal,b.dname
from emp a
left join dept b 
on a.deptno=b.deptno
where a.sal>(SELECT max(sal) from emp where deptno='30')

26、列出在每个部门工作的员工数量, 平均工资和平均服务期限
SELECT count(*) from emp group by deptno

SELECT b.dname,count(a.ename) ecount,ifnull(avg(a.sal),0) as avgsal,ifnull(avg(TIMESTAMPDIFF(year,hiredate,NOW())),0) as avgservicetime
from emp a
RIGHT JOIN dept b
on a.deptno=b.deptno
GROUP BY a.deptno

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

SELECT a.ename,b.dname,a.sal
from emp a
LEFT JOIN dept b
on a.deptno=b.deptno

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

连接两表用关键字on，不要用where，否则会报错

SELECT a.deptno,a.dname,a.loc,count(b.ename)
from dept a
LEFT JOIN emp b
on a.deptno=b.deptno
GROUP BY a.deptno,a.dname,a.loc

29、列出各种工作的最低工资及从事此工作的雇员姓名
先写子句：
SELECT job,min(sal)
from emp
GROUP BY job
再写总句：
SELECT e.ename,t.*
from emp e
join (SELECT job,min(sal) as minsal
from emp
GROUP BY job) t
on e.sal=t.minsal

30、列出各个部门的 MANAGER( 领导) 的最低薪金
注意观察,不要想得太复杂了。manager在这是一种职业
SELECT deptno,min(sal)
FROM emp
where job="manager"
GROUP BY deptno

31、列出所有员工的 年工资, 按 年薪从低到高排序
sum(sal*12) as yearsal
ORDER by yearsal

SELECT ename,(sal+ifnull(comm,0))*12 as yearsal
from emp
ORDER by yearsal asc
IFNULL 函数是 MySQL 控制流函数之一。
它有两个参数，第一个参数不为 NULL 时返回。 否则，IFNULL 函数返回第二个参数。
这两个参数可以是文字值或表达式。
 

32、求出员工领导的薪水超过3000的员工名称与领导
薪水超过3000的
自连接
SELECT a.ename,b.ename
from emp a
join emp b
on a.mgr=b.empno
where b.sal>3000

33、求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数
部门名称中, 带'S'字符的部门
select d.deptno,d.dname,d.loc,ifnull(sum(e.sal),0) as sumsal,count(e.ename) as count
from emp e
right join dept d
on e.deptno=d.deptno
where d.dname like '%S%'
GROUP by d.deptno,d.dname,d.loc

34、给任职日期超过 30 年的员工加薪 10%.
update emp set sal=sal*1.1
where TIMESTAMPDIFF(year,hiredate,NOW()) >30