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

1Z0-051V9.02141-145ViewtheExhibitandexaminethestructureofCUSTOMERSandGRADEStables.Youneedtodisplaynamesandgradesofcustomerswhohavethehighestcreditlimit.WhichtwoSQLstatementswould plishthetask?(Choosetwo.)A.SELECTcustname,gradeFROMcustomers,WHERE(SELECTMAX(cust_credit_limit)FROMcustomers)BETWEENstartvalandendval;B.SELECTcustname,gradeFROMcustomers,gradesWHERE(SELECTMAX(cust_credit_limit)FROMcustomers)BETWEENstartvalandendvalANDcust_credit_limitBETWEENstartvalANDC.SELECTcustname,gradeFROMcustomers,gradesWHEREcust_credit_limit=(SELECTMAX(cust_credit_limit)FROMANDcust_credit_limitBETWEENstartvalANDD.SELECTcustname,gradeFROMcustomers,gradesWHEREcust_credit_limitIN(SELECTMAX(cust_credit_limit)FROMANDMAX(cust_credit_limit)BETWEENstartvalANDCUSTOMERSCUSTOMERSandGRADEScreditlimit的用户的名称和等级SQL语句能得出想要的结果?（选择两项）BETWEEN...ANDdroptablecust141;droptablegrade141;createtablecust141(cnamevarchar2(10),crlnumber);insertintocust141values('zs',65);insertintocust141values('ls',75);insertintocust141values('ww',85);insertintocust141valuescreatetablegrade141(gnamevarchar2(2),startvalnumber,endvalinsertintograde141values('A',90,99);insertintograde141values('B',80,89);insertintograde141values('C',70,79);insertintograde141valuesSELECTCNAME,FROMcust141,WHERE(SELECTMAX(CRL)FROMcust141)BETWEENstartvalSELECTCNAME,FROMcust141,WHERE(SELECTMAX(CRL)FROMcust141)BETWEENstartvalANDCRLBETWEENstartvalANDSELECTSELECTCNAME,FROMcust141,WHEREcrl=(SELECTMAX(crl)FROMANDcrlBETWEENstartvalANDSELECTcustname,FROMcust141,WHERECRLIN(SELECTMAX(CRL)FROMANDMAX(CRL)BETWEENstartvalANDendval;--SELECTMAX(CRL)FROMcust141WHERECRL<90HAVINGViewtheExhibitandexaminethestructureofthePRODUCTSEvaluatethefollowingSQL>SELECTprod_nameFROMproductsWHEREprod_idIN(SELECTprod_idFROMproductsWHEREprod_list_price(SELECTMAX(prod_list_price)FROMproductsWHEREprod_list_price(SELECTMAX(prod_list_price)Whatwouldbe eofexecutingtheaboveSQLItproducesanItshowsthenamesofallproductsintheItshowsthenamesofproductswhoselistpriceisthesecondhighestinthetable.Itshowsthenamesofallproductswhoselistpriceislessthanthe umlistprice.SQL语句执行后会出现什么结果?SELECTMAX(prod_list_priceFROMproducts，得出SELECTMAX(prod_list_price)FROMproductsWHEREprod_list_price<(SELECTMAX(prod_list_price)FROMSELECTprod_nameFROMproductsWHEREprod_idIN(SELECTFROMWHEREprod_list_price(SELECTMAX(prod_list_price)FROMproductsWHEREprod_list_price(SELECTMAX(prod_list_price)FROM)DROPTABLECREATETABLET142(prod_idNUMBER,prod_nameVARCHAR2(10),prod_list_priceNUMBER);INSERTINTOT142VALUES(1,'A',100);INSERTINTOT142VALUESINSERTINTOT142VALUESSELECTSELECTFROMWHEREprod_id(SELECTFROMWHEREprod_list_price(SELECTMAX(prod_list_price)FROMT142WHEREprod_list_price(SELECTMAX(prod_list_price)FROM)ViewtheExhibitandexaminethestructureofthePROMOTIONSYouhavetogenerateareportthatdisplaysthepromonameandstartdateforallpromosthatstartedafterthelastpromointhe'INTERNET'category.Whichquerywouldgiveyoutherequiredoutput?A.SELECTpromo_name,promo_begin_dateFROMWHEREpromo_begin_date>ALL(SELECTMAX(promo_begin_date)FROMpromotions)ANDpromo_category='INTERNET';B.SELECTpromo_name,promo_begin_dateFROMpromotionsWHEREpromo_begin_dateIN(SELECTpromo_begin_dateFROMWHEREpromo_category=C.SELECTpromo_name,promo_begin_dateFROMpromotionsWHEREpromo_begin_date>ALL(SELECTpromo_begin_dateFROMpromotionsWHEREpromo_category=D.SELECTpromo_name,promo_begin_dateFROMpromotionsWHEREpromo_begin_date>ANY(SELECTpromo_begin_dateFROMpromotionsWHEREpromo_category=promonamestartA选项不正确，没结果，促销开始日期大于最大促销日期的（所有活动中最大的），并INTERNET的商品，很明显，不存在这样的记录。CINTERNET的开始日期，然后>ALL,表示大于最大的开始日SELECTpromo_name,FROMWHEREpromo_begin_date>ALL(SELECTFROMWHEREpromo_category=DROPTABLECREATETABLE(promo_nameVARCHAR2(3),promo_begin_datepromo_categoryINSERTINTO T143VALUES('A',SYSDATE,'TV');INSERTINTO T143VALUES('B',SYSDATE+1,'TV');INSERT T143VALUES('C',SYSDATE-INSERTINSERT T143VALUES('D',SYSDATE-SELECTpromo_name,FROMWHEREpromo_begin_date>ALL(SELECTFROMWHEREpromo_category=ViewtheExhibitandexaminethestructureofthePRODUCTSYouwanttodisplaythecategorywiththe umnumberofitems.Youissuethefollowingquery:SQL>SELECTCOUNT(*),prod_category_idFROMproductsGROUPBYHAVINGCOUNT(*)=(SELECTMAX(COUNT(*))FROMWhatis ItexecutessuccessfullyandgivesthecorrectItexecutessuccessfullybutdoesnotgivethecorrectItgeneratesanerrorbecausethesubquerydoesnothaveaGROUPBYclause.Itgeneratesanerrorbecause=isnotvalidandshouldbereplacedbytheINoperator.groupby子句，countSELECTCOUNT(*),FROMGROUPBYHAVINGCOUNT(*)=(SELECTMAX(COUNT(*))FROMproducts);----问题出在这SELECTMAX(COUNT(*))FROMproducts--SELECTMAX(CTFROMSELECTCOUNT(*CTFROMproducts--成SELECTprod_category_id,MAX(COUNT(*))FROMproductsGROUPprod_category_id--SELECTMAX(COUNT(*))FROMproductsGROUPBYprod_category_id-成--相当selectmax(ct)fromSELECTprod_category_id,COUNT(*)ctFROMproductsGROUPprod_category_idSELECTcount(COUNT(*))FROMproductsGROUPBYprod_category_id-成--相当SELECTcount(CT)FROM(SELECTCOUNT(*)CTFROMproductsGROUP)--修改一SELECTcount(CT)FROM(SELECTprod_category_id,COUNT(*)CTproductsGROUPBYprod_category_id--成--再修改一下，比较结SELECTprod_category_id,count(CT)FROM(SELECTprod_category_id,COUNT(*CTFROMproductsGROUPBYprod_category_id)GROUPBYprod_category_id--成功SELECTCOUNT(*),FROMGROUPBYHAVINGHAVINGCOUNT(*)=(SELECTMAX(COUNT(*))FROMproductsBYprod_category_id);--SELECTCOUNT(*),FROMGROUPBYHAVINGCOUNT(*)=(SELECTMAX(ct)FROM(selectCOUNT(*)fromproductsgroupbyprod_category_id));-SELECTCOUNT(*),FROMGROUPBYHAVINGCOUNT(*)=(SELECTMAX(ct)FROM(selectCOUNT(*)fromT143groupbypromo_category));-SELECT*FROMINSERT T143VALUES('E',SYSDATE-SELECTCOUNT(*),FROMGROUPBYHAVINGCOUNT(*)=(SELECTMAX(ct)FROM(selectCOUNT(*)fromT143groupbypromo_category));-ViewtheExhibitandexaminethestructureoftheCUSTOMERSYouissuethefollowingSQLstatementontheCUSTOMERStabletodisplaythecustomerswhoareinthesamecountryascustomerswiththelastname'KING'andwhosecreditlimitislessthantheumcreditlimitincountriesthathavecustomerswiththelastname'KING':SQL>SELECTcust_id,FROMcustomersWHEREcountry_idIN(SELECTcountry_idFROMcustomersWHERE=ANDcust_credit_limit(SELECTMAX(cust_credit_limit)FROMcustomersWHEREcountry_idIN(SELECTFROMWHEREcust_last_name='King'));Whichstatementistrueregarding eoftheaboveItexecutesandshowstherequiredItproducesanerrorandthe<operatorshouldbereplaced<ALLtogettherequiredItproducesanerrorandthe<operatorshouldbereplaced<ANYtogettherequiredItproducesanerrorandtheINoperatorshouldbereplaced=intheWHEREclauseofthemainquerytogettherequiredSQLla