老杜34道数据库作业题

最新推荐文章于 2023-07-13 11:08:13 发布

无所事事的小窝

最新推荐文章于 2023-07-13 11:08:13 发布

阅读量928

点赞数

文章标签：数据库安全 sql

本文链接：https://blog.csdn.net/zy3256/article/details/127214700

版权

你可以随时转身，但不能一直后退

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

取得每个部门的最高薪水（按照部门编号分组）
在emp中找人员（谁的薪水等于最高薪水），1中的临时表t与emp表连接条件是 e.deptno = t.deptno and t.maxsal = e.sal;(我写的时候没有前面的条件，说明我考虑连接条件的时候存在漏洞)

select 
	e.ename,t.*
from 
	emp e 
join 
	(select deptno ,max(sal)  maxsal from emp group by deptno) t 
on 
	e.deptno = t.deptno and e.sal=t.maxsal;

在这里插入图片描述

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

先找出每个部门的平均薪水（按照部门分组求平均薪水）
上查询结果作为 t表，与 emp表连接，连接条件是 e.deptno = t.deptno and e.sal > avgsal;

select
	 t.*,e.ename,e.sal 
from 
	emp e 
join
	 (select deptno,avg(sal) avgsal from emp group by deptno) t 
on 
	e.sal > t.avgsal and e.deptno = t.deptno;

在这里插入图片描述

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

平均的薪水等级 :先计算每一个薪水的等级，找出薪水等级的平均值
平均薪水的等级：找出每个部门的平均薪水，再求等级

要注意group by 分组后，前面只能查询 group by 后面的字段和分组函数，它是有要求的！！！！

select 
	deptno,avg(grade) avggrade
from 
	emp 
join 
	salgrade 
on 
	sal between losal and hisal
group by
	deptno;

在这里插入图片描述

不使用组函数max求得最高薪水（给出两种方案）

第一种：降序取第一个
第二种：用max
第三种：采用表的自连接,
select distinct e.sal from emp e join emp r on e.sal< r.sal ;

select 
	ename,sal 
from 
	emp 
where 
	sal not in (select distinct e.sal from emp e join emp r on e.sal< r.sal);

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

方案一：

找出每个部门的平均薪水+编号
取得平均薪水的最大值第一步降序取头部
方案二·
找出每个部门的平均薪水+编号
通过max 求最大值

//方案一：
select 
	deptno,avg(sal)avgsal
from 
	emp 
group by
	deptno
order by
	avgsal desc
limit 1;

//方案二：
select 
	deptno ,t.avgsal
from 
	(select deptno,avg(sal) avgsal from emp group by deptno) t 
where 
	avgsal = (select max(avgsal) maxavgsal from (select avg(sal) avgsal from emp group by deptno)t) ;

//同样是方案二：这是网课上老师的
select 
	deptno,avg(sal) avgsal
from 
	emp 
group by
	deptno
having 
	avgsal = (select max(avgsal) maxavgsal from (select avg(sal) avgsal from emp group by deptno)t) ;

max(avgsal) 不可以的
在这里插入图片描述
6. 求取得平均薪水最高的部门的部门名称（根据第5题连接dept这个表得到部门名称）

select 
	d.DNAME 
from 
	(select deptno,avg(sal) avgsal from emp group by deptno) t 
join 
	dept d 
on 
	t.deptno = d.deptno 
where 
	avgsal =(select max(avgsal) maxavgsal from (select avg(sal) avgsal from emp group by deptno)t) ;

在这里插入图片描述

求平均薪水的等级最低的部门的部门名称（第一个一定是平均薪水等级最低，但是第二个也有可能·，第三个也有可能，因为等级的判断是根据区间分的）

按照部门名称分组，找出每个部门的平均薪水
以上 t表和salgrade表连接，求得平均薪水等级
抛开之前找最低等级，升序找第一个，根据最低的平均薪水找最低等级
再通过grade = 最低等级；

//平均薪水最低的等级一定最低，但也存在平均薪水不是最低的等级最低


//鄙人的做法
select deptno,avg(sal) as avgsal from emp group by deptno;
select  min(avgsal)as minavgsal from (select deptno,avg(sal) as avgsal from emp group by deptno)t ;
select deptno from (select deptno,avg(sal) as avgsal from emp group by deptno)t where avgsal = 1566.666667;
select dname from dept where deptno = 30;

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

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

select distinct mgr from emp; 可以找出领导的员工编号，那么普通员工就是 not in ( select distinct mgr from emp)
找出普通员工的最高薪水
找出高于最高薪水的领导

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

在这里插入图片描述

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

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

在这里插入图片描述

取得薪水最高的第6到第10

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

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

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

在这里插入图片描述

求每个薪水等级有多少员工

找出每个员工的薪水等级
根据薪水等级分组，通过count 统计员工人数

select 
	s.grade,count(grade) as countGrade   //因为后面有分组，所以这里只能有 分组字段 以及分组函数
from
	 emp e 
join 
	salgrade  s 
on
	 e.sal between s.losal and s.hisal  
group by 
	s.grade;

在这里插入图片描述

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

select 
	e.ename '员工',m.ename '领导'
from 
	emp e
left join 
	emp m
on 
	e.mgr = m.empno;

在这里插入图片描述

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

自连接

select 
	e.ename '员工',e.hiredate, m.ename '领导',m.hiredate,d.dname
from 
	emp e
left join 
	emp m
on 
	e.mgr = m.empno
join 
	dept d
on 
	e.deptno = d.deptno
where 
	e.hiredate<m.hiredate;

