傲云油气装备网

让卖家找上门

发布询价单>>

微信扫一扫

傲云油气装备网二维码

热门分类
首页 > 资讯中心 > 物联网 > 物联网技术文章

MySQL作业题

   日期：2020-08-07     浏览：102    评论：0    
核心提示：34道MySQL作业题

MySQL作业题

      • 0 创建测试数据库
      • 1 取得每个部门最高薪水的人员名称
      • 2 哪些人的薪水在部门的平均薪水之上
      • 3 取得部门中（所有人的）平均的薪水等级
      • 4 不准用组函数（Max），求得最高薪水
      • 5 取平均薪水最高的部门的部门编号
      • 6 取得平均薪水最高的部门的部门名称
      • 7 求平均薪水的等级最低的部门的部门名称
      • 8 取得比普通员工（员工代码没有在mgr字段上出现）的最高薪水还要高的领导人的姓名
      • 9 取得薪水最高的前五名员工
      • 10 取得薪水最高的第六到第十名员工
      • 12 取得最后入职的5名员工
      • 12 取得每个薪水等级有多少员工
      • 13 面试题
      • 14 列出所有员工及领导的姓名
      • 15 列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称
      • 16 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
      • 17 列出至少有5个员工的部门
      • 18 列出薪金比'SMITH'多的员工信息
      • 19 列出所有'CLERK'（办事员）的姓名及其部门名称，部门的人数
      • 20 列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
      • 21 列出在部门'SALES'《销售部》工作的员工的姓名，假定不知道销售部的部门编号
      • 22 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，雇员的工资等级
      • 23 列出于'SCOTT'从事相同工作的所有员工及部门名称
      • 24 列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金
      • 25 列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金 部门名称
      • 26 列出在每个部门工作的员工数量，平均工资和平均服务期限
      • 27 列出所有员工的姓名、部门名称和工资
      • 28 列出所有部门的详细信息和人数
      • 29 列出各种工作的最低工资及从事此工作的雇员姓名
      • 30 列出各个部门的 MANAGER( 领导) 的最低薪金
      • 31 列出所有员工的年工资, 按年薪从低到高排序
      • 32 求出员工领导的薪水超过3000的员工名称与领导
      • 33 求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数
      • 34 给任职日期超过 30 年的员工加薪 10%

0 创建测试数据库

创建数据库，执行bjpowernode.sql

create database bjpowernode;
use bjpowernode;
source /home/project/02 MySQL/Day10/02动力节点/bjpowernode.sql


DEPT

EMP

SALGRADE

1 取得每个部门最高薪水的人员名称

① 分组 & MAX 函数
取得每个部门最高薪水（按照部门编号分组，找出每一组最大值）

SELECt DEPTNO,MAX(SAL) AS MAXSAL FROM EMP GROUP BY DEPTNO;

② 多表查询操作
将上述查询结果当做一张临时表T
T表和EMP表连接
条件：T.DEPTNO = E.DEPTNO AND T.MAXSAL = E.SAL

SELECt  E.ENAME,T.* FROM EMP E JOIN (SELECt DEPTNO,MAX(SAL) AS MAXSAL FROM EMP GROUP BY DEPTNO) T ON T.DEPTNO = E.DEPTNO AND T.MAXSAL = E.SAL;

2 哪些人的薪水在部门的平均薪水之上

① 分组 & AVG函数
找出每个部门的平均薪水

SELECt DEPTNO,AVG(SAL) AVGSQL FROM EMP GROUP BY DEPTNO;


② 连接
将上述查询结果当做一张临时表T
T表和EMP表连接
条件：T.DEPTNO = E.DEPTNO AND T.AVGSAL < E.SAL

SELECt  E.ENAME,E.SAL,T.* FROM EMP E JOIN (SELECt DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO) T ON T.DEPTNO = E.DEPTNO AND T.AVGSAL < E.SAL;

3 取得部门中（所有人的）平均的薪水等级

平均的薪水等级 先计算每一个薪水的等级，然后找出薪水等级的平均值
平均薪水的等级 先计算平均薪水，然后找出每个平均薪水的等级制

① 连接 & 排序
找出每个人的薪水等级
EMP E 和 SALGRADE S 连接
条件：E.SAL BETWEEN S.LOSAL AND S.HISAL

