use xsgl
go
create table student
(
id int identity(1,1) primary key ,
name char(10)not null,
sex char(2),
age int check(age>0 and age<100),
class char(5)
)
create table cause
(
id int identity (1,1) primary key ,
name char(10) not null,
period int ,
credit int
)
create table exam
(
stu_id int not null,
cau_id int not null,
grade int not null,
constraint pk primary key(stu_id,cau_id),
constraint fk1 foreign key(stu_id) references student(id),
constraint fk2 foreign key(cau_id) references cause(id)
)
insert into student values('张三','男',20,'一班')
insert into student values('李四','男',25,'二班')
insert into student values('李雨春','女',22,'一班')
insert into student values('周杰伦','男',20,'一班')
insert into student values('成龙','男',40,'二班')
insert into student values('李小龙','男',30,'一班')
insert into cause values('语文',10,3)
insert into cause values('数学',10,3)
insert into cause values('英语',20,5)
insert into cause values('数据结构',20,5)
insert into exam values(1,1,80)
insert into exam values(1,2,36)
insert into exam values(1,3,80)
insert into exam values(1,4,68)
insert into exam values(2,1,56)
insert into exam values(2,2,52)
insert into exam values(2,3,16)
insert into exam values(2,4,95)
insert into exam values(3,1,45)
insert into exam values(3,2,55)
insert into exam values(3,3,86)
insert into exam values(3,4,86)
insert into exam values(4,1,45)
insert into exam values(4,2,76)
insert into exam values(4,3,45)
insert into exam values(4,4,75)
insert into exam values(5,1,54)
insert into exam values(5,2,65)
insert into exam values(5,3,68)
insert into exam values(5,4,97)
insert into exam values(6,1,67)
insert into exam values(6,2,98)
insert into exam values(6,3,78)
insert into exam values(6,4,78)