sql基本操作

开局一张图，答案全靠蒙(绝密期末考试题)

 

 

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;