这是我参与11月更文挑战的第16天，活动详情查看：2021最后一次更文挑战
5.21 —5 .25
【习题21】列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号
select e.ename from emp e join dept d on d.dname = 'SALES' and e.deptno = d.deptno ;
【习题22】列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级
select e.ename '姓名', d.dname '部门名称', ifnull(m.ename , '无') '上级领导', s.grade '工资等级' from emp e join dept d on e.deptno = d.deptno left join emp m on e.mgr = m.empno join salgrade s on e.sal between s.losal and s.hisal where e.sal > (select avg(sal) avgsal from emp) ;
【习题23】列出与"SCOTT"从事相同工作的所有员工及部门名称.
select e.ename , d.dname from emp e join dept d on e.deptno = d.deptno where job = (select job from emp where ename = 'SCOTT') and ename != 'SCOTT';
【习题24】列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金
select ename , sal from emp where sal in (select sal from emp where deptno = 30) and deptno != 30 ;
【习题25】列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金.部门名称.
select e.ename , e.sal , d.dname from emp e join dept d on d.deptno = e.deptno where e.sal > (select max(sal) from emp where deptno = 30) ;
5.26 — 5.30
【习题26】列出在每个部门工作的员工数量,平均工资和平均服务期限.
// 服务期限：从入职到现在服务了多长时间
select d.dname, count(*) , avg(sal) avgsal , avg(now() - e.hiredate ) avgser from emp e join dept d on e.deptno = d.deptno group by e.deptno order by e.deptno asc;
【习题27】出所有员工的姓名、部门名称和工资。
select e.ename , d.dname , e.sal from emp e join dept d on e.deptno = d.deptno ;
【习题28】列出所有部门的详细信息和人数
select d.deptno , d.dname ,d.loc, ifnull(c.cc , '0') '人数' from dept d left join (select deptno , count(*) cc from emp group by deptno) as c on d.deptno = c.deptno ;
【习题29】列出各种工作的最低工资及从事此工作的雇员姓名
select e.empno , e.ename , e.job , e.mgr , e.hiredate , e.sal , e.comm , e.deptno from emp e join (select min(sal) minsal from emp group by job ) as m where e.sal = m.minsal;
【习题30】列出各个部门的 MANAGER(领导)的最低薪金
select deptno , min(sal) from emp where job = 'manager' group by deptno order by deptno asc ;
5.31 — 5.34
【习题31】列出所有员工的年工资,按年薪从低到高排序
select ename , sal*12 as income from emp order by income asc ;
【习题32】求出员工领导的薪水超过 3000 的员工名称与领导名称
select e.ename , m.ename from emp e join emp m on m.sal > 3000 where e.mgr = m.empno;
【习题33】求出部门名称中,带'S'字符的部门员工的工资合计、部门人数
select d.dname , ifnull(sum(sal), '0') as sumsal, count(e.ename) from emp e right join dept d on d.deptno = e.deptno where d.dname like '%S%' group by d.deptno , d.dname ;
【习题34】给任职日期超过 30 年的员工加薪 10%
start transaction ;
update emp set sal = sal*1.1 where timestampdiff(YEAR , hiredate , now()) > 30 ;
rollback ;
\
