1z0007.docx

1z0007.docx

《1z0007.docx》由会员分享，可在线阅读，更多相关《1z0007.docx（105页珍藏版）》请在冰豆网上搜索。

1z0007

Q:

1ExaminethestructureoftheEMPLOYEEStable:

EMPLOYEE_IDNUMBERPrimaryKeyFIRST_NAMEVARCHAR2（25）LAST_NAMEVARCHAR2（25）

Whichthreestatementsinsertarowintothetable?

（Choosethree.）

A.INSERTINTOemployees

VALUES（NULL,'John','Smith'）;

B.INSERTINTOemployees（first_name,last_name）VALUES（'John','Smith'）;

C.INSERTINTOemployees

VALUES（'1000','John',NULL）;

D.INSERTINTOemployees（first_name,last_name,employee_id）VALUES（1000,'John','Smith'）;

E.INSERTINTOemployees（employee_id）

VALUES（1000）;

F.INSERTINTOemployees（employee_id,first_name,last_name）VALUES（1000,'John',''）;

Answer:

C,E,F

Q:

2EvaluatetheSQLstatement:

SELECTROUND（45.953,-1）,TRUNC（45.936,2）FROMdual;

Whichvaluesaredisplayed?

A.46and45

B.46and45.93

C.50and45.93

D.50and45.9

E.45and45.93

F.45.95and45.93

Answer:

C

Q:

3WhichareDMLstatements?

（Chooseallthatapply.）

A.COMMITB.MERGEC.UPDATED.DELETEE.CREATEF.DROP...

Answer:

B,C,D

Q:

4EvaluatethesetofSQLstatements:

CREATETABLEdept

（deptnoNUMBER

（2）,dnameVARCHAR2（14）,locVARCHAR2（13））;

ROLLBACK;DESCRIBEDEPT

Whatistrueabouttheset?

A.TheDESCRIBEDEPTstatementdisplaysthestructureoftheDEPTtable.

B.TheROLLBACKstatementfreesthestoragespaceoccupiedbytheDEPTtable.

C.TheDESCRIBEDEPTstatementreturnsanerrorORA-04043:

objectDEPTdoesnotexist.

D.TheDESCRIBEDEPTstatementdisplaysthestructureoftheDEPTtableonlyifthereisaCOMMIT

statementintroducedbeforetheROLLBACKstatement.

Answer:

A

Q:

5EvaluatethisSQLstatement:

SELECTename,sal,12*sal+100

FROMemp;

TheSALcolumnstoresthemonthlysalaryoftheemployee.Whichchangemustbemadetotheabovesyntaxtocalculatetheannualcompensationas"monthlysalaryplusamonthlybonusof$100,multipliedby12"?

A.Nochangeisrequiredtoachievethedesiredresults.

B.SELECTename,sal,12*（sal+100）FROMemp;

C.SELECTename,sal,（12*sal）+100

FROMemp;

D.SELECTename,sal+100,*12

FROMemp;

Answer:

B

Q:

6ExaminetheSQLstatementthatcreatesORDERStable:

CREATETABLEorders

（SER_NONUMBERUNIQUE,ORDER_IDNUMBER,ORDER_DATEDATENOTNULL,STATUSVARCHAR2（10）

CHECK（statusIN（'CREDIT','CASH'））,

PROD_IDNUMBER

REFERENCESPRODUCTS（PRODUCT_ID）,ORD_TOTALNUMBER,

PRIMARYKEY（order_id,order_date））;

ForwhichcolumnswouldanindexbeautomaticallycreatedwhenyouexecutetheaboveSQLstatement?

（Choosetwo.）

A.SER_NO

B.ORDER_IDC.STATUS

D.PROD_ID

E.ORD_TOTAL

F.compositeindexonORDER_IDandORDER_DATE

Answer:

A,F

Q:

7ExaminethestructureoftheEMP_DEPT_VUview:

ColumnNameTypeRemarks

EMPLOYEE_IDNUMBERFromtheEMPLOYEEStableEMP_NAMEVARCHAR2（30）FromtheEMPLOYEEStableJOB_IDVARCHAR2（20）FromtheEMPLOYEEStableSALARYNUMBERFromtheEMPLOYEEStableDEPARTMENT_IDNUMBERFromtheDEPARTMENTStableDEPT_NAMEVARCHAR2（30）FromtheDEPARTMENTStable

WhichSQLstatementproducesanerror?