SELECt E.ENAME,E.SAL,E.DEPTNO,S.GRADE FROM EMP E JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;


SELECt E.ENAME,E.SAL,E.DEPTNO,S.GRADE FROM EMP E JOIN SALGRA E.SAL BETWEEN S.LOSAL AND S.HISAL ORDER BY DEPTNO;

② 分组 & AVG函数 & 连接
基于上述结果继续按照DEPTNO分组，求GRADE平均值
不用当做临时表

SELECt  E.DEPTNO,AVG(S.GRADE) FROM EMP E JOIN SALGRADE S ON  E.SAL BETWEEN S.LOSAL AND S.HISAL GROUP BY  E.DEPTNO;

4 不准用组函数（Max），求得最高薪水

① SAL降序 LIMIT 1

SELECt ENAME,SAL FROM EMP ORDER BY SAL DESC LIMIT 1;

② 表自连接

5 取平均薪水最高的部门的部门编号

方案一：降序取第一
① 分组 & AVG & 排序
找出每个部门的平均薪水

SELECt DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO;

② 降序 & LIMIT 1
降序取第一个

SELECt DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO ORDER BY AVGSAL DESC LIMIT 1;

方案二：Max
① 每个部门的平均薪资当成临时表 求出最大值

SELECt MAX(T.AVGSAL) FROM (SELECt DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO) T;

② HAVINg

SELECt DEPTNO,AVG(SAL) AS AVGSAL FROM EMP GROUP BY DEPTNO HAVINg AVGSAL = (SELECt MAX(T.AVGSAL) FROM (SELECt DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO) T);

6 取得平均薪水最高的部门的部门名称

①

SELECt   D.DNAME,AVG(E.SAL) AVGSAL FROM  EMP E JOIN  DEPT D ON  E.DEPTNO = D.DEPTNO GROUP BY  D.DNAME  ORDER BY  AVGSAL DESC LIMIT   1;


方案二：
① 用户变量

SELECt @max_deptno := DEPTNO,AVG(SAL) AS AVGSAL FROM EMP GROUP BY DEPTNO HAVINg AVGSAL = (SELECt MAX(T.AVGSAL) FROM (SELECt DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO) T);


SELECt DNAME FROM DEPT WHERe DEPTNO = @max_deptno;

7 求平均薪水的等级最低的部门的部门名称

① 分组 & AVG函数
按照部门名称分组，找出每个部门的平均薪水

SELECt DEPTNO,AVG(SAL) AS AVGSAL FROM EMP GROUP BY DEPTNO;

② 连接 & BETWEEN AND
找出每个部门的平均薪水的等级
上述结果作为临时表T 和 SALGRADE表连接
条件：T.AVGSAL BETWEEN S.LOSAL AND S.HISAL

SELECt T.*,S.GRADE FROM (SELECt DEPTNO,AVG(SAL) AS AVGSAL FROM EMP GROUP BY DEPTNO) T  JOIN  SALGRADE S ON  T.AVGSAL BETWEEN S.LOSAL AND S.HISAL;

③ ORDER BY & LIMIT

SELECt  T.*,S.GRADE FROM  (SELECt DEPTNO,AVG(SAL) AS AVGSAL FROM EMP GROUP BY DEPTNO ORDER BY AVGSAL LIMIT 1) T JOIN  SALGRADE S ON  T.AVGSAL BETWEEN S.LOSAL AND S.HISAL;


== 区间 ==

8 取得比普通员工（员工代码没有在mgr字段上出现）的最高薪水还要高的领导人的姓名

SELECt DISTINCT MGR FROM EMP;
员工编号没有在上述结果范围内的都是普通员工
① not in & MAX
找出普通员工的最高薪水

SELECt MAX(SAL) FROM EMP WHERe EMPNO NOT IN (SELECt DISTINCT MGR FROM EMP);

not in 在使用中记得排除NULL -> WHERe MGR IS NOT NULL

SELECt MAX(SAL) FROM EMP WHERe EMPNO NOT IN (SELECt DISTINCT MGR FROM EMP WHERe MGR IS NOT NULL);

② >
找出高于普通员工最高薪水的

