SQL SERVER 資料庫面試題

1.用一條SQL語句 查詢出每門課都大于80分的學生姓名

name   kecheng   fenshu

張三     國文       81

張三     數學       75

李四     國文       76

李四     數學       90

王五     國文       81

王五     數學       100

王五     英語       90

A: select distinct name from table where name not in (select distinct name from table where fenshu<=80)

2.學生表 如下:

自動編号   學号   姓名 課程編号 課程名稱 分數

1        2005001 張三 0001      數學    69

2        2005002 李四 0001      數學    89

3        2005001 張三 0001      數學    69

删除除了自動編号不同,其他都相同的學生備援資訊

A: delete tablename where 自動編号 not in(select min(自動編号) from tablename group by 學号,姓名,課程編号,課程名稱,分數)

一個叫department的表，裡面隻有一個字段name,一共有4條紀錄，分别是a,b,c,d,對應四個球對，現在四個球對進行比賽，用一條sql語句顯示所有可能的比賽組合.

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

答：select a.name, b.name

from team a, team b

where a.name < b.name

請用SQL語句實作：從TestDB資料表中查詢出所有月份的發生額都比101科目相應月份的發生額高的科目。請注意：TestDB中有很多科目，都有1－12月份的發生額。

AccID：科目代碼，Occmonth：發生額月份，DebitOccur：發生額。

資料庫名：JcyAudit，資料集：Select * from TestDB

答：select a.*

from TestDB a

,(select Occmonth,max(DebitOccur) Debit101ccur from TestDB where AccID='101' group by Occmonth) b

where a.Occmonth=b.Occmonth and a.DebitOccur>b.Debit101ccur

************************************************************************************

面試題：怎麼把這樣一個表兒

year month amount

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

查成這樣一個結果

year m1 m2 m3 m4

1991 1.1 1.2 1.3 1.4

1992 2.1 2.2 2.3 2.4

答案一、

select year,

(select amount from aaa m where month=1 and m.year=aaa.year) as m1,

(select amount from aaa m where month=2 and m.year=aaa.year) as m2,

(select amount from aaa m where month=3 and m.year=aaa.year) as m3,

(select amount from aaa m where month=4 and m.year=aaa.year) as m4

from aaa group by year

這個是ORACLE 中做的：

select * from (select name, year b1, lead(year) over

(partition by name order by year) b2, lead(m,2) over(partition by name order by year) b3,rank()over(

partition by name order by year) rk from t) where rk=1;

精妙的SQL語句！

精妙SQL語句

作者：不詳 發文時間：2003.05.29 10:55:05

說明：複制表(隻複制結構,源表名：a 新表名：b)

SQL: select * into b from a where 1<>1

說明：拷貝表(拷貝資料,源表名：a 目标表名：b)

SQL: insert into b(a, b, c) select d,e,f from b;

說明：顯示文章、送出人和最後回複時間

SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b

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

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

說明：日程安排提前五分鐘提醒

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

說明：兩張關聯表，删除主表中已經在副表中沒有的資訊

SQL:

delete from info where not exists ( select * from infobz where info.infid=infobz.infid )

說明：--

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

FROM TABLE1,

(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE

FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND

FROM TABLE2

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

(SELECT NUM, UPD_DATE, STOCK_ONHAND

WHERE TO_CHAR(UPD_DATE,'YYYY/MM') =

TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') ¦¦ '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y,

WHERE X.NUM = Y.NUM （+）

AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B

WHERE A.NUM = B.NUM

select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名稱='"&strdepartmentname&"' and 專業名稱='"&strprofessionname&"' order by 性别,生源地,聯考總成績

說明：

從資料庫中去一年的各機關電話費統計(電話費定額賀電化肥清單兩個表來源）

SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV,

SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC

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

FROM TELFEESTAND a, TELFEE b

WHERE a.tel = b.telfax) a

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

說明：四表聯查問題：

SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

說明：得到表中最小的未使用的ID号

SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID

FROM Handle

WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)

*******************************************************************************

有兩個表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面試題

原表:

courseid coursename score

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

1 java 70

2 oracle 90

3 xml 40

4 jsp 30

5 servlet 80

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

courseid coursename score mark

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

1 java 70 pass

2 oracle 90 pass

3 xml 40 fail

4 jsp 30 fail

5 servlet 80 pass

寫出此查詢語句

沒有裝ＯＲＡＣＬＥ，沒試過

select courseid, coursename ,score ,decode（sign(score-60),-1,'fail','pass') as mark from course

