第十二章 高校信息系统数据库管理

学生信息表:

/****** 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