爱站程序员基地— 30. 查询不及格的课程
[code]select sname, cname, scorefrom scores as sc inner join course as coon sc.cid = co.cidinner join student as ston st.sid = sc.sidwhere score < 60;
— 31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
[code]select st.sid, sname, sc.cid, scorefrom scores as sc inner join student as ston sc.sid = st.sid and score >= 80inner join course as coon sc.cid = co.cid and sc.cid = \'01\';
— 32. 求每门课程的学生人数
[code]select sc.cid, cname, count(*) as \'学生人数\'from scores as sc inner join course as coon sc.cid = co.cidgroup by cid;
— 33. 成绩不重复，查询选修「张三」老师所授课程的学生中，
— 成绩最高的学生信息及其成绩
[code]select st.*, scorefrom scores as sc inner join course as coon sc.cid = co.cidinner join teacher as teon co.tid = te.tid and tname = \'张三\'inner join student as ston st.sid = sc.sidorder by score desclimit 1;
— 34. 成绩有重复的情况下，查询选修「李四」老师所授课程的学生中，
— 成绩最高的学生信息及其成绩
[code]-- 先找到张三老师教的课程代码select tname, sc.cid, sc.sid, sc.scorefrom teacher as te inner join course as coon te.tid = co.tidinner join scores as scon sc.cid = co.cidwhere tname = \'李四\';-- 最大值select max(t.score) as msfrom(select tname, sc.cid, sc.sid, sc.scorefrom teacher as te inner join course as coon te.tid = co.tidinner join scores as scon sc.cid = co.cidwhere tname = \'李四\') as t;-- 有重复，筛选出最高分信息select tname, sc.cid, sc.sid, st.*, sc.scorefrom teacher as te inner join course as coon te.tid = co.tidinner join scores as scon sc.cid = co.cidinner join student as ston sc.sid = st.sidwhere tname = \'李四\' and score =(select max(t.score) as msfrom(select tname, sc.cid, sc.sid, sc.scorefrom teacher as te inner join course as coon te.tid = co.tidinner join scores as scon sc.cid = co.cidwhere tname = \'李四\') as t);
— 35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
[code]-- 先查不同课程相同成绩的sidselect *from scores as sc1 inner join scores as sc2on sc1.score = sc2.score and sc1.cid != sc2.cidgroup by sc1.sid;
— 36. 查询每门功成绩最好的前两名
[code]-- 先对每组排名，再选select *, count(s2.score) as rank_numfrom(select * from scores as scgroup by sc.cid, sc.sid) as s1left join(select * from scores as scgroup by sc.cid, sc.sid) as s2on s1.cid = s2.cid and s1.score < s2.score or (s1.score = s2.score and s1.sid > s2.sid)group by s1.sid, s1.score;
— 37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）
[code]select sc.cid, cname, count(sc.cid) as \"选修人数\"from scores sc inner join course coon sc.cid = co.cidgroup by sc.cidhaving 选修人数 >= 5;
— 38. 检索至少选修两门课程的学生学号
[code]select st.sid, sname, count(cid) as \"选修课数\"from scores as sc inner join student as ston sc.sid = st.sidgroup by sidhaving 选修课数 >= 2;
— 39. 查询选修了全部课程的学生信息
[code]select st.*, count(sc.cid) as \"选修课数\"from scores as sc inner join student as ston sc.sid = st.sidgroup by sc.sidhaving 选修课数 = (select count(cid) from course);
