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

吉大《数据库应用技术》在线作业一答案
试卷总分：100测试时间：--试卷得分：100
单项选择题
一、单项选择题（共25道试题，共100分。）得分：100V
1.Giventhefollowingtable:
TestTableC1-----------12345AndifthefollowingCLIcallsaremade:
SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv);SQLSetEnvAttr(henv,
SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,0);SQL
AllocHandle(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,(unsigned
char*)"select*fromTestorderbyC1',SQL_NTS);
SQLBindCol(hstmt,1,SQL_C_SHORT,&data,0,NULL);SQLExecute(hstmt);
SQLFetch(hstmt);printf(Data:%i\n",data);SQLFetch(hstmt);
printf(Data:%i\n",data);SQLFetch(hstmt);printf(Data:%i\n",data);
SQLEndTran(SQL_HANDLE_ENV,henv,SQL_COMMIT);SQLFetch(hstmt);
printf(Data:%i\n",data);Whichofthefollowingwillbereturnedbythe
program?
A.Data:1Data:2Data:3Data:3
B.Data:1Data:2Data:3Data:4
C.Data:1Data:2Data:3Data:1
D.Data:1Data:2Data:3Data:5
满分：4分得分：4
2.Giventhecode:EXECSQLDECLAREcursor1CURSORFORSELECT
name,age,b_dateFROMperson;EXECSQLOPENcursor1;Underwhichofthe
followingsituationswilltheabovecursorbeimplicitlyclosed?
A.WhenaCLOSEstatementisissued
B.WhenaCOMMITstatementisissued
C.Whentherearenorowsintheresultset
D.WhenallrowsareFETCHedfromtheresultset
满分：4分得分：4
3.Giventheapplicationcode:EXECSQLDECLAREcurCURSORWITHHOLDFOR
SELECTc1FROMt1EXECSQLOPENcurEXECSQLFETCHcurINTO:hv/*
Statement1*/EXECSQLCOMMIT/*Statement2*/EXECSQLFETCHcurINTO
:hv/*Statement3*/EXECSQLROLLBACK/*Statement4*/EXECSQLCLOSE
cur/*Statement5*/IfthetableT1hasnorowsinit,whichstatement
willcausethecursor"cur"tobeclosedfirst?
A.Statement1
B.Statement2
C.Statement3
D.Statement4
满分：4分得分：4
4HowmanyrowscanberetrievedusingasingleSELECTINTOstatement?
A.Onlyonerow
B.Asmanyasareintheresult
C.Asmanyasarehostvariablesusedinthecall
D.Asmanyashostvariablearraystructurescanhold
满分：4分得分：4
5.GiventhetableT1withthefollowingdata:COL1IDX--------A
single-threadedCLIapplicationexecutesthefollowingpseudocodein
sequence: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,"SELECTcol1
FROMtable1WHEREidx=1",SQL_NTS)WhichofthefollowingvaluesforCOL1
willbefetchedwhenthesequenceforthepseudocodelistedaboveis
successfullyexecuted?
A.10
B.20
C.30
D.40
满分：4分得分：4
6.GiventhetableT1withthefollowingdata:C1C2----1122An
applicationissuesthefollowingSQLstatementswithAUTOCOMMITdisabled:
UPDATEt1SETc1=10WHEREc2=1UPDATEt1SETc1=20WHEREc2=2
SAVEPOINTsp1UPDATEt1SETc1=30WHEREc2=1UPDATEt1SETc1=40,c2
=3WHEREc2=2SAVEPOINTsp1UPDATEt1SETc1=50WHEREc2=1UPDATE
t1SETc1=60WHEREc2=2ROLLBACKTOSAVEPOINTsp1UPDATEt1SETc1=
50WHEREc2=3COMMITWhatistheresultofthefollowingquery?SELECT
c1,c2FROMt1ORDERBYc2
A.101202
B.301503
C.301403
D.101503
满分：4分得分：4
7.Whichofthefollowingcursordefinitionswilldefineacursorcalled
c2thatwillfetchrowsfromtablet2,andforeveryrowfetchedwill
updatecolumnc1intablet2?
A.DECLAREc2CURSORFORSELECT*FROMt2FORUPDATEOFt2
B.DECLAREc2CURSORFORSELECT*FROMt2FORUPDATEOFc2
C.DECLAREc2CURSORFORSELECT*FROMt2FORUPDATEOFc1
D.DECLAREc2CURSORWITHHOLDFORSELECT*FROMt2FORUPDATEOFt2
满分：4分得分：4
8.GivenanODBC/CLIprogramwithasingleconnection,twothreadsand
thefollowingactionswhichcompletesuccessfully:Thread1:INSERTINTO
mytabVALUES(1)Thread2:INSERTINTOmytabVALUES(2)Thread1:COMMIT
Thread2:INSERTINTOmytabVALUES(3)Thread1:ROLLBACKThread2:COMMIT
HowmanyrecordswillbeinsertedandretainedinthetableMYTAB?
A.0
B.1
C.2
D.3
满分：4分得分：4
9.Giventhefollowingcode:EXECSQLEXECUTEIMMEDIATE:sqlstmtWhichof
thefollowingvaluesmustsqlstmtcontainsothatallrowsaredeleted
fromtheSTAFFtable?
A.DROPTABLEstaff
B.DELETEFROMstaff
C.DROP*FROMstaff
D.DELETE*FROMstaff
满分：4分得分：4
10.Giventheexpression:WITHmost_citiesAS(SELECT
b.id,b.name,a.citiesFROMcountrya,staffbWHEREa.person=b.idAND
cities>:threshold)SELECT*FROMmost_citiesInwhichofthefollowing
doesMOST_CITIESexist?
A.usertables
B.servermemory
C.usertablespace
D.systemcatalogtables
满分：4分得分：4
11.Giventhefollowingstatements:EXECSQLINSERTINTOemployee
VALUES(:new_emp,:new_name)EXECSQLUPDATEcompanySET
num_employees=num_employees+1WHEREcompany_id=1EXECSQLCOMMITWhichof
thefollowingcanbeaddedtothedatabasesothatthecompanytablewill
stillbeupdatedwithouttheneedfortheexplicitUPDATESQLstatement?
A.AnINSERTtriggeronCOMPANY
B.AnUPDATEtriggeronCOMPANY
C.AnINSERTtriggeronEMPLOYEE
D.AnUPDATEtriggeronEMPLOYEE
满分：4分得分：4
12.Whichofthefollowingproducesasequentiallyincreasingnumber,
suitableforuseasaprimarykey?
A.ROWIDdatatype
B.GeneratedIDENTITYcolumn
C.GENERATE_UNIQUEbuilt-infunction
D.CURRENTSEQUENCEspecialregister
满分：4分得分：4
13.AcursorisdeclaredwiththeWITHHOLDoption.Whichofthe
followingstatementsisalwaystrue?
A.ThecursorwillremainopenafteraCOMMIT.
B.AllrowsretrievedarelockeduntilaCOMMIT.
C.ACOMMITwillnotbealloweduntilthecursorisclosed.
D.LocksobtainedbythecursorwillbekeptafteraCOMMIT.
满分：4分得分：4
14.GiventhetablecalledNAMEwiththefollowingcolumnanddata:lname
------SmithSMITHSmiThsmithWhichofthefollowingSQLstatementswill
returnallfourrowsinuppercase?
A.SELECTCAPS(lname)FROMname
B.SELECTUCASE(lname)FROMname
C.SELECTSTRUPR(lname)FROMname
D.SELECTTOUPPER(lname)FROMname
满分：4分得分：4
15.GiventhetablesT1andT2,eachwithanINTEGERcolumn:T1COL1
-----------1-1-22T2COL1-----------1-2-22andthefollowingquery
thatexecutessuccessfully:SELECT*FROMT1LEFTOUTERJOINT2ON
T1.COL1=T2.COL1Howmanyrowswillthequeryreturn?
A.5
B.6
C.10
D.36
满分：4分得分：4
16.Whichofthefollowingwillretrieveresultsthatwillonlybein
lowercase?
A.SELECTNAMEFROMEMPLOYEEWHERENAME='ali'
B.SELECTNAMEFROMEMPLOYEEWHERELCASE(NAME)='ali'
C.SELECTUCASE(NAME)FROMEMPLOYEEWHERELCASE(NAME)='ali'
D.SELECTNAMEFROMEMPLOYEEWHERENAMEIN(SELECTNAMEFROMEMPLOYEE
WHERELCASE(NAME)=LCASE('ALI'))
满分：4分得分：4
17.Giventhetables:COUNTRYidname1Argentina3Cuba4-NATIONid
name2Belgium4USAandthecode:EXECSQLDECLAREC1CURSORFORSELECT*
FROMcountryWHEREnameISNOTNULLUNIONSELECT*FROMnationEXECSQL
OPENC1Howmanyrowsareintheresultset?
A.1
B.2
C.3
D.4
满分：4分得分：4
18.Giventhetables:EMPLOYEEDEPTemp_numemp_namedeptdept_id
dept_name1Adams11Planning2Jones12Support3Smith24Williams1
andthestatement:ALTERTABLEemployeeADDFOREIGNKEY(dept)REFERENCES
dept(dept_id)ONDELETECASCADEHowmanyrowswillbedeletedwhenthe
followingstatementisexecuted?DELETEFROMemployeeWHEREdept=1
A.0
B.1
C.3
D.4
满分：4分得分：4
19.Whichofthefollowingisabenefitofuser-definedfunctions?
A.Improvesapplicationconcurrency
B.Improvesblockingofresultsets
C.Simplifiesapplicationmaintenance
D.Reducesmemoryrequirementsontheserver
满分：4分得分：4
20.AnapplicationusesstaticSQLtoconnecttoaremoteDB2serverand
insertsdataintotheCUST.ORDERStableonthatremoteDB2server.To
enableaccesstotheremoteDB2server,FOOneedstocreateapackagewith
defaultoptionssothatBARistheonlynon-administrativeuserthatcan
usethispackageontheremoteDB2server.Whichstatementdescribesthe
privilegesthatFOOrequirestoaccomplishthis?
A.FOOrequiresEXECUTEprivilegeonthepackage.
B.FOOrequirestheprivilegetocreatethepackageontheremoteDB2
server.
C.FOOrequiresEXECUTEprivilegeonthepackageandINSERTprivilegeon
CUST.ORDERS.
D.FOOrequirestheprivilegetocreatethepackageontheremoteDB2
serverandINSERTprivilegeonCUST.ORDERS.
满分：4分得分：4
21.Ifastoredprocedurereturnsmultiplerows,whichofthefollowing
mustthecallingapplicationusetoaccesstheresultset?
A.Acursor
B.Aselectstatement
C.Adeclaredtemporarytable
D.Atableuser-definedfunction
满分：4分得分：4
22.WhichofthefollowingCLI/ODBCfunctionsshouldbeusedtodelete
rowsfromaDB2table?
A.SQLDelete()
B.SQLExecDirect()
C.SQLBulkDelete()
D.SQLExecuteUpdate()
满分：4分得分：4
23.WhichofthefollowingisusedtorunanembeddeddynamicSQLUPDATE
statement?
A.UPDATE
B..PREPARE
C..DECLARE
D..EXECUTE
满分：4分得分：4
24.AnODBC/CLIapplicationperformsanarrayinsertintoatable
containingaprimarykey.Ifoneofthevaluesinsertedgeneratesa
duplicaterowerror,whichofthefollowingAPIscanbecalledto
determinethefailingrow?
A.SQLError()
B.SQLNumRows()
C.SQLRowCount()
D.SQLGetDiagField()
满分：4分得分：4
25.Giventhefollowingcode:BEGINATOMICUPDATEcountrySET
cities=:countWHERECURRENTOFC1;INSERTINTOcountry
VALUES(:co11,:co12,:co13);INSERTINTOcountryVALUES(:co14,:co15,:co16);
INSERTINTOcountryVALUES(:co17,:co18,:co19);INSERTINTOcountry
VALUES(:co110,:co111,:co112);COMMIT;ENDGiventhatallstatements
succeedexceptthefollowing:INSERTINTOcountry
VALUES(:co17,:co18,:co19);Howmanyrowswillbeaffectedintable
COUNTRY?
A.0
B.3
C.4
D.5
满分：4分得分：4