MySql 数据库 - 34道练习题

最新推荐文章于 2023-01-19 19:50:39 发布

佬涛

最新推荐文章于 2023-01-19 19:50:39 发布

阅读量654

点赞数

分类专栏： MySql 数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/W1324926684/article/details/108015383

版权

MySql 数据库专栏收录该内容

14 篇文章 3 订阅

订阅专栏

MySql 数据库 - 34道练习题

传送门

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

第一步：取得每个部门最高薪水(按照部门编号分组，找出每一组最大值)
	select deptno,max(sal) as maxsal from emp group by deptno;
	+--------+---------+
	| deptno | maxsal  |
	+--------+---------+
	|     10 | 5000.00 |
	|     20 | 3000.00 |
	|     30 | 2850.00 |
	+--------+---------+
第二步：将以上的查询结果当做一张临时表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;

	+-------+--------+---------+
	| ename | deptno | maxsal  |
	+-------+--------+---------+
	| BLAKE |     30 | 2850.00 |
	| SCOTT |     20 | 3000.00 |
	| KING  |     10 | 5000.00 |
	| FORD  |     20 | 3000.00 |
	+-------+--------+---------+

错误解法————一步到位是不行的：
	select deptno,max(sal) as maxsal ,ename from emp group by deptno;
	+--------+---------+-------+
	| deptno | maxsal  | ename |
	+--------+---------+-------+
	|     10 | 5000.00 | CLARK |
	|     20 | 3000.00 | SMITH |
	|     30 | 2850.00 | ALLEN |
	+--------+---------+-------+
	这里的ename的三个值，并不是对应的每个部门最高薪水的人员名称，
	只是各个部门的第一个人，主要还是因为没有约束这里

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

第一步：找出每个部门的平均薪水
	select deptno,avg(sal) as avgsal from emp group by deptno;
	+--------+-------------+
	| deptno | avgsal      |
	+--------+-------------+
	|     10 | 2916.666667 |
	|     20 | 2175.000000 |
	|     30 | 1566.666667 |
	+--------+-------------+
第二步：将以上查询结果当做t表，t和emp表连接
	条件：部门编号相同，并且emp的sal大于t表的avgsal
	select 
		t.*, e.ename, e.sal
	from
		emp e
	join
		(select deptno,avg(sal) as avgsal from emp group by deptno) t
	on
		e.deptno = t.deptno and e.sal > t.avgsal;
	+--------+-------------+-------+---------+
	| deptno | avgsal      | ename | sal     |
	+--------+-------------+-------+---------+
	|     30 | 1566.666667 | ALLEN | 1600.00 |
	|     20 | 2175.000000 | JONES | 2975.00 |
	|     30 | 1566.666667 | BLAKE | 2850.00 |
	|     20 | 2175.000000 | SCOTT | 3000.00 |
	|     10 | 2916.666667 | KING  | 5000.00 |
	|     20 | 2175.000000 | FORD  | 3000.00 |
	+--------+-------------+-------+---------+

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;

	+--------+---------+--------+-------+
	| ename  | sal     | deptno | grade |
	+--------+---------+--------+-------+
	| CLARK  | 2450.00 |     10 |     4 |
	| KING   | 5000.00 |     10 |     5 |
	| MILLER | 1300.00 |     10 |     2 |

	| SMITH  |  800.00 |     20 |     1 |
	| ADAMS  | 1100.00 |     20 |     1 |
	| SCOTT  | 3000.00 |     20 |     4 |
	| FORD   | 3000.00 |     20 |     4 |
	| JONES  | 2975.00 |     20 |     4 |

	| MARTIN | 1250.00 |     30 |     2 |
	| TURNER | 1500.00 |     30 |     3 |
	| BLAKE  | 2850.00 |     30 |     4 |
	| ALLEN  | 1600.00 |     30 |     3 |
	| JAMES  |  950.00 |     30 |     1 |
	| WARD   | 1250.00 |     30 |     2 |
	+--------+---------+--------+-------+

第二步：基于以上的结果继续按照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;

	+--------+--------------+
	| deptno | avg(s.grade) |
	+--------+--------------+
	|     10 |       3.6667 |
	|     20 |       2.8000 |
	|     30 |       2.5000 |
	+--------+--------------+

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

第零种方案：select max(sal) from emp;//题目不让用

