oracle试题及答案

Oracle 阶段测试题

一选择题（2*10=20）

1．下列不属于ORACLE的逻辑结构的是（C ）

A区

B段

C 数据文件

D表空间

2. 下面哪个用户不是ORACLE缺省安装后就存在的用户(A)

A . SYSDBA

B. SYSTEM

C. SCOTT

D. SYS

3 .下面哪个操作会导致用户连接到ORACLE数据库，但不能创建表(A)

A授予了CONNECT的角色，但没有授予RESOURCE的角色

B没有授予用户系统管理员的角色

C数据库实例没有启动

D数据库监听没有启动

4. 在Oracle中，下面关于函数描述正确的是（AD）。

A SYSDATE函数返回Oracle服务器的日期和时间

B ROUND数字函数按四舍五入原则返回指定十进制数最靠近的整数

C ADD_MONTHS日期函数返回指定两个月份天数的和

D SUBSTR函数从字符串指定的位置返回指定长度的子串

5. 在Oracle中，表EMP包含以下列：

……

NAME VARCHAR2(20)

ADDR VARCHAR2(60)

……

要以NAME’s address is ADDR格式返回数据，以下SQL语句正确的是（B）。

+ 的两边做T o_number()

A SELECT NAME + ’’’s address is ‘ + ADDR FROM EMP;

B SELECT NAME || ’’’s address is ‘ || ADDR FROM EMP;

C SELECT NAME + ’\’s address is ‘ + ADDR FROM EMP;

D SELECT NAM

E || ’\’s address is ‘ || ADDR FROM EMP;

6:

在Oracle中，执行下面的语句：

SELECT ceil(-97.342),

floor(-97.342), -123.01 -124

round(-97.342),

trunc(-97.342)

FROM dual;

哪个函数的返回值不等于-97（B）。

A ceil()

B floor()

C round()

D trunc()

7:在Oracle中，事务中使用下列SQL语句不会引起锁定（A）。（选择一项）

A SELECT

B INSERT

C UPDATE

D DELETE

8:

ORACLE中，执行语句：SELECT address1||’,'||address2||’,'||address2 ”Address”FROM employ；将会返回（B）列。

A 0

B 1

C 2

D 3

9:

Oracle数据库中，下面（C）可以作为有效的列名。

A Column

B 123_NUM

C NUM_#123

D #NUM123

10:

Oracle数据库中，以下（C）命令可以删除整个表中的数据，并且无法回滚。

A drop

B delete

C truncate

D cascade

二简答题（4*10=40）

1. 写出几个对字符串操作的函数？

答：ASCII() –函数返回字符表达式最左端字符的ASCII 码值

CHR() –函数用于将ASCII 码转换为字符

–如果没有输入0 ~ 255 之间的ASCII 码值CHR 函数会返回一个NULL 值

–应该是必须给chr()赋数字值

concat(str,str)连接两个字符串

LOWER() –函数把字符串全部转换为小写

UPPER() –函数把字符串全部转换为大写

LTRIM() –函数把字符串头部的空格去掉

RTRIM() –函数把字符串尾部的空格去掉

TRIM() –同时去掉所有两端的空格

实际上LTRIM(),RTRIM(),TRIM()是将指定的字符从字符串中裁减掉

其中LTRIM(),RTRIM()的格式为xxxx(被截字符串，要截掉的字符串)，但是TRIM()

的格式为TRIM(要截掉的一个字符from 被截的字符串)

SUBSTR() –函数返回部分字符串

INSTR(String,substring) –函数返回字符串中某个指定的子串出现的开始位置，如

果不存在则返回0

REPLACE(原来的字符串，要被替换掉的字符串，要替换成的字符串)

SOUNDEX() –函数返回一个四位字符码

–SOUNDEX函数可用来查找声音相似的字符串但SOUNDEX函数对数字和汉字

均只返回NULL 值

2.描述一下事务，以及事务的一致性和可恢复性。

答案：事务是这样一种机制，它确保多个SQL语句被当作单个工作单元来处理。事务具有以下的作用：

* 一致性：同时进行的查询和更新彼此不会发生冲突，其他用户不会看到发生了变

化但尚未提交的数据。

* 可恢复性：一旦系统故障，数据库会自动地完全恢复未完成的事务。

3.Truncate; drop; delete三者的区别？

这道题可以衍生出很多的问题比如：想快速的清除一个很大的表中的数据应该用设么操作？答案是：应该用truncate。还有，在对两个相同纪录数的分别表使用

了truncate,和delete commit;后对这两个表进行count(*)统计记录数操作，那个表

速度会快些？答案是：使用了truncate的会快些。)

