数据库专家选拔试题.docx
- 文档编号:6903707
- 上传时间:2023-01-12
- 格式:DOCX
- 页数:25
- 大小:27.54KB
数据库专家选拔试题.docx
《数据库专家选拔试题.docx》由会员分享,可在线阅读,更多相关《数据库专家选拔试题.docx(25页珍藏版)》请在冰豆网上搜索。
数据库专家选拔试题
数据库专家成员选拔试题
计分标准:
20分为基本分,每做对一题加一分,满分100分,高于100分按附加分算。
题型说明:
无说明的均为单选,多选题均有提示,没有选择项的为填空。
1.Youneedtostorecurrencydataandyouknowthatdatawillalwayshavetwodigitstotherightofthedecimalpoints.Howeverthenumberofdigitstotheleftofthedecimalplacewillvarygreatly.Whichdatatypewouldbemostappropriatetostorethedata?
_b_________
aNUMBER
bNUMBER(T)
cLANG
dLANGRA
2.ExaminethestructureofSTUDENTtable._______e_____
NAMENULLTYPE
STUDENTIDNOTNULLNUMBER(3)
NAMENOTNULLVARCHAR2(25)
PHONENOTNULLVARCAHR2(9)
ADDRESSVARCHAR2(50)
GRADUATIONUPDATE
Therearehundredrecordsinthestudenttable.YouneedtomodifythePhonecolumntoholdonlynumericvalue.WhichstatementwillmodifythedatatypeofthePhonecolumn?
e
aALTERTABLEstudentMODIFYphoneNUMBER(9)
bALTERSTUDENTtable
cMODIFYCOLUMNphoneNUMBER(9);
dYoucannotmodifyaVARCHER2datatypetoaNUMBERdatatypeforacolumn.
eYoucannotmodifythedatatypeofacolumnifthereisdatainthecolumn.
3.Yourcompanywantstogiveeachemployeea100$salaryincrement.Youneedtoevaluatetheresultsfromtheemptablepriortotheactualmodification.Ifyoudonotwanttostoretheresultsinthedatabase.Whichstatementisvalid?
______c______
aYouneedtoaddacolumntotheemptable.
bYouneedtogivethearithmeticexpressionthatinvokesthesalaryincrementinthesetclauseoftheupgradeclause.
cYouneedtogivethearithmeticexpressionthatinvokesthesalaryincrementintheselectclauseoftheselectstatement.
dYouneedtogivethearithmeticexpressionthatinvokesthesalaryincrementintheupdateclauseoftheselectstatement.
eYouneedtogivethearithmeticexpressionthatinvokesthesalaryincrementinthedisplayclauseoftheselectstatement.
4.YouhavedecidedtopermanentlyremoveallthedatafromtheSTUDENTtableandyouneedthetablestructureinthefuture.Whichsinglecommandperformthis?
_____b_______
aDROPTABLEstudent;
bTRUNCATETABLEstudent;
cDELETE*FROMstudent;
dTRUNCATETABLEstudentKEEPSTRUCTURE;
eDELETE*FROMstudentKEEPSTRUCTURE.
5.WithinaPL/SQLloopyouneedtotestifthecurrentfetchwassuccessful.WhichSQLcursorattributeswouldyouusetoaccomplishthistask?
__c__________
aSQL%ISOPEN.
bSQL%ROWCOUNT.
cSQL%FOUND.
dThistaskcannotbeaccomplishedwithaSQLcursorattribute.
eAcursorattributecan’tbeusedwithinaPL/SQLloop.
6.YouareauserofPRODdatabasewhichcontainsoverthousandtablesandyouneedtodeterminethenumberoftablesyoucanaccess.Whichdatadictionaryviewcouldyouquerytodisplaythisinformation?
______b______
aUSER_OBJECTS
bALL_OBJECTS
cDBA_SEGEMNTS
dDBA_TABLES
7.HowwouldyoudeclareaPL/SQLtableofrecordstoholdtherowsselectedfromtheEMPtable?
_______d_____
aDECLARE
emp-tableisTABLEofemp%ROWTYPE.
bBEGIN
TYPEemp-tableisTABLEofemp%ROWTYPE
emp-tableemp-table-type;
cDECLARE
TYPEemp-tableisTABLEofemp%ROWTYPE
INDEXBYWHOLENUMBER:
emp-tableemp-table-type;
dDECLARE
TYPEemp-tableisTABLEofemp%ROWTYPE
INDEXBYBINARYINTEGRATDE.
emp-tableemp-table-type;
8.Whichstatementistruewhenwritingacursorforloop?
_______d_____
aYoumustexplicitlyfetchtherowswithinacursorforloop.
bYoumustexplicitlyopenthecursorpriortothecursorforloop.
cYoumustexplicitlyclosethecursorpriortotheendofprogram.
dYoudonotexplicitlyopen,fetchorcloseacursorwithinacursorforloop.
eYoumustexplicitlydeclaretherecordvariablethatholdstherowreturnedfromthecursor.
9.Youattempttoquerytothedatabasewiththiscommand.___b_________
SELECTdept_no,AVG(MONTHS_BETWEEN(SYSDATE,hire-data))
FROMemployeeWHEREAVG(MONTHS_BETWEEN(SYSDATE,hire_date))>60
GROUPBYbydept_no
ORDERBYAVG(MONTHS_BETWEEN(SYSDATE,hire_date));
Whydoesthisstatementcauseanerror?
b
aAselectclausecannotcontainagroupfunction.
bAwhereclausecannotbeusedtorestrictgroups.
cAnorderbyclausecannotcontainagroupfunction.
dAgroupfunctioncannotcontainasinglerowfunction.
10.Whichstatementaboutmultiplecolumnsubqueriesistrue?
________c____
aApairwisecomparisonproducesacrossproduct.
bAnon-pairwisecomparisonproducesacrossproduct.
cInapairwisequerythevaluesreturnedfromthesubqueryarecomparedindividuallytothevalueintheouterquery.
dInthenonpairwisequerythevaluesreturnedfromthesubqueryarecomparedasagrouptothevaluesintheouterquery.
11.StructureofDEPTtableisasfollows:
________b____
NameNullType
DEPTNONOTNULLNUMBER
(2)
DNAMEVARCHAR2(14)
LOCVARCHAR2(13)
Examinethedeclarationsection.
DECLARE
TYPEdept_table_typeISTABLEOFdept&ROWTYPE
INDEXBYBINARYINTEGER
dept_tabledept_table_type;
YouneedtoassignLOCfileinrecord15thevalueof‘Atlanta’.WhichPL/SQLstatementmakesthisassignment?
b
adept_table.loc.15:
=‘Atlanta’;
bdept_table[15].loc:
=‘Atlanta’;
cdept_table(15).loc:
=‘Atlanta’;
ddept_table_type(15).loc:
=‘Atlanta’;
12.InwhichsectionofaPL/SQLblockisauserdefinedexceptionraised?
_____c_______
aHeading.
bExecuted.
cDeclarative.
dExceptionhandling.
13.Whichstatementistrueaboutnestingblocks?
_____b_______
aVariablenamemustbeuniquebetweenblocks.
bAvariabledefinedintheouterblockisvisibleintheinnerblocks.
cAvariabledefinedintheinnerblockisvisibleintheouterblocks.
dAvariableisinaninnerblockmayhavethesamenameasavariableinanouterblockonlyifthedatatypesaredifferent.
14.ThestructureoftheDEPTtableas:
______a______
NameNull?
Type
DEPTNONotNULLNumber(25)
DNAMEVARCHER2(14)
LOCVARCHER2(13)
Examinethedeclarationsection.
DECLARE
TYPEdept-record-typeisRECORD
(dnoNUMBER,
nameVARCHER(20));
depy-recdept-record-type;
HowdoyouretrieveanentirerowoftheDEPTtableusingtheDEPT-RECvariable?
a
aSELECT*
INTOdept-rec
FROMdept
WHEREdeptno=10;
bSELECTdeptno,dname,loc
INTOdept-rec
FROMdept
WHEREdeptno=10;
cYoucan’tretrievetheentirerowusingtheDEPT-RECvariable
declaredinthecode.
dSELECT*
INTOdept-rec.dno,dept-rec.name,dept-rec.
FROMdept
WHEREdeptno=10;
15.Giventhiscursorstatement:
____d________
DECLARE
CURSORquery_cursor(v_salary)IS
SELECTLAST_NAME,SALARY,DEPT_NO
FROMEMPLOYEE
WHERESALARY>V_SALARY;
Whydoesthisstatementcauseanerror?
d
aTheparametermodewasnotdefined.
bAwareclauseisnotallowedinacursorstatement.
cTheintoclauseismissingfromtheselectstatement.
dAscalartypewasnotspecifiedfortheparameter.
16.UsingSQLPlusyoucreatedauserwiththiscommand:
___c_________
CREATEUSERJenniferIDENTIFIEDBYjbw122
Whatshouldyoudotoallowusersdatabaseaccess?
c
aUsethealterusercommandtoassigntheuseradefaulttablespace.
bGranttheuserthecreatesessionprivilege.
cUsethealterusertoassigntheuseradefaultprofile.
dNoactionisrequiredtogivetheuserdatabaseaccess.
17.Whichdatadictionaryviewcontainsthedefinitionofaview?
_______b_____
aMY_VIEWS.
bUSER_VIEWS.
cSYSTEM_VIEWS.
dUSER_TAB_VIEWS.
18.Whichstatementaboutusingasubqueryinthefromclauseistrue?
_____b_______
aYoucan’tuseasubqueryinthefromclause.
bYoueliminatetheneedtocreateanewviewortablebyplacingasubqueryinthefromclause.
cYoueliminatetheneedtograntselectprivilegesonthetableusedinthefromclausesubquery.
dYoudefineadatasourceforfutureselectstatementwhenusingasubqueryinthefromclause.
19.Theemployeetablehastencolumns.Sinceyouoftenquerythetablewithconditionbasedonfourormorecolumns,youcreatedanindexonallthecolumnsinthetable.Whichresultwilloccur?
_____b_______
aUpdatesonthetablewillbeslower.
bThespeedofinsertswillbeincreased.
cAllqueriesonthetablewillbefaster.
dThesizeoftheemployeetablewillbeincreased.
20.EvaluatethisPL/SQLblock:
___b_________
BEGIN
FROMiIN1..5LOOP
IFi=1THENNULL;
ELSIFi=3THENCOMMIT;
ELSIF1=5THENROLLBACK;
ELSEINSERTINTOtest(results);
VALUES(i);
ENDIF;
ENDLOOP;
COMMIT;
END;
HowmanyvalueswillbepermanentlyinsertedintotheTESTtable?
b
a0.
b1
c2
d3
e5
f6
21.Whichscriptwouldyouusetoquerythedatadictionarytoviewonlythenamesoftheprimarykeyconstraintsusingasubstitutionparameterforthetablename?
____a________
aACCEPTTABLEPROMPT(‘tabletoviewprimarykeyconstraint:
’)
SELECTconstraint_name
FROMuser_constraints
WHEREtable_name=upper(‘&table’)ANDconstraint_type=‘P’;
bACCEPTTABLEPROMPT(‘tabletoviewprimarykeyconstraint:
’)
SELECTconstraint_name
FROMuser_constraint
WHEREtable_name=upper(‘&table’)ANDconstraint_type=‘PRIMARY’;
cACCEPTTABLEPROMPT(‘tabletoviewprimarykeyconstraint:
’)
SELECTconstraint_name,constraint_type
FROMuser_constraint
WHEREtable_name=upper(‘&table’);
dACCEPTTABLEPROMPT(‘tabletoviewprimarykeyconstraint:
’)
SELECTconstraint_name
FROMuser_cons_columns
WHEREtable_name=upper(‘&table’)ANDconstraint_type=‘P’;
22.Whichselectstatementdisplaystheorderidproductidandquantityofitemsintheitemtablethatmatchesboththeproductidandquantityofanitemorder(605).Donotdisplaythedetailsoftheorder605?
_____c_______
aSELECTordeid,prodid,qty
FROMitem
WHERE(prodid,qty)IN
(SELECTprodid,qty
FROMitem
WHEREordid=605);
bSELECTordeid,prodid,qty
FROMitem
WHERE(prodid,qty)=
(SELECTprodid,qty
FROMitem
WHEREordid=605);
AND
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 专家 选拔 试题