MySQL-34道作业题（2）

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

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

10、取得薪水最高的第六名道第十名员工的姓名

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

11、取得最后入职的五名员工（hiredate入职日期）
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 grade

13、面试题
有三张表S（学生表），C（课程表），SC表（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER)代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：

• 1、找出没选过“黎明”老师的所有学生姓名。
  第一步：找出黎明老师所授课的课程编号
  select cno from c where cteacher='黎明'
  第二部：通过‘学生选课表’查询 . cno= 上面结果 的sno,这些cno都是选择黎明老师课程学生的学号
  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们）不及格学生姓名及平均成绩
  第一步：列出两门以上不及格的学生姓名
  select sc.sno s.name
  from sc
  join s
  on sc.sno=s.sno
  where sc.scgrade<60
  group by s.sname,sc.sno
  having count()>=2;（表T1）
  第二步：找出每个学生的平均成绩（按序；学号分组求平均值）
  select sno,avg(scgrade) as avgscore from sc group by sno；（表T）
  第三步：第一步和第二步联合
  select t1.sname,t2,avgscore
  from (select sc.sno,s.sname
  from sc join s
  on sc.sno=s.sno
  where sc.scgrade<60
  group by
  sc.sno,s.name
  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 from sc join s on sc.sno=s.sno where cno=1 and sno in(select sno from sc where cno=2);

14、列出所有员工及领导的姓名
emp a<员工表>
emp b<领导表>
select a.name empname ,b.name leadername
from emp a
left join emp b
on a.mgr=b.empno

15、列出受雇日期早于其直接上级的所有员工的编号，姓名，本门名称
emp a<员工表>
emp b<领导表>
select
a.empno '员工编号‘ ，a.ename '员工姓名‘，a.hiredate '员工入职日期‘ ， b.empno '领导编号' ,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、列出部门名称和这些部门的员工信息，同时列出哪些没有员工的部门 emp e<员工表> dept d<部门表>
select e.*,d.dname
from emp e
right join dept d
on e.deptno=d.deptno;

17 、列出至少有5个员工的部门【部门详细信息】 ;emp e<员工表> dept d<部门表>
select d.deptno,d.dname,d.loc ,count(e.ename)
from emp e
join dept b
on e.deptno=d.depgno
group by
d.deptno,d.dname,d.loc
having count(e.ename)>=5;
18、列出薪金比“smith”多的所有员工信息
select *from emp
where sao>(select sal from emp where ename='smith');

19、列出所有办事员（CLERK）的姓名及其部门名称，部门的人数
第一步：select e.ename,d.dname from dept d join emp e on e.deptno=d.deptno where e.job='CLERK'

第二步：select deptno ,count() as totalEmp
from emp e group by deptno
第三步：
select
e.ename ,d.dname,t.totalEmp
from
dept d
join
emp e
on
e.deptno=d.deptno
join
(select deptno ,count() as totalEmp
from emp e group by deptno) t
on d.deptno=e.deptno
where
e.job="CLERK'

20、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数。
select min(sal),count(job),job
from emp
where min(sal)>1500

21、列出在部门“SALES”<销售部>工作的员工姓名，假定不知道销售部的部门编号
select ename from emp where deptno=(select deptno from dept where dname="SALES");
列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，雇员的工资等级
emp a <员工表>
emp b <领导表>
emp d<部门表>
salgrade s <工资等级表>
select
a.enam empname ,d.dname.b,ename,leadername,s.grade
from
emp a
join
dept d
on
a.deptno=d.deptno
join
emp b
on a.mgr = b.empno
join
salgrade s
on
a.sal between s.losal and s.hisal
where
a.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");

24、列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金
selet 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);