如何在数据库中排序并显示排序序号

学生有各门课的成绩,算出总成绩后进行排序,还要显示出名次来

student_info表
姓名 年度  学期   语文   数学    英语
a    2007   上    80     70      90
b    2007   上    60     60      60
c    2007   上    90     90      90
.....
a    2006   下    80     70      90
b    2006   下    60     60      60
c    2006   下    90     90      90
......
我想通过SQL语句得到这结果:
姓名 年度  学期  总分  排名
c    2007   上    270   1
a    2007   上    240   2
b    2007   上    180   3
......
c    2006   下    270   1
a    2006   下    240   2
b    2006   下    180   3

if object_id('pubs..tb') is not null
   drop table tb
go

create table tb(姓名 varchar(10),年度 varchar(4),学期 varchar(2),语文 int,数学 int,英语 int)
insert into tb(姓名,年度,学期,语文,数学,英语) values('a','2007','上',80,70,90)
insert into tb(姓名,年度,学期,语文,数学,英语) values('b','2007','上',60,60,60)
insert into tb(姓名,年度,学期,语文,数学,英语) values('c','2007','上',90,90,90)
insert into tb(姓名,年度,学期,语文,数学,英语) values('a','2006','下',80,70,90)
insert into tb(姓名,年度,学期,语文,数学,英语) values('b','2006','下',60,60,60)
insert into tb(姓名,年度,学期,语文,数学,英语) values('c','2006','下',90,90,90)
go

select * , 语文+数学+英语 总分, 排名=(select count(1) from tb where 年度=a.年度 and 学期=a.学期 and (语文+数学+英语)>(a.语文+a.数学+a.英语))+1 from tb a
order by 年度,学期,排名

drop table tb

/*
姓名       年度 学期  语文        数学        英语        总分        排名
---------- ---- ---- ----------- ----------- ----------- ----------- -----
c          2006 下    90          90          90          270         1
a          2006 下    80          70          90          240         2
b          2006 下    60          60          60          180         3
c          2007 上    90          90          90          270         1
a          2007 上    80          70          90          240         2
b          2007 上    60          60          60          180         3

（所影响的行数为 6 行）
*/

照上面的我已经实现其功能,顺便再问一下,在上面的基础上,要是第一名奖励300块,第二名奖励200,第三名150,其他的不奖励,又怎么实现呢?

if object_id('pubs..tb') is not null
   drop table tb
go

create table tb(姓名 varchar(10),年度 varchar(4),学期 varchar(2),语文 int,数学 int,英语 int)
insert into tb(姓名,年度,学期,语文,数学,英语) values('a','2007','上',80,70,90)
insert into tb(姓名,年度,学期,语文,数学,英语) values('b','2007','上',60,60,60)
insert into tb(姓名,年度,学期,语文,数学,英语) values('c','2007','上',90,90,90)
insert into tb(姓名,年度,学期,语文,数学,英语) values('a','2006','下',80,70,90)
insert into tb(姓名,年度,学期,语文,数学,英语) values('b','2006','下',60,60,60)
insert into tb(姓名,年度,学期,语文,数学,英语) values('c','2006','下',90,90,90)
go

select * , 奖励 = case 排名 when  1 then 300
                           when  2 then 200
                           when  3 then 150
                           else 0 end
from
(
  select * , 语文+数学+英语 总分, 排名=(select count(1) from tb where 年度=a.年度 and 学期=a.学期 and (语文+数学+英语)>(a.语文+a.数学+a.英语))+1 from tb a
) t
order by 年度,学期,排名

drop table tb

/*
姓名 年度 学期 语文        数学        英语        总分        排名        奖励
---- ---- ---- ----------- ----------- ----------- ----------- ----------- ----
c    2006 下    90          90          90          270         1           300
a    2006 下    80          70          90          240         2           200
b    2006 下    60          60          60          180         3           150
c    2007 上    90          90          90          270         1           300
a    2007 上    80          70          90          240         2           200
b    2007 上    60          60          60          180         3           150

（所影响的行数为 6 行）
*/