create table stuInfo
(
stuNo varchar2(8) not null primary key,
stuName varchar2(10) not null,
stuSex varchar2(2) not null,
stuAge number(6) not null,
stuSeat number(6) not null,
strAddress varchar2(255) default('地址不详')
)
go
--创建学生成绩表
create table stuMarks
(
ExamNo varchar2(7) not null primary key,
stuNo varchar2(6) not null references stuInfo(stuNo),
writtenExam number(6) null,
LabExam number(6) null
)
go
--往学生信息表内插入测试数据
insert into stuInfo(stuNo, stuName, stuSex, stuAge,stuSeat,strAddress)
select 's25301', '张秋丽', '男', 18,1, '北京海淀' from dual union
select 's25303', '李斯文', '女', 22,2, '河阳洛阳' from dual union
select 's25302', '李文才', '男', 85, 3,'地址不详' from dual union
select 's25304', '欧阳俊雄', '男', 28, 4,'新疆' from dual union
select 's25318', '梅超风', '女', 23, 5,'地址不详' from dual
go
--往学生成绩表内插入测试数据
insert into stuMarks(ExamNo, stuNo, writtenExam, LabExam)
select 's271811', 's25303', 93, 59 from dual union
select 's271813', 's25302', 63, 91 from dual union
select 's271816', 's25301', 90, 83 from dual union
select 's271817', 's25318', 63, 53 from dual