SQL Server 学习

第一题 查询"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 课程号