教案ocp1z0 051答案解析

ViewtheExhibitandexaminethestructureoftheSALES,CUSTOMERS,PRODUCTS,andTIMESThePROD_IDcolumnistheforeignkeyintheSALEStable,whichreferencesthePRODUCTStable.Similarly,theCUST_IDandTIME_IDcolumnsarealsoforeignkeysintheSALEStablereferencingtheCUSTOMERSandTIMEStables,respectively.EvaluatethefollowingCREATETABLEcommand:CREATETABLEnew_sales(prod_id,cust_id,order_dateDEFAULTSYSDATE)SELECTprod_id,cust_id,time_idFROMsales;WhichstatementistrueregardingtheaboveTheNEW_SALEStablewouldnotgetcreatedbecausetheDEFAULTvaluecannotbespecifiedinthecolumnTheNEW_SALEStablewouldgetcreatedandalltheNOTNULLconstraintsdefinedonthespecifiedcolumnswouldbepassedtothenewTheNEW_SALEStablewouldnotgetcreatedbecausethecolumnnamesintheCREATETABLEcommandandtheSELECTclausedonotTheNEW_SALEStablewouldgetcreatedandalltheFOREIGNKEYconstraintsdefinedonthespecifiedcolumnswouldbepassedtothenewAnswer:CREATETABLEnew_sales(prod_id,cust_id,order_dateDEFAULTSYSDATE)SELECTprod_id,cust_id,time_idFROMsales;salescreatetablezbcxyidnumber(20)notsnonumber(10)char(2)check(in('男','女')),cnonumber(6)referencescreatetablezbdbaselect*from notnull char(2) ViewtheExhibittoexaminethedescriptionfortheSALESWhichviewscanhaveallDMLoperationsperformedonit?(ChooseallthatCREATEVIEWASSELECT*FROMWHEREcust_id=2034WITHCHECKCREATEVIEWASSELECT*FROMSALESWHEREtime_id<=SYSDATE-2*365WITHCHECKOPTION;CREATEVIEWASSELECTprod_id,cust_id,time_idFROMSALESWHEREtime_id<=SYSDATE-2*365WITHCHECKCREATEVIEWASSELECTprod_id,cust_id,SUM(ty_sold)FROMWHEREtime_id<=SYSDATE-GROUPBYprod_id,cust_idWITHCHECKOPTION;Answer:dmlcdc和dsalesinsert操作，即withcheckoptionsales表所有屬性列都是為非空的withcheckoption就是對視圖操作的一個條件限制，條件就是創(chuàng)建視圖時B選項，則對視圖做更新操作時，time_id是不能>sysdate-2*365YouneedtoextractdetailsofthoseproductsintheSALEStablewherethePROD_IDcontainstheWhichWHEREclausecouldbeusedintheSELECTstatementtogettherequiredWHEREprod_idLIKE'%_D123%'ESCAPEWHEREprod_idLIKE'%\_D123%'ESCAPEWHEREprod_idLIKE'%_D123%'ESCAPEWHEREprod_idLIKE'%\_D123%'ESCAPEAnswer:如果要查詢的信息本身包含”%”或”_”,ESCAPESelect*fromempwhereenamelike‘’%x_%’escapeWhichtwostatementsaretrueregardingsinglerowfunctions?(ChooseTheyacceptonlyasingleTheycanbenestedonlytotwoArgumentscanonlybecolumnvaluesorTheyalwaysreturnasingleresultrowforeveryrowofaqueriedTheycanreturnadatatypevaluedifferentfromtheonethatisAnswer:oracle文檔sqlreference中對Single-rowfunctions的解釋Single-rowfunctionsreturnasingleresultrowforeveryrowofaqueriedtableorview.Thesefunctionscanappearinselectlists,WHEREclauses,STARTWITHandCONNECTBYclauses,andHAVINGclausesATAN2(m,n),a選項錯誤numberDEWhichSQLstatementswoulddisythevalue1890.55as$1,890.55?(Choosethree.)FROMDUAL;FROMDUAL;FROMDUAL;FROMDUAL;FROMDUAL;Answer:222FROMDUAL;*1行出現錯誤ORA-01481:22G代表逗號，DVTO_CHAR(N,'999V9'),pV的位置,則該表達式＝to_char(N×(10P-1次方)).9個數又必須保證大于等于乘積之后表示的位數.ExaminethestructureoftheSHIPMENTSnameNullPO_IDNOTNULLNUMBER(3)PO_DATENOTNULLDATESHIPMENT_DATENOTNULLDATEYouwanttogenerateareportthatdisysthePO_IDandthepenaltyamounttobepaidiftheSHIPMENT_DATEislaterthanonemonthfromthePO_DATE.Thepenaltyis$20perEvaluatethefollowinoSQL>SELECTWHENMONTHS_BETWEEN(shipment_date,po_date)>1THENTO_CHAR((shipment_date-po_date)*20)ELSE'NoPenalty'ENDPENALTYFROMshipments;SQL>SELECTTO_CHAR((shipment_date-po_date)*20),'NoPenalty')PENALTYFROMshipments;WhichstatementistrueregardingtheaboveBothexecutesuccessfullyandgivecorrectOnlythefirstqueryexecutessuccessfullybutgivesawrongOnlythefirstqueryexecutessuccessfullyandgivesthecorrectOnlythesecondqueryexecutessuccessfullybutgivesawrongOnlythesecondqueryexecutessuccessfullyandgivesthecorrectAnswer:caseexpressionswhichletyouuseIFTHENELSElogicinSQLstatementswithouthavingtoDECODEcomparesexprtoeachsearchvalueonebyone.Ifexprisequaltoasearch,thenOracleDatabasereturnsthecorrespondingresult.Ifnomatchisfound,thenOraclereturnsdefault.Ifdefaultisomitted,thenOraclereturnsnull.Theargumentscanbeanyofthenumerictypes(NUMBER,BINARY_FLOAT,orBINARY_DOUBLE)orcharactertypes.WhichtwostatementsaretrueregardingtheUSINGandONclausesintablejoins?(Choosetwo.)BothUSINGandONclausescanbeusedforequijoinsandAumofonepairofcolumnscanbejoinedbetweentwotablesusingtheONTheONclausecanbeusedtojointablesoncolumnsthathavedifferentnamesbutcompatibledataTheWHEREclausecanbeusedtoapplyadditionalconditionsinSELECTstatementscontainingtheONortheUSINGAnswer:ONcondition UsetheONclausetospecifyajoincondition.ngsoletsyouspecifyjoinconditionsseparatefromanysearchorfilterconditionsintheWHEREclause.USING(column) Whenyouarespecifyinganequijoinofcolumnsthathavethesamenameinbothtables,theUSINGcolumnclauseindicatesthecolumnstobeused.Youcanusethisclauseonlyifthejoincolumnsinbothtableshavethesamename.Withinthisclause,donotqualifythecolumnnamewithatablenameortablealias.ViewtheExhibitandexaminethestructureoftheCUSTOMERSWhichtwotaskswouldrequiresubqueriesorjoinstobeexecutedinasinglestatement?(Choosetwo.)listingofcustomerswhodonothaveacreditlimitandwerebornbeforefindingthenumberofcustomers,ineachcity,whosemaritalstatusisfindingtheaveragecreditlimitofmalecustomersresidingin'Tokyo'orlistingofthosecustomerswhosecreditlimitisthesameasthecreditlimitofcustomersresidinginthecityfindingthenumberofcustomers,ineachcity,whosecreditlimitismorethantheaveragecreditlimitofalltheAnswer:joinWhichstatementistrueregardingtheINTERSECTItignoresReversingtheorderoftheintersectedtablesalterstheThenamesofcolumnsinallSELECTstatementsmustbeThenumberofcolumnsanddatatypesmustbeidenticalforallSELECTstatementsinthequery.Answer:insertDViewtheExhibit;examinethestructureofthePROMOTIONSEachpromotionhasadurationofatleastsevendaysYourmanagerhasaskedyoutogenerateareport,whichprovidestheweeklycostforeachpromotiondonetolWhichquerywouldachievetherequiredSELECTpromo_name,promo_cost/promo_end_date-promo_begin_date/7FROMpromotions;FROMpromotions;SELECTpromo_name,promo_cost/(promo_end_date-promo_begin_date/7)FROMpromotions;SELECTpromo_name,promo_cost/((promo_end_date-promo_begin_date)/7)FROMpromotions;Answer:7ViewtheExhibitandexaminethestructureofthePRODUCTSAllproductshaveaYouissuethe mandtodisythetotalpriceofeachproductafteradiscountof25%andataxof15%areappliedonit.Freightchargesof$100havetobeappliedtoalltheAS"TOTALPRICE"FROMWhatwouldbe eifalltheparenthesesareremovedfromtheaboveItproducesaTheresultThetotalpricevaluewouldbelowerthanthecorrectThetotalpricevaluewouldbehigherthanthecorrectAnswer:Youneedtoproduceareportwhereeachcustomer'screditlimithasbeenincrementedby$1000.Intheoutput,thecustomer'slastnameshouldhavetheheadingNameandtheincrementedcreditlimitshouldbelabeledNewCreditLimit.ThecolumnheadingsshouldhaveonlythefirstletterofeachwordinuppercaseWhich plishthisSELECTcust_last_nameName,cust_credit_limit+1000"NewCreditLimit"FROMSELECTcust_last_nameASName,cust_credit_limit+1000ASNewCreditLimitFROMSELECTcust_last_nameAS"Name",cust_credit_limit+1000AS"NewCreditLimit"FROMSELECTINITCAP(cust_last_name)"Name",cust_credit_limit+1000INITCAP("NEWCREDITLIMIT")FROMAnswer:scott@ORCL>selectename "NewCreditLitmit"from NewCreditscott@ORCL>selectenameas as"NewCreditLimit"from NewCreditasscott@ORCL>selectename "NewCreditLitmit"from NewCreditViewtheExhibitandexaminethestructureofthePRODUCTSYouneedtogenerateareportinthefollowingformat:5MPDigitalPhotoCamera'scategoryisPhotoYBox'scategoryisElectronicsEnvoyAmbassador'scategoryisWhichtwoquerieswouldgivetherequiredoutput?(ChooseSELECTprod_name||q''||'scategoryis'||prod_categoryFROMSELECTprod_name||q'['s]||'categoryis'||prod_categoryCATEGORIESFROMproducts;SELECTprod_name||q'\'s\'||'categoryis'||prod_categoryCATEGORIESFROMproducts;SELECTprod_name||q'<'s>'||'categoryis'||prod_categoryCATEGORIESFROMproducts;Answer:scott@ORCL>selectename||q'\'s\'||'categoryis'||salfromSMITH'scategoryisALLEN'scategoryisscott@ORCL>selectename||q'<'s>'||'categoryis'||salfromSMITH'scategoryisUsingtheCUSTOMERStable,youneedtogenerateareportthatshows50%ofeachamountineach elevel.ThereportshouldNOTshowanyrepeatedcreditamountsineach Whichquerywouldgivetherequirede_level,DISTINCTcust_credit_limit*0.50AS"50%CreditLimit"FROM e_level,DISTINCTcust_credit_limit*0.50AS"50%CreditLimit"FROM e_level||''||cust_credit_limit*0.50AS"50%CreditLimit"e_level''cust_credit_limit*0.50AS"50%CreditLimit"Answer:解析：主要dictinct的應用，去除重復ViewtheExhibitandexaminethedataintheCUSTOMERSEvaluatetheSQL>SELECTcust_nameAS"NAME",cust_credit_limit/2ASMIDPOINT,MIDPOINT+100AS"MAXLOWERFROMTheabovequeryproducesanerroronexecution.Whatisthereasonfortheerror?AnaliascannotbeusedinanThealiasNAMEshouldnotbeenclosedwithindoublequotationmarksTheMIDPOINT+100expressiongivesanerrorbecauseCUST_CREDIT_LIMITcontainsNULLThealiasMIDPOINTshouldbeenclosedwithindoublequotationmarksfortheCUST_CREDIT_LIMIT/2expression.Answer:EvaluatethefollowingSQL>SELECTpromo_name||q'{'sstartdatewas}'||promo_begin_dateAS"PromotionLaunches"FROMWhatwould eoftheaboveItproducesanerrorbecauseflowerbraceshavebeenItproducesanerrorbecausethedatatypesarenotItexecutessuccessfullyandintroducesan'sattheendofeachpromo_nameintheItexecutessuccessfullyanddisysthelil"{'sstartdatewas}"foreachrowintheoutput.Answer:scott@ORCL>SELECTename||q'{'sstartdatewas}'||AS"PromotionPromotionSMITH'sstartdatewas17-12月-80ALLEN'sstartdatewas20-2月-81WARD'sstartdatewas22-2月-81JONES'sstartdatewas02-4月-81MARTIN'sstartdatewas28-9月-81BLAKE'sstartdatewas01-5月-81CLARK'sstartdatewas09-6月-81SCOTT'sstartdatewas19-4月-87KING'sstartdatewas17-11月-81TURNER'sstartdatewas08-9-81ADAMS'sstartdatewas23-5月-87JAMES'sstartdatewas03-12月-81FORD'sstartdatewas03-12月-81MILLER'sstartdatewas23-1月-82ViewtheExhibitandexaminethedataintheEMPLOYEESYouwanttogenerateareportshowingthetotalcompensationpaidtoeachemployeetoYouissuetheSQL>SELECTename||'joinedon'||hiredate||',the pensationpaidis'||TO_CHAR(ROUND(ROUND(SYSDATE-hiredate)/365)*sal+comm)"COMPENSATIONUNTILDATE"FROMWhatis ItgeneratesanerrorbecausethealiasisnotItexecutessuccessfullyandgivesthecorrectItexecutessuccessfullybutdoesnotgivethecorrectItgeneratesanerrorbecausetheusageoftheROUNDfunctionintheexpressionisnotvalid.Itgeneratesanerrorbecausetheconcatenationoperatorcanbeusedtocombineonlytwoitems.Answer:scott@ORCL>SELECTename||'joinedon'||hiredate',thetotalcompensationpaidis'TO_CHAR(ROUND(ROUND(SYSDATE-hiredate)/365)*sal+"COMPENSATIONUNTILCOMPENSATIONUNTILSMITHjoinedon17-12月-80,thetotalcompensationpaidisALLENjoinedon20-2-81thetotalcompensationpaidis52960WARDjoinedon22-2月-81thetotalcompensationpaidis41750JONESjoinedon02-4月-81,thetotalcompensationpaidisMARTINjoinedon28-9-81thetotalcompensationpaidis41400BLAKEjoinedon01-5月-81,thetotalcompensationpaidisCLARKjoinedon09-6-81thetotalcompensationpaidisSCOTTjoinedon19-4-87thetotalcompensationpaidisKINGjoinedon17-11月-81,thetotalcompensationpaidisTURNERjoinedon08-9-81thetotalcompensationpaidis48000ADAMSjoinedon23-5月-87,thetotalcompensationpaidisJAMESjoinedon03-12月-81,thetotalcompensationpaidisFORDjoinedon03-12月-81,thetotalcompensationpaidisMILLERjoinedon23-1-82thetotalcompensationpaidis14 mfromempwhereMoraclescott@ORCL>select1000+nvl(comm,0)fromempwhereExaminethestructureofthePROMOTIONStable:nameNullTypePROMO_IDNOTNULLNUMBER(6)PROMO_NAMENOTNULLVARCHAR2(30)PROMO_CATEGORYNOTNULLVARCHAR2(30)PROMO_COSTNOTNULLNUMBER(10,2)ThemanagementwantstoseeareportofuniquepromotioncostsineachpromotionWhichquerywouldachievetherequiredSELECTDISTINCTpromo_cost,promo_categoryFROMSELECTpromo_category,DISTINCTpromo_costFROMSELECTDISTINCTpromo_cost,DISTINCTpromo_categoryFROMSELECTDISTINCTpromo_category,promo_costFROMpromotionsORDERBYAnswer:解析：distinct后的字段順序不同，得到的結果順序也不同，但是最終的內容都是一樣的，ADorderby1的區(qū)別，下面我們看看它的作用：scott@ORCL>selectdistinctjob,salfrom 630012行。scott@ORCL>selectdistinctjob,salfromemporderby 12DEvaluatethefollowingSELECTINTERVAL'300'MONTH,INTERVAL'54-2'YEARTOMONTH, 'HOURTOFROMWhatisthecorrectoutputoftheaboveA.+25-00,+54- ,+54- C.+25-00 ,+00Answer:INTERVAL'54-2'YEARTOINTERVAL 'HOURTO +00INTERVALYEARTOMONTHDataINTERVALYEARTOMONTHstoresaperiodoftimeusingtheYEARandMONTHdatetimefields.Thisdatatypeisusefulforrepresentingthedifferencebetweentwodatetimevalueswhenonlytheyearandmonthvaluesaresignificant.SpecifyINTERVALYEARTOMONTHasINTERVALYEAR[(year_precision)]TOwhereyear_precisionisthenumberofdigitsintheYEARdatetimefield.Thedefaultvalueofyear_precisionis2.MONTH2300YEARTOMONTH于是206123457WhichthreestatementsaretrueregardingthedatatypesinOracleDatabaseOnlyoneLONGcolumncanbeusedperATIMESTAMPdatatypecolumnstoresonlytimevalueswithfractionalTheBLOBdatatypecolumnisusedtostorebinarydatainanoperatingsystemTheminimumcolumnwidththatcanbespecifiedforaVARCHAR2datatypecolumnisThevalueforaCHARdatatypecolumnisblank-paddedtotheumdefinedcolumnwidth.Answer:oracle文檔：TheuseofLONGvaluesissubjecttotheseAtablecancontainonlyoneLONGYoucannotcreateanobjecttypewithaLONGLONGcolumnscannotappearinWHEREclausesorinintegrityconstraints(exceptthattheycanappearinNULLandNOTNULLconstraints).TheTIMESTAMPdatatypeisanextensionoftheDATEdatatype.Itstorestheyear,month,anddayoftheDATEdatatype,plushour,minute,andsecondvalues.Thisdatatypeisusefulforstoringprecisetimevaluesandforcollectingandevaluatingdateinformationacrossgeographicregions.TheBLOBdatatypestoresunstructuredbinarylargeobjects.BLOBobjectscanbethoughtofasbitstreamswithnocharactersetsemantics.BLOBobjectscanstorebinarydataupto(4gigabytes-1)*(thevalueoftheCHUNKparameterofLOBstorage).Ifthetablespacesinyourdatabaseareofstandardblocksize,andifyouhaveusedthedefaultvalueoftheCHUNKparameterofLOBstoragewhencreatingaLOBcolumn,thenthisisequivalentto(4gigabytes-1)*(databaseblocksize).TheVARCHAR2datatypespecifiesavariable-lengthcharacterstring.WhenyoucreateaVARCHAR2column,yousupplytheumnumberofbytesorcharactersofdatathatitcanhold.Oraclesubsequentlystoreseachvalueinthecolumnexactlyasyouspecifyit,providedthevaluedoesnotexceedtheumlengthofthecolumn.Ifyoutrytoinsertavaluethatexceedsthespecifiedlength,thenOraclereturnsanerrorTheCHARdatatypespecifiesafixed-lengthcharacterstring.OracleensuresthatallvaluesstoredinaCHARcolumnhavethelengthspecifiedbysize.Ifyouinsertavaluethatisshorterthanthecolumnlength,thenOracleblank-padsthevaluetocolumnlength.Ifyoutrytoinsertavaluethatistoolongforthecolumn,thenOraclereturnsanerror.ExaminethedescriptionoftheEMP_DETAILStablegivenbelow:nameNULLTYPEEMP_IDNOTNULLNUMBEREMP_NAMENOTNULLVARCHAR2(40)EMP_IMAGELONGWhichtwostatementsaretrueregardingSQLstatementsthatcanbeexecutedonthetable?(ChooseAnEMP_IMAGEcolumncanbeincludedintheGROUPBYAnEMP_IMAGEcolumncannotbeincludedintheORDERBYYoucannotaddanewcolumntothetablewithLONGasthedataYoucanalterthetabletoincludetheNOTNULLconstraintontheEMP_IMAGEAnswer:oracle文檔：Inaddition,LONGcolumnscannotappearinthesepartsofSQLGROUPBYclauses,ORDERBYclauses,orCONNECTBYclausesorwiththeDISTINCToperatorinSELECTstatementsTheUNIQUEoperatorofaSELECTThecolumnlistofaCREATECLUSTEREMP_IMAGELONGgroupbyorderbyTheuseofLONGvaluesissubjecttotheseAtablecancontainonlyoneLONGYoucannotcreateanobjecttypewithaLONGLONGcolumnscannotappearinWHEREclausesorinintegrityconstraints(exceptthattheycanappearinNULLandNOTNULLconstraints).LONGYouneedtocreateatableforabankingapplication.OneofthecolumnsinthetablehasthefollowingYouwantacolumninthetabletostorethedurationofthecreditThedatainthecolumnshouldbestoredinaformatsuchthatitcanbeeasilyaddedDATEdatatypewithoutusingconversionTheumperiodofthecreditprovisionintheapplicationis30Theinteresthastobecalculatedforthenumberofdaysanindividualhastakenacreditfor.WhichdatatypewouldyouuseforsuchacolumnintheINTERVALDAYINTERVALYEARAnswer:datedateINTERVALDAYINTERVALDAYTOSECONDstoresaperiodoftimeintermsofdays,hours,minutes,andseconds.ThisdatatypeisusefulforrepresentingtheprecisedifferencebetweentwodatetimevaluesExaminethestructureproposedfortheTRANSACTIONStable:nameNullTypeTRANS_IDNOTNULLNUMBER(6)CUST_NAMENOTNULLVARCHAR2(20)CUST_STATUSNOTNULLCHARTRANS_DATENOTNULLDATETRANS_VALIDITYVARCHAR2CUST_CREDIT_LIMITNUMBERWhichstatementsaretrueregardingthecreationandstorageofdataintheabovetable(ChooseallthatTheCUST_STATUScolumnwouldgiveanTheTRANS_VALIDITYcolumnwouldgiveanTheCUST_STATUScolumnwouldstoreexactlyoneTheCUST_CREDIT_LIMITcolumnwouldnotbeabletostoredecimalTheTRANS_VALIDITYcolumnwouldhaveaumsizeofoneTheTRANS_DATEcolumnwouldbeabletostoreday,month,century,year,hour,minutes,seconds,andfractionsAnswer:ThedefaultlengthforaCHARcolumnis1byteandtheumallowedis2000bytes.A1-bytestringcanbeinsertedintoaCHAR(10)column,butthestringisblank-paddedto10bytesbeforeitisstored.C正確，AYoumustspecifyaumlengthforaVARCHAR2column.Thisummustbeatleast1byte,althoughtheactualstringstoredispermittedtobeazero-lengthstringB正確，EActualDataSpecifiedAsStored123.89NUMBERscott@ORCL>createtablezbcxy(idscott@ORCL>insertintozbcxy1scott@ORCL>select*fromDTheDATEdatatypestoresdateandtimeinformation.Althoughdateandtimeinformationcanberepresentedinbothcharacterandnumberdatatypes,theDATEdatatypehasspecialassociatedproperties.ForeachDATEvalue,Oraclestoresthefollowinginformation:year,month,day,hour,minute,andsecond.FExaminethestructureproposedfortheTRANSACTIONStable:nameNullTypeTRANS_IDNOTNULLNUMBER(6)CUST_NAMENOTNULLVARCHAR2(20)CUST_STATUSNOTNULLVARCHAR2TRANS_DATENOTNULLDATETRANS_VALIDITYINTERVALDAYTOSECONDWhichtwostatementsaretrueregardingthestorageofdataintheabovetablestructure?(Choosetwo.)TheTRANS_DATEcolumnwouldallowstorageofdatesonlyinthedd-mon-yyyyTheCUST_CREDIT_VALUEcolumnwouldallowstorageofpositiveandnegativeTheTRANS_VALIDITYcolumnwouldallowstorageofatimeintervalindays,hours,minutes,andTheCUST_STATUScolumnwouldallowstorageofdatauptotheumVARCHAR2sizeof4,000Answer:Ato_char設置多種格式TheNUMBERdatatypestoreszeroaswellaspositiveandnegativefixednumberswithabsolutevaluesfrom1.0x10-130tobutnotincluding1.0x10126.BINTERVALDAYTOSECONDstoresaperiodoftimeintermsofdays,hours,minutes,andseconds.Thisdatatypeisusefulforrepresentingtheprecisedifferencebetweentwodatetimevalues.CDvarcharDYouneedtocreateatablewiththefollowingcolumnEmployeeID(numericdatatype)foreachEmployeeName(characterdatatype)thatstorestheemployeeHiredate,whichstoresthedateofjoiningtheorganizationforeachStatus(characterdatatype),thatcontainsthevalue'ACTIVE'ifnodataisResume(characterlargeobject[CLOB]datatype),whichcontainstheresumesubmittedbytheWhichisthecorrectsyntaxtocreatethisCREATETABLE(emp_idNUMBER(4),emp_nameVARCHAR2(25),start_dateDATE,resumeCREATETABLE(emp_idNUMBER(4),emp_nameVARCHAR2(25),start_dateDATE,resumeCREATETABLE(emp_idNUMBER(4),emp_nameVARCHAR2(25),start_dateDATE,resumeCREATETABLE(emp_idNUMBER,emp_nameVARCHAR2(25),start_dateDATE,resumeAnswer:TheCLOBdatatypestoressingle-byteandmultibytecharacterdata.Bothfixed-widthandvariable-widthcharactersetsaresupportedandbothusethedatabasecharactersetclobA錯BCdefault用單引號D正確WhichisthevalidCREATETABLECREATETABLEemp9$#(emp_noNUMBERCREATETABLE9emp$#(emp_noCREATETABLEemp*123(emp_noCREATETABLEemp9$#(emp_noNUMBER(4),dateAnswer:1-30字節(jié)，并且以字母開頭，可以包含字oracle數據庫的保留字BCDoracle中關鍵字作為屬性名稱A正確Whichtwostatementsaretrueregardingtables?(ChooseAtablenamecanbeofanyAtablecanhaveanynumberofAcolumnthathasaDEFAULTvaluecannotstorenullAtableandaviewcanhavethesamenameinthesameAtableandasynonymcanhavethesamenameinthesameThesametablenamecanbeusedindifferentschemasinthesameAnswer:1-30ABFORIIN1..999EXECUTEIMMEDIATE'ALTERTABLEzbcxyADDid'||I||'NUMBER(1)';ENDLOOP;scott@ORCL>altertablezbcxyaddid1000number(1);altertablezbcxyaddid1000number(1)*1行出現錯誤ORA-01792:oracle1000BCscott@ORCL>createtablezbcxy(namevarchar(20)defaultscott@ORCL>insertintozbcxyvalues('1scott@ORCL>select*fromCDDEscott@ORCL>createpublicsynonymempforscott@ORCL>createsynonymdeptfordept;createsynonymdeptfordept*1行出現錯誤ORA-01471:無法創(chuàng)建與對象同名的同義詞EFWhichtwostatementsaretrueregardingconstraints?(ChooseAforeignkeycannotcontainNULLAcolumnwiththeUNIQUEconstraintcancontainNULLAconstraintisendonlyfortheINSERToperationonaAconstraintcanbedisabledeveniftheconstraintcolumncontainsAllconstraintscanbedefinedatthecolumnlevelaswellasthetableAnswer:Insertintozbcxyvaluesselecetenamefromemp;empename的控制插入zbcxy表中ABuniqueCinsertupdateDscott@ORCL>createtablezbcxy(idnumber(4)constraintp_pkprimaryscott@ORCL>insertintozbcxy1scott@ORCL>altertablezbcxydisableconstraintDEEWhichtwostatementsaretrueregardingconstraints?(ChooseAforeignkeycannotcontainNULLThecolumnwithaUNIQUEconstraintcanstoreNULLSAconstraintisendonlyforanINSERToperationonaYoucanhavemorethanonecolumninatableaspartofaprimaryAnswer:ABCD選項，一個表中只能有雖然只有一個約束，但是可以建立一個聯(lián)合主鍵約束，這樣就有D選項正確EvaluatethefollowingCREATETABLECREATETABLE(ord_noNUMBER(2)CONSTRAINTord_pkPRIMARYord_dateDATE,cust_idNUMBER(4));CREATETABLEord_items(ord_noNUMBER(2),item_noNUMBER(3),qtyNUMBER(3)CHECK(qtyBETWEEN100ANDexpiry_datedateCHECK(expiry_date>SYSDATE),CONSTRAINTit_pkPRIMARYKEY(ord_no,item_no),CONSTRAINTord_fkFOREIGNKEY(ord_no)REFERENCESorders(ord_no));Theabovecommandfailswhenexecuted.Whatcouldbethereason?SYSDATEcannotbeusedwiththeCHECKTheBETWEENclausecannotbeusedfortheCHECKTheCHECKconstraintcannotbecedoncolumnshavingtheDATEdataORD_NOandITEM_NOcannotbeusedasacompositeprimarykeybecauseORD_NOisalsotheFOREIGNAnswer:(ord_noitem_noqtyNUMBER(3)CHECK(qtyBETWEEN100ANDexpiry_datedateCHECK(expiry_date>CONSTRAINTit_pkPRIMARYKEYCONSTRAINTord_fkFOREIGNKEY(ord_no)REFERENCESorders(ord_no));expiry_datedateCHECK(expiry_date>SYSDATE),*5行出現錯誤ORA-02436:CHECKAEvaluatethefollowingSQLSQL>CREATESEQUENCEord_seqINCREMENTBY10STARTWITHMAXVALUE9999SQL>CREATE(ord_noNUMBER(4)DEFAULTord_seq.NEXTVALNOTqtyNUMBER(3)CHECK(qtyBETWEEN100ANDexpiry_datedateCHECK(expiry_date>SYSDATE),CONSTRAINTit_pkPRIMARYKEY(ord_no,item_no),CONSTRAINTord_fkFOREIGNKEY(ord_no)REFERENCESThecommandtocreateatablefails.IdentifythereasonfortheSQLstatementfailure?(ChooseallthatYoucannotuseSYSDATEintheconditionofaCHECKYoucannotusetheBETWEENclauseintheconditionofaCHECKYoucannotusetheNEXTVALsequencevalueasaDEFAULTvalueforaYoucannotuseORD_NOandITEM_NOcolumnsasacompositeprimarykeybecauseORD_NOisalsotheFOREIGNAnswer:(ord_noNUMBER(4)DEFAULTord_seq.NEXTVALNOTitem_noqtyNUMBER(3)CHECK(qtyBETWEEN100ANDexpiry_datedateCHECK(expiry_date>CONSTRAINTit_pkPRIMARYKEYCONSTRAINTord_fkFOREIGNKEY(ord_no)REFERENCESorders(ord_no));(ord_noNUMBER(4)DEFAULTord_seq.NEXTVALNOTNULL,*2行出現錯誤ORA-00984:A選項正確，B，D選項錯誤，經上面測試，CWhichCREATETABLEstatementisCREATETABLEord_details(ord_noNUMBER(2)PRIMARYKEY,ord_dateDATENOTCREATE(ord_noNUMBER(2)UNIQUE,NOTord_dateDATEDEFAULTSYSDATENOTCREATETABLEord_details(ord_noNUMBER(2),ord_dateDATEDEFAULTNOTCONSTRAINTord_uqUNIQUE(ord_no),CONSTRAINTord_pkPRIMARYKEY(ord_no));CREATETABLEord_details(ord_noNUMBER(2),ord_dateDATEDEFAULTSYSDATENOTCONSTRAINTord_pkPRIMARYKEY(ord_no,Answer:ABC選項：defaultscott@ORCL>createtablezbcxy(idconstraintp_iduconstraintp_idkprimaryconstraintp_idu*2行出現錯誤ORA-02261:DYouwanttocreateanORD_DETAILtabletostoredetailsforanordercedhavingthefollowingbusinessTheorderIDwillbeuniqueandcannothavenullTheorderdatecannothavenullvaluesandthedefaultshouldbethecurrentTheorderamountshouldnotbelessthanTheorderstatuswillhavevalueseithershippedornotTheorderpaymentmodeshouldbecheque,creditcard,orcashondeliveryWhichisthevalidDDLstatementforcreatingtheORD_DETAILCREATE(ord_idNUMBER(2)CONSTRAINTord_id_nnNOTNULL,ord_dateDATEDEFAULTSYSDATENOTNULL,ord_amountNUMBER(5,2)CONSTRAINTord_amount_minCHECK(ord_amount>50),ord_statusVARCHAR2(15)CONSTRAINTord_status_chkCHECK(ord_statusIN('Shipped','NotShipped')),ord_pay_modeVARCHAR2(15)CONSTRAINTord_pay_chkCHECK(ord_pay_modeIN('Cheque','CreditCard','CashOnCREATE(ord_idNUMBER(2)CONSTRAINTord_id_ukUNIQUENOTNULL,ord_dateDATEDEFAULTSYSDATENOTNULL,ord_amountNUMBER(5,2)CONSTRAINTord_amount_minCHECK(ord_amount>50),ord_statusVARCHAR2(15)CONSTRAINTord_status_chkCHECK(ord_statusIN('Shipped','NotShipped')),ord_pay_modeVARCHAR2(15)CONSTRAINTord_pay_chkCHECK(ord_pay_modeIN('Cheque','CreditCard','CashOnCREATE(ord_idNUMBER(2)CONSTRAINTord_id_pkPRIMARYKEY,ord_dateDATEDEFAULTSYSDATENOTNULL,ord_amountNUMBER(5,2)CONSTRAINTord_amount_minCHECK(ord_amount>=50),ord_statusVARCHAR2(15)CONSTRAINTord_status_chkCHECK(ord_statusIN('Shipped','NotShipped')),ord_pay_modeVARCHAR2(15)CONSTRAINTord_pay_chkCHECK(ord_pay_modeIN('Cheque','CreditCard','CashOnCREATETABLEord_details(ord_idNUMBER(2),ord_dateDATENOTNULLDEFAULTord_amountNUMBER(5,2)CONSTRAINTord_amount_minCHECK(ord_amount>=50),ord_statusVARCHAR2(15)CONSTRAINTord_status_chkCHECK(ord_statusIN('Shipped','NotShipped')),ord_pay_modeVARCHAR2(15)CONSTRAINTord_pay_chkCHECK(ord_pay_modeIN('Cheque','CreditCard','CashOnAnswer:根據題目第一句話：TheorderIDwillbeuniqueandcannothavenull只有主鍵才能滿足這個條件，unique是可以為空值的C選項YoucreatedanORDERStablewiththefollowingdescription:nameNullTypeORD_IDNOTNULLNUMBER(2)CUST_IDNOTNULLNUMBER(3)ORD_DATENOTNULLDATEORD_AMOUNTNOTNULLNUMBERYouinsertedsomerowsinthetable.Aftersometime,youwanttoalterthetablebycreatingthePRIMARYKEYconstraintontheORD_IDcolumn.WhichstatementistrueinthisYoucannothavetwoconstraintsononeYoucannotaddaprimarykeyconstraintifdataexistsintheTheprimarykeyconstraintcanbecreatedonlyatthetimeoftablecreationYoucanaddtheprimarykeyconstraintevenifdataexists,providedthatthereareAnswerDscott@ORCL>select*from123scott@ORCL>altertablezbcxyaddconstraintp_pkprimaryscott@ORCL>select*from1232scott@ORCL>altertablezbcxyaddconstraintp_pkprimarykey(id);altertablezbcxyaddconstraintp_pkprimarykey(id)*1行出現錯誤ORA-02437:無法驗證(SCOTT.P_PK)-主DWhichtwostatementsaretrueregardingconstraints?(ChooseAtablecanhaveonlyoneprimarykeyandoneforeignAtablecanhaveonlyoneprimarykeybutmultipleforeignOnlytheprimarykeycanbedefinedatthecolumnandtableTheforeignkeyandparenttableprimarykeymusthavethesameBothprimarykeyandforeignkeyconstraintscanbedefinedatbothcolumnandtableAnswer:ExaminethefollowingSQLSQL>CREATETABLEproductsprod_idNUMBER(3)CONSTRAINTp_ckCHECK(prod_id>0),prod_nameCHAR(30),prod_qtyNUMBER(6),CONSTRAINTp_nameNOTNULL,CONSTRAINTprod_pkPRIMARYKEY(prod_id));SQL>CREATETABLEwarehouse(warehouse_idroomnoNUMBER(10)CONSTRAINTr_idCHECK(roomnoBETWEEN101ANDlocationVARCHAR2(25),prod_idNUMBER(3),CONSTRAINTwr_pr_pkPRIMARYKEYCONSTRAINTprod_fkFOREIGNKEY(prod_id)REFERENCESproducts(prod_id));WhichstatementistrueregardingtheexecutionoftheaboveSQLcommands?BothcommandsThefirstCREATETABLEcommandgeneratesanerrorbecausetheNULLconstraintisnotvalid.ThesecondCREATETABLEcommandgeneratesanerro