-- 作者：admin
-- 发布时间：2012/3/16 8:14:07
-- 第一次作业参考答案
题目：
CREATE TABLE student
(
"NUMBER" varchar2(6) NOT NULL,
name varchar2(8) NULL,
sex number(1),
birthday timestamp NULL,
height number(3,2) NULL
);
insert into student ("NUMBER",name,sex,birthday,height) values ( \'000001\',\'黎明\',1,TO_DATE(\'15-4月 -1980\',\'dd-Mon-
yyyy\'),1.78);
insert into student ("NUMBER",name,sex,birthday,height) values ( \'000002\',\'赵怡春\',0,TO_DATE(\'17-12月 -1982\',\'dd-Mon-
yyyy\'),1.77);
insert into student ("NUMBER",name,sex,birthday,height) values ( \'000003\',\'张富平\',1,TO_DATE(\'1-2月 -1981\',\'dd-Mon-
yyyy\'),1.80);
insert into student ("NUMBER",name,sex,birthday,height) values ( \'000004\',\'白丽\',0,TO_DATE(\'20-11月 -1986\',\'dd-Mon-
yyyy\'),1.73);
insert into student ("NUMBER",name,sex,birthday,height) values ( \'000005\',\'牛玉德\',1,TO_DATE(\'1-5月 -1983\',\'dd-Mon-
yyyy\'),1.74);
insert into student ("NUMBER",name,sex,birthday,height) values ( \'000006\',\'姚华\',0,TO_DATE(\'12-5月 -1983\',\'dd-Mon-
yyyy\'),1.77);
CREATE TABLE grade
(
"NUMBER" varchar2(6) NULL,
course varchar2(50) NULL,
grade number(3) NULL
);
insert into grade ("NUMBER",course,grade) values (\'000001\',\'A03\',56);
insert into grade ("NUMBER",course,grade) values (\'000002\',\'A02\',90);
insert into grade ("NUMBER",course,grade) values (\'000004\',\'A02\',77);
insert into grade ("NUMBER",course,grade) values (\'000005\',\'B01\',91);
insert into grade ("NUMBER",course,grade) values (\'000004\',\'A03\',76);
insert into grade ("NUMBER",course,grade) values (\'000003\',\'A03\',67);
insert into grade ("NUMBER",course,grade) values (\'000001\',\'B01\',78);
insert into grade ("NUMBER",course,grade) values (\'000002\',\'A03\',74);
insert into grade ("NUMBER",course,grade) values (\'000002\',\'B01\',86);
insert into grade ("NUMBER",course,grade) values (\'000003\',\'A02\',80);
insert into grade ("NUMBER",course,grade) values (\'000004\',\'B01\',90);
CREATE TABLE course
(
course varchar2(50) NULL,
tearcher varchar2(50) NULL
);
insert into course(course,tearcher) values (\'A02\',\'张玉山\');
insert into course(course,tearcher) values (\'A03\',\'李树青\');
insert into course(course,tearcher) values (\'B01\',\'刘卫国\');
1、建立每个表的主键
ALTER TABLE "SYSTEM"."STUDENT" ADD ( PRIMARY KEY ("NUMBER") VALIDATE )
ALTER TABLE "SYSTEM"."GRADE" ADD ( PRIMARY KEY ("NUMBER") VALIDATE )
ALTER TABLE "SYSTEM"."COURSE" ADD ( PRIMARY KEY ("COURSE") VALIDATE )
2、建立成绩与学生、成绩与课程的外键连接
ALTER TABLE "SYSTEM"."GRADE" ADD ( FOREIGN KEY ("NUMBER") REFERENCES "SYSTEM"."STUDENT" ("NUMBER") VALIDATE )
ALTER TABLE "SYSTEM"."GRADE" ADD ( FOREIGN KEY ("COURSE") REFERENCES "SYSTEM"."COURSE" ("COURSE") VALIDATE )
3、建立所有字段的非空约束
ALTER TABLE "SYSTEM"."GRADE" MODIFY ( "NUMBER" NOT NULL , "COURSE" NOT NULL , "GRADE" NOT NULL )
ALTER TABLE "SYSTEM"."STUDENT" MODIFY ( "NAME" NOT NULL , "SEX" NOT NULL , "BIRTHDAY" NOT NULL , "HEIGHT" NOT NULL )
ALTER TABLE "SYSTEM"."COURSE" MODIFY ( "TEARCHER" NOT NULL )
4、建立学生性别默认值为1
ALTER TABLE "SYSTEM"."STUDENT" MODIFY ( "SEX" DEFAULT 1 )
5、建立学生分数约束0到100之间，身高0到3之间
ALTER TABLE "SYSTEM"."GRADE" ADD ( CHECK (grade >0 and grade <100) VALIDATE )
ALTER TABLE "SYSTEM"."STUDENT" ADD ( CHECK (height >0 and height <3) VALIDATE )
6、建立学生的姓名和教师姓名的唯一约束
ALTER TABLE "SYSTEM"."STUDENT" ADD ( UNIQUE ("NAME") VALIDATE )
ALTER TABLE "SYSTEM"."COURSE" ADD ( UNIQUE ("COURSE") VALIDATE )
附加练习：建立课程号约束：三个字符，第一字符为字母，第二字符为0或者1，第三字符为数字
使用check约束，表达式为：
length(course)=3 and upper(substr(course,1,1))>=\'A\' and upper(substr(course,1,1))<=\'Z\' and (substr(course,2,1)=\'0\' or substr(course,2,1)=\'1\' ) and upper(substr(course,3,1))>=\'0\' and upper(substr(course,3,1))<=\'9\'
更为简单的方法：regexp_like(course,\'[A-Z][0-1][0-9]\')
[此贴子已经被作者于2012-03-16 08:38:32编辑过]
|