--判断student\course\sc表是否存在，存在则删除表
if Object_id('sc') is not null
Drop table sc
if Object_id('student') is not null
Drop table student
if Object_id('course') is not null
Drop table course
create table student
( sno char(10) primary key,
sname varchar(20) not null,
sage smallint,
ssex char(2),
sdept varchar(20)
);
create table course
(
cno char(10) primary key,
cname varchar(20) not null,
credit smallint
);
create table sc
(sno char(10),
cno char(10),
grade smallint,
primary key(sno,cno)
);
-- 对学生表进行插入数据
insert into student (sno,sname,sage,ssex,sdept) values ('001','张华',21,'f','cs');
insert into student (sno,sname,sage,ssex,sdept) values ('002','张钱',25,'f','wl');
insert into student (sno,sname,sage,ssex,sdept) values ('003','李华',23,'m','cs');
insert into student (sno,sname,sage,ssex,sdept) values ('004','王丽',21,'m','sx');
insert into student (sno,sname,sage,ssex,sdept) values ('005','海风',20,'f','cs');
insert into student values ('006','叶欣',24,'m','cs');
insert into student values ('007','李民浩',23,'f','cs');
insert into student values ('008','傲雪',22,'m','cs');
insert into student values ('009','无敌',23,'m','wy');
insert into student values ('010','周明',21,'f','cs');
--对课程表插入数据
insert into course values ('001','计算机基础',3);
insert into course values ('002','计算机网络',3);
insert into course values ('003','C语言',5);
insert into course values ('004','汇编语言',3);
insert into course values ('005','体育',2);
insert into course values ('006','外语',3);
insert into course values ('007','数据库技术',5);
insert into course values ('008','教学设计',5);
insert into course values ('009','数据恢复',3);
insert into course values ('010','计算机组成原理',5);
--对选课表插入数据
insert into sc values ('001','001',98);
insert into sc values ('001','002',89);
insert into sc values ('001','005',85);
insert into sc values ('001','006',65);
insert into sc values ('001','007',78);
insert into sc values ('001','010',89);
insert into sc values ('001','003',85);
insert into sc values ('001','004',65);
insert into sc values ('002','006',89);
insert into sc values ('002','005',74);
insert into sc values ('002','001',98);
insert into sc values ('002','003',89);
insert into sc values ('002','004',74);
insert into sc values ('002','002',60);
insert into sc values ('003','002',88);
insert into sc values ('003','003',85);
insert into sc values ('003','004',98);
insert into sc values ('003','005',88);
insert into sc values ('003','006',70);
insert into sc values ('003','007',76);
insert into sc values ('004','006',76);
insert into sc values ('004','005',85);
insert into sc values ('004','004',76);
insert into sc values ('004','001',85);
insert into sc values ('004','007',76);
insert into sc values ('004','008',85);
insert into sc values ('005','004',90);
insert into sc values ('005','006',65);
insert into sc values ('005','005',85);
insert into sc values ('005','003',92);
insert into sc values ('005','002',66);
insert into sc values ('005','007',85);
insert into sc values ('006','001',70);
insert into sc values ('006','002',80);
insert into sc values ('006','003',85);
insert into sc values ('006','004',72);
insert into sc values ('006','08',89);
insert into sc values ('006','010',85);
insert into sc values ('007','005',85);
insert into sc values ('007','004',81);
insert into sc values ('007','006',89);
insert into sc values ('007','007',85);
insert into sc values ('007','008',92);
insert into sc values ('007','003',89);
insert into sc values ('008','005',85);
insert into sc values ('008','001',90);
insert into sc values ('008','002',89);
insert into sc values ('008','003',81);
insert into sc values ('008','004',80);
insert into sc values ('008','006',98);
insert into sc values ('008','008',89);
insert into sc values ('008','010',85);
insert into sc values ('009','004',98);
insert into sc values ('009','006',58);
insert into sc values ('009','005',59);
insert into sc values ('009','002',60);
insert into sc values ('009','003',70);
insert into sc values ('009','007',59);
insert into sc values ('010','001',52);
insert into sc values ('010','002',89);
insert into sc values ('010','004',98);
insert into sc values ('010','006',60);
insert into sc values ('010','003',74);
insert into sc values ('010','005',89);
insert into sc values ('010','008',86);
insert into sc values ('010','010',60);
评论0