山东大学数据库实验答案2—8 CREATE TABLE test2_01 ASSELECT SID, NAMEFROM pub.STUDENTWHERE sid NOT IN(SELECT sid FROM pub.STUDENT_COURSE)CREATE TABLE test2_02 ASSELECT SID,NAMEFROM PUB.STUDENTWHERE SID IN(SELECT DISTINCT SIDFROM PUB.STUDENT_COURSEWHERE CID IN(SELECT CID FROM PUB.STUDENT_COURSE WHERESID='200900130417'))CREATE TABLE test2_03 ASselect SID,NAME from PUB.STUDENT where SID in ( select distinct SID from PUB.STUDENT_COURSE where CID in (select CID from PUB.COURSE where FCID='300002') )CREATE TABLE test2_04 ASselect SID,NAME from PUB.STUDENT where SID in ( select distinct SID from PUB.STUDENT_COURSE where CID in (select CID from PUB.COURSE where NAME='操作系统')intersectselect distinct SID from PUB.STUDENT_COURSE where CID in (select CID from PUB.COURSE where NAME='数据结构'))create table test2_05 aswith valid_stu(sid,name) as(select SID,NAME from PUB.STUDENT where AGE=20 and SID in (select SID from PUB.STUDENT_COURSE))select sid,name as name,ROUND(avg(score)) as avg_score,sum(score) as sum_score fromPUB.STUDENT_COURSE natural join valid_stu where SID in (select SID from valid_stu)group by SID,NAMEcreate table test2_06 asselect CID,(max(SCORE))max_score from PUB.STUDENT_COURSE group byCIDcreate table test2_07 asselect SID,NAME from PUB.STUDENT where(NAME not like '张%' and NAME not like '李%' and NAME not like '王%') create table test2_08 aswith xing(value) as (select substr(NAME,1,1) from PUB.STUDENT)select value as second_name,count(value) as p_count from xing group by valuecreate table test2_09 asselect distinct SID,NAME,SCORE from PUB.STUDENT_COURSE natural join PUB.STUDENTwhere CID ='300003';create table test2_10 asselect distinct SID,CID from PUB.STUDENT_COURSE where SID in (select SID from PUB.STUDENT_COURSE)/* 1 */create table test3_01 asselect * from pub.student_31 where regexp_like(SID,'^[0-9]+$')/* 2 */create table test3_02 asselect * from pub.student_31where to_number(substr(BIRTHDAY,8,2))+AGE=112/* 3 */create table test3_03 asselect * from pub.student_31where SEX is null or SEX='男' or SEX='女'/* 4 */create table test3_04 asselect * from pub.student_31where DNAME is not null and length(DNAME)>=3 and instr(DNAME,' ')<=0 /* 5 */create table test3_05 asselect * from pub.student_31where regexp_like(CLASS,'^[0-9]{4}$')/* 6 */create table test3_06 asselect * from pub.student_31where regexp_like(SID,'^[0-9]+$')and to_number(substr(BIRTHDAY,8,2))+AGE=112 and (SEX is null orSEX='男' or SEX='女')and (DNAME is not null and length(DNAME)>=3 and instr(DNAME,' ')<=0) and regexp_like(CLASS,'^[0-9]{4}$') and (instr(NAME,' ')<=0 and length(NAME)>=2)/* 7 */create table test3_07 asselect * from pub.student_course_32 where SID in (select SID from pub.student)/* 8 */create table test3_08 asselect * from pub.student_course_32 natural join pub.teacher_course /* 9 */create table test3_09 asselect * from pub.student_course_32 where SCORE>=0 and score <=100 /* 10 */create table test3_10 asselect * from pub.student_course_32 natural join pub.teacher_course where SID in (select SID from pub.student) and CID in (select CID from pub.course) and TID in (select TID from pub.teacher) and SCORE>=0 and score <=100/*-------------- test4_01 --------------*/ create table test4_01 as select * from pub.student_41;ALTER TABLE test4_01 ADD sum_score number;update test4_01 set sum_score=(select sum(score) from pub.student_course a wheretest4_01.sid=a.sid group by sid);/*-------------- test4_02 --------------*/create table test4_02 as select * from pub.student_41;ALTER TABLE test4_02 ADD avg_score number;update test4_02 set avg_score =(select round(avg(score),1) from pub.student_course a wheretest4_02.sid=a.sidgroup by sid);/*-------------- test4_03 --------------*/create table test4_03 as select * from pub.student_41;ALTER TABLE test4_03 ADD sum_credit number;update test4_03 set sum_credit =(with a as (select * from pub.student_course natural join pub.course) (select sum(credit) from awhere SCORE>=60 and test4_03.sid=a.sid group by sid) )/* 4 */create table test4_04 as select * from pub.student_41;update test4_04 set dname=(select did from pub.department wheretest4_04.dname=pub.department.dname)where dname in (select dname from pub.department);/* 5 */create table test4_05 as select * from pub.student_41;ALTER TABLE test4_05 add did varchar(2);ALTER TABLE test4_05 ADD sum_score number;ALTER TABLE test4_05 ADD avg_score number;ALTER TABLE test4_05 ADD sum_credit number;update test4_05 set sum_score=(select sum(score) from pub.student_course a wheretest4_05.sid=a.sid group by sid);update test4_05 set avg_score =(select round(avg(score),1) from pub.student_course a wheretest4_05.sid=a.sidgroup by sid);update test4_05 set sum_credit =(with a as (select * from pub.student_course natural join pub.course) (select sum(credit) from awhere SCORE>=60 and test4_05.sid=a.sid group by sid));update test4_05 set did =(select did from pub.department wherepub.department.dname=test4_05.dname) where dname in (select dname from pub.department);update test4_05 set did =(select did from pub.department_41 where pub.department_41.dname=test4_05.dname) where dname in (select dname from pub.department_41);update test4_05 set did ='00' where did is null;/* 6 */create table test4_06 as select * from pub.student_42;update test4_06 set name=replace(name,' ','');/* 7 */create table test4_07 as select * from pub.student_42;update test4_07 set SEX=substr(trim(SEX),1,1) where length(SEX)<>1;/* 8 */create table test4_08 as select * from pub.student_42;update test4_08 set CLASS=substr(CLASS,1,4) where length(CLASS)<>4;/* 9 */create table test4_09 as select * from pub.student_42;update test4_09 set AGE=112-to_number(substr(BIRTHDAY,8,2)) where AGE is null;/* 10 */create table test4_10 as select * from pub.student_42;update test4_10 set name=replace(name,' ',''),DNAME=replace(DNAME,' ','');update test4_10 set SEX=substr(trim(SEX),1,1) where length(SEX)<>1;update test4_10 set CLASS=substr(CLASS,1,4) where length(CLASS)<>4;update test4_10 set AGE=112-to_number(substr(BIRTHDAY,8,2)) where AGE is null;create table test5_10(test varchar(20),age numeric (3));insert into test5_10 values('结果1',88);insert into test5_10 values('结果2',90);insert into test5_10 values('结果3',90);insert into test5_10 values('结果4',86);insert into test5_10 values('结果5',90);insert into test5_10 values('结果6',90);insert into test5_10 values('结果7',86);insert into test5_10 values('结果8',86);insert into test5_10 values('结果9',76);insert into test5_10 values('结果10',86);/* 1 */create view test6_01 asselect sid,name,dname from pub.studentwhere age<20 and dname='物理学院' order by sid/* 2 */create view test6_02 aswith temp_table(sid,ss) as(select sid,sum(score) from pub.student_course group by sid) select sid,name,dname,ss as sum_score from pub.student natural join temp_table where class='2009' and dname='软件学院'/* 3 */create view test6_03 asselect * from pub.student_course natural join pub.student whereclass='2010' and dname='计算机科学与技术学院' and cid =(select cid from pub.course where name='操作系统')/* 4 */create view test6_04 asselect sid, from pub.student_course natural join pub.studentwhere score>90 and cid =(select cid from pub.course where name='数据库系统')/* 5 */create view test6_05 asselect sid,cid,name,score from pub.student_course natural joinpub.coursewhere sid in (select sid from pub.student where name='李龙')/* 6 */create view test6_06 aswith a as (select sid,count(*) as totc from pub.student_course group by sid)select sid,name from pub.student where sid in (select sid from a where totc>=(selectcount(*) from pub.course))/* 7 */create view test6_07 asselect * from test6_06 where sid not in (select distinct sid from pub.student_course where sid in(select sid from test6_06)and score<60)/* 8 */create view test6_08 asselect cid,name from pub.coursewhere fcid in (select cid from pub.course where credit=2)/* 9 */create view test6_09 aswith a(sid,sum_credit) as(select sid,sum(credit) as sum_creditfrom pub.student_course natural join pub.course where SCORE>=60 group by sid)select sid,name,sum_credit from pub.student natural join a where class='2010' and dname='化学与化工学院'/* 10 */create view test6_10 asselect cid,name from pub.coursewhere fcid in(select cid from pub.course where fcid is not null)/* 1 */create table test7_01 asselect First_name,count(*) as frequency from( select substr(NAME,2) as First_name from pub.student ) group by First_name/* 2 */create table test7_02 asselect letter,count(*) as frequency from(select substr(NAME,2,1) as letter from pub.studentunion allselect substr(NAME,3) as letter from pub.student wherelength(Name)=3 ) group by letter/* 3 */create table test7_03 aswith a as (select sid,sum(credit) as tot from pub.student_course natural join pub.course where SCORE>=60 group by sid),b as (select * from pub.student natural left join a),c as (select dname,class,count(sid) as p_count from b group by (dname,class)),d as (select dname,class,count(sid) as p_count1 from b where tot>=10 group by (dname,class)),e as (select dname,class,count(sid) as p_count2 from b where tot<10 or tot is null group by (dname,class))select * from c natural left join d natural join eupdate test7_03 set p_count1=0 where p_count1 is null;/* 4 */create view test7_04_v1 asselect * from pub.student natural left join(select sid,sum(credit) as tot from pub.student_course natural join pub.course where SCORE>=60 group by sid);create table test7_04 aswith b as (select * from test7_04_v1),c as (select dname,class,count(sid) as p_count from b group by (dname,class)),d as (select dname,class,count(sid) as p_count1 from b where class<=2008 and tot>=8 group by (dname,class)unionselect dname,class,count(sid) as p_count1 from b where class>2008 and tot>=10 group by (dname,class))select * from c natural left join d where dname is not null;alter table test7_04 add p_count2 NUMBER;update test7_04 set p_count2 = p_count - p_count1;/* 1 */create table test8_01 aswith A as(select DNAME,SCORE,NAME from(select CID,DNAME,SCORE from pub.student_course natural joinpub.student where dnameis not null) natural join pub.course),B as(select dname,round(avg(score)) as avg_ds_score from A where name='数据结构' groupby dname),C as(select dname,round(avg(score)) as avg_os_score from A where name='操作系统' groupby dname)select * from B natural join C/* 2 */create table test8_02 aswith a as(select sid from pub.student_course where cid=(select cid frompub.course where name='操作系统') intersectselect sid from pub.student_course where cid=(select cid frompub.course where name='数据结构')),b as (select a.sid,score as ds_score from pub.student_course,a where pub.student_course.sid=a.sid and cid=(select cid from pub.course where name='数据结构')),c as (select a.sid,score as os_score from pub.student_course,a where pub.student_course.sid=a.sid and cid=(select cid from pub.course where name='操作系统'))select sid,name,dname,ds_score,os_score from b natural join cnatural join pub.student where dname='计算机科学与技术学院' /* 3 */create table test8_03 aswith a as(select sid from pub.student_course where cid=(select cid frompub.course where name='操作系统') unionselect sid from pub.student_course where cid=(select cid frompub.course where name='数据结构')),b as (select a.sid,score as ds_score from pub.student_course,a where pub.student_course.sid=a.sid and cid=(select cid from pub.course where name='数据结构')),c as (select a.sid,score as os_score from pub.student_course,a wherepub.student_course.sid=a.sid and cid=(select cid from pub.course where name='操作系统'))select sid,name,dname,ds_score,os_score from b natural full join c natural join pub.student where dname='计算机科学与技术学院' /* 4 */create table test8_04 aswith a as(select sid from pub.student_course where cid=(select cid frompub.course where name='操作系统') unionselect sid from pub.student_course where cid=(select cid frompub.course where name='数据结构')),b as (select a.sid,score as ds_score from pub.student_course,a where pub.student_course.sid=a.sid and cid=(select cid from pub.course where name='数据结构')),c as (select a.sid,score as os_score from pub.student_course,a where pub.student_course.sid=a.sid and cid=(select cid from pub.course where name='操作系统'))select sid,name,dname,ds_score,os_score from b natural full join c natural full join pub.student where dname='计算机科学与技术学院'。