实验二:基础数据如下
create database XSGL
go
use XSGL
go
create table student
(
sno char(8) primary key,
sname char(4) not null,
ssex char(2) default ‘男’ check(ssex=’男’ or ssex=’女’),
sage int,
sdept char(10) not null
)
create table course
(
cno char(2)constraint PK_course primary key,
cname char(30),
credit int,
cpno char(3)
)
create table sc
(
sno char(8),
cno char(2),
grade int check(grade<=100 and grade>=0),
constraint PK_sc primary key(sno,cno),
constraint Fk1 foreign key(sno) references student(sno),
constraint FK2 foreign key(cno) references course(cno)
)
insert into student(sno,sname,ssex,sage,sdept) values(‘95001’, ‘李勇’, ‘男’, 20, ‘CS’)
insert into student(sno,sname,ssex,sage,sdept) values(‘95002’, ‘刘晨’, ‘女’, 19, ‘IS’)
insert into student(sno,sname,ssex,sage,sdept) values(‘95003’, ‘王敏’, ‘女’, 18, ‘MA’)
insert into student(sno,sname,ssex,sage,sdept) values(‘95004’, ‘张立’, ‘男’, 19, ‘IS’)
insert into student(sno,sname,ssex,sage,sdept) values(‘95005’, ‘刘云’, ‘女’, 18, ‘CS’)
insert into course(cno, cname,credit,cpno) values(‘1’, ‘数据库’, 4, ‘5’)
insert into course(cno, cname,credit,cpno) values(‘2’, ‘数学’, 6, null)
insert into course(cno, cname,credit,cpno) values(‘3’, ‘信息系统’, 3, ‘1’)
insert into course(cno, cname,credit,cpno) values(‘4’, ‘操作系统’, 4, ‘6’)
insert into course(cno, cname,credit,cpno) values(‘5’, ‘数据结构’, 4, ‘7’)
insert into course(cno, cname,credit,cpno) values(‘6’, ‘数据处理’, 3, null)
insert into course(cno, cname,credit,cpno) values(‘7’, ‘PASCAL语言’, 4, ‘6’)
insert into sc(sno,cno,grade) values(‘95001’, ‘1’ ,92)
insert into sc(sno,cno,grade) values(‘95001’, ‘2’ ,85)
insert into sc(sno,cno,grade) values(‘95001’, ‘3’ ,88)
insert into sc(sno,cno,grade) values(‘95002’, ‘2’ ,90)
insert into sc(sno,cno,grade) values(‘95002’, ‘3’ ,80)
insert into sc(sno,cno,grade) values(‘95003’, ‘2’ ,85)
insert into sc(sno,cno,grade) values(‘95004’, ‘1’ ,58)
insert into sc(sno,cno,grade) values(‘95004’, ‘2’ ,85) Continue reading →