第一种：sal降序，limit 1
	select ename,sal from emp order by sal desc limit 1;
	+-------+---------+
	| ename | sal     |
	+-------+---------+
	| KING  | 5000.00 |
	+-------+---------+

第二种方案：表的自连接
	a表
	+---------+
	| sal     |
	+---------+
	|  800.00 |
	| 1600.00 |
	| 1250.00 |
	| 2975.00 |
	| 1250.00 |
	| 2850.00 |
	| 2450.00 |
	| 3000.00 |
	| 5000.00 |
	| 1500.00 |
	| 1100.00 |
	|  950.00 |
	| 3000.00 |
	| 1300.00 |
	+---------+

	b表
	+---------+
	| sal     |
	+---------+
	|  800.00 |
	| 1600.00 |
	| 1250.00 |
	| 2975.00 |
	| 1250.00 |
	| 2850.00 |
	| 2450.00 |
	| 3000.00 |
	| 5000.00 |
	| 1500.00 |
	| 1100.00 |
	|  950.00 |
	| 3000.00 |
	| 1300.00 |
	+---------+
	
	select 
		distinct a.sal 
	from 
		emp a 
	join 
		emp b 
	on 
		a.sal < b.sal;
	先找出不是最高工资的剩余的工资有哪些（如果存在工资比自己大的，那么自己就不是最大的）
	
	再not in

	select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal);
	+---------+
	| sal     |
	+---------+
	| 5000.00 |
	+---------+

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

第一种方案：降序取第一个

	第一步：找出每个部门的平均薪水
		select deptno,avg(sal) as avgsal from emp group by deptno;
		+--------+-------------+
		| deptno | avgsal      |
		+--------+-------------+
		|     10 | 2916.666667 |
		|     20 | 2175.000000 |
		|     30 | 1566.666667 |
		+--------+-------------+
	第二步：降序选第一个。
		select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
		+--------+-------------+
		| deptno | avgsal      |
		+--------+-------------+
		|     10 | 2916.666667 |
		+--------+-------------+

第二种方案：max

	第一步：找出每个部门的平均薪水

		select deptno,avg(sal) as avgsal from emp group by deptno;
		+--------+-------------+
		| deptno | avgsal      |
		+--------+-------------+
		|     10 | 2916.666667 |
		|     20 | 2175.000000 |
		|     30 | 1566.666667 |
		+--------+-------------+

	第二步：找出以上结果中avgsal最大的值。
		select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t;
		+---------------+
		| max(t.avgsal) |
		+---------------+
		|   2916.666667 |
		+---------------+

	第三步：把第二步作为having放到第一步上面
		select 
			deptno,avg(sal) as avgsal 
		from 
			emp 
		group by 
			deptno
		having
			avgsal = (select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t);
		
		+--------+-------------+
		| deptno | avgsal      |
		+--------+-------------+
		|     10 | 2916.666667 |
		+--------+-------------+

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

select 
	d.dname,avg(e.sal) as avgsal 
from 
	emp e
join
	dept d
on
	e.deptno = d.deptno
group by 
	d.dname
order by 
	avgsal desc 
limit 
	1;

+------------+-------------+
| dname      | avgsal      |
+------------+-------------+
| ACCOUNTING | 2916.666667 |
+------------+-------------+

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

平均薪水是800
平均薪水是900
那么他俩都是1级别。

第一步：找出每个部门的平均薪水
	select deptno,avg(sal) as avgsal from emp group by deptno;
	+--------+-------------+
	| deptno | avgsal      |
	+--------+-------------+
	|     10 | 2916.666667 |
	|     20 | 2175.000000 |
	|     30 | 1566.666667 |
	+--------+-------------+
	select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname;
	+------------+-------------+
	| dname      | avgsal      |
	+------------+-------------+
	| ACCOUNTING | 2916.666667 |
	| RESEARCH   | 2175.000000 |
	| SALES      | 1566.666667 |
	+------------+-------------+


