2023年Sql常見面試題

Sql常見面試題（總結(jié)）1.用一條SQL語(yǔ)句查詢出每門課都大于80分的學(xué)生姓名

name

kecheng

fenshu

張三

語(yǔ)文

81

張三

數(shù)學(xué)

75

李四

語(yǔ)文

76

李四

數(shù)學(xué)

90

王五

語(yǔ)文

81

王五

數(shù)學(xué)

100

王五

英語(yǔ)

90

A:selectdistinctnamefromtable

where

namenotin(selectdistinctnamefromtablewherefenshu<=80)

2.學(xué)生表如下:

自動(dòng)編號(hào)

學(xué)號(hào)

姓名課程編號(hào)課程名稱分?jǐn)?shù)

1

2005001

張三

0001

數(shù)學(xué)

69

2

2005002

李四

0001

數(shù)學(xué)

89

3

2005001

張三

0001

數(shù)學(xué)

69

刪除除了自動(dòng)編號(hào)不同,其他都相同的學(xué)生冗余信息

A:deletefromtablenamewhere自動(dòng)編號(hào)notin(selectmin(自動(dòng)編號(hào))fromtablenamegroupby學(xué)號(hào),姓名,課程編號(hào),課程名稱,分?jǐn)?shù))一個(gè)叫department的表，里面只有一個(gè)字段name,一共有4條紀(jì)錄，分別是a,b,c,d,對(duì)應(yīng)四個(gè)球?qū)?，現(xiàn)在四個(gè)球?qū)M(jìn)行比賽，用一條sql語(yǔ)句顯示所有可能的比賽組合.

你先按你自己的想法做一下，看結(jié)果有我的這個(gè)簡(jiǎn)單嗎？答：,

fromteama,teamb

<請(qǐng)用SQL語(yǔ)句實(shí)現(xiàn)：從TestDB數(shù)據(jù)表中查詢出所有月份的發(fā)生額都比101科目相應(yīng)月份的發(fā)生額高的科目。請(qǐng)注意：TestDB中有很多科目，都有1－12月份的發(fā)生額。

AccID：科目代碼，Occmonth：發(fā)生額月份，DebitOccur：發(fā)生額。

數(shù)據(jù)庫(kù)名：JcyAudit，數(shù)據(jù)集：Select*fromTestDB答：selecta.*fromTestDBa

,(selectOccmonth,max(DebitOccur)Debit101ccurfromTestDBwhereAccID='101'groupbyOccmonth)b

wherea.Occmonth=b.Occmonthanda.DebitOccur>b.Debit101ccur************************************************************************************面試題：怎么把這樣一個(gè)表兒

year

monthamount

1991

1

1.1

1991

2

1.2

1991

3

1.3

1991

4

1.4

1992

1

2.1

1992

2

2.2

1992

3

2.3

1992

4

2.4

查成這樣一個(gè)結(jié)果

yearm1

m2

m3

m4

1991.4

1992.4

答案一、

selectyear,

(selectamountfrom

aaamwheremonth=1

andm.year=aaa.year)asm1,

(selectamountfrom

aaamwheremonth=2

andm.year=aaa.year)asm2,

(selectamountfrom

aaamwheremonth=3

andm.year=aaa.year)asm3,

(selectamountfrom

aaamwheremonth=4

andm.year=aaa.year)asm4

fromaaa

groupbyyear這個(gè)是ORACLE

中做的：

select*from(selectname,yearb1,lead(year)over

(partitionbynameorderbyyear)b2,lead(m,2)over(partitionbynameorderbyyear)b3,rank()over(

partitionbynameorderbyyear)rkfromt)whererk=1;************************************************************************************精妙的SQL語(yǔ)句！

精妙SQL語(yǔ)句

作者：不詳發(fā)文時(shí)間：2003.05.2910:55:05

說(shuō)明：復(fù)制表(只復(fù)制結(jié)構(gòu),源表名：a新表名：b)

SQL:select*intobfromawhere1<>1

說(shuō)明：拷貝表(拷貝數(shù)據(jù),源表名：a目標(biāo)表名：b)

SQL:insertintob(a,b,c)selectd,e,ffromb;

說(shuō)明：顯示文章、提交人和最后回復(fù)時(shí)間

SQL:selecta.title,a.username,b.adddatefromtablea,(selectmax(adddate)adddatefromtablewheretable.title=a.title)b

說(shuō)明：外連接查詢(表名1：a表名2：b)

