2023年Sql常見面試題

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

name

kecheng

fenshu

張三

語文

81

張三

數(shù)學

75

李四

語文

76

李四

數(shù)學

90

王五

語文

81

王五

數(shù)學

100

王五

英語

90

A:selectdistinctnamefromtable

where

namenotin(selectdistinctnamefromtablewherefenshu<=80)

2.學生表如下:

自動編號

學號

姓名課程編號課程名稱分數(shù)

1

2005001

張三

0001

數(shù)學

69

2

2005002

李四

0001

數(shù)學

89

3

2005001

張三

0001

數(shù)學

69

刪除除了自動編號不同,其他都相同的學生冗余信息

A:deletefromtablenamewhere自動編號notin(selectmin(自動編號)fromtablenamegroupby學號,姓名,課程編號,課程名稱,分數(shù))一個叫department的表，里面只有一個字段name,一共有4條紀錄，分別是a,b,c,d,對應四個球對，現(xiàn)在四個球對進行比賽，用一條sql語句顯示所有可能的比賽組合.

你先按你自己的想法做一下，看結果有我的這個簡單嗎？答：,

fromteama,teamb

<請用SQL語句實現(xiàn)：從TestDB數(shù)據(jù)表中查詢出所有月份的發(fā)生額都比101科目相應月份的發(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)b

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

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

查成這樣一個結果

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這個是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ā)文時間：2003.05.2910:55:05

說明：復制表(只復制結構,源表名：a新表名：b)

SQL:select*intobfromawhere1<>1

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

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

說明：顯示文章、提交人和最后回復時間

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

說明：外連接查詢(表名1：a表名2：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

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

SQL:

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

說明：--

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

說明：--

SQL:

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

說明：

從數(shù)據(jù)庫中去一年的各單位電話費統(tǒng)計(電話費定額賀電化肥清單兩個表來源）

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

說明：四表聯(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)asHandleID

FROMHandle

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

這道題的SQL語句怎么寫？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);***************************************************************************高級sql面試題原表:

courseidcoursenamescore

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

1java70

2oracle90

3xml40

4jsp30

5servlet80

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

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

courseidcoursenamescoremark

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

1java70pass

2oracle90pass

3xml40fail

4jsp30fail

5servlet80pass

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

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

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

寫出查詢語句解決方案

sql求解

表a

列a1a2

記錄1a

1b

2x

2y

2z

用select能選成以下結果嗎？

1ab

2xyz

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

下面是一個例子

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*******************************************************************************一個SQL面試題去年應聘一個職位未果,其間被考了一個看似簡單的題,但我沒有找到好的大案.

不知各位大蝦有無好的解法?

題為:

有兩個表,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語句列出如下結果:------如A的SUM(BAL)為B,C,D的和,B的SUM(BAL)為A,C,D的和.......

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

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

A9900

B9800

C9700

D9600

A9.520

B9.5100

C9.5120

D9.5120

A100

B1080

C1080

D1080關于論壇上那個SQL微軟面試題問題：

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

這個問題的難點在于每個用戶在某天可能有多條紀錄，也可能一條紀錄也沒有（不包括第一天）

返回的記錄集是一個100天*100個用戶的紀錄集

下面是我的思路：

1.創(chuàng)建表并插入測試數(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.組合查詢語句：

a.我們必須返回一個從第一天開始到100天的紀錄集：

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

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

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

fromtable2

groupbyusername

orderbyconvert(int,username)

這里的奧妙在于：

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

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

返回的結果：

outdate

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

2001-10-0100:00:00.000

.........

2002-01-0800:00:00.000

b.返回一個所有用戶名的紀錄集：

selectdistinctusernamefromtable2

返回結果：

username

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

1

10

100

......

99

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

select*from

(

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

fromtable2

groupbyusername

orderbyconvert(int,username))asA

CROSSjoin

(

selectdistinctusernamefromtable2

)asB

orderbyoutdate,convert(int,username)

返回結果100*100條紀錄：

outdate

username

2001-10-0100:00:00.000

1

......

2002-01-0800:00:00.000

100

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

selectoutdate,username,min(cash)ascashfromtable2

groupbyoutdate,username

orderbyoutdate,convert(int,username)

返回紀錄：

outdate

username

cash

2001-10-0100:00:00.000

1

90

......

2002-01-0800:00:00.000

100

50

e.將c中返回的笛卡爾集和d中返回的紀錄做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)

注意：用戶在當天如果沒有紀錄，cash字段返回NULL，否則cash返回每個用戶當天的余額

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，我們要返回小于當前紀錄日期的第一個用戶余額(由于我們使用orderbycash,所以返回top1紀錄即可，使用min應該也可以)，這個余額即為當前的余額：

caseisnull(D.cash,0)

when0then

(

selecttop1cashfromtable2wheretable2.username=C.username

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

orderbytable2.cash

)

elseD.cash

endascash

g.最后組合的完整語句就是

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