2023年吉大数据库应用技术在线作业一答案.doc

吉大数据库应用技术在线作业一答案试卷总分：100测试时间：-试卷得分：100单项选择题一、单项选择题（共25道试题，共100分。）得分：100V1.Giventhefollowingtable:TestTableC1-12345AndifthefollowingCLIcallsaremade:SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv);SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,0);SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc);SQLConnect(hdbc,(SQLCHAR*)db,SQL_NTS,(SQLCHAR*)userid,SQL_NTS,(SQLCHAR*)password,SQL_NTS);SQLSetConnectAttr(hdbc,SQL_ATTR_AUTOCOMMIT,SQL_AUTOCOMMIT_OFF,0);SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);SQLPrepare(hstmt,(unsignedchar*)select*fromTestorderbyC1,SQL_NTS);SQLBindCol(hstmt,1,SQL_C_SHORT,&data,0,NULL);SQLExecute(hstmt);SQLFetch(hstmt);printf(Data:%in,data);SQLFetch(hstmt);printf(Data:%in,data);SQLFetch(hstmt);printf(Data:%in,data);SQLEndTran(SQL_HANDLE_ENV,henv,SQL_COMMIT);SQLFetch(hstmt);printf(Data:%in,data);Whichofthefollowingwillbereturnedbytheprogram?A.Data:1Data:2Data:3Data:3B.Data:1Data:2Data:3Data:4C.Data:1Data:2Data:3Data:1D.Data:1Data:2Data:3Data:5满分：4分得分：42.Giventhecode:EXECSQLDECLAREcursor1CURSORFORSELECTname,age,b_dateFROMperson;EXECSQLOPENcursor1;Underwhichofthefollowingsituationswilltheabovecursorbeimplicitlyclosed?A.WhenaCLOSEstatementisissuedB.WhenaCOMMITstatementisissuedC.WhentherearenorowsintheresultsetD.WhenallrowsareFETCHedfromtheresultset满分：4分得分：43.Giventheapplicationcode:EXECSQLDECLAREcurCURSORWITHHOLDFORSELECTc1FROMt1EXECSQLOPENcurEXECSQLFETCHcurINTO:hv/*Statement1*/EXECSQLCOMMIT/*Statement2*/EXECSQLFETCHcurINTO:hv/*Statement3*/EXECSQLROLLBACK/*Statement4*/EXECSQLCLOSEcur/*Statement5*/IfthetableT1hasnorowsinit,whichstatementwillcausethecursorcurtobeclosedfirst?A.Statement1B.Statement2C.Statement3D.Statement4满分：4分得分：44.HowmanyrowscanberetrievedusingasingleSELECTINTOstatement?A.OnlyonerowB.AsmanyasareintheresultC.AsmanyasarehostvariablesusedinthecallD.Asmanyashostvariablearraystructurescanhold满分：4分得分：45.GiventhetableT1withthefollowingdata:COL1IDX-Asingle-threadedCLIapplicationexecutesthefollowingpseudocodeinsequence:SQLAllocHandle(SQL_HANDLE_ENV,NULL,&hEnv)SQLAllocHandle(SQL_HANDLE_DBC,hEnv,&hDbc)SQLConnect(hDbc,SAMPLE,SQL_NTS,NULL,SQL_NTS,NULL,SQL_NTS)SQLSetConnectAttr(hDbc,SQL_ATTR_AUTOCOMMIT,SQL_AUTOCOMMIT_ON)SQLAllocHandle(SQL_HANDLE_STMT,hDbc,&hStmt)SQLExecDirect(hStmt,UPDATEtable1SETcol1=10WHEREidx=1,SQL_NTS)SQLExecDirect(hStmt,UPDATEtable1SETcol1=20WHEREidx=2,SQL_NTS)SQLEndTran(SQL_HANDLE_DBC,hDbc,SQL_COMMIT)SQLExecDirect(hStmt,UPDATEtable1SETcol1=30WHEREidx=1,SQL_NTS)SQLExecDirect(hStmt,UPDATEtable1SETcol1=40WHEREidx=1,SQL_NTS)SQLEndTran(SQL_HANDLE_DBC,hDbc,SQL_ROLLBACK)SQLExecDirect(hStmt,SELECTcol1FROMtable1WHEREidx=1,SQL_NTS)WhichofthefollowingvaluesforCOL1willbefetchedwhenthesequenceforthepseudocodelistedaboveissuccessfullyexecuted?A.10B.20C.30D.40满分：4分得分：46.GiventhetableT1withthefollowingdata:C1C2-1122AnapplicationissuesthefollowingSQLstatementswithAUTOCOMMITdisabled:UPDATEt1SETc1=10WHEREc2=1UPDATEt1SETc1=20WHEREc2=2SAVEPOINTsp1UPDATEt1SETc1=30WHEREc2=1UPDATEt1SETc1=40,c2=3WHEREc2=2SAVEPOINTsp1UPDATEt1SETc1=50WHEREc2=1UPDATEt1SETc1=60WHEREc2=2ROLLBACKTOSAVEPOINTsp1UPDATEt1SETc1=50WHEREc2=3COMMITWhatistheresultofthefollowingquery?SELECTc1,c2FROMt1ORDERBYc2A.101202B.301503C.301403D.101503满分：4分得分：47.Whichofthefollowingcursordefinitionswilldefineacursorcalledc2thatwillfetchrowsfromtablet2,andforeveryrowfetchedwillupdatecolumnc1intablet2?A.DECLAREc2CURSORFORSELECT*FROMt2FORUPDATEOFt2B.DECLAREc2CURSORFORSELECT*FROMt2FORUPDATEOFc2C.DECLAREc2CURSORFORSELECT*FROMt2FORUPDATEOFc1D.DECLAREc2CURSORWITHHOLDFORSELECT*FROMt2FORUPDATEOFt2满分：4分得分：48.GivenanODBC/CLIprogramwithasingleconnection,twothreadsandthefollowingactionswhichcompletesuccessfully:Thread1:INSERTINTOmytabVALUES(1)Thread2:INSERTINTOmytabVALUES(2)Thread1:COMMITThread2:INSERTINTOmytabVALUES(3)Thread1:ROLLBACKThread2:COMMITHowmanyrecordswillbeinsertedandretainedinthetableMYTAB?A.0B.1C.2D.3满分：4分得分：49.Giventhefollowingcode:EXECSQLEXECUTEIMMEDIATE:sqlstmtWhichofthefollowingvaluesmustsqlstmtcontainsothatallrowsaredeletedfromtheSTAFFtable?A.DROPTABLEstaffB.DELETEFROMstaffC.DROP*FROMstaffD.DELETE*FROMstaff满分：4分得分：410.Giventheexpression:WITHmost_citiesAS(SELECTb.id,b.name,a.citiesFROMcountrya,staffbWHEREa.person=b.idANDcities:threshold)SELECT*FROMmost_citiesInwhichofthefollowingdoesMOST_CITIESexist?A.usertablesB.servermemoryC.usertablespaceD.systemcatalogtables满分：4分得分：411.Giventhefollowingstatements:EXECSQLINSERTINTOemployeeVALUES(:new_emp,:new_name)EXECSQLUPDATEcompanySETnum_employees=num_employees+1WHEREcompany_id=1EXECSQLCOMMITWhichofthefollowingcanbeaddedtothedatabasesothatthecompanytablewillstillbeupdatedwithouttheneedfortheexplicitUPDATESQLstatement?A.AnINSERTtriggeronCOMPANYB.AnUPDATEtriggeronCOMPANYC.AnINSERTtriggeronEMPLOYEED.AnUPDATEtriggeronEMPLOYEE满分：4分得分：412.Whichofthefollowingproducesasequentiallyincreasingnumber,suitableforuseasaprimarykey?A.ROWIDdatatypeB.GeneratedIDENTITYcolumnC.GENERATE_UNIQUEbuilt-infunctionD.CURRENTSEQUENCEspecialregister满分：4分得分：413.AcursorisdeclaredwiththeWITHHOLDoption.Whichofthefollowingstatementsisalwaystrue?A.ThecursorwillremainopenafteraCOMMIT.B.AllrowsretrievedarelockeduntilaCOMMIT.C.ACOMMITwillnotbealloweduntilthecursorisclosed.D.LocksobtainedbythecursorwillbekeptafteraCOMMIT.满分：4分得分：414.GiventhetablecalledNAMEwiththefollowingcolumnanddata:lname-SmithSMITHSmiThsmithWhichofthefollowingSQLstatementswillreturnallfourrowsinuppercase?A.SELECTCAPS(lname)FROMnameB.SELECTUCASE(lname)FROMnameC.SELECTSTRUPR(lname)FROMnameD.SELECTTOUPPER(lname)FROMname满分：4分得分：415.GiventhetablesT1andT2,eachwithanINTEGERcolumn:T1COL1-1-1-22T2COL1-1-2-22andthefollowingquerythatexecutessuccessfully:SELECT*FROMT1LEFTOUTERJOINT2ONT1.COL1=T2.COL1Howmanyrowswillthequeryreturn?A.5B.6C.10D.36满分：4分得分：416.Whichofthefollowingwillretrieveresultsthatwillonlybeinlowercase?A.SELECTNAMEFROMEMPLOYEEWHERENAME=aliB.SELECTNAMEFROMEMPLOYEEWHERELCASE(NAME)=aliC.SELECTUCASE(NAME)FROMEMPLOYEEWHERELCASE(NAME)=aliD.SELECTNAMEFROMEMPLOYEEWHERENAMEIN(SELECTNAMEFROMEMPLOYEEWHERELCASE(NAME)=LCASE(ALI)满分：4分得分：417.Giventhetables:COUNTRYidname1Argentina3Cuba4-NATIONidname2Belgium4USAandthecode:EXECSQLDECLAREC1CURSORFORSELECT*FROMcountryWHEREnameISNOTNULLUNIONSELECT*FROMnationEXECSQLOPENC1Howmanyrowsareintheresultset?A.1B.2C.3D.4满分：4分得分：418.Giventhetables:EMPLOYEEDEPTemp_numemp_namedeptdept_iddept_name1Adams11Planning2Jones12Support3Smith24Williams1andthestatement:ALTERTABLEemployeeADDFOREIGNKEY(dept)REFERENCESdept(dept_id)ONDELETECASCADEHowmanyrowswillbedeletedwhenthefollowingstatementisexecuted?DELETEFROMemployeeWHEREdept=1A.0B.1C.3D.4满分：4分得分：419.Whichofthefollowingisabenefitofuser-definedfunctions?A.ImprovesapplicationconcurrencyB.ImprovesblockingofresultsetsC.SimplifiesapplicationmaintenanceD.Reducesmemoryrequirementsontheserver满分：4分得分：420.AnapplicationusesstaticSQLtoconnecttoaremoteDB2serverandinsertsdataintotheCUST.ORDERStableonthatremoteDB2server.ToenableaccesstotheremoteDB2server,FOOneedstocreateapackagewithdefaultoptionssothatBARistheonlynon-administrativeuserthatcanusethispackageontheremoteDB2server.WhichstatementdescribestheprivilegesthatFOOrequirestoaccomplishthis?A.FOOrequiresEXECUTEprivilegeonthepackage.B.FOOrequirestheprivilegetocreatethepackageontheremoteDB2server.C.FOOrequiresEXECUTEprivilegeonthepackageandINSERTprivilegeonCUST.ORDERS.D.FOOrequirestheprivilegetocreatethepackageontheremoteDB2serverandINSERTprivilegeonCUST.ORDERS.满分：4分得分：421.Ifastoredprocedurereturnsmultiplerows,whichofthefollowingmustthecallingapplicationusetoaccesstheresultset?A.AcursorB.AselectstatementC.AdeclaredtemporarytableD.Atableuser-definedfunction满分：4分得分：422.WhichofthefollowingCLI/ODBCfunctionsshouldbeusedtodeleterowsfromaDB2table?A.SQLDelete()B.SQLExecDirect()C.SQLBulkDelete()D.SQLExecuteUpdate()满分：4分得分：423.WhichofthefollowingisusedtorunanembeddeddynamicSQLUPDATEstatement?A.UPDATEB.PREPAREC.DECLARED.EXECUTE满分：4分得分：424.AnODBC/CLIapplicationperformsanarrayinsertintoatablecontainingaprimarykey.Ifoneofthevaluesinsertedgeneratesaduplicaterowerror,whichofthefollowingAPIscanbecalledtodeterminethefailingrow?A.SQLError()B.SQLNumRows()C.SQLRowCount()D.SQLGetDiagField()满分：4分得分：425.Giventhefollowingcode:BEGINATOMICUPDATEcountrySETcities=:countWHERECURRENTOFC1;INSERTINTOcountryVALUES(:co11,:co12,:co13);INSERTINTOcountryVALUES(:co14,:co15,:co16);INSERTINTOcountryVALUES(:co17,:co18,:co19);INSERTINTOcountryVALUES(:co110,:co111,:co112);COMMIT;ENDGiventhatallstatementssucceedexceptthefollowing:INSERTINTOcountryVALUES(:co17,:co18,:co19);HowmanyrowswillbeaffectedintableCOUNTRY?A.0B.3C.4D.5满分：4分得分：4