1.3 练习题 ( 20-34 )

20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数

select job,count(*) from emp group by job having min(sal)>1500; 正确的分组统计过滤

+-----------+----------+
| job | count(*) |
+-----------+----------+
| 分析师 | 2 |
| 总裁 | 1 |
| 经理 | 3 |
+-----------+----------+

select count(*),job from emp where sal>1500 group by job; //这样写错误的，错误的原因在下面

+----------+-----------+
| count(*) | job |
+----------+-----------+
| 2 | 分析师 |
| 1 | 总裁 |
| 1 | 推销员 |
| 3 | 经理 |
+----------+-----------+

---------------------------------------------------------------------------

错误的原因：

select job,sal from emp order by sal;

+-----------+------+
| job | sal |
+-----------+------+
| 办事员 | 800 |
| 办事员 | 950 |
| 办事员 | 1100 |
| 推销员 | 1250 |
| 推销员 | 1250 |
| 办事员 | 1300 |
| 推销员 | 1500 |
| 推销员 | 1600 | 这一条虽然大于1500，但是和1500是同一个工作所以不能统计
| 经理 | 2450 |
| 经理 | 2850 |
| 经理 | 2975 |
| 分析师 | 3000 |
| 分析师 | 3000 |
| 总裁 | 5000 |
+-----------+------+
14 rows in set (0.00 sec)

select count(*),job from emp where sal>1500 group by job;

+----------+-----------+
| count(*) | job |
+----------+-----------+
| 2 | 分析师 |
| 1 | 总裁 |
| 1 | 推销员 |
| 3 | 经理 |
+----------+-----------+

21、列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号.

1 不知道编号，先查出'SALES'的部门编号

select deptno from dept where dname = 'SALES';
+--------+
| deptno |
+--------+
| 30 |
+--------+

select ename from emp where deptno = (select deptno from dept where dname = 'SALES');

22、列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级.

公司平均工资

select avg(sal) from emp;
+-----------+
| avg(sal) |
+-----------+
| 2073.2143 |
+-----------+

员工表 e 领导表 c 部门表 d 工资等级 s

select

e.ename as '姓名',d.dname as '所在部门',c.ename as '上级领导',s.grade as '工资等级'

from emp e

left join emp c//这里不用left的话 king就漏掉了

on e.mgr = c.empno

join dept d

on e.deptno = d.deptno

join salgrade s

on e.sal between s.losal and hisal

where e.sal>(select avg(sal) from emp);

+-------+------------+-------+-------+

// 没有加 left

23、列出与"SCOTT" 从事相同工作的所有员工及部门名称

1 找出 SCOTT 的工作

select job from emp where ename = 'SCOTT';
+-----------+
| job |
+-----------+
| 分析师 |
+-----------+

select

e.ename,e.job,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';

+-------+-------+----------+
| ename | job | dname |
+-------+-------+----------+
| FORD | 分析师 | RESEARCH |
+-------+-------+----------+

24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金.

1 找出 30 部门所有员工的薪金

select distinct sal from emp where deptno = 30;
+------+
| sal |
+------+
| 1600 |
| 1250 |
| 2850 |
| 1500 |
| 950 |
+------+

select

ename,sal

from emp

where sal in(select distinct sal from emp where deptno = 30)

and deptno <> 30;//这个条件一定要加上

25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称

1 找出 30 部门的最高薪水

select distinct max(sal) as maxsal from emp where deptno = 30;
+--------+
| maxsal |
+--------+
| 2850 |
+--------+

select

e.ename as '员工姓名',e.sal as '薪金',d.dname as '部门名称'

from emp e

join dept d

on e.deptno = d.deptno

where sal>(select distinct max(sal) as maxsal from emp where deptno = 30);

+--------------+-------------+
| 员工姓名 | 薪金 | 部门名称 |
+--------------+-------------+
| JONES | 2975 | RESEARCH |
| SCOTT | 3000 | RESEARCH |
| KING | 5000 | ACCOUNTING |
| FORD | 3000 | RESEARCH |
+--------------+-------------+

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

没有员工的部门应该为0
平均服务期限：服务期限的平均值 avg(服务期限 = 系统当前年份 - 入职的年份)

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

