1、-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 create table card( cid number
2、(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,yy
3、yymmddhh24miss),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
4、); 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(m
5、ax(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)a2.id and a1.name=a2.name and a1.age=a2.age and a1.sex=a2.sex面试题八（福州）用一条 SQL 语句 查询出每门课都大于
6、80 分的学生姓名name张三 kecheng语文 fenshu81张三 数学 75李四 语文 76李四 数学 90王五 语文 81王五 数学 100王五 英语 90select Distinct name fromTEST A Where Not Exists(Select * fromTESTWhere Name=A.NameAnd fenshu80select name from test where name not in(select name from test where fens hu1)面试题十（福州）有员工表 empinfo(Fempno varchar2(10) not
7、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 fsalary9999 and fage 35 then 1else 0end) as “fsalary9999 and fage 35“, sum(case when tt.fsalary9999 and fage 9999 and fage 35 then 1else 0end) as “fsalary 35“, sum(case when tt.fsalary9999 and fage 1-DBA 数据库管理员 JAVA 程序员架构师必看
