本公众号使用SQL SERVER2014测试。SQL语句可能因数据库系统不同而存在少许差异。

测试数据请参照第一章节，各表查询结果如下图：

练习题目

31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
32. 求每门课程的学生人数
33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
36. 查询每门功成绩最好的前两名
37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）。
38. 检索至少选修两门课程的学生学号
39. 查询选修了全部课程的学生信息
40. 查询各学生的年龄，只按年份来算

参考答案

--------------------------------------------------------

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

select t1.SId,t1.Sname from Student t1,SC t2 where t1.SId=t2.SId and t2.CId=01 and t2.score>=80

输出结果

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

select cid,count(*) as 学生人数 from SC group by CId

输出结果

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

select top 1 t1.*,t2.score from student t1, sc t2,Course t3 ,Teacher t4where t1.SId=t2.SId and t2.CId=t3.CId and t3.TId=t4.TId and t4.Tname='张三'order by score desc

输出结果

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

select * from (select t1.*,t2.score,rank() over (order by t2.score desc) as num  from student t1, sc t2,Course t3 ,Teacher t4where t1.SId=t2.SId and t2.CId=t3.CId and t3.TId=t4.TId and t4.Tname='张三')ttwhere tt.num=1

输出结果

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

select a.* from sc a,sc b  where a.SId=b.SId and  a.CId!=b.CId and a.score=b.score group by a.SId, a.cid,a.score

或

select *from sc as t1where exists(select * from sc as t2 where t1.SId=t2.SId and t1.CId!=t2.CId and t1.score =t2.score )

输出结果

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

select *from sc as t1where (select count(*) from sc as t2 where t1.CId=t2.CId and t2.score >t1.score)<2ORDER BY t1.CId

输出结果

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

select cid,count(*) as 选修人数 from sc group by CId having count(*)>5

输出结果

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

select sid from SC group by sid having count(*)>=2

输出结果

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

select * from student ,(select t1.sid from SC t1 group by t1.SId having count(*)=(select count(*) from (select CId from SC t2 group by t2.CId)t))ttwhere Student.SId=tt.SId

输出结果

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

select t.*,  year(getdate())-year(sage) as 年龄  from Student t

或

select t.*, datediff(year,t.Sage,getdate()) as '年龄' from Student t

输出结果

ITPub博客

SQL 经典50题练习之31-40题

最新文章