oracle部分面试题-DBA数据库管理员JAVA程序员架构师必看.doc

-DBA数据库管理员JAVA程序员架构师必看

面试题一（厦门）
Table: （员工emp1）
id name
1 a
2 b
3 c
4 d

Table:( 性别 sext)

id sex
1 男
4 女
5 男

找出忘记填写性别的员工（用 Oracle 的两种方式）
select id ,name from emp1 e where e.id not in(select id from sext);
select id from emp1 minus select id from sext;
select * from emp1 e where e.id <> all(select id from sext);
select e.* from emp1 e,(select id from emp1 minus select id from sext) s where e.id = s.id;
select e.id,e.name from emp1 e,sext s where e.id=s.id(+) and s.sex is null;
select * from emp1 left outer join sext on emp1.id = sext.id where sext.sex is null;
select * from emp1 e where not exists(select * from sext s where e.id
= s.id);
select * from emp1 where id not in (select emp1.id from emp1, sext where emp1.id = sext.id); select name from emp1 where id not in (select id from emp1 intersect select id from sext); SELECT
*
FROM emp1 e WHERE (SELECT COUNT(*)
FROM
(SELECT id FROM emp1 UNION ALL SELECT id FROM sext) t
WHERE t.id = e.id) <2;
面试题二（上海）
表一(AAA)
商品名称 mc 商品总量 sl
A 100
B 120
表二(BBB)
商品名称 mc 出库数量 sl
A 10
A 20
B 10
B 20
B 30
用一条 Transact-SQL 语句算出商品 A,B 目前还剩多少？
select
AAA.mc,
sl-e.sum_sl as leave from
AAA,
(select sum(sl) sum_sl,mc from BBB group by mc) e where
AAA.mc = e.mc

Oracle 教程
select AAA.mc,AAA.sl-(select sum(BBB.sl) from BBB where BBB.mc=AAA.mc)
from AAA;
面试题三（上海）
人员情况表（employee）中字段包括，员工号（ID），姓名（name），年龄（age），文化程 度（wh）：包括四种情况（本科以上，大专，高中，初中以下）,现在我要根据年龄字段查 询统计出：表中文化程度为本科以上，大专，高中，初中以下，各有多少人，占总人数多 少。结果如下：
学历 年龄 人数 百分比
本科以上 20 34 14
大专 20 33 13
高中 20 33 13
初中以下 20 100 40
本科以上 21 50 20
。。。。。。
Transact-SQL 查询语句如何写？

select wh,age,trunc(count(*)/(select count(*) from employee)*100)
from employee group by wh,age;
面试题四（上海）
1. Here's two table STUDENT and SCORE_RANK, write a SQL, list all student's name who ranks 'A'
Table STUDENT
COLUMN NAME COLUMN TYPE Comment
ID char(9) not nullable Student‟s ID
NAME Varchar(30) not nullable Student‟s Name
SCORE Int nullable Student‟s score
Table SCORE_RANK
COLUMN NAME COLUMN TYPE Comment LO_SCORE Int not nullable Low score HI_SCORE Int not nullable High score RANKChar(1) nullable rank
Select name from student,score_rank where score between lo_score and hi_score and rank=‟A;
2. Here‟s two table STUDENT and SCORES, a student who have 3 or more courses rank „A‟ is a
„GOOD LEARNER‟, write a SQL, list all „GOOD LEARNER‟ „s name.
Table STUDENT
COLUMN NAME COLUMN TYPE Comment
ID char(9) not nullable Student‟s ID
NAME Varchar(30) not nullable Student‟s Name
Table SCORE
COLUMN NAME COLUMN TYPE Comment STUDENT_ID char(9) not nullable Student‟s ID COURSE_ID Int not nullable Course ID
SCORE Int not nullable Stuend‟s score of this course

Select name from student,(Select student_id from score,rank where score between lo_score and
hi_score and rank=‟A‟ group by student_id having count(course_id)>=3) e where
id=e.student_id;

面试题五（福州）
四张表学生表 student（sid,sname)， 教师表 teacher(tid,tname)，
课程表 course(cid,cname，ctype)， 选课表 choose_course(ccid,sid,tid,cid)

