北大青鸟SQL Server项目

/*=======插入数据练习=========*/ --向学员信息表stuInfo插入数据-- INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)VALUES('张秋丽','s25301','男',18,'北京海淀') INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress) VALUES('李斯文','s25303','女',22,'河南洛阳') INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge) VALUES('李文才','s25302','男',31) INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress) VALUES('欧阳俊雄','s25304','男',28,'新疆威武哈') --向学员成绩表stuMarks插入数据-- INSERT INTO stuMarks(ExamNo,stuNo,writtenExam,LabExam) VALUES('E2005070001','s25301',80,58) INSERT INTO stuMarks(ExamNo,stuNo,writtenExam) VALUES('E2005070002','s25302',50) INSERT INTO stuMarks(ExamNo,stuNo,writtenExam,LabExam) VALUES('E2005070003','s25303',97,82) --查看数据-- select * from stuInfo select * from stuMarks /*=======查询数据练习=========*/ --1.查询两表的数据-- select * from stuInfo select * from stuMarks --2.查询男学员名单-- select * from stuInfo where stuSex='男' --3.查询笔试成绩优秀的学员情况（成绩在75~100之间)-- select stuNo ,writtenExam from stuMarks where writtenExam between 75 and 100 --4.查询参加本次考试的学员成绩，包括学员姓名，笔试成绩，机试成绩-- select stuName,writtenExam,labExam from stuInfo inner join stuMarks on stuInfo.stuNo=stuMarks.stuNo --5.统计笔试考试平均分和机试考试平均分-- select avg(writtenExam) as '笔试考平均分', avg(labexam) as '机试平均分' from stuMarks --6.统计参加本次考试的学员人数 select count(stuNo) as 考试人数 from stuMarks --7.查询没有通过考试的人数（笔试或机试小于60分）-- select count(stuNo) as 未通过的人数 from stuMarks where labExam<60 or writtenExam<60 --8.查询学员成绩，显示学号，笔试成绩，机试成绩，平均分-- select stuNO as 学号,writtenExam as 笔试,labExam as 机试, (writtenExam+labExam)/2.0 as 平均分 from stuMarks --9.排名次（按平均分从高到低排序），显示学号、平均分-- select stuNO as 学号,(writtenExam+labExam)/2.0 as 平均分 from stuMarks order by 平均分 desc --10.排名次（按平均分从高到低排序），显示姓名，笔试成绩，机试成绩，平均分-- select stuName as 姓名,writtenExam as 笔试,labExam as 机试, (writtenExam+labExam)/2.0 as 平均分 from stuInfo inner join stuMarks on stuInfo.stuNo=stuMarks.stuNo order by 平均分 desc --11.根据平均分，显示前两名信息，包括姓名、笔试成绩、机试成绩、平均分-- select top 2 stuName as 姓名,writtenExam as 笔试,labExam as 机试, (writtenExam+labExam)/2.0 as 平均分 from stuInfo inner join stuMarks on stuInfo.stuNo=stuMarks.stuNo order by 平均分 desc /*=======修改数据练习=========*/ --都提5分-- update stuMarks set writtenExam=writtenExam+5 select * from stuMarks --100分封顶（加分后超过100分的，按100分计算）-- update stuMarks set writtenExam=100 where writtenExam>100 select * from stuMarks