完全正确

SQL> desc course_v

Name Null? Type

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

COURSEID NUMBER

COURSENAME VARCHAR2(10)

SCORE NUMBER

SQL> select * from course_v;

COURSEID COURSENAME SCORE

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

SQL> select courseid, coursename ,score ,decode(sign(score-60),-1,'fail','pass') as mark from course_v;

COURSEID COURSENAME SCORE MARK

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

id proid proname

1 1 M

1 2 F

2 1 N

2 2 G

3 1 B

3 2 A

查詢後的表:

id pro1 pro2

1 M F

2 N G

3 B A

寫出查詢語句

解決方案

sql求解

表a

列 a1 a2

記錄 1 a

1 b

2 x

2 y

2 z

用select能選成以下結果嗎？

1 ab

2 xyz

使用pl/sql代碼實作，但要求你組合後的長度不能超出oracle varchar2長度的限制。

下面是一個例子

create or replace type strings_table is table of varchar2(20);

/

create or replace function merge (pv in strings_table) return varchar2

is

ls varchar2(4000);

begin

for i in 1..pv.count loop

ls := ls || pv(i);

end loop;

return ls;

end;

create table t (id number,name varchar2(10));

insert into t values(1,'Joan');

insert into t values(1,'Jack');

insert into t values(1,'Tom');

insert into t values(2,'Rose');

insert into t values(2,'Jenny');

column names format a80;

select t0.id,merge(cast(multiset(select name from t where t.id = t0.id) as strings_table)) names

from (select distinct id from t) t0;

drop type strings_table;

drop function merge;

drop table t;

用sql：

Well if you have a thoretical maximum, which I would assume you would given the legibility of listing hundreds of employees in the way you describe then yes. But the SQL needs to use the LAG function for each employee, hence a hundred emps a hundred LAGs, so kind of bulky.

This example uses a max of 6, and would need more cut n pasting to do more than that.

SQL> select deptno, dname, emps

2 from (

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

4 lead(e.ename,1) over (partition by d.deptno

5 order by e.ename) ||', '||

6 lead(e.ename,2) over (partition by d.deptno

7 order by e.ename) ||', '||

8 lead(e.ename,3) over (partition by d.deptno

9 order by e.ename) ||', '||

10 lead(e.ename,4) over (partition by d.deptno

11 order by e.ename) ||', '||

12 lead(e.ename,5) over (partition by d.deptno

13 order by e.ename),', ') emps,

14 row_number () over (partition by d.deptno

15 order by e.ename) x

16 from emp e, dept d

17 where d.deptno = e.deptno

18 )

19 where x = 1

20 /

DEPTNO DNAME EMPS

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

10 ACCOUNTING CLARK, KING, MILLER

20 RESEARCH ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH

30 SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD

also

先create function get_a2;

create or replace function get_a2( tmp_a1 number)

return varchar2

Col_a2 varchar2(4000);

Col_a2:='';

for cur in (select a2 from unite_a where a1=tmp_a1)

loop

Col_a2=Col_a2||cur.a2;

return Col_a2;

end get_a2;

select distinct a1 ,get_a2(a1) from unite_a

1 ABC

2 EFG

3 KMN

一個SQL 面試題