insert into student values(1,'小明');
insert into student values(2,'小花');

insert into teacher values(1,'陈红');
insert into teacher values(2,'陈白');
insert into course values(1,'语文','文科');
insert into course values(2,'数学','理科');
--小明选了陈红老师的语文
insert into choose_course values(1,1,1,1);
--小明选了陈红老师的数学
insert into choose_course values(2,1,1,2);
--小花选了陈红老师的数学
insert into choose_course values(3,2,1,2);
--小明选了陈白老师的语文
insert into choose_course values(1,1,2,1);
--小花选了陈红老师的语文
insert into choose_course values(4,2,1,1);
1.查找陈红老师教的学生是那些？
select *
from student stu where stu.sid in
(select distinct cc.sid
from teacher t,choose_course cc
where t.tid = cc.tid and t.tname='陈红');

---------------------------------------------------------- select distinct s.sid,s.sname
from
teacher t,choose_course cc ,student s
t.tid = cc.tid
where
and t.tname='陈红' and s.sid = cc.sid;

2.找学生小明所有的文科老师？

select *
from teacher where tid in
(
select distinct tid
from choose_course cc,student s,course c where cc.sid = s.sid
and cc.cid = c.cid
and s.sname = '小明' and c.ctype = '文科'
);
3.找出没有选修陈红老师的学生
select *
from student stu where stu.sid not in
(select cc.sid
from teacher t,choose_course cc
where t.tid = cc.tid and t.tname='陈红');
4.教的学生最少的老师？
select t.tid,t.tname,t2.cou from teacher t, (
select tid,cou from
(select tid,count(distinct sid) cou
from choose_course group by tid order by cou) t1 where rownum=1) t2
where
t.tid = t2.tid;
---------------------------------------------- select t.* from teacher t where t.tid =
(
select tid from
(select tid,count(distinct sid) cou
from choose_course group by tid order by cou) t1
where rownum=1);

面试题六（厦门）

8:00--12:00 为迟到, 12:00--18:00 为早退

打卡表 card
SQL> create table card( cid number(20), ctime date,
cuser number(20));

人员表 person create table person( pid number(20), name varchar2(10)
)

--插入人员表的数据
insert into person values(1,'a');
insert into person values(2,'b');

--插入打卡的数据
insert into card values(1,to_date('20090719080200','yyyymmddhh24miss'),1); insert into card values(2,to_date('20090719180200','yyyymmddhh24miss'),1); insert into card values(3,to_date('20090719090200','yyyymmddhh24miss'),2); insert into card values(4,to_date('20090719170200','yyyymmddhh24miss'),2);

insert into card values(5,to_date('20090720080200','yyyymmddhh24miss'),1); insert into card values(6,to_date('20090720160200','yyyymmddhh24miss'),1); insert into card values(7,to_date('20090720070200','yyyymmddhh24miss'),2);
insert into card values(8,to_date('20090720200200','yyyymmddhh24miss'),2);
--分析： 先分组统计出每个人，每天的上班时间和下班时间 即（id,day,mindate,maxdate)
select p.pid as id, to_char(c.ctime,'yyyymmdd') as day, to_char(min(c.ctime),'hh24mi') as mindate, to_char(max(c.ctime),'hh24mi') as maxdate
from card c,person p where c.cuser = p.pid group by p.pid,to_char(c.ctime,'yyyymmdd');
--把上面的分析做成一个视图，判断上班时间是否为迟到 和 下班时间是否为早退
-- 如 果 判 断 前 10 天 的 打 卡 记 录 ， 就 改 成
to_char(c.ctime,'yyyymmdd')<=to_char(sysdate-10,'yyyymmdd')

select p.name as person_name, e1.day as work_day,
e1.mindate as AM, e1.maxdate as PM,
--判断迟到
case
when e1.mindate between '0800' and '1200' then 'yes' else 'no'
end as later,
--判断早退
case
when e1.maxdate between '1201' and '1800' then 'yes' else 'no'
end as leave_early from
--员工表
person p,
--上面那张视图表
(select
p.pid as id, to_char(c.ctime,'yyyymmdd') as day, to_char(min(c.ctime),'hh24mi') as mindate, to_char(max(c.ctime),'hh24mi') as maxdate from card c,person p
where
c.cuser = p.pid and to_char(c.ctime,'yyyymmdd')<=to_char(sysdate-1,'yyyymmdd') group by p.pid,to_char(c.ctime,'yyyymmdd')
) e1
where p.pid = e1.id;

