首页 技术笔记正文

收藏:学生各门课程成绩统计SQL语句大全---SQL练习汇总

拾柴者 技术笔记 2017-09-17 19:02:41 1459 0 JAVASQL

与学生、课程、成绩相关的SQL面试题,建表test (oracle测试)

插入测试数据

insert into dbo.stuscore values ('张三','数学',89,1);
insert into dbo.stuscore values ('张三','语文',80,1);
insert into dbo.stuscore values ('张三','英语',70,1);
insert into dbo.stuscore values ('李四','数学',90,2);
insert into dbo.stuscore values ('李四','语文',70,2);
insert into dbo.stuscore values ('李四','英语',80,2);

插入结果:

8BX2A)0Z~0DLC]U8@8L`R1L.png

需求:

1.计算每个人的总成绩并排名(要求显示字段:姓名,总成绩)

select name,SUM(score) as allscore from test
group by name 
order by allscore;

结果:

6~EABJAQSPI8G(`72NV2}L1.png

2.计算每个人的总成绩并排名(要求显示字段: 学号,姓名,总成绩)

select uuid,name,SUM(score) as allscore from test 
group by name,uuid 
order by allscore;

结果:

5~]@A_C5I@QDWA`I%2S6DD5.png

3.计算每个人单科的最高成绩(要求显示字段: 学号,姓名,课程,最高成绩)

select t1.uuid,t1.name,t1.sub,t1.score from test t1,
(select uuid,max(score) as maxscore from test group by uuid) t2 
where t1.uuid=t2.uuid and t1.score=t2.maxscore;

结果:

I%]}(P}41I2ZHEYVPMMNF79.png

4.计算每个人的平均成绩(要求显示字段: 学号,姓名,平均成绩)

select uuid,name,AVG(score) avgscore from test 
group by uuid,name;

结果:

GONFZA2H`U[M%Q2@B@_RZQA.png

5.列出各门课程成绩最好的学生(要求显示字段: 学号,姓名,科目,成绩)

select t1.uuid,t1.name,t1.sub,t1.score from test t1,(
select sub,MAX(score) as maxscore from test group by sub)t2
where t1.sub = t2.sub and t1.score = t2.maxscore;

结果:

`]IW}]FY0{LBF[XJ]8_5]HR.png

6.列出各门课程成绩最好的两位学生(要求显示字段: 学号,姓名,科目,成绩)

--mysql  --完善
select  t1.* from test t1 where t1.uuid in (
select top 2 uuid from test where sub = t1.sub order by score desc)
order by t1.sub;
--oracle --有bug,待完善
select t.uuid,t.name,t.sub,t.score from
(select test.*,row_number() over (order by sub desc) sc from test) t
where sc<=2;

7.统计如下: 学号     姓名     语文     数学     英语     总分     平均分

select uuid 学号,name 姓名,sum(case when sub='语文' then score else 0 end )as 语文,
sum(case when sub='数学' then score else 0 end )as 数学,
sum(case when sub='英语' then score else 0 end )as 英语,
SUM(score)总分,avg(score)平均分 from test
group by uuid,name order by 总分;

结果:

CBR)G)@LST%(%4FVY6KA$MB.png

8.列出各门课程的平均成绩(要求显示字段:课程,平均成绩)

select sub,AVG(score)平均成绩 from test 
group by sub;


结果:

`M]405ZU%$O]_7}~[TW~1VE.png

9.列出数学成绩的排名(要求显示字段:学号,姓名,成绩,排名)

select uuid,name,score,
(select count(*) from test t1 where sub='数学' and t1.score > t2.score)+1 
as 名次 from test t2  
where sub='数学' order by score desc;

结果:

K[W1PKY`$URBBH28L7PDF`T.png

10.列出数学成绩在2-3名的学生(要求显示字段:学号,姓名,科目,成绩)

--mysql
select t3.* from (
select top 2  t2.* from (
select top 3 uuid,name,sub,score from test where 
sub = '数学' order by score desc) t2 order by t2.score) t3
order by t3.score desc;
--oracle 有bug 
select t3.*  from (
 select uuid,name,sub,score,
(select count(*) from test t1 where sub ='数学' and t1.score > t2.score)+1 as 名次 from
 test t2  where sub='数学') t3 
 where t3.名次 between 2 and 3 order by t3.score desc;

11.求出李四的数学成绩的排名

 select uuid,name,sub,score,(select count(*) from test t1 where sub ='数学' 
 and t1.score > t2.score)+1 as 名次
 from test t2  where sub='数学' and name = '李四' order by score desc;

结果:

R2EK7Y2N$2{O(VIZN@V0CXS.png

12.统计如下: 课程     不及格(0-59)个     良(60-80)个     优(81-100)个

select sub 科目,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 test
 group by sub;

结果:

~]1$WZRC_Z6DO$S`WOOHHCJ.png


打赏 支付宝打赏 微信打赏

评论

双十一优惠

点击关闭
  • 在线客服1