求助 --- oracle面试题目 解析！

Question

1、表：table1(FId,Fclass,Fscore),用最高效最简单的SQL列出各班成绩最高的列表，显示班级，成绩两个字段。2、有一个表table1有两个字段FID，Fno，字都非空，写一个SQL语句列出该表中一个FID对应多个不同的Fno的纪录。
类如：
101a1001
101a1001
102a1002
102a1003
103a1004
104a1005
104a1006
105a1007
105a1007
105a1007
结果：
102a1002
102a1003
104a1005
104a10063、有员工表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
每种员工的数量；4、表A字段如下
month person income
月份 人员 收入
要求用一个SQL语句（注意是一个）的处所有人（不区分人员）每个月及上月和下月的总收入
要求列表输出为
月份 当月收入 上月收入 下月收入
5，表B
C1 c2
2005-01-01 1
2005-01-01 3
2005-01-02 5要求的处数据
2005-01-01 4
2005-01-02 5
合计 9
试用一个Sql语句完成。
6，数据库1，2，3 范式的概念与理解。7，简述oracle行触发器的变化表限制表的概念和使用限制，行触发器里面对这两个表有什么限制。8、oracle临时表有几种。
临时表和普通表的主要区别有哪些，使用临时表的主要原因是什么？9，怎么实现：使一个会话里面执行的多个过程函数或触发器里面都可以访问的全局变量的效果，并且要实现会话间隔离？10，aa，bb表都有20个字段，且记录数量都很大，aa，bb表的X字段（非空）上有索引，
请用SQL列出aa表里面存在的X在bb表不存在的X的值，请写出认为最快的语句，并解译原因。11，简述SGA主要组成结构和用途？12什么是分区表？简述范围分区和列表分区的区别，分区表的主要优势有哪些？13，背景：某数据运行在archivelog，且用rman作过全备份和数据库的冷备份，
且所有的归档日志都有，现控制文件全部损坏，其他文件全部完好，请问该怎么恢复该数据库，说一两种方法。14，用rman写一个备份语句：备份表空间TSB，level 为2的增量备份。15，有个表a(x number(20),y number(20))用最快速高效的SQL向该表插入从1开始的连续的1000万记录。

Answer 1

5，表B
C1 c2
2005-01-01 1
2005-01-01 3
2005-01-02 5解答如下：
select c1,sum(*) from B group by c1;

Answer 2

1: select fclass,
          max(fscore)
     from table1
    group by fclass;2: 
SQL> select t2.*
 2     from table1 t1, table1 t2
 3    where t1.fid = t2.fid
 4      and t1.fno <> t2.fno;FID        FNO
---------- ----------
102a       1002
102a       1003
104a       1005
104a       1006SQL> 3: 
select sum(case when fsalary > 9999 and fage > 35
                then 1
                else 0
                 end) as "fsalary>9999_fage>35",
       sum(case when fsalary > 9999 and fage < 35
                then 1
                else 0
                 end) as "fsalary>9999_fage<35",
       sum(case when fsalary < 9999 and fage > 35
                then 1
                else 0
                 end) as "fsalary<9999_fage>35",
       sum(case when fsalary < 9999 and fage < 35
                then 1
                else 0
                 end) as "fsalary<9999_fage<35"
  from empinfo;4:SQL> select * from a;MONTHS     PERSON         INCOME
---------- ---------- ----------
200807     mantisXF         5000
200806     mantisXF2        3500
200806     mantisXF3        3000
200805     mantisXF1        2000
200805     mantisXF6        2200
200804     mantisXF7        1800
200803     8mantisXF        4000
200802     9mantisXF        4200
200802     10mantisXF       3300
200801     11mantisXF       4600
200809     11mantisXF       680011 rows selectedSQL> 
SQL> select months, max(incomes), max(prev_months), max(next_months)
  2    from (select months,
  3                 incomes,
  4                 decode(lag(months) over(order by months),
  5                        to_char(add_months(to_date(months, 'yyyymm'), -1),
  6                                'yyyymm'),
  7                        lag(incomes) over(order by months),
  8                        0) as prev_months,
  9                 decode(lead(months) over(order by months),
 10                        to_char(add_months(to_date(months, 'yyyymm'), 1),
 11                                'yyyymm'),
 12                        lead(incomes) over(order by months),
 13                        0) as next_months
 14            from (select months, sum(income) as incomes from a group by months) aa) aaa
 15   group by months;MONTHS     MAX(INCOMES) MAX(PREV_MONTHS) MAX(NEXT_MONTHS)