A.SELECT*

FROMemp_dept_vu;

B.SELECTdepartment_id,SUM（salary）FROMemp_dept_vu

GROUPBYdepartment_id;

C.SELECTdepartment_id,job_id,AVG（salary）FROMemp_dept_vu

GROUPBYdepartment_id,job_id;

D.SELECTjob_id,SUM（salary）FROMemp_dept_vu

WHEREdepartment_idIN（10,20）GROUPBYjob_id

HAVINGSUM（salary）>20000;

E.Noneofthestatementsproduceanerror;allarevalid.

Answer:

E

Q:

8ClicktheExhibitbuttonandexaminethedatafromtheORDERSand

CUSTOMERStables.EvaluatethisSQLstatement:

SELECTcust_id,ord_total

FROMorders

WHEREord_total>ANY（SELECTord_total

FROMorders

WHEREcust_idIN（SELECTcust_idFROMcustomersWHEREcityLIKE

'NewYork'））;Whatistheresultwhentheabovequeryisexecuted?

A.A

Answer:

A

Q:

9EvaluatethisSQLstatement:

SELECTe.EMPLOYEE_ID,e.LAST_NAME,e.DEPARTMENT_ID,d.DEPARTMENT_NAMEFROMEMPLOYEESe,DEPARTMENTSd

WHEREe.DEPARTMENT_ID=d.DEPARTMENT_ID;

Inthestatement,whichcapabilitiesofaSELECTstatementareperformed?

A.selection,projection,joinB.difference,projection,joinC.selection,intersection,joinD.intersection,projection,join

E.difference,projection,product

Answer:

A

Q:

10ClicktheExhibitbuttonandexaminethedatafromtheEMPtable.

TheCOMMISSIONcolumnshowsthemonthlycommissionearnedbytheemployee.

Whichthreetaskswouldrequiresubqueriesorjoinsinordertobeperformedinasinglestep?

（Choosethree.）

A.deletingtherecordsofemployeeswhodonotearncommission

B.increasingthecommissionofemployee3bytheaveragecommissionearnedindepartment20

C.findingthenumberofemployeeswhodoNOTearncommissionandareworkingfordepartment20

D.insertingintothetableanewemployee10whoworksfordepartment20andearnsacommissionthatisequaltothecommissionearnedbyemployee3

E.creatingatablecalledCOMMISSIONthathasthesamestructureanddataasthecolumnsEMP_IDand

COMMISSIONoftheEMPtable

F.decreasingthecommissionby150fortheemployeeswhoareworkingindepartment30andearningacommissionofmorethan800

Answer:

B,D,E

Q:

11YouneedtomodifytheSTUDENTStabletoaddaprimarykeyonthe

STUDENT_IDcolumn.Thetableiscurrentlyempty.Whichstatementaccomplishesthistask?

A.ALTERTABLEstudents

ADDPRIMARYKEYstudent_id;B.ALTERTABLEstudents

ADDCONSTRAINTPRIMARYKEY（student_id）;C.ALTERTABLEstudents

ADDCONSTRAINTstud_id_pkPRIMARYKEYstudent_id;D.ALTERTABLEstudents

ADDCONSTRAINTstud_id_pkPRIMARYKEY（student_id）;E.ALTERTABLEstudents

MODIFYCONSTRAINTstud_id_pkPRIMARYKEY（student_id）;

Answer:

D

Q:

12WhichthreeareDATETIMEdatatypesthatcanbeusedwhenspecifyingcolumndefinitions?

（Choosethree.）

A.TIMESTAMP

B.INTERVALMONTHTODAYC.INTERVALDAYTOSECONDD.INTERVALYEARTOMONTH

E.TIMESTAMPWITHDATABASETIMEZONE

Answer:

A,C,D

Q:

13TheEMPLOYEEStablecontainsthesecolumns:

LAST_NAMEVARCHAR2（25）

SALARYNUMBER（6,2）COMMISSION_PCTNUMBER（6）

Youneedtowriteaquerythatwillproducetheseresults:

1.Displaythesalarymultipliedbythecommission_pct.

2.Excludeemployeeswithazerocommission_pct.

3.Displayazeroforemployeeswithanullcommissionvalue.EvaluatetheSQLstatement:

SELECTLAST_NAME,SALARY*COMMISSION_PCTFROMEMPLOYEES

WHERECOMMISSION_PCTISNOTNULL;

