0104 MySQL作业

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

1.取得每个部门最高薪水（按部门编号分组，找出每一组最大值）

select deptno,max(sal) as maxsal from emp group by deptno;

2.将以上查询结果当作一张临时表t,和emp表连接

条件：t.deptno = e.deptno and t.maxsal = e.sal

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 t.maxsal = e.sal;

---

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

1.找出每个部门的平均薪水

select deptno,avg(sal) as avgsal from emp group by deptno;

2.将以上查询结果当作一张临时表t,和emp表连接

条件：t.deptno = e.deptno and e.sal > t.avgsal

select

t.*,e.ename,e.sal

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.取得部门中（所有人的）平均的薪水等级

找出每个人的薪水等级

emp e 和salgrade s表连接

条件：e.sal between s.losal and s.hisal;

按照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.取得最高薪水

1.sal降序，limit 1；

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

2.max

select max(sal) from emp;

---

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

找出每个部门平均薪水,降序排序limit

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

---

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

select

d.dname,avg(e.sal) as avgsal

from

emp e

join

dept d

on e.deptno = d.deptno

group by

d.dname

order by

avgsal desc

limit

1;

---

7.取得比普通员工最高薪水还要高的领导人名字

1.找出普通员工最高薪水

select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);

//not in 记得排除null

2.大于普通员工薪水的

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

---

8.取得薪资最高的前五名员工

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

---

9.取得薪资最高的第六到第十名员工

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

---

10.取得最后入职的五名员工

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

---

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

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

---

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

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

---

13. 列出在部门‘SALES’工作的员工姓名，假定不知道部门编号

select ename from emp where deptno = (select deptno from dept where dname = 'SALES');

---

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

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

---

15.有3个表S（学生表）C（课程表）SC（学生选课表）

S（SNO，SNAME）代表学号，姓名

C（CNO,CNAME,CTEACHER）代表课号，课名，教师

SC（SNO,CNO,SCGRADE）代表学号，课号，成绩

1.找出没选‘黎明’老师的所有学生姓名

2.列出两门以上（含两门）不及格学生姓名及平均成绩

3.既学过1号课程又学过2号课程所有学生的姓名

1.

select CNO from C where CTEACHER = '黎明';

select SNO from SC where CNO = (select CNO from C where CTEACHER = '黎明');

select * from S where sno not in(select SNO from SC where CNO = (select CNO from C where CTEACHER = '黎明'););

2.

找出两门以上不及格学生的学号，姓名(t1表）

select SC.SNO,S.SNAME,count(*) as studentNum from SC join S on SC.CNO = S.CNO where SCGRADE < 60 group by SC.SNO,S.SNAME having studentNum >= 2

找出学生学号和平均成绩（t2表）

select SC.SNO,avg(SC.SCGRADE) as avgscgrade from SC group by SC.SNO

连接两表，条件：t1.SNO = t2.SNO

select t1.SNAME,t2.avgscgrade from

(select SC.SNO,S.SNAME,count(*) as studentNum from SC join S on SC.CNO = S.CNO where SCGRADE < 60 group by SC.SNO,S.SNAME having studentNum >= 2) t1

join

(select SC.SNO,avg(SC.SCGRADE) as avgscgrade from SC group by SC.SNO) t2

on

t1.SNO = t2.SNO;

3.

select S.SNAME from SC join S on SC.SNO = S.SNO where CNO = 1 and SC.SNO in(select SNO from SC where CNO = 2);