CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY, /* 列级完整性约束条件,Sno是主码*/
Sname CHAR(20) UNIQUE, /* Sname取唯一值*/
Ssex CHAR(2),
Sbirthday date,
Saddress varchar(20),
Sphone CHAR(11),
Sage SMALLINT,
Sdept CHAR(20),
CONSTRAINT sexcheck CHECK(Ssex='男' or Ssex='女'),
CONSTRAINT agecheck CHECK(Sage>=0 and Sage<=100)
);
CREATE TABLE Course
(Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40),
Cpno CHAR(4),
Ccredit SMALLINT,
CONSTRAINT F1 FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);
CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT DEFAULT 0,
CONSTRAINT P1 PRIMARY KEY (Sno,Cno),
/* 主码由两个属性构成，必须作为表级完整性进行定义*/
CONSTRAINT F2 FOREIGN KEY (Sno) REFERENCES Student(Sno),
/* 表级完整性约束条件，Sno是外码，被参照表是Student */
CONSTRAINT F3 FOREIGN KEY (Cno)REFERENCES Course(Cno),
/* 表级完整性约束条件， Cno是外码，被参照表是Course*/
CONSTRAINT Gradecheck CHECK(Grade>=0 and Grade<=100)
);
insert into Student values (201215121,'李勇','男','1987-01-12','山东省青岛市',13505360536,'20','计算机工程系');
insert into Student values (201215122,'刘晨','女','1988-06-04','山东省菏泽市',15905360536,'19','信息工程系');
insert into Student values (201215123,'王敏','女','1989-12-23','山东省菏泽市',13805360536,'18','软件工程系');
insert into Student values (201215124,'张立','男','1988-08-25','山东省菏泽市',13905360536,'19','信息工程系');
insert into Course(Cno,Cname,Ccredit) values(1,'数据库',4);
insert into Course(Cno,Cname,Ccredit) values(2,'数学',2);
insert into Course values(3,'信息系统',1,4);
insert into Course(Cno,Cname,Ccredit) values(5,'数据处理',2);
insert into Course values(4,'操作系统',5,3);
insert into Course(Cno,Cname)values('6','DB_Design');
insert into Course(Cno,Cname)values('7','DBSDesign');
insert into Course(Cno,Cname)values('8','DB%Design');
INSERT into sc VALUES(201215121,1,92);
INSERT into sc VALUES(201215121,2,85);
INSERT into sc VALUES(201215121,3,88);
INSERT into sc VALUES(201215122,2,90);
INSERT into sc VALUES(201215122,3,80);
/*以上是数据准备*/
/*对数据准备进行验证*/
select * from student;
select * from course;
select * from sc;
