MYSQL练习题的书写

最新推荐文章于 2023-04-17 16:42:33 发布

白马非马·

最新推荐文章于 2023-04-17 16:42:33 发布

阅读量304

点赞数

分类专栏： 8. 数据库文章标签： mysql java

本文链接：https://blog.csdn.net/qq_42974034/article/details/124768916

版权

8. 数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

1. 题目所涉及到的三个文件

提取码：hlda

表一：雇员的信息表：emp

+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

表二：部门表：dept

+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

表三：工资等级表:salgrade

+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

2. 34道题目

2.1 第一题：取得每个部门最高薪水的人员名称

	//按部门分组，在分组情况下，找出最大的薪水人
	select  max(sal) from emp group by deptno;
	//把上面一章表当做b表，原表当做a表，联合a和b两张表，查询a表中与b表的部门和薪资一样人的信息
	select a.ename,a.sal,a.deptno 
	from emp a 
		join (select  max(sal) sal from emp group by deptno) b
		on a.sal=b.sal;

//结果
+-------+---------+--------+
| ename | sal     | deptno |
+-------+---------+--------+
| BLAKE | 2850.00 |     30 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+

2.2 第二题：哪些人的薪水在部门的平均薪水之上

	//先求出平均薪水，然后找出在平均薪水上的人
	select avg(sal) from emp;
	//把找出来的人，按照薪水升序进行排列
	select ename,sal from emp where sal>(select avg(sal) from emp) order by sal;

+-------+---------+
| ename | sal     |
+-------+---------+
| CLARK | 2450.00 |
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| KING  | 5000.00 |

2.3 第三题：取得部门中（所有人的）平均的薪水等级

	//先获得平均薪资
	select avg(sal) from emp group by deptno;
	//把上面这个表作为b表，连接emp表，根据薪资等级表，查询等级
	select b.deptno, b.sal,a.grade 
	from salgrade a 
	join (select avg(sal) sal,deptno from emp group by deptno) b
	on  b.sal between losal and hisal;

	//结果
	+--------+-------------+-------+
| deptno | sal         | grade |
+--------+-------------+-------+
|     10 | 2916.666667 |     4 |
|     20 | 2175.000000 |     4 |
|     30 | 1566.666667 |     3 |
+--------+-------------+-------+

2.4 第四题：不准用组函数（ Max），取得最高薪水（给出两种解决方案）

方法一：使用排序,由小到大排序，拿出第一个来

	select sal from emp order by sal desc limit 1;

方法二：自连接
1）首先通过自连接，找出a表中小于b表中的元素，只有最大值不会小于，不会出现
2）通过在emp中寻找不在上面查找结果中的值
3）使用了自连接，使用了查询函数not in / in

	select sal 
	from emp 
	where sal not in(select a.sal from emp a join emp b on a.sal<b.sal);

结果

+---------+
| sal     |
+---------+
| 5000.00 |
+---------+

2.5 第五题：取得平均薪水最高的部门和部门编号（至少给出两种解决方案）

方法一:把部门平均薪水排个序，取出第一个（不适合当多个平均薪资出现并列第一的情况）

	//先获取各部门的平均薪资,然后从大到小排序，取出第一个，即得到了平均薪资最高的薪资和部门边号
		select avg(sal) sal,deptno 
		from emp 
		group by deptno 
		order by avg(sal) desc 
		limit 0,1;

方法二：把部门平均薪水排个序，取出值与第一个相同的部门

	select avg(sal),deptno 
	from emp 
	group by deptno 
	having avg(sal)=(select avg(sal)
		from emp 
		group by deptno 
		order by avg(sal) desc 
		limit 0,1);

	//结果
+-------------+--------+
| sal         | deptno |
+-------------+--------+
| 2916.666667 |     10 |
+-------------+--------+

2.6 第六题：取得平均薪水最高的部门名称

首先分组求出部门的平均薪资，并且找出最低的薪水的部门编号
连接部门表，根据部门的编号，找出部门的名称
注意思路，一步步写就不会出现问题，如果有一个大的from就是用一个表去取别名。

	//1）分组求出部门的平均薪水
	select avg(sal) avgsal, deptno from emp group by deptno;
	//2)找到平均薪水最低的薪水和部门编号
	select avgsal minavgsal,deptno 
	from (select avg(sal) avgsal, Deptno 
		from emp 
		group by deptno
		having avgsal=(select avg(sal) avgsal from emp group by deptno order by avg(sal) desc limit 0,1)
		) as a;
+-------------+--------+
| minavgsal   | deptno |
+-------------+--------+
| 2916.666667 |     10 |
+-------------+--------+
	//3)连接部门表，根据部们编号找到对应的部门名称
	select a.avgsal minavgsal,a.deptno,b.dname 
	from ( select avg(sal) avgsal, deptno 
			from emp 
			group by deptno 
			having avgsal=(select avg(sal) avgsal from emp group by deptno order by avg(sal) desc limit 0,1)
			 )as a
		join dept b
		on a.deptno=b.deptno;