第二步：找出每个部门的平均薪水的等级
	以上t表和salgrade表连接，条件：t.avgsal between s.losal and s.hisal

	select 
		t.*,s.grade
	from
		(select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t
	join
		salgrade s
	on
		t.avgsal between s.losal and s.hisal;

	+------------+-------------+-------+
	| dname      | avgsal      | grade |
	+------------+-------------+-------+
	| SALES      | 1566.666667 |     3 |
	| ACCOUNTING | 2916.666667 |     4 |
	| RESEARCH   | 2175.000000 |     4 |
	+------------+-------------+-------+

第三步：找出等级最低的
	
	抛开之前的，最低等级你怎么着？
		平均薪水最低的对应的等级一定是最低的.
		select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1;
		+-------------+
		| avgsal      |
		+-------------+
		| 1566.666667 |
		+-------------+

		select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal;
		+-------+
		| grade |
		+-------+
		|     3 |
		+-------+

	select 
		t.*,s.grade
	from
		(select d.dname,avg(sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t
	join
		salgrade s
	on
		t.avgsal between s.losal and s.hisal
	where
		s.grade = (select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1) between losal and hisal);

	+-------+-------------+-------+
	| dname | avgsal      | grade |
	+-------+-------------+-------+
	| SALES | 1566.666667 |     3 |
	+-------+-------------+-------+

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

	select distinct mgr from emp where mgr is not null;
	+------+
	| mgr  |
	+------+
	| 7902 |
	| 7698 |
	| 7839 |
	| 7566 |
	| 7788 |
	| 7782 |
	+------+
	员工编号没有在以上范围内的都是普通员工。

第一步：找出普通员工的最高薪水！
	not in在使用的时候，后面小括号中记得排除NULL。
	select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);
	+----------+
	| max(sal) |
	+----------+
	|  1600.00 |
	+----------+

第二步：找出高于1600的
	select ename,sal from emp where sal > (select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));
	+-------+---------+
	| ename | sal     |
	+-------+---------+
	| JONES | 2975.00 |
	| BLAKE | 2850.00 |
	| CLARK | 2450.00 |
	| SCOTT | 3000.00 |
	| KING  | 5000.00 |
	| FORD  | 3000.00 |
	+-------+---------+

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

select ename,sal from emp order by sal desc limit 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

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 |
| MARTIN | 1250.00 |
+--------+---------+

11、取得最后入职的 5 名员工

日期也可以降序，升序。

select ename,hiredate from emp order by hiredate desc limit 5;

+--------+------------+
| ename  | hiredate   |
+--------+------------+
| ADAMS  | 1987-05-23 |
| SCOTT  | 1987-04-19 |
| MILLER | 1982-01-23 |
| FORD   | 1981-12-03 |
| JAMES  | 1981-12-03 |
+--------+------------+

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

分组count

第一步：找出每个员工的薪水等级
select 
	e.ename,e.sal,s.grade 
from 
	emp e 
join 
	salgrade s 
on 
	e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+

第二步：继续按照grade分组统计数量
select 
	s.grade ,count(*)
from 
	emp e 
join 
	salgrade s 
on 
	e.sal between s.losal and s.hisal
group by
	s.grade;

+-------+----------+
| grade | count(*) |
+-------+----------+
|     1 |        3 |
|     2 |        3 |
|     3 |        2 |
|     4 |        5 |
|     5 |        1 |
+-------+----------+

13、【面试题】 ****

有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）

问题：
1，找出没选过“黎明”老师的所有学生姓名。
2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。
3，即学过 1 号课程又学过 2 号课所有学生的姓名。

【附：表数据】
	CREATE TABLE SC
	(
	  SNO      VARCHAR(200),
	  CNO      VARCHAR(200),
	  SCGRADE  VARCHAR(200)
	);

	CREATE TABLE S
	(
	  SNO    VARCHAR(200 ),
	  SNAME  VARCHAR(200)
	);

	CREATE TABLE C
	(
	  CNO       VARCHAR(200),
	  CNAME     VARCHAR(200),
	  CTEACHER  VARCHAR(200)
	);

	INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '1', 'yuwen', 'zhangsan'); 
	INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', 'zhengzhi', 'lisi'); 
	INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', 'yingyu', 'wangwu'); 
	INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', 'shuxue', 'zhaoliu'); 
	INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', 'wuli', 'liming'); 
	commit;
	 
	INSERT INTO S ( SNO, SNAME ) VALUES ( '1', 'student1'); 
	INSERT INTO S ( SNO, SNAME ) VALUES ( '2', 'student2'); 
	INSERT INTO S ( SNO, SNAME ) VALUES ( '3', 'student3'); 
	INSERT INTO S ( SNO, SNAME ) VALUES ( '4', 'student4'); 
	commit;
	 
	INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '40'); 
	INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '30'); 
	INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '20'); 
	INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '4', '80'); 
	INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '5', '60'); 
	INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '1', '60'); 
	INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '60'); 
	INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '60'); 
	INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '60'); 
	INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '5', '40'); 
	INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60'); 
	INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '80'); 
	commit;