SQL:selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONa.a=b.c

說(shuō)明：日程安排提前五分鐘提醒

SQL:select*from日程安排wheredatediff('minute',f開始時(shí)間,getdate())>5

說(shuō)明：兩張關(guān)聯(lián)表，刪除主表中已經(jīng)在副表中沒有的信息

SQL:

deletefrominfowherenotexists(select*frominfobzwhereinfo.infid=infobz.infid)

說(shuō)明：--

SQL:

SELECTA.NUM,A.NAME,B.UPD_DATE,B.PREV_UPD_DATE

FROMTABLE1,

(SELECTX.NUM,X.UPD_DATE,Y.UPD_DATEPREV_UPD_DATE

FROM(SELECTNUM,UPD_DATE,INBOUND_QTY,STOCK_ONHAND

FROMTABLE2

WHERETO_CHAR(UPD_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM'))X,

(SELECTNUM,UPD_DATE,STOCK_ONHAND

FROMTABLE2

WHERETO_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)B

WHEREA.NUM=B.NUM

說(shuō)明：--

SQL:

select*fromstudentinfowherenotexists(select*fromstudentwherestudentinfo.id=student.id)and系名稱='"&strdepartmentname&"'and專業(yè)名稱='"&strprofessionname&"'orderby性別,生源地,高考總成績(jī)

說(shuō)明：

從數(shù)據(jù)庫(kù)中去一年的各單位電話費(fèi)統(tǒng)計(jì)(電話費(fèi)定額賀電化肥清單兩個(gè)表來(lá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))ASDEC

FROM(SELECTa.userper,a.tel,a.standfee,b.telfeedate,b.factration

FROMTELFEESTANDa,TELFEEb

WHEREa.tel=b.telfax)a

GROUPBYa.userper,a.tel,a.standfee,TO_CHAR(a.telfeedate,'yyyy')

說(shuō)明：四表聯(lián)查問題：

SQL:select*fromaleftinnerjoinbona.a=b.brightinnerjoincona.a=c.cinnerjoindona.a=d.dwhere.....

說(shuō)明：得到表中最小的未使用的ID號(hào)

SQL:

SELECT(CASEWHENEXISTS(SELECT*FROMHandlebWHEREb.HandleID=1)THENMIN(HandleID)+1ELSE1END)asHandleID

FROMHandle

WHERENOTHandleIDIN(SELECTa.HandleID-1FROMHandlea)*******************************************************************************有兩個(gè)表A和B，均有key和value兩個(gè)字段，如果B的key在A中也有，就把B的value換為A中對(duì)應(yīng)的value

這道題的SQL語(yǔ)句怎么寫？update

b

set

b.value=(select

a.value

from

a

where

a.key=b.key)

where

b.id

in(select

b.id

from

b,a

where

b.key=a.key);***************************************************************************高級(jí)sql面試題原表:

courseidcoursenamescore

-------------------------------------

1java70

2oracle90

3xml40

4jsp30

5servlet80

-------------------------------------

為了便于閱讀,查詢此表后的結(jié)果顯式如下(及格分?jǐn)?shù)為60):

courseidcoursenamescoremark

---------------------------------------------------

1java70pass

2oracle90pass

3xml40fail

4jsp30fail

5servlet80pass

---------------------------------------------------

寫出此查詢語(yǔ)句沒有裝ＯＲＡＣＬＥ，沒試過

selectcourseid,coursename,score,decode（sign(score-60),-1,'fail','pass')asmarkfromcourse完全正確

SQL>desccourse_v

NameNull?Type

-----------------------------------------------------------------------------

COURSEIDNUMBER

COURSENAMEVARCHAR2(10)

SCORENUMBER

SQL>select*fromcourse_v;

COURSEIDCOURSENAMESCORE

------------------------------

1java70

2oracle90

3xml40

4jsp30

5servlet80

SQL>selectcourseid,coursename,score,decode(sign(score-60),-1,'fail','pass')asmarkfromcourse_v;

COURSEIDCOURSENAMESCOREMARK

----------------------------------

1java70pass

2oracle90pass

3xml40fail

4jsp30fail

5servlet80pass*******************************************************************************原表:

idproidproname

11M

12F

21N

22G

31B

32A

查詢后的表:

idpro1pro2

1MF

2NG

3BA

寫出查詢語(yǔ)句解決方案

sql求解

表a

列a1a2

記錄1a

1b

2x

2y

2z

用select能選成以下結(jié)果嗎？