+-------------+--------+------------+
| minavgsal   | deptno | dname      |
+-------------+--------+------------+
| 2916.666667 |     10 | ACCOUNTING |
+-------------+--------+------------+

2.7 第七题：求平均薪水的等级最低的部门的部门名称

首先等级最低的部门不止一个
首先按部门分组求得部门的平均薪资

	select avg(sal) avgsal,deptno from emp group by deptno;

连接部门等级表，求得平均薪资的等级

	select a.avgsal,a.deptno,b.grade,c.dname
	from (select avg(sal) avgsal,deptno from emp group by deptno) as a
			join salgrade b
			on a.avgsal between b.losal and b.hisal;
			join dept c
			on a.deptno=c.deptno

从上面表中找到最低平均薪水的部门编号，然后在根据部门标号，在上面表中找出对应标号的部门

	select avgsal,deptno,grade
	from (select a.avgsal,a.deptno,b.grade
			from (select avg(sal) avgsal,deptno from emp group by deptno) as a
			join salgrade b
			on a.avgsal between b.losal and b.hisal
			)as a
	where grade=(select grade
	 from (select a.avgsal,a.deptno,b.grade
			from (select avg(sal) avgsal,deptno from emp group by deptno) as a
			join salgrade b
			on a.avgsal between b.losal and b.hisa
			)as b order by grade limit 0,1);

	select avgsal,deptno,grade,dname
	from (select a.avgsal,a.deptno,b.grade,c.dname
			from (select avg(sal) avgsal,deptno from emp group by deptno) as a
			join salgrade b
			on a.avgsal between b.losal and b.hisal
			join dept c
			on a.deptno=c.deptno
			)as a
	where grade=(select grade
	 from (select a.avgsal,a.deptno,b.grade,c.dname
			from (select avg(sal) avgsal,deptno from emp group by deptno) as a
			join salgrade b
			on a.avgsal between b.losal and b.hisal
			join dept c
			on a.deptno=c.deptno
			)as a order by grade limit 0,1);
+-------------+--------+-------+-------+
| avgsal      | deptno | grade | dname |
+-------------+--------+-------+-------+
| 1566.666667 |     30 |     3 | SALES |
+-------------+--------+-------+-------+

2.8 第八题：取得比普通员工(员工代码没有在 mgr 字段上出现的)的最高薪水还要高的领导人姓名

2.9 第九题：取得薪水最高的前五名员工

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

	//首先取出薪水，然后按工资进行排序，从大到小排，之后只取出五个
	select ename,sal from emp order by sal desc limit 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| FORD  | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

2.10 第十题：取得薪水最高的第六到第十名员工

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

	select ename,sal from emp order by sal desc limit 5,5;
+--------+---------+
| ename  | sal     |
+--------+---------+
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD   | 1250.00 |
+--------+---------+

2.11 第十一题：取得最后入职的 5 名员工

取得最后入职的 5 名员工

	select ename,hiredate from emp order by hiredate limit 5;
+-------+------------+
| ename | hiredate   |
+-------+------------+
| SMITH | 1980-12-17 |
| ALLEN | 1981-02-20 |
| WARD  | 1981-02-22 |
| JONES | 1981-04-02 |
| BLAKE | 1981-05-01 |
+-------+------------+

2.12 第十二题：取得每个薪水等级有多少员工

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

	//1. 新建一张表，得到员工的名字和薪资水平等级
	select a.ename,a.sal,b.grade
	from emp a
		join salgrade b
		on a.sal between b.losal and b.hisal
	
	//2)把上面这张表根据等级进行分组，然后再统计个数
	select grade,counts(*) number 
	from (select a.ename,a.sal,b.grade
			from emp a
			join salgrade b
			on a.sal between b.losal and b.hisal
			) a
	group by grade;

2.13 面试题

2.14 第十四题：列出所有员工及领导的姓名

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