delete一般用于删除少量记录的操作，而且它是要使用回滚段并且要进行显示的提交事务。而truncate则使用于大量删除数据，而且隐式提交事务，其速度要

比使用delete快的多。

1>、TRUNCATE在各种表上无论是大的还是小的都非常快。如果有ROLLBACK

命令DELETE将被撤销，而TRUNCATE则不会被撤销。

2>、TRUNCATE是一个DDL语言而DELETE是DML语句，向其他所有的DDL

语言一样，他将被隐式提交，不能对TRUNCATE使用ROLLBACK命令。

3>、TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全

浏览时，经过TRUNCATE操作后的表比DELETE操作后的表要快得多。

4>、TRUNCATE不能触发触发器，DELETE会触发触发器。

5>、不能授予任何人清空他人的表的权限。

6>、当表被清空后表和表的索引讲重新设置成初始大小，而delete则不能。

7>、不能清空父表。

4.DDL和DML分别代表什么？？

DDL表示数据定义语言，在ORACLE中主要包括CREATE，ALTER，DROP

DML表示数据操作语言，主要的DML有SELECT，INSERT，UPDATE，DELETE

5.oracle中如何删除用户？

Oracle中使用DROP USER来删除用户，如果使用DROP USER CASCADE那么用户的对象也同时被删除掉。为了达到删除用户的效果而又不影响对用户下的对象的使用可以使用alter user username account lock将用户锁定。

6.ORACLE的基本数据类型有哪些？

Char()存储定长字符，定义的时候可以不为他指定长度但是如若往里插入值则会出错；

varchar2()存储变长字符定义的时候必须指定长度，date存储时间日期；Number()数字类型，包括整型，浮点型等；clob()大容量字符串；blob()大二进制对象

7.列举几种表的连接方式

Answer：等连接（内连接）、非等连接、自连接、外连接（左、右、全）

Or hash join/merge join/nest loop(cluster join)/index join ？？

ORACLE 8i，9i 表连接方法。

一般的相等连接：select * from a, b where a.id = b.id; 这个就属于内连接。

对于外连接：

Oracle中可以使用“(+) ”来表示，9i可以使用LEFT/RIGHT/FULL OUTER JOIN

LEFT OUTER JOIN：左外关联

SELECT https://www.360docs.net/doc/ca8046513.html,st_name, e.department_id, d.department_name

FROM employees e

LEFT OUTER JOIN departments d

ON (e.department_id = d.department_id);

等价于

SELECT https://www.360docs.net/doc/ca8046513.html,st_name, e.department_id, d.department_name

FROM employees e, departments d

WHERE e.department_id=d.department_id(+)

结果为：所有员工及对应部门的记录，包括没有对应部门编号department_id的员工记录。

RIGHT OUTER JOIN：右外关联

SELECT https://www.360docs.net/doc/ca8046513.html,st_name, e.department_id, d.department_name

FROM employees e

RIGHT OUTER JOIN departments d

ON (e.department_id = d.department_id);

等价于

SELECT https://www.360docs.net/doc/ca8046513.html,st_name, e.department_id, d.department_name

FROM employees e, departments d

WHERE e.department_id(+)=d.department_id

结果为：所有员工及对应部门的记录，包括没有任何员工的部门记录。

FULL OUTER JOIN：全外关联

SELECT https://www.360docs.net/doc/ca8046513.html,st_name, e.department_id, d.department_name

FROM employees e

FULL OUTER JOIN departments d

ON (e.department_id = d.department_id);

结果为：所有员工及对应部门的记录，包括没有对应部门编号department_id的员工记录和没有任何员工的部门记录。

ORACLE8i是不直接支持完全外连接的语法，也就是说不能在左右两个表上同时加上(+)，下面是在ORACLE8i可以参考的完全外连接语法

select t1.id,t2.id from table1 t1,table t2 where t1.id=t2.id(+)

union

select t1.id,t2.id from table1 t1,table t2 where t1.id(+)=t2.id

连接类型

定义图示例子

内连接只连接匹配的行select A.c1,B.c2 from A join B on A.c3 = B.c3;

左外连接包含左边表的全部行（不管右边的表中是否存在与它们匹配的行）以及右边表中全部匹配的行select A.c1,B.c2 from A left join B on A.c3 = B.c3;

右外连接包含右边表的全部行（不管左边的表中是否存在与它们匹配的行）以及左边表中全部匹配的行select A.c1,B.c2 from A right join B on A.c3 = B.c3;

全外连接包含左、右两个表的全部行，不管在另一边的表中是否存在与它们匹配的行select A.c1,B.c2 from A full join B on A.c3 = B.c3;

