数据库面试题

1、按年龄段展现不同年龄段的平均薪资(如：20-21，平均多少，22-23，平均多少)

create table DB2ADMIN.ztest_1(age int, sal int);

insert into DB2ADMIN.ztest_1 values(20,100);
insert into DB2ADMIN.ztest_1 values(20,100);
insert into DB2ADMIN.ztest_1 values(20,100);
insert into DB2ADMIN.ztest_1 values(20,100);
insert into DB2ADMIN.ztest_1 values(20,100);

insert into DB2ADMIN.ztest_1 values(21,200);
insert into DB2ADMIN.ztest_1 values(21,200);

insert into DB2ADMIN.ztest_1 values(22,300);
insert into DB2ADMIN.ztest_1 values(22,300);
insert into DB2ADMIN.ztest_1 values(22,300);


insert into DB2ADMIN.ztest_1 values(23,50);
insert into DB2ADMIN.ztest_1 values(23,50);


select X.class,avg(sal)
from
(select (case when age>=20 and age<=21 then 'A'
when age>=22 and age<=23 then 'B'
else 'C'
end) as class,
sal
from DB2ADMIN.ztest_1 ) X
group by X.class



select (case when age>=20 and age<=21 then 'A'
when age>=22 and age<=23 then 'B'
else 'C'
end) as class,
avg(sal)
from DB2ADMIN.ztest_1
group by (case when age>=20 and age<=21 then 'A'
when age>=22 and age<=23 then 'B'
else 'C'
end)

2、参见如下表结构
出版社:
出版社代码 char(2),
出版社名称 varchar2(32)

图书:
图书编号 char(8),
图书名称 varchar2(128),
出版社代码 char(2),
作者代号 char(4),
图书简介 varchar2(128)

作者:
作者代号 char(4),
作者名称 varchar2(10),
性别 char(1),
年龄 number(3),
文学方向 varchar2(64)

获奖名单:
获奖日期 date,
获奖人员 char(4)

编写SQL语句，找出“作者”库中没有出现在“获奖名单”库中所有作者信息的SQL语句（提示：使用not in、not exists以及外关联三种方法，并说明哪种方法最优。

create table DB2ADMIN.author(aid char(4), aname char(10), sex char(1));

insert into DB2ADMIN.author values('1','john','1');

insert into DB2ADMIN.author values('2','karl','0');

insert into DB2ADMIN.author values('3','foul','0');

insert into DB2ADMIN.author values('4','jell','1');


create table DB2ADMIN.award(adate date, aid char(4));


insert into DB2ADMIN.award values('2010-09-12','1');


insert into DB2ADMIN.award values('2009-02-11','4');


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

Select * from DB2ADMIN.author where aid not in ( select aid from DB2ADMIN.award);
Select * from DB2ADMIN.author a where not exists (select 1 from DB2ADMIN.award b where b.aid=a.aid); 最高效
Select a.* from DB2ADMIN.author a left join DB2ADMIN.award b on a.aid=b.aid where b.aid is null; 高效


3、找出每组Name中最高的两个value

Name Value
A 1200
A 5100
A 3100
A 8

100
B 3000
B 4000
B 7000
B 5000


处理后为：
Name Value
A 8100
A 5100
B 7000
B 5000


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

create table DB2ADMIN.MS_ZQTX_1
(Name char(10),
Value integer
);

comment on table DB2ADMIN.MS_ZQTX_1 is '面试-深圳证券通信';

insert into DB2ADMIN.MS_ZQTX_1 values('A',1200);
insert into DB2ADMIN.MS_ZQTX_1 values('A',5100);
insert into DB2ADMIN.MS_ZQTX_1 values('A',3100);
insert into DB2ADMIN.MS_ZQTX_1 values('A',8100);
insert into DB2ADMIN.MS_ZQTX_1 values('B',3000);
insert into DB2ADMIN.MS_ZQTX_1 values('B',4000);
insert into DB2ADMIN.MS_ZQTX_1 values('B',7000);
insert into DB2ADMIN.MS_ZQTX_1 values('B',5000);

------------------------------------
(1) 用OLAP函数row_number()

select Name, Value
from
(
select Name, Value, row_number() over (partition by Name order by Name ,Value desc) as rn
from DB2ADMIN.MS_ZQTX_1
) A
where rn<=2


(2) 不用OLAP函数

with MaxT as
( select Name, max(Value) as Value
from DB2ADMIN.MS_ZQTX_1
group by Name
),
secT as
(
select Name, max(Value) as Value
from
(select Name, Value
from DB2ADMIN.MS_ZQTX_1
except
select Name, Value
from DB2ADMIN.MaxT) A
group by Name
)

select Name,Value
from
(
select Name, Value
from MaxT
union all
select Name, Value
from SecT
)B
order by Name,Value desc



4、找出每个客户最早日期的存款

create table DB2ADMIN.MS_ZQTX_2
(Name char(10),
Value integer,
CDate date
);


comment on table DB2ADMIN.MS_ZQTX_2 is '面试-深圳证券通信2';

insert into DB2ADMIN.MS_ZQTX_2 values('A',1200,'2010-05-04');
insert into DB2ADMIN.MS_ZQTX_2 values('A',5100,'2010-02-04');
insert into DB2ADMIN.MS_ZQTX_2 values('A',3100,'2010-03-04');
insert into DB2ADMIN.MS_ZQTX_2 values('A',8100,'2009-01-04');
insert into DB2ADMIN.MS_ZQTX_2 values('B',3000,'2008-01-02');
insert into DB2ADMIN.MS_ZQTX_2 values('B',4000,'2009-08-02');
insert into DB2ADMIN.MS_ZQTX_2 values('B',7000,'2010-05-02');
insert into DB2ADMIN.MS_ZQTX_2 values('B',5000,'2011-03-02');

结果为：
'A',8100,'2009-01-04'
'B',3000,'2008-01-02'


解法一：

select Name,Value
from
(
select Name,Value,CDate, min(CDate) over (partition by Name) as mdt
from DB2ADMIN.MS_ZQTX_2
)A
where A.CDate=A.mdt



解法二：

select distinct ,B.Value
from
(
select Name,Value,CDate, min(CDate) over (partition by Name) as mdt
from DB2ADMIN.MS_ZQTX_2
)A , DB2ADMIN.MS_ZQTX_2 as B
where mdt=B.CDate



5、
id url status
5 a 91
5 b 80
6 d 82
6 aa 93
6 bb 93

取出:
5 a 91
6 aa 93
6 bb 93



Create table DB2ADMIN.T (
ID INTEGER ,
URL CHARACTER(2) ,
STATUS INTEGER )
;

insert into

db2admin.t values(5,'a',91);
insert into db2admin.t values(5,'a',81);
insert into db2admin.t values(6,'d',82);
insert into db2admin.t values(6,'aa',93);
insert into db2admin.t values(6,'bb',93);


解法一：

select id,url,status from t where (id,status)=any
(select id, max(status) as maxs
from t
group by id
)

解法二：
select id,url,status from t as t1 where exists
(
select id, maxs
from
(
select id, max(status) as maxs
from t as t2
group by id
)t3
where t1.id=t3.id and t1.status=t3.maxs
)


解法三：去重 db2

取出:
5 a 91
6 aa 93

select id,url,maxs
from
(
select id,url,status, max(status) over(partition by id order by id,url) as maxs,row_number() over(partition by id) as rn
from t
)b
where rn=1