--查询学生信息
select * from stuInfo
--考试成绩统计汇总
select * from stuInfo inner join dbo.stuMarks on stuInfo.StuNo=stuMarks.StuNo
--查询没有通过考试的人数select COUNT(StuNo)as 未通过 from stuMarks where labExam< 60 or writtenExam <60
--查询学员成绩,学号、笔试、机试成绩及平均分
select stuInfo.StuNo,writtenExam,labExam,((writtenExam+ labExam)/2) as 平均分 from stuInfo inner join dbo.stuMarks on stuInfo.StuNo=stuMarks.StuNo
--排名次(按平均分从高到低排序),学号和平均分
SELECT stuInfo.StuNo,(writtenExam+ labExam) /2 as 平均分 from stuInfo inner join dbo.stuMarks on stuInfo.StuNo=stuMarks.StuNo order by (writtenExam+ labExam) /2
select * from stuMarks
--给每个学生的成绩加5
update stuMarks set writtenExam=(case when writtenExam>=95 then 100 when writtenExam<95 then writtenExam+'5' end)
select * from stuMarks
--删除学号是s25301,s25302 的两名学生的成绩
--1
delete from stuMarks where stuMarks.StuNo='s25301'and stuMarks.StuNo='s25302'
--2
delete from stuMarks where stuMarks.StuNo in('s25301','s25302')