CREATE DATABASE 学生成绩管理
ON PRIMARY
( NAME = 'StuDB_Data',
FILENAME = 'D:\Stu_Data.mdf',
SIZE = 1,
MAXSIZE = 20,
FILEGROWTH = 10% )
LOG ON
( NAME = 'StuDB_Log',
FILENAME = 'D:\StuDB_Log.ldf',
SIZE = 1MB,
MAXSIZE = 25MB,
FILEGROWTH = 10%)
use 学生成绩管理

CREATE TABLE Student (
Sno char ( 7 ) PRIMARY KEY,
Sname char ( 10 ) NOT NULL,
Ssex char (2)
CHECK (Ssex = '男' OR Ssex = '女'),
Sage tinyint
CHECK (Sage >= 15 AND Sage <=45),
Sdept char (20 ) DEFAULT '计算机系'
)
CREATE TABLE Course (
Cno char(10) NOT NULL,
Cname char(20) NOT NULL,
Ccredit tinyint CHECK (Ccredit > 0),
Semester tinyint CHECK (Semester > 0),
Period int CHECK (Period > 0),
PRIMARY KEY(Cno)
)
CREATE TABLE SC (
Sno char(7) NOT NULL,
Cno char(10) NOT NULL,
Grade tinyint,
CHECK (Grade >= 0 and Grade <= 100),
PRIMARY KEY ( Sno, Cno ),
FOREIGN KEY ( Sno )
REFERENCES Student ( Sno ),
FOREIGN KEY ( Cno )
REFERENCES Course ( Cno ) )

INSERT INTO Student VALUES ('9521105', '陈凯歌', '男', 38, '信息系')
INSERT INTO Student VALUES ('9521106', '刘德华', '男', 18, '管理系')
INSERT INTO Student VALUES ('9521107', '陈坤', '男', 25, '文法系')
INSERT INTO Student VALUES ('9521108', '赵薇', '女', 18, '体育系')
INSERT INTO Student VALUES ('9521109', '张国立', '男', 24, '中文系')
INSERT INTO Student VALUES ('9521110', '张学友', '男', 20, '计算机系')

INSERT INTO Course(cno, Cname, Ccredit,Semester,Period ) VALUES( 'c01', '数据库基础',4,2,6)
INSERT INTO Course(cno, Cname, Ccredit,Semester,Period ) VALUES( 'c02', '计算机基础',4,1,1)
INSERT INTO Course(cno, Cname, Ccredit,Semester,Period ) VALUES( 'c03', '写作',2,2,2)
INSERT INTO Course(cno, Cname, Ccredit,Semester,Period ) VALUES( 'c04', '英语',8,2,4)
INSERT INTO Course(cno, Cname, Ccredit,Semester,Period ) VALUES( 'c05', '数学',4,2,2)

INSERT INTO SC(Sno, Cno, Grade ) VALUES('9521105','c01',85)
INSERT INTO SC(Sno, Cno, Grade) VALUES('9521106','c03',95)
INSERT INTO SC(Sno, Cno, Grade) VALUES('9521109','c04',65)
INSERT INTO SC(Sno, Cno, Grade) VALUES('9521105','c05',75)

select * from Student
select * from Course
select * from SC