SQL:某公司dba面试题记录

注：本文来自互联网，收集只为学习之用，权作笔记，请注重个人知识成果，回答是我个人的

面试题一：

table1(FId,Fclass,Fscore),用最高效最简单的 SQL 列出各班成绩最高的列表，显示班级，成绩两个字段。

我的回答：

SELECT Fclass,MAX(Fscore) FROM table1 GROUP BY Fclass;

面试题二：

有一个表 table1 有两个字段 FID，Fno，字都非空，写一个 SQL 语句列出该表中一个FID对应多个不同的 Fno的纪录。
类如：

101a 1001
101a 1001
102a 1002
102a 1003
103a 1004
104a 1005
104a 1006
105a 1007
105a 1007
105a 1007
结果：
102a 1002
102a 1003
104a 1005
104a 1006

我的回答：

DROP TABLE table1
CREATE TABLE table1(FId NUMBER,Fclass VARCHAR2(20),Fscore VARCHAR2(20));
INSERT INTO  table1 VALUES(1,'A',90);
INSERT INTO  table1 VALUES(2,'B',90);
INSERT INTO  table1 VALUES(3,'A',30);
INSERT INTO  table1 VALUES(4,'C',70);
INSERT INTO  table1 VALUES(5,'D',10);
SELECT Fclass,Fscore FROM table1 WHERE Fscore=(SELECT MAX(FSCORE) FROM table1); 
SELECT * FROM 
  (SELECT Fclass,DENSE_RANK() OVER(ORDER BY Fscore DESC) AS a,Fscore
FROM table1) t
WHERE a=1 

 

面试题三：

有一张员工表 

empinfo ( empno varchar2(10) not null primary key,

empname varchar2(20) not null,

age number not null,

salary number not null);

假如数据量很大约 1000 万条；写一个你认为最高效的 SQL，用一个 SQL 计算以下四种人：

salary>9999 and age > 35 salary>9999 and age < 35 salary<9999 and age > 35 salary<9999 and age < 35 每种员工的数量

//case when 优点就是只扫描表一次
SELECT
SUM(CASE WHEN salary>9999 AND age > 35 THEN 1 ELSE 0 END) class1,
SUM(CASE WHEN salary>9999 AND age < 35 THEN 1 ELSE 0 END) class2,
SUM(CASE WHEN salary<9999 AND age > 35 THEN 1 ELSE 0 END) class3,
SUM(CASE WHEN salary<9999 AND age < 35 THEN 1 ELSE 0 END) class4
FROM empinfo

面试题四：

表A字段如下：

月份  人员   收入

month person income  

要求用一个SQL语句（注意是一个）的处所有人（不区分人员）每个月及上月和下月的总收入 要求列表输出为 月份   当月收入    上月收入  下月收入

我的回答：

TRUNCATE TABLE INCOME
CREATE TABLE income(MONTH VARCHAR2(20),employee VARCHAR2(20),income NUMBER)

INSERT INTO income VALUES('2012-01','A',100);
INSERT INTO income VALUES('2012-01','B',150);
INSERT INTO income VALUES('2012-01','C',550);
INSERT INTO income VALUES('2012-02','A',100);
INSERT INTO income VALUES('2012-02','B',150);
INSERT INTO income VALUES('2012-02','C',150);
INSERT INTO income VALUES('2012-03','A',100);
INSERT INTO income VALUES('2012-03','B',400);
INSERT INTO income VALUES('2012-03','C',300);
INSERT INTO income VALUES('2012-04','A',100);
INSERT INTO income VALUES('2012-04','B',400);
INSERT INTO income VALUES('2012-04','C',300);
SELECT MONTH,SUM(INCOME) CUR_INCOME,SUM(A) LAST_INCOME,SUM(B)NETX_INCOME FROM(
       SELECT MONTH,INCOME,
              LAG(INCOME,3)OVER(ORDER BY MONTH) A, 
              LEAD(INCOME,3)OVER(ORDER BY MONTH) B FROM INCOME)K
GROUP BY ROLLUP(K.MONTH)

 

面试题五：

表B

