SQL经典45题 - （31~45）

表字段如图所示：

31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

select Student.SId, Student.SId
from student inner join sc
where sc.CId='01' and score>80;

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

select s.CId, count(Student.SId)
from student inner join SC S on Student.SId = S.SId
group by s.CId;

33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

# 写法一
select Student.*, score
from student inner join SC S on Student.SId = S.SId
inner join Course C on S.CId = C.CId
inner join teacher t on C.TId = t.Tid
where t.Tname='张三'
order by score desc
limit 1;

# 写法二
select Student.*, max(score)
from student inner join SC S on Student.SId = S.SId
inner join Course C on S.CId = C.CId
inner join teacher t on C.TId = t.Tid
where t.Tname='张三';

# 写法三
# step1. 中间表(查出张三老师所教课程的最高成绩)
select max(score)
from SC inner join course c on SC.CId = c.CId
inner join teacher t on c.TId = t.Tid
where t.Tname='张三';

# step2. 查询成绩等于中间表查询出来的最高分，并且是"张三"老师的课程
select Student.*, S.score
from student inner join SC S on Student.SId = S.SId
inner join Course C on S.CId = C.CId
inner join teacher t on C.TId = t.Tid
where S.score=(
    select max(score)
    from SC inner join course c on SC.CId = c.CId
    inner join teacher t on c.TId = t.Tid
    where t.Tname='张三'
    )
and t.Tname='张三';

34.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生

select Student.*, S.score
from student inner join SC S on Student.SId = S.SId
inner join Course C on S.CId = C.CId
inner join teacher t on C.TId = t.Tid
where S.score=(
    select max(score)
    from SC inner join course c on SC.CId = c.CId
    inner join teacher t on c.TId = t.Tid
    where t.Tname='张三'
    )
and t.Tname='张三';

35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select distinct sc1.*
from sc sc1 inner join sc sc2 on sc1.score=sc2.score
and sc1.CId<>sc2.CId;

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

select *
from (
    select SC.CId,
           SC.SId,
           SC.score,
           rank() over (partition by SC.CId order by SC.score desc ) as score_order
    from SC
         ) t
where score_order<=2;

37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）

select count(SC.SId)
from sc
group by sc.CId
having count(SC.SId)>5;

38.检索至少选修两门课程的学生学号

select SC.SId, count(SC.CId) c_count
from SC
group by SC.SId
having count(SC.CId)>=2;

39.查询选修了全部课程的学生信息

select s.*
from SC inner join course c on SC.CId = c.CId
inner join student s on SC.SId = s.SId
group by SC.SId
having count(SC.CId)=(
    select count(Course.CId)
    from course
    );

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

select Student.Sname, year(now())-year(Student.Sage) as age
from student;

41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

select Student.Sname,
       case when date_format(now(), ('%m-%d')) - date_format(Sage, ('%m-%d')) < 0
       then year(now())-year(Sage)-1
       else year(now())-year(Sage)
       end as '年龄'
from student;

# 写法二
SELECT Sname,TIMESTAMPDIFF(YEAR,Sage,NOW()) age
FROM student;

42.查询本周过生日的学生

select Student.*
from student
where week(Student.Sage)=week(now());

43. 查询下周过生日的学生

select Student.*
from student
where week(Sage)=week(now())+1;

44.查询本月过生日的学生

select Student.*
from student
where month(Student.Sage)=month(now());

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

select Student.*
from student
where month(Student.Sage)=month(now())+1;