开局一张图,答案全靠蒙(绝密期末考试题)
1.计算每个人的总成绩并排名(要求显示字段:姓名,总成绩)
select name,SUM(score) as allscore from dbo.stuscore group by name order by allscore;
select name,SUM(score) as allscore from dbo.stuscore group by name order by allscore;
2.计算每个人的总成绩并排名(要求显示字段: 学号,姓名,总成绩)
select stuid,name,SUM(score) as allscore from dbo.stuscore group by name,stuid order by allscore;
select stuid,name,SUM(score) as allscore from dbo.stuscore group by name,stuid order by allscore;
3.计算每个人单科的最高成绩(要求显示字段: 学号,姓名,课程,最高成绩)
select t1.stuid,t1.name,t1.subject,t1.score from stuscore t1, (select stuid,max(score) as maxscore from stuscore group by stuid) t2 where t1.stuid=t2.stuid and t1.score=t2.maxscore;
select t1.stuid,t1.name,t1.subject,t1.score from stuscore t1, (select stuid,max(score) as maxscore from stuscore group by stuid) t2 where t1.stuid=t2.stuid and t1.score=t2.maxscore;
4.计算每个人的平均成绩(要求显示字段: 学号,姓名,平均成绩)
select stuid,name,AVG(score) avgscore from dbo.stuscore group by stuid,name;
select stuid,name,AVG(score) avgscore from dbo.stuscore group by stuid,name;
5.列出各门课程成绩最好的学生(要求显示字段: 学号,姓名,科目,成绩)
select t1.stuid,t1.name,t1.subject,t1.score from stuscore t1,( select subject,MAX(score) as maxscore from stuscore group by subject)t2 where t1.subject = t2.subject and t1.score = t2.maxscore;
select t1.stuid,t1.name,t1.subject,t1.score from stuscore t1,( select subject,MAX(score) as maxscore from stuscore group by subject)t2 where t1.subject = t2.subject and t1.score = t2.maxscore;
6.列出各门课程成绩最好的两位学生(要求显示字段: 学号,姓名,科目,成绩)
select t1.* from stuscore t1 where t1.stuid in ( select top 2 stuid from stuscore where subject = t1.subject order by score desc) order by t1.subject;
select t1.* from stuscore t1 where t1.stuid in ( select top 2 stuid from stuscore where subject = t1.subject order by score desc) order by t1.subject;
7.统计如下:
学号 | 姓名 | 语文 | 数学 | 英语 | 总分 | 平均分 |
select stuid 学号,name 姓名,sum(case when subject='语文' then score else 0 end )as 语文, sum(case when subject='数学' then score else 0 end )as 数学, sum(case when subject='英语' then score else 0 end )as 英语, SUM(score)总分,avg(score)平均分 from stuscore group by stuid,name order by 总分;
select stuid 学号,name 姓名,sum(case when subject='语文' then score else 0 end )as 语文, sum(case when subject='数学' then score else 0 end )as 数学, sum(case when subject='英语' then score else 0 end )as 英语, SUM(score)总分,avg(score)平均分 from stuscore group by stuid,name order by 总分;
8.列出各门课程的平均成绩(要求显示字段:课程,平均成绩)
select subject,AVG(score)平均成绩 from stuscore group by subject;
select subject,AVG(score)平均成绩 from stuscore group by subject;
9.列出数学成绩的排名(要求显示字段:学号,姓名,成绩,排名)
select stuid,name,score, (select count(*) from stuscore t1 where subject ='数学' and t1.score > t2.score)+1 as 名次 from stuscore t2 where subject='数学' order by score desc; --注释:排序,比较大小,比较的次数+1 = 排名。
select stuid,name,score, (select count(*) from stuscore t1 where subject ='数学' and t1.score > t2.score)+1 as 名次 from stuscore t2 where subject='数学' order by score desc; --注释:排序,比较大小,比较的次数+1 = 排名。
10.列出数学成绩在2-3名的学生(要求显示字段:学号,姓名,科目,成绩)
select t3.* from ( select top 2 t2.* from ( select top 3 stuid,name,subject,score from stuscore where subject = '数学' order by score desc) t2 order by t2.score) t3 order by t3.score desc;
11.统计如下
课程 | 不及格(0-59)个 | 良(60-80)个 | 优(81-100)个 |
select subject 科目,sum(case when score between 0 and 59 then 1 else 0 end) as 不及格, sum(case when score between 60 and 80 then 1 else 0 end) as 良, sum(case when score between 81 and 100 then 1 else 0 end) as 优秀 from stuscore group by subject;