CREATE TABLE t_student (
f_id char(3) NOT NULL Primary Key,
f_name char(8) NOT NULL ,
f_sex char(1) NOT NULL ,
f_birth date NULL ,
f_department char(5) NULL ,
f_class int NULL
);
CREATE TABLE t_course(
f_id char(2) NOT NULL Primary Key,
f_name char(10) NULL ,
f_teacher char(8) NULL
);
CREATE TABLE t_grade (
f_stuid char(3) NOT NULL REFERENCES t_student(f_id),
f_courseid char(2) NOT NULL REFERENCES t_course(f_id),
f_grade int NULL ,
Primary Key(f_stuid,f_courseid)
);
--t_student
insert into t_student values('001','张三','m','01-8月-84','CS',1);
insert into t_student values('002','李四','f','11-5月-86','PHY',3);
insert into t_student values('003','王五','m','17-6月-83','CS',1);
insert into t_student values('004','马六','m','23-7月-87','Maths',4);
insert into t_student values('005','田七','m','03-9月-81','CS',2);
insert into t_student values('006','赵八','f','19-10月-82','CS',2);
insert into t_student values('007','陈九','f','07-12月-87','Maths',4);
insert into t_student values('008','楚留香','m','08-3月-86','CS',2);
insert into t_student values('009','陈世美','m','10-4月-85','PHY',3);
insert into t_student values('010','田富贵','m','27-5月-84','PHY',3);
insert into t_student values('011','刘晨','f','21-6月-85','CS',1);
insert into t_student values('012','张明','m','15-8月-82','BIO',1);
--t_course
insert into t_course values ('01','C语言','wang');
insert into t_course values ('02','Java','yang');
insert into t_course values ('03','SqlServer','qin');
insert into t_course values ('04','WinForm','zhang');
insert into t_course values ('05','Oracle','qi');
insert into t_course values ('06','Web','yin');
insert into t_course values ('07','C++','wei');
insert into t_course values ('08','English','huang');
insert into t_course values ('09','OOAD','hua');
--t_grade
insert into t_grade values('001','01',61);
insert into t_grade values('001','02',69);
insert into t_grade values('001','03',70);
insert into t_grade values('001','04',79);
insert into t_grade values('001','07',88);
insert into t_grade values('001','08',89);
insert into t_grade values('002','01',96);
insert into t_grade values('002','02',69);
insert into t_grade values('002','03',55);
insert into t_grade values('002','04',59);
insert into t_grade values('002','06',78);
insert into t_grade values('002','07',54);
insert into t_grade values('002','08',67);
insert into t_grade values('003','01',86);
insert into t_grade values('003','02',62);
insert into t_grade values('003','03',79);
insert into t_grade values('003','04',59);
insert into t_grade values('003','05',70);
insert into t_grade values('003','06',60);
insert into t_grade values('003','07',76);
insert into t_grade values('003','08',60);
insert into t_grade values('004','01',80);
insert into t_grade values('004','03',50);
insert into t_grade values('004','04',49);
insert into t_grade values('004','05',89);
insert into t_grade values('004','07',48);
insert into t_grade values('004','08',83);
insert into t_grade values('005','01',73);
insert into t_grade values('005','03',63);
insert into t_grade values('005','04',53);
insert into t_grade values('005','05',93);
insert into t_grade values('005','07',43);
insert into t_grade values('005','08',91);
insert into t_grade values('006','01',81);
insert into t_grade values('006','02',61);
insert into t_grade values('006','03',71);
insert into t_grade values('006','04',92);
insert into t_grade values('006','05',82);
insert into t_grade values('006','06',72);
insert into t_grade values('007','01',62);
insert into t_grade values('007','02',52);
insert into t_grade values('007','03',94);
insert into t_grade values('007','04',84);