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

-DBA数据库管理员JAVA程序员架构师必看面试题一（厦门）Table: （员工emp1）id name1 a2 b3 c4 dTable:( 性别 sext)id sex1 男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) tWHERE t.id = e.id)=3)ewhereid=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.sidfrom teacher t,choose_course ccwhere t.tid = cc.tid and t.tname=陈红);- select distinct s.sid,s.snamefromteacher t,choose_course cc ,student st.tid = cc.tid whereand t.tname=陈红 and s.sid = cc.sid;2.找学生小明所有的文科老师？select *from teacher where tid in(select distinct tidfrom choose_course cc,student s,course c where cc.sid = s.sidand cc.cid = c.cidand s.sname = 小明 and c.ctype = 文科);3.找出没有选修陈红老师的学生select *from student stu where stu.sid not in(select cc.sidfrom teacher t,choose_course ccwhere 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) coufrom choose_course group by tid order by cou) t1 where rownum=1) t2wheret.tid = t2.tid;- select t.* from teacher t where t.tid =(select tid from(select tid,count(distinct sid) coufrom choose_course group by tid order by cou) t1where rownum=1);面试题六（厦门）8:00-12:00 为迟到, 12:00-18:00 为早退打卡表 cardSQL 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 maxdatefrom 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,-判断迟到casewhen e1.mindate between 0800 and 1200 then yes else noend as later,-判断早退casewhen e1.maxdate between 1201 and 1800 then yes else noend as leave_early from-员工表person p,-上面那张视图表(selectp.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 pwherec.cuser = p.pid and to_char(c.ctime,yyyymmdd)a2.id and a1.name=a2.name and a1.age=a2.age and a1.sex=a2.sex面试题八（福州）用一条 SQL 语句查询出每门课都大于 80 分的学生姓名 name张三kecheng语文fenshu81张三数学75李四语文76李四数学90王五语文81王五数学100王五英语90selectDistinctnamefromTESTAWhereNotExists(Select*fromTESTWhereName=A.NameAndfenshu80select name from test where name not in(select name from test where fens hu1)面试题十（福州）有员工表 empinfo(Fempno varchar2(10) not null pk, Fempname varchar2(20) not null, Fage number not null,Fsalary number not null);假如数据量很大约 1000 万条；写一个你认为最高效的 SQL，用一个 SQL 计算以下四种人：fsalary9999 and fage 35 fsalary9999 and fage 35 fsalary 35 fsalary9999 and fage 9999 and fage 35 then 1else 0end) as fsalary9999 and fage 35, sum(case when tt.fsalary9999 and fage 9999 and fage 35,sum(case when tt.fsalary 35 then 1else 0end) as fsalary 35, sum(case when tt.fsalary9999 and fage 9999 and fage 1-DBA数据库管理员JAVA程序员架构师必看