33道作业题MySQL

最新推荐文章于 2024-10-08 11:31:42 发布

123456lww

最新推荐文章于 2024-10-08 11:31:42 发布

阅读量39

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_51470924/article/details/131744450

版权

33道作业题MySQL

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

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

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

select deptno,avg(sal) as avgsal from emp group by deptno;按部门编号分组

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;

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

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

select 
	e.ename,e.sal,e.deptno,s.grade
from 
	salgrade s
join 
	emp e
on 
	e.sal between s.losal and  s.hisal
（order by deptno;按部门编号排序）
不需要将以上表看成临时表做表连接
select 
	e.deptno,avg(s.grade)
from 
	salgrade s
join 
	emp e
on 
	e.sal between s.losal and  s.hisal
group by
	e.deptno;

不准用函数max，取得最高薪水

第一种：sal降序，limit 1
select ename,sal from emp order by sal desc limit 1;

第二种：表的自连接
select distinct a.sal from emp a join emp b on a.sal < b.sal;查到所有小于最大值的工资，dintinct去重

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

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

第一种：
找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;

降序选第一个
select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;

第二种：
查出最高的平均薪水
select max(t.avgsal) from (select avg(sal) as avgsal from emp group by deptno) t;

查出部门编号
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);

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

降序排序，每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;

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

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

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

按部门分组升序排列，求出各个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal asc limit 1;

求出最低平均薪水对应的薪资等级
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 deptno,avg(sal) as avgsal from emp group by deptno) 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);

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

领导员工号（员工代码出现在mgr字段）
select distinct mgr from emp where mgr is not null;

普通员工的最高薪水
select max(sal) as maxsal from emp where empno not in(select distinct mgr from emp where mgr is not null);

select ename,sal from emp where sal > (select max(sal) as maxsal from emp where empno not in(select distinct mgr from emp where mgr is not null));

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

select deptno,ename,sal from emp order by sal desc limit 5;

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

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

取得最后入职的 5 名员工(日期可以降序也可以升序)

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

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

select s.grade,count(s.grade) from emp e join salgrade s on e.sal between s.losal and hisal group by s.grade order by s.grade asc;

面试题：
有 3 个表 S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
1，找出没选过“黎明”老师的所有学生姓名。
2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。
3，即学过 1 号课程又学过 2 号课所有学生的姓名。

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

select e1.empno,e1.ename '员工',e2.ename '领导' from emp e1 join emp e2 on e1.mgr = e2.empno;

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

select a.ename '员工',a.hiredate,b.ename '领导',b.hiredate,d.dname from emp a join emp b on a.mgr = b.empno and a.hiredate < b.hiredate join dept d on a.deptno = d.deptno;

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

右连接，以右边的表为基准
select e.*, d.dname from emp e right join dept d on e.deptno = d.deptno;

列出至少有 5 个员工的所有部门
按照部门编号分组，计数，筛选出 >= 5

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

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

select ename,sal from emp where sal > (select sal from emp where ename = 'SMITH');

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

求工作为办事员的姓名和部门名称
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno and e.job = 'CLERK';

求部门编号以及对应的部门人数
select deptno,count(deptno) as count_deptno from emp group by deptno;

select  e.ename,d.dname,e.deptno, t.count_deptno from emp e join dept d on e.deptno = d.deptno and e.job = 'CLERK' join (select deptno,count(deptno) as count_deptno from emp group by deptno) t on e.deptno = t.deptno;

列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数
按照工作岗位分组求最小值。

select job,count(job) from emp group by job having min(sal) > 1500;

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

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

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

select e.ename '员工',l.ename '领导',d.dname ,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);

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

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 e.ename != 'SCOTT';

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

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

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

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

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

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

select d.deptno,count(e.ename) as count_ename, 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;

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

timestampdiff(YEAR, hiredate, now())

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

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

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

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

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

select e.ename, t.job, t.minsal 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;

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

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

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

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

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

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

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

select d.*,ifnull(sum(e.sal),0) as sumsal,count(e.empno) 
from emp e right join dept d on e.deptno = d.deptno where d.dname like '%S%' group by deptno;

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

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

123456lww

关注

0
点赞
踩
0

收藏

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

分享到 QQ

分享到新浪微博

扫一扫