1ab

2xyz

使用pl/sql代碼實(shí)現(xiàn)，但要求你組合后的長(zhǎng)度不能超出oraclevarchar2長(zhǎng)度的限制。

下面是一個(gè)例子

createorreplacetypestrings_tableistableofvarchar2(20);

/

createorreplacefunctionmerge(pvinstrings_table)returnvarchar2

is

lsvarchar2(4000);

begin

foriin1..pv.countloop

ls:=ls||pv(i);

endloop;

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))names

from(selectdistinctidfromt)t0;

droptypestrings_table;

dropfunctionmerge;

droptablet;

用sql：

Wellifyouhaveathoreticalmaximum,whichIwouldassumeyouwouldgiventhelegibilityoflistinghundredsofemployeesinthewayyoudescribethenyes.ButtheSQLneedstousetheLAGfunctionforeachemployee,henceahundredempsahundredLAGs,sokindofbulky.

Thisexampleusesamaxof6,andwouldneedmorecutnpastingtodomorethanthat.

SQL>selectdeptno,dname,emps

2from(

3selectd.deptno,d.dname,rtrim(e.ename||','||

4lead(e.ename,1)over(partitionbyd.deptno

5orderbye.ename)||','||

6lead(e.ename,2)over(partitionbyd.deptno

7orderbye.ename)||','||

8lead(e.ename,3)over(partitionbyd.deptno

9orderbye.ename)||','||

10lead(e.ename,4)over(partitionbyd.deptno

11orderbye.ename)||','||

12lead(e.ename,5)over(partitionbyd.deptno

13orderbye.ename),',')emps,

14row_number()over(partitionbyd.deptno

15orderbye.ename)x

16fromempe,deptd

17whered.deptno=e.deptno

18)

19wherex=1

20/

DEPTNODNAMEEMPS

------------------------------------------------------------

10ACCOUNTINGCLARK,KING,MILLER

20RESEARCHADAMS,FORD,JONES,ROONEY,SCOTT,SMITH

30SALESALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

also

先createfunctionget_a2;

createorreplacefunctionget_a2(tmp_a1number)

returnvarchar2

is

Col_a2varchar2(4000);

begin

Col_a2:='';

forcurin(selecta2fromunite_awherea1=tmp_a1)

loop

Col_a2=Col_a2||cur.a2;

endloop;

returnCol_a2;

endget_a2;

selectdistincta1,get_a2(a1)fromunite_a

1ABC

2EFG

3KMN*******************************************************************************一個(gè)SQL面試題去年應(yīng)聘一個(gè)職位未果,其間被考了一個(gè)看似簡(jiǎn)單的題,但我沒有找到好的大案.

不知各位大蝦有無(wú)好的解法?

題為:

有兩個(gè)表,t1,t2,

Tablet1:

SELLER|NON_SELLER

----------

AB

AC

AD

BA

BC

BD

CA

CB

CD

DA

DB

DC

Tablet2:

SELLER|COUPON|BAL

-----------------------

A9100

B9200

C9300

D9400

A9.5100

B9.520

A1080

要求用SELECT語(yǔ)句列出如下結(jié)果:------如A的SUM(BAL)為B,C,D的和,B的SUM(BAL)為A,C,D的和.......

且用的方法不要增加數(shù)據(jù)庫(kù)負(fù)擔(dān),如用臨時(shí)表等.

NON-SELLER|COUPON|SUM(BAL)---------------

A9900

B9800

C9700

D9600

A9.520

B9.5100

C9.5120

D9.5120

A100

B1080

C1080

D1080關(guān)于論壇上那個(gè)SQL微軟面試題問題：

一百個(gè)賬戶各有100$，某個(gè)賬戶某天如有支出則添加一條新記錄，記錄其余額。一百天后，請(qǐng)輸出每天所有賬戶的余額信息

這個(gè)問題的難點(diǎn)在于每個(gè)用戶在某天可能有多條紀(jì)錄，也可能一條紀(jì)錄也沒有（不包括第一天）

返回的記錄集是一個(gè)100天*100個(gè)用戶的紀(jì)錄集

下面是我的思路：

1.創(chuàng)建表并插入測(cè)試數(shù)據(jù)：我們要求username從1-100