SELECt @max_sal := MAX(SAL) FROM EMP WHERe EMPNO NOT IN (SELECt DISTINCT MGR FROM EMP WHERe MGR IS NOT NULL);


SELECt ENAME,SAL FROM EMP WHERe SAL > @max_sal;

9 取得薪水最高的前五名员工

降序取前五

SELECt EMPNO,ENAME,SAL  FROM EMP ORDER BY SAL DESC LIMIT 5;

10 取得薪水最高的第六到第十名员工

降序 & LIMIT 参数
SELECt EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC LIMIT 5,5;

12 取得最后入职的5名员工

日期也可以排序哦！

SELECt EMPNO,ENAME,HIREDATE  FROM EMP ORDER BY HIREDATE DESC LIMIT 5;

12 取得每个薪水等级有多少员工

① 连接 & BETWEEN AND
每个员工的薪水等级

SELECt E.ENAME,E.SAL,S.GRADE FROM EMP E JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;


② 连接 & 分组 & BETWEEN AND
继续按照GRADE分组统计数量

SELECt S.GRADE,COUNT(*) FROM EMP E JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL GROUP BY S.GRADE;

13 面试题

14 列出所有员工及领导的姓名

左外连接

SELECt A.ENAME '员工',B.ENAME '领导'  FROM EMP A LEFT JOIN EMP B ON A.MGR = B.EMPNO;

15 列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称

EMP A 员工表
EMP B 领导表
A.MGR = B.EMPNO AND A.HIREDATA < B.HIREDATA

SELECt A.ENAME '员工',A.HIREDATE,B.ENAME '领导',B.HIREDATE,D.DNAME  FROM EMP A JOIN EMP B ON A.MGR=B.EMPNO JOIN DEPT D ON A.DEPTNO=D.DEPTNO WHERe A.HIREDATE < B.HIREDATE;

16 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门

右连接

SELECt E.*,D.DNAME  FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;


左连接

SELECt E.*,D.DNAME  FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;


SELECt D.DNAME,E.*  FROM EMP E LEFT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

17 列出至少有5个员工的部门

HAVINg & COUNT & 分组

SELECt DEPTNO FROM EMP GROUP BY DEPTNO HAVINg COUNT(*) >= 5;

18 列出薪金比’SMITH’多的员工信息

① 'SMITH’员工的薪金

SELECt SAL FROM EMP WHERe ENAME = 'SMITH';

② 大于'SMITH'员工的薪金的员工信息

SELECt * FROM EMP WHERe SAL > (SELECt SAL FROM EMP WHERe ENAME = 'SMITH');

19 列出所有’CLERK’（办事员）的姓名及其部门名称，部门的人数

① 所有’CLERK’（办事员）的姓名及其部门名称 T1

mysql> SELECt E.ENAME,E.JOB,D.DNAME,D.DEPTNO
    -> FROM EMP E
    -> JOIN DEPT D
    -> ON E.DEPTNO = D.DEPTNO 
    -> WHERe E.JOB = 'CLERK';

② 部门的人数 T2

SELECt DEPTNO,COUNT(*) AS DEPTCOUNT FROM EMP GROUP BY DEPTNO;

③ 连接T1 T2

SELECt T1.*,T2.DEPTCOUNT FROM (SELECt E.ENAME,E.JOB,D.DNAME,D.DEPTNO FROM EMP E JOIN DEPT D ON E.DEPTNO=D.DEPTNO   WHERe E.JOB='CLERK') T1 JOIN (SELECt DEPTNO,COUNT(*) AS DEPTCOUNT FROM EMP GROUP BY DEPTNO) T2 ON T1.DEPTNO=T2.DEPTNO;

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

按照工作岗位分组求最小值

mysql> SELECt JOB,COUNT(*)
    -> FROM EMP
    -> GROUP BY JOB
    -> HAVINg MIN(SAL) > 1500;

21 列出在部门’SALES’《销售部》工作的员工的姓名，假定不知道销售部的部门编号

① 部门编号

mysql> SELECt DEPTNO
    -> FROM DEPT
    -> WHERe DNAME = 'SALES';


②

mysql> SELECt ENAME 
    -> FROM EMP
    -> WHERe DEPTNO = (SELECt DEPTNO FROM DEPT WHERe DNAME = 'SALES');

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

① 平均工资

