MySQL练习题

#1.取得每个部门最高薪水的人员名称
select deptno,max(sal) as maxsal from emp group by deptno #取得每个部门的最高薪水（按照部门编号分组，取最大值）

#将以上查询结果看做一张临时表t
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 e.sal = t.maxsal;
	
#2.哪些人的薪水在部门的平均薪水之上
select deptno,avg(sal) as avgsal from emp group by deptno; #找出每个部门的平均薪水
# 将以上查询结果当做t表，t和emp表连接
#条件:部门编号相同，并且emp的sal大于avgsal
select 
	e.ename,e.sal,t.*
from 
	emp e
join (select deptno,avg(sal) as avgsal from emp group by deptno) t
on
	t.deptno = e.deptno and e.sal > t.avgsal;

#3.取得部门中所有人的平均的薪水等级
select avg(sal) as avgsal from emp;
#第一步：找出每个人的薪水等级
#emp e和salgrade s表连接
#连接条件：e.sal between s.losal and s.hisal
select 
	e.ename,e.deptno,e.sal,s.grade
from 
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal
order by
	deptno ;

#第二步：基于以上结果继续按照deptno分组，求grade的平均值
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），取得最高薪水（给出两种解决方案）
#第一种方案：降序 limit1
select 
	ename,sal 
from 
	emp
order by 
	sal desc
limit 1;		

#第二种方案：表的自连接

/*
	a和b是同一张表  拿来做大于小于的比对的话  除了最大的那个值  其他的值是肯定会打印出来的
	最大工资不会小于b表的任何一个数据   所以去重之后他就不在表里了
	
	此时采用not in 输出的正好是最大的值
	
*/
select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal < b.sal);
select
	distinct	a.sal
from 
	emp a
join 
	emp b
on
	a.sal < b.sal
	
#5.取得平均薪资最高的部门编号
#第一种方案：

#第一步：找出每个部门的平均薪水
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;

#第二种方案：max
select max(t.avgsal) as 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) as avgsal from (select avg(sal) as avgsal from emp group by deptno)t);


#6.取得平均薪水最高的部门名称
select 
	d.dname,avg(e.sal) as avgsal
from
	emp e
join 
	dept d
on
	d.DEPTNO = e.DEPTNO
group by
	d.dname
order by
	avgsal desc
limit 
	1;

#7.求平均薪水的等级最低的部门的部门名称
#第一步：找出每个部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
#第二步：找出每个部门的平均薪水的等级
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
group by
	deptno;

#找出最低等级

#薪水最低，等级一定最低，但是薪水不是最低，等级也有可能是最低的
select avg(sal) as avgsal from emp group by deptno order by avgsal	limit 1;

select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by avgsal	limit 1) between LOSAL and hisal;

select
	t.* ,s.GRADE
from 
	(select d.dname,avg(sal) as avgsal from emp e join dept d on d.DEPTNO = e.DEPTNO group by dname) 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	limit 1) between LOSAL and hisal);
	
#8.取得比普通员工（员工代码没有在mgr字段上出现的）的最高薪水还要高的领导人姓名

select distinct mgr from emp where mgr is not null;
#员工编号没有出现在以上范围内的都是普通员工

#第一步：找出普通员工的最高薪水
#not in在使用的时候，后面的小括号中记得排除null
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);

#第二步：找出高于1600的
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 sal from emp order by sal desc limit 5; 

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

#11.取得最后入职的5名员工
select hiredate from emp order by hiredate desc;

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.有三个表 S（学生表） C（课程表） SC（学生选课表）
S(SNO,SNAME)  代表（学号，姓名）
C（CNO，CNAME，CTEACHER） 代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE） 代表 （学号，课号，成绩）

问题：
1.找出没选过“黎明”老师的所有学生姓名
2.列出2门以上（含两门）不及格学生姓名及平均成绩
3.既学过一号课程又学过2号课程的所有学生的姓名
*/