+--------+----------------+----------------------+------------+
| deptno | count(e.ename) | ifnull(avg(e.sal),0) | difference |
+--------+----------------+----------------------+------------+
| 10 | 3 | 2916.6667 | 38.6667 |
| 20 | 5 | 2175.0000 | 36.4000 |
| 30 | 6 | 1566.6667 | 38.8333 |
| 40 | 0 | 0.0000 | 0.0000 |
+--------+----------------+----------------------+------------+


时间差值函数
mysql中计算两个日期的年差
timestampdiff(类型,前一个日期,后一个日期);

select timestampdiff(year,hiredate,now())as difference from emp;
+------------+
| difference |
+------------+
| 39 |
| 39 |
| 39 |
| 39 |
| 39 |
| 39 |
| 39 |
| 33 |
| 39 |
| 39 |
| 33 |
| 38 |
| 38 |
| 38 |
+------------+

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

select
e.ename as '姓名',e.sal as '工资',d.dname as '部门名称'
from emp e
join dept d
on e.deptno = d.deptno;

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

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;

+--------+------------+-----------+----------------+
| deptno | dname | loc | count(e.ename) |
+--------+------------+-----------+----------------+
| 10 | ACCOUNTING | 纽约 | 3 |
| 20 | RESEARCH | 达拉斯 | 5 |
| 30 | SALES | 芝加哥 | 6 |
| 40 | OPERATIONS | 波士顿 | 0 |
+--------+------------+-----------+----------------+

//没有加right 漏掉了一条

+--------+------------+-----------+
| deptno | dname | loc | count(*) |
+--------+------------+-----------+
| 10 | ACCOUNTING | 纽约 | 3 |
| 20 | RESEARCH | 达拉斯 | 5 |
| 30 | SALES | 芝加哥 | 6 |
+--------+------------+-----------+

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

1 找出各岗位的最低薪资

select job,min(sal)as minsal from emp group by job;
+-----------+--------+
| job | minsal |
+-----------+--------+
| 分析师 | 3000 |
| 办事员 | 800 |
| 总裁 | 5000 |
| 推销员 | 1250 |
| 经理 | 2450 |
+-----------+--------+

2 把查询结果当成临时表 t

select

e.ename,t.*

from (select job,min(sal)as minsal from emp group by job)as t

join emp e

on t.job = e.job and t.minsal = e.sal;

+--------+-----------+--------+
| ename | job | minsal |
+--------+-----------+--------+
| SMITH | 办事员 | 800 |
| WARD | 推销员 | 1250 |
| MARTIN | 推销员 | 1250 |
| CLARK | 经理 | 2450 |
| SCOTT | 分析师 | 3000 |
| KING | 总裁 | 5000 |
| FORD | 分析师 | 3000 |
+--------+-----------+--------+

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

select min(sal),deptno from emp where job = '经理' group by deptno;//太简单了

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

select ename,(sal+ifnull(comm,0))*12 as yearsal from emp order by yearsal ASC;//考察ifnull函数的使用

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

select
e.ename,c.ename
from emp e
join emp c
on e.mgr = c.empno
where c.sal>3000;

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

select dname,deptno from dept where dname like '%S%';
+------------+--------+
| dname | deptno |
+------------+--------+
| RESEARCH | 20 |
| SALES | 30 |
| OPERATIONS | 40 |
+------------+--------+

select

d.deptno,sum(ifnull(e.sal,0)),d.dname,count(e.empno)

from emp e

right join (select dname,deptno from dept where dname like '%S%') as d

on e.deptno = d.deptno

group by d.deptno;

+--------+----------------------+------------+----------------+

| deptno | sum(ifnull(e.sal,0)) | dname | count(e.empno) |
+--------+----------------------+------------+----------------+
| 20 | 10875 | RESEARCH | 5 |
| 30 | 9400 | SALES | 6 |
| 40 | 0 | OPERATIONS | 0 |
+--------+----------------------+------------+----------------+

方法2：

select

d.deptno,sum(ifnull(e.sal,0)),count(e.ename)

from emp e

right join dept d

on e.deptno = d.deptno

where d.dname like '%S%'

group by d.deptno;

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

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

posted @ 2020-12-25 08:00 棉花糖88 阅读(355) 评论(0) 收藏举报

刷新页面返回顶部

棉花糖88

1.3 练习题 ( 20-34 )

公告