《数据库原理》课件上机实验点评及参考答案2017.docx

上机实验三一基本表的建立和修改三、实习内容，1 .启动MSSQ1.SerVer服务,打开ErnerPriSeManagerQueryAna1.yzer-,2 .f.QueryArui1.yzer中用CREATETAB1.E命令在实验二创建的GrtidoManager数据摩中定义基本表：学生表(Student)课程表(Course),利用EnterpriseMiIndger的图形化功能建立班级表(C1.iISS)以及成绩表(GnIdO).酎心往下«!哦,有点“清fcreatetab1.eStudent(Snochar(7)primarykey.SnameVarChar(20)notnu1.1.,Ssexchar(2)notnu1.1.,SageSma1.1.int,C1.nochar(5notnu1.1.);createtab1.eC1.ass(C1.noCharPrimarVkey.Specia1.ityvarchar(20)notnu1.1.,Inyearchar(4)notnu1.1.zNumberinteger,Monitorchar(7);createtab1.eCourse(Cnochar(1.)primarykey,CnameVarChar(20)notnu1.1.,CreditSma1.1.1.nt);createtab1.eGrade(Snochar7referencesStUdenUSno),Cnochar(1.)referencescourse(cno),Gmarknumeric,Primarykey(sno,cno);四、骨对以上四个表，用SQI语言完成以下各项操作，给学生表增加一属性NatiOn(民族)，数据类型为Varehar(20>：IM除学生表中新增的属性Nation：向成绩表中插入记录(-200W1.(F,-3*80)；修改学号为“20011Hr的学生的成绩为70分；删除学号为"00II1.(T的学生的成绩记录：©为学生表创建一个名为IX-C1.aSS的索引.A1.TERTAB1.EStudentADDNationvarchar(20);2.A1.TERTAB1.EStudentDROPCO1.UMNNation;3.Insertintogradesno,cno,gmark)va1.ues(,20011103r.80>点评：该语句没仃语法格误.但好多同学在执行时出现了问虺.是对的,但同学们要知道为什么会出现这个问题？请仔细乔卜.前面的创建表的语句：UPDATEGradeSETGmark=70WHERESno=,2001110,;5.DE1.ETEFROMGradeWHERESno=,2001110,;6.CREATEINDEXIX_C1.assONStudent1.CInoA$c);7.DROPINDEXStudent.1.X_C1.ass;五、思考三s在定义基本表语句时，NOTW1.1.参数的使用有何作用？答：NotNUu参数,可以保证在插入数据时.该属性列的取值不为空,上机实验四一SE1.ECT语句的使用（一）三、实习内容；完成以下各项操作的SQ1.语句：找出所有被学生选修了的课程号：se1.ectdistinctcofromgrade;该语句实现的路径应该是Grade衣，COUrSe我中可能有某门课，个学生都没有选他的.所以不能是S询Course的Cno.找出01311班女学生的个人信息：se1.ect*fromstudentwhereSSeX=女andc1.no='01311,;找出01311班、01312班的学生姓名、性别、出生年份:se1.ectSname,Ssex,2016-Sageasbirthfromstudentwherec1.no="01311'orc1.no='O1312,;OR；se1.ectSname,Ssex,2016Sageasbirthfromstudentwherec1.noin('01311','01312')；OR:(在T-SQ1.中,超大纲了)Se1.ectsnae,sex,year(ge1.dateO)-SageFromStUdenttherec1.noinC013H,01312,)：找出所有姓李的学生的个人信息；se1.ectfromstudentv/hereSname1.ike'李'找出学生李勇所在班级的学生人数：Se1.ectcount()fromstudentWherec1.noin(Se1.ectc1.nofromstudentWhereSname="李勇");Or:Se1.ectnumberfromc1.assBhercc1.noin(Se1.ectc1.nofro®studentWhereSna1.nC="李勇")；Or:Se1.ectnumberfronc1.ass,studentBhereSnatte二'李勇'andc1.ass,c1.nostudent.c1.no：找IH课程名为操作系统的平均成锁、果高分、最低分;Se1.ectavg(gmark),Max(gmark),Min(gmark)Fromgrade,courseWhereCninnO二"操作系统"o-couo;Or:Se1.ectavR(11ark),Max(mark),Min(gaark)FromgradeWherecno=(Se1.ectcnofroncoursewhereCname="操作系统;找出选修了课程的学生人数：se1.ectcount(distinctSno)fromgrade；找出选修了课程操作系统的学生人数.Se1.ectcount(sno)FromgradeWherecno=(Se1.ectcnofromcourseWhereCnaBe="操作系统");Or:Se1.ectcount(sno)Fromgfade.courseWhereRo=oandCnamC="操作系统"<9)找出2000级计免机软件班的成绩为空的学生姓名。se1.ectSnamefromStudentwhereC1.noinIrhCreSPeCiaIity='计算机软件'andInyear=2000)andSnoin(se1.ectSnofronGradewheregmarkisnu1.1.)：四、思考题：什么倘况卜需要使用关系的别名？别名的作用范围是什么？答：股几种两种怙况下使用别名：1.关系名太长，为了简化：2.为了喊自身连接杳询.3.在同一个SQ1.语句中,为了对某个表进行2次扫描.别名的作用范用是当前该SQ1.语句国开当前SQ1.谙句,别名就不在存在.上机实验五一一SE1.ECT语句的使用(二)三、实习内容：完成以下各项振作的W1.诏句：找出与李勇在同一个班级的学生信息：找出所有与李勇有相同选修课的学生信息：找出年龄介丁学生李勇和25岁之间的学生信总：(已知李勇年龄小于25岁)找出选修了课程操作系统的学生学号和姓名找出没有选修1号课程的学生姓名找出选修了全部课程的学生姓名：找出与李勇在同一个班级的学生信息：Se1.ectfromstudentWherec1.no=(Se1.ectc1.nofro三st<kntwhereSname='李勇')；Or：本物还可以用(i身连接进行ft.找出所有与学生李勇有相同选修课程的学生信息se1.ectfromStudentwhereSnoin(se1.ectSnofromGradehereCnoin(se1.ectCnofronGradewhereSnoin(se1.ectSnofromStudentwhereSna三e='李勇)andSname<>'李勇；找HI年龄介于学生李勇和25岁之间的学生信息；Se1.ect*fromstudentWheresagebetween(Se1.ectsagefromstudentBhereSnamO=李安)And25;找出选修了课程操作系统的学生学号和姓名；Se1.ectsno,snamefro三studentWheresnoin(Se1.ectsnofrongradeWherecno=Se1.ectcnofroncourseBherecn<me='操作系统)；找出所有没布.选修1号课程的学生姓名：Se1.ectsnamefromstudentWherenotexist(Se1.ectsnofromgradewhereCnO=TandSnO=S1.UdCn1.sno):OR:Se1.ectsnamefromstudentUheresnonotin(Se1.ectsnofromgradeherecno=,);Or：用集合运算se1.ectSnamefromStudentwereSnOin(se1.ectSnofromGradeexceptse1.ectSnofromGradewhereCr>=1);找出选修了全部课程的学生姓名(提示：可找出这样的学生,没有一门课程是他不选修的。)卜面这个编程的思路有间踽，是错误的哦：SE1.ECTSnameFROMStudentWHERI:SnoIN(SE1.ECTSnoFROMGrade叫ERECnoI)ANDSnoINtSE1.ECTSnoEROMGrade!HERECno=2)ANDSnoIN(SE1.ECTSnoFROMGradeIiHERECnO=3)ANDSnoIN(SE1,RCTSnoFROMGradeHHERECno=I)NDSnoIN(SE1.ECTSnoFROMGradeWiERECno=5)ANDSnoINSE1.ECTSnoFROMGradeWHERECM=6)ANDSnoINfSE1.ECTSno1.ROMGradeBHERECno=7)解一:Se1.ectSnan1.efromstudentBherenotexists(Se1.ect*fromcoursewherenotexists(Se1.ect*fromgradeWheresno=student.snoandcno=o);解二：Se1.ectsnamefronstudentBheresnoin(Se1.ectsnofrongradeGroupbysnoHavingcount()=Se1.ectcount()fromcourse);OR:Se1.ectsnamefromstudentBhere(Se1.ectcount(cno)fro三gradeGroupbysno)=(Se1.cctcount()fromcourse);解法三：Se1.ectSnaaeFromStudentWHEREnotexists(Se1.ectCnoFronCourseexceptSe1.ectCnoFromGradeMHI-RIiStudent.Snn-Grade.Sno);类姒表达的还有；1.SHI.ECTSnag1.ROMStudentHERE(se1.ectcount(cno)froncourse)=(SE1.ECTcount(sno)FKOMGradeGROUPBYSno)2.SE1.ECTSnaroeFROMStudentWHERESnoANY(SE1.ECTSnoFROMGradeGROUPBYSnoHAVING(COUW()=(SE1.ECTCO1.NT()FROMCourse)ORDERBYSnarne:se1.ectSnamefromStudentwhereSnoin(se1.ectSnofromGradewhereCno>-a1.(SeteCICnofromCourse)(I)查询选修了3号课程的学牛.学号及成绩，并按成绩的降序排列se1.ectSno1GmarkfromGradewhereCno='3'orderbyGmarkdesc;(2)查询全体学生信息，要求杳询结果按班级号升序排列，同一班级学生按年龄降序排列se1.ect,fromStudentorderbyCIno1Sagedesc;(3)求每个课程号及其相应的选课人数se1.ectCno1Count(Sno)as选课人数fromGradegroupbyCo;(4)查询选修了3门以上课程的学生.学号se1.ectSno1Count(Cno)as选课门数fromGradegroupbySnohavingcount(Cno)>3;se1.ectsnofromGradegwhere(se1.ectCOUNT(cno)fromGradewhereSno=g.Sno)>3;1 .用UNIsi或UN1.oNA1.1.籽两个SE1.ECT命令结合为一个时.结果有何不同？2 .当四能用连接词杳珈又能用嵌套告ifU时，应该选择哪种伤血较好？为什么？3 .库函数能否白接使用在：SE1.ECT选取目标、HAV1.NG子句、常HERE子句、GRoUpBY列名中？上机实验六一SQ1.的存储操作三、实习内容，完成以下各项掾作的SQI语句：将01311班全体学生的成绩置零：删除2001级计©机软件的全体学生的选课记录：学生李勇已退学，从数据库中删除有关他的记录：对每个班，求学生的平均年龄，并把结果存入数据陈.将01311班的全体学生的成绩跟零：updategradesetgtark=0wheresnoin(se1.ectsnofromstudentwherec1.no=/0131)剧除01311班全体学生的选课记录：de1.etefro三gradewheresnoin(se1.ectsnofromstudentwherec1.no='01311'学生李勇已退学从数据库中删除有关他的记录。注意：数据的一致性删除表的次序：Grade/c/asstudentUPDATEC1.assSETMonitor=CaseWIENMonitor=(SE1.ECSnoFROMStudent叫IERESname='李勇,)THEN,endFROMC1.assVHEREC1.noin(SE1.ECTC1.noFROMStudentWHERESnHa李勇')de1.etefromgradewhere$noin(se1.ectsnofromstudentwhereSname='李勇)；updatedosssetnumber=number-1wherec1.no三(se1.ectdnofromstudentwhereSname='李勇);de1.etefromstudentwhereSname='李勇'作业点评：有同学的谙旬如下:de1.etefromstudentajoinGradebona.Sn>=b.SnojoinCourseconc.Cno=b.CnowhereSname='李勇'注息；ANSISQ1.的定义是De1.。S语句只能对一个表进行除操作I对珞个班，求学生的平均年龄，并把结果“人数据冻；解法一，建表、插入createtab1.eavgage(c1.nochar(5)»avgagesma1.1.int);insertintoavgagese1.ectc1.no,avg(sage)fromstudentgrpupbyc1.no;解法二：建视图createviewv_avgageasse1.ectc1.no.avg(sage)asavg_agefromstudent解法三：修改CIaSS表后插入a1.tertab1.ec1.assaddavgagesma1.1.intgoupdatec1.asssetavgage=(se1.ectaRc(sacfromstudentgroupbyc1.nohavingstudent,c1.no=c1.ass,c1.no)解法四：直接建表插入se1.ectc1.no,avg(sagc)asavgageintoavgage下面这种根据表中的数据进行潟程的解法的思维是错误的,数据是随时都会变化的(虽然CaSe语句用的不错©>：a1.tertab1.eC1.assaddCagesma1.Iininu1.IupdateC1.asssetCagC=CaSCwhenC1.no='0031then(se1.ectAVG(Sage)fromStudentwhereC1.no,00311')whenC1.no-'00312,then(se1.ectAVG(Sage)fromStudentwhereC1.no-*00312')whenC1.no'0131then(se1.ectAVG(Sage)fromStudentwhereC1.no=,0131.)endfromC1.ass五、思考题，DROP命令和DEIETE命令的本质区别是什么？19.视图操作createviewStu-OO312-1.asse1.ectfromStudentwhereStudent.Snoin(se1.ectSnofromGradev/hereGrade.Cno=,1.')adStudent.C1.no三,00312,createviewStu_OO312_22asse1.ect*fromStudentv/hereStudent.Snoin(se1.ectSnofromGradewhereGrade.Cno='1.'andGrade.Gmark<=60)andStudent.C1.no='00312,createviewStu_year-3asse1.ectSnozSname,2OO5-SagebirthyearfromStudentse1.ectSname-4fromStu-yearv/herebirthyear>1983se1.ectSno,Sname,2005-Sage5fromStu_01312.2实会八完整性约束(sochar(7)primarykeyrsamevarchar(20)notnu1.1.,SSeXchar(2)notnu1.1.check(sscx时男，女,)defau1.t('W1)/sageSma1.1.1.ntCheCk(SagCV65andsage>14)rc1.nochar(5)notnu1.1.)a1.tertab1.ecourseaddprimaryke(cno);a1.tertab1.ecourseaddconstraintPJourseprimarykey(cno);a1.tertab1.ecourseaddconstraintchk-credcheck(editin(1,2,3,4,5,6);a1.tertab1.ec1.assaddprimarykey(c1.no);a1.tertab1.ec1.assaddcheck(number>1.andnumber<100);a1.tertab1.ec1.assaddforeignkey(monitor)referencesstudct(sno);a1.tertab1.estudentaddforeignkey(c1.no)referencesdass(c1.no);a1.tertab1.egradeaddprimarykey(snofcno);a1.tertab1.egradeaddforeignkey(cno)referencescourse(co);a1.tertab1.egradeaddnstraintchk-markcheck(gmark>=OandGmark<=100);点评：要注意外部码定义的时候，首先被参照关系(父表必须已经定义了主码(或者唯一性约束定义)，另外如果表中已经有数据的，表中己有数据不能违反参照完整性约束。假如数据库中已有StUdent、Grade、CoUrSe和C1.aSS表，再添加以上约束:a1.tertab1.eStudentaddCheck(Sse×1.n¾r)>a1.tertab1.eStudentadddefau1.t'男，forsse×a1.tertab1.eStudentaddcheck(Sage>14andSage<65)a1.tertab1.eC1.aSS先给C1.aSS我创卜世，否则StUdent不能it立与CIaSS的外部约束addprimarykey<c1.no1.a1.tertab1.eStudentaddforeignkeyc1.no)referencesC1.ass(c1.no)onupdateCASCADECouric表a1.tertab1.eCourseaddprimarykey(cno)a1.tertab1.eCourseaddcheck(Creditin<1.z2,3,4tS,6)>C1.ass及a1.tertab1.eC1.assaddcheck<Number>1.andNumber<100)a1.tertab1.eC1.assaddforeignkey(Monitor)referencesStudent(sno)Grade表a1.tertab1.eGradeaddforeignkey(sno)referencesStudent(ano)a1.tertab1.eGradeaddforeignkey(cno)referencesCourse(c11o>a1.tertabicGradeaddcheckGrrark>0KrJdGr11rk<100)