【第一问】找出没选过“黎明”老师的所有学生姓名。

	第一步：找出“黎明”老师带的课的课号:
		select distinct cno from c where cteacher='liming';
		+------+
		| cno  |
		+------+
		| 5    |
		+------+
	第二步：找出选过“黎明”老师课的学生的学号
		select  sno from sc  where  cno in (
			select distinct cno from c where cteacher='liming'
		);
		+------+
		| sno  |
		+------+
		| 1    |
		| 2    |
		+------+

	第三步：找出没选过“黎明”老师的所有学生姓名

		select sname from s where sno not in (
			select  sno from sc  where  cno in (
				select distinct cno from c where cteacher='liming'
			)
		);
		+----------+
		| sname    |
		+----------+
		| student3 |
		| student4 |
		+----------+

【第二问】列出 2 门以上（含2 门）不及格学生姓名及平均成绩。
	
	第一步：找出2 门以上（含2 门）不及格学生的学号
		select sno from sc where  scgrade<60 group by sno having count(*) >=2;
		+------+
		| sno  |
		+------+
		| 1    |
		+------+
	第二步：每个学生的平均成绩
		select sno, avg(scgrade) as avg_grade from sc group by sno;
		+------+-----------+
		| sno  | avg_grade |
		+------+-----------+
		| 1    |        46 |
		| 2    |        56 |
		| 3    |        70 |
		+------+-----------+
	第三步：最终结果
		select 
			s.sname, t.avg_grade 
		from
			s
		join
			(select sno, avg(scgrade) as avg_grade from sc group by sno) t
		on	
			s.sno=t.sno
		where
			s.sno  in (select sno from sc where  scgrade<60 group by sno having count(*) >=2);
		+----------+-----------+
		| sname    | avg_grade |
		+----------+-----------+
		| student1 |        46 |
		+----------+-----------+

【第三问】即学过 1 号课程又学过 2 号课所有学生的姓名。
	第一步：学过 1 号课程的学生学号
		select sno from sc where cno = 1;
	
	第二步：学过 2 号课程的学生学号
		select sno from sc where cno = 2;
	
	第三步：即学过 1 号课程又学过 2 号课的学生学号
		select distinct sno from sc where sno in (select sno from sc where cno= 1)
			and sno in (select sno from sc where cno= 2 );
		简化：
		select  sno from sc where cno= 1 
			and sno in (select sno from sc where cno= 2 );
	第四步：对应的学生姓名
		select sname from s where sno in(
			select  sno from sc where cno= 1 
				and sno in (select sno from sc where cno= 2 )	
		);
		+----------+
		| sname    |
		+----------+
		| student1 |
		| student2 |
		+----------+

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

select 
	a.ename '员工', b.ename '领导'
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   | NULL  |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+

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

受雇日期早于其直接上级的所有员工: 自连接
emp a 员工表
emp b 领导表
a.mgr = b.empno and a.hiredate < b.hiredate

select 
	a.empno, a.ename, 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;

+-------+-------+------------+
| empno | ename | dname      |
+-------+-------+------------+
|  7782 | CLARK | ACCOUNTING |
|  7369 | SMITH | RESEARCH   |
|  7566 | JONES | RESEARCH   |
|  7499 | ALLEN | SALES      |
|  7521 | WARD  | SALES      |
|  7698 | BLAKE | SALES      |
+-------+-------+------------+

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

select 
	e.*, d.dname
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno;

+-------+--------+-----------+------+------------+---------+---------+--------+------------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO | dname      |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 | 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 |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.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   |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 | RESEARCH   |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 | RESEARCH   |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 | SALES      |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 | SALES      |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.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      |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 | SALES      |
|  NULL | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |   NULL | OPERATIONS |
+-------+--------+-----------+------+------------+---------+---------+--------+------------+

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

按照部门编号分组，计数，筛选出 >= 5

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

+--------+
| deptno |
+--------+
|     20 |
|     30 |
+--------+

18、列出薪金比"SMITH" 多的所有员工信息

select ename,sal from emp where sal > (select sal from emp where ename = 'SMITH');
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+

19、列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数 ***

