Sql常见面试题.docx
SQ1.:selecta.titleza.usemame,b.adddatefromtablear(selectma×(adddate)adddatefromtablewheretable.title=a.title)b说明，外连接查询(表名1.a表名2：b)SQ1.：selecta.aza.bza.cfbeb.d,b.ffroma1.EFTOUTJOINbONa.a=b.c说明，H程支限提前五分钟提示SQ1.：selectfrom11程支配wheredatediff('mlnute',f起先时间耻tdate()>5说明，两张关联表,删除主表中已经在副表中没有的信息SQ1.:deletefrominfowherenotexists(select*fromifobzwhereinfo.infid=infobz.infid)说明：一SQ1.:SE1.ECTA.NUM,A.NAMEzB.UPD.DAT,B.PREV-UPD,DATEFROMIAB1.E1,(SE1.ECTX.NUMzX.UPD-DATezY,UPD.DATEPREV-PD-DATEFROM(SE1.ECTNUM,UPD,DATE,INBOUND,QTSTOCK_ONHANDFROMTAB1.E2WHERETO_CHAR(UPD_DATE；YYYY/MM,)三TO_CHAR(SYSDATE,YYYYMM,)X,(SE1.ECTNUM,UPD.DATE,STOCKeONHANDFROMTAB1.E2WHERETO-CHAR(UPD.DATEz,YYYYMM,)=TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,'YYYYMM,)：；,01rYYYYMMDD,)-1,'YYYYMM,)where×.nm=y.num<÷>2oracle90pass3xml40fail4jsp30fail5servlet80pass写出此查询语句没有装ORAC1.E,没试过selectcourseidzCOUgename,score,decode<sign(score-60)zlfait,pass')asmarkfromcourse完全正确SQ1.>desccourse-vNameNull?TypeCourseidnumberCOURSENAMEVARCHAR2(10)SCORENUMBERSQ1.>select*fromCOUrSJv;CourseidcoursenamescoreIjava702oracle903xml404jsp305servlet80SQ1.>selectCOUrSeid,coursename,scorerdecode(sign(score-60)lfai,'pass')asmarkfromCOUrSjv;Courseidcoursenamescoremark1java70pass2oracle90pass3xml40fall4jsp30fail5servlet80pass原衣:idproidpronameIlM12F21N22G31B32A瓷询后的表：idprolpro2IMF2NG3BA写出杳询语句解决方案sq求解表a列ala2记录】aIb2x2y2zfllselect能选成以下结果吗？1 ab2 xyz运用pl/sql代码实现，但要求你加合后的长度不能超出OradeVarChar2长度的限制,下面是一个例子createorreplacetypestrings_tableistableofvarchar2(20);Zcreateorreplacefunctionmerge(PVin$tring$_table)returnvarchar2i$ISVarChar2(4000);beginforiinl.pv.countloopIs：,=Is11pv(i);endloop;returnIs;end;/createtablet(idnumber,namevarchar2(10);insertintotva!ues(l,Joan');insertintotvafues(l,Jack,);insertintotvaIues(l,Tm');insertintotvaIues(2,Rose');insertintotvaues(2,'Jenny');columnnamesformata80;selectt.id,merge(cast(multiset(selectnamefromtwheret.id三t.id)asstrings-table)namesfrom(selectdistinctidfromt)t;droptypeStringsJabIe;dropfunctionmerge;droptablet;用sql：Wellifyouhaveathoreticalmaximum,whichIwouldassumeyouwouldgiventhelegibilityoflistinghundredsofemployeesinthewayyoudescribethenyes.ButtheSQ1.needstousethe1.AGfunctionforeachemployee,henceahundredempsahundred1.AGs,sokindofbulky.Thisexampleusesamaxof6,andwouldneedmorecutnpastingtodomorethanthat.SQ1.>selectdeptno,dname,emps2 from(3 selectd.deptnord.dname,rtrim(e.ename11,4 lead(e.eamezl)over(partitionbyd.depto5 orderbye.ename)6 lead(e.ename,2)over(partitionbyd.deptno7 orderbye.ename)8 eade.enamez3)over(partitionbyd.deptno9 orderbye.ename)10 Ieadgename.4)over(partitionbyd.deptno11 orderbye.ename)(r(12 lead(e.ename,5)over(partitionbyd.deptno13 orderbye.ename)，')emps,14 row_number)over(partitionbyd.deptno15 orderbye.ename)×16 fromempe,deptd17 whered.deptno=e.deptno18 )19 where×=120/DEPTNODNAMEEMPS10ACCOUNTINGC1.ARK,KING,MI1.1.ER20RESEARCHADAMS,FORD,JONES,RNEY,SCOTTSMITH30SA1.ESA1.1.ENzB1.AKE,JAMES,MARTIN.TURNER,WARDalso先createfucUonget_a2;createorreplacefunctionget_a2(tmp_alnumber)returnvarchar2isCol_a2VarChar2(4000);beginCol_a2:»H;forcurin(selecta2fromuite-awhereal=tmp-al)loopCol_a2=Col_a2|cur.a2;endloop;returnCO1.a2;endget-a2;selectdistinctalrget-a2(al)fromunite_aIABC2EK53KMN直*xac*，*«*，*«*”*：*寥*«*个3Ql面试鹿去年应聘一个职位未果工间被考了一个看似筒洁的题却.我没有找到好的大案.不知各位大虾有无好的解法?JS为:有两个表,tl,tzTabletl:SE1.1.ERINON_SE1.1.ERABACADBABCBDCACBCDDADBDCTablet2:SE1.1.ERICOUPONBAlA9100B9200C9300D9400A9.S1B9.S20AlO80要求用SE1.ECT语句列出如下结果:一如A的SUM(BAU为BCD的和,B的SUM(BAl)为ACD的和.且用的方法不要增加数据库负担,如用临时表等.NONSE1.1.ERCOUPONISUM(BA1.)A9900B9800C9700D9600A9.520B9.5100C9.5120D9.5120AlOOBlO80C1080D1080关于论坛上那个SQ1.微软面试题问遂，J百个账户在下j100$，某个账户某天如有支出则添加一条新记录，记录其余额。百天后，请输出包天全部账户的余额信息这个问题的难点在于j个用户在某天可能有多条纪录，也可能一条纪录也没有(不包括第一天返回的记录集是一个天oo个用户的纪录集下面是我的思跖：1 .创建表并插入测试数据：我们要求username从1-1CREATETAB1.E(db.TA1.E2(username(varchar(50)NOTNU1.1.,用户名outdate)(datetime)NOTNU1.1.z-H期cashfloatjNoTNU1.I-余额)ONPRIMARYdeclareiintseti=lwhilei<=100begininserttable2values(convert(varchar(501.g>i),2001-10l'r100)inserttable2values(convert(varchar(S0),i)f'2001-ll-l,r50)seti=i+lendinserttable2values(convert(varchar(S0),(Si)r,2001-10-l,z90)selectafromtable2orderbyoutdatefnvert(int,userame)2 .组合查询语句：a.我们必得返Wl-个从第一天起先到100大的纪录集：如:2001-101(这个日期是随意的)到2002-1-8由于笫一天是随戢一天，所以我们须要下面的SQ1.语句：selecttop100dateadd(d,convert(int,username)-l,min(outdate)asoutdatefromtable2groupbyusernameorderbyconvert(int,username)这里的奥妙在于：convert(intfusername)-l(记得我们指定用户名从1-100:-)groupbyUSemame,mln(outdate):第一天就可能每:个用户有多个纪录，返回的结果：outdate*
