Sql常見面試題（總結）
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 )
說明：--
SQL:
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
FROM TABLE2
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
說明：--
SQL:
select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名稱='"&strdepartmentname&"' and 專業名稱='"&strprofessionname&"' order by 性別,生源地,高考總成績
說明：
從數據庫中去一年的各單位電話費統計(電話費定額賀電化肥清單兩個表來源）
SQL:
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號
SQL:
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
---------- ---------- ----------
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
SQL> select courseid, coursename ,score ,decode(sign(score-60),-1,'fail','pass') as mark from course_v;
COURSEID COURSENAME SCORE MARK
---------- ---------- ---------- ----
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
*******************************************************************************
原表:
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
is
Col_a2 varchar2(4000);
begin
Col_a2:='';
for cur in (select a2 from unite_a where a1=tmp_a1)
loop
Col_a2=Col_a2||cur.a2;
end loop;
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
begin
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
(
select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdate
from table2
group by username
order by convert(int,username)
) as A
CROSS join
(
select distinct username from table2
) 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
order by outdate,convert(int,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
(
select * from
(
select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdate
from table2
group by username
order by convert(int,username)
) as A
CROSS join
(
select distinct username from table2
) as B
) as C
left join
(
select outdate,username,min(cash) as cash from table2
group by outdate,username
) 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返回每個用戶當天的余額
outdate username cash
2001-10-01 00:00:00.000 1 90
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 <--注意這里
......
2002-01-08 00:00:00.000 100 50
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.最后組合的完整語句就是
select C.outdate,C.username,
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
from
(
select * from
(
select top 100 dateadd(d,convert(int,username)-1,min(outdate)) as outdate
from table2
group by username
order by convert(int,username)
) as A
CROSS join
(
select distinct username from table2
) as B
) as C
left join
(
select outdate,username,min(cash) as cash from table2
group by outdate,username
) as D
on(C.username=D.username and datediff(d,C.outdate,D.outdate)=0)
order by C.outdate,convert(int,C.username)
返回結果：
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語句查出所有節點及節點所在的層.
表a
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
-- ----------
A 1
B 2
C 2
D 3
E 3
N 3
F 4
I 4
K 4
已選擇9行。