在这里插入图片描述

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

SELECT
	e.*,
	d.*
FROM
	emp e
	RIGHT JOIN dept d ON e.deptno = d.deptno;

在这里插入图片描述

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

我的想法太过复杂，明明可以直接连接dept表，然后再分组，而我居然将连接的表弄成一个中间表t 明显看起来很复杂

SELECT
	dname,
	count( *) count
FROM
	emp e
	JOIN dept d ON e.deptno = d.deptno 
GROUP BY
	d.dname 
HAVING
	count >= 5;

在这里插入图片描述
16. 列出薪资比”SMITH“多的所有员工信息

select 
	* 
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
where 
	job = 'Clerk';

//每个部门人数
select deptno,count(*) deptcount from emp group by deptno;	

//两张表联合起来
SELECT
	t1.*,
	t2.deptcount 
FROM
	(
	SELECT
		e.ename,
		e.deptno,
		d.dname 
	FROM
		emp e
		JOIN dept d ON e.deptno = d.deptno 
	WHERE
		job = 'Clerk' 
	) t1
	JOIN ( SELECT deptno, count(*) deptcount FROM emp GROUP BY deptno ) t2 ON t1.deptno = t2.deptno;

在这里插入图片描述

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

同样可以先用where 将1500的过滤掉，**后面反应过来where 执行的时候还没有minsal呢！！！**所以用where 一开始过滤是不行的

SELECT
	job,
	min( sal ) AS minsal,
	count(*) AS count  //求从事此工作的所有雇员人数
FROM
	emp 
GROUP BY
	job 
HAVING
	minsal > 1500;

在这里插入图片描述

列出部门在"SALES"（销售部）工作的员工的姓名，假定不知道销售部的部门编号

select deptno from dept where dname = 'SALES';  //找出销售部的部门编号
select 
	ename 
from 
	emp 
where 
	deptno = (select deptno from dept where dname = 'SALES');

在这里插入图片描述

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


SELECT
	e.ename AS '员工',
	d.dname AS '部门名称',
	s.grade AS '工资等级',
	m.ename '领导' 
FROM
	emp e
JOIN 
	dept d 
ON 
	e.deptno = d.deptno
JOIN 
	salgrade s 
ON 
	e.sal BETWEEN s.losal AND s.hisal
JOIN 、
	emp m 
ON 
	e.mgr = m.empno 
WHERE
	e.sal > ( SELECT avg( sal ) avgsal FROM emp );

在这里插入图片描述

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

select 
	e.ename,d.dname 
from 
	emp e 
join 
	dept d 
on
	 e.deptno =d.deptno 
 where 
 	job = (select job from emp where ename = 'scott') and ename != 'scott';

在这里插入图片描述

列出薪金等于部门30中员工的薪水的其他员工的姓名及员工（很奇怪）

SELECT DISTINCT
	sal 
FROM
	emp 
WHERE
	deptno = 30;
SELECT
	ename,
	sal 
FROM
	emp 
WHERE
	sal IN ( SELECT ename, deptno, sal FROM emp WHERE deptno = 30 ) 
	AND deptno != 30;

列出薪金高于部门30中员工的薪水的其他员工的姓名及员工

select max(sal)  from emp where deptno = 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)and e.deptno!=30;

在这里插入图片描述

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

注意：40部门没有一个员工，请问如何处理
平均服务期限：系统当前年份 - 入职年份，TimeStampDiff(间隔类型，前一个日期，后一个日期)
间隔类型：年，月，日，季度，星期

select 
	d.deptno,count(e.ename) as '员工数量',ifnull(avg(e.sal),0),ifnull(avg(timestampdiff(year,e.hiredate,now())),0) 
from
	emp e  
right join 
	dept d
on 
	e.deptno = d.deptno
group by 
	d.deptno;

在这里插入图片描述

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

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

在这里插入图片描述

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

SELECT
	d.*,
	t.`员工数量` 
FROM
	dept d
	LEFT JOIN ( SELECT deptno, count( ename ) AS '员工数量', avg( sal ) AS '平均工资' FROM emp e GROUP BY deptno ) t ON d.deptno = t.deptno;

在这里插入图片描述

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

对于如何找员工有点奇怪

SELECT
	e.empno,
	e.ename,
	e.job,
	e.mgr,
	e.hiredate,
	e.sal,
	e.comm,
	e.deptno 
FROM
	emp e
	JOIN ( SELECT job, min( sal ) minsal FROM emp GROUP BY job ) t ON e.sal = t.minsal;

在这里插入图片描述

列出每个部门的MANAGER （领导）的最低薪金（？？？？？）

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

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

在这里插入图片描述

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

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

在这里插入图片描述
31. 求出部门名称中带"S"字符的部门员工的工资合计、部门人数（为啥 right join 它是错的）

加where 条件，不用having
而且一定要考虑分组函数那什么自动忽略NULL，且所有运算和null做运算都是NULL，并且补充一点，如果某个值要等于NULL，不能直接 = NULL ,要 is NULL；

SELECT
	d.deptno,
	d.dname,
	d.loc,
	ifnull( sum( e.sal ), 0 ),       
	count( e.ename ) 
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;

在这里插入图片描述

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

update emp set sal = sal *1.1 where  timestampdiff(year,hiredate,now());

在这里插入图片描述

无所事事的小窝

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
老杜34道数据库作业题

老杜 34 道数据库作业题（没有面试题，以及某一个类似的题目）
复制链接

扫一扫

老杜34道数据库作业题

“相关推荐”对你有帮助么？