资源描述:
《数据库原理和技术简明教程(第二版)课后习题参考答案解析》由会员上传分享,免费在线阅读,更多相关内容在工程资料-天天文库。
1、第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),S
2、deptchar(20)DEFAULT'计算机系')CREATETABLECourse(Cnochar(10)NOTNULL,Cnamechar(20)NOTNULL,CcredittinyintCHECK(Ccredit>0),SemestertinyintCHECK(Semester>0),PeriodintCHECK(Period>0),PRIMARYKEY(Cno))CREATETABLESC(Snochar(7)NOTNULL,Cnochar(10)NOTNULL,Gradetinyint,CHECK(Grade>=0ANDGrade<=100),PRIMARYKEY(Sno,Cno
3、),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)6Alter
4、tablescdropcolumnxklb7SELECTSno,SnameFROMStudent8SELECTSnameFROMStudentWHERESdept='计算机系'9SELECTSname,SageFROMStudentWHERESage<20或:SELECTSname,SageFROMStudentWHERENOTSage>=2010SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23此句等价于:SELECTSname,Sdept,SageFROMStudentWHERESage>=20ANDSage<=2311SELE
5、CTSname,SsexFROMStudentWHERESdeptIN('信息系','数学系','计算机系')此句等价于:SELECTSname,SsexFROMStudentWHERESdept='信息系'ORSdept='数学系'ORSdept='计算机系‘6SELECT*FROMStudentWHERESnameLIKE‘张%’7SELECT*FROMStudentWHERESnameLIKE‘[张李刘]%’8SELECTSname,SnoFROMStudentWHERESnameLIKE'_[小大]%'9SELECTSno,CnoFROMSCWHEREGradeISNOTNULL10S
6、ELECTSnameFROMStudentWHERESdept=计算机系'ANDSage<2011SELECTSno,GradeFROMSCWHERECno='c02'ORDERBYGradeDESC12SELECT*FROMStudentORDERBYSdept,SageDESC13SELECTCOUNT(DISTINCTSno)FROMSC14SELECTSUM(Grade)FROMSCWHERESno='9512101'15SELECTAVG(Grade)FROMSCWHERECno='C01'16SELECTMAX(Grade),MIN(Grade)FROMSCWHERECno='C0
7、1'17SELECTCnoAS课程号,COUNT(Sno)AS选课人数FROMSCGROUPBYCno24SELECTSno学号,COUNT(*)选课门数,AVG(Grade)平均成绩FROMSCGROUPBYSno25SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)>226SELECTSno,AVG(Grade)平均成绩,COUNT(*)修课门数FROMSCGROU