ocp051題庫筆記講解1z0v902解析

1Z0-051V9.022017-6-5ExaminethedataintheORD_ITEMS Youwanttofindoutifthereisanyiteminthetableforwhichtheaverageumtyismorethan50.YouissuethefollowingSQL>SELECTFROMord_itemsGROUPBYitem_noHAVINGWhichstatementistrueregarding eofthisItexecutessuccessfullyandgivesthecorrectItgivesanerrorbecausetheHAVINGclauseisnotItexecutessuccessfullybutdoesnotgivethecorrectItgivesanerrorbecausetheGROUPBYexpressionisnotD.GROUPBY表達式無效。HAVING2個組函數(shù)，且嵌套內(nèi)的值已經(jīng)是最大值了，且只有一個，再createtablet116(ORD_NOnumber,ITEM_NOnumber,QTYnumber);insertintot116values(1,111,10);insertintot116valuesinsertintot116values(1,333,30);insertintot116values(2,333,30);insertintot116values(2,444,40);insertintot116valuesselect*fromSELECTFROMGROUPBYHAVINGSELECTFROMGROUPBYHAVINGSELECTFROMGROUPBYSELECTFROMGROUPBYSELECT(20+30+40+40)/4FROMSELECTFROMGROUPBYSELECTFROMGROUPBYHAVINGMAX(qty)selectavg(mq)from(SELECTMAX(qtyFROMGROUPBY)SELECTSELECTFROMGROUPBYHAVINGMAX(qty)selectavg(mq)from(SELECTMAX(qty)mqFROMGROUPBYHAVINGMAX(qty))WhichstatementsaretrueregardingtheWHEREandHAVINGclausesinaSELECTstatement?(Chooseallthatapply.)TheHAVINGclausecanbeusedwithaggregatefunctionsinTheWHEREclausecanbeusedtoexcluderowsafterdividingthemintogroups.TheWHEREclausecanbeusedtoexcluderowsbeforedividingthemintogroups.TheaggregatefunctionsandcolumnsusedintheHAVINGclausemustbespecifiedintheSELECTlistofthequery.TheWHEREandHAVINGclausescanbeusedinthesamestatementonlyiftheyareappliedtodifferentcolumnsinthetable.A.HAVING子句能在子查詢中使用聚合函數(shù)。createtablet117(IDnumber,QTYnumber);insertintot117values(1,10);insertintot117valuesinsertintot117values(1,30);insertintot117values(2,30);insertintot117values(2,40);insertintot117valuesSELECTid,FROMGROUPBYIDHAVINGAVG(qty)>20;SELECT*FROMSELECTID,AVG(qty)FROMGROUPBYSELECT*FROMT117WHEREQTY>10SELECTID,AVG(qty)FROMt117WHEREQTY>10GROUPBYIDSELECTID,AVG(qty)FROMWHEREQTY>10GROUPBYID--;HAVINGAVG(qty)>30SELECTID,AVG(qty)FROMWHEREQTY>10GROUPBYIDHAVINGSELECTAVG(qty)FROMWHEREQTY>10GROUPBYIDHAVINGSELECTID,max(qty)FROMWHEREQTY>10GROUPBYIDHAVINGHAVINGSELECTID,AVG(qty)FROMWHEREQTY>10GROUPBYIDHAVINGViewtheExhibitandexaminethestructureofthePROMOTIONSExaminethefollowingtwoSQLStatementSQL>SELECTpromo_category,SUM(promo_cost)FROMpromotionsWHEREpromo_end_date-promo_begin_date>30GROUPBYpromo_category;StatementSQL>SELECTpromo_category,sum(promo_cost)FROMpromotionsGROUPBYHAVINGMIN(promo_end_date-WhichstatementistrueregardingtheabovetwoSQLstatement1givesanerror,statement2executesstatement2givesanerror,statement1executesstatement1andstatement2executesuccessfullyandgivethesameoutputstatement1andstatement2executesuccessfullyandgiveadifferentoutput評估下面的2個語句21執(zhí)行成功。SELECTpromo_category,SUM(promo_cost)FROMpromotionsWHEREpromo_end_date-promo_begin_date>30GROUPBYpromo_category;SELECTpromo_category,sum(promo_cost)FROMpromotionsGROUPBYHAVINGMIN(promo_end_date-ExaminethedataintheORD_ITEMSORD_NOITEM_NO111223EvaluatethefollowingSQL>SELECTitem_no,FROMHAVINGAVG(qty)>MIN(qty)*GROUPBYWhichstatementistrueregarding eoftheaboveItgivesanerrorbecausetheHAVINGclauseshouldbespecifiedaftertheGROUPBYclause.ItgivesanerrorbecausealltheaggregatefunctionsusedintheHAVINGclausemustbespecifiedintheSELECTlist.Itdisystheitemnoswiththeiraveragetywheretheaveragetyismorethandoubletheminimumtyofthatiteminthetable.Itdisystheitemnoswiththeiraveragetywhereaveragetyismorethandoubletheoverallminimumtyofalltheitemsinthetable.HAVINGGROUPBYA選項不正確，HAVINGGROUPBYSELECTitem_no,FROMHAVINGAVG(qty)>MIN(qty)*GROUPBYSELECTitem_no,AVG(qty),MIN(qty),MIN(qty)*2FROMT116GROUPBYHAVINGAVG(qty)>MIN(qty)*ViewtheExhibits andexaminethestructuresofthePRODUCTS,SALES,andCUSTOMERStables.Yd_id,prod_name,prod_list_price,ty_sold,cust_last_nameFROMproductspNATURALJOINsalessNATURALJOINcustomersWHEREprod_idWhichstatementistrueregarding eofthisItexecutesItproducesanerrorbecausetheNATURALjoincanbeusedonlywithtwotables.ItproducesanerrorbecauseacolumnusedintheNATURALjoincannothaveaqualifier.ItproducesanerrorbecauseallcolumnsusedintheNATURALjoinshouldhaveaqualifier.PRODUCTS,SALES,andCUSTOMERSSELECTd_id,prod_name,prod_list_price, FROMproductspNATURALJOINsalessNATURALJOINcustomerscWHEREprod_id=148;--SELECTprod_id,prod_name,prod_list_price, FROMproductsNATURALJOINsalessNATURALJOINcustomerscWHEREprod_id=148;SELECTprod_id,cust_id,d_name, ty_sold,FROMproductsNATURALJOINsalessNATURALJOINcustomerscWHEREprod_id=148;NATURAL FROMtablelNATURALJOINDROPTABLEt120X;DROPTABLEt120Y;DROPTABLEcreatetablet120X(anumber,bnumber,cnumber);createtablet120Y(bnumber,cnumber,dnumber);createtablet120Z(dnumber,enumber,fINSERTINTOT120XVALUESINSERTINTOT120YVALUES(2,3,4);INSERTINTOT120YVALUES(2,3,40);INSERTINTOT120YVALUESINSERTINTOT120ZVALUES(4,5,6);INSERTINTOT120ZVAL