MYSQ系列(3) 经典练习题上

MY SQL

三张表

emp 表， 字段empno, ename, job, mgr, hiredate, sal, comm, deptno

dept表 ， 字段 deptno, dname, loc

salgrade, 字段 grade, losal, hisal

第一题， 列出至少有五个员工的所有部门

1. 先求出每个部门的员工数量
2. 再加条件

# 先求每个部门的员工数量
select d.dname, count(e.empno) 
		e.deptno, count(e.empno) as sum_emp
from	
		emp e
group by e.deptno

# 然后就可以按照要求来进行连接和条件判断
select 

from 
		emp e join dept d on e.deptno = d.deptno
group by e.deptno
having count(e.empno) >5

第二题，列出受雇日期早于其上级的员工编号，姓名，部门名称

1. 核心方法是一张表多用，员工的领导编号是领导的员工编号，就可以人为的造出两张表格
2. 然后用条件判断大于领导编号

select
		e.empno, e.ename, e.deptno, d.dname 
from
		emp e join emp c 
on 		
		e.mgr = d.empno
		join dept d
on 
		e.deptno = d.deptno
where 
		e.hiredate < d.hiredate
		

第三题， 取得每个薪水等级共有多少个员工

1. 先求出员工的薪水等级
2. 把员工的薪水等级作为一个临时表，对这个临时表进行group by 和计数操作

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

第四题， 取得比普通员工的薪水还要高的经理人的姓名

 1.1 先找出普通员工
--  1.1.1 先找出经理人编号
SELECT distinct mgr from emp;
-- 1.1.2 员工编号不在经理人编号里的就是员工编号
select * from emp where empno not in
																		(SELECT distinct mgr from emp where mgr is not null);
-- 1.2 查找出普通员工的最大薪资
select max(sal) as max_sal from emp where empno not in
																		(SELECT distinct mgr from emp where mgr is not null);
-- 1.3 员工表中大于员工薪资的就是经理， 因为大于最大员工就一定是经理
select 
			ename 
from 
			emp
where 
			sal > (select max(sal) as max_sal from emp where empno not in
																		(SELECT distinct mgr from emp where mgr is not null));

第五题， 求平均薪水等级最低的部门编号

-- 1.1 求每个部门平均薪水等级情况
select 
			e.deptno, s.grade as avg_grade
from 
			emp e inner join salgrade s
on
			e.sal between s.losal and s.hisal
group by e.deptno;
 -- 1.2 求出部门最低薪水等级
select 
				min(t.avg_grade)
from 	
				(select 
			e.deptno, s.grade as avg_grade
from 
			emp e inner join salgrade s
on
			e.sal between s.losal and s.hisal
group by e.deptno) t;
 
-- 1.3 将两张表格进行连接 having 作为条件 grade
select 
			e.deptno, s.grade as avg_grade
from 
			emp e inner join salgrade s
on
			e.sal between s.losal and s.hisal
group by e.deptno
having avg_grade = (select 
				min(t.avg_grade)
from 	
				(select 
			e.deptno, s.grade as avg_grade
from 
			emp e inner join salgrade s
on
			e.sal between s.losal and s.hisal
group by e.deptno) t);

第六题，求出平均薪水最高的部门编号

-- 1.1 求出每个部门的平均薪资
select 
				deptno, avg(sal) avg_sal
from 
				emp 
group by deptno;

-- 1.2 求出最高的平均薪资
select 
				max(avg_sal)
from 
				(select 
				deptno, avg(sal) avg_sal
from 
				emp 
group by deptno) t;

-- 1.3 求出平均薪水对应的最高编号
select 
				deptno, avg(sal) avg_sal
from 
				emp 
group by deptno
having avg_sal = (select 
				max(avg_sal)
from 
				(select 
				deptno, avg(sal) avg_sal
from 
				emp 
group by deptno) t);

第七题， 求出部门中所有人的平均薪水等级

-- 1.1 每个部门的平均薪水
select 
				deptno, avg(sal) avg_sal
from 
				emp
group by deptno;
-- 1.2 每个部门对应的薪水等级
select 
				t.deptno, s.grade
from 
			(select 
				deptno, avg(sal) avg_sal
				from 
				emp
				group by deptno)	t join salgrade s 
				on t.avg_sal between s.losal and s.hisal;

第八题， 每个部门的最高薪水

-- 1.1 求出每个部门的最高薪资
select 
				deptno,max(sal) as max_sal
from 
				emp 
group by deptno;

-- 1.2 将两张表格进行连接，求出一些想要的东西
select 
				e.deptno, e.ename, t.max_sal
from 
				emp e join (select 
				deptno,max(sal) as max_sal
				from 
				emp 
				group by deptno) t on e.deptno = t.deptno and e.sal = t.max_sal;

第九题， 哪些人的薪资水平在部门的平平均薪资之上

-- 每个部门平均薪资水平
select 
				deptno, avg(sal) as avg_sal
from
				emp 
group by deptno;

-- 高于平均薪资的人
select 
			  e.ename, e.sal
from 
				emp e join (select 
				deptno, avg(sal) as avg_sal
				from
				emp 
				group by deptno) t on e.deptno = t.deptno and e.sal > t.avg_sal;