数据库系统概论试题及答案

1ABCDA2ADBMSBE-RCDA3A(1)()(2)(3)(4)B(1)()(2)(3)(4)C(1)()(2)(3)(4)D(1)(2)(3)(4)B4.ABCDB5ABCDC6.ABDBMSCDC7.ABCDD8.ABCDB9.ABCDA10.ABCDABCDBB11.(DB)(DBS)(DBMS)ADBSDBDBMSBDDMSDBDBSCDBDBSDBMSDDBSDBDBMSA12.ABCDD13.(DBMS)ABCDD14.(DBMS)ABCDB15.ABCDA16ABCDA17.ABCDC18.ABCDB19.ABCDD20ABCDB21.ABCDD22ABCDD23.ABCDC24.A11B1mCmnD111mC25.E.FCodd1970ABCDC1.2.3DBMS4.45.6.DBMS7.8.9.10.111mmn111.?2.?()()()()()()3.?(DBMS)(DBS)DBMSDBMSDBMSDBMS4.??5ER21ABCDB2ABCDC3RSRSmnn1ABCDD4ABCDC5ABCDA6ABCDC7RfRARRBRRCf(R)Df(R)FC8R1R2R3R2R1ABCACD121XYyR3ABCDEACC112XYyMMNIIJABCDD1(12,n)2DEM125MNMIJK34567()()S(S#,SNAME,AGE,SEX)C(C#,CNAME,TEACHER)SC(S#,C#,GRADE)(1)(C#)(CNAME)(2)21(S#)(SNAME)(3)(SNAME)(4)(C#)(5)(S#)(6)(C#)(CNAME)(7)(S#)(8)k1k5(S#)(9)(SNAME)(10)2(S#)(11)C(S#)(SNAME)(1).C#,CNAME(TEACHER=(C))(2).S#,SNAME(AGE21SEX=(C))(3).SNAME{s[S#,C#scC#(TEACHER=(C))]}(4).C#(C)-C#(SNAME=(S)SC)(5).S#([1]=[4][2][5](SCSC))(6).C#,CNAME(C(S#,C#(sc)S#(S)))(7).S#(SCC#(TEACHER=(C)))(8).S#,C#scC#(C#=k1C#=k5(C))(9).SNAME{s[S#,C#scC#(C)]}(10).S#,C#scC#(S#=2(SC))(11).S#,SNAME{s[S#(SCCNAME=C(C))]}RSRSRABCDabcdabefabhkbdefbddlckcdckef3SQL1SQLABCDB2SQLABCDC3SQLABCDB4SQLSQLASQLBSQLCSQLDSQLC5S(S#SNAMESEXAGE)C(C#CNAMETEACHER)SC(S#C#GRADE)COMPUTERASBSCCCSSCDSCSCD6SQLA(02503720)B(00504750)SCDcdefC001700D03803B7SQLstudentCREATETABLEstudent(NOC(4)NOTNULLNAMEC(8)NOTNULLSEXC(2)AGEN(2))studentA(103123)B(1031NULLNULL)C(NULL23)D(1031NULL23)B811SCSCS(S#SNSEXAGEDEPT)C(C#CN)SC(S#C#GRADE)S#SNSEXAGEDEPTC#CNGRADE8SELECTASELECTSNAGESEXFROMSWHEREAGE(SELECTAGEFROMSWHERESN=)BSELECTSNAGESEXFROMSWHERESNCSELECTSNAGESEXFROMSWHEREAGE(SELECTAGEWHERESN=)DSELECTSNAGESEXFROMSWHEREAGEAGEA9C2SELECTASELECTS#FORMSCWHEREC#=C2ANDGRAD(SELECTGRADEFORMSCWHEREC#=C2)BSELECTS#FORMSCWHEREC#=C2ANDGRADEIN(SELECTGRADEFORMSCWHEREC#=C2)CSELECTS#FORMSCWHEREC#=C2ANDGRADENOTIN(SELECTGRADEFORMSCWHEREC#=C2)DSELECTS#FORMSCWHEREC#=C2ANDGRADEALL(SELECTGRADEFORMSCWHEREC#=C2)D10SELECTASELECTSSNSCC#SCGRADEFROMSWHERESS#=SCS#BSELECTSSNSCC#SCGRADEFROMSCWHERESS#SCGRADECSELECTSSNSCC#SCGRADEFROMSSCWHERESS#=SCS#DSELECTSSNSCC#SCGRADEFROMSSCC11()SELECTASELECTS#SUM(GRADE)FROMSCWHEREGRADE=60GROUPBYS#ORDERBY2DESCHAVINGCOUNT(*)4WHEREC#=C2ANDGRADE(SELECTGRADEFORMSCWHEREC#=C2)BSELECTS#FORMSCWHEREC#=C2ANDGRADEIN(SELECTGRADEFORMSCWHEREC#=C2)CSELECTS#FORMSCWHEREC#=C2ANDGRADENOTIN(SELECTGRADEFORMSCWHEREC#=C2)DSELECTS#FORMSCWHEREC#=C2ANDGRADEALL(SELECTGRADEFORMSCWHEREC#=C2)D1SQL23RR(NoNAMESEXAGECLASS)NONONAMESEXAGECLASSSQL(252195031)950313010951019509120INSERTINTORVALUES(252195031)INSERTINTOR(NONAMECLASS)VALUES(3095031)UPDATERSETNAME=WHERENO10UPDATERSETCLASS95091WHERECLASS95101DELETEFROMRWHERENO=20DELETEFROMRWHERENAMELIKE31SS#SNAMEAGESEXSCS#C#GRADECC#CNAMES#C#SNAMEAGESEXGRADECNAMESQL1MATHS2C1C2318201820480561SELECTSNAME,AGEFROMS,SC,CWHERES.S#=SC.S#ANDC.C#=SC.C#ANDCNAME=MATHS(2)SELECTS#FROMSCWHERECNO=C1ANDS#IN(SELECTS#FROMSCWHERECNO=C2)(3)SELECTS#,SNAME,AGEFROMSWHEREAGEBETWEEN18AND20(4)SELECTS#,AVG(GRADE)FROMSCGROUPBYS#HAVINGAVG(GRADE)80(5)SELECTSNAMEFROMSWHERENOTEXISTS(SELECT*FROMCWHERENOTEXISTS(SELECT*FROMSCWHERES#=S.S#ANDC#=C.C#))(6)SELECTSNAMEFROMS,SCWHERES.S#=SC.S#GROUPBYSNAMEHAVINGCOUNT(*)32-StudentSnoSnameSexSageSdeptCourseCnoCnameCcreditSCSnoCnoGradeSnoSnameSexSageSdeptCnoCnameCcreditGradeSQL110422S_CS_VIEWSnoSnameSex;324datascore_view(1)SELECTDISTINCTSNOFROMSCSCXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.SNO='1042'ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.SNO=SCX.SNOANDSCZ.CNO=SCY.CNO))(2)CREATEVIEWS_CS_VIEWASSELECTSNO,SNAME,SEXFROMSTUDENTWHERESdept=CS(3)UPDATES_CS_VIEWSETSNAME=WHERESNAME=(4)CREATEVIEWdatascore_viewASSELECTSNOSNAMEGRADEFROMSTUDENT,SC,COURSEWHERESTUDENT.SNO=SC.SNOANDCOURSE.CNO=SC.CNOANDCNAME=41ABCDB2ABCDA3ABCDB4ABCDB5ABCDD6SQLZHAOSTUDXHAREVOKEUPDATE(XH)ONTABLEFROMZHAOBREVOKEUPDATE(XH)ONTABLEFROMPUBLICCREVOKEUPDATE(XH)ONSTUDFROMZHAODREVOKEUPDATE(XH)ONSTUDFROMPUBLICC7SCGRADEZHAOSQLAGRANTGRADEONSCTOZHAOBGRANTUPDATEONSCTOZHAOCGRANTUPDATE(GRADE)ONSCTOZHAODGRANTUPDATEONSC(GRADE)TOZHAOC8SQLServerAROLLBACKB.DROPCDELALLOCATED.DELETEB12345SQLGRANTREVOKE6DBMS________________7SQLServerSQLINSERTDELETE,UPDATE51A.B.C.D.D2A.B.C.D.C3A.InsertB.DeleteC.UpdateD.CreateD4____ABCDB50100________ABCDC1.__234DBMS0100createtriggerstu_ins_trionscforinsertasbegindeclare@s#char(6),@c#char(5),@gradeintselect@s#=sno,@c#=cno,@grade=scorefrominsertedif(@s#notin(selectsnofromstudent))or(@c#notin(selectcnofromcourse))or(@gradenotbetween0and100)rollbacktransactionelseprint''end61ABCDAD2ABCDA3ABCDC4R(AB)3NFABCBCNFDACB5A1NFB2NFC3NFDBCNFA6DBA1NFB2NFC3NFDBCNFD7RRA2NFB3NFC4NFDBCNFC8ABCDB91NFA1NFB2NFC3NFD4NFB10A0B1C1DCB11ABB12?()ABCDB1A(SSND)B(DCNNMASBDDS21NF1NF2NF2NF3NF31R(U)UXYXYYXXY2R(U)UXYXYYX,XY1S(SnoSnameSDSdnameCourseGrade)SnoSnameSDSdnameCourseGrade(1)S(2)S?(3)3NF?(1)S:SSnoSnameSDSdnameSnoSD(SnoCourse)GradeSSnoCourse(2)S?:S1NF(SnoCourse)2NF2NFS1(SnoSnameSDSdname)S2(SnoCourseGrade)(3)3NF?:3NFS1SnoSDSDSdnameSdnameSnoS13NFS11(SnoSname,SD)S12(SDSdname)S11S123NFS23NFS(SnoSnameSDSdnameCourseGrade)3NFS11(SnoSnameSD)S12(SDSdname)S2(SnoCourseGrade)2R(1)??(2)??(3)?(1)