部分常见ORACLE面试题以及SQL注意事项
时间：2022-03-14 00:18
部分常见ORACLE面试题以及SQL注意事项
一、表的创建：
一个通过单列外键联系起父表和子表的简单例子如下：
CREATE TABLE parent(id INT NOT NULL,
PRIMARY KEY (id)
)
CREATE TABLE child(id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
)
建表时注意不要用关键字当表名或字段名，如insert,use等。
CREATE TABLE parent(id INT NOT NULL,
PRIMARY KEY (id)
) TYPE=INNODB;
InnoDB Tables 概述
InnoDB给MySQL提供了具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
InnoDB 提供了行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs)。这些特性均提高了多用户并发操作的性能表现。在InnoDB表中不需要扩大锁定(lock escalation)，
因为 InnoDB 的列锁定(row level locks)适宜非常小的空间。
InnoDB 是 MySQL 上第一个提供外键约束(FOREIGN KEY constraints)的表引擎。
InnoDB 的设计目标是处理大容量数据库系统，它的CPU利用率是其它基于磁盘的关系数据库引擎所不能比的。
从一个表中查询出数据插入到另一个表中的方法：
select * into destTbl from srcTbl ;
insert into destTbl(fld1, fld2) select fld1, 5 from srcTbl ;以上两句都是将 srcTbl 的数据插入到 destTbl，但两句又有区别的。
第一句（select into from）要求目标表（destTbl）不存在，因为在插入时会自动创建。
第二句（insert into select from）要求目标表（destTbl）存在，由于目标表已经存在，所以我们除了插入源表（srcTbl）的字段外，还可以插入常量，如例中的：5。
如果只想要结构而不要数据。
create table s_emp_42 as select * from s_emp where 1=2;//永假式
SQL查询练习题
1.
表1：book表，字段有id(主键)，name (书名)；
表2：bookEnrol表（图书借出归还登记），字段有id，bookId（外键），dependDate（变更时间），state（1.借出 2.归还）。
id name
1 English
2 Math
3 JAVA
id bookId dependDate state
1 1 2009-01-02 1
2 1 2009-01-12 2
3 2 2009-01-14 1
4 1 2009-01-17 1
5 2 2009-02-14 2
6 2 2009-02-15 1
7 3 2009-02-18 1
8 3 2009-02-19 2
要求查询结果应为：(被借出的书和被借出的日期)
Id Name dependDate
1 English 2009-01-17
2 Math 2009-02-15
Select e.bookId,b.name,e.dependDate from book b,bookEnrol e where
第二个表是用来登记的，不管你是借还是还，都要添加一条记录。
请写一个SQL语句，获取到现在状态为已借出的所有图书的相关信息。
参考语句：
select book.id,book.name,max(dependDate)
from book inner join bookEnrol on book.id=bookEnrol.bookid AND booker.state=1
group by book.id ;
2
第(1)题练习使用group by /having 子句。类似的笔试题还有：
表一：各种产品年销售量统计表 sale
年 产品 销量
2005 a 700
2005 b 550
2005 c 600
2006 a 340
2006 b 500
2007 a 220
2007 b 350
要求得到的结果应为：
年 产品 销量
2005 a 700
2006 b 500
2007 b 350
即：每年销量最多的产品的相关信息。
参考答案：
Select * from sale a where not exists(select * from sale where 年=a.年 and 销量>a.销量);
--or:
select * from sale a inner join (select 年,max(销量) as 销量from sale group by 年) b
on a.年=b.年 and a.销量=b.销量
3.查询语句排名问题：
名次 姓名 月积分(char) 总积分（char）
1 WhatIsJava 1 99
2 水王 76 981
3 新浪网 65 96
4 牛人 22 9
5 中国队 64 89
6 北林信息 66 66
7 加太阳 53 66
8 中成药 11 33
9 西洋参 25 26
10 大拿 33 23
如果用总积分做降序排序..因为总积分是字符型，所以排出来是这样子(9,8,7,6,5...)，要求按照总积分的数字大小排序。
select * from tablename order by cast(总积分 as int) desc
表tb
uid mark
1 7
1 6
2 3
2 2
2 5
3 4
3 3
4 8
4 1
4 3
想查出uid=4的名次：
uid mc
4 3
select uid, sum(mark) as total from tab_name group by uid order by total desc;
4
表A字段如下
month name income
月份 人员 收入
1 a 1000
2 a 2000
3 a 3000
要求用一个SQL语句（注意是一个）的处所有人（不区分人员）每个月及上月和下月的总收入
要求列表输出为
月份 当月收入 上月收入 下月收入
2 2000 1000 3000
Select (Select Month From Table Where Month = To_Char(Sysdate, ‘mm‘)) 月份,
(Select Sum(Income) From Table Where Month = To_Char(Sysdate, ‘mm‘)) 当月收入,
(Select Sum(Income) From Table Where To_Number(Month) = To_Number(Extract(Month From Sysdate)) - 1) 上月收入,
(Select Sum(Income) From Table Where To_Number(Month) = To_Number(Extract(Month From Sysdate)) + 1) 下月收入
From Dual
5.删除重复记录
方法原理：
1、Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，
rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。
2、在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中
那些具有最大rowid的就可以了，其余全部删除。
实现方法：
SQL> create table a (
2 bm char(4), --编码
3 mc varchar2(20) --名称
4 )
5 /
SQL> select rowid,bm,mc from a;
ROWID BM MC
------------------ ---- -------
000000D5.0000.0002 1111 1111
000000D5.0001.0002 1112 1111
000000D5.0002.0002 1113 1111
000000D5.0003.0002 1114 1111
000000D5.0004.0002 1111 1111
000000D5.0005.0002 1112 1111
000000D5.0006.0002 1113 1111
000000D5.0007.0002 1114 1111
查询到8记录.
查出重复记录
SQL> select rowid,bm,mc from a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);
ROWID BM MC
------------------ ---- --------------------
000000D5.0000.0002 1111 1111
000000D5.0001.0002 1112 1111
000000D5.0002.0002 1113 1111
000000D5.0003.0002 1114 1111
删除重复记录
SQL> delete from a a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);
删除4个记录.
SQL> select rowid,bm,mc from a;
ROWID BM MC
------------------ ---- --------------------
000000D5.0004.0002 1111 1111
000000D5.0005.0002 1112 1111
000000D5.0006.0002 1113 1111
000000D5.0007.0002 1114 1111
其他组合函数
Group by 子句
Distinct 关键字
伪列ROWNUM，用于为子查询返回的每个行分配序列值注意：组函数可以处理一组数据，返回一个值。组函数会忽略空值。where 后只能跟单行函数，不能有组函数。
使用TOP-N分析法
TOP-N分析法基于条件显示表中最上面N条记录或最下面N条记录
TOP-N查询包含以下内容：
1，一个用于排序数据的内联视图
2，使用ORDER BY子句或DESC参数的子查询
3，一个外层查询。由它决定最终记录中行的数目。这包括ROWNUM伪列和用于比较运算符的WHERE子句
//语法：
SELECT ROWNUM,column_list
FROM (SELECT column_list FROM table_name ORDER BY Top-n-column_name)
WHERE ROWNUM <= N
例1：查询Employee表的顶部10条记录
//方法1：单表时可以用
select cEmployeeCode,vFirstName,vLastName from employee where rownum <= 10
//方法2：较复杂的查询，建议使用这种
select * from (select rownum as num,cEmployeeCode,vFirstName,vLastName from employee)
where num <= 10
例2: 查询Employee表的 第1 到 第10条 记录，可以用于分页显示
//注意:因为这里子查询的rownum需要被外层查询所使用，因此要使用别名，否则将被认为是两个不同的rownum
select * from (select rownum as num,Employee.* from Employee) where num between 10 and 20
select * from (select rownum as num,Employee.* from Employee) where num between 1 and 10
SQL注入 1=1永远成立,相当于查询所有记录
select * from person_zdk where 1=1 or name like ‘%a%‘ and age=13;
DEcode函数
是ORACLE PL/SQL是功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数，其他数据库厂商的SQL实现还没有此功能。
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
该函数的含义如下:
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
......
ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF
假设我们想给智星职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％，用DEcode函数，那么我们就可以把这些流控制语句省略，通过SQL语句就可以直接完成。如下：select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary from employee.
SQL中的单记录函数
1.CONCAT
连接两个字符串;
SQL> select concat(‘010-‘,‘88888888‘)||‘转23‘ 高乾竞电话 from dual;高乾竞电话
----------------
010-88888888转23
2.LTRIM和RTRIM
LTRIM 删除左边出现的字符串
RTRIM 删除右边出现的字符串
SQL> select ltrim(rtrim(‘ gao qian jing ‘,‘ ‘),‘ ‘) from dual;
LTRIM(RTRIM(‘
-------------
gao qian jing
3..SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL> select substr(‘13088888888‘,3,8) from dual;
SUBSTR(‘
--------
08888888
4日期函数
如:LAST_DAY 返回本月日期的最后一天
具体参见oracle笔记.
其他主要函数：.TRUNC 按照指定的精度截取一个数；SQRT 返回数字n的根；POWER（n1,n2）返回n1的n2次方根；MOD(n1,n2) 返回一个n1除以n2的余数；FLOOR 对给定的数字取整数；REPLACE(‘string‘,‘s1‘,‘s2‘) string 希望被替换的字符或变量 s1 被替换的字符串 s2 要替换的字符串；LOWER 返回字符串,并将所有的字符小写；UPPER返回字符串,并将所有的字符大写；LENGTH
返回字符串的长度。
ORALCE常识 及 SQL 基本语法
1，ORACLE安装完成后的初始口令？
internal/oracle
sys/change_on_install
system/manager
scott/tiger scott是Oracle的核心开发人员之一，tiger是他家的一只猫的名字
sysman/oem_temp
例：conn ;
conn as sysdba;
2，IBM的Codd （Edgar Frank Codd）博士提出《大型共享数据库数据的关系模型》
3,ORACLE 9i 中的 i （internet）是因特网的意思
4,ORACLE的数据库的物理结构：数据文件、日志文件、控制文件
5,ORACLE的数据库的逻辑结构：表空间——表——段——区间——块
表空间 类似于SQLSERVER中数据库的概念
6,SYSDATE 返回当前系统日期（说明：当函数没有参数时可以省略括号）
7,在SQL PLUS中 执行缓冲区中的SQL命令的方式：
SQL> run
SQL> r
SQL> /
8,在SQL PLUS中 修改当前会话的日期显示格式
SQL> alter session set nls_date_format = ‘YYYY-MM-DD‘
9,使用临时变量，提高输入效率
SQL> insert into emp(empno,ename,sal) values(&employeeno,‘&employeename‘,&employeesal);
10,从其他表中复制数据并写入表
SQL> insert into managers(id,name,salary,hiredate)
SQL> select empno,ename,sal,hiredate
SQL> from emp
SQL> where job = ‘MANAGER‘;
11,修改表中的记录
SQL> update table set column = value [,column = value,……] [where condition];
12,删除表中的记录
SQL> delete [from] table [where condition];
13,数据库事务，事务是数据库一组逻辑操作的集合
一个事务可能是：
多个DML语句
单个DDL语句
单个DCL语句
14,事务控制使用 savepoint,rollback,commit 关键字
SQL> savepoint aaa;
SQL> rollback to aaa;
SQL> commit;
15,查询表中的数据
select * from table_name;
select column_list from table_name;
16,Number and Date 可以用于算术运算
因为 Date 类型 其实存储为 Number 类型
17,用运算表达式产生新列
SQL> select ename,sal,sal+3000 from emp;
SQL> select ename,sal,12*sal+100 from emp;
18,算术表达式中NULL值错误的处理
因为任何数与NULL运算无意义，所以为避免错误，需要用其他值替换NULL值
例如：
SQL> select ename "姓名",12*sal+comm "年薪" from emp where ename = ‘KING‘;
姓名 薪水
---------- ----------
KING
因为comm（提成工资）列为NULL值，结果也出现了NULL值，所以需要用0来替换NULL
注意函数nvl的使用 NVL(原值,新值)
SQL> select ename "姓名",12*sal+NVL(comm,0) "年薪" from emp where ename = ‘KING‘;
员工姓名 员工薪水
---------- ----------
KING 60000
——————————————
19,使用友好的列名,有下面三种形式
SQL> select ename as 姓名, sal 月薪, sal*12 "年薪" from emp
20,过滤重复行，使用关键字 distinct
SQL> select distinct * from emp;
21,SQL PLUS访问ORACLE数据库的原理
SQL*Plus —> Buffer —> Server —> Query Result
22,where 子句中 字符型 是区分大小写的，最好都转成大写
因为在ORACLE库中，字符会转换成大写来保存
23,比较运算符：等于"=",不等于有两种"<>"或者"!="
24,复杂的比较运算符：
between …… and ……
in (……value list……)
like （% 代表匹配至多个任意字符，_ 代表单个任意字符）
null （与NULL进行比较时，需要使用 is null 或者 is not null）
25,逻辑运算符，按优先级从高到低排列
Not , And , Or
26,Order by 子句 中 （ asc 表示 升序，desc 表示降序）
27,ORACLE 函数，分为
单行函数：每条记录返回一个结果值
多行函数：多条记录返回一个结果值
28,字符函数——转换函数
LOWER：转为小写
UPPER：转为大写
INITCAP：将每个单词的首字母大写，其他字母小写
29,字符函数——操纵函数（注意：ORACLE以UNIcode存储字符）
CONCAT:连接两个字符串，与并置运算符“||”类似
SUBSTR:substr(string,position,length) 从string中的position开始取length个字符
LENGTH:返回字符串的长度
INSTR: instr(string,value) 返回 value 在 string 的起始位置
LPAD: lpad(string,number,value) 若string不够number位，从左起用vlaue字符串填充(不支持中文)
30,四舍五入函数 round(数值,小数位)
SQL> SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1) FROM DUAL;
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1)
--------------- --------------- ----------------
45.92 46 50
31,数值截取函数 trunct
SQL> SELECT TRUNC(45.923,2),TRUNC(45.923,0),TRUNC(45.923,-1) FROM DUAL;
TRUNC(45.923,2) TRUNC(45.923,0) TRUNC(45.923,-1)
--------------- --------------- ----------------
45.92 45 40
32,求模函数 MOD(a,b) 返回a被b整除后的余数
33,Oracle内部默认的日期格式: DD-MON-YY （24-9月 -06）
34,DUAL ：哑元系统表，是名义表，只能范围唯一值
35,Date类型的算术运算，以天为单位
例如：部门编号为10的员工分别工作了多少年
SQL> select ename,(sysdate-hiredate)/365 as years from emp where deptno = 10;
ENAME YEARS
---------- ----------
CLARK 25.3108341
KING 24.8697382
MILLER 24.6861766
36,日期函数
MONTHS_BETWEEN 返回两个日期之间相差多少个月
ADD_MONTHS 在日期上加上月份数
NEXT_DAY 下一个日子 select next_day(sysdate,‘星期一‘) from dual;
LAST_DAY 该月的最后一天
ROUND 四舍五入日期 round(sysdate,‘year‘) 或者 round(sysdate,‘month‘)
TRUNC 截取日期 trunc(sysdate,‘year‘) 或者 trunc(sysdate,‘month‘)
37,数据类型转换 —— Oracle 可隐式转换的情况有：
From To
varchar2 or char —— number （当字符串是数字字符时）
varchar2 or char —— date
number —— varchar2
date —— varchar2
38,数据类型转换 —— Oracle 数据类型转换函数
to_char
to_number
to_date
39,日期格式模型字符
YYYY 代表完整的年份
YEAR 年份
MM 两位数的月份
MONTH 月份的完整名称
DY 每星期中天的三个字符缩写
DAY 表示星期日——星期六
另外还有 D,DD,DDD 等。。。
40,NVL(value,substitute)
value:是可能有null的列，substitute是缺省值
这个函数的作用就是当出现null值的时候，后缺省值替换null
41,Coalesce(exp_name1,exp_name2……exp_n)
42,Decode 函数： Decode(exp,testvalue1,resultvalue1,testvalue2,resultvalue2)
例如，根据国家名称显示相应的国家代码:
1>创建国家表
create table countrys
(
vCountryName varchar2(50)
);
2>写入几行，分别为中国、日本、韩国
insert into countrys values (‘&name‘);
3>用DEcode函数，进行匹配和显示
select vCountryName as "国家名称",
DEcode(vCountryName,‘中国‘,‘086‘,‘日本‘,‘116‘) as "国家编号" from countrys;
国家名称 国家编号
-------------------------------------------------- ---
中国 086
日本 116
韩国
结果，在DEcode中存在且成功匹配的值将会被显示，否则显示为NULL
SQL语句书可以提高执行效率的方法
1、操作符号： NOT IN操作符
此操作是强列推荐不使用的，因为它不能应用表的索引。推荐方案：用NOT EXISTS 或（外连接+判断为空）方案代替 "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", "LIKE ‘%500‘"，因为他们不走索引全是表扫描。NOT IN会多次扫描表，使用EXISTS、NOT EXISTS、IN、LEFT OUTER JOIN来替代，特别是左连接,而Exists比IN更快，最慢的是NOT操作。
2、注意union和union all的区别。union比union all多做了一步distinct操作。能用union all的情况下尽量不用union。
如: 两个表A和B都有一个序号字段ID,要求两个表中的ID字段最大的值：
select max(id) as max_id
from(
select id from 表A
union all
select id from 表B ) t
3、查询时尽量不要返回不需要的行、列。另外在多表连接查询时，尽量改成连接查询，少用子查询。
4、尽量少用视图，它的效率低。对视图操作比直接对表操作慢,可以用存储过程来代替它。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。
我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时，不要使用指向多个表的视图，
直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销,查询受到干扰.为了加快视图的查询，MsSQL增加了视图索引的功能。
5、创建合理的索引，对于插入或者修改比较频繁的表，尽量慎用索引。因为如果表中存在索引，插入和修改时也会引起全表扫描。
索引一般使用于where后经常用作条件的字段上。
6、在表中定义字段或者存储过程、函数中定义参数时，将参数的大小设置为合适即可，勿设置太大。这样开销很大。
7、Between在某些时候比IN速度更快,Between能够更快地根据索引找到范围。用查询优化器可见到差别。
select * from chineseresume where title in (‘男‘,‘女‘)
Select * from chineseresume where between ‘男‘ and ‘女‘是一样的。由于in会在比较多次，所以有时会慢些。
8、在必要是对全局或者局部临时表创建索引，有时能够提高速度，但不是一定会这样，因为索引也耗费大量的资源。他的创建同是实际表一样。
9、WHERE后面的条件顺序影响
WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响，如
Select * from zl_yhjbqk where dy_dj = ‘1KV以下‘ and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = ‘1KV以下‘
以上两个SQL中dy_dj（电压等级）及xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，如果dy_dj = ‘1KV以下‘条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的 比较，而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。所以尽量 将范围小的条件放在前面。。
10、用OR的字句可以分解成多个查询，并且通过UNION 连接多个查询。他们的速度只同是否使用索引有关,如果查询需要用到联合索引，用 UNION all执行的效率更高.多个OR的字句没有用到索引，改写成UNION的形式再试图与索引匹配。一个关键的问题是否用到索引。
11、没有必要时不要用DISTINCT和ORDER BY，这些动作可以改在客户端执行。它们增加了额外的开销。这同UNION和UNION ALL一样的道理。
12、使用in时，在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，这样可以减少判断的次数
13、当用SELECT INTO时，它会锁住系统表(sysobjects，sysindexes等等)，阻塞其他的连接的存取。创建临时表时用显示声明语句，在另一个连接中 SELECT * from sysobjects可以看到 SELECT INTO 会锁住系统表， Create table 也会锁系统表(不管是临时表还是系统表)。所以千万不要在事物内使用它！！！这样的话如果是经常要用的临时表请使用实表，或者临时表变量。
14、一般在GROUP BY和HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优：select 的Where字句选择所有合适的行，Group By用来分组个统计行，Having字句用来剔除多余的分组。这样Group By和Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。如果Group BY的目的不包括计算，只是分组，那么用Distinct更快
15、一次更新多条记录比分多次更新每次一条快,就是说批处理好
16、慎用临时表，临时表存储于tempdb库中，操作临时表时，会引起跨库操作。尽量用结果集和表变量来代替它。
17、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译好、优化过，并且被组织到一个执行规划里、且存储在数据库中的 SQL语句，是控制流语言的集合，速度当然快。
18、不要在一段SQL或者存储过程中多次使用相同的函数或相同的查询语句，这样比较浪费资源,建议将结果放在变量里再调用。这样更快。
19、按照一定的次序来访问你的表。如果你先锁住表A，再锁住表B，那么在所有的存储过程中都要按照这个顺序来锁定它们。如果你（不经意的）某个存储过程中先锁定表B，再锁定表A，这可能就会导致一个死锁。
oracle Certification Program (OCP认证)的题目
(1) A 表中有100条记录.
Select * FROM A Where A.COLUMN1 = A.COLUMN1
这个语句返回几条记录? (简单吧,似乎1秒钟就有答案了:)
(2) Create SEQUENCE PEAK_NO
Select PEAK_NO.NEXTVAL FROM DUAL --> 假设返回1
10秒中后,再次做
Select PEAK_NO.NEXTVAL FROM DUAL --> 返回多少?
(3) SQL> connect sys as sysdba
Connected.
SQL> insert into dual values ( ‘Y‘);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from dual;
COUNT(*)
----------
2
SQL> delete from dual;
commit;
-->DUAL里还 剩几条记录?
JUST TRY IT
一些高难度的SQL面试题
以 下的null代表真的null，写在这里只是为了让大家看清楚
根据如下表的查询结果，那么以下语句的结果是（知识点：not in/not exists+null）
SQL> select * from usertable;
USERID USERNAME
----------- ----------------
1 user1
2 null
3 user3
4 null
5 user5
6 user6
SQL> select * from usergrade;
USERID USERNAME GRADE
---------- ---------------- ----------
1 user1 90
2 null 80
7 user7 80
8 user8 90
执行语句：
select count(*) from usergrade where username not in (select username from usertable);
select count(*) from usergrade g where not exists
(select null from usertable t where t.userid=g.userid and t.username=g.username);
结 果为：语句1( 0 ) 语句2 ( 3 )
A: 0 B:1 C:2 D:3 E:NULL
2
在以下的表的显示结果中，以下语句的执行结果是（知识 点：in/exists+rownum）
SQL> select * from usertable;
USERID USERNAME
----------- ----------------
1 user1
2 user2
3 user3
4 user4
5 user5
SQL> select * from usergrade;
USERNAME GRADE
---------------- ----------
user9 90
user8 80
user7 80
user2 90
user1 100
user1 80
执行语句
Select count(*) from usertable t1 where username in
(select username from usergrade t2 where rownum <=1);
Select count(*) from usertable t1 where exists
(select ‘x‘ from usergrade t2 where t1.username=t2.username and rownum <=1);
以上语句的执行结果 是：( ) ( )
A: 0 B: 1 C: 2 D: 3
根 据以下的在不同会话与时间点的操作，判断结果是多少，其中时间T1原始表记录为；
select * from emp;
EMPNO DEPTNO SALARY
----- ------ ------
100 1 55
101 1 50
select * from dept;
DEPTNO SUM_OF_SALARY
------ -------------
1 105
2
可以看到，现在因为还没有部门2的员工，所以总薪水为null，现在，
有两个 不同的用户（会话）在不同的时间点（按照特定的时间顺序）执行了一系列的操作，那么在其中或最后的结果为：
time session 1 session2
----------- ------------------------------- -----------------------------------
T1 insert into emp
values(102,2,60)
T2 update emp set deptno =2
where empno=100
T3 update dept set sum_of_salary =
(select sum(salary) from emp
where emp.deptno=dept.deptno)
where dept.deptno in(1,2);
T4 update dept set sum_of_salary =
(select sum(salary) from emp
where emp.deptno=dept.deptno)
where dept.deptno in(1,2);
T5 commit;
T6 select sum(salary) from emp group by deptno;
问题一：这里会话2的查询结果为：
T7 commit;
=======到这里为此，所有事务都已完成，所以以下查询与会话已没有关系========
T8 select sum(salary) from emp group by deptno;
问题二：这里查询结果为
T9 select * from dept;
问题三：这里查询的结果为
问题一的结果( ) 问题 二的结果是( ) 问题三的结果是( )
A: B:
---------------- ----------------
1 50 1 50
2 60 2 55
C: D:
---------------- ----------------
1 50 1 115
2 115 2 50
E: F:
---------------- ----------------
1 105 1 110
2 60 2 55
有表一的查询结果如下，该表为学生成绩表（知识点：关联更新）
select id,grade from student_grade
ID GRADE
-------- -----------
1 50
2 40
3 70
4 80
5 30
6 90
表二为补考成绩表
select id,grade from student_makeup
ID GRADE
-------- -----------
1 60
2 80
5 60
现在有一个dba通过如下语句把补考成绩更新到成绩表中，并提交：
update student_grade s set s.grade =
(select t.grade from student_makeup t
where s.id=t.id);
commit;
请问之后查询：
select GRADE from student_grade where id = 3;结果为：
A: 0 B: 70 C: null D: 以上都不对
根据以下的在不同会话与时间点的操作，判断结果是多少，
其中时间T1
session1 session2
-------------------------------------- ----------------------------------------
T1 select count(*) from t;
--显示结果(1000)条
T2 delete from t where rownum <=100;
T3 begin
delete from t where rownum <=100;
commit;
end;
/
T4 truncate table t;
T5 select count(*) from t;
-- 这里显示的结果是多少
A: 1000 B: 900 C: 800 D: 0
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计算以下四种人：
fsalary>9999 and fage > 35
fsalary>9999 and fage < 35
fsalary <9999 and fage > 35
fsalary <9999 and fage < 35
每种员工的数量；
select sum(case when fsalary > 9999 and fage > 35
then 1
else 0end) 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、表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 6800
11 rows selected
select 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)---------- ------------ ---------------- ----------------200801 4600 0 7500200802 7500 4600 4000200803 4000 7500 1800200804 1800 4000 4200200805 4200 1800 6500200806 6500 4200 5000200807 5000 6500 0200809 6800 0 0
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语句完成。
select nvl(to_char(t02,‘yyyy-mm-dd‘),‘合计‘),sum(t01)from test
group by rollup(t02)
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万记录。
1、表：table1(FId,Fclass,Fscore),用最高效最简单的SQL列出各班成绩最高的列表，显示班级，成绩两个字段。
2、有一个表table1有两个字段FID，Fno，字都非空，写一个SQL语句列出该表中一个FID对应多个不同的Fno的纪录。
类如：
101 a1001
101 a1001
102 a1002
102 a1003
103 a1004
104 a1005
104 a1006
105 a1007
105 a1007
105 a1007
结果：
102 a1002
102 a1003
104 a1005
104 a1006
3、有员工表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万记录。
答案：
1、select Fclass,max(Fscore) from table1 group by Fclass
2、select * from table1 where FID in (select FID from table1 group by FID having (count(Distinct Fno))>=2)
3、select sum(case when fsalary>9999 and fage>35 then 1 else 0 end),
sum(case when fsalary>9999 and fage<35 then 1 else 0 end),
sum(case when fsalary<9999 and fage>35 then 1 else 0 end),
sum(case when fsalary<9999 and fage<35 then 1 else 0 end) from empinfo
4、
Select (Select Month From Table Where Month = To_Ch
