--create Domain stu_name varchar(20)
Sp_addtype stu_name 'varchar(20)'
create table Student
(
sno char(10) not null,
sname stu_name,
sage smallint,
ssex char(1),
sdept char(2),
primary key (sno),
check(ssex in ('M','F'))
)
create table Course(
cno char(10) not null,
cname varchar(20),
cpno char(10) null,
credit smallint,
primary key(cno),
forerign key(cpno) references Course
)
create table SC(
sno char(10) not null,
cno char(10) not null,
grade smallint,
primary key (sno,cno),
foreign key(sno) references Student,
foreign key(cno) references Course,
check(grade>=0 and grade<=100)
)
insert into Student(sno,sname,sage,ssex,sdept)
values('95001','李勇',20,'M','CS');
insert into Student(sno,sname,sage,ssex,sdept)
values('95002','刘晨',19,'F','IS');
insert into Student(sno,sname,sage,ssex,sdept)
values('95003','王敏',18,'F','MA');
insert into Student(sno,sname,sage,ssex,sdept)
values('95004','张立',19,'M','IS');
insert into Course(cno,cname,cpno,credit)
values('2','数学',null,2);
insert into Course(cno,cname,cpno,credit)
values('6','数据处理',null,2);
insert into Course(cno,cname,cpno,credit)
values('7','PASCAL语言','6','4');
insert into Course(cno,cname,cpno,credit)
values('4','操作系统','6',3);
insert into Course(cno,cname,cpno,credit)
values('5','数据结构','7',4);
insert into Course(cno,cname,cpno,credit)
values('1','数据库','5',4);
insert into Course(cno,cname,cpno,credit)
values('3','信息系统','1',4);
insert into SC values('95001','1',92);
insert into SC values('95001','2',85);
insert into SC values('95001','3',88);
insert into SC values('95002','2',90);
insert into SC values('95002','3',80);