（theta）连接使用等值以外的条件来匹配左、右两个表中的行select A.c1,B.c2 from A join B on A.c3 != B.c3;

交叉连接生成笛卡尔积——它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行一一匹配select A.c1,B.c2 from A,B;

8.如何修改表名?

alter table old_table_name rename to new_table_name;

9、如何统计两个表的记录总数

select (select count(id) from table_nameA)+(select count(id) from table_nameB)总数from dual;

10 、日期的各部分的常用的的写法

select to_char(sysdate,’yyyy’) from dual;

取时间点的月份的写法:

select to_char(sysdate,’mm’) from dual;

取时间点的日的写法:

select to_char(sysdate,’dd’) from dual;

取时间点的时的写法:

select to_char(sysdate,’hh24′) from dual;

取时间点、分的写法:

select to_char(sysdate,’mi’) from dual;

取时间点、秒的写法:

select to_char(sysdate,’ss’) from dual;

取时间点的日期的写法:

select trunc(sysdate) from dual;

取时间点的时间的写法:

select to_char(sysdate,’hh24:mi:ss’) from dual;

日期,时间形态变为字符形态

select to_char(sysdate) from dual;

将字符串转换成日期或时间形态:

select to_date(’2003/08/01′) from dual;

select to_char(sysdate,’d') from dual;

返回参数一年中的第几天的写法:

select to_char(sysdate,’ddd’) from dual;

返回午夜和参数中指定的时间值之间的秒数的写法:

select to_char(sysdate,’sssss’) from dual;

返回参数中一年的第几周的写法:

select to_char(sysdate,’ww’) from dual;

currval和nextval为表创建序列

create sequence empseq … ;

select empseq.currval from dual ;

自动插入序列的数值

insert into emp values (empseq.nextval, ‘lewis’, ‘clerk’, 7902, sysdate, 1200, null,20) ;

三操作题（2*20=40）

1、查询EMP表显示在1981年2月1日到1981年5月1日之间雇佣的雇员名、岗位及雇佣日期，并以雇佣日期进行排序。（注意日期转换的知识点）

select ename, job, hiredate

from emp

where hiredate > to_date('1981/2/1', 'YYYY/MM/DD')

and hiredate < to_date('1981/5/1', 'YYYY/MM/DD')

order by hiredate;

2、列出至少有一个雇员的所有部门。

select d.deptno, d.dname, count(e.empno)

from emp e, dept d

where e.deptno(+) = d.deptno

group by d.deptno, d.dname

having count(e.empno) >= 1

3、列出与“SCOTT”从事相同工作的所有雇员。（注意关于exist的东西，因为in的执行效率比较低，虽然写起来简单）

select * from emp e where e.job in (select distinct p.job from emp p where p.ename='SC OTT');

select * from emp e where exists (select 1 from emp p where p.job = e.job and p.ename ='SCOTT');

4、列出薪金等于在部门30工作的所有雇员的薪金的雇员的姓名和薪金。（注意考虑

e.deptno is null这种情况及exist的写法）

select * from emp e where e.sal in (select p.sal from emp p where p.deptno = 30) and (e. deptno <> 30 or e.deptno is null);

select * from emp e where exists (select 1 from emp p where p.sal = e.sal and p.deptno= 30 and (e.deptno<>30 or e.deptno is null) );

5、列出在每个部门工作的雇员的数量以及其他信息。

select d.deptno,d.loc,d.dname ,count(e.empno) from emp e ,dept d where e.deptno (+)= d.deptno group by d.deptno,d.loc,d.dname;

select e.*,d.deptno,count(e.empno) over(partition by d.deptno) from emp e ,dept d where e.deptno (+)= d.deptno

6、找出各月倒数第3天受雇的所有员工.

select e.* from emp e where last_day(e.hiredate)= e.hiredate+2;

7、找出早于12年前受雇的员工.

select *

from emp

where hiredate < trunc(sysdate)+(INTERVAL '-12' YEAR);

8、显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面.

select ename,

extract(YEAR from hiredate) 年,

extract(MONTH from hiredate) 月

from emp

order by 月, 年;

select e.ename,

to_char(e.hiredate, 'yyyy') 年,

to_char(e.hiredate, 'mm') 月

from emp e

order by 月, 年;

9、对于每个员工,显示其加入公司的天数. （注意如果select后面的字段中除了emp表中原有字段还有其他字段，那么需要将emp重命名）

select e.*,ceil(sysdate-e.hiredate) "入职天数" from emp e ;

select ename, trunc(sysdate-hiredate) from emp;

如果我们需要将两个select语句的结果作为一个整体显示出来，我们就需要用到union或者union all关键字。union(或称为联合)的作用是将多个结果合并在一起显示出来。

union和union all的区别是,union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

Select1 union select2 取两个select 去除重复的

Select1 union all select 不去除重复的

Select1 intersect select2 取交集

Select1 minus select2 取差集

10、用一个查询语句，实现查询各个岗位的总工资和各个部门的总工资和所有雇员的总工资

（方法1）-- union all ！！

select e.job||'岗位总工资' "总工资",sum(e.sal) from emp e group by e.job

union all

select e.deptno||'部门总工资',sum(e.sal) from emp e group by e.deptno

union all

select '雇员总工资',sum(e.sal) from emp e;

（方法2）grouping sets！！

select e.job,e.deptno,sum(e.sal) from emp e group by grouping sets((e.job),(e.deptno),( null));

（方法3）

select distinct

sum(e.sal) over(partition by e.job) 岗位总工资,

sum(e.sal) over(partition by e.deptno) 部门总工资,

sum(e.sal) over() 雇员总工资

from emp e;

（方法3）grouping sets（不重要）

select sum(e.sal),

decode(grouping_id(e.job), 1, 'deptno合计', e.job) job,

decode(grouping_id(e.deptno), 1, 'job合计', e.deptno) deptno,

decode(grouping_id(e.job),

1,

decode(grouping_id(e.deptno), 1, '总计')) sal_sum

from emp e

group by grouping sets((e.job),(e.deptno),(null));

（方法4）cube （不重要）

select *

from (select sum(e.sal),

decode(grouping_id(e.job), 1, '合计', e.job) job,

decode(grouping_id(e.deptno), 1, '合计', e.deptno) deptno

from emp e

group by cube(e.job, e.deptno)) a

where a.job = '合计'

or(a.job is not null and a.deptno ='合计')

11、一个面试中遇到的SQL语句的查询问题

--表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B 列大于C列时选择B列否则选择C列。

create table compare(A number(2),B number(2), C number(2));

insert into compare values (70, 80, 98);

select * from compare;

答案：

select (case when a > b then a else b end),

(case when b > c then b else c end)

from compare

12、有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）：

大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。

显示格式：

语文数学英语

及格优秀不及格

*/

------------------------------------------

create table exam("语文" number(2),"数学" number(2), "英语" number(2));

insert into exam values (70, 80, 58);

select * from exam;

答案：

select case

when 语文>= 80 then '优秀'

when 语文>= 60 then '及格'

else '不及格'

end 语文,

case

when 数学>= 80 then '优秀'

when 数学>= 60 then '及格'

else '不及格'

end 数学,

case

when 英语>= 80 then '优秀'

when 英语>= 60 then '及格'

else '不及格'

end 英语

from exam;

13、查询每个部门的员工数量，只找大于4个的

select deptno,sum(sal) from emp

group by deptno

select deptno,count(*) from emp

group by deptno having count(*)>4

14、查询每个部门的员工数量，没有的也要显示，部门要显示名称

select d.deptno,count(e.empno) from emp e,dept d where e.deptno(+)=d.deptno

group by d.deptno having count(e.empno)>=1;

15、查询高于平均工资的员工工资

select ename from emp

where sal>(select avg(sal) from emp);

ALL和any

select ename,sal,deptno from emp

where sal < all(select sal from emp where deptno=10)

;

select ename,sal,deptno from emp

where sal < any(select sal from emp where deptno=10)

16、查询每个部门中每个岗位收入和

select deptno,job, sum(sal) from emp

group by job,deptno order by deptno

17、查询工资最高的前两名

select * from (select e.*,rank() over(order by sal desc) rank from emp e) where rank<=2

18、列出在部门"SALES"（销售部）工作的员工的姓名，假定不知道销售部的部门编号.

a.通过dept表查询出销售部的部门编号

select deptno from dept where dname like 'SALES'

b.将之前的查询作为子查询：

select ename from emp where deptno =

(select deptno from dept where dname like 'SALES')

19、给任职超过十年的加薪10%(这个问题是个批量更新)

update emp set sal=sal*1.1 where months_between(sysdate,hiredate)/12 >20;

20、列出薪金高于部门30中员工的薪金的所有员工的姓名和薪金,部门名称

第一步：查询部门30中员工的薪金

select sal from emp where deptno='30';

第二步：查询高于上述薪金的员工姓名和薪金

select e.ename,e.sal

from emp e

where e.sal >all(select sal from emp where deptno='30');

第三步：查询部门

select e.ename,e.sal,d.dname

from emp e,dept d

where e.sal >all(select sal from emp where deptno='30') and e.deptno=d.deptno;