山东大学数据库实验答案28.docx
《山东大学数据库实验答案28.docx》由会员分享，可在线阅读，更多相关《山东大学数据库实验答案28.docx（14页珍藏版）》请在冰豆网上搜索。
山东大学数据库实验答案28
山东大学数据库实验答案2—8
CREATETABLEtest2_01AS
SELECTSID,NAME
FROMpub.STUDENT
WHEREsidNOTIN
（
SELECTsidFROMpub.STUDENT_COURSE
）
CREATETABLEtest2_02AS
SELECTSID,
NAME
FROMPUB.STUDENT
WHERESIDIN
（
SELECTDISTINCTSID
FROMPUB.STUDENT_COURSE
WHERECIDIN
（
SELECTCIDFROMPUB.STUDENT_COURSEWHERE
SID='200900130417'
）
）
CREATETABLEtest2_03AS
selectSID,NAMEfromPUB.STUDENTwhereSIDin（selectdistinctSIDfromPUB.STUDENT_COURSEwhereCIDin（selectCIDfromPUB.COURSEwhereFCID='300002'））
CREATETABLEtest2_04AS
selectSID,NAMEfromPUB.STUDENTwhereSIDin（selectdistinctSIDfromPUB.STUDENT_COURSEwhereCIDin（selectCIDfromPUB.COURSEwhereNAME='操作系统'）
intersect
selectdistinctSIDfromPUB.STUDENT_COURSEwhereCIDin（selectCIDfromPUB.COURSEwhereNAME='数据结构'）
）
createtabletest2_05as
withvalid_stu（sid,name）as
（
selectSID,NAMEfromPUB.STUDENTwhereAGE=20andSIDin（selectSIDfromPUB.STUDENT_COURSE）
）
selectsid,nameasname,ROUND（avg（score））asavg_score,sum（score）assum_scorefrom
PUB.STUDENT_COURSEnaturaljoinvalid_stuwhereSIDin（selectSIDfromvalid_stu）
groupbySID,NAME
createtabletest2_06as
selectCID,（max（SCORE））max_scorefromPUB.STUDENT_COURSEgroupbyCID
createtabletest2_07as
selectSID,NAMEfromPUB.STUDENTwhere
（NAMEnotlike'张%'andNAMEnotlike'李%'andNAMEnotlike'王%'）
createtabletest2_08as
withxing（value）as（selectsubstr（NAME,1,1）fromPUB.STUDENT）selectvalueassecond_name,count（value）asp_countfromxinggroupbyvalue
createtabletest2_09as
selectdistinctSID,NAME,SCOREfromPUB.STUDENT_COURSEnaturaljoinPUB.STUDENT
whereCID='300003';
createtabletest2_10as
selectdistinctSID,CIDfromPUB.STUDENT_COURSEwhereSIDin（selectSIDfromPUB.STUDENT_COURSE）
/*1*/
createtabletest3_01as
select*frompub.student_31whereregexp_like（SID,'^[0-9]+$'）
/*2*/
createtabletest3_02as
select*frompub.student_31
whereto_number（substr（BIRTHDAY,8,2））+AGE=112
/*3*/
createtabletest3_03as
select*frompub.student_31
whereSEXisnullorSEX='男'orSEX='女'
/*4*/
createtabletest3_04as
select*frompub.student_31
whereDNAMEisnotnullandlength（DNAME）>=3andinstr（DNAME,''）<=0
/*5*/
createtabletest3_05as
select*frompub.student_31
whereregexp_like（CLASS,'^[0-9]{4}$'）
/*6*/
createtabletest3_06as
select*frompub.student_31
whereregexp_like（SID,'^[0-9]+$'）
andto_number（substr（BIRTHDAY,8,2））+AGE=112and（SEXisnullorSEX='男'orSEX='女'）
and（DNAMEisnotnullandlength（DNAME）>=3andinstr（DNAME,''）<=0）
andregexp_like（CLASS,'^[0-9]{4}$'）and（instr（NAME,''）<=0andlength（NAME）>=2）
/*7*/
createtabletest3_07as
select*frompub.student_course_32whereSIDin（selectSIDfrompub.student）
/*8*/
createtabletest3_08as
select*frompub.student_course_32naturaljoinpub.teacher_course
/*9*/
createtabletest3_09as
select*frompub.student_course_32whereSCORE>=0andscore<=100
/*10*/
createtabletest3_10as
select*frompub.student_course_32naturaljoinpub.teacher_course
whereSIDin（selectSIDfrompub.student）andCIDin（selectCIDfrompub.course）andTIDin（selectTIDfrompub.teacher）andSCORE>=0andscore<=100
/*--------------test4_01--------------*/createtabletest4_01asselect*frompub.student_41;
ALTERTABLEtest4_01ADDsum_scorenumber;
updatetest4_01setsum_score=
（selectsum（score）frompub.student_courseawheretest4_01.sid=a.sidgroupbysid）;
/*--------------test4_02--------------*/
createtabletest4_02asselect*frompub.student_41;
ALTERTABLEtest4_02ADDavg_scorenumber;
updatetest4_02setavg_score=
（selectround（avg（score）,1）frompub.student_courseawheretest4_02.sid=a.sid
groupbysid）;
/*--------------test4_03--------------*/
createtabletest4_03asselect*frompub.student_41;
ALTERTABLEtest4_03ADDsum_creditnumber;
updatetest4_03setsum_credit=
（
withaas（select*frompub.student_coursenaturaljoinpub.course）（selectsum（credit）froma
whereSCORE>=60andtest4_03.sid=a.sidgroupbysid））
/*4*/
createtabletest4_04asselect*frompub.student_41;
updatetest4_04setdname=
（selectdidfrompub.departmentwheretest4_04.dname=pub.department.dname）
wherednamein（selectdnamefrompub.department）;
/*5*/
createtabletest4_05asselect*frompub.student_41;
ALTERTABLEtest4_05adddidvarchar
（2）;
ALTERTABLEtest4_05ADDsum_scorenumber;
ALTERTABLEtest4_05ADDavg_scorenumber;
ALTERTABLEtest4_05ADDsum_creditnumber;
updatetest4_05setsum_score=
（selectsum（score）frompub.student_courseawheretest4_05.sid=a.sidgroupbysid）;
updatetest4_05setavg_score=
（selectround（avg（score）,1）frompub.student_courseawheretest4_05.sid=a.sid
groupbysid）;
updatetest4_05setsum_credit=
（
withaas（select*frompub.student_coursenaturaljoinpub.course）（selectsum（credit）froma
whereSCORE>=60andtest4_05.sid=a.sidgroupbysid）
）;
updatetest4_05setdid=（selectdidfrompub.departmentwherepub.department.dname=test4_05.dname）wherednamein（selectdnamefrompub.department）;
updatetest4_05setdid=（selectdidfrompub.department_41wherepub.department_41.dname=test4_05.dname）wherednamein（selectdnamefrompub.department_41）;
updatetest4_05setdid='00'wheredidisnull;
/*6*/
createtabletest4_06asselect*frompub.student_42;
updatetest4_06setname=replace（name,'',''）;
/*7*/
createtabletest4_07asselect*frompub.student_42;
updatetest4_07setSEX=substr（trim（SEX）,1,1）wherelength（SEX）<>1;
/*8*/
createtabletest4_08asselect*frompub.student_42;
updatetest4_08setCLASS=substr（CLASS,1,4）wherelength（CLASS）<>4;
/*9*/
createtabletest4_09asselect*frompub.student_42;
updatetest4_09setAGE=112-to_number（substr（BIRTHDAY,8,2））whereAGEisnull;
/*10*/
createtabletest4_10asselect*frompub.student_42;
updatetest4_10setname=replace（name,'',''）,DNAME=replace（DNAME,'',''）;
updatetest4_10setSEX=substr（trim（SEX）,1,1）wherelength（SEX）<>1;
updatetest4_10setCLASS=substr（CLASS,1,4）wherelength（CLASS）<>4;
updatetest4_10setAGE=112-to_number（substr（BIRTHDAY,8,2））whereAGEisnull;
createtabletest5_10（testvarchar（20）,agenumeric（3））;
insertintotest5_10values（'结果1',88）;
insertintotest5_10values（'结果2',90）;
insertintotest5_10values（'结果3',90）;
insertintotest5_10values（'结果4',86）;
insertintotest5_10values（'结果5',90）;
insertintotest5_10values（'结果6',90）;
insertintotest5_10values（'结果7',86）;
insertintotest5_10values（'结果8',86）;
insertintotest5_10values（'结果9',76）;
insertintotest5_10values（'结果10',86）;
/*1*/
createviewtest6_01as
selectsid,name,dnamefrompub.student
whereage<20anddname='物理学院'orderbysid
/*2*/
createviewtest6_02as
withtemp_table（sid,ss）as
（selectsid,sum（score）frompub.student_coursegroupbysid）selectsid,name,dname,ssassum_scorefrompub.studentnaturaljointemp_table
whereclass='2009'anddname='软件学院'
/*3*/
createviewtest6_03as
select*frompub.student_coursenaturaljoinpub.studentwhereclass='2010'anddname='计算机科学与技术学院'andcid=
（selectcidfrompub.coursewherename='操作系统'）
/*4*/
createviewtest6_04as
selectsid,pub.student.namefrompub.student_coursenaturaljoinpub.student
wherescore>90andcid=
（selectcidfrompub.coursewherename='数据库系统'）
/*5*/
createviewtest6_05as
selectsid,cid,name,scorefrompub.student_coursenaturaljoinpub.course
wheresidin（selectsidfrompub.studentwherename='李龙'）
/*6*/
createviewtest6_06as
withaas（selectsid,count（*）astotcfrompub.student_coursegroupbysid）
selectsid,namefrompub.studentwheresidin（selectsidfromawheretotc>=（select
count（*）frompub.course））
/*7*/
createviewtest6_07as
select*fromtest6_06wheresidnotin（
selectdistinctsidfrompub.student_coursewheresidin（selectsidfromtest6_06）
andscore<60）
/*8*/
createviewtest6_08as
selectcid,namefrompub.course
wherefcidin（selectcidfrompub.coursewherecredit=2）
/*9*/
createviewtest6_09as
witha（sid,sum_credit）as
（selectsid,sum（credit）assum_credit
frompub.student_coursenaturaljoinpub.coursewhereSCORE>=60groupbysid）
selectsid,name,sum_creditfrompub.studentnaturaljoinawhereclass='2010'anddname='化学与化工学院'
/*10*/
createviewtest6_10as
selectcid,namefrompub.course
wherefcidin
（selectcidfrompub.coursewherefcidisnotnull）
/*1*/
createtabletest7_01as
selectFirst_name,count（*）asfrequencyfrom（selectsubstr（NAME,2）asFirst_namefrompub.student）groupbyFirst_name
/*2*/
createtabletest7_02as
selectletter,count（*）asfrequencyfrom（
selectsubstr（NAME,2,1）asletterfrompub.student
unionall
selectsubstr（NAME,3）asletterfrompub.studentwherelength（Name）=3）groupbyletter
/*3*/
createtabletest7_03as
withaas（
selectsid,sum（credit）astotfrompub.student_coursenaturaljoinpub.coursewhereSCORE>=60groupbysid）,
bas（select*frompub.studentnaturalleftjoina）,
cas（selectdname,class,count（sid）asp_countfrombgroupby（dname,class））,das（selectdname,class,count（sid）asp_count1frombwheretot>=10groupby（dname,class））,
eas（selectdname,class,count（sid）asp_count2frombwheretot<10ortotisnullgroupby（dname,class））
select*fromcnaturalleftjoindnaturaljoine
updatetest7_03setp_count1=0wherep_count1isnull;
/*4*/
createviewtest7_04_v1as
select*frompub.studentnaturalleftjoin（
selectsid,sum（credit）astotfrompub.student_coursenatural
升级会员 