第二周作业

2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

问题：为什么这里的group by 的having 条件查询，可以直接使用 s_avg 别名？

select a.sid,d.sname,avg(score) as s_avg from

sc a left join student d on a.sid=d.sid 

group by a.sid having s_avg>60;

查询在 SC 表存在成绩的学生信息

select   

 b.* from sc a left join student b on a.sid=b.sid

group by a.sid;

10.查询没学过"张三"老师讲授的任一门课程的学生姓名

select *

fromstudent a

where a.sid not in(select sid from sc a left join course con a.cid=c.cid inner join teacher don c.tid=d.tid and d.tname='张三')

16、查询学生的总成绩，并进行排名，总分重复时保留名次空缺

select s.*,

case when @sco=scos then '' else @rank:=@rank+1 end as rn ,

@sco:=scos from (select sid,sum(score) as scos from sc group by sid order by scos desc) s,

(select @rank:=0,@sco:=null) as t

16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

这个空缺的定义比较模糊，看看就好。

select t1.*,

case when @fontscore=t1.sumscore then @currank when @fontscore:=t1.sumscore then @currank:=@currank+1 end as rank

from (select sc.SId, sum(score) as sumscore from sc GROUP BY sc.SId ORDER BY sum(score) desc) as t1,(select @currank:=0,@fontscore:=null) as t

20、查询出只选修两门课程的学生学号和姓名

select student. 

SId,student.

Sname from sc,student

where student.SId=sc.SId  

GROUP BY sc.SId

HAVING count(*)=2

22、查询名字中含有「风」字的学生信息

select * from 

student where sname like'%风%';

25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select cid,avg(score) as avg_sco 

from sc group by cid 

order by avg_sco desc,cid asc;

29、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

select *

from sc a

left join student b on a.sid=b.sid left join course c

on a.cid=c.cid where a.score>70

32、求每门课程的学生人数

select sc.CId,count(*) as 学生人数

from sc GROUP BY sc.CId

36.查询每门功成绩最好的前两名

select *

from sc a 

where (select count(1)from sc b where a.cid=b.cid and b.score>a.score)<=1;

40.查询各学生的年龄，只按年份来算

select *,

year(now())-year(sage) as age

from student;

45.查询下月过生日的学生

select *,

month(sage) as birth_month,

month(now()) as now_month

from student where month(sage)=month(now())+1