创建学生信息表,科目表和成绩表,并创建序列,添加数据,最后提交
create table stuinfo( --学生表
stuid int primary key,
sname varchar2(50) unique,
age int not null
constraint CK_sage check(age>=18 and age<=30),
addr varchar2(50),
sex char(2) default 'M'
constraint CK_sexs check(sex in('M','F'))
);
create sequence seq_stuid start with 10001 increment by 1;
create table course( --科目表
cid int primary key,
cname varchar2(50)
);
create sequence seq_cid start with 10001 increment by 1;
create table score( --成绩
scid int primary key,
stuid int
constraint FK_stuid references stuinfo(stuid),
courseid int
constraint FK_courseid references course(cid),
score int
);
create sequence seq_scid start with 10001 increment by 1;
insert into course values(seq_cid.nextval,'J2EE精讲');
insert into course values(seq_cid.nextval,'Oracle');
insert into course values(seq_cid.nextval,'html网页设计');
insert into course values(seq_cid.nextval,'Java基础');
insert into stuinfo values(seq_stuid.nextval,'张果老',22,null,'M');
insert into stuinfo values(seq_stuid.nextval,'李豹',22,null,'M');
insert into stuinfo values(seq_stuid.nextval,'老胡',22,'北京','M');
insert into stuinfo values(seq_stuid.nextval,'老江',24,'湖南','M');
insert into stuinfo values(seq_stuid.nextval,'张无忌',26,'衡阳','M');
insert into stuinfo values(seq_stuid.nextval,'二师兄',28,'长沙','M');
insert into stuinfo values(seq_stuid.nextval,'凤姐',24,'上海','F');
insert into stuinfo values(seq_stuid.nextval,'元芳',28,'上海','M');