mysql> SELECt AVG(SAL) 
    -> FROM EMP;

② 多表连接查询

mysql> SELECt E.ENAME '员工',D.DNAME,L.ENAME '领导',S.GRADE
    -> FROM EMP E
    -> JOIN DEPT D
    -> ON E.DEPTNO = D.DEPTNO
    -> LEFT JOIN EMP L
    -> ON E.MGR = L.EMPNO
    -> JOIN SALGRADE S
    -> ON E.SAL BETWEEN S.LOSAL AND S.HISAL
    -> WHERe E.SAL > (SELECt AVG(SAL) FROM EMP);

23 列出于’SCOTT’从事相同工作的所有员工及部门名称

①

SELECt JOB FROM EMP WHERe ENAME = 'SCOTT';


②

mysql> SELECt E.ENAME,E.JOB,D.DNAME
    -> FROM EMP E
    -> JOIN DEPT D
    -> ON E.DEPTNO = D.DEPTNO
    -> WHERe E.JOB = (SELECt JOB FROM EMP WHERe ENAME = 'SCOTT')
    -> AND E.ENAME <> 'SCOTT';

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

①

SELECt DISTINCT SAL FROM EMP WHERe DEPTNO = 30;

②

mysql> SELECt ENAME,SAL
    -> FROM EMP
    -> WHERe SAL IN (
    ->  SELECt DISTINCT SAL FROM EMP WHERe DEPTNO = 30);

③

SELECt ENAME,SAL FROM EMP WHERe SAL IN (  SELECt DISTINCT SAL FROM EMP WHERe DEPTNO = 30) AND DEPTNO <> 30;

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

① 部门30员工的最高薪金

mysql> SELECt MAX(SAL) 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) FROM EMP WHERe DEPTNO = 30);

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

① COUNT AVG GROUP BY 右连接

mysql> SELECt D.DEPTNO,COUNT(E.ENAME),AVG(E.SAL)
    -> FROM EMP E
    -> RIGHT JOIN DEPT D
    -> ON E.DEPTNO = D.DEPTNO
    -> GROUP BY D.DEPTNO;

② IFNULL

mysql> SELECt D.DEPTNO,COUNT(E.ENAME),IFNULL(AVG(E.SAL),0)
    -> FROM EMP E
    -> RIGHT JOIN DEPT D
    -> ON E.DEPTNO = D.DEPTNO
    -> GROUP BY D.DEPTNO;

③ 计算日期年差 函数
Mysql中计算两个日期之间的时间间隔的方法

SELECt TimeStampDiff(间隔类型，前一个日期，后一个日期);

间隔类型：

  • SECOND 秒
  • MINUTE 分钟
  • HOUR 小时
  • DAY 天
  • WEEK 星期
  • MONTH 月
  • QUARTER 季度
  • YEAR 年

当前日期： now()

SELECT TIMESTAMPDIFF(YEAR,HIREDATE,NOW()) FROM EMP;

④ 整合

mysql> SELECt D.DEPTNO,COUNT(E.ENAME) ECOUNT,IFNULL(AVG(E.SAL),0) AVGSAL,
    ->  IFNULL(AVG(TIMESTAMPDIFF(YEAR,HIREDATE,NOW())),0) AVGSERVICETIME
    -> FROM EMP E
    -> RIGHT JOIN DEPT D
    -> ON E.DEPTNO = D.DEPTNO
    -> GROUP BY D.DEPTNO;

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

mysql> SELECt E.ENAME,D.DNAME,E.SAL
-> FROM EMP E
-> JOIN DEPT D
-> ON E.DEPTNO = D.DEPTNO;

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

mysql> SELECt D.*,COUNT(E.ENAME)
    -> FROM EMP E
    -> RIGHT JOIN DEPT D
    -> ON E.DEPTNO = D.DEPTNO
    -> GROUP BY D.DEPTNO;


mysql> SELECt D.*,COUNT(E.EMPNO)
    -> FROM EMP E
    -> RIGHT JOIN DEPT D
    -> ON E.DEPTNO = D.DEPTNO
    -> GROUP BY D.DEPTNO;

mysql> SELECt D.*,COUNT(*)
    -> FROM EMP E
    -> RIGHT JOIN DEPT D
    -> ON E.DEPTNO = D.DEPTNO
    -> GROUP BY D.DEPTNO;