Whatdoesthestatementprovide?

A.allofthedesiredresultsB.twoofthedesiredresultsC.oneofthedesiredresultsD.anerrorstatement

Answer:

C

Q:

14EvaluatetheSQLstatement:

TRUNCATETABLEDEPT;

WhichthreearetrueabouttheSQLstatement?

（Choosethree.）

A.Itreleasesthestoragespaceusedbythetable.

B.Itdoesnotreleasethestoragespaceusedbythetable.

C.Youcanrollbackthedeletionofrowsafterthestatementexecutes.

D.YoucanNOTrollbackthedeletionofrowsafterthestatementexecutes.

E.AnattempttouseDESCRIBEontheDEPTtableaftertheTRUNCATEstatementexecuteswilldisplayanerror.

F.YoumustbetheownerofthetableorhaveDELETEANYTABLEsystemprivilegestotruncatetheDEPT

table.

Answer:

A,D,F

Q:

15TheEMPtablecontainsthesecolumns:

EMPLOYEE_IDNUMBER（4）

EMPNAMEVARCHAR2（25）SALARYNUMBER（9,2）HIRE_DATEDATE

YouquerythedatabasewiththisSQLstatement:

SELECTempname,hire_dateHIREDATE,salary

FROMEMP

ORDERBYhire_date;

Howwilltheresultsbesorted?

A.randomly

B.ascendingbydate

C.descendingbydate

D.ascendingalphabetically

E.descendingalphabetically

Answer:

B

Q:

16MaryhasaviewcalledEMP_DEPT_LOC_VUthatwascreatedbasedon

theEMPLOYEES,DEPARTMENTS,andLOCATIONStables.ShegrantedSELECTprivilegetoScottonthisview.WhichoptionenablesScotttoeliminatetheneedtoqualifytheviewwiththenameMARY.EMP_DEPT_LOC_VUeachtimetheviewisreferenced?

A.ScottcancreateasynonymfortheEMP_DEPT_LOC_VUbyusingthecommand

CREATEPRIVATESYNONYMEDL_VUFORmary.EMP_DEPT_LOC_VU;

thenhecanprefixthecolumnswiththissynonym.

B.ScottcancreateasynonymfortheEMP_DEPT_LOC_VUbyusingthecommand

CREATESYNONYMEDL_VUFORmary.EMP_DEPT_LOC_VU;

thenhecanprefixthecolumnswiththissynonym.

C.ScottcancreateasynonymfortheEMP_DEPT_LOC_VUbyusingthecommand

CREATELOCALSYNONYMEDL_VU

FORmary.EMP_DEPT_LOC_VU;

thenhecanprefixthecolumnswiththissynonym.

D.ScottcancreateasynonymfortheEMP_DEPT_LOC_VUbyusingthecommand

CREATESYNONYMEDL_VUONmary（EMP_DEPT_LOC_VU）;

thenhecanprefixthecolumnswiththissynonym.

E.Scottcannotcreateasynonymbecausesynonymscanbecreatedonlyfortables.

F.ScottcannotcreateanysynonymforMary'sview.MaryshouldcreateaprivatesynonymfortheviewandgrantSELECTprivilegeonthatsynonymtoScott.

Answer:

B

Q:

17Asubquerycanbeusedto.

A.creategroupsofdata

B.sortdatainaspecificorder

C.convertdatatoadifferentformat

D.retrievedatabasedonanunknowncondition

Answer:

D

Q:

18ClicktheExhibitbuttontoexaminethedataoftheEMPLOYEEStable.

WhichstatementliststheID,name,andsalaryoftheemployee,andtheIDandnameoftheemployee'smanager,foralltheemployeeswhohaveamanagerandearnmorethan4000?

A.SELECTemployee_id"Emp_id",emp_name"Employee",salary,

employee_id"Mgr_id",emp_name"Manager"FROMemployees

WHEREsalary>4000;

B.SELECTe.employee_id"Emp_id",e.emp_name"Employee",e.salary,

m.employee_id"Mgr_id",m.emp_name"Manager"FROMemployeeseJOINemployeesm

WHEREe.mgr_id=m.mgr_id

ANDe.salary>4000;

C.SELECTe.employee_id"Emp_id",e.emp_name"Employee",

e.salary,

m.employee_id"Mgr_id",m.emp_name"Manager"

FROMemployeeseJOINemployeesm

ON（e.mgr