通过自连接，列出员工的名字和领导的名字(采用外连接，把员工表作为主表）

	select a.ename '雇员', ifnull(b.ename,'noboss')  '老板'
	from emp a
		left join emp b
		on a.mgr=b.empno;
+--------+--------+
| 雇员   | 老板   |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| KING   | noboss |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+

2.15 第十五题：列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

日期进行比较，使用函数DATEDIFF(day, time1 , time2)
首先通过自连接，找出员工和老板的对应，然后进行筛选，根据日期比较函数

	select a.empno,a.ename,c.dname
	from emp a
		join emp b
		on a.mgr=b.empno
		join dept c
		on a.deptno=c.deptno
	where a.hiredate< b.hiredate;
	select a.empno,a.ename,c.dname
	from emp a
		join emp b
		on a.mgr=b.empno
		join dept c
		on a.deptno=c.deptno
	where a.hiredate< b.hiredate;

2.16 第十六题：列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门

部门和员工之间进行关联，同时以部门为主表

select b.dname,a.* 
from emp a right 
	join dept b 
	on a.deptno=b.deptno;
order by dname;
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| dname      | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+
| ACCOUNTING |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
| ACCOUNTING |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
| ACCOUNTING |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
| OPERATIONS |  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL |
| RESEARCH   |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
| RESEARCH   |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
| RESEARCH   |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
| RESEARCH   |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
| RESEARCH   |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
| SALES      |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
| SALES      |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
| SALES      |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
| SALES      |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
| SALES      |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
| SALES      |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
+------------+-------+--------+-----------+------+------------+---------+---------+--------+

2.17 第十七题：列出至少有 5 个员工的所有部门

首先把emp根据部门进行分组，统计个数，然后找出个数大于5的部门名字来

	select deptno,count(*) number
	from emp 
	group by deptno
	having number>=5

把上表连接部门表，找出名字

select b.dname,a.number
from (select deptno,count(*) number
		from emp 
		group by deptno
		having number>=5
		)as a
		join dept b
		on a.deptno=b.deptno
+----------+--------+
| dname    | number |
+----------+--------+
| RESEARCH |      5 |
| SALES    |      6 |
+----------+--------+

2.18 第十八题：列出薪金比"SMITH"多的所有员工信息.

	select * 
	from emp 
	where sal>(select sal from emp where ename='smith');
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

2.19 第十九题：列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数

得到一张表，统计部门的编号和部门的人数

	select deptno,count(*) number 
	from emp
	group by deptno

得到一张表，统计部门的编号，名称和部门的人数

	select c.ename,c.deptno,a.number,b.dname
	from (select deptno,count(*) number 
		from emp
		group by deptno
		) a
		join dept b
		on a.deptno=b.deptno
		join emp c
		on c.deptno=a.deptno
	where c.job='CLERK';
+--------+--------+--------+------------+
| ename  | deptno | number | dname      |
+--------+--------+--------+------------+
| SMITH  |     20 |      5 | RESEARCH   |
| ADAMS  |     20 |      5 | RESEARCH   |
| JAMES  |     30 |      6 | SALES      |
| MILLER |     10 |      3 | ACCOUNTING |
+--------+--------+--------+------------+

2.20 第二十题：列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数

按工作进行分组，工作的最低薪金，工作的名称和工作的人数

	select job,count(*)number, min(sal) minsal from emp group by job having minsal>1500

2.21 第二十一题：列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号.

	select ename from emp where deptno=(select deptno from dept where dname='SALES');
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+

2.22 第二十二题：列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.

需要联合三张表的信息
首先计算出公司的平均薪金，然后作为where条件，连接三张表

	select a.ename 'emp', ifnull(b.ename,'wu')  'boss', c.dname 'deptname', d.grade 'grade'
	from emp a
		left  join emp b
		on a.mgr=b.empno
		join dept c
		on a.deptno=c.deptno
		join salgrade d
		on a.sal between d.losal and d.hisal
	where a.sal>(select avg(sal) from emp)
+-------+-------+------------+-------+
| emp   | boss  | deptname   | grade |
+-------+-------+------------+-------+
| JONES | KING  | RESEARCH   |     4 |
| BLAKE | KING  | SALES      |     4 |
| CLARK | KING  | ACCOUNTING |     4 |
| SCOTT | JONES | RESEARCH   |     4 |
| KING  | wu    | ACCOUNTING |     5 |
| FORD  | JONES | RESEARCH   |     4 |
+-------+-------+------------+-------+

2.23 第二十三题：列出与"SCOTT"从事相同工作的所有员工及部门名称(不包括scott了）

找出scott从事的工作
建立一张表，找到员工，工作和部门

	//1）找到scott从事的工作
	select job from emp where ename='scott';
	//2.连接表一和表二，根据工作名字，找到对应的人和部门名称
	select a.ename,a.job,b.dname
	from emp a
		join dept b
		on a.deptno=b.deptno
	where a.job=(select job from emp where ename='scott') and a.ename <>'scott';
+-------+---------+----------+
| ename | job     | dname    |
+-------+---------+----------+
| FORD  | ANALYST | RESEARCH |
+-------+---------+----------+

2.24 第二十四题：列出与"SCOTT"从事相同工作的所有员工及部门名称(不包括scott了）

2.25 第二十五题：列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金.部门名

首先得到在部门30工作所有员工的最高薪金

select max(sal) from emp where deptno=30;

连接emp和dept两张表

select a.ename,a.sal,b.dname
from emp a
	join dept b
	on a.deptno=b.deptno
where sal>(select max(sal) from emp where deptno=30);

2.26 第二十六题：列出在每个部门工作的员工数量,平均工资

	select a.dname, b.count(*) number, b.avg(sal) 
	from dept a
		join (select  deptno, count(*) number, avg(sal) 
			from emp group by deptno) b
		on a.deptno=b.deptno
+------------+--------+-------------+
| dname      | number | avgsal      |
+------------+--------+-------------+
| ACCOUNTING |      3 | 2916.666667 |
| RESEARCH   |      5 | 2175.000000 |
| SALES      |      6 | 1566.666667 |
+------------+--------+-------------+

2.27 第二十七题：列出所有员工的姓名、部门名称和工资。

select a.ename,a.sal,b.dname
from emp a
join dept b
on a.deptno=b.deptno
+--------+---------+------------+
| ename  | sal     | dname      |
+--------+---------+------------+
| SMITH  |  800.00 | RESEARCH   |
| ALLEN  | 1600.00 | SALES      |
| WARD   | 1250.00 | SALES      |
| JONES  | 2975.00 | RESEARCH   |
| MARTIN | 1250.00 | SALES      |
| BLAKE  | 2850.00 | SALES      |
| CLARK  | 2450.00 | ACCOUNTING |
| SCOTT  | 3000.00 | RESEARCH   |
| KING   | 5000.00 | ACCOUNTING |
| TURNER | 1500.00 | SALES      |
| ADAMS  | 1100.00 | RESEARCH   |
| JAMES  |  950.00 | SALES      |
| FORD   | 3000.00 | RESEARCH   |
| MILLER | 1300.00 | ACCOUNTING |
+--------+---------+------------+

2.28 第二十八题：列出所有部门的详细信息和人数

统计部门的编号的人数

select a.*, b.number
from dept a
left join (select deptno ,count(*) number
	from emp group by deptno) b
on a.deptno=b.deptno
+--------+------------+----------+--------+
| DEPTNO | DNAME      | LOC      | number |
+--------+------------+----------+--------+
|     10 | ACCOUNTING | NEW YORK |      3 |
|     20 | RESEARCH   | DALLAS   |      5 |
|     30 | SALES      | CHICAGO  |      6 |
|     40 | OPERATIONS | BOSTON   |   NULL |
+--------+------------+----------+--------+

2.29 第二十九题：列出各种工作的最低工资及从事此工作的雇员姓名

求出各中工作的最低工资
使用in运算符，包含在里面的值输出

select * from emp where sal in (select  min(sal) from emp group by job);
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
+-------+--------+-----------+------+------------+---------+---------+--------+

2.30 第三十题：列出各个部门的 MANAGER(领导)的最低薪金

select  min(sal), deptno from emp where job='MANAGER' group by deptno;
+----------+--------+
| min(sal) | deptno |
+----------+--------+
|  2450.00 |     10 |
|  2975.00 |     20 |
|  2850.00 |     30 |
+----------+--------+

2.31 第三十一题：列出所有员工的年工资,按年薪从低到高排序

select ename,(sal+ifnull(comm,0))*12 yearsal from emp order by yearsal;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| JAMES  | 11400.00 |
| ADAMS  | 13200.00 |
| MILLER | 15600.00 |
| TURNER | 18000.00 |
| WARD   | 21000.00 |
| ALLEN  | 22800.00 |
| CLARK  | 29400.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| JONES  | 35700.00 |
| SCOTT  | 36000.00 |
| FORD   | 36000.00 |
| KING   | 60000.00 |
+--------+----------+

2.32 第三十二题：求出员工领导的薪水超过 3000 的员工名称与领导名称

select a.ename,a.sal ,b.ename,b.sal
	from emp a
	join emp b
	on a.mgr=b.empno
where b.sal>3000
+-------+---------+-------+---------+
| ename | sal     | ename | sal     |
+-------+---------+-------+---------+
| JONES | 2975.00 | KING  | 5000.00 |
| BLAKE | 2850.00 | KING  | 5000.00 |
| CLARK | 2450.00 | KING  | 5000.00 |
+-------+---------+-------+---------+

2.33 第三十三题：求出部门名称中,带’S’字符的部门员工的工资合计、部门人数

部门员工合计和部门人数

select a.dname,b.sum,b.number 
from dept a
	join (select deptno, count(*) number, sum(sal) sum from emp group by deptno
	)b
	on a.deptno =b.deptno
where a.dname like '%S%'
+----------+----------+--------+
| dname    | sum      | number |
+----------+----------+--------+
| RESEARCH | 10875.00 |      5 |
| SALES    |  9400.00 |      6 |
+----------+----------+--------+

2.34 第三十四题：给任职日期超过 30 年的员工加薪 10%

使用时间计算函数：TimeStampDiff(YEAR,hiredate,now())

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

白马非马·

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录