【MySQL】MySQL习题（25-37）

问题25：列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，雇员的薪资等级

mysql>select a.ename empname,b.ename leadername,d.dname,s.grade 
 from emp a
 join emp b on a.mgr=b.empno 
 join dept d on a.deptno=d.deptno 
 join salgrade s on a.sal between s.losal and s.hisal 
 where a.sal>(select avg(sal) from emp);

所以代码修改为：

mysql>select a.ename empname,b.ename leadername,d.dname,s.grade 
 from emp a
 left join emp b on a.mgr=b.empno 
 join dept d on a.deptno=d.deptno 
 join salgrade s on a.sal between s.losal and s.hisal 
 where a.sal>(select avg(sal) from emp);

问题26：列出与"SCOTT"从事相同工作的所有员工及部门名称

mysql> select job from emp where ename="SCOTT";
mysql> select ename from emp where job =(select job from emp where ename="SCOTT");
mysql> 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");

排除SCOTT本人：

mysql> 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 e.ename!="SCOTT";

问题27：列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金

妈的，这句话是在考语文吗？

第一步：薪金等于部门30中员工的薪金
mysql> select distinct sal from emp where deptno =30;

第二步：
列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金
不在部门30中与部门30的薪金相同的员工姓名和薪金
mysql> select ename,sal from emp 
where sal in( select distinct sal from emp where deptno=30) and deptno<>30;

问题28：列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金，部门名称

mysql> select max(sal) maxsal from emp where deptno=30;
mysql>select e.ename,e.sal,d.dname from emp e 
join dept d on e.deptno=d.deptno 
where e.sal>( select max(sal) maxsal from emp where deptno=30) and e.deptno<>30;

问题29：列出每个部门工作的员工数量，平均工资和平均服务期限

第一步：将员工的emp表和部门表dept进行表连接，显示全部信息

第二步：在以上查询结果的基础上，按照d.deptno分组，按照e.ename计数
列出每个部门工作的员工数量，平均工资
mysql> select d.deptno,count(e.ename) countname,ifnull(avg(e.sal),0) avgsal
    -> from emp e right join dept d
    -> on e.deptno=d.deptno
    -> group by d.deptno;

第三步：计算每个员工的平均服务期限
mysql> select to_days(now());
mysql> select to_days(hiredate) from emp;
mysql> select (to_days(now())-to_days(hiredate))/365 from emp;

第四步：在第二步的基础上
列出每个部门工作的员工数量，平均工资和平均服务期限
mysql> select
    -> d.deptno,
    -> count(e.ename) countemp,
    -> ifnull(avg(e.sal),0) avgsal,
    -> ifnull( avg( (to_days(now())-to_days(hiredate))/365),0) avgtime
    -> from emp e
    -> right join dept d
    -> on e.deptno=d.deptno
    -> group by d.deptno;

问题30：列出所有员工的姓名，部门名称和工资

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

问题31：列出所有部门的详细信息和人数

将emp表与dept表右连接：
mysql> select e.*,d.* from emp e right join dept d on e.deptno=d.deptno;

l列出所有部门的详细信息和人数：
mysql> select d.*,count(e.ename) 
from  emp e right join dept d 
on e.deptno=d.deptno group by d.deptno;

问题32：列出各种工作的最低工资以及从事此工作的雇员姓名

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

问题33：列出各个部门’‘MANAGER’'的最低薪金

mysql> select deptno,min(sal) minsal from emp  where job="MANAGER" group by deptno;

问题34：列出所有员工的年工资，按照年薪从低到高排

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

问题35：求员工领导的薪水超过3000的员工名称和领导名称

mysql> select a.ename empname,b.ename leadername
    -> from emp a join emp b on a.mgr= b.empno
    -> where b.sal>3000;

问题36：求出部门名称中，带’s’字符的部门员工的工资合计，部门人数

mysql> select
    ->  d.dname,
    ->  ifnull(sum(e.sal),0) sumsal,
    ->  count(e.ename) countemp
    ->  from emp e
    ->  right join dept d
    ->  on e.deptno=d.deptno
    ->  where d.dname like '%s%'
    ->  group by d.deptno;

问题37：给任职员工日期超过30年的员工加薪10%