mysql> SELECt D.*,COUNT(E.ENAME)
    -> FROM EMP E
    -> JOIN DEPT D
    -> ON E.DEPTNO = D.DEPTNO
    -> GROUP BY D.DEPTNO;

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

① 各种工作的最低工资

mysql> SELECt JOB,MIN(SAL) MINSAL
    -> FROM EMP
    -> GROUP BY JOB;


② 自连接

mysql> SELECt E.ENAME,T.*
    -> FROM EMP E
    -> JOIN (SELECt JOB,MIN(SAL) MINSAL FROM EMP GROUP BY JOB) T
    -> ON E.JOB = T.JOB AND E.SAL = T.MINSAL;


SELECt E.ENAME,T.* FROM EMP E JOIN (SELECt JOB,MIN(SAL) MINSAL FROM EMP GROUP BY JOB) T ON E.SAL = T.MINSAL;


SELECt E.ENAME,T.* FROM EMP E JOIN (SELECt JOB,MIN(SAL) MINSAL FROM EMP GROUP BY JOB) T ON E.JOB = T.JOB;

30 列出各个部门的 MANAGER( 领导) 的最低薪金

mysql> SELECt DEPTNO,MIN(SAL) MINSAL
    -> FROM EMP
    -> WHERe JOB = 'MANAGER'
    -> GROUP BY DEPTNO;


mysql> SELECt DISTINCT JOB FROM EMP;

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

mysql> SELECt ENAME,SAL*12 YEARSAL
    -> FROM EMP
    -> ORDER BY YEARSAL ASC;

== 津贴==

mysql> SELECt ENAME,(SAL + IFNULL(COMM,0)) * 12 YEARSAL
    -> FROM EMP
    -> ORDER BY YEARSAL;

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

mysql> SELECt A.ENAME '员工',B.ENAME '领导'
    -> FROM EMP A
    -> JOIN EMP B
    -> ON A.MGR = B.EMPNO
    -> WHERe B.SAL > 3000;

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

LIKE

mysql> SELECt D.DEPTNO,D.DNAME,D.LOC,COUNT(E.ENAME),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%

mysql> UPDATE EMP SET SAL = SAL * 1.1  WHERe TIMESTAMPDIFF(YEAR,HIREDATE,NOW()) > 30;

 
打赏
 本文转载自：网络 
所有权利归属于原作者，如文章来源标示错误或侵犯了您的权利请联系微信13520258486
更多>最近资讯中心
• 在Linux系统中编写简易shell脚本，向PLSQL表中 • 自动生成存储过程抽取数据
• spring框架与mybatis框架整合做项目 • LeetCode 415. 字符串相加（字符串、高精度）—
• 511遇见易语言乐玩插件FindColorEx找色扩展 • 【有奖众测】快服务开发者俱乐部第一期来咯！
• Esp8266天猫精灵_RGB灯_非点灯平台 • STM32F103 串口1和串口3对发数据 配合蓝牙模块
• TMS570学习【1】了解什么是TMS570 • 新闻稿 | Qt公司收购froglogic公司以巩固市场领
• [Java]SpringBoot2整合mqtt服务器EMQ实现消息订 • 苹果群控投屏同步操作原理及运用的平台APP分享
更多>最新资讯中心
• Esp8266天猫精灵_RGB灯_非点灯平台 • STM32F103 串口1和串口3对发数据 配合蓝牙模块
• TMS570学习【1】了解什么是TMS570 • 新闻稿 | Qt公司收购froglogic公司以巩固市场领
• [Java]SpringBoot2整合mqtt服务器EMQ实现消息订 • 苹果群控投屏同步操作原理及运用的平台APP分享
• STM32查询式按键输入[直接用寄存器] • Ubuntu系统 USB设备端口绑定
• 2021-04-14 第四次 按键输入实验 • Flutter扫码功能完美实现
0 条相关评论

推荐图文
推荐资讯中心
点击排行
最新信息
新手指南
采购商服务
供应商服务
交易安全
关注我们
手机网站:
新浪微博:
微信关注:

13520258486

周一至周五 9:00-18:00
（其他时间联系在线客服）

24小时在线客服