/*
学生信息表添加数据
*/
insert into dbo.stuInfo (stuName,stuNo,stuSex,stuAge,StuAddress)
values ('张秋丽','s25301','男',18,'北京海淀')
insert into dbo.stuInfo (stuName,stuNo,stuSex,stuAge,StuAddress)
values ('李斯文','s25303','女',22,'河南洛阳')
insert into dbo.stuInfo (stuName,stuNo,stuSex,stuAge,StuAddress)
values ('李文材','s25302','男',31, default)
insert into dbo.stuInfo (stuName,stuNo,stuSex,stuAge,StuAddress)
values ('欧阳俊雄','s25304','男',28,'新疆克拉玛依')
/*
学生成绩表添加数据
*/
insert into dbo.stuMarks (examNo,stuNo,writtenExam,labExam)
values ('E2005070001','s25301',80,58)
insert into dbo.stuMarks (examNo,stuNo,writtenExam,labExam)
values ('E2005070002','s25302',50,default)
insert into dbo.stuMarks (examNo,stuNo,writtenExam,labExam)
values ('E2005070003','s25303',97,82)
/*
查询两表数据
*/
select * from dbo.stuInfo
select * from dbo.stuMarks
/*
查询男学员
*/
select *
from dbo.stuInfo
where stuSex='男'
/*
查询优秀学员
*/
select dbo.stuMarks.writtenExam,dbo.stuMarks.labExam,
dbo.stuInfo.stuName
from dbo.stuMarks inner join dbo.stuInfo
on(dbo.stuInfo.stuNo=dbo.stuMarks.stuNo)
where
dbo.stuMarks.writtenExam between 75 and 100
and
dbo.stuMarks.labExam between 75 and 100
/*
笔试成绩平均分和机试成绩平均分
*/
select avg(writtenExam) as 笔试成绩,avg(labExam) as 机试成绩
from dbo.stuMarks
/*参加考试人数*/
select count(*) as 考试人数
from dbo.stuMarks
/*
通过考试的人数
*/
select count(*) as 通过考试人数
from dbo.stuMarks
where
dbo.stuMarks.writtenExam between 60 and 100
and
dbo.stuMarks.labExam between 60 and 100
/*查询学员平均成绩以及学号*/
select dbo.stuInfo.stuNo as 学号, (dbo.stuMarks.writtenExam+dbo.stuMarks.labExam)/2 as 平均成绩
from dbo.stuInfo inner join dbo.stuMarks
on(dbo.stuInfo.stuNo = dbo.stuMarks.stuNo)
/*查询学员平均成绩以及学号,从高到低排列*/
select dbo.stuInfo.stuNo as 学号, (dbo.stuMarks.writtenExam+dbo.stuMarks.labExam)/2 as 平均成绩
from dbo.stuInfo inner join dbo.stuMarks
on(dbo.stuInfo.stuNo = dbo.stuMarks.stuNo)
order by dbo.stuMarks.labExam desc
/*查询学员姓名，学员笔试成绩，学员机试成绩，学员平均成绩以及学号,从平均成绩高到低排列*/
select dbo.stuInfo.stuName as 学员姓名,dbo.stuMarks.writtenExam as 笔试成绩,dbo.stuMarks.labExam as 机试成绩,
dbo.stuInfo.stuNo as 学号, (dbo.stuMarks.writtenExam+dbo.stuMarks.labExam)/2 as 平均成绩
from dbo.stuInfo inner join dbo.stuMarks
on(dbo.stuInfo.stuNo = dbo.stuMarks.stuNo)
order by dbo.stuMarks.labExam desc
/*取前两名*/
select top 2 dbo.stuInfo.stuName as 学员姓名,dbo.stuMarks.writtenExam as 笔试成绩,dbo.stuMarks.labExam as 机试成绩,
dbo.stuInfo.stuNo as 学号, (dbo.stuMarks.writtenExam+dbo.stuMarks.labExam)/2 as 平均成绩
from dbo.stuInfo inner join dbo.stuMarks
on(dbo.stuInfo.stuNo = dbo.stuMarks.stuNo)
order by dbo.stuMarks.labExam desc
/*
加分
*/
update dbo.stuMarks set writtenExam=writtenExam+5 where writtenExam<=95
update dbo.stuMarks set writtenExam=100 where writtenExam>95
select * from dbo.stuMarks
