数据库原理与技术简明教程(第二版)课后习题参考答案

第1章单选题：BBBBC/DCABA简述题：1．略2．星期节数课程星期一1语文星期二1数学星期一2数学星期一3英语。。。。。。。。。。。。。。。。。。姓名养老保险失业保险医疗保险住房公积金张三3005075400李四2705080380王五28050905003．略4．略5．略6．略7．略8．略9．略第2章实践环节设计：1．CREATETABLEStudent(Snochar(7)PRIMARYKEY,Snamechar(10)NOTNULL,Ssexchar(2)CHECK(Ssex='男'ORSsex='女'),SagetinyintCHECK(Sage=15ANDSage=45),Sdeptchar(20)DEFAULT'计算机系')CREATETABLECourse(Cnochar(10)NOTNULL,Cnamechar(20)NOTNULL,CcredittinyintCHECK(Ccredit0),SemestertinyintCHECK(Semester0),PeriodintCHECK(Period0),PRIMARYKEY(Cno))CREATETABLESC(Snochar(7)NOTNULL,Cnochar(10)NOTNULL,Gradetinyint,CHECK(Grade=0ANDGrade=100),PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno),FOREIGNKEY(Cno)REFERENCESCourse(Cno))2．Insertintostudentvalues(‘9512101’,’李勇’,’男’,19,’计算机系’)Insertintocoursevalues(‘co1’,’数据库原理’,3,4,64)Insertintoscvalues(‘9512101’,’co1’,90)3．DropTABLEstudent4ALTERTABLESCADDXKLBchar(4)5ALTERTABLESCALTERCOLUMNXKLBchar(6)6Altertablescdropcolumnxklb7SELECTSno，SnameFROMStudent8SELECTSnameFROMStudentWHERESdept='计算机系'9SELECTSname,SageFROMStudentWHERESage20或：SELECTSname,SageFROMStudentWHERENOTSage=2010SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23此句等价于：SELECTSname,Sdept,SageFROMStudentWHERESage=20ANDSage=2311SELECTSname,SsexFROMStudentWHERESdeptIN('信息系','数学系','计算机系')此句等价于：SELECTSname,SsexFROMStudentWHERESdept='信息系'ORSdept='数学系'ORSdept='计算机系‘12SELECT*FROMStudentWHERESnameLIKE‘张%’13SELECT*FROMStudentWHERESnameLIKE‘[张李刘]%’14SELECTSname,SnoFROMStudentWHERESnameLIKE'_[小大]%'15SELECTSno,CnoFROMSCWHEREGradeISNOTNULL16SELECTSnameFROMStudentWHERESdept=计算机系'ANDSage2017SELECTSno,GradeFROMSCWHERECno='c02'ORDERBYGradeDESC18SELECT*FROMStudentORDERBYSdept,SageDESC19SELECTCOUNT（DISTINCTSno）FROMSC20SELECTSUM(Grade)FROMSCWHERESno='9512101'21SELECTAVG(Grade)FROMSCWHERECno='C01'22SELECTMAX(Grade),MIN(Grade)FROMSCWHERECno='C01'23SELECTCnoAS课程号,COUNT(Sno)AS选课人数FROMSCGROUPBYCno24SELECTSno学号,COUNT(*)选课门数,AVG(Grade)平均成绩FROMSCGROUPBYSno25SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)226SELECTSno,AVG(Grade)平均成绩,COUNT(*)修课门数FROMSCGROUPBYSnoHAVINGCOUNT(*)=227SELECTSname,Cno,GradeFROMStudentJOINSCONStudent.Sno=SC.SnoWHERESdept='计算机系'28SELECTSname,Cname,GradeFROMStudentsJOINSCONs.Sno=SC.SnoJOINCoursecONc.Cno=SC.CnoWHERESdept='信息系'ANDCname='VB'29SELECTS2.Sname,S2.SdeptFROMStudentS1JOINStudentS2ONS1.Sdept=S2.SdeptWHERES1.Sname='刘晨'ANDS2.Sname!='刘晨’或SELECTSname,SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname='刘晨')30SELECTStudent.Sno,Sname,Cno,GradeFROMStudentLEFTOUTERJOINSCONStudent.Sno=SC.Sno也可以用右外连接实现：SELECTStudent.Sno,Sname,Cno,GradeFROMSCRIGHTOUTERJOINStudentONStudent.Sno=SC.Sno31SELECTSno,SnameFROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECnoIN(SELECTCnoFROMCourseWHERECname='数据库原理'))用多表连接实现：SELECTStudent.Sno,SnameFROMStudentJOINSCONStudent.Sno=SC.SnoJOINCourseONCourse.Cno=SC.CnoWHERECname='数据库原理'32SELECTSno,GradeFROMSCWHERECno='c02'ANDGrade(SELECTAVG(Grade)FROMSCWHERECno='c02')33INSERTINTOStudentVALUES(‘9521105’,‘陈冬’,'男',18,'信息系')34UPDATEStudentSETSage=21WHERESno='9512101'35DELETEFROMSCWHEREGrade6036DELETEFROMSCWHEREGrade60ANDSnoIN(SELECTSnoFROMStudentWHERESdept=‘计算机系’)用多表连接实现DELETEFROMSCFROMSCJOINStudentONSC.Sno=Student.SnoWHERESdept=‘计算机系’ANDGrade6037deletefromscwheresnoin(selectsnofromstudentwheresname=’李勇’)andcnoin(selectcnofromcoursewherecname=’数据库原理’)多表连接方式：deletefromscfromscjoinstudentonsc.sno=student.snojoincourseonsc.cno=course.cnowheresname=’李勇’andcname=’数据库原理’38selectstudent.*,course.*fromstudentjoinsconstudent.sno=sc.snojoincourseoncourse.cno=sc.cnowheresnoin(selectsnofromscgroupbysnohavingcount(cno)2)派生关系(没调试，可以不讲)：selectstudent.*,course.*fromstudentjoinsconstudent.sno=sc.snojoincourseoncourse.cno=sc.cnojoin(selectsnofromscgroupbysnohavingcount(cno)3)astt(sno)ontt.sno=student.sno39(方法可以有很多种)select*fromstudentwheresnoin(selectsnofromscwheregrade=(selectmax(grade)fromscwherecno=(selectcnofromcoursewherecname=’数据库原理’)andcno=(selectcnofromcoursewherecname=’数据库原理’)))40select*fromstudentwheresnoin(selectsnofromscjoin(selectmax(grade),cnofromscgroupbycno)asmax_grade(m_grade,cno)onsc.cno=max_grade.cnoandsc.grade=max_grade.m_grade)41select*fromstudentwheresnoin(selectsnofromscgroupbysnohavingcount(*)=all(selectcount(*)fromscgroupbysno))42略43略44selecttop3withtiessname,sdept,gradefromstudentjoinsconStudent.sno=sc.snojoincourseoncourse.cno=sc.cnowherecname=’VB’orderbygradedesc45selectsname,sdeptfromstudentwheresnonotin(selectsnofromscwherecno=’c01’)Selectsname,sdeptfromstudentwherenotexists(select*fromscwheresno=student.snoandcno=’c01’)46selectTop3sname,sage,sdeptfromstudentorderbysagedesc47selectcno,count(*)astotal,avg(grade)asavggrade,max(grade)asmaxgrade,min(grade)asmingradefromstudentjoinsconstudent.sno=sc.snowheresdept=’计算机系’groupbycno48selectc1.cname,c1.creditfromcoursec1joincoursec2onc1.credit=c2.creditwherec2.cname=’数据结构’49selectc.cno,cnamefromcoursecleftjoinsconc.cno=sc.cnowheresc.cnoisnull50selectsname,sexfromstudentwheresnonotin(Selectsnofromscjoincourseonsc.cno=course.cnowherecname=’VB’)Andsdept=’计算机系’习题：单选题：BBADA/CBAAD/BDDAB/CCCBB简述题：已知有顾客购买