去年應聘一個職位未果,其間被考了一個看似簡單的題,但我沒有找到好的大案.

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

題為:

有兩個表, t1, t2,

Table t1:

SELLER | NON_SELLER

----- -----

A B

A C

A D

B A

B C

B D

C A

C B

C D

D A

D B

D C

Table t2:

SELLER | COUPON | BAL

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

A 9 100

B 9 200

C 9 300

D 9 400

A 9.5 100

B 9.5 20

A 10 80

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

且用的方法不要增加資料庫負擔,如用臨時表等.

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

A 9 900

B 9 800

C 9 700

D 9 600

A 9.5 20

B 9.5 100

C 9.5 120

D 9.5 120

A 10 0

B 10 80

C 10 80

D 10 80

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

問題：

一百個賬戶各有100$，某個賬戶某天如有支出則添加一條新記錄，記錄其餘額。一百天後，請輸出每天所有賬戶的餘額資訊

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

傳回的記錄集是一個100天*100個使用者的紀錄集

下面是我的思路：

1.建立表并插入測試資料：我們要求username從1-100

CREATE TABLE [dbo].[TABLE2] (

[username] [varchar] (50) NOT NULL , --使用者名

[outdate] [datetime] NOT NULL , --日期

[cash] [float] NOT NULL --餘額

) ON [PRIMARY

declare @i int

set @i=1

while @i<=100

    insert table2 values(convert(varchar(50),@i),'2001-10-1',100)

    insert table2 values(convert(varchar(50),@i),'2001-11-1',50)

    set @i=@i+1

end

insert table2 values(convert(varchar(50),@i),'2001-10-1',90)

select * from table2 order by outdate,convert(int,username)

2.組合查詢語句：

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

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

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

select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdate

from table2

group by username

order by convert(int,username)

這裡的奧妙在于：

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

group by username,min(outdate):第一天就可能每個使用者有多個紀錄。

傳回的結果：

outdate                                               

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

2001-10-01 00:00:00.000

.........

2002-01-08 00:00:00.000

b.傳回一個所有使用者名的紀錄集：

select distinct username from table2

傳回結果：

username                                         

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

1

10

100

......

99

c.傳回一個100天記錄集和100個使用者記錄集的笛卡爾集合：

select * from

(

) as A

CROSS join

) as B

order by outdate,convert(int,username)

傳回結果100*100條紀錄：

outdate                            username

2001-10-01 00:00:00.000            1

2002-01-08 00:00:00.000            100

d.傳回目前所有使用者在資料庫的有的紀錄：

select outdate,username,min(cash) as cash from table2

group by outdate,username

傳回紀錄：

outdate                            username    cash

2001-10-01 00:00:00.000            1          90

2002-01-08 00:00:00.000            100        50

e.将c中傳回的笛卡爾集和d中傳回的紀錄做left join:

select C.outdate,C.username,

D.cash

from

) as C

left join

) as D

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

order by C.outdate,convert(int,C.username)

注意：使用者在當天如果沒有紀錄，cash字段傳回NULL，否則cash傳回每個使用者當天的餘額

2001-10-01 00:00:00.000            2          100

2001-10-02 00:00:00.000            1          90

2001-10-02 00:00:00.000            2          NULL <--注意這裡

f.好了，現在我們最後要做的就是，如果cash為NULL，我們要傳回小于目前紀錄日期的第一個使用者餘額(由于我們使用order by cash,是以傳回top 1紀錄即可，使用min應該也可以)，這個餘額即為目前的餘額：

case isnull(D.cash,0)

when 0 then

select top 1 cash from table2 where table2.username=C.username

and datediff(d,C.outdate,table2.outdate)<0

order by table2.cash

)

else D.cash

end as cash

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

outdate                                 username        cash

2001-10-01 00:00:00.000    1                    90

2001-10-01 00:00:00.000    2                   100

2002-01-08 00:00:00.000    100                50

***********************************************************************************

