ocp051题库笔记讲解1z0-v9.02解析

1Z0-051V9.02156-160EvaluatethefollowingSQLSQL>SELECTcust_id,cust_last_name"LastName"FROMcustomersWHEREcountry_id=10SELECTcust_idCUST_NO,cust_last_nameFROMcustomersWHEREcountry_id=WhichORDERBYclausesarevalidfortheabovequery?(Chooseallthatapply.)ORDERBYORDERBYORDERBYORDERBYORDERBY"LastORDERORDERBY子句是有效的(选择所有正确的选项ORDERBYselect语句后的第几个字段，也可以使用列名。BD选项不正确，因为只能使用第一个查询的列进行排序，包括别名、列名或表达SELECTcust_id,cust_last_name"LastFROMWHEREcountry_id=10SELECTcust_idCUST_NO,FROMWHEREcountry_id=--ORDERBY2,1----ORDERBYCUST_NO--B.----ORDERBY2,cust_id----ORDERBY"CUST_NO"--D.----ORDERBY"LastName"--/*/*集合运算结果列表的列名以第一个SELECTselectcust_id,"LastName"fromSELECTcust_id,cust_last_name"LastFROMWHEREcountry_id=10SELECTcust_idCUST_NO,FROMWHEREcountry_id=--selectCUST_NO,cust_last_namefromSELECTcust_id,cust_last_name"LastFROMWHEREcountry_id=minus--此处集合运算符可以包括：交intersect，并union/unionall，差SELECTcust_idCUST_NO,FROMWHEREcountry_id=)ViewtheExhibitandexaminethestructureoftheORDERSandCUSTOMERStables.EvaluatethefollowingSQLSQL>SELECTo.order_id,c.cust_name,o.order_total,c.credit_limitFROMordersoJOINcustomerscUSING(customer_id)WHEREo.order_total>c.credit_limitFORUPDATEORDERBYWhichtwostatementsaretrueregardingthe eoftheabovequery?(Choosetwo.)ItlocksalltherowsthatsatisfytheconditionintheItlocksonlythecolumnsthatsatisfytheconditioninboththetables.ThelocksarereleasedonlywhenaCOMMITorROLLBACKisThelocksarereleasedafteraDMLstatementisexecutedonthelockedrows.SQL命令:关于上面查询的结果哪两句话是正确的？(2项)只有执 AA选项正确，selectforupdateselect出来的所有行，以防果在其它会话中在被锁定的行上做DML操作，则会被阻塞。DROPTABLEcreatetables157(sidnumber,snamevarchar2(2),s_reqnumber);insertintos157values(1,'ZS',60);insertintos157values(2,'LS',75);insertintos157DROPTABLEcreatetablesc157(sid amevarchar2(2),scorenumber);insertintosc157values(1,'EN',59);insertintosc157values(2,'EN',76);insertintosc157select*froms157whereselect*froms157wheresid=1forupdates157sets_req=60whereSELECTSID,S.Sname,SC.SCORE,FROMs157SJOINsc157ScUSING(SID)WHERESC.SCORE>FORUPDATEORDERBYS.Sname;--在另一个会话执行下面的语句，不受影响，因为SID=1的行未被锁UPDATESC157SETSCORE=58WHERESID=1;UPDATES157SETS_REQ=61WHERESID=1;--在另一个会话执行下面的语句排队等待，因为SID为2和3的行被锁UPDATESC157SETSCORE=77WHEREUPDATEUPDATES157SETS_REQ=70WHERE行WhichstatementsaretrueregardingtheFORUPDATEclauseinaSELECTstatement?(Chooseallthatapply.)ItlocksonlythecolumnsspecifiedintheSELECTItlockstherowsthatsatisfytheconditionintheSELECTItcanbeusedonlyinSELECTstatementsthatarebasedonasingletable.ItcanbeusedinSELECTstatementsthatarebasedonasingleormultipletables.AfteritisendbyaSELECTstatement,nootherqueryaccessthesamerowsuntilaCOMMITorROLLBACKisSELECTCOMMITROLLBACK后才能B选项正确，forupdateselectC选项不正确，selectforupdate可以用于基于一张表，也可以基于多张DE选项，forupdate语句执行之后，其它查询不能变更这些相关的行，但是不影响查SELECTSIDFROMS157WHERESID=1FOR--另一个会话中运行,出现了一直等待updates157setSname='WB'where--C,--C,下列语句锁定了两张表，每张表锁定了2SELECTSID,S.Sname,SC.SCORE,FROMs157SJOINsc157ScUSING(SID)WHERESC.SCORE>FORUPDATEORDERBYS.Sname;ViewtheExhibitandexaminethestructureoftheCUSTOMERSNEW_CUSTOMERSisanewtablewiththecolumnsCUST_ID,CUST_NAMEandCUST_CITYthathavethesamedatatypesandsizeasthecorrespondingcolumnsintheCUSTOMERStable.EvaluatethefollowingINSERTINSERTINTOnew_customers(cust_id,cust_name,cust_city)(SELECTcust_id,cust_first_name||''||cust_last_name,FROMWHEREcust_id>TheINSERTstatementfailswhenexecuted.WhatcouldbetheTheVALUESclausecannotbeusedinanINSERTwithaColumnnamesintheNEW_CUSTOMERSandCUSTOMERStablesdonotTheWHEREclausecannotbeusedinasubqueryembeddedinanINSERTstatement.ThetotalnumberofcolumnsintheNEW_CUSTOMERStabledoesnotmatchthetotalnumberofcolumnsintheCUSTOMERStable.NEW_CUSTOMERSNEW_CUSTOMERSCUST_IDCUST_NAMEandCUST_CITY列的CUSTOMERS相同.A.VALUESINSERT语句。B.NEW_CUSTOMERSCUSTOMERS表不匹配。droptablecreatetablenew_customers(cust_idnumber,varchar2(30),cust_cityINSERTINTO(cust_id,cust_name,cust_city)(SELECTcust_id,cust_first_name||'FROMWHEREcust_id>23004);--INSERTINTO(cust_id,cust_name,SELECTcust_id,cust_first_name||''||cust_last_name,FROMWHEREcust_id>ViewtheExhibitandexaminethestructureofORDERSandCUSTOMERStables.Thereisonlyonecustomerwiththecust_last_namecolumnhavingvalueRoberts.WhichINSERTstatementshouldbeusedtoaddarowintotheORDERStableforthecustomerwhoseCUST_LAST_NAMEisRobertsandCREDIT_LIMITis600?INSERTINTOorders(SELECTcustomer_idFROMcustomersWHEREcust_last_name='Roberts'ANDcredit_limit=600),INSERTINTOorders(SELECTcustomer_idFROMcustomersWHEREcust_last_name='Roberts'ANDcredit_limit=600),(1,'10-mar-2007','direct',&&customer_id,1000);C.INSERTINTO(SELECTFROMorderso,customersWHEREo.customer_id=ANDc.cust_last_name='Roberts'ANDc.credit_limit=600)(SELECTcustomer_idFROMcustomersWHEREcust_last_name='Roberts'ANDcredit_limit=600),D.INSERTINTOorders(SELECTcustomer_idFROMcustomersWHEREcust_last_name='Roberts'ANDcredit_limit=600),(1,'10-mar-2007','direct',&customer_id,ORDERSandORDERSandCUSTOMERSSELECTcustomer_idFROMWHEREcust_last_name=ANDcredit_limit=updateupdatecustomerssetcust_last_name=cu