---------- ------------ ---------------- ----------------
200801             4600                0             7500
200802             7500             4600             4000
200803             4000             7500             1800
200804             1800             4000             4200
200805             4200             1800             6500
200806             6500             4200             5000
200807             5000             6500                0
200809             6800                0                08 rows selectedSQL> 5:SQL> select decode(grouping(c1),1,'合计',c1),
  2         sum(c2)
  3    from B
  4   group by rollup(c1);DECODE(GROUPING(C1),1,'合计',C    SUM(C2)
------------------------------ ----------
01-1月 -05                              4
02-1月 -05                              5
合计                                         9不行了,要睡觉了... Zzzzzzz!~~~
,.,.,.,.

Answer 3

15. insert into a(x,y)
select rownum,rownum from all_objects where rownum<10000000;
commit;

Answer 4

题目出得不错,比较务实,赞!!!!
1.SELECT FCLASS, MAX(FSCORE) FROM TABLE1 GROUP BY FCLASS;
2.SELECT FID FROM table1 GROUP BY FID HAVING(COUNT(DISTINCT fno))>1;
3.select sum(case when fsalary > 9999 and fage > 35
                then 1
                else 0
                 end) a,
       sum(case when fsalary > 9999 and fage < 35
                then 1
                else 0
                 end) b,
       sum(case when fsalary < 9999 and fage > 35
                then 1
                else 0
                 end) c,
       sum(case when fsalary < 9999 and fage < 35
                then 1
                else 0
                 end) d
  from empinfo;
4.CREATE TABLE a (MONTH INT, person VARCHAR2(20), income INT);
insert into a values(1,'a',100);
insert into a values(2,'a',110);
insert into a values(3,'a',120);
insert into a values(4,'a',130);
insert into a values(1,'b',110);
insert into a values(2,'b',130);
insert into a values(1,'c',130);
insert into a values(2,'c',150);
insert into a values(2,'d',150);
SELECT * FROM a;
SELECT MONTH,
       SS current_month,
       LAG(SS) OVER(ORDER BY MONTH) last_month,
       lead(SS) OVER(ORDER BY MONTH) next_month
  FROM (SELECT MONTH, SUM(INCOME) SS FROM A GROUP BY MONTH ORDER BY 1);
输出:
MONTH CURRENT_MONTH LAST_MONTH NEXT_MONTH
1 340   540
2 540 340 120
3 120 540 130
4 130 120 5.DROP TABLE b;
CREATE TABLE b (c1 DATE,c2 INT);
INSERT INTO b VALUES(trunc(SYSDATE),1);
INSERT INTO b VALUES(trunc(SYSDATE),3);
INSERT INTO b VALUES(trunc(SYSDATE+1),5);
COMMIT;
SELECT *FROM b;
SELECT DECODE(GROUPING(C1), 1, '合计', TO_CHAR(C1, 'yyyy-mm-dd')), SUM(C2)
  FROM B
 GROUP BY ROLLUP((C1, NULL));
6.baidu,google
7.不能自我参照
8.事务和session临时表.commit后删不删数据的区别.
9.package中定义变量,应该可以.(待确定)
10.select x FROM aa MINUS  SELECT x FROM bb;
11.baidu,google
12.baidu,google
13.create CONTROLFILE ....;ALTER DATABASE OPEN RESETLOGS;
14.run{
   backup incremental level 2 format '/bak/oradata/tsuser_%u_%s_%p' tablespace TSB;
   }
15. 
CREATE TABLE a(x NUMBER(20),y NUMBER(20));
DECLARE
  TYPE NTAB IS TABLE OF NUMBER(20) INDEX BY PLS_INTEGER;
  V_T NTAB;
BEGIN
  FOR J IN 1 .. 10000000 LOOP
    V_T(J) := J;
  END LOOP;
  FORALL J IN 1 .. 10000000
    INSERT INTO A VALUES (V_T(J), V_T(J));
  COMMIT;
END;
/

Answer 5

题目出得不错,比较务实,赞!!!!
1.SELECT FCLASS, MAX(FSCORE) FROM TABLE1 GROUP BY FCLASS;
2.SELECT FID FROM table1 GROUP BY FID HAVING(COUNT(DISTINCT fno))>1;
3.select sum(case when fsalary > 9999 and fage > 35
                then 1
                else 0
                 end) a,
       sum(case when fsalary > 9999 and fage < 35
                then 1
                else 0
                 end) b,
       sum(case when fsalary < 9999 and fage > 35
                then 1
                else 0
                 end) c,
       sum(case when fsalary < 9999 and fage < 35
                then 1
                else 0
                 end) d
  from empinfo;
4.CREATE TABLE a (MONTH INT, person VARCHAR2(20), income INT);
insert into a values(1,'a',100);
insert into a values(2,'a',110);
insert into a values(3,'a',120);
insert into a values(4,'a',130);
insert into a values(1,'b',110);
insert into a values(2,'b',130);
insert into a values(1,'c',130);
insert into a values(2,'c',150);
insert into a values(2,'d',150);
SELECT * FROM a;
SELECT MONTH,
       SS current_month,
       LAG(SS) OVER(ORDER BY MONTH) last_month,
       lead(SS) OVER(ORDER BY MONTH) next_month
  FROM (SELECT MONTH, SUM(INCOME) SS FROM A GROUP BY MONTH ORDER BY 1);
输出:
MONTH CURRENT_MONTH LAST_MONTH NEXT_MONTH
1 340   540
2 540 340 120
3 120 540 130
4 130 120 5.DROP TABLE b;
CREATE TABLE b (c1 DATE,c2 INT);
INSERT INTO b VALUES(trunc(SYSDATE),1);
INSERT INTO b VALUES(trunc(SYSDATE),3);
INSERT INTO b VALUES(trunc(SYSDATE+1),5);
COMMIT;
SELECT *FROM b;
SELECT DECODE(GROUPING(C1), 1, '合计', TO_CHAR(C1, 'yyyy-mm-dd')), SUM(C2)
  FROM B
 GROUP BY ROLLUP((C1, NULL));
6.baidu,google
7.不能自我参照
8.事务和session临时表.commit后删不删数据的区别.
9.package中定义变量,应该可以.(待确定)
10.select x FROM aa MINUS  SELECT x FROM bb;
11.baidu,google
12.baidu,google
13.create CONTROLFILE ....;ALTER DATABASE OPEN RESETLOGS;
14.run{
   backup incremental level 2 format '/bak/oradata/tsuser_%u_%s_%p' tablespace TSB;
   }
15. 
CREATE TABLE a(x NUMBER(20),y NUMBER(20));
DECLARE
  TYPE NTAB IS TABLE OF NUMBER(20) INDEX BY PLS_INTEGER;
  V_T NTAB;
BEGIN
  FOR J IN 1 .. 10000000 LOOP
    V_T(J) := J;
  END LOOP;
  FORALL J IN 1 .. 10000000
    INSERT INTO A VALUES (V_T(J), V_T(J));
  COMMIT;
END;
/