CREATE DATABASE 教学成绩管理数据库 ON  PRIMARY 
( NAME = N'教学成绩管理数据库_Data', FILENAME = N'D:\教学成绩管理数据库_Data.MDF' )
 LOG ON 
( NAME = N'教学成绩管理数据库_Log', FILENAME = N'D:\教学成绩管理数据库_Log.LDF' )


CREATE TABLE [dbo].[学生信息表](
 [学生ID] [int] NOT NULL,
 [学号] [nvarchar](10) NOT NULL,
 [姓名] [nvarchar](10) NOT NULL,
 [性别] [nchar](1) NOT NULL,
 [出生日期] [datetime] NOT NULL,
 [身高] [decimal](15, 2) NOT NULL,
 [体重] [decimal](15, 2) NOT NULL,
 [小组] [nvarchar](4) NOT NULL,
 [课程职务] [nchar](2) NOT NULL,
 [EMail] [nvarchar](30) NOT NULL,
 [QQ号码] [varchar](16) NOT NULL,
 [手机号码] [char](11) NOT NULL,
 [民族] [nvarchar](8) NOT NULL,
 [政治面貌] [nvarchar](8) NOT NULL,
 [身份证号] [char](18) NOT NULL,
 [籍贯] [nvarchar](30) NOT NULL,
 [家庭住址] [nvarchar](30),
)


SELECT [学生ID] ,
 [学号] ,
 [姓名] ,
 [性别] ,
 [出生日期] ,
 [身高] ,
 [体重] ,
 [小组] ,
 [课程职务] ,
 [EMail] ,
 [QQ号码] ,
 [手机号码] ,
 [民族] ,
 [政治面貌] ,
 [身份证号] ,
 [籍贯] ,
 [家庭住址] ,
 [备注] 
FROM [上机实训课程过程化考核数据库].[dbo].[学生信息表]
  where [班级ID] = 300027 


GO
CREATE TABLE [dbo].[考核信息表](
	[考核ID] [int] NOT NULL,
	[考核名称] [nvarchar](50) NOT NULL,
	[考核类型] [nchar](4) NOT NULL,
	[开始时间] [datetime] NOT NULL,
	[结束时间] [datetime] NOT NULL,
	[满分分值] [int] NOT NULL,
	[批阅类型] [nchar](4) NOT NULL
)


SELECT [考核ID]
      ,[考核名称]
      ,[考核类型]
      ,[开始时间]
      ,[结束时间]
      ,[满分分值]
      ,[批阅类型]
  FROM [上机实训课程过程化考核数据库].[dbo].[考核信息表]
  where [班级ID] = 300027 and [课程ID]=100001


CREATE TABLE [dbo].[考核成绩表](
	[学生ID] [int] NOT NULL,
	[考核ID] [int] NOT NULL,
	[分数] [numeric](5, 1) NULL,
	[批阅人] [nvarchar](10) NULL
)



SELECT  [学生ID]      ,[考核ID]      ,[分数]      ,[批阅人]
  FROM [上机实训课程过程化考核数据库].[dbo].[学生考核完成信息表]
  where [考核ID] in (
  SELECT [考核ID]
  FROM [上机实训课程过程化考核数据库].[dbo].[考核信息表]
  where [班级ID] = 300027 and [课程ID]=100001
  )



CREATE TABLE [dbo].[教师信息表](
	[教师ID] [int] NOT NULL,
	[姓名] [nvarchar](10) NOT NULL,
	[性别] [nchar](1) NOT NULL,
	[出生日期] [datetime] NOT NULL,
	[EMail] [nvarchar](30) NOT NULL,
	[QQ号码] [varchar](16) NOT NULL,
	[手机号码] [char](11) NOT NULL
) 

SELECT [教师ID]
      ,[姓名]
      ,[性别]
      ,[出生日期]
      ,[EMail]
      ,[QQ号码]
      ,[手机号码]
  FROM [上机实训课程过程化考核数据库].[dbo].[教师信息表]