CREATETABLE[dbo].[TABLE2](

[username][varchar](50)NOTNULL,--用戶名

[outdate][datetime]NOTNULL,--日期

[cash][float]NOTNULL--余額

)ON[PRIMARY

declare@iint

set@i=1

while@i<=100

begin

inserttable2values(convert(varchar(50),@i),'2001-10-1',100)

inserttable2values(convert(varchar(50),@i),'2001-11-1',50)

set@i=@i+1

end

inserttable2values(convert(varchar(50),@i),'2001-10-1',90)

select*fromtable2orderbyoutdate,convert(int,username)

2.組合查詢語(yǔ)句：

a.我們必須返回一個(gè)從第一天開始到100天的紀(jì)錄集：

如：2001-10-1（這個(gè)日期是任意的）到2002-1-8

由于第一天是任意一天，所以我們需要下面的SQL語(yǔ)句：

selecttop100dateadd(d,convert(int,username)-1,min(outdate))asoutdate

fromtable2

groupbyusername

orderbyconvert(int,username)

這里的奧妙在于：

convert(int,username)-1（記得我們指定用戶名從1-100:-))

groupbyusername,min(outdate):第一天就可能每個(gè)用戶有多個(gè)紀(jì)錄。

返回的結(jié)果：

outdate

------------------------------------------------------

2001-10-0100:00:00.000

.........

2002-01-0800:00:00.000

b.返回一個(gè)所有用戶名的紀(jì)錄集：

selectdistinctusernamefromtable2

返回結(jié)果：

username

--------------------------------------------------

1

10

100

......

99

c.返回一個(gè)100天記錄集和100個(gè)用戶記錄集的笛卡爾集合：

select*from

(

selecttop100dateadd(d,convert(int,username)-1,min(outdate))asoutdate

fromtable2

groupbyusername

orderbyconvert(int,username))asA

CROSSjoin

(

selectdistinctusernamefromtable2

)asB

orderbyoutdate,convert(int,username)

返回結(jié)果100*100條紀(jì)錄：

outdate

username

2001-10-0100:00:00.000

1

......

2002-01-0800:00:00.000

100

d.返回當(dāng)前所有用戶在數(shù)據(jù)庫(kù)的有的紀(jì)錄：

selectoutdate,username,min(cash)ascashfromtable2

groupbyoutdate,username

orderbyoutdate,convert(int,username)

返回紀(jì)錄：

outdate

username

cash

2001-10-0100:00:00.000

1

90

......

2002-01-0800:00:00.000

100

50

e.將c中返回的笛卡爾集和d中返回的紀(jì)錄做leftjoin:

selectC.outdate,C.username,

D.cash

from

(

select*from

(

selecttop100dateadd(d,convert(int,username)-1,min(outdate))asoutdate

fromtable2

groupbyusername

orderbyconvert(int,username)

)asA

CROSSjoin

(

selectdistinctusernamefromtable2

)asB

)asC

leftjoin

(

selectoutdate,username,min(cash)ascashfromtable2

groupbyoutdate,username

)asD

on(C.username=D.usernameanddatediff(d,C.outdate,D.outdate)=0)

orderbyC.outdate,convert(int,C.username)

注意：用戶在當(dāng)天如果沒有紀(jì)錄，cash字段返回NULL，否則cash返回每個(gè)用戶當(dāng)天的余額

outdate

username

cash

2001-10-0100:00:00.000

1

90

2001-10-0100:00:00.000

2

100

......

2001-10-0200:00:00.000

1

902001-10-0200:00:00.000

2

NULL

<--注意這里

......

2002-01-0800:00:00.000

100

50

f.好了，現(xiàn)在我們最后要做的就是，如果cash為NULL，我們要返回小于當(dāng)前紀(jì)錄日期的第一個(gè)用戶余額(由于我們使用orderbycash,所以返回top1紀(jì)錄即可，使用min應(yīng)該也可以)，這個(gè)余額即為當(dāng)前的余額：

caseisnull(D.cash,0)

when0then

(

selecttop1cashfromtable2wheretable2.username=C.username

anddatediff(d,C.outdate,table2.outdate)<0

orderbytable2.cash

)

elseD.cash

endascash

g.最后組合的完整語(yǔ)句就是

selectC.outdate,C.username,

caseisnull(D.cash,0)

when0then

(

selecttop1cashfromtable2wheretable2.username=C.username

anddatediff(d,C.outdate,table2.outdate)<0

orderbytable2.cash

)

elseD.cash

endascash

from

(

select*from

(

selecttop100dateadd(d,convert(int,username)-1,min(outdate))asoutdate

fromtable2

groupbyusername

orde