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子句是有效的(選擇所有正確的選項(xiàng)ORDERBYselect語句后的第幾個字段，也可以使用列名。BD選項(xiàng)不正確，因?yàn)橹荒苁褂玫谝粋€查詢的列進(jìn)行排序，包括別名、列名或表達(dá)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"--/*/*集合運(yùn)算結(jié)果列表的列名以第一個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--此處集合運(yùn)算符可以包括：交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命令:關(guān)于上面查詢的結(jié)果哪兩句話是正確的？(2項(xiàng))只有執(zhí) AA選項(xiàng)正確，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;--在另一個會話執(zhí)行下面的語句，不受影響，因?yàn)镾ID=1的行未被鎖UPDATESC157SETSCORE=58WHERESID=1;UPDATES157SETS_REQ=61WHERESID=1;--在另一個會話執(zhí)行下面的語句排隊(duì)等待，因?yàn)镾ID為2和3的行被鎖UPDATESC157SETSCORE=77WHEREUPDATEUPDATES157SETS_REQ=70WHERE行WhichstatementsaretrueregardingtheFORUPDATEclauseinaSELECTstatement?(Chooseallthatapply.)ItlocksonlythecolumnsspecifiedintheSELECTItlockstherowsthatsatisfytheconditionintheSELECTItcanbeusedonlyinSELECTstatementsthatarebasedonasingletable.ItcanbeusedinSELECTstatementsthatarebasedonasingleormultipletables.AfteritisendbyaSELECTstatement,nootherqueryaccessthesamerowsuntilaCOMMITorROLLBACKisSELECTCOMMITROLLBACK后才能B選項(xiàng)正確，forupdateselectC選項(xiàng)不正確，selectforupdate可以用于基于一張表，也可以基于多張DE選項(xiàng)，forupdate語句執(zhí)行之后，其它查詢不能變更這些相關(guān)的行，但是不影響查SELECTSIDFROMS157WHERESID=1FOR--另一個會話中運(yùn)行,出現(xiàn)了一直等待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