28、列出所有部门的详细信息和人数
我的解法:为什么外连接,deptno=40的人数为null?
select
d.deptno, d.dname, d.loc, count(e.ename)
from
emp e
right JOIN
dept d
on
e. deptno=d.DEPTNO
group by
d.deptno, d.dname ,d.loc;
29、列出各种工作的最低工资及从事此工作的雇员姓名
-- 29、列出各种工作的最低工资及从事此工作的雇员姓名
select
job,min(sal) as minsal
from
emp
GROUP BY
job;
-- e 表与上面的的t秒连接。
select e.ENAME ,e.sal
from
emp e
join
(select
job,min(sal) as minsal
from
emp
GROUP BY
job) t
on
e.sal =t.minsal and e.job=t.job;
30、列出各个部门的 MANAGER( 领导) 的最低薪金
select
min(sal),e.deptno
from
emp e
where
job='MANAGER'
group by
deptno ;
31、列出所有员工的 年工资, 按 年薪从低到高排序
SELECT
e.ename, 12*e.sal as yearsal
from
emp e
order by
yearsal asc;
32、求出员工领导的薪水超过3000的员工名称与领导
select
a.ename 'employee' , b.ename 'leader'
from
emp a
join
emp b
on
a.mgr=b.empno
where
b.sal>3000;
33、求出部门名称中, 带’S’字符的部门员工的工资合计、部门人数
select
d. deptno, d.dname,d.loc,ifnull(count(e.deptno),0) as count ,ifnull(sum(e.sal), 0) as sumsal
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())>40;