第一步：列出所有"CLERK"( 办事员) 的姓名
	select ename,job from emp where job = 'CLERK';
	+--------+-------+
	| ename  | job   |
	+--------+-------+
	| SMITH  | CLERK |
	| ADAMS  | CLERK |
	| JAMES  | CLERK |
	| MILLER | CLERK |
	+--------+-------+

第二步：列出所有"CLERK"( 办事员) 的姓名 及其部门名称 ：临时表 t1

	select 
		e.ename,e.job,d.dname,d.deptno
	from 
		emp e
	join
		dept d
	on
		e.deptno = d.deptno
	where 
		e.job = 'CLERK';

	+--------+-------+------------+--------+
	| ename  | job   | dname      | deptno |
	+--------+-------+------------+--------+
	| MILLER | CLERK | ACCOUNTING |     10 |
	| SMITH  | CLERK | RESEARCH   |     20 |
	| ADAMS  | CLERK | RESEARCH   |     20 |
	| JAMES  | CLERK | SALES      |     30 |
	+--------+-------+------------+--------+


第三步：每个部门的人数：做临时表 t2
	select deptno, count(*) as deptcount from emp group by deptno;
	+--------+-----------+
	| deptno | deptcount |
	+--------+-----------+
	|     10 |         3 |
	|     20 |         5 |
	|     30 |         6 |
	+--------+-----------+
第四步：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;

	+--------+-------+------------+--------+-----------+
	| ename  | job   | dname      | deptno | deptcount |
	+--------+-------+------------+--------+-----------+
	| MILLER | CLERK | ACCOUNTING |     10 |         3 |
	| SMITH  | CLERK | RESEARCH   |     20 |         5 |
	| ADAMS  | CLERK | RESEARCH   |     20 |         5 |
	| JAMES  | CLERK | SALES      |     30 |         6 |
	+--------+-------+------------+--------+-----------+

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

按照工作岗位分组求最小值。
select job,count(*) from emp group by job having min(sal) > 1500;

+-----------+----------+
| job       | count(*) |
+-----------+----------+
| ANALYST   |        2 |
| MANAGER   |        3 |
| PRESIDENT |        1 |
+-----------+----------+

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

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

+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+

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

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);

+-------+------------+-------+----------+
| 员工     | 部门    | 领导  | 工资等级|
+-------+------------+-------+----------+
| JONES | RESEARCH   | KING  |        4 |
| BLAKE | SALES      | KING  |        4 |
| CLARK | ACCOUNTING | KING  |        4 |
| SCOTT | RESEARCH   | JONES |        4 |
| KING  | ACCOUNTING | NULL  |        5 |
| FORD  | RESEARCH   | JONES |        4 |
+-------+------------+-------+----------+

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

第一步："SCOTT" 从事的工作
	select job from emp where ename = 'SCOTT';
	+---------+
	| job     |
	+---------+
	| ANALYST |
	+---------+
第二步：
	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';

	+-------+---------+----------+
	| ename | job     | dname    |
	+-------+---------+----------+
	| FORD  | ANALYST | RESEARCH |
	+-------+---------+----------+

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

部门 30 中员工的薪金：
select distinct sal from emp where deptno = 30;
+---------+
| sal     |
+---------+
| 1600.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
|  950.00 |
+---------+

select 
	ename,sal 
from 
	emp 
where 
	sal in(select distinct sal from emp where deptno = 30) 
and 
	deptno <> 30;

Empty set (0.00 sec)

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

在部门 30 工作的员工的最高薪金
select max(sal) from emp where deptno = 30;
+----------+
| max(sal) |
+----------+
|  2850.00 |
+----------+

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);

+-------+---------+------------+
| ename | sal     | dname      |
+-------+---------+------------+
| KING  | 5000.00 | ACCOUNTING |
| JONES | 2975.00 | RESEARCH   |
| SCOTT | 3000.00 | RESEARCH   |
| FORD  | 3000.00 | RESEARCH   |
+-------+---------+------------+

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

没有员工的部门，部门人数是0

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

	+--------+--------+-------------+----------------+
	| deptno | ecount | avgsal      | avgservicetime |
	+--------+--------+-------------+----------------+
	|     10 |      3 | 2916.666667 |        38.0000 |
	|     20 |      5 | 2175.000000 |        35.8000 |
	|     30 |      6 | 1566.666667 |        38.3333 |
	|     40 |      0 |    0.000000 |         0.0000 |
	+--------+--------+-------------+----------------+