#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
left join
	emp b
on
	 a.mgr = b.empno
join
	dept d
on
	a.DEPTNO = d.deptno
where
	a.HIREDATE < b.HIREDATE;

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

#17.列出至少有五个员工的所有部门
#按照部门编号分组，计数，筛选出 >= 5
select
	deptno
from
	emp
group by
	deptno
having
	count(*) >= 5;
	
#18.列出薪金比“SMITH”多的所有员工信息
select sal from emp where ename = 'SMITH';

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

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

select ename,job from emp where job='CLERK';

select 
	e.ename,e.job,d.dname,d.deptno
from
	emp e
join
	dept d
on
	e.DEPTNO = d.DEPTNO
where
	e.job = 'CLERK';
	
#每个部门的人数
select deptno,count(*) as deptcount from emp group by deptno; 

select
	t1.*,t2.deptcount
from
	(select e.ename,e.job,d.dname,d.deptno from	emp e join 	dept d on e.DEPTNO = d.DEPTNO	where e.job = 'CLERK') t1
join
	(select deptno,count(*) as deptcount from emp group by deptno) t2
on
	 t1.deptno = t2.deptno;
 
 #20.列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
 #按照工作岗位分组求最小值
 select job,count(*) from emp group by job having min(sal) > 1500;

#21.列出在部门“SALES”<销售部>工作的员工姓名，假定不知道销售部的部门编号
select deptno from dept where dname = 'SALES';

select ename from emp where deptno = (select deptno from dept where 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 job FROM emp WHERE ename = '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 DISTINCT sal FROM emp WHERE deptno = 30;

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

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

#26.列出在每个部门工作的员工数量，平均工资和平均服务期限
#timestampdiff(时间间隔，前一个日期，后一个日期)
SELECT
	d.*,count(e.ename) ecount, ifnull(avg(e.sal),0) as avgsal,ifnull(avg(TIMESTAMPDIFF(YEAR,hiredate,now())),0) as avgyservicetime
FROM
	emp e
RIGHT JOIN
	dept d 
ON
	e.deptno = d.deptno
GROUP BY
	d.deptno,d.dname,d.loc;
	
#列出所有员工的姓名、部门名称和工资 
SELECT
	e.ename,d.dname,e.sal
FROM
	emp e
JOIN
	dept d
ON
	e.deptno = d.deptno;

#28.列出所有部门的详细信息和人数
SELECT
	d.deptno,d.dname,d.loc,count(e.ename)
FROM
	emp e
RIGHT JOIN
	dept d
ON
	e.deptno = d.deptno
GROUP BY
	d.deptno,d.dname,d.loc;

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

SELECT
	job,min(sal) as minsal
FROM
	emp
GROUP  BY
	job;
	
SELECT
	e.ename,t.*
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;

#30.列出各个部门的MANAGER的最低薪金
SELECT
	deptno,min(sal) as minsal 
FROM 
	emp
WHERE 
	job = 'MANAGER' 
GROUP BY 
	deptno;

#31.列出所有员工的年薪，按年薪从低到高排序
SELECT
	ename,(sal + IFNULL(comm,0)) * 12 as yearsal
FROM
	emp
order by
	yearsal asc;

#32.求出员工领导的薪水超过3000的员工名称与领导
SELECT
	a.ename '员工',b.ename '领导'
FROM
	emp a
JOIN
	emp b
ON
	a.mgr = b.empno
WHERE
	b.sal > 3000;

#求出部门名称中，带‘S’字符的部门员工的工资合计、部门人数
SELECT
	d.deptno,d.dname,d.loc,count(e.ename),IFNULL(sum(e.sal),0) as sumsal
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;

#34.给任职日期超过30年的员工加薪百分之10
update emp set sal = sal * 1.1 where TIMESTAMPDIFF(YEAR,hiredate,now()) > 30;
select * from emp;