在B站学习SQL---动力节点34道题

一、之前对sql的操作仅仅存在低级的CRUD语句

于是去B站学习了动力节点的34道题，差不多用了一天时间。
因为除了做出来，还要学会理解其含义

B站动力节点SQL34道题学习视频链接

二、以下是建表语句和我手写的SQL语句

数据库名：bjpowernode
建表sql 百度云链接
链接：https://pan.baidu.com/s/1YtPor787nuDTgDY_VJsaSQ
提取码：1234

#1.取得各部门最高薪水（员工名 最高薪水 部门）-难点最高薪水可能不止一人
select e.ename,t.*
from emp e
join (SELECT max(sal) as maxsal,DEPTNO from emp GROUP BY DEPTNO) t
on
e.SAL=t.maxsal and e.DEPTNO=t.DEPTNO

#2.哪些人的薪水在部门平均薪水之上
select t.*,ename,sal,emp.DEPTNO from emp
join (select deptno,avg(sal) as avgsal from emp group by deptno) t
on emp.sal>t.avgsal and emp.DEPTNO=t.DEPTNO

#3.取得部门中所有人的 平均薪水的等级
select e.DEPTNO,avg(s.grade)
from emp e
join salgrade s
on e.SAL BETWEEN s.LOSAL and s.HISAL 
GROUP BY DEPTNO

#4.不用组函数取得最高薪水s
#使用降序 limit 1
select ENAME,sal from emp ORDER BY sal desc limit 1
#使用max()
#表的自连接
select sal from emp
where sal not in(
select distinct a.sal from emp a
join emp b
on a.SAL<b.SAL)


#5.取得平均薪水最高的部门的部门编号
方案一：降序取第一个
select avg(sal),deptno 
from emp 
group by deptno
ORDER BY avg(sal) desc
limit 1
方案二：使用max
select t.deptno,max(t.avgsal) as avgsal
from 
(select deptno,avg(sal) avgsal from emp group by deptno) t
方案三：使用having
select deptno,avg(sal) as avgsal
from emp
group by deptno
having 
avgsal = (select max(t.avgsal) as avgsal 
from 
(select deptno,avg(sal) avgsal from emp group by deptno) t)


#6.取得平均薪水最高的部门名称
select a.deptno,avg(a.sal),b.dname
from emp a
join dept b
on a.deptno=b.deptno
group by a.deptno
order by avg(a.sal) desc
limit 1

#7.求平均薪水的等级最低的部门的部门名称 难点：不能根据薪水去取最小值 因为可能多个薪水都处于最小级别 要先求出最小级别
#第一步找出每个部门的平均薪水等级
select t.*,s.grade,d.dname from salgrade s
join
(select deptno,avg(sal) as avgsal from emp group by deptno) t
join dept d
on t.avgsal between s.LOSAL and s.HISAL
and t.deptno=d.deptno
where grade=
(
select grade from salgrade a
join
(select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal limit 1) b
on b.avgsal between a.LOSAL and a.HISAL
)

#8.取得比普通员工（员工代码没有在mgr字段上出现的）的最高薪水还高的领导人姓名
select * from emp
where empno in(select distinct mgr from emp where mgr is not null)
having sal>
(select max(sal) maxsal from emp 
where empno not in(select distinct mgr from emp where mgr is not null)
)

#9.取得薪水最高的前五名
select * from emp 
order by sal desc
limit 5

#10.取得薪水最高的第六到第十名员工
select * from emp 
order by sal desc
limit 5,5

#11.取得最后入职的五名员工
select * from emp
ORDER BY HIREDATE desc
limit 5

#12.取得每个薪水等级有多少员工
select s.grade,count(e.empno)
from emp e
join salgrade s
on e.sal between s.LOSAL and s.HISAL
group by s.grade

#-------------学生信息表查询
#13、面试题:
-- 有3个表s(学生表)，C(课程表)，SC(学生选课表)
-- s (SNO， SNAME)代表(学号，姓名)
-- c (CNO， CNAME， CTEACHER) 代表(课号，课名，教师)
-- sc (sNO， CNO， SCGRADE) 代表(学号，课号，成绩)
-- 1，找出没选过黎明"老师的所有学生姓名。
-- 2，列出2门以上(含2门)不及格学生姓名及平均成绩。
-- 3，即学过1号课程又学过2号课所有学生的姓名。




#14.列出所有员工及领导的姓名
select a.ename "员工",ifnull(b.ename,'the boss') "领导"
from emp a
left join emp b
on a.mgr=b.empno

#15.列出受雇日期早于其直接上级的所有员工的编号，姓名，部门名称
select a.empno,a.ename,c.dname,a.HIREDATE, b.ENAME "领导",b.HIREDATE "领导入职" from emp a
join emp b
on a.HIREDATE<b.HIREDATE and a.mgr=b.empno
join dept c
on a.deptno=c.deptno

#16.列出部门名称和这些部门的员工信息，同时列出哪些没有员工的部门
select a.*,b.*
from dept a
left join emp b
on b.deptno=a.deptno
order by a.DEPTNO

#17.列出至少有五个员工的所有部门
select deptno,count(*) from emp
group by deptno
having(count(*)>=5)

