sq测试的脚本

prompt PL/SQL Developer import file
prompt Created on 2014年7月24日 by mWX204345
set feedback off
set define off
prompt Dropping COURSE...
drop table COURSE cascade constraints;
prompt Dropping STUDENT...
drop table STUDENT cascade constraints;
prompt Dropping SC...
drop table SC cascade constraints;
prompt Dropping TEACHER...
drop table TEACHER cascade constraints;
prompt Creating COURSE...
create table COURSE
(
  CNO   VARCHAR2(20) not null,
  CNAME VARCHAR2(20) not null,
  TNO   VARCHAR2(20)
)
;
alter table COURSE
  add constraint P_COURSE primary key (CNO);

prompt Creating STUDENT...
create table STUDENT
(
  SNO   VARCHAR2(20) not null,
  SNAME VARCHAR2(20) not null,
  SAGE  NUMBER(38),
  SSEX  NUMBER(38)
)
;
alter table STUDENT
  add constraint P_STUDENT primary key (SNO);

prompt Creating SC...
create table SC
(
  SNO   VARCHAR2(20) not null,
  CNO   VARCHAR2(20) not null,
  SCORE NUMBER default 0
)
;
alter table SC
  add constraint P_SC primary key (SNO, CNO);
alter table SC
  add constraint F1_SC foreign key (SNO)
  references STUDENT (SNO);
alter table SC
  add constraint F2_SC foreign key (CNO)
  references COURSE (CNO);

prompt Creating TEACHER...
create table TEACHER
(
  TNO   VARCHAR2(20) not null,
  TNAME VARCHAR2(20)
)
;
alter table TEACHER
  add constraint P_TEACHER primary key (TNO);

prompt Disabling triggers for COURSE...
alter table COURSE disable all triggers;
prompt Disabling triggers for STUDENT...
alter table STUDENT disable all triggers;
prompt Disabling triggers for SC...
alter table SC disable all triggers;
prompt Disabling triggers for TEACHER...
alter table TEACHER disable all triggers;
prompt Loading COURSE...
insert into COURSE (CNO, CNAME, TNO)
values ('001', 'chinese', '001');
insert into COURSE (CNO, CNAME, TNO)
values ('002', 'math', '002');
insert into COURSE (CNO, CNAME, TNO)
values ('003', 'english', '003');
commit;
prompt 3 records loaded
prompt Loading STUDENT...
insert into STUDENT (SNO, SNAME, SAGE, SSEX)
values ('001', '001', 11, 1);
insert into STUDENT (SNO, SNAME, SAGE, SSEX)
values ('002', '002', 11, 1);
insert into STUDENT (SNO, SNAME, SAGE, SSEX)
values ('003', '003', 11, 1);
commit;
prompt 3 records loaded
prompt Loading SC...
insert into SC (SNO, CNO, SCORE)
values ('001', '001', 50);
insert into SC (SNO, CNO, SCORE)
values ('003', '001', 30);
insert into SC (SNO, CNO, SCORE)
values ('001', '002', 80);
insert into SC (SNO, CNO, SCORE)
values ('002', '002', 70);
insert into SC (SNO, CNO, SCORE)
values ('003', '002', 50);
insert into SC (SNO, CNO, SCORE)
values ('001', '003', 50);
insert into SC (SNO, CNO, SCORE)
values ('002', '003', 80);
insert into SC (SNO, CNO, SCORE)
values ('003', '003', 90);
commit;
prompt 8 records loaded
prompt Loading TEACHER...
insert into TEACHER (TNO, TNAME)
values ('001', 't1');
insert into TEACHER (TNO, TNAME)
values ('002', '张三');
insert into TEACHER (TNO, TNAME)
values ('003', 't3');
commit;
prompt 3 records loaded
prompt Enabling triggers for COURSE...
alter table COURSE enable all triggers;
prompt Enabling triggers for STUDENT...
alter table STUDENT enable all triggers;
prompt Enabling triggers for SC...
alter table SC enable all triggers;
prompt Enabling triggers for TEACHER...
alter table TEACHER enable all triggers;
set feedback on
set define on
prompt Done.