Sql常见面试题.docx

《Sql常见面试题.docx》由会员分享，可在线阅读，更多相关《Sql常见面试题.docx（10页珍藏版）》请在第壹文秘上搜索。

1、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.:deletefrominf

2、owherenotexists(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.ECTNU

3、M,UPD.DATE,STOCKeONHANDFROMTAB1.E2WHERETO-CHAR(UPD.DATEz,YYYYMM,)=TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,YYYYMM,)：；,01rYYYYMMDD,)-1,YYYYMM,)where.nm=y.num2oracle90pass3xml40fail4jsp30fail5servlet80pass写出此查询语句没有装ORAC1.E,没试过selectcourseidzCOUgename,score,decodedesccourse-vNameNull?TypeCourseidnumberCOURSENAM

4、EVARCHAR2(10)SCORENUMBERSQ1.select*fromCOUrSJv;CourseidcoursenamescoreIjava702oracle903xml404jsp305servlet80SQ1.selectCOUrSeid,coursename,scorerdecode(sign(score-60)lfai,pass)asmarkfromCOUrSjv;Courseidcoursenamescoremark1java70pass2oracle90pass3xml40fall4jsp30fail5servlet80pass原衣:idproidpronameIlM12

5、F21N22G31B32A瓷询后的表：idprolpro2IMF2NG3BA写出杳询语句解决方案sq求解表a列ala2记录】aIb2x2y2zfllselect能选成以下结果吗？1 ab2 xyz运用pl/sql代码实现，但要求你加合后的长度不能超出OradeVarChar2长度的限制,下面是一个例子createorreplacetypestrings_tableistableofvarchar2(20);Zcreateorreplacefunctionmerge(PVin$tring$_table)returnvarchar2i$ISVarChar2(4000);beginforiinl.p

6、v.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)as

7、strings-table)namesfrom(selectdistinctidfromt)t;droptypeStringsJabIe;dropfunctionmerge;droptablet;用sql：Wellifyouhaveathoreticalmaximum,whichIwouldassumeyouwouldgiventhelegibilityoflistinghundredsofemployeesinthewayyoudescribethenyes.ButtheSQ1.needstousethe1.AGfunctionforeachemployee,henceahundredemp

8、sahundred1.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)

9、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/DEPTNODNAMEEMPS1

10、0ACCOUNTINGC1.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|cu

11、r.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语句列出

12、如下结果:一如A的SUM(BAU为BCD的和,B的SUM(BAl)为ACD的和.且用的方法不要增加数据库负担,如用临时表等.NONSE1.1.ERCOUPONISUM(BA1.)A9900B9800C9700D9600A9.520B9.5100C9.5120D9.5120AlOOBlO80C1080D1080关于论坛上那个SQ1.微软面试题问遂，J百个账户在下j100$，某个账户某天如有支出则添加一条新记录，记录其余额。百天后，请输出包天全部账户的余额信息这个问题的难点在于j个用户在某天可能有多条纪录，也可能一条纪录也没有(不包括第一天返回的记录集是一个天oo个用户的纪录集下面是我的思跖：1

13、.创建表并插入测试数据：我们要求username从1-1CREATETAB1.E(db.TA1.E2(username(varchar(50)NOTNU1.1.,用户名outdate)(datetime)NOTNU1.1.z-H期cashfloatjNoTNU1.I-余额)ONPRIMARYdeclareiintseti=lwhileii),2001-10lr100)inserttable2values(convert(varchar(S0),i)f2001-ll-l,r50)seti=i+lendinserttable2values(convert(varchar(S0),(Si)r,200

14、1-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*