T-SQL编程（二）

表stumarks

表stuinfo

 

问题：

      查询缺考的人

   

select stuName from stuinfo where  stuNo not in (select stuNo from stumarks)

  判断本班成绩有没有大于80的，有则每人加2分，没有则每人加5分

if exists(select * from stumarks where writtenExam>80 )

	update stumarks set writtenExam=writtenExam+2

else

	update stumarks set writtenExam=writtenExam+5
 
并且查询所有学员的通过情况，缺考的显示缺考，新加一类显示是否通过
 
select stuName,stuinfo.stuNo,笔试成绩=case

					when writtenExam  is not null then writtenExam

					else '缺考'

				end,



			     机试成绩= case

 					when LabExam is not null then LabExam

					else '缺考'

				end,



			    是否通过= case

					when LabExam>60 and writtenExam>60 then '通过'

					else '没有通过'

				end



from stuinfo left join stumarks 

on stuinfo.stuNo=stumarks.stuNo