sqlserver面試經(jīng)典講課教案

用一條SQL語句查詢出每門課都大于80分的學(xué)生姓名namekechengfenshu張三語文81張三數(shù)學(xué)75李四語文76李四數(shù)學(xué)90王五語文81王五數(shù)學(xué)100王五英語90A:selectdistinctnamefromtablewherenamenotin(selectdistinctnamefromtablewherefenshu<=80)學(xué)生表如下:自動編號學(xué)號姓名課程編號課程名稱分?jǐn)?shù)12022001張三0001數(shù)學(xué)6922022002李四0001數(shù)學(xué)8932022001張三0001數(shù)學(xué)69刪除除了自動編號不同,其他都一樣的學(xué)生冗余信息A:deletetablenamewhere自動編號notin(selectmin(自動編號)fromtablenamegroupby學(xué)號,姓名,課程編號,課程名稱,分?jǐn)?shù))一個叫department的表，里面只有一個字段name,一共有4條紀(jì)錄，分別是a,b,c,d,對應(yīng)四個球?qū)ΓF(xiàn)在四個球?qū)M(jìn)展競賽，用一條sql語句顯示全部可能的競賽組合.你先按你自己的想法做一下，看結(jié)果有我的這個簡潔嗎？答：select,fromteama,team<請用SQL語句實(shí)現(xiàn)：從TestDB數(shù)據(jù)表中查詢出全部月份的發(fā)生額都比101科目相應(yīng)月份的發(fā)生額高的科目。請留意：TestDB中有很多科目，都有1－12月份的發(fā)生額。AccID：科目代碼，Occmonth：發(fā)生額月份，DebitOccur：發(fā)生額。數(shù)據(jù)庫名：JcyAudit，數(shù)據(jù)集：Select*fromTestDB答：selecta.*fromTestDBa,(selectOccmonth,max(DebitOccur)Debit101ccurfromTestDBwhereAccID=”101”groupbyOccmonth)bwherea.Occmonth=b.Occmonthanda.DebitOccur>b.Debit101ccur************************************************************************************面試題：怎么把這樣一個表兒yearmonthamount199111.1199121.2199131.3199141.4199212.1199222.2199232.3199242.4查成這樣一個結(jié)果yearm1m2m3m419911.11.21.31.419922.12.22.32.4答案一、selectyear,(selectamountfromaaamwheremonth=1andm.year=aaa.year)asm1,(selectamountfromaaamwheremonth=2andm.year=aaa.year)asm2,(selectamountfromaaamwheremonth=3andm.year=aaa.year)asm3,(selectamountfromaaamwheremonth=4andm.year=aaa.year)asm4fromaaagroupbyyear這個是ORACLE中做的：select*from(selectname,yearb1,lead(year)over(partitionbynameorderbyyear)b2,lead(m,2)over(partitionbynameorderbyyear)b3,rank()over(partitionbynameorderbyyear)rkfromt)whererk=1;************************************************************************************精妙的SQL語句！精妙SQL語句不詳發(fā)文時間：2022.05.2910:55:05說明：復(fù)制表(只復(fù)制構(gòu)造,源表名：a新表名：b)SQL:select*intobfromawhere1<>1說明：拷貝表(拷貝數(shù)據(jù),源表名：a目標(biāo)表名：b)SQL:insertintob(a,b,c)selectd,e,ffromb;說明：顯示文章、提交人和最終回復(fù)時間SQL:selecta.title,a.username,b.adddatefromtablea,(selectmax(adddate)adddatefromtablewheretable.title=a.title)b說明：外連接查詢(1：a2：b)SQL:selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c說明：日程安排提前五分鐘提示SQL:select*from日程安排wheredatediff(”minute”,f開頭時間,getdate())>5說明：兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒有的信息SQL:deletefrominfowherenotexists(select*frominfobzwhereinfo.infid=infobz.infid)說明：--SQL:B.PREV_UPD_DATEFROMTABLE1,(SELECTX.NUM,X.UPD_DATE,Y.UPD_DATEPREV_UPD_DATEFROM(SELECTNUM,UPD_DATE,INBOUND_QTY,STOCK_ONHANDFROMTABLE2WHERETO_CHAR(UPD_DATE,”YYYY/MM”)=TO_CHAR(SYSDATE,”YYYY/MM”))X,(SELECTNUM,UPD_DATE,STOCK_ONHANDFROMTABLE2WHERETO_CHAR(UPD_DATE,”YYYY/MM”)=TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,”YYYY/MM”)|”/01”,”YYYY/MM/DD”)-1,”YYYY/MM”))Y,WHEREX.NUM=Y.NUM〔+〕ANDX.INBOUND_QTY+NVL(Y.STOCK_ONHAND,0)<>X.STOCK_ONHAND)BWHEREA.NUM=B.NUM說明：--SQL:select*fromstudentinfowherenotexists(select*fromstudentwherestudentinfo.id=student.id)and系名稱=”“&strdepartmentname&“”and專業(yè)名稱orderby性別,生源地,高考總成績說明：從數(shù)據(jù)庫中去一年的各單位費(fèi)統(tǒng)計(jì)(費(fèi)定額賀電化肥清單兩個表來源〕SQL:SELECTa.userper,a.tel,a.standfee,TO_CHAR(a.telfeedate,”yyyy”)AStelyear,SUM(decode(TO_CHAR(a.telfeedate,”mm”),”01”,a.factration))ASJAN,SUM(decode(TO_CHAR(a.telfeedate,”mm”),”02”,a.factration))ASFRI,SUM(decode(TO_CHAR(a.telfeedate,”mm”),”03”,a.factration))ASMAR,SUM(decode(TO_CHAR(a.telfeedate,”mm”),”04”,a.factration))ASAPR,SUM(decode(TO_CHAR(a.telfeedate,”mm”),”05”,a.factration))ASMAY,SUM(decode(TO_CHAR(a.telfeedate,”mm”),”06”,a.factration))ASJUE,SUM(decode(TO_CHAR(a.telfeedate,”mm”),”07”,a.factration))ASJUL,SUM(decode(TO_CHAR(a.telfeedate,”mm”),”08”,a.factration))ASAGU,SUM(decode(TO_CHAR(a.telfeedate,”mm”),”09”,a.factration))ASSEP,SUM(decode(TO_CHAR(a.telfeedate,”mm”),”10”,a.factration))ASOCT,SUM(decode(TO_CHAR(a.telfeedate,”mm”),”11”,a.factration))ASNOV,SUM(decode(TO_CHAR(a.telfeedate,”mm”),”12”,a.factration))ASDECFROM(SELECTa.userper,a.tel,a.standfee,b.telfeedate,b.factrationFROMTELFEESTANDa,TELFEEbWHEREa.tel=b.telfax)aGROUPBYa.userper,a.tel,a.standfee,TO_CHAR(a.telfeedate,”yyyy”)說明：四表聯(lián)查問題：SQL:select*fromaleftinnerjoinbona.a=b.brightinnerjoincona.a=c.cinnerjoindona.a=d.dwhere.....說明：得到表中最小的未使用的ID號SQL:SELECT(CASEWHENEXISTS(SELECT*FROMHandlebWHEREb.HandleID=1)THENMIN(HandleID)+1ELSE1END)asHandleIDFROMHandleWHERENOTHandleIDIN(SELECTa.HandleID-1FROMHandlea)*******************************************************************************有兩個表A和BkeyvalueB的key在ABvalue換為A中對應(yīng)的value這道題的SQL語句怎么寫？update b set b.value=(select a.value from a where a.key=b.key) whereb.id in(select b.id from b,a where b.key=a.key);***************************************************************************sql面試題原表:courseidcoursenamescorejava70oracle90xml40jsp30servlet80為了便于閱讀,查詢此表后的結(jié)果顯式如下(及格分?jǐn)?shù)為60):courseidcoursenamescoremarkjava70passoracle90passxml40failjsp30failservlet80pass寫出此查詢語句沒有裝ＯＲＡＣＬＥ，沒試過selectcourseid,coursename,score,decode〔sign(score-60),-1,”fail”,”pass”)asmarkfromcourse完全正確NameNull?TypeCOURSEIDNUMBERCOURSENAMEVARCHAR2(10)SCORENUMBERSQL>select*fromcourse_v;COURSEIDCOURSENAMESCOREjava70oracle90xml40jsp30servlet80SQL>selectcourseid,coursename,score,decode(sign(score-60),-1,”fail”,”pass”)asmarkfromcourse_v;COURSEIDCOURSENAMESCOREMARKjava70passoracle90passxml40failjsp30failservlet80pass*******************************************************************************原表:idproidproname11M2F1N2G1B32A查詢后的表:pro21MFNGBA寫出查詢語句解決方案sql求解aa1a21abx2y2zselect能選成以下結(jié)果嗎？1ab2xyz使用pl/sql代碼實(shí)現(xiàn)，但要求你組合后的長度不能超出oraclevarchar2長度的限制。下面是一個例子createorreplacetypestrings_tableistableofvarchar2(20);/createorreplacefunctionmerge(pvinstrings_table)returnvarchar2islsvarchar2(4000);beginforiin1..pv.countloopls:=ls||pv(i);loop;returnls;end;/createtablet(idnumber,namevarchar2(10));insertintotvalues(1,”Joan”);insertintotvalues(1,”Jack”);insertintotvalues(1,”Tom”);insertintotvalues(2,”Rose”);insertintotvalues(2,”Jenny”);columnnamesformata80;selectt0.id,merge(cast(multiset(selectnamefromtwheret.id=t0.id)asstrings_table))namesfrom(selectdistinctidfromt)t0;droptypestrings_table;dropfunctionmerge;droptablet;sql：Wellifyouhaveathoreticalmaximum,whichIwouldassumeyouwouldgiventhelegibilityoflistinghundredsofemployeesinthewayyoudescribethenyes.ButtheSQLneedstousetheLAGfunctionforeachemployee,henceahundredempsahundredLAGs,sokindofbulky.Thisexampleusesamaxof6,andwouldneedmorecutnpastingtodomorethanthat.SQL>selectdeptno,dname,empsfrom(selectd.deptno,d.dname,rtrim(e.ename||”,”||4lead(e.ename,1)over(partitionbyd.deptnoorderbye.ename)||”,”||lead(e.ename,2)over(partitionbyd.deptno7orderbye.ename)||”,”||8lead(e.ename,3)over(partitionbyd.deptno9orderbye.ename)||”,”||10lead(e.ename,4)over(partitionbyd.deptno11orderbye.ename)||”,”||12lead(e.ename,5)over(partitionbyd.deptno13orderbye.ename),”,”)emps,14row_number()over(partitionbyd.deptno15orderbye.ename)xfromempe,deptdwhered.deptno=e.deptno18)19wherex=120/DEPTNODNAMEEMPS10ACCOUNTINGCLARK,KING,MILLER20RESEARCHADAMS,FORD,JONES,ROONEY,SCOTT,SMITH30SALESALLEN,BLAKE,JAMES,MARTIN,TURNER,WARDalsocreatefunctionget_a2;createorreplacefunctionget_a2(tmp_a1number)returnvarchar2isCol_a2varchar2(4000);beginCol_a2:=””;forcurin(selecta2fromunite_awherea1=tmp_a1)loopCol_a2=Col_a2||cur.a2;endloop;returnCol_a2;endget_a2;selectdistincta1,get_a2(a1)fromunite_a1ABCEFGKMN*******************************************************************************一個SQL面試題去年應(yīng)聘一個職位未果,其間被考了一個看似簡潔的題,但我沒有找到好的大案.不知各位大蝦有無好的解法?題為:有兩個表t1,t2,Tablet1:SELLER|NON_SELLERABACADBABCBDCACBCDDADBDCTablet2:SELLER|COUPON|BALA9100B9200C9300D9400A9.5100B9.520A1080要求用SELECT語句列出如下結(jié)果 如A的SUM(BAL)為B,C,D的和,B的SUM(BAL)A,C,D的和.......且用的方法不要增加數(shù)據(jù)庫負(fù)擔(dān),如用臨時表等.NON-SELLER|COUPON|SUM(BAL)---------------A9900B9800C9700D9600A9.520B9.5100C9.5120D9.5120A100B1080C1080D1080關(guān)于論壇上那個SQL微軟面試題問題：一百個賬戶各有100$請輸出每天全部賬戶的余額信息〔不包括第一天〕100天*100個用戶的紀(jì)錄集下面是我的思路：創(chuàng)立表并插入測試數(shù)據(jù)：我們要求username從1-100CREATETABLE[dbo].[TABLE2]([username][varchar](50)NOTNULL,--用戶名[outdate][datetime]NOTNULL,--日期[cash][float]NOTNULL--余額)ON[PRIMARYdeclare@iintset@i=1while@i<=100begininserttable2values(convert(varchar(50),@i),”2022-10-1”,100)inserttable2values(convert(varchar(50),@i),”2022-11-1”,50)set@i=@i+1endinserttable2values(convert(varchar(50),@i),”2022-10-1”,90)select*fromtable2orderbyoutdate,convert(int,username)組合查詢語句：我們必需返回一個從第一天開頭到100天的紀(jì)錄集：如：2022-10-1〔這個日期是任意的〕2022-1-8由于第一天是任意一天，所以我們需要下面的SQL語句：selecttop100dateadd(d,convert(int,username)-1,min(outdate))asoutdatefromtable2groupbyusernameorderbyconvert(int,username)這里的微妙在于：convert(int,username)-1〔1-100:-))groupbyusername,min(outdate):第一天就可能每個用戶有多個紀(jì)錄。返回的結(jié)果：outdate2022-10-0100:00:00.000.........2022-01-0800:00:00.000返回一個全部用戶名的紀(jì)錄集：selectdistinctusernamefromtable2返回結(jié)果：username110100......99100100個用戶記錄集的笛卡爾集合：select*from(selecttop100dateadd(d,convert(int,username)-1,min(outdate))asoutdatefromtable2groupbyusernameorderbyconvert(int,username))asACROSSjoin(selectdistinctusernamefromtable2)asBorderbyoutdate,convert(int,username)返回結(jié)果100*100條紀(jì)錄：outdate username2022-10-0100:00:00.000 1......2022-01-0800:00:00.000 100返回當(dāng)前全部用戶在數(shù)據(jù)庫的有的紀(jì)錄：selectoutdate,username,min(cash)ascashfromtable2groupbyoutdate,usernameorderbyoutdate,convert(int,username)返回紀(jì)錄：outdateusernamecash2022-10-0100:00:00.000190......2022-01-0800:00:00.00010050c中返回的笛卡爾集和d中返回的紀(jì)錄做leftjoin:selectC.outdate,C.username,D.cashfrom(select*from(selecttop100dateadd(d,convert(int,username)-1,min(outdate))asoutdatefromtable2groupbyusernameorderbyconvert(int,username))asACROSSjoin(selectdistinctusernamefromtable2)asB)asCjoin(selectoutdate,username,min(cash)ascashfromtable2groupbyoutdate,username)asDon(C.username=D.usernameanddatediff(d,C.outdate,D.outdate)=0)orderbyC.outdate,convert(int,C.username)留意：用戶在當(dāng)天假設(shè)沒有紀(jì)錄，cash字段返回NULL，否那么cash返回每個用戶當(dāng)天的余額outdateusernamecash2022-10-0100:00:00.0001902022-10-0100:00:00.0002100......2022-10-0200:00:00.0001902022-10-0200:00:00.000......2NULL<--留意這里2022-01-0800:00:00.00010050cash為NULL，我們要返回小于當(dāng)前紀(jì)錄日期的第一個用戶余額(由于我們使用orderbycash,所以返回top1min應(yīng)當(dāng)也可以)這個余額即為當(dāng)前的余額：caseisnull(D.cash,0)when0then(selecttop1cashfromtable2wheretable2.username=C.usernameanddatediff(d,C.outdate,table2.outdate)<0orderbytable2.cash)elseD.cash最終組合的完整語句就是selectC.outdate,C.username,caseisnull(D.cash,0)when0then(selecttop1cashfromtable2wheretable2.username=C.usernameanddatediff(d,C.outdate,table2.outdate)<0orderbytable2.cash)elseD.cashendascashfrom(select*from(selecttop100dateadd(d,convert(int,username)-1,min(outdate))asoutdatefromtable2groupbyusernameorderbyconvert(int,username))asACROSSjoin(selectdistinctusernamefromtable2)asB)asCjoin(selectoutdate,username,min(cash)ascashfromtable2groupbyoutdate,username)asDon(C.username=D.usernameanddatediff(d,C.outdate,D.outdate)=0)orderbyC.outdate,convert(int,C.username)返回結(jié)果：outdateusernamecash2022-10-0100:00:00.0001902022-10-0100:00:00.0002100......2022-01-0800:00:00.00010050***********************************************************************************sql3140的記錄〔以自動增長ID為主鍵〕*從數(shù)據(jù)表中取出第n條到第m條的記錄*/declare@mintdeclare@nintdeclare@sqlvarchar(800)set@m=40set@n=31set@sql=”selecttop”+str(@m-@n+1)+”*fromidetailwhereautoidnotin(selecttop”+str(@n-1)+”autoidfromidetail)”exec(@sql)selecttop10*fromtwhereidnotin(selecttop30idfromtorderbyid)ordebyidselecttop10*fromtwhereidin(selecttop40idfromtorderbyid)orderbyiddesc*******************************************************************************一道面試題,sql語句有表a存儲二叉樹的節(jié)點(diǎn),要用一條sql語句查出全部節(jié)點(diǎn)及節(jié)點(diǎn)所在的層.ac1c2A 1 /\ABBC 2AC//\BDDNE 3CE/\\DFFKI 4EIDKCN所要得到的結(jié)果如下jdcsA1B2C2D3N3E3F4K4I4有高手指導(dǎo)一下,我只能用pl/sql寫出來,請教用一條sql語句的寫法SQL>selectc2,level+1lv2fromteststartwithc1=”A”connectbyc1=priorc25union6select”A”,1fromdual7orderbylv;C2LVA1B2C2D3E3N3F4I4K49行。以上轉(zhuǎn)自HYPERLINK“:///wenboliang/archive/2022/04/20/4095859.aspx““:///wenboliang/archive/2022/04/20/4095859.aspxSQLgroupby表內(nèi)容：2022-05-09勝2022-05-09勝2022-05-09負(fù)2022-05-09負(fù)2022-05-10勝2022-05-10負(fù)2022-05-10負(fù)假設(shè)要生成以下結(jié)果,sql語句?勝負(fù)2022-05-09222022-05-1012createtable#tmp(rqvarchar(10),shengfunchar(1))insertinto#tmpvalues(”2022-05-09”,”勝”)insertinto#tmpvalues(”2022-05-09”,”勝”)insertinto#tmpvalues(”2022-05-09”,”負(fù)”)insertinto#tmpvalues(”2022-05-09”,”負(fù)”)insertinto#tmpvalues(”2022-05-10”,”勝”)insertinto#tmpvalues(”2022-05-10”,”負(fù)”)insertinto#tmpvalues(”2022-05-10”,”負(fù)”)selectrqsum(casewhenshengfu=”勝then1else0end)”勝”,sum(casewhenshengfu=”負(fù)”then1else0end)”負(fù)”from#tmpgroupbyrqselectN.rq,N.勝,M.負(fù)from(selectrq,勝=count(*from#tmpwhereshengfu=”勝”groupbyrq)Ninnerjoin(selectrq,負(fù)=count(*)from#tmpwhereshengfu=”負(fù)”groupbyrq)MonN.rq=M.rqselecta.col001,a.a1勝,b.b1負(fù)from(selectcol001,count(col001a1fromtemp1wherecol002=”勝groupbycol001)a,(selectcol001,count(col001b1fromtemp1wherecol002=”負(fù)groupbycol001)bwherea.col001=b.col001SQL語句的查詢問題表中有ABC三列,SQL語句實(shí)現(xiàn)：當(dāng)A列大于B列時選擇A列否那么選擇B列，當(dāng)B列大于C列時選擇B列否那么選擇C列。select(casewhena>bthenaelsebend),(casewhenb>cthenbeslecend)fromtable_namesql語句？tb_send表中日期(SendTime字段)為當(dāng)天的全部記錄?(SendTime字段為datetime型，包含日期與時間)select*fromtbwheredatediff(dd,SendTime,getdate())=03個字段：語文，數(shù)學(xué)，英語。其中有370分，8058分，請用一條sql語句查詢出這三條記錄并按以下條件顯示出來〔并寫出您的思路〕：806060分表示不及格。顯示格式：語文數(shù)學(xué)英語及格優(yōu)秀不及格select(casewhen語文>=80then”優(yōu)秀”when語文>=60then”及格”else”不及格”)as語文,(casewhen數(shù)學(xué)>=80then”優(yōu)秀”when數(shù)學(xué)>=60then”及格”else”不及格”)as數(shù)學(xué),(casewhen英語>=80then”優(yōu)秀”when英語>=60then”及格”else”不及格”)as英語,fromtablesqlserver2000sqlIDIDValues,int型，并解釋下兩者的區(qū)分?用戶臨時表:createtable#xx(IDint,IDValuesint)系統(tǒng)臨時表:createtable##xx(IDint,IDValuesint)區(qū)分:用戶臨時表只對創(chuàng)立這個表的用戶的Session可見