2-1
create or replace view test2_01 as select sid,name from pub.student where sid not in (select sid from pub.student_course);
2-2
create or replace view test2_02 as select sid,name from pub.student where sid in (select sid from pub.student_course where cid in (select cid from pub.student_course where sid=200900130417));
2-3
create or replace view test2_03 as select sid,name from pub.student where sid in (select sid from pub.student_course where cid in (select cid from pub.course where fcid=300002));
2-4
create or replace view test2_04 as select sid,name from pub.student where sid in (select sid from pub.student_course where cid=300005) and sid in (select sid from pub.student_course where cid=300002);
2-5
create or replace view test2_05 as select sid,name,round(avg(score),0) avg_score,sum(score) sum_score from pub.student_course natural join pub.student where age=20 group by sid,name;
2-6
create or replace view test2_06 as select cid,name,max_score,max_score2,max_score_count from pub.course natural left outer join ( select cid,max(score) max_score from pub.student_course group by cid ) natural left outer join ( select cid,max(score) max_score2 from pub.student_course natural join pub.course where (cid,score) not in (select cid,max(score) from pub.student_course natural join pub.course group by cid) group by cid ) natural left outer join ( select cid,count(distinct sid) max_score_count from pub.student_course where (cid,score) in (select cid,max(score) from pub.student_course natural join pub.course group by cid) group by cid )
2-7
create or replace view test2_07 as select sid,name from pub.student where name not like '王%' and name not like '李%' and name not like '张%';
2-8
create or replace view test2_08 as select substr(name,0,1) second_name,count(substr(name,0,1)) p_count from pub.student group by substr(name,0,1);
2-9
create or replace view test2_09 as select B.sid,name,score from pub.student A,pub.student_course B where B.cid=300003 and B.sid=A.sid;
2-10
create or replace view test2_10 as select sid,name from( select sid,name,cid,count(*) lj from pub.student natural join pub.student_course where score<60 group by sid,name,cid ) where lj>1
3-1
create table test3_01( sid char(12), name varchar(10), sex varchar(10), age number(22), birthday date, dname varchar(30), class varchar(10) ); insert into test3_01 select * from pub.student_31; delete from test3_01 where regexp_like (sid,'[^0-9]')
3-2
create table test3_02( sid char(12), name varchar(10), sex varchar(10), age number(22), birthday date, dname varchar(30), class varchar(10) ) insert into test3_02 select * from pub.student_31 delete from test3_02 where 2012-extract(year from birthday)<>age
3-3
create table test3_03( sid char(12), name varchar(10), sex varchar(10), age number(22), birthday date, dname varchar(30), class varchar(10) ) insert into test3_03 select * from pub.student_31 delete from test3_03 where sex<>'男' and sex<>'女' and sex is not null
3-4
create table test3_04( sid char(12), name varchar(10), sex varchar(10), age number(22), birthday date, dname varchar(30), class varchar(10) ) insert into test3_04 select * from pub.student_31 delete from test3_04 where regexp_like (dname,' ') or dname is null or length(dname)<3
3-5
create table test3_05( sid char(12), name varchar(10), sex varchar(10), age number(22), birthday date, dname varchar(30), class varchar(10) ) insert into test3_05 select * from pub.student_31 delete from test3_05 where class not in (select class from pub.student)
3-6
create table test3_06( sid char(12), name varchar(10), sex varchar(10), age number(22), birthday date, dname varchar(30), class varchar(10) ) insert into test3_06 select * from pub.student_31 delete from test3_06 where regexp_like (sid,'[^0-9]') or 2012-extract(year from birthday)<>age or regexp_like (name,' ') or length(name)<2 or sex<>'男' and sex<>'女' and sex is not null or regexp_like (dname,' ') or dname is null or length(dname)<3 or class not in (select class from pub.student)
3-7
create table test3_07( sid char(12), cid char(6), score numeric(5,1), tid char(6) ) insert into test3_07 select * from pub.student_course_32 delete from test3_07 where sid not in( select sid from pub.student)
3-8
create table test3_08( sid char(12), cid char(6), score numeric(5,1), tid char(6) ) insert into test3_08 select * from pub.student_course_32 delete from test3_08 where (cid,tid) not in( select cid,tid from pub.teacher_course)
3-9
create table test3_09( sid char(12), cid char(6), score numeric(5,1), tid char(6) ) insert into test3_09 select * from pub.student_course_32 delete from test3_09 where score<0 or score>100
3-10
create table test3_10( sid char(12), cid char(6), score numeric(5,1), tid char(6) ) insert into test3_10 select * from pub.student_course_32 delete from test3_10 where sid not in( select sid from pub.student) or cid not in( select cid from pub.course) or tid not in( select tid from pub.teacher) or (cid,tid) not in( select cid,tid from pub.teacher_course) or score<0 or score>100
4-3
create table test4_03( sid char(12), name varchar(10), sex varchar(10), age number(22), birthday date, dname varchar(30), class varchar(10) ) insert into test4_03 select * from pub.student_41 alter table test4_03 add sum_credit number(22,1) update test4_03 S set sum_credit=( select sum(credit) from pub.course natural join (select sid,cid,max(score) from pub.student_course where score>=60 group by sid,cid) T where T.sid=S.sid )
4-4
create table test4_04( sid char(12), name varchar(10), sex varchar(10), age number(22), birthday date, dname varchar(30), class varchar(10) ) insert into test4_04 select * from pub.student_41 update test4_04 S set dname=( select did from pub.department T where S.dname=T.dname ) where dname in (select dname from pub.department)
4-5
create table test4_05 as select * from pub.student_41 alter table test4_05 add did varchar(2) update test4_05 set did=('00') update test4_05 s set did=( select did from pub.department_41 t where s.dname=t.dname ) where dname in (select dname from pub.department_41)
4-6
create table test4_06 as select * from pub.student_42 update test4_06 s set name=( select translate(name,'/ ','/') from pub.student_42 t where s.sid=t.sid )
4-7
insert into test4_07 select * from pub.student_42 update test4_07 s set sex=('女') where sex='女性' //还要去空格
4-8
create table test4_08 as select * from pub.student_42 update test4_08 s set class=( select translate(class,'/级','/') from pub.student_42 t where s.sid=t.sid )
4-9
create table test4_09 as select * from pub.student_42 update test4_09 s set age=( select 2012-extract(year from birthday) from pub.student_42 t where s.sid=t.sid ) where age is null
5-1
create table test5_01( first_name varchar(4), frequency numeric(4) ) insert into test5_01 (first_name,frequency) select substr(name,2),count(*) from pub.student group by substr(name,2)
5-2
insert into test5_02 (letter,frequency) select qnmd,(count(*)) sb from ((select substr(name,2,1) qnmd from pub.student ) union all (select substr(name,3) qnmd from pub.student )) where qnmd is not null group by qnmd
5-3
create table test5_03( dname varchar(30), class varchar(10), P_count1 int, P_count2 int, p_count int ) P_count1,P_count2,dname,class,count(*) create table test5_031 as select * from (select dname,class,count(sid) P_count1 from (select dname,class,sid,sum(credit) sum_credit from ((select sid,dname,class from pub.student where dname is not null) natural join (select sid,cid,score from pub.student_course where score>=60) natural join pub.course) group by dname,class,sid) where sum_credit>=10 group by dname,class ) create table test5_032 as insert into test5_032 select dname,class,sum(p2) P_count2 from ((select dname,class,count(sid) p2 from (select dname,class,sid,sum(credit) sum_credit from ((select sid,dname,class from pub.student where dname is not null) natural join (select sid,cid,score from pub.student_course where score>=60) natural join pub.course) group by dname,class,sid) where sum_credit < 10 group by dname,class) union all( select dname,class,count(sid) p2 from pub.student where dname is not null and sid not in (select sid from pub.student_course) group by dname,class )) group by dname,class create table test5_033 as insert into test5_033 select dname,class,count(sid) P_count from pub.student where dname is not null group by dname,class insert into test5_03 select * from test5_031 natural full outer join test5_032 natural full outer join test5_033 update test5_03 set P_count1=0 where P_count1 is null update test5_03 set P_count2=0 where P_count2 is null
5-4
create table test5_04( dname varchar(30), class varchar(10), P_count1 int, P_count2 int, p_count int ) create table test5_041 as select dname,class,sum(p1) P_count1 from ((select dname,class,count(sid) p1 from (select dname,class,sid,sum(credit) sum_credit from ((select sid,dname,class from pub.student where dname is not null) natural join (select sid,cid,score from pub.student_course where score>=60) natural join pub.course) group by dname,class,sid) where sum_credit>=8 and class<=2008 group by dname,class ) union all( select dname,class,count(sid) p1 from (select dname,class,sid,sum(credit) sum_credit from ((select sid,dname,class from pub.student where dname is not null) natural join (select sid,cid,score from pub.student_course where score>=60) natural join pub.course) group by dname,class,sid) where sum_credit>=10 and class>2008 group by dname,class )) group by dname,class create table test5_042 as select dname,class,sum(p2) P_count2 from ((select dname,class,count(sid) p2 from (select dname,class,sid,sum(credit) sum_credit from ((select sid,dname,class from pub.student where dname is not null) natural join (select sid,cid,score from pub.student_course where score>=60) natural join pub.course) group by dname,class,sid) where sum_credit < 8 and class<=2008 group by dname,class) union all( select dname,class,count(sid) p2 from (select dname,class,sid,sum(credit) sum_credit from ((select sid,dname,class from pub.student where dname is not null) natural join (select sid,cid,score from pub.student_course where score>=60) natural join pub.course) group by dname,class,sid) where sum_credit < 10 and class>2008 group by dname,class ) union all( select dname,class,count(sid) p2 from pub.student where dname is not null and sid not in (select sid from pub.student_course) group by dname,class ) ) group by dname,class create table test5_043 as select dname,class,count(sid) P_count from pub.student where dname is not null group by dname,class insert into test5_04 select * from test5_041 natural full outer join test5_042 natural full outer join test5_043 update test5_04 set P_count1=0 where P_count1 is null update test5_04 set P_count2=0 where P_count2 is null
5-5
create table test5_051 as select dname,round(avg(Max_score)) avg_ds_score from pub.student natural join (select sid,cid,max(score) Max_score from pub.student_course where cid=300002 group by sid,cid) where dname is not null group by dname create table test5_052 as select dname,round(avg(Max_score)) avg_os_score from pub.student natural join (select sid,cid,max(score) Max_score from pub.student_course where cid=300005 group by sid,cid) where dname is not null group by dname create table test5_05 as select * from test5_051 natural join test5_052
5-6
create table test5_061 as select sid,name,dname,Max_score ds_score from pub.student natural join (select sid,cid,max(score) Max_score from pub.student_course group by sid,cid) where dname='计算机科学与技术学院' and cid=300002 create table test5_062 as select sid,name,dname,Max_score os_score from pub.student natural join (select sid,cid,max(score) Max_score from pub.student_course group by sid,cid) where dname='计算机科学与技术学院' and cid=300005 create table test5_06 as select * from test5_061 natural join test5_062
5-7
create table test5_07 as select * from test5_061 natural full outer join test5_062
5-8
create table test5_061 as select sid,name,dname,Max_score ds_score from pub.student natural join (select sid,cid,max(score) Max_score from pub.student_course group by sid,cid) where dname='计算机科学与技术学院' and cid=300002 create table test5_062 as select sid,name,dname,Max_score os_score from pub.student natural join (select sid,cid,max(score) Max_score from pub.student_course group by sid,cid) where dname='计算机科学与技术学院' and cid=300005 create table test5_082 as insert into test5_082 select sid,name,dname from pub.student where dname='计算机科学与技术学院' and sid not in (select sid from pub.course natural join pub.student_course where cid=300002 or cid=300005) create table test5_08 as insert into test5_08 select distinct * from test5_061 natural full outer join test5_062 natural full outer join test5_082
6-1
create or replace view test6_01 as select sid,name,dname from pub.student where dname='物理学院' and age<20 order by sid
6-2
create or replace view test6_02 as select sid,name,sum(score) sum_score from pub.student natural join pub.student_course where dname='软件学院' and class='2009' group by sid,name
6-3
create or replace view test6_03 as select sid,name,score from pub.student natural join pub.student_course where dname='计算机科学与技术学院' and class='2010' and cid='300005'
6-4
create or replace view test6_04 as select sid,name from pub.student natural join pub.student_course where cid='300003' and score>90
6-5
create or replace view test6_05 as select a.sid,b.cid,c.name,score from pub.student a,pub.student_course b,pub.course c where a.name='李龙' and a.sid=b.sid and b.cid=c.cid
6-6
create or replace view test6_06 as select sid,name from pub.student where sid in (select sid from pub.student_course group by sid having count(*)>=(select count(*) from pub.course))
6-7
create or replace view test6_07 as select sid,name from pub.student where sid in (select sid from pub.student_course where score >=60 group by sid having count(*)>=(select count(*) from pub.course))
6-8
create or replace view test6_08 as select cid,name from pub.course a where exists( select cid from pub.course where cid=a.fcid and credit=2 )
6-9
create or replace view test6_09 as select a.name,a.sid,sum(credit) sum_credit from pub.student a,pub.student_course b,pub.course c where class='2010' and dname='化学与化工学院' and b.cid=c.cid and a.sid=b.sid and b.score>=60 group by a.name,a.sid
6-10
create or replace view test6_10 as select cid,name from pub.course a where exists( select cid from pub.course b where cid=a.fcid and exists( select cid from pub.course where cid=b.fcid ) )
7-1
create index student_name on test7_01(substr(name,1,1));
7-2
create index sb on test7_02(birthday);
7-3
create view test7_03 as select * from (select sid,name,birthday, (select count(name) from pub.student where name like concat(substr(t1.name,1,1),'%') ) samefirstname from pub.student_testindex t1) where samefirstname=7
7-4
create view test7_04 as select * from (select sid,name,birthday, (select count(*) from pub.student where birthday>=trunc(t1.birthday,'mm') and birthday<=last_day(t1.birthday) ) sameyearmonth, (select count(*) from pub.student where birthday>=trunc(t1.birthday,'yyyy') and birthday<=last_day(add_months(trunc(t1.birthday,'y'),11)) ) sameyear from pub.student_testindex t1) where sameyearmonth=35
7-5
create view test7_05 as select * from (select sid,name,birthday, (select count(*) from pub.student where birthday=t1.birthday+1 ) nextbirthday from pub.student_testindex t1) where nextbirthday=7
8-1
9-1
create table test9_01 as select * from pub.student_11_1 delete from test9_01 create index sbsid on test9_01(sid); insert into test9_01 select * from pub.student where sex='女' insert into test9_01 select * from pub.student_11_1 where sex='女' and sid not in(select sid from test9_01) insert into test9_01 select * from pub.student_11_2 where sex='女' and sid not in(select sid from test9_01)
9-2
create table test9_02 as select * from pub.student_11_1 delete from test9_02 create index sdsid on test9_02(sid); insert into test9_02 select * from pub.student where sex='女' and sid in (select sid from pub.student_course where score<60) insert into test9_02 select * from pub.student_11_1 where sex='女' and sid in (select sid from pub.student_course where score<60) and sid not in(select sid from test9_02) insert into test9_02 select * from pub.student_11_2 where sex='女' and sid in (select sid from pub.student_course where score<60) and sid not in(select sid from test9_02)