22.MySQL练习作业

咸鱼2022-09-192024-08-03

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

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;

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

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

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

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;

4.不准用组函数（max），取得最高薪水（给出两种解决方案）

第一种：sal降序，limit 1

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

第二种：表的自连接

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

5.取的平均薪水最高的部门的部门编号(两种)

第一种：max

select
	deptno,avg(sal)
from
	emp
group by
	deptno
having
	avg(sal) = (select
	max(t.avgsal)
from
	(select
    	deptno,avg(sal) avgsal
    from
    	emp
    group by
    	deptno) t);

第二种：排序

select
	deptno,avg(sal) avgsal
from
	emp
group by
	deptno
order by
	avgsal desc
limit
	1;

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

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

7.求平均薪水的等级最高的部门的部门名称

第一部分：选出最高的薪水等级
select
	s.grade
from
	salgrade s
join
	(select
	avg(sal) avgsal
from
	emp
group by
	deptno
order by
	avgsal desc
limit
	1) t
on
	t.avgsal between s.losal and hisal;
	
第二部分：给每个部门连接上薪水等级并连接上部门名称	
select
	d.dname,s.grade
from
	(select
	e.deptno,avg(e.sal) avgsal
from
	emp e
group by
	deptno) t
join
	salgrade s
on
	t.avgsal between s.losal and hisal
join
	dept d
on
	d.deptno = t.deptno;



第三部：将前两步连接，选出所有最高的等级
select
	tt.dname,tt.grade
from
	(select
	d.dname,s.grade
from
	(select
	e.deptno,avg(e.sal) avgsal
from
	emp e
group by
	deptno) t
join
	salgrade s
on
	t.avgsal between s.losal and hisal
join
	dept d
on
	d.deptno = t.deptno) tt
join
	(select
	s.grade
from
	salgrade s
join
	(select
	avg(sal) avgsal
from
	emp
group by
	deptno
order by
	avgsal desc
limit
	1) t
on
	t.avgsal between s.losal and hisal) ttt
on
	tt.grade = ttt.grade;

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

第一步：找出普通员工的最高薪水
select
	max(sal)
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)
from
	emp
where
	empno not in (select distinct mgr from emp where mgr is not null));

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

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

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

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

11.取得最后入职的5名员工（日期也可以降序，升序）

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

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

第一步：找出每个员工的薪水等级
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal
第二步：继续按照grade分组统计数量
select 
	s.grade,count(*)
from 
	emp e 
join 
	salgrade s 
on 
	e.sal 
between 
	s.losal and s.hisal
group by
	s.grade;

13.面试题：

14.列出所有员工及领导的名字

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

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

select
	a.ename '员工',a.hiredate,b.ename '领导',b.hiredate,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;

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

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

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

select
	e.deptno,d.dname,count(*)
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
group by
	deptno
having
	count(*)>=5;

18.列出薪水比“SMITH”多的员工信息

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

19.列出所有“CLERK”（办事员）的姓名及其部门名称，部门人数

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

2.t2
select
	deptno,count(*) deptcount
from
	emp
group by
	deptno

3.
select
	t1.*,t2.deptcount
from
	(select
	e.ename,e.job,d.dname,e.deptno
from
	emp e
join
	dept d
on
	e.deptno = d.deptno
where
	e.job = 'CLERK') t1
join
	(select
	deptno,count(*) deptcount
from
	emp
group by
	deptno) t2
on
	t1.deptno = t2.deptno;

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

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

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

select
	e.ename,d.dname
from
	emp e
join
	dept d
on
	e.deptno = d.deptno and d.dname = 'SALES'

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)

23.列出与“SCOTT”从事相同工作的所有员工及部门名称

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

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

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

25.列出薪金高于在部门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);

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

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

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

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

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

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

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

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

30.列出各个部门的MANAGER（领导）的最低薪资

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

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

select
	ename,(sal+ifnull(comm,0))*12 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
	d.dname,ifnull(sum(e.sal),0) sumsal,count(e.deptno)
from
	emp e
right join
	dept d
on
	d.deptno =e.deptno
where
	d.dname like '%S%'
group by
	d.deptno

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

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