面试题七（福州）

删除一张表重复记录（ID 是自增唯一，重复记录：其他字段都是一样）
非常经典的一道面试题（可能存在很多数据，要求性能比较高）

1 louis 20
2 louis 20
3 jimmy 30
4 louis 20
------------------------------------------------------------------
delete from aa where id not in(select min(id) from aa group by name,age);

select a1.id from a a1,
a a2
where a1.id>a2.id and a1.name=a2.name and a1.age=a2.age and a1.sex=a2.sex

面试题八（福州）

用一条 SQL 语句 查询出每门课都大于 80 分的学生姓名

name
张三 kecheng
语文 fenshu

81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90

select Distinct name from TEST A Where Not Exists(Select * from TEST Where Name

=A.Name And fenshu<=80)

select name from test
group by name having min(fenshu)>80

select name from test where name not in(select name from test where fens hu<=80)

面试题九（福州）

有一表 table1 有两个字段 FID，Fno，写一个 SQL 语句列出该表中一个 FID 对应多个不同 的 Fno 的纪录。
类如：

101 1001
101 1001
102 1002
102 1003
103 1004
104 1005
104 1006
105 1007
105 1007
105 1007
105 1008
结果：
102 1002
102 1003
104 1005
104 1006
105 1007
105 1008

select distinct fid,fno from table1 where fid in
(select fid from table1 group by fid having count(distinct fno)>1)
面试题十（福州）
有员工表 empinfo
(
Fempno varchar2(10) not null pk, Fempname varchar2(20) not null, Fage number not null,
Fsalary number not null
);
假如数据量很大约 1000 万条；写一个你认为最高效的 SQL，用一个 SQL 计算以下四种人：
fsalary>9999 and fage > 35 fsalary>9999 and fage < 35 fsalary<9999 and fage > 35 fsalary<9999 and fage < 35
每种员工的数量；
select sum(case when tt.fsalary>9999 and fage > 35 then 1
else 0
end) as "fsalary>9999 and fage > 35", sum(case when tt.fsalary>9999 and fage < 35
then 1 else 0
end) as "fsalary>9999 and fage < 35",
sum(case when tt.fsalary<9999 and fage > 35 then 1
else 0
end) as "fsalary<9999 and fage > 35", sum(case when tt.fsalary<9999 and fage < 35
then 1 else 0
end) as "fsalary>9999 and fage < 35" from empinfo tt;

面试题十一（上海）

表内容：
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要生成下列结果, 该如何写 sql 语句?
胜 负
2005-05-09 2 2
2005-05-10 1 2
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
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

面试题十二（上海）

请用一个 sql 语得出结果
从 table1,table2 中取出如 table3 所列格式数据
table1
月份 mon 部门 dep 业绩 yj
-------------------------------
一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8

-39-

二月份 04 9
三月份 03 8

table2

部门 dep 部门名称 dname
--------------------------------
01 国内业务一部
02 国内业务二部
03 国内业务三部
04 国际业务部
05 其他部门

table3 （result）

部门 dep 部门名称 一月份 二月份 三月份
--------------------------------------
01 国内业务一部 10 0 0
02 国内业务二部 10 8 0
03 国内业务三部 0 5 8
04 国际业务部 0 0 9
05 其他部门

select a.dep,a.dname
sum(case when b.mon='一月份' then b.yj else 0 end) as '一月份', sum(case when b.mon='二月份' then b.yj else 0 end) as '二月份', sum(case when b.mon='三月份' then b.yj else 0 end) as '三月份',
from table2 a left join table1 b on a.dep=b.dep

面试题十二（上海）

华为一道面试题
一个表中的 Id 有多个记录，把所有这个 id 的记录查出来，并显示共有多少条记录数。
select id, Count(*) from tb group by id having count(*)>1
-DBA数据库管理员JAVA程序员架构师必看