取出sql表中第31到40的記錄（以自動增長ID為主鍵）

*從資料表中取出第n條到第m條的記錄*/

declare @m int

declare @n int

declare @sql varchar(800)

set @m=40

set @n=31

set @sql='select top '+str(@m-@n+1) + '* from idetail where autoid not in(

select top '+ str(@n-1) + 'autoid from idetail)'

exec(@sql)

select top 10 * from t where id not in (select top 30 id from t order by id ) orde by id

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

select top 10 * from t where id in (select top 40 id from t order by id) order by id desc

一道面試題,寫sql語句

有表a存儲二叉樹的節點,要用一條sql語句查出所有節點及節點所在的層.

c1 c2 A ----------1

---- ---- / \

A B B C --------2

A C / / \

B D D N E ------3

C E / \ \

D F F K I ---4

E I

D K

C N

所要得到的結果如下

jd cs

----- ----

A 1

B 2

C 2

D 3

N 3

E 3

F 4

K 4

I 4

有高手指導一下,我隻能用pl/sql寫出來,請教用一條sql語句的寫法

SQL> select c2, level + 1 lv

2 from test start

3 with c1 = 'A'

4 connect by c1 = prior c2

5 union

6 select 'A', 1 from dual

7 order by lv;

C2 LV

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

已選擇9行。

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

1.一道SQL語句面試題，關于group by

表内容：

2005-05-09 勝

2005-05-09 負

2005-05-10 勝

2005-05-10 負

如果要生成下列結果, 該如何寫sql語句?

            勝 負

2005-05-09 2 2

2005-05-10 1 2

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

create table #tmp(rq varchar(10),shengfu nchar(1))

insert into #tmp values('2005-05-09','勝')

insert into #tmp values('2005-05-09','負')

insert into #tmp values('2005-05-10','勝')

insert into #tmp values('2005-05-10','負')

1)select rq, sum(case when shengfu='勝' then 1 else 0 end)'勝',sum(case when shengfu='負' then 1 else 0 end)'負' from #tmp group by rq

