经典Sql面试题搜集!!ITeye - 千亿集团

经典Sql面试题搜集!!ITeye

2019年02月21日10时17分48秒 | 作者: 鸿畴 | 标签: 面试题,句子,纪录 | 浏览: 153


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
-
写出此查询句子

没有装ORACLE,没试过
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行。

注:转自CSDN:wenboliang
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表千亿集团立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1

    经典Sql面试题搜集!!ITeye

    面试题,句子,纪录
  • 2
  • 3

    oracle数据库同步技能ITeye

    数据库,数据,技能
  • 4

    mysql免安装版装备ITeye

    设置,效劳,数据库
  • 5
  • 6

    按要求写sql句子itjob

    句子,学员,课程
  • 7

    检查数据库的SQL快报

    检查,数据库,检查表
  • 8

    DB2 备份和康复huabian

    康复,备份,数据库
  • 9

    运用MySQL头条

    运用,问题,效劳
  • 10