资源描述：
《51cto下载-oracle_sql面试题及答案整理》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。
1、Oracle面试题及答案整理1、表：table1(FId,Fclass,Fscore),用最高效最简单的SQL列出各班成绩最高的列表，显示班级，成绩两个字段。selectfclass,max(fscore)fromtable1groupbyfclass,fid2、有一个表table1有两个字段FID，Fno，字都非空，写一个SQL语句列出该表中一个FID对应多个不同的Fno的纪录。类如：101a1001101a1001102a1002102a1003103a1004104a1005104a1006105a1007105a1007105a1007结果：10
2、2a1002102a1003104a1005104a1006selectt2.*fromtable1t1,table1t2wheret1.fid=t2.fidandt1.fno<>t2.fno;3、有员工表empinfo(Fempnovarchar2(10)notnullpk,Fempnamevarchar2(20)notnull,Fagenumbernotnull,Fsalarynumbernotnull);假如数据量很大约1000万条；写一个你认为最高效的SQL，用一个SQL计算以下四种人：fsalary>9999andfage>35fsalary>
3、9999andfage<35fsalary<9999andfage>35fsalary<9999andfage<35每种员工的数量；selectsum(casewhenfsalary>9999andfage>35then1else0end),sum(casewhenfsalary>9999andfage<35then1else0end),sum(casewhenfsalary<9999andfage>35then1else0end),sum(casewhenfsalary<9999andfage<35then1else0end)fromempinfo4、
4、表A字段如下monthpersonincome月份人员收入要求用一个SQL语句（注意是一个）的处所有人（不区分人员）每个月及上月和下月的总收入要求列表输出为月份当月收入上月收入下月收入MONTHSPERSONINCOME------------------------------200807mantisXF5000200806mantisXF23500200806mantisXF33000200805mantisXF12000200805mantisXF62200200804mantisXF718002008038mantisXF40002008029m
5、antisXF420020080210mantisXF330020080111mantisXF460020080911mantisXF680011rowsselectedselectmonths,max(incomes),max(prev_months),max(next_months)from(selectmonths,incomes,decode(lag(months)over(orderbymonths),to_char(add_months(to_date(months,'yyyymm'),-1),'yyyymm'),lag(incomes)ov
6、er(orderbymonths),0)asprev_months,decode(lead(months)over(orderbymonths),to_char(add_months(to_date(months,'yyyymm'),1),'yyyymm'),lead(incomes)over(orderbymonths),0)asnext_monthsfrom(selectmonths,sum(income)asincomesfromagroupbymonths)aa)aaagroupbymonths;MONTHSMAX(INCOMES)MAX(PRE
7、V_MONTHS)MAX(NEXT_MONTHS)------------------------------------------------------200801460007500200802750046004000200803400075001800200804180040004200200805420018006500200806650042005000200807500065000200809680000Select(SelectMonthFromTableWhereMonth=To_Char(Sysdate,'mm'))月份,(Selec
8、tSum(Income)FromTableWhereMonth=To_Char(