【在mysql当中怎么计算两个日期的“年差”，差了多少年】？
	TimeStampDiff(间隔类型, 前一个日期, 后一个日期)
	
	timestampdiff(YEAR, hiredate, now())

	间隔类型：
		SECOND   秒，
		MINUTE   分钟，
		HOUR   小时，
		DAY   天，
		WEEK   星期
		MONTH   月，
		QUARTER   季度，
		YEAR   年

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

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

+--------+------------+---------+
| ename  | dname      | sal     |
+--------+------------+---------+
| CLARK  | ACCOUNTING | 2450.00 |
| KING   | ACCOUNTING | 5000.00 |
| MILLER | ACCOUNTING | 1300.00 |
| SMITH  | RESEARCH   |  800.00 |
| JONES  | RESEARCH   | 2975.00 |
| SCOTT  | RESEARCH   | 3000.00 |
| ADAMS  | RESEARCH   | 1100.00 |
| FORD   | RESEARCH   | 3000.00 |
| ALLEN  | SALES      | 1600.00 |
| WARD   | SALES      | 1250.00 |
| MARTIN | SALES      | 1250.00 |
| BLAKE  | SALES      | 2850.00 |
| TURNER | SALES      | 1500.00 |
| JAMES  | SALES      |  950.00 |
+--------+------------+---------+

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

select 
	d.*, count(e.ename)
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
group by
	d.deptno;

+--------+------------+----------+----------------+
| deptno | dname      | loc      | count(e.ename) |
+--------+------------+----------+----------------+
|     10 | ACCOUNTING | NEW YORK |              3 |
|     20 | RESEARCH   | DALLAS   |              5 |
|     30 | SALES      | CHICAGO  |              6 |
|     40 | OPERATIONS | BOSTON   |              0 |
+--------+------------+----------+----------------+

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

各种工作的最低工资：
select 
	job,min(sal) as minsal
from
	emp
group by
	job;

+-----------+----------+
| job       | minsal		|
+-----------+----------+
| ANALYST   |  3000.00 |
| CLERK     |   800.00 |
| MANAGER   |  2450.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  1250.00 |
+-----------+----------+

emp e和以上t连接

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

+--------+-----------+---------+
| ename  | job       | minsal  |
+--------+-----------+---------+
| SMITH  | CLERK     |  800.00 |
| WARD   | SALESMAN  | 1250.00 |
| MARTIN | SALESMAN  | 1250.00 |
| CLARK  | MANAGER   | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 5000.00 |
| FORD   | ANALYST   | 3000.00 |
+--------+-----------+---------+

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

select 
	deptno, min(sal)
from
	emp
where
	job = 'MANAGER'
group by
	deptno;

+--------+----------+
| deptno | min(sal) |
+--------+----------+
|     10 |  2450.00 |
|     20 |  2975.00 |
|     30 |  2850.00 |
+--------+----------+

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

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

+--------+----------+
| 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 |
| FORD   | 36000.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
+--------+----------+

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

select 
	a.ename '员工',b.ename '领导'
from
	emp a
join
	emp b
on
	a.mgr = b.empno
where
	b.sal > 3000;

+-------+------+
| 员工  | 领导 |
+-------+------+
| JONES | KING |
| BLAKE | KING |
| CLARK | KING |
+-------+------+

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

select 
	d.dname '部门',  ifnull(sum(e.sal),0)  '工资合计', count(e.empno) '人数'
from
	emp e
right join
	dept d
on
	e.deptno = d.deptno
where
	d.dname like '%S%'
group by
	d.deptno;

+------------+----------+------+
| 部门       | 工资合计| 人数 |
+------------+----------+------+
| RESEARCH   | 10875.00 |    5 |
| SALES      |  9400.00 |    6 |
| OPERATIONS |     0.00 |    0 |
+------------+----------+------+

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

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

传送门

上一章：MySql 数据库 - 第3章：约束、事务、索引、视图、范式

佬涛

关注

0
点赞
踩
6

收藏

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

分享到 QQ

分享到新浪微博

扫一扫

专栏目录

MySql 数据库 - 34道练习题

MySql 数据库 - 34道练习题

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

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

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

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

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

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

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

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

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

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

11、取得最后入职的 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%. **

传送门

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

19、列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数 ***

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

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

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