MySQL

新鲜的大白菜
于 2020-12-12 23:26:13 发布
阅读量121
点赞数 1
分类专栏： MySQL
本文链接：https://blog.csdn.net/qq_45669199/article/details/111086943
版权
MySQL 专栏收录该内容
3 篇文章 0 订阅
订阅专栏
在这里插入图片描述
练习题
dept d
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

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

emp e
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

/* 1、取得每个部门最高薪水的人员名称
select 
	e.ename, e.sal, t.DEPTNO
from
(
	select 
		max(e.sal) as 'maxsal', e.DEPTNO
	from 
		emp e
	group by
		e.DEPTNO
) t
join
	emp e
on
	t.maxsal = e.sal; */
	
/* 2、哪些人的薪水在部门的平均薪水之上
select 
	e.ename, e.sal
from
	emp e
join
(
	select 
		e.DEPTNO as 'DEPTNO', avg(e.sal) as 'avg_sal'
	from
		emp e
	group by
		e.DEPTNO
) t
on 
	t.avg_sal <= e.sal and t.DEPTNO = e.DEPTNO; */

/* 3、取得部门中（所有人的）平均的薪水等级
select 
	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），取得最高薪水
select 
	e.sal
from
	emp e
order by
	e.sal desc
limit 
	1; */

/* 5、取得平均薪水最高的部门的部门编号
select
	DEPTNO
from
	emp e
group by
	e.DEPTNO
order by
	avg(e.sal) desc
limit
	1; */

/* 6、取得平均薪水最高的部门的部门名称
select 
	d.DNAME
from 
(
	select 
		e.DEPTNO as 'DEPTNO'
	from 
		emp e
	group by
		e.DEPTNO
	order by
		avg(e.sal) desc
	limit
		1
) t
join
	dept d
on 
	t.DEPTNO = d.DEPTNO; */

/* 7、求平均薪水的等级最低的部门的部门名称
select 
	d.DNAME
from
	salgrade s
join
(
	select
		DEPTNO, avg(e.sal) as 'avg_sal'
	from 
		emp e
	group by
		e.DEPTNO
)t
on
	t.avg_sal between s.LOSAL and s.HISAL
join
	dept d
on	
	d.DEPTNO = t.DEPTNO
order by
	s.GRADE asc
limit
	1; */

/* 8、取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名(not in)
select
	e.ename, e.sal
from
	emp e
join
(
	select
		distinct e.mgr as 'mgr'
	from
		emp e
) t
on
	e.EMPNO = t.mgr
where
	e.sal > ( select max(e.sal) from emp e where e.empno not in(select distinct e.mgr from emp e where e.mgr is not null) ) */

/* 9、取得薪水最高的前五名员工
select 
	e.ename, e.sal
from 
	emp e
order by
	e.sal desc
limit
	0, 5; */

/* 10、取得薪水最高的第六到第十名员工
select 
	e.ename, e.sal
from 
	emp e
order by
	e.sal desc, ename asc
limit
	5, 5; */

/* 11、取得最后入职的5名员工
select 
	e.ename, e.HIREDATE
from 
	emp e
order by
	HIREDATE desc
limit
	0, 5; */

/* 12、取得每个薪水等级有多少员工
select 
	t.GRADE as 'grade', count(t.GRADE) as 'count<*>'
from
(
	select
		e.ename as 'ename', s.GRADE as 'GRADE'
	from
		emp e
	join
		salgrade s
	on 
		e.sal between s.LOSAL and s.HISAL
) t
group by
	t.GRADE; */

/* 13、面试题
c
+------+-------+----------+
| CNO  | CNAME | CTEACHER |
+------+-------+----------+
| 1    | 语文  | 张       |
| 2    | 政治  | 王       |
| 3    | 英语  | 李       |
| 4    | 数学  | 赵       |
| 5    | 物理  | 黎明     |
+------+-------+----------+

s
+------+-------+
| SNO  | SNAME |
+------+-------+
| 1    | 学生1 |
| 2    | 学生2 |
| 3    | 学生3 |
| 4    | 学生4 |
+------+-------+

sc;
+------+------+---------+
| SNO  | CNO  | SCGRADE |
+------+------+---------+
| 1    | 1    | 40      |
| 1    | 2    | 30      |
| 1    | 3    | 20      |
| 1    | 4    | 80      |
| 1    | 5    | 60      |
| 2    | 1    | 60      |
| 2    | 2    | 60      |
| 2    | 3    | 60      |
| 2    | 4    | 60      |
| 2    | 5    | 40      |
| 3    | 1    | 60      |
| 3    | 3    | 80      |
+------+------+---------+

有3个表S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）  
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题： */
/* 13.1，找出没选过“黎明”老师的所有学生姓名
select
	s.sname
from
	s s
where
	s.SNO not in(select sc1.SNO as 'sno' from sc sc1 where sc1.CNO = (select CNO from c where CTEACHER = '黎明')) */
	
