第一题 查询"103"课程不及格的学生学号,姓名和联系电话
select 学号,姓名,联系电话 from xsqk where 学号 in (select 学号 from xs_kc where 成绩<60 and 课程号='103')
第二题 查询恰有两门课程不及格的学生信息
select * from xsqk where 学号 in (select 学号 from xs_kc where 成绩<60 group by 学号 having COUNT(学号)=2)
第三题 查询每门课程最高分的学生记录
select * from xsqk where 学号 in (select b.学号 from (select 课程号,max(成绩) m from xs_kc GROUP BY 课程号) t,xs_kc b where t.课程号=b.课程号 and t.m=b.成绩)
select * from xs_kc a where 成绩= (select MAX(成绩) from xs_kc b where a.课程号=b.课程号)
第四题 查询每个学生的最低分学生记录
select * from xsqk where 学号 in (select b.学号 from (select 课程号,min(成绩) m from xs_kc GROUP BY 课程号) t,xs_kc b where t.课程号=b.课程号 and t.m=b.成绩)
select * from xs_kc a where 成绩= (select min(成绩) from xs_kc b where a.课程号=b.课程号)
第五题 查询每门课程的选修人数(提示:使用新增列完成)
select distinct a.课程号, 人数=(select COUNT(学号) from xs_kc b where b.课程号=a.课程号)from xs_kc a
select 课程号,count(学号) as 人数 from xs_kc group by 课程号