C1                    C2
2005-01-01       1
2005-01-01       3
2005-01-01       4
2005-01-02       5
2005-01-02       5
合计             9
试用一个Sql语句完成。

我的回答：

SELECT k,Grouping(k),SUM(i) FROM t2  GROUP BY  GROUPING SETS(k,ROLLUP(k,i));

面试题六：

数据库1，2，3 范式的概念与理解

1、第一范式（1NF）：一个关系模式R的所有属性都是不可分的基本数据项。
2、第二范式（2NF）：满足第一范式，然后消除部分依赖。
3、第三范式（3NF）： 满足第二范式，消除传递依赖。

面试题七：

简述oracle行触发器的变化表限制表的概念和使用限制，行触发器里面对这两个表有什么限制。

触发器主体中的SQL语句不允许进行:
1,读取或修改触发语句的任何变化表.这些表也包括触发表自己
2,读取或修改触发表的限制表中的主键,唯一值列或外键列.但如果需要的话,可以修改其他的列

面试题八：

oracle临时表有几种。 临时表和普通表的主要区别有哪些，使用临时表的主要原因是什么？

on delete on transaction

面试题九：

怎么实现：使一个会话里面执行的多个过程函数或触发器里面都可以访问的全局变量的效果，并且要实现会话间隔离?

可以使用plsql cflags

面试题十：

aa，bb表都有 20个字段，且记录数量都很大，aa，bb表的X字段（非空）上有索引， 请用SQL列出aa表里面存在的 X在bb表不存在的 X的值，
请写出认为最快的语句，并解译原因.

数据量都很大，走索引扫描，nestlops

SELECT x FROM aa NOT EXISTS (SELECT x FROM bb WHERE aa.x=bb.x);

面试题十一：

简述SGA主要组成结构和用途？

fixed area，shared pool，redo buffer ，buffer cache，large pool，streaming pool
fixed area  oracle安装或者编译时候需要，一般不会占用太大的空间
shared pool 游标和SQL/PLSQL 变量等，数据字典
redo buffer redo日志缓冲区，写时候定刷3秒一次，当然还有其他触发规则刷到磁盘，比方切换日志
buffer cache 数据缓存 LRU策略，通常按照数据块大小 分8k 16k等 分配单位granule 4m或者16m等固定大小
large pool 导入导出的缓冲区或者备份时候，一般UGA也在里面
streaming pool  oracle stream 逻辑复制产品，后面被ogg取代，不用太关注这个

面试题十二：

什么是分区表？简述范围分区和列表分区的区别，分区表的主要优势有哪些？

顾名思义，分区表就是按照表内某个列进行分区的表，优势提高查询效率，分散IO提高读写性能，提高维护性比方分区交换，分区索引维护等

面试题十三：

背景：某数据运行在 archivelog，且用rman 作过全备份和数据库的冷备份， 且所有的归档日志都有，现控制文件全部损坏，其他文件全部完好，请问该怎么恢复该数据库，说一两种方法。

先恢复控制文件，可以手动，然后用命令行去恢复，看rman全库本身是否开启了控制文件自动备份，进行完全恢复，然后还是需要restlogs 方式打开：

startup nomount；
set dbid=;
restore controlfile from autobackup;
alter database mount;

restore database;
recover database;
alter database open resetlogs;

# 加分项
shutdown immediate;
startup mount;
backup database; 

面试题十四：

用 rman写一个备份语句：备份表空间 TSB，level 为2的增量备份。

我的回答：

backup incremental level 2 format=‘/backup/%d_%s.dbf’ tablespace TSB;

面试题十五：

有个表a(x number(20),y number(20))用最快速高效的 SQL向该表插入从 1 开始的连续的1000 万记录。

# 分区，或者多个表空间数据分多个磁盘
create table a(x varchar2(10) not null,y varchar2(10)) paritition by hash(x) nologging
;
# 索引默认全局，本地索引对分区表比较好维护
drop index idx_a;
# nologging需要表开nologging
# 并行考虑CPU核数
insert /*+ append no_logging parallel(a,4) */ into a values(x,y) \
select 1 from dual connect by level<10000001;
create index idx_a on a(x,y);