select a.*,count(b.empno) as emptotal
from dept a
left join emp b
on b.deptno=a.deptno
group by a.deptno
having(count(b.empno)>=5)


#18.列出薪金比“SMITH”多的所有员工信息
select * from emp
where sal>(select sal from emp where ename='SMITH')

#19.列出所有“CLERK”职位办事员的姓名及其部门名称，部门的人数  *****重点
select a.ename,a.job,b.dname,b.deptno,c.totlal
from emp a
join dept b
on a.deptno=b.deptno and a.job="CLERK"
left join (select deptno,count(*) as totlal from emp group by deptno) c
on a.DEPTNO=c.DEPTNO

#20.列出最低薪金大于1500的各种工作，及从事此工作的全部雇员人数按照工作岗位分组求最小值
select job,count(*) 
from emp 
group by job
having(min(sal)>1500)

select a.minsal,a.job,a.total from (select min(sal) minsal,job,count(*) as total from emp group by job) a
where minsal>1500

#21.列出在部门“SALES”销售部 工作的员工姓名 假定不知道销售部的部门编号
select ename from emp a
join (select deptno from dept where dname='SALES') b
on a.DEPTNO=b.DEPTNO

#22.列出薪金高于平均薪金的所有员工，所在部门，上级领导，雇员的工资等级
select a.ename,b.dname,c.ename "领导",d.grade
from emp a
join dept b
on a.deptno=b.deptno
left join emp c
on a.mgr=c.empno
join salgrade d
on a.sal between d.LOSAL and d.HISAL
join (select avg(sal) as sumsal from emp) e
on a.sal>e.sumsal

#23.列出与“SCOTT”从事相同工作的所有员工及部门名称
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"

select a.*,b.dname from
(select * from emp where job=(select job from emp where ename="SCOTT")) a
join dept b
on a.deptno=b.deptno
and a.ename <> "SCOTT"

#24.列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金
select ename,sal from emp 
where sal in(select distinct sal from emp where deptno=30)

#25.列出薪金高于在部门30中的所有员工的薪金的员工姓名和薪金 部门名称
select a.ename,a.sal,b.dname
from emp a
join dept b
on a.deptno=b.deptno
where a.sal>(select max(sal) from emp where deptno=30)

#26.列出在每个部门工作的员工数量，平均工资和平均服务期限
#timestampdiff(类型，开始时间，结束时间)
select b.*,count(a.ename),ifnull(avg(a.sal),0),avg(timestampdiff(year,a.hiredate,now())) "平均服务期限"
from emp a
right join dept b
on a.deptno=b.deptno
group by b.deptno

#
select b.*,count(a.ename),ifnull(avg(a.sal),0),avg(year(curdate())-year(a.hiredate)) "平均服务期限"
from emp a
right join dept b
on a.deptno=b.deptno
group by b.deptno

#日期转换DATE_FORMAT(日期,格式) 
#大写Y：2021 小写y：21 | 大写M：April 小写m：04 | 大写D：6th 小写d：06
select DATE_FORMAT(now(),'%Y-%m-%d')

#27.列出所有员工的姓名，部门名称和工资
select a.ename,b.dname,a.sal
from emp a
join dept b
on a.deptno=b.deptno

#28.列出所有部门的详细信息和人数
select a.*,count(b.empno)
from dept a
left join emp b
on a.deptno=b.deptno
group by a.deptno

#29.列出各种工作的最低工资及从事此工作的雇员姓名
select a.ename,a.job,a.sal from emp a
join (select min(sal) minsal from emp group by job) b
on a.sal=b.minsal

#30.列出各个部门的MANAGER(领导)的最低薪金
select deptno,min(sal)
from emp
where job="MANAGER"
group by deptno

-- #外加列出各个部门的非普通员工的最低薪金
-- select ename,deptno,min(sal) from emp 
-- where empno in(select distinct mgr from emp where mgr is not null)
-- group by deptno
-- 
-- #离谱有问题
-- select a.deptno,a.empno,a.ename,max(a.sal) from emp a
-- join (select distinct mgr from emp where mgr is not null) b
-- on a.empno=b.mgr
-- group by a.deptno


#31.列出所有员工的年工资 按年薪从低到高排序
select ename,(sal+ifnull(comm,0))*12 as yearsal
from emp
order by yearsal

#32.求出员工领导的薪水超过3000 的员工名称与领导
select a.ename,b.ename "领导" from emp a
join emp b
on a.mgr=b.empno
where b.sal>3000

#33.求出部门名称中，带‘S’字符的部门员工的工资合计、部门人数
select a.deptno,a.dname,sum(b.sal),count(b.ename) 
from dept a
left join emp b
on a.deptno=b.deptno
where a.dname like "%s%"
group by b.deptno

select d.deptno,d.dname,d.loc,count(e.ename),sum(e.sal)
from emp e
right join dept d
on e.deptno=d.deptno
where d.dname like "%s%"
group by d.deptno

select a.deptno,sum(a.sal),count(*) from emp a
join (select deptno from dept where dname like "%S%") b
on a.deptno=b.deptno
group by a.deptno

#34.给任职日期超过30年的员工加薪10%
update emp set sal=sal*1.1
where curdate()-hiredate>30

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

个人学习如有错误还请指出 感谢！