判断成绩排序.txt

SQL语言常用的一些命令各代码

原表: 
courseid coursename score 
------------------------------------- 
1 java 70 
2 oracle 90 
3 xml 40 
4 jsp 30 
5 servlet 80 
------------------------------------- 
为了便于阅读,查询此表后的结果显式如下(及格分数为60): 
courseid coursename score mark 
--------------------------------------------------- 
1 java 70 pass 
2 oracle 90 pass 
3 servlet 80 pass 
4 xml 40 fail
5  jsp 30 fail 
--------------------------------------------------- 
写出此查询语句 

create table #tab(courseid int,coursename varchar(10),score int)
insert into #tab select 1, 'java', 70 
union all        select 2, 'oracle', 90 
union all        select 3, 'xml', 40 
union all        select 4, 'jsp', 30 
union all        select 5, 'servlet', 80 
/*解法1*/
select courseid,coursename,score,mark='pass'from #tab where score>=60
union all
select courseid,coursename,score,mark='fail'from #tab where score<60
order by courseid
/*解法2*/
select courseid,coursename,score,mark=(case  when score>=60 then 'pass' else 'fail'end) from #tab