/* 13.2，列出2门以上（含2门）不及格学生姓名及平均成绩
select
	t2.sname, avg(sc.SCGRADE)
from
	sc sc
join
(
	select
		s.sname as 'sname', s.sno as 'sno'
	from
		s s
	join
	(
		select
			sc.SNO as 'sno', count(sc.CNO) as 'count_cno'
		from
			sc sc
		where
			sc.SCGRADE < 60
		group by
			sc.SNO
		having
			count(sc.CNO) >= 2
	) t
	on
		t.SNO = s.SNO
) t2
on
	t2.sno = sc.sno */
	
/* 13.3，即学过1号课程又学过2号课所有学生的姓名
select
	s.sname
from
	s s
join
(
	select
		t1.sno as 'sno'
	from
	(
		select
			sc.SNO as 'sno'
		from
			sc sc
		where
			sc.cno = 1
	) t1
	join
	(
		select
			sc.SNO as 'sno'
		from
			sc sc
		where
			sc.cno = 2
	) t2
	on
		t1.sno = t2.sno
) t3
on
	t3.sno = s.sno */


/* 14、列出所有员工及领导的姓名
select
	e.ename, e2.ename
from
	emp e
left join
	emp e2
on
	e.mgr = e2.empno; */

/* 15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
select
	t.empno, t.ename, d.DNAME
from
	dept d
join
(
	select
		e.EMPNO as 'empno', e.ENAME as 'ename', e.DEPTNO as 'deptno'
	from
		emp e
	join
		emp e2
	on
		e.MGR = e2.EMPNO
	where
		e.HIREDATE < e2.HIREDATE
)t
on
	t.deptno = d.DEPTNO; */

/* 16、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
select 
	d.DNAME, e.*
from
	dept d
left join
	emp e
on
	d.DEPTNO = e.DEPTNO */

/* 17、列出至少有5个员工的所有部门
select 
	d.DNAME, t.cnt
from
	dept d
join
(
	select
		DEPTNO as 'DEPTNO', count(e.DEPTNO) as 'cnt'
	from
		emp e
	group by
		e.DEPTNO
	having
		count(e.DEPTNO) >= 5
)t
on
	d.DEPTNO = t.DEPTNO; */

/* 18、列出薪金比"SMITH"多的所有员工信息
select
	e.*
from
	emp e
where
	e.sal > (select e.sal from emp e where e.ename = 'SMITH'); */

/* 19、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数
select 
	t.name, d.DNAME, t2.cnt_DEPTNO
from
(
	select 
		e.ename as 'name', e.DEPTNO as 'DEPTNO'
	from
		emp e
	where 
		e.JOB = 'CLERK'
) t
join
	dept d
on
	d.DEPTNO = t.DEPTNO
join
(
	select
		count(e.DEPTNO) as 'cnt_DEPTNO', e.DEPTNO as 'DEPTNO'
	from
		emp e
	group by
		e.DEPTNO
) t2
on
	t2.DEPTNO = t.DEPTNO */

/* 20、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
select
	t1.job, t2.cnt_job 
from
(
	select
		t.job, t.min_sal
	from
	(
		select 
			e.job as 'job', min(e.sal) as 'min_sal'
		from
			emp e
		group by
			e.job
	) t
	where
		t.min_sal > 1500
) t1
join
(
	select
		e.job as 'job', count(e.JOB) as 'cnt_job'
	from
		emp e
	group by
		e.JOB
) t2
on
	t1.job = t2.job; */

/* 21、列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号
select
	e.ENAME
from
	emp e
where
	DEPTNO = (select d.DEPTNO from dept d where d.DNAME = 'SALES') */

/* 22、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级
select
	t.ename, d.DNAME, e.ename, s.GRADE
from
(
	select
		e.ename as 'ename', e.DEPTNO as 'DEPTNO', e.MGR as 'MGR', e.sal as 'sal'
	from
		emp e
	where
		e.sal > (select avg(e.sal) from emp e)
) t
left join
	dept d
on
	t.DEPTNO = d.DEPTNO
left join
	emp e
on
	t.MGR = e.EMPNO
left join
	salgrade s
on
	t.sal between s.LOSAL and HISAL; */