2) select N.rq,N.勝,M.負 from (

select rq,勝=count(*) from #tmp where shengfu='勝'group by rq)N inner join

(select rq,負=count(*) from #tmp where shengfu='負'group by rq)M on N.rq=M.rq

3)select a.col001,a.a1 勝,b.b1 負 from

(select col001,count(col001) a1 from temp1 where col002='勝' group by col001) a,

(select col001,count(col001) b1 from temp1 where col002='負' group by col001) b

where a.col001=b.col001

2.請教一個面試中遇到的SQL語句的查詢問題

表中有A B C三列,用SQL語句實作：當A列大于B列時選擇A列否則選擇B列，當B列大于C列時選擇B列否則選擇C列。

select (case when a>b then a else b end ),

(case when b>c then b esle c end)

from table_name

3.面試題：一個日期判斷的sql語句？

請取出tb_send表中日期(SendTime字段)為當天的所有記錄?(SendTime字段為datetime型，包含日期與時間)

select * from tb where datediff(dd,SendTime,getdate())=0

4.有一張表，裡面有3個字段：國文，數學，英語。其中有3條記錄分别表示國文70分，數學80分，英語58分，請用一條sql語句查詢出這三條記錄并按以下條件顯示出來（并寫出您的思路）：

   大于或等于80表示優秀，大于或等于60表示及格，小于60分表示不及格。

       顯示格式：

       國文              數學                英語

       及格              優秀                不及格   

select

(case when 國文>=80 then '優秀'

        when 國文>=60 then '及格'

else '不及格') as 國文,

(case when 數學>=80 then '優秀'

        when 數學>=60 then '及格'

else '不及格') as 數學,

(case when 英語>=80 then '優秀'

        when 英語>=60 then '及格'

else '不及格') as 英語,

from table

5.在sqlserver2000中請用sql建立一張使用者臨時表和系統臨時表，裡面包含兩個字段ID和IDValues,類型都是int型，并解釋下兩者的差別?

使用者臨時表:create table #xx(ID int, IDValues int)

系統臨時表:create table ##xx(ID int, IDValues int)

差別:

使用者臨時表隻對建立這個表的使用者的Session可見,對其他程序是不可見的.

當建立它的程序消失時這個臨時表就自動删除.

全局臨時表對整個SQL Server執行個體都可見,但是所有通路它的Session都消失的時候,它也自動删除.

6.sqlserver2000是一種大型資料庫，他的存儲容量隻受存儲媒體的限制，請問它是通過什麼方式實作這種無限容量機制的。

它的所有資料都存儲在資料檔案中(*.dbf),是以隻要檔案夠大,SQL    Server的存儲容量是可以擴大的.

SQL Server 2000 資料庫有三種類型的檔案：

主要資料檔案

主要資料檔案是資料庫的起點，指向資料庫中檔案的其它部分。每個資料庫都有一個主要資料檔案。主要資料檔案的推薦檔案擴充名是 .mdf。

次要資料檔案

次要資料檔案包含除主要資料檔案外的所有資料檔案。有些資料庫可能沒有次要資料檔案，而有些資料庫則有多個次要資料檔案。次要資料檔案的推薦檔案擴充名是 .ndf。

日志檔案

日志檔案包含恢複資料庫所需的所有日志資訊。每個資料庫必須至少有一個日志檔案，但可以不止一個。日志檔案的推薦檔案擴充名是 .ldf。

7.請用一個sql語句得出結果

從table1,table2中取出如table3所列格式資料，注意提供的資料及結果不準确，隻是作為一個格式向大家請教。

如使用存儲過程也可以。

table1

月份mon 部門dep 業績yj

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

一月份      01      10

一月份      02      10

一月份      03      5

二月份      02      8

二月份      04      9

三月份      03      8

table2

部門dep      部門名稱dname

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

      01      國内業務一部

      02      國内業務二部

      03      國内業務三部

      04      國際業務部

table3 （result）

部門dep 一月份      二月份      三月份

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

      01      10        null      null

      02      10         8        null

      03      null       5        8

      04      null      null      9

1)

select a.部門名稱dname,b.業績yj as '一月份',c.業績yj as '二月份',d.業績yj as '三月份'

from table1 a,table2 b,table2 c,table2 d

where a.部門dep = b.部門dep and b.月份mon = '一月份' and

a.部門dep = c.部門dep and c.月份mon = '二月份' and

a.部門dep = d.部門dep and d.月份mon = '三月份' and

2)

select a.dep,

sum(case when b.mon=1 then b.yj else 0 end) as '一月份',

sum(case when b.mon=2 then b.yj else 0 end) as '二月份',

sum(case when b.mon=3 then b.yj else 0 end) as '三月份',

sum(case when b.mon=4 then b.yj else 0 end) as '四月份',

sum(case when b.mon=5 then b.yj else 0 end) as '五月份',

sum(case when b.mon=6 then b.yj else 0 end) as '六月份',

sum(case when b.mon=7 then b.yj else 0 end) as '七月份',

sum(case when b.mon=8 then b.yj else 0 end) as '八月份',

sum(case when b.mon=9 then b.yj else 0 end) as '九月份',

sum(case when b.mon=10 then b.yj else 0 end) as '十月份',

sum(case when b.mon=11 then b.yj else 0 end) as '十一月份',

sum(case when b.mon=12 then b.yj else 0 end) as '十二月份',

from table2 a left join table1 b on a.dep=b.dep

8.華為一道面試題

一個表中的Id有多個記錄，把所有這個id的記錄查出來，并顯示共有多少條記錄數。

select id, Count（*) from tb group by id having count(*)>1

select * from(select count(ID) as count from table group by ID)T where T.count>1