学生信息表:
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [stuName]
,[stuNo]
,[stuSex]
,[stuAge]
,[stuSeat]
,[stuAaddress]
FROM [ONE].[dbo].[stulnfo]
INSERT dbo.stulnfo(stuName,stuNo,stuSex,stuAge,stuAaddress)
SELECT '张秋丽','s25301','男',18,'北京海淀' UNION
SELECT '李斯文','s25303','女',22,'河南洛阳' UNION
SELECT '李文才','s25302','男',31,'' union
select '欧阳俊雄','s25304','男',28,'新疆克拉玛依'
select * from dbo.stulnfo order by stuNo
select * from dbo.stulnfo where stuSex='男'
学生成绩表:
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP 1000 [examNo]
,[stuNo]
,[writtenEaxam]
,[labExam]
FROM [ONE].[dbo].[stuMarks]
INSERT stuMarks(examNo,stuNo,writtenEaxam,labExam)
SELECT 'E2013120301','s25301',80,58 union
select 'E2013121102','s25302',50,'' union
select 'E2013122203','s25303',97,82
select * from dbo.stuMarks where labExam between 75 and 100
select stuNo,writtenEaxam,labExam from dbo.stuMarks
select AVG(writtenEaxam+labExam)/2 from dbo.stuMarks
select COUNT(examNo) as 总人数 from dbo.stuMarks
select * from dbo.stuMarks where writtenEaxam<60 or labExam<60
select avg(writtenEaxam+labExam)/2 as 平均分 from stuMarks
select examNo ,(writtenEaxam+labExam)/2 as 平均分 from dbo.stuMarks order by 平均分 desc