create table jw0220student
 (sno char(6),
 sname char(10) not null,
 ssex char(2) check (ssex in('男','女',' ')),
 sage number(3) check(sage between 17 and 31),  
 sscore number(5,1),
 saddf number(3,1),
 primary key(sno));


 create table jw0220course
 (cno char(5),
 cname char(20) not null,
 primary key(cno));


create table jw0220teacher
 (tno char(6),
 tname char(8) not null,
 tsex char(2) check (tsex in('男','女',' ')),
 tage number(2), 
 techtitle char(10),
 primary key(tno));


create table jw0220choice
 (sno char(6),
  cno char(5),
  score number(5,1),
 primary key(sno,cno),
 foreign key(sno)
 references jw0220student(sno),
 foreign key(cno)
 references jw0220course(cno));





 


insert into jw0220student values('980101','王小艷','女','18','80','15');

insert into jw0220student values('980201','李明','男','18','570','10');

insert into jw0220student values('980102','司馬奮進','男','19','560','5');

insert into jw0220student values('980202','李明','女','18','530','10');

insert into jw0220student values('980103','成功','男','18','540','15');



insert into jw0220course values('101','oracle');

insert into jw0220course values('102','sql');

insert into jw0220course values('103','c++');

insert into jw0220course values('104','計算機維修');

insert into jw0220course values('105','操作系統');


insert into jw0220teacher values('000001','王海冰','男','30','講師');
insert into jw0220teacher values('000002','李鎬','男','46','教授');
insert into jw0220teacher values('000003','周紅','女','56','博士');
insert into jw0220teacher values('000006','蕭黎','女','26','碩士');
insert into jw0220teacher values('000006','李愈','男','32','副教授');



insert into jw0220choice values('980101','101','75');
insert into jw0220choice values('980101','102','55');
insert into jw0220choice values('980101','103','85');
insert into jw0220choice values('980101','104','65');
insert into jw0220choice values('980101','105','95');

insert into jw0220choice values('980102','101','65');
insert into jw0220choice values('980102','102','45');
insert into jw0220choice values('980102','103','55');
insert into jw0220choice values('980102','104','75');
insert into jw0220choice values('980102','105','75');

insert into jw0220choice values('980103','101','75');
insert into jw0220choice values('980103','102','95');
insert into jw0220choice values('980103','103','85');
insert into jw0220choice values('980103','104','65');
insert into jw0220choice values('980103','105','95');

insert into jw0220choice values('980201','101','75');
insert into jw0220choice values('980201','102','55');
insert into jw0220choice values('980201','103','45');
insert into jw0220choice values('980201','104','55');
insert into jw0220choice values('980201','105','95');

insert into jw0220choice values('980202','101','75');
insert into jw0220choice values('980202','102','85');
insert into jw0220choice values('980202','103','85');
insert into jw0220choice values('980202','104','95');
insert into jw0220choice values('980202','105','95');










desc jw0220student;
desc jw0220choice;
desc jw0220teacher;
desc jw0220course;


select tno,tname,tage
from jw0220teacher
where techtitle='講師';

select *
from jw0220student
where sname like '李%';


select sno,sname
from jw0220choice a ,jw0220student b
where  a.sno=b.cno and cno in c.score=a.score
      (select  )
      

select  distinct sname,tname,cname
from   jw0220student a,jw0220teacher,jw0220choice b,jw0220course d
where  a.sno=b.sno and b.cno=d.cno and b.score>any
       (select b.score
        from jw0220choice b
        where b.score>60);
       

select  distinct cname
from   jw0220student a,jw0220choice b,jw0220course d
where  a.sno=b.sno and b.cno=d.cno and b.score>any
       (select b.score
        from jw0220choice b
        where b.score<60);

update jw0220choice
set score=60
where  score<60 and score in (select score
              from jw0220choice,jw0220course
              where jw0220course.cno=jw0220choice.cno
              and jw0220course.cname='oralce');



create view sscour_view
as select sno,sname,cno,cname,tno,tname,score
from student,course,teacher


desc sscour_view
select *
from sscour_view 

create sequence insert_data
insert into course values(insert_data.nextval,' ','  ')
insert into course values(insert_data.nextval,' ','  ')

create or replace  procedure ts (
    t_sex in        techer.tsex%type
    summ  out       varchar2,
   
begin
    select tname,tsex,sum(tex)
    into t_sex
from teacher  
where 
dbms_output.put_line(to_char());
end;


create or replace function ts2(sumb varchar)
return carchar2 as
sex varchar2(2)
begin
select sum(tsex)
into sex
from teacher
where tsex=sumb
return sex
end; 

//列
create or replace trigger a1
before delete on student
begin
    dbms_output._line('trigger successfull!');
end;

create or replace trigger a1
before delete on course
begin
    dbms_output._line('delete successfull!');
end;


//行
create or replace trigger a1
before delete on student
for each row 
begin
    dbms_output._line('trigger successfull!');
end;

create or replace trigger a1
before delete on course
for each row 
begin
    dbms_output._line('delete successfull!');
end;



 SQL>DECLARE
            TREC SCORE%ROWTYPE;
         BEGIN
            SELECT  *
                INTO TREC
                FROM CHOICE
                WHERE SNO='999';
         END;