/* 23、列出与"SCOTT"从事相同工作的所有员工及部门名称
select
	t.ename, d.DNAME
from
(
	select
		e.ENAME as 'ENAME', e.DEPTNO as 'DEPTNO'
	from
		emp e
	join
	(
		select 
			e.job as 'job', e.ename as 'ename'
		from 
			emp e 
		where 
			e.ENAME = 'SCOTT'
	) t
	on
		t.job = e.job and e.ename != 'SCOTT'
) t
join
	dept d
on
	t.DEPTNO = d.DEPTNO */

/* 24、列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金
select 
	e.ename, e.sal
from
	emp e
join
(
	select
		distinct e.sal as 'sal'
	from
		emp e
	where
		e.DEPTNO = 30
) t
on 
	e.DEPTNO != 30 and e.sal = t.sal; */

/* 25、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称
select 
	t.ename, t.sal, d.DNAME
from
(
	select
		e.ename as 'ename', e.sal as 'sal', e.DEPTNO as 'DEPTNO'
	from
		emp e
	where
		e.sal > (select max(e.sal) from emp e where e.DEPTNO = 30) and e.DEPTNO != 30
) t
join
	dept d
on
	d.DEPTNO = t.DEPTNO; */

/* 26、列出在每个部门工作的员工数量,平均工资和平均服务期限(★★DATEDIFF函数)
select
	e.DEPTNO, count(e.DEPTNO), avg(e.sal), avg(DATEDIFF('2020-12-13',e.HIREDATE))
from
	emp e
group by
	e.DEPTNO */

/* 27、列出所有员工的姓名、部门名称和工资
select
	e.ename, d.DNAME, e.sal
from
	emp e
join
	dept d
on
	d.DEPTNO = e.DEPTNO; */

/* 28、列出所有部门的详细信息和人数
select 
	d.*, t.cnt_DEPTNO
from
	dept d
left join
(
	select
		e.DEPTNO as 'DEPTNO', count(e.DEPTNO) as 'cnt_DEPTNO'
	from
		emp e
	group by
		e.DEPTNO
) t
on
	t.DEPTNO = d.DEPTNO */

/* 29、列出各种工作的最低工资及从事此工作的雇员姓名
select 
	t.job, t.min_sal, e.ename
from
	emp e
join
(
	select
		e.JOB as 'job', min(e.sal) as 'min_sal'
	from
		emp e
	group by
		e.JOB
) t
on
	t.job = e.job and t.min_sal = e.sal; */

/* 30、列出各个部门的MANAGER(领导)的最低薪金
select
	t.ENAME, min(t.sal), t.DEPTNO
from
(
	select
		distinct e2.EMPNO as 'EMPNO', e2.ENAME as 'ENAME', e2.sal as 'sal', e2.DEPTNO as 'DEPTNO'
	from
		emp e1
	join
		emp e2
	on
		e1.MGR = e2.EMPNO
) t
group by
	t.DEPTNO; */

/* 31、列出所有员工的年工资,按年薪从低到高排序
select
	e.ename, (e.sal + ifnull(e.COMM, 0))*12 as 'year_sal'
from
	emp e
order by
	year_sal asc */
	
/* 32、求出员工领导的薪水超过3000的员工名称与领导名称
select	
	e.ename, t.ename
from
	emp e
join
(
	select 
		distinct e2.ename as 'ename', e2.EMPNO as 'EMPNO', e2.sal
	from
		emp e1
	join
		emp e2
	on
		e1.mgr = e2.EMPNO
	where
		e2.sal > 3000
) t
on 
	e.mgr = t.EMPNO; */

/* 33、求出部门名称中,带'S'字符的部门员工的工资合计、部门人数
select
	t1.DNAME, t2.sum_sal, t2.cnt_ename
from
(
	select 
		d.DEPTNO as 'DEPTNO', d.DNAME as 'DNAME'
	from
		dept d
	where
		d.DNAME like '%S%'
) t1
left join
(
	select
		e.DEPTNO as 'DEPTNO', sum(e.sal) as 'sum_sal', count(e.ename) as 'cnt_ename'
	from
		emp e
	group by
		e.DEPTNO
) t2
on
	t1.DEPTNO = t2.DEPTNO; */

/* 34、给任职日期超过30年的员工加薪10%
drop table if exists emp1;
create table emp1 as select * from emp;

update emp1
	set sal=sal*1.1
where
	HIREDATE < '1990-12-13'; */
新鲜的大白菜
关注
1
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySQL

练习题dept d+--------+------------+----------+| DEPTNO | DNAME | LOC |+--------+------------+----------+| 10 | ACCOUNTING | NEW YORK || 20 | RESEARCH | DALLAS || 30 | SALES | CHICAGO || 40 | OPERATIONS | BOSTON |.
复制链接

扫一扫
专栏目录