莫晓毅.txt

关于oracle和sql的书籍和ppt教程,非常好,本人珍藏品

create table student11
(
sno          number(4)      not null,
sname	     varchar2(20),
sex	     char(2),
age	     number(2),	
cno          number(4),
score	     number(3),
constraint   pk_student11   primary key (sno)   validate,
constraint   fg_cno         foreign key (cno)   
             references  class11(cno)           validate,
constraint   checking_sex check (sex in ('男','女') and 
  	     age between 18 and 60 and score>0 ) validate
);

create table class11
(
cno	     number(4) 	     not null,
cname	     varchar2(20)    not null,
credit       number(2)	     not null,
constraint   pk_class11      primary key (cno)   validate
);

insert into class11 values (1,'computer',4);
insert into class11 values (2,'english',6);
insert into class11 values (3,'math',6);
insert into class11 values (4,'chinese',8);
insert into class11 values (5,'history',2);

insert into student11 values (1,'allex','男',22,2,90);
insert into student11 values (2,'ammy','女',20,1,80);
insert into student11 values (3,'andy','男',21,5,48);
insert into student11 values (4,'terry','男',24,1,56);
insert into student11 values (5,'lucy','女',19,3,70);
insert into student11 values (6,'lily','女',23,4,52);
insert into student11 values (7,'ryo','男',26,3,37);
insert into student11 values (8,'kim','女',23,2,69);
insert into student11 values (9,'john','男',19,1,73);
insert into student11 values (10,'more','男',22,5,68);

update student11 set score= score + 10 where score<60;
select * from student11 where score<60;

commit it;