数据库实验《实验》.docx

实脍名称实验实验地点8-318实验类型设计实聆学时1实验日期2018-6-14*携E注意:烧面格式已设置好（不得更改）.次人内容即可.一'实蕤目得K掌握系统数据类型行特点与功能。2、掌握创建.修改次结构得方法.3、掌握插入、更新与删除衣数据得方法.二'实验内容I、我询所有班级得期末成绩平均分,并按照平均分降序排序。2,吉彻教师玷本信息与教报课程信息.其中包括未分配课程得我加信息.3、咨询160501班级中选修了“韩晋升”老师讲授加课程得学生学号'姓名、课程号与期末成绩.4.1询年”课程得课程号、课程名选懂该课程得学生人数.并按所选人依升序推r5,玄御两门及以上课程得期末成绩超过80分得学生.姓名及平均成浦.6,性询入学考试成缄戢高得学生学号.姓名与入学成绩.7、我询同时教授COSl27H与cO5IO9号课程得教师信息，8、性湖至少选修了姓名为“林吟秋”为学生所选修课程中一门课程得学生学号与姓名，9、花询所有强授d）5127号课程和教师信息。10,查询没有被任何学生选修得课程编号、课程名称与学分.IK行询“C语；;”课程期末成绩比“电子技术”课程期末成统高得所有学生得学号与姓名.I2.ifi询所有班级期末平均成绩得出淘分,并将其出假给受量.通过PRINT语句输HI.13、使用游标输出学生姓名、选修课程名称与期末考试成绩.14、住用游标统计每个学院教师所开设课程得选修率.15、使用游标计。学生期末成缄母等级,并更新IeVel列.三'实脸环境1 .操作系统:WiIKk>wsXP2 .开发软件SQ1.Server2008四,提交文档提交本实验报告（电子版,文件名命8：学号姓名<stx；xxxxxwt>.doc皎如格维HJSQT分）行全体学生实妆报告刻入一派光点存档,保江光盘可读.-1M:字幺中211i.小五、附:源代码¾cYtUudentnixAXrGiGnalg平均分from<c<xrgrvupbyItudcntnoorderbyAVGfind2,SekVl'frUruchcr*elat,fn)mMiidemsckc,fromcourseinrtintocourcc<enoxnametype.pehndxrvdit33M5ar。的故牝0侬644.OiMrkvtfr11ncutcinrtinto¼rxvtudcntn(>.curc11i>,uully.11ndl¼uluc'161222KKMJM05I03,.87,00,82、OOinurrlinuIcucheK(eArbCT11o.lnxne.11jt<,p11>f.dcpxtfnt句Utt(M)50”,帮畜升*耿仲工铿供授,计算机学院'Jsckv,fromclax*insertintoCkCbMgdxMunw.dePMmtfn<.nunilna句URl6O5O.计。机,计煤机j成.我MNekvtfraUruch-chs¼insertintoteuchckstte-hcrn>x1.ino.ourw11D句UMW500.60MH.t<sIor)SekVlfraUruchcrektftvfrmcourseNekVtf11xnSCVnr>ekctcla>sn(>.?VGlfinal×平均分fromstudentj»xnmzocv的JU加inStudcniiKi-SCOfC.Muikniiiogroupbycla»nNderbyAVG(GnaI)dew%ekxIeacher.,.-nx11efromIeucherkfljxnIeuch-ckiMcaeuchcr.½ache11o=c*zh.clax.ICilChCn1。kftjotCiMirsCon<c,clas¼.ClaWmoUfHCc<xrscn<>3*cZ*udcnt.Mudcntnn>jmcc1111w.IbuIfromM>dcn<Au>SVOfciudcms<udcn<no-*rofe.(udemnoJOinCcHlXonmu-、(x)un«fM>=$c<xv.c<xrtcn>wherescore,coufcnnm(selectc<>untfcx>frcmtcach.c1.xtjointc&:berBHCUCh-CUM、IctKtKmo-IcactiCf.(cuvlcrno*brretna11xr-,HWft')anddaino-XM>MJ、%ekaonune.OOun<no,cnmne、COIMlwidcinnmfromcuzHtnEUrNeanScW、WUKg-BUrWeurwnugroupbycor*e.eours<t×hC11IMXkTbyCOVNTBMng”IkMr5、ckviMumc.AVG'ftfulr<nscorejoin(udcnonNeUrc、UudciHnir-Mudtfnf-Sludcnlm(“herefnl>80groupbyMukn(ludcnlm,ms11eZVingCouNTtCn0>0r>：26、«Jcc<uudcnt11i>.5nanc.p11tntfrom¼tudcn:when:MUikniiio-selectIaPIEiaJcninutr<n%u)c<><derbyp<nu7、%elatICiiehcr.tcwhcmfume,11u>or.pnfdepnnnvntfnxn¼uclcrxinic(K_cla«*onteacher,0hemo=tcach.cj.ee2cbemowherecrscn<>=,(5l27'8、ckttdincM*kflsicnno,*n>mefn)mjxvinMudcn100s<re.JUdemn»sUHkn1.n>Jcnno*bervxnc11>in(*ctIXlU-fh»m5C5cv尸inSuXknlonscore、Mudencno=MudencStudcnlno*trcSfUHne-,醇吟秋)mu，SlUmC!='"夕秋9,*ekv<f11xnUrachcrwhen:tcachcrnoin(selectcchern>11miitcurh.ck*en:COaJfUcOSIlT)10.*ektcnur0¾11,cnnv£n?d»fromcour%ewhereneMuSekVlf11xnscunrwhere%wrc,ccrct-course,(XhhnowiIKsckvMudcm.Mudcnno.Mjmcfmhwcwl»<»»nSWdCnlUnlSCI、tdcnlnr-uden(.Mudenlnci)Mncourseclom«l.<x>un<fto=cI.CrtUNCnn)*creclan)c-,ci11,andexist*SeioClvfn)m«nre就2joinorcc2OnlM:2.a)urwn=c2.c<xmcoi*hctfc2a11e-'UfH£'und11rl.*ldenlt-c2Sludeinnoandwl.tinal>¼'2.final)12.AxkircInjlXIiumcrici621SekVt11uMAX，均分ifromebz<clM<>如谈>：,AVGlHnab麻T>tfromvorcj<>inUudcnlon<cvres乂IHkInmr-SwdEHludenlmt>cincourveoncou11<.cou11tf0=ware.coUrXCnmwhereGnnlenowllgroupbyclaH11oIPrHUji“X；公期I、TfjA"f!4i,ZV'.Hm八“八"vhj11613.rchfvn11cthw8CndnwIUrhiInK)11alnuncrk62fccUre%c_cur«)rcu11orfor*ekvMmc.vnaw.finalfrxnWOtfrWinSUKknlonscore.tJentnc=Mudcnc.Mudentnoi>tutM?onMX>fC.cour>cno-course.CoSCnOlopenM：-cur«irfetchnelfromxqM)cintoSndnK(CrKHnC.IinJPnnl.学生"X谭H名称明K成婚print-一一.Wtilel÷T<HSTATUS=OIXgnprintNnanec11dmc<j54final»nchan6fetchncxfromsc.<-u>wintoManIinalendcwetc.curorIkuJIoCtttcw_e(MH14.fcvUre*tqx11mennchun?Oinumin<vO(Mll&chn:curcurMirSUIiCforekvdefimcnc<vuu'缩滋卷4!tt,fromCtaW*hccCuM、cbnoin<«kvtMtnknt.ClaMnCfromsnxicnBmuPbyela11ogroupbydqwtnw111OPCncurfetchcurintodcp11nnl.nunWtavgHnclcvlC(M'fromla*where<Jcafin11dqxirtnc11(printdepartmentprim4Vj»WftileHICH-SIS0bcinfetchnextfrncurintodcp11ment.numWlavg-mn¼clc<iCCHJ、l11o<nela>Whe(CdCPajmKnlIlkfXmmCUUprintdcpartx11lprimav*endclosecurIfcoilOCMteCUr15.dec1.mSrUnwncKlr3。ICnAnICh301.finalfkl(ScvhrcMucun>orStMiCfor*ekagmcEiuUnnmefroms<wdcm.scw.coMfsewhevstudent.JIKkntnn=mxv、XlIKkntnOad<<urc.u5eno-xxc.C(XDOopenMufetchstuintoMiamcliiial.<tutfnciIinab-90primV(t'fmnccnsnedxeiffinal>-80undfinal*90print、ft'*%namtf*c11aneelseiful>-70andIiiiul-80printV1P,*Mianurc11anrdeiffiul><0.nlIinnl70printV'*snau:*c11arncelseiffi