MySQLDay07(练习题)

/*
 *2、哪些人的薪水在部门的平均薪水之上
 *第一步：找出部门的平均薪水【按照部门编号分组求薪水平均值】
 *select deptno,avg(sal) as avgsal from emp group by deptno;
 *第二步:将上面的查询结果当做临时表t,t表和emp e表进行表连接
 *条件 t.deptno=e.deptno and e.sal>t.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、取得部门中(所有人的)平均薪水等级
 *3.1、取得部门中（所有人的）平均薪水的等级
 *第一步：取得部门的平均薪水
 *select deptno,avg(sal) as avgsal from emp group by deptno;
 *第二步：将上面的查询结果当作临时表t,t表和salgrade s 进行连接，条件：t.avgsal between s.losal and s.hisal
 *select 
 *  t.*,s.grade
 *from
 *  salgrade s
 *join
 *  (select deptno,avg(sal) as avgsal from emp group by deptno) t
 *on
 *  t.avgsal between s.losal and s.hisal;
 *3.2、 取得部门中(所有人的)平均的薪水等级
 *第一步：每一个员工的薪水等级
 *select 
 *  e.ename,e.sal,s.grade
 *from
 *  emp e
 *join 
 *  salgrade s
 *on e.sal between s.losal and s.hisal;
 *第二步：在以上的sql语句基础之上，继续以部门编号分组，求等级的平均值
 *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）取得最高薪水（两种解决方案）
 *第一种方案，按照薪水降序排列，取第一个
 *select sal from emp order by sal desc limit 1;
 *第二种方案：自连接
 *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 deptno,avg(sal) as avgsal from emp group by deptno;
 *第二步：取得平均薪水的最大值
 *select avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
 *第三步：第一步和第二步联合
 *select 
 *  deptno,avg(sal) as avgsal
 *from 
 *  emp
 *group by
 *  deptno
 *having 
 *  avg(sal) = (select avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1);
 *第二种方案：max函数
 *select 
 *  deptno,avg(sal) as avgsal
 *from 
 *  emp
 *group by
 *  deptno
 *having 
 *  avg(sal)=(select max(t.avgsal) from (select avg(sal) from emp group by deptno) t );
 *6、取得平均薪水最高的部门的部门名称
 *select 
 *  d.dname,avg(sal) as avgsal
 *from
 *  emp e
 *join
 *  dept d
 *on 
 *  e.deptnoo=d.deptno
 *group by
 *  d.dname
 *having 
 *  avg(sal) =(select avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1);
 *7、求平均薪水的等级最低的部门的部门名称
 *第一步：求各个部门平均薪水的等级
 *select deptno,avg(sal) as avgsal from emp group by deptno;
 *select
 *  t.dname,t.avgsal,s.grade
 *from
 *  (select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t 
 *join 
 *  salgrade s
 *on 
 *  t.avgsal between s.losal and s.hisal;
 *第二步：获取最高等级值
 *select
 *  max(s.grade)
 *from
 *  (select avg(sal) as avgsal from emp group by deptno) t
 *join 
 *  salgrade s
 *on 
 *  t.avgsal between s.losal and s.hisal;
 *第三步：第一步和第二步联合
 *select 
 *  t.dname,t.avgsal,s.grade
 *from
 *  (select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t
 *join 
 *  salgrade s
 *on
 *  t.avgsal between s.losal and s.hisal    
 *where 
 *  s.grade =(
 *select 
 *      max(s.grade)
 *from 
 *  (select avg(sal) as avgsal from emp group by deptno) t
 *join 
 *  salgrade s
 *on 
 *  t.avgsal between s.losal and s.hisal
 *);
 *8、取得比普通员工（员工代码没有在mgr字段上出现的）的最高薪水还要高的领导人姓名
 *第一步：找出普通员工
 *select * from emp where empno not in (select distinct mgr from emp);
 *以上语句无法查询到结果，因为not in不会自动忽略null，需要程序员手动排除null
 *select * from emp where empno not in(select distinct mgr from emp where magr is not null);
 *手动加上条件排除null
 *第二步：找出普通员工最高薪水
 *select max(sal) from emp where empno not in(select distinct mgr from emp where magr 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 magr is not null);)
 *
 *not in不会自动忽略空值，但是in会自动忽略空值
 *select * from emp where empno in(select distict from emp);
 *
 *case...when...then...when...then..else...end 使用在DQL语句当中，类似与java中的 switch...case
 *select 
 *  ename,sal,(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 end) as newsal
 *from 
 *  emp
 *没有else的话不在范围内的就成NULL
 *select
 *  ename,sal,(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal
 *from 
 *  emp;
 *9、取得薪水最高的前五名员工
 *select ename,sal from emp order by sal desc limit 5;
 *10、取得薪水最高的第六名到第十名
 *select ename,sal from emp order by sal desc limit 5,5;
 *11、取得最后入职的5名员工
 *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 
 *  e.ename,count(*)
 *from 
 *  emp e
 *join
 *  salgrade s
 *on
 *  e.sal between s.losal and s.hisal
 *group by
 *  s.grade;
 *有三个表S（学生表），C(课程表），SC(学生选课表) 
 *S(SNO,SNAME)代表（学号，姓名）
 *C(CNO,CNAME,CTEACHER)代表（课号，课名，教师）
 *SC(SNO,CNO,SCGRADE)代表(学号，课号，成绩)
 *问题：
 *1、找出没选过"黎明"老师的所有学生姓名
 *第一步：找出黎明老师所授的课程编号
 *select cno from c where cteacher='黎明';
 *第二步：通过"学生选课表"查询cno=上面结果的sno 这些sno都是选择黎明老师课程的学号
 *select sno from sc where cno=(select cno from c where cteacher='黎明')；
 *第三步：在学生表中查询sno not in上面结果的数据
 *select 
 *  sname 
 *from 
 *  s 
 *where 
 *  sno not in(select sno from sc where cno=(select cno from c where cteacher='黎明'));
 *
 *2、列出2门以上（含2门）不及格学生姓名及平均成绩
 *第一步：列出2门以上(含两门)不及格学生姓名
 *select 
 *  sc.sno,s.sname
 *from 
 *  sc 
 *join 
 *  s
 *on
 *  sc.sno = s.sno
 *where 
 *  sc.grade <60 
 *group by 
 *  sc.sno,s.sname 
 *having 
 *  count(*)>=2;
 *第二步：找出每一个学生的平均成绩
 *select sno,avg(scgrade) as avgscore from sc group by sno;
 *第三步：第一步和第二步联合  
 *select
 *  t1.sname,t2.avgscore 
 *from 
 *  (select
 *      sc.sno,s.sname
 *  from
 *      sc
 *  join
 *      s
 *  on
 *      sc.sno = s.sno
 *  where
 *      sc.grade<60
 *  group by
 *      sc.sno,s.sname
 *  having
 *      count(*)>=2)t1
 *join 
 *  (select sc.sno,avg(sc.scgrade) as avgscore from sc group by sc.sno)t2
 *on
 *  t1.sno=t2.sno;
 *3、即学过1号课程又学过2号课的所有学生的姓名
 *第一步：找出学过1号课程的学生
 *select sno from sc where cno=1;
 *第二步：找出学过2号课程的学生
 *select sno from sc where cno =2;
 *第三步：第一步和第二步联合
 *select s.sname,sc.sno from sc join s on sc.sno = s.sno where sc.cno=1 and sc.sno in(select sno from sc where cno=2);
 *S 学生表
 *sno(pk) sname
 *--------------
 *C 课程表
 *cno(pk) cname cteacher
 *----------------
 *sc 学生选课表
 *sno cno   scgrade(sno+cno 是复合主键，主键只有一个，同时sno是外键，cno也是外键，外键有2个)
 *-----------------
 */