51cto下载-oracle_sql面试题及答案整理.doc

Oracle面试题及答案整理1、表：table1(FId,Fclass,Fscore),用最高效最简单的SQL列出各班成绩最高的列表，显示班级，成绩两个字段。 select fclass,max(fscore) from table1 group by fclass,fid 2、有一个表table1有两个字段FID，Fno，字都非空，写一个SQL语句列出该表中一个FID对应多个不同的Fno的纪录。 类如： 101a1001 101a1001 102a1002 102a1003 103a1004 104a1005 104a1006 105a1007 105a1007 105a1007 结果： 102a1002 102a1003 104a1005 104a1006 select t2.* from table1 t1, table1 t2 where t1.fid = t2.fid and t1.fno t2.fno; 3、有员工表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 9999 and fage 35 fsalary 9999 and fage 35 每种员工的数量； select sum(case when fsalary9999 and fage35 then 1 else 0 end),sum(case when fsalary9999 and fage35 then 1 else 0 end),sum(case when fsalary9999 and fage35 then 1 else 0 end),sum(case when fsalary9999 and fage35 then 1 else 0 end) from empinfo 4、表A字段如下 month person income 月份 人员 收入 要求用一个SQL语句（注意是一个）的处所有人（不区分人员）每个月及上月和下月的总收入 要求列表输出为 月份 当月收入 上月收入 下月收入 MONTHS PERSON INCOME---------- ---------- ----------200807 mantisXF 5000200806 mantisXF2 3500200806 mantisXF3 3000200805 mantisXF1 2000200805 mantisXF6 2200200804 mantisXF7 1800200803 8mantisXF 4000200802 9mantisXF 4200200802 10mantisXF 3300200801 11mantisXF 4600200809 11mantisXF 680011 rows selectedselect months, max(incomes), max(prev_months), max(next_months)from (select months,incomes,decode(lag(months) over(order by months),to_char(add_months(to_date(months, yyyymm), -1), yyyymm), lag(incomes) over(order by months), 0) as prev_months, decode(lead(months) over(order by months), to_char(add_months(to_date(months, yyyymm), 1), yyyymm), lead(incomes) over(order by months), 0) as next_months from (select months, sum(income) as incomes from a group by months) aa) aaagroup by months; MONTHS MAX(INCOMES) MAX(PREV_MONTHS) MAX(NEXT_MONTHS)---------- ------------ ---------------- ---------------