xscj.sql

这是一个简单的学生成绩数据库系统

CREATE DATABASE XSCJ
  ON 
   (NAME='XSCJ_DATA',
         FILENAME='D:\XSCJ_DATA.MDF',
         SIZE=1MB,
         MAXSIZE=20MB,
         FILEGROWTH=1MB
         )
   LOG ON
     (NAME='XSCJ_LOG',
         FILENAME='D:\XSCJ_LOG.LDF',
         SIZE=1MB,
         MAXSIZE=20MB,
         FILEGROWTH=1MB
      ) 
GO
USE XSCJ
CREATE TABLE XS(学号 char(6) primary key,
                姓名 char(8) not null,
                专业名 char(10) null,
                性别   bit not null default 1,
                出生时间 smalldatetime not null,
                总学分 tinyint null,
                备注 text)  
CREATE TABLE KC(课程号 char(3) primary key,
                课程名 char(16) not null,
                开课学期 tinyint not null check (开课学期 between 1 and 8) ,
                学时  tinyint not null,
                学分 tinyint null )  
CREATE TABLE XS_KC(学号 char(6) not null,
                课程号 char(3) not null,
                成绩 tinyint  null,
                学分 tinyint null,
                primary key (学号,课程号))
INSERT INTO XS VALUES('001101','王林','计算机',1,'1980-02-10',50,null)
INSERT INTO XS VALUES('001102','程明','计算机',1,'1981-02-01',50,null)
INSERT INTO XS VALUES('001103','王燕','计算机',0,'1979-10-06',50,null)
INSERT INTO XS VALUES('001104','韦严平','计算机',1,'1980-08-26',50,null)
INSERT INTO XS VALUES('001106','李方方','计算机',1,'1980-11-20',50,null)
INSERT INTO XS VALUES('001107','李明','计算机',1,'1980-05-01',54,'已提前修完<数据结构>,并获得学分')
INSERT INTO XS VALUES('001108','林一帆','计算机',1,'1979-08-05',52,'已提前修完一门课')
INSERT INTO XS VALUES('001109','张强民','计算机',1,'1978-08-11',50,null)
INSERT INTO XS VALUES('001110','张蔚','计算机',0,'1981-07-22',50,'三好学生')
INSERT INTO XS VALUES('001111','赵琳','计算机',0,'1980-03-18',50,null)
INSERT INTO XS VALUES('001113','严红','计算机',0,'1979-08-11',48,'一门功课不及格,待补考')
INSERT INTO XS VALUES('001201','王敏','通信工程',1,'1978-06-10',42,null)
INSERT INTO XS VALUES('001202','王林','通信工程',1,'1979-01-29',40,'一门功课不及格,待补考')
INSERT INTO XS VALUES('001203','王玉民','通信工程',1,'1980-03-26',42,null)
INSERT INTO XS VALUES('001204','马琳琳','通信工程',0,'1978-02-10',42,null)
INSERT INTO XS VALUES('001206','李计','通信工程',1,'1979-09-20',42,null)
INSERT INTO XS VALUES('001210','李红庆','通信工程',1,'1979-05-01',44,'已提前修完一门课,并获得学分')
INSERT INTO XS VALUES('001216','孙详欣','通信工程',1,'1978-03-09',42,null)
INSERT INTO XS VALUES('001218','孙研','通信工程',1,'1980-10-09',42,null)
INSERT INTO XS VALUES('001220','吴薇华','通信工程',0,'1980-03-18',42,null)
INSERT INTO XS VALUES('001221','刘燕敏','通信工程',0,'1979-11-12',42,null)
INSERT INTO XS VALUES('001241','罗琳琳','通信工程',0,'1980-01-30',50,'转专业学习')
INSERT INTO KC VALUES('101','计算机基础',1,80,5)
INSERT INTO KC VALUES('102','程序设计与语言',2,68,4)
INSERT INTO KC VALUES('206','离散数学',4,68,4)
INSERT INTO KC VALUES('208','数据结构',5,68,4)
INSERT INTO KC VALUES('209','操作系统',6,68,4)
INSERT INTO KC VALUES('210','计算机原理',7,85,5)
INSERT INTO KC VALUES('212','数据库原理',7,68,4)
INSERT INTO KC VALUES('301','计算机网络',7,51,3)
INSERT INTO KC VALUES('302','软件工程',7,51,3)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001101','101',80)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001101','102',78)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001101','206',76)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001103','101',62)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001103','102',70)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001103','206',81)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001104','101',90)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001104','102',84)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001104','206',65)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001102','102',78)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001102','206',78)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001106','101',65)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001106','102',71)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001106','206',80)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001107','101',78)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001107','102',80)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001107','206',68)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001108','101',85)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001108','102',64)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001108','206',87)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001109','101',66)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001109','102',83)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001109','206',70)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001110','101',95)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001110','102',90)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001110','206',89)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001111','101',91)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001111','102',70)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001111','206',76)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001113','101',63)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001113','102',79)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001113','206',60)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001201','101',80)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001202','101',65)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001203','101',87)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001204','101',91)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001210','101',76)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001216','101',81)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001218','101',70)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001220','101',82)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001221','101',76)
INSERT INTO XS_KC(学号,课程号,成绩) VALUES('001241','101',90)

go
create table cxb(
xb bit,
xbm varchar(4)
)
go
insert into cxb values(1,'男')
insert into cxb values(0,'女')