题目：
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编辑过]
加好友 
发短信
管理员
Post By：2012/3/16 8:14:07 [只看该作者]
