Sql学习笔记：试题库1

SQL：

1.Mysql综合测评

1.1统计薪资大于薪资最高的员工所在部门的平均工资和薪资最低的员工所在部门的平均工资的平均工资的员工信息。

2.统计薪资和平均薪资最低的部门中薪资最高的员工的薪资相差500之内的全部员工的信息

3.统计出薪资和平均薪资处于正中间的部门中薪资最高的员工的薪资 相差500之内的全部员工的信息

4.查询部门名称不是research,职位是manager,且薪资大于平均薪资的员工(包含ename hiredait loc三个字段)

5.找出5月1号之后入职的并且薪资高于NEWYORK地区的平均薪资的员工的具体信息(部门信息代替部门编号)按薪资降序列出

6查询各个部门{工资小于【纽约员工平均工资】}且{入职时间大于30年}的员工人数（输出字段至少包含[部门地址，部门名称，人数]这三个字段）

7.查询出入职时间最早的员工所在部门的最高薪资的员工名字,工作职位.工作地点

8.统计出薪资位于平均工资最高的部门与平均薪资最低的部门之间 的入职日期最晚的员工的工作城市的所有员工的平均工资

9.查询平均工资最高的地区的最早入职的员工

10 列出各个地区平均薪资处于中等水平的地区，列出该地区人数和平均在职时间

2.Oracle数据库练习

2.1demobld.sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
--
-- Copyright (c) Oracle Corporation 1988, 2000.  All Rights Reserved.
--
-- NAME
--  demobld.sql
--
-- DESCRIPTION
--  This script creates the SQL*Plus demonstration tables in the
--  current schema.  It should be STARTed by each user wishing to
--  access the tables.  To remove the tables use the demodrop.sql
--  script.
--
--  USAGE
--   From within SQL*Plus, enter:
--     START demobld.sql

SET TERMOUT ON
PROMPT Building demonstration tables.  Please wait.
SET TERMOUT OFF
DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE BONUS;
DROP TABLE SALGRADE;
DROP TABLE DUMMY;
CREATE TABLE EMP	
​   (
     -- 雇员编号
    EMPNO NUMBER(4) NOT NULL,
     -- 雇员姓名
​    ENAME VARCHAR2(10),
     -- 雇员职位
​    JOB VARCHAR2(9),
     -- 雇员对应的领导的编号
​    MGR NUMBER(4),
     -- 雇员的雇佣日期
​    HIREDATE DATE,
     -- 雇员的基本工资
​    SAL NUMBER(7, 2),
     -- 奖金
​    COMM NUMBER(7, 2),
     -- 所在部门
​    DEPTNO NUMBER(2),
     foreign key(deptno) references dept(deptno)
 );
INSERT INTO EMP VALUES
​    (7369, 'SMITH',  'CLERK',   7902,
​    TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO EMP VALUES
​    (7499, 'ALLEN',  'SALESMAN',  7698,
​    TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMP VALUES
​    (7521, 'WARD',  'SALESMAN',  7698,
​    TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMP VALUES
​    (7566, 'JONES',  'MANAGER',  7839,
​    TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMP VALUES
​    (7654, 'MARTIN', 'SALESMAN',  7698,
​    TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
​    (7698, 'BLAKE',  'MANAGER',  7839,
​    TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMP VALUES
​    (7782, 'CLARK',  'MANAGER',  7839,
​    TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMP VALUES
​    (7788, 'SCOTT',  'ANALYST',  7566,
​    TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
​    (7839, 'KING',  'PRESIDENT', NULL,
​    TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
​    (7844, 'TURNER', 'SALESMAN',  7698,
​    TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500,   0, 30);
INSERT INTO EMP VALUES
​    (7876, 'ADAMS',  'CLERK',   7788,
​    TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
​    (7900, 'JAMES',  'CLERK',   7698,
​    TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  950, NULL, 30);
INSERT INTO EMP VALUES
​    (7902, 'FORD',  'ANALYST',  7566,
​    TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMP VALUES
​    (7934, 'MILLER', 'CLERK',   7782,
​    TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
 

CREATE TABLE DEPT
​    (
     -- 部门编号
     DEPTNO NUMBER(2),
     -- 部门名称
​    DNAME VARCHAR2(14),
     -- 部门所在位置
​    LOC VARCHAR2(13) );
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',  'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',    'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE BONUS
​    (
      -- 雇员姓名
      ENAME VARCHAR2(10),
      -- 雇员职位
​     JOB  VARCHAR2(9),
      -- 雇员工资
​     SAL  NUMBER,
      -- 雇员资金
​     COMM  NUMBER);
 
CREATE TABLE SALGRADE
​    (GRADE NUMBER,
​     LOSAL NUMBER,
​     HISAL NUMBER);
 
INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);
 
CREATE TABLE DUMMY
​    (DUMMY NUMBER);
INSERT INTO DUMMY VALUES (0);
COMMIT;

SET TERMOUT ON
PROMPT Demonstration table build is complete.
EXIT

SCOTT用户四张表结构

表一：部门表DEPT（使用DESC DEPT;查询）

（SELECT * FROM DEPT；）

表二：雇员表EMP（使用DESC EMP;查询）

（SELECT * FROM EMP;）

表三：工资等级表：（DESC SALGRADE）

（SELECT * FROM SALGRADE；查询）

表四：工资表BONUS：（DESC BONUS）

2.2SCOTT用户语句与函数练习

2.2.1.列出至少有一个员工的所有部门

1
2
3
4
5
6
select d.dname, t1.co
from (select e.deptno, count(e.deptno) co from emp e group by e.deptno) t1,
dept d
where d.deptno = t1.deptno
and t1.co > 1;

2.2.2.列出薪金比‘SMITH’多的所有员工

1
2
3
4
select *
from emp
where sal > (select e.sal from emp e where e.ename = 'SMITH');

2.2.3.列出所有员工的姓名以及直接上级的姓名

1
2
select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;

2.3.4.列出受雇日期早于直接上级入职日期的职工编号，姓名，入职日期

1
2
3
4
5
6
7
8
9
select e1.empno 员工号,
e1.ename 员工姓名,
e1.hiredate 入职日期,
e2.ename 上级姓名,
e2.hiredate 上级入职日期
from emp e1, emp e2
where e1.mgr = e2.empno
and e1.hiredate < e2.hiredate;

2.3.5.列出部门名称和这些部门的员工信息，同时列出没有员工的部门信息

1
2
3
4
5
6
select d.dname, e.*
from emp e
right outer join dept d
on e.deptno = d.deptno
order by d.dname;

2.3.6.列出所有职位为’CLERK’的姓名和部门名称，部门人数

1
2
3
4
5
select e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno
and e.job = 'CLERK';

2.3.7.列出最低工薪大于1500的各种工作以及从事此工作的全部雇员的人数

1
2
3
4
5
select distinct e.job, count(*)
from emp e
where e.sal > 1500
group by e.job;

2.3.8.列出在部门’SALES’(销售部)工作的员工信息,假设不知道销售部门的部门号

1
2
select e.ename from emp e where e.job = 'SALESMAN';

2.3.9.列出薪金高于公司平均薪金的所有员工,部门,上级领导,工资级别

1
2
3
4
5
6
select e2.ename, e2.deptno, e3.ename, s.grade
from emp e2, emp e3, salgrade s
where e2.sal > (select avg(e.sal) from emp e)
and (e2.sal between s.losal and s.hisal)
and e2.mgr = e3.empno;

2.3.10.列出与’SCOTT’从事相同工作的所有员工及部门名称

1
2
3
4
5
select e.ename, d.dname
from emp e, dept d
where e.job in (select e2.job from emp e2 where e2.ename = 'SCOTT')
and e.deptno = d.deptno;

2.3.11.列出薪金等于部门20中的员工的薪金的所有员工和薪金

1
2
3
4
select *
from emp
where sal in (select e.sal from emp e where e.deptno = 20);

2.3.12.列出薪金高于部门30中的员工的薪金的所有员工和薪金,部门名称

1
2
3
4
5
6
7
select e2.ename, e2.sal, d.dname
from emp e2, dept d
where sal >
(select max(t1.sal)
from (select e.sal from emp e where e.deptno = 30) t1)	
and d.deptno = e2.deptno;

2.3.13.列出每个部门员工的数量,平均工资和平均年限

1
2
3
4
5
6
7
8
9
10
11
select t2.*, t1.ro3 平均年限
from (select e2.deptno, count(*) 员工数量, avg(e2.sal) 平均工资
from emp e2
group by e2.deptno) t2,
(select ro2.deptno, avg(ro2.ro) ro3
from (select e3.deptno,
round(months_between(sysdate, e3.hiredate) / 12) ro
from emp e3) ro2
group by ro2.deptno) t1
where t1.deptno = t2.deptno;

2.3.14.列出所有员工的姓名，工资，部门名称

1
2
3
4
select e.ename, e.sal, d.dname
from emp e, dept d
where d.deptno = e.deptno;

2.3.15.列出所有部门信息和部门人数

1
2
3
4
5
6
7
select d.*, nvl(t1.c, 0) 部门人数
from dept d
left outer join (select e.deptno, count(e.deptno) c
from emp e
group by e.deptno) t1
on d.deptno = t1.deptno;

2.3.16.列出各种工作的最低工薪以及从事此工作的员工信息

1
2
3
4
5
select *
from emp, (select e.job j, min(e.sal) s from emp e group by e.job) t1
where emp.job = t1.j
and emp.sal = t1.s;

2.3.17.列出各个部门职位为’MANAGER’(经理)的最低工薪

1
2
3
4
5
6
7
select a.dname, min(a.sal)
from (select d.dname, e.sal
from emp e, dept d
where e.deptno = d.deptno
and e.job = 'MANAGER') a
group by a.dname;

2.3.18.列出所有员工的年薪，按照年薪由低到高排序

1
2
select e.ename, e.sal * 12 from emp e order by e.sal;

2.3.19.查出每个员工的上级领导，并且求出这些主管的工资超过3000

1
2
3
4
5
select e1.ename 本人姓名, e2.ename 领导姓名, e2.sal 领导工资
from emp e1, emp e2
where e1.mgr = e2.empno
and e2.sal > 3000;

2.2.20.求出部门名称中带有’S’的部门员工的工资合计，部门人数

1
2
3
4
5
6
7
8
select d.dname 部门名称, t2.su 工资合计, t2.co 部门人数
from (select e.deptno, sum(e.sal) su, count(e.empno) co
from emp e
group by e.deptno) t2,
dept d
where t2.deptno in (select d.deptno from dept d where d.dname like '%S%')
and d.deptno = t2.deptno;

2.2.21.给任职日期超过10年的员工加薪10%

1
2
3
4
5
6
7
8
update emp
set sal = sal * 1.1
where empno in (select t1.empno
from (select e.empno,
round(months_between(sysdate, e.hiredate) / 12) ro
from emp e) t1
where t1.ro > 10);

2.2.22.列出所有雇员的姓名及其上级的姓名

1
2
select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;

2.2.23.列出入职日期早于其直接上级的所有雇员

1
2
3
4
5
6
7
8
9
select e1.empno 员工号,
e1.ename 员工姓名,
e1.hiredate 入职日期,
e2.ename 上级姓名,
e2.hiredate 上级入职日期
from emp e1, emp e2
where e1.mgr = e2.empno
and e1.hiredate < e2.hiredate;

2.2.24.列出所有"CLERK"(办事员)的姓名及其部门名称

1
2
3
4
5
select e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno
and e.job = 'CLERK';

2.2.25.列出各种工作类型的最低薪金,并使最低薪金大于1500

1
2
3
4
5
select e.job, min(e.sal)
from emp e
group by e.job
having min(e.sal) > 1500;

2.2.26.查询从事"SALES"(销售)工作的雇员的姓名(假定不知道销售部的部门编号)

1
2
select e.ename from emp e where e.job = 'SALESMAN';

2.2.27.列出从事同一种工作但属于不同部门的雇员的不同组合

1
2
3
4
5
select distinct e.job, d.dname
from emp e, dept d
where e.deptno = d.deptno
order by job;

2.2.28.列出各个部门的MANAGER(经理)的最低薪金

1
2
3
4
5
6
7
select a.dname, min(a.sal)
from (select d.dname, e.sal
from emp e, dept d
where e.deptno = d.deptno
and e.job = 'MANAGER') a
group by a.dname;

2.2.29.列出部门号为20和30的雇员名称,部门名称和薪金

1
2
3
4
5
select e.ename, e.sal, d.dname
from emp e, dept d
where (e.deptno in (20, 30))
and e.deptno = d.deptno;

2.2.30.显示雇员姓名,根据其服务年限,将最老的雇员排在最前面(拓展:查询那个最老雇员的信息)

1
2
3
4
5
6
7
8
9
select e.ename, e.hiredate from emp e order by e.hiredate;
select b.ename, b.empno, b.dname, b.job, b.hiredate
from (select rownum as rn, a.*
from (select e.ename, e.empno, d.dname, e.job, e.hiredate
from emp e, dept d
where e.deptno = d.deptno
order by e.hiredate) a) b
where b.rn = 1

2.2.31.查找EMP表中前10条记录

1
2
select * from emp where rownum < 11;

2.2.32.查找EMP表中7条以后的记录

1
2
select a.* from (select e.*, rownum as rn from emp e) a where a.rn > 7;

2.2.33.列出薪金水平处于第二位的雇员

1
2
3
4
select *
from (select dense_rank() over(order by sal desc) rk, e.* from emp e) t
where t.rk = 2;

2.2.34.查找EMP表中薪水第6高的员工

1
2
3
4
select *
from (select dense_rank() over(order by sal desc) rk, e.* from emp e) t
where t.rk = 6;

2.2.35.查找EMP表中每部门薪水第3的员工

1
2
3
4
5
6
select *
from (select dense_rank() over(partition by deptno order by sal desc) rk,
e.*
from emp e) t
where t.rk = 3;

2.2.36.各月最后受雇的所有雇员

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
/*
根据每个月的雇佣日期排序（逆序）
select dense_rank() over(partition by m order by d desc) rk, t1.*
from (select e.empno,
extract(month from e.hiredate) m,
extract(day from e.hiredate) d
from emp e) t1
*/
select *
from emp
where emp.empno in (select t2.empno
from (select dense_rank() over(partition by m order by d desc) rk,
t1.*
from (select e.empno,
extract(month from e.hiredate) m,
extract(day from e.hiredate) d
from emp e) t1) t2
where t2.rk = 1);

2.2.37、查询薪金高于公司平均水平的所有雇员

1
2
select * from emp where sal > (select avg(sal) from emp);

2.2.38、列出薪金等于在部门30工作的所有雇员的姓名和薪金

1
2
3
4
select *
from emp
where sal in (select e.sal from emp e where e.deptno = 30);

2.2.39.查询出没有员工的部门的部门编号和部门名称

1
2
3
4
select d.deptno, d.dname
from dept d
where d.deptno not in (select distinct deptno from emp);

2.2.40.查询出平均工资最高的部门编号、部门名称和平均工资

1
2
3
4
5
6
7
8
9
10
select d.deptno 部门编号, d.dname 部门名称, t3.av 平均工资
from (select t2.deptno, t2.av
from (select t1.*, dense_rank() over(order by t1.av desc) rk
from (select e.deptno, avg(sal) av
from emp e
group by e.deptno) t1) t2
where t2.rk = 1) t3,
dept d
where t3.deptno = d.deptno;

2.2.41.查找EMP表部门30中薪水第3的员工

1
2
3
4
5
6
7
select t1.*
from (select dense_rank() over(partition by e.deptno order by e.sal) rk,
e.*
from emp e) t1
where t1.rk = 3
and t1.deptno = 30;

2.3面试答疑

2.3.1.1列出在每个部门工作的员工数量、平均工资和平均服务期限。

1
2
Select deptno,count(*),avg(sal+nvl(comm,0)),avg(sysdate-hiredate) from emp group by deptno;

2.3.1.2列出所有员工的姓名、部门名称和工资。

1
2
Select ename,bname,sal+nvl(comm,0) from dept,emp where dept.deptno=emp.deptno;

2.3.1.3列出从事同一种工作但属于不同部门的员工的一种组合。

1
2
Select distinct a.ename , a.job,a.deptno from emp a,emp b where (a.deptno>b.deptno) or (a.deptno<b.deptno) and a.job =b.job order by a.job;

2.3.1.4列出所有部门的详细信息和部门人数。

1
2
select a.deptno,a.dname,a.loc,count(b.empno )from dept a,emp b where a.deptno = b.deptno group by a.deptno,a.dname,a.loc;

2.3.1.5列出各种工作的最低工资。

1
2
select deptno,min(sal+nvl(comm,0) from emp group by deptno;

2.3.1.6列出各个部门的MANAGER（经理）的最低薪金。

1
2
Select sal from emp where job=’MANAGER’

2.3.1.7列出所有员工的年工资,按年薪从低到高排序。

1
2
select ename,(sal+nvl(comm,0))*12 yearsal from emp order by yearsal;

2.3.1.8列出至少有一个员工的所有部门。

1
2
Select dname from dept where deptno in (select deptno from emp);

2.3.1.9列出薪金比“SMITH”多的所有员工。

1
2
Select ename from emp where sal>(select sal from emp where ename=‘SMITH’);

2.3.1.10列出所有员工的姓名及其直接上级的姓名。

1
2
select a.ename 员工姓名,b.ename 直接上级 from emp a,emp b where a.mgr=b.empno;

2.3.1.11列出受雇日期早于其直接上级的所有员工。

1
2
select a.ename from emp a,emp b where a.hiredate<b.hiredate and a.mgr=b.empno;

2.3.1.12列出最低薪金大于1500的各种工作。

1
2
select job from emp where sal>1500;

2.3.1.13列出在部门“SALES”（销售部）工作的员工的姓名， 假定不知道销售部的部门编号。

1
2
Select ename from emp where deptno=(select deptno from dept where dname=’SALES’);

2.3.1.14列出薪金高于公司平均薪金的所有员工。

1
2
Select ename from emp where sal>(select avg(sal) as avg_sal from emp);

2.3.1.15列出与“SCOTT”从事相同工作的所有员工。

1
2
Select ename from emp where job=(select job from emp where ename =’SCOTT’); 

2.3.1.16列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

1
2
Select ename,sal from emp where deptno!= 30,sal in (select sal from emp where deptno=’30’);

2.3.1.17列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。

1
2
Select ename from emp where sal>(select max(sal) as max_sal from emp where deptno=’30’);

2.3.1.18找出奖金高于工资的员工

1
2
select * from emp where comm>sal;

2.3.1.19找出每个员工奖金和工资的总和

1
2
select ename,decode(comm,null,0,comm)+sal from emp;

2.3.1.20找出部门10中的经理(MANAGER)和部门20中的普通员工(CLERK)

1
2
select * from emp where deptno=10 and job='MANAGER' or deptno='20' and job='CLERK';

2.3.1.21找出部门10中既不是经理也不是普通员工，而且工资大于等于2000的员工

1
2
3
select * from emp where deptno=10 and job<>'MANAGER' and job<>'CLERK' and sal>=2000;
select * from emp where deptno=10 and job not in ('MANAGER' ,'CLERK' )and sal>=2000;

2.3.1.22找出有奖金的员工的不同工作

1
2
select distinct(job) from emp where comm is not null 

2.3.1.23找出没有奖金或者奖金低于500的员工*/

1
2
select * from emp where comm is null or comm<500;

2.3.1.24显示雇员姓名，根据其服务年限，将最老的雇员排在最前面 */

1
2
select ename,hiredate from emp order by hiredate,ename;

2.3.1.25找出每个月倒数第三天受雇的员工*/

1
2
select * from emp where hiredate = last_day(hiredate)-2;

2.3.1.26分别用case和decode函数列出员工所在的部门，deptno=10显示’部门10’, deptno=20显示’部门20’ deptno=30显示’部门30’ deptno=40显示’部门40’ 否则为’其他部门’*/

1
2
3
4
5
6
7
8
9
10
11
select ename,decode(deptno,10,'部门10',20,'部门20',30,'部门30',40,'部门40','其他部门') from emp;
select ename,
case deptno 
 when 10 then '部门10'
  when 20 then '部门20'
   when 30 then '部门30'
​    when 40 then '部门40'
​     else '其他部门'
​      end 
​      from emp;

2.3.1.27分组统计各部门下工资>1500的员工的平均工资*/

1
2
select deptno,avg(sal) from emp where sal>1500 group by deptno

2.3.1.28统计各部门下平均工资大于1500的部门

1
2
3
4
select deptno,avg(sal) from emp
group by deptno
having avg(sal)>1500;

2.3.1.29算出部门30中得到最多奖金的员工奖金

1
2
select max(comm) from emp where deptno=30;

2.3.1.30算出部门30中得到最多奖金的员工姓名*/

1
2
3
select ename,comm from emp where comm in
(select max(comm) from emp where deptno=30)

2.3.1.31算出每个职位的员工数和最低工资*/

1
2
3
select job,count(*),min(sal) from emp
group by job;

2.3.1.32列出员工表中每个部门的员工数，和部门no

1
2
select deptno,count(*) from emp group by deptno;

2.3.1.33得到工资大于自己部门平均工资的员工信息

1
2
3
4
select * from emp e, 
(select avg(sal) b from emp group by deptno) a
where e.sal>a.b;

2.3.1.34分组统计每个部门下，每种职位的平均奖金（也要算没奖金的人）和总工资(包括奖金)

1
2
3
4
select deptno,job ,avg(nvl(comm,0)), sum(sal + nvl(comm, 0))  
from scott.emp  
group by deptno,job  

2.3.1.35显示员工ID,名字，直属主管ID，名字

1
2
3
4
select empno,ename,mgr, 
(select ename from emp e1 where e1.empno = e2.mgr)
from emp e2 ;

2.3.1.36列出员工表中每个部门的员工数，和部门no

1
2
select deptno,count(*) from emp group by deptno;

2.3.1.37列出员工表中每个部门的员工数（员工数必须大于3），和部门名称

1
2
3
4
5
select e.deptno,d.dname,count(*) from emp e,dept d
where e.deptno=d.deptno
group by e.deptno,d.dname
having count(*)>3;

2.3.1.38找出工资比jones多的员工

1
2
3
select * from emp e
where e.sal>(select sal from emp where ename='JONES');

2.3.1.39列出所有员工的姓名和其上级的姓名

1
2
3
4
5
6
select a.ename 员工,b.ename 上级 from emp a,emp b
where b.empno=a.mgr;
select * from emp;
select ename, (select ename from emp e1 where e1.empno = e2.mgr)  
from emp e2  

2.3.1.40以职位分组，找出平均工资最高的两种职位

1
2
3
4
select avg(sal) from emp
group by job 
having avg(sal) in (select max(sal) from emp group by job);

2.3.1.41查找出不在部门20，且比部门20中任何一个人工资都高的员工姓名、部门名称

1
2
3
4
select e.ename,d.dname from emp e,dept d
where e.deptno=d.deptno and e.deptno<>20 
and e.sal>(select max(sal) from emp where deptno=20) ;  

2.3.1.42得到平均工资大于2000的工作职种

1
2
3
4
select JOB,avg(sal) from emp 
group by JOB
having avg(sal)>2000; 

2.3.1.43分部门得到工资大于2000的所有员工的平均工资，并且平均工资还要大于2500

1
2
3
4
5
select avg(sal) from emp
where sal>2000
group by deptno 
having avg(sal)>2500;

2.3.1.44查找出收入（工资加上奖金），下级比上级还高的员工编号，员工名字，员工收入

1
2
3
4
select empno,ename,sal+nvl(comm,0) from emp e1
where sal+nvl(comm,0) >
(select sal+nvl(comm,0) from emp where empno=e1.mgr)

2.3.1.45查找出不属于任何部门的员工

1
2
select * from emp where deptno  is null; 

2.3.1.46查询出king所在部门的部门号部门名称部门人数

1
2
3
4
select d.deptno,d.dname,count(*) from dept d,emp e
where d.deptno=e.deptno and d.deptno=(select deptno from emp where ename='KING')
group by d.deptno,d.dname; 

2.3.1.47查询出king所在部门的工作年限最大的员工名字

1
2
3
4
select min(e.hiredate) from emp e
where deptno in
(select deptno from emp where ename='KING') 

2.3.1.48选择部门30中的雇员

1
2
select * from emp where deptno=30;

2.3.1.49列出所有办事员的姓名、编号和部门

1
2
select ename,empno,dname from emp e inner join dept d on e.deptno = d.deptno where job=upper('clerk’);

2.3.1.50找出佣金高于薪金的雇员

1
2
select * from emp where comm>sal;

2.3.1.51找出佣金高于薪金60%的雇员

1
2
select * from emp where comm>sal*0.6

2.3.1.52找出部门10中所有经理和部门20中的所有办事员的详细资料

1
2
select * from emp where (deptno=10 and job=upper('manager')) or (deptno=20 and job=upper('clerk '));

2.3.1.53找出部门10中所有经理、部门20中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料

1
2
select * from emp where (deptno=10 and job=upper('manager')) or (deptno=20 and job=upper('clerk ')) or (job<>upper(‘manager’) and job<>upper(‘clerk’) and sal>=2000)

2.3.1.54找出收取佣金的雇员的不同工作

1
2
select distinct job from emp where comm>0;

2.3.1.55找出不收取佣金或收取的佣金低于100的雇员

1
2
select * from emp where nvl(comm,0)<100;

2.3.1.56找出各月最后一天受雇的所有雇员

1
2
select * from emp where hiredate= last_day(hiredate);

2.3.1.57找出早于25年之前受雇的雇员

1
2
3
select * from emp where months_between(sysdate,hiredate)/12>25;
select * from emp where hiredate<add_months(sysdate,-12*25);

2.3.1.58显示只有首字母大写的所有雇员的姓名

1
2
select ename from emp where ename=initcap(ename);

2.3.1.59显示正好为6个字符的雇员姓名

1
2
select ename from emp where length(ename)=6;

2.3.1.60显示不带有’R’的雇员姓名

1
2
3
Select ename from emp where ename not like ‘%R%’;
Select ename from emp where instr(ename,’R’)=0;

2.3.1.61显示所有雇员的姓名的前三个字符

1
2
select substr(ename,1,3) from emp;

2.3.1.62显示所有雇员的姓名，用a替换所有’A’

1
2
Select replace(ename,’A’,’a’) from emp;

2.3.1.63显示所有雇员的姓名以及满10年服务年限的日期

1
2
Select ename,add_months(hiredate,12*10) ‘服务年限的日期’ from emp;

2.3.1.64显示雇员的详细资料，按姓名排序

1
2
Select * from emp order by ename

2.3.1.65显示雇员姓名，根据其服务年限，将最老的雇员排在最前面

1
2
Select ename from emp order by hiredate;

2.3.1.66显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序

1
2
Select ename,job,sal from emp order by job desc ,sal asc;

2.3.1.67显示所有雇员的姓名和加入公司的年份和月份，按雇员受雇日所在月排序，将最早年份的项目排在最前面

1
2
select ename,to_char(hiredate,'yyyy'),to_char(hiredate,'mm') from emp order by hiredate asc;

2.3.1.68显示在一个月为30天的情况下所有雇员的日薪金

1
2
select ename,sal/30 from emp;

2.3.1.69找出在（任何年份的）2月受聘的所有雇员

1
2
select * from emp where to_char(hiredate,'mm')='02';

2.3.1.70对于每个雇员，显示其加入公司的天数

1
2
　select ename,sysdate-hiredate from emp --两个日期数据相减表示它们之间相差的天数

2.3.1.71显示姓名字段的任何位置，包含 “A” 的所有雇员的姓名

1
2
3
　select ename from emp where ename like '%A%';
　select ename from emp where instr(ename,’A’,1)>0;

2.3.1.72以年、月和日显示所有雇员的服务年限

1
2
Select months_between(sysdate,hiredate)/12 as “年”, months_between(sysdate,hiredate) as “月”, sysdate-hiredate as “日” from emp

2.4OracleDBA数据库结构考题选1

q1 : physical disk resources in an oracle database are

1. control files
2. redo log files
3. data files
4. all of the above

q. 2 : what is a schema

1. a physical organization of objects in the database
2. a logical organization of objects in the database
3. a scheme of indexing
4. none of the above

q. 3 : an oracle instance is

1. oracle memory structures
2. oracle i/o structures
3. oracle background processes
4. all of the above

q. 4 : the sga consists of the following items

1. buffer cache
2. shared pool
3. redo log buffer
4. all of the above

q. 5 : the area that stores the blocks recently used by sql statements is called

1. shared pool
2. buffer cache
3. pga
4. uga

2.5Oracle笔试题

2.5.1、选择题

1.当你执行以下查询语句:
　　SELECT empno,ename
　　FROM emp
　　WHERE empno =7782 OR empno =7876;
　　在WHERE语句中，以下哪个操作符可以取代OR？
　　A. IN
　　B. BETWEEN ……
　　C. LIKE
　　D. <=
　　E. >=

2. 哪个实现 IF…THEN…ELSE 逻辑?
A. INITCAP()
B. REPLACE()
C. DECODE()
D. IFELSE()

3. 以下哪行有错?
1 SELECT deptno
2 FROM emp
3 GROUP BY deptno
4 HAVING COUNT(deptno)=
5 (SELECT max(count(deptno))
6 FROM emp
7 GROUP BY deptno);
A. Line 3
B. Line 4
C. Line 5
D. Line 7
E. 都没错.

4. 当一个程序执行了 SELECT…FOR UPDATE 语句, 以下哪个步骤必须需要完成?
A. 执行 COMMIT or ROLLBACK 结束 transaction, 即使并没有数据改变。
B. 如果有数据改变,则需要执行commit or rollback 来结束 transaction。
C. 由于只有数据确实改变后，一个transaction 才会开始，所以现在不需要COMMIT or ROLLBACK 被执行。

5. 以下哪个命令可以被用来从表 STATE中 drop 列 UPDATE_DT ?
A. ALTER TABLE STATE DROP COLUMN UPDATE_DT;
B. ALTER TABLE STATE REMOVE COLUMN UPDATE_DT;
C. DROP COLUMN UPDATE_DT FROM STATE;
D. 你不能从这个表中DROP该列
.
6. 哪个命令用来创建一个primary key constraint
pk_books 在表 BOOKS, 列 ISBN上? 请选择一个。
A. create primary key on books(ISBN);
B. create constraint pk_books primary key on books(ISBN);
C. alter table books add constraint pk_books primary key (ISBN);
D. alter table books add primary key (ISBN);

7. 以下哪行有错r?
   1 X :=Y +200;
   2 IF X <10 THEN
   3 Y :=30;
   4 ELSEIF X <40 THEN
   5 Y :=20;
   6 END IF;

A. Line 2
B. Line 3
C. Line 4
D. Line 5

8. 一个VIEW被以下语句创建，请问在该VIEW上可进行哪个操作?
CREATE VIEW USA_STATES
AS SELECT *FROM STATE
WHERE CNT_CODE =1
WITH READ ONLY;

A. SELECT
B. SELECT , UPDATE
C. SELECT , DELETE
D. SELECT , INSERT

2.5.2问答题

2.5.2.1 请简述ORACLE在UNIX平台下安装的步骤；

2.5.2.2ORACLE的启动方式有哪几种？

1、startup nomount ，非安装启动，这种方式启动下可执行：重建控制文件、重建数据库读取init.ora文件，启动instance，即启动SGA和后台进程，这种启动只需要init.ora文件。
2、startup mount dbname
　　安装启动，这种方式启动下可执行：
　　数据库日志归档、
　　数据库介质恢复、
　　使数据文件联机或脱机，
　　重新定位数据文件、重做日志文件。
　　执行“nomount”，然后打开控制文件，确认数据文件和联机日志文件的位置，
　　但此时不对数据文件和日志文件进行校验检查。
3、startup open dbname
　　先执行“nomount”，然后执行“mount”，再打开包括Redo log文件在内的所有数据库文件，
　　这种方式下可访问数据库中的数据。
4、startup，等于以下三个命令
　　startup nomount
　　alter database mount
　　alter database open
5、startup restrict
　　约束方式启动
　　这种方式能够启动数据库，但只答应具有一定特权的用户访问
　　非特权用户访问时，会出现以下提示：
　　ERROR：
　　ORA-01035: Oracle 只答应具有 RESTRICTED SESSION 权限的用户使用
6、startup force
　　强制启动方式
　　当不能关闭数据库时，可以用startup force来完成数据库的关闭
　　先关闭数据库，再执行正常启动数据库命令
7、startup pfile=参数文件名
　　带初始化参数文件的启动方式
　　先读取参数文件，再按参数文件中的设置启动数据库
　　例：startup pfile=E:Oracleadminoradbpfileinit.ora
8、startup EXCLUSIVE

2.5.2.3 在ORACLE中控制文件起什么作用？

在ORACLE数据库中,控制文件记录着数据库的物理结构和事务控制的值 (SCN,CHECKPOINT等),以及一些可以被改写的信息(如备份的元数据). 在实例恢复和介质恢复过程中，都要用到控制文件。同时还需要归档日志文件。

2.5.2.4请简述SGA的构成；

SGA （System Global Area）是Oracle Instance的 基本组成部分，在实例启动时分配。是一组包含一个Oracle实例的数据和控制信息的共享内存结构。主要是用于存储数据库信息的内存区，该信息为数据库进程所共享（PGA不能共享的）。它包含Oracle 服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写它主要包括：

1.数据库高速缓存(the database buffer cache)，

2.重演日志缓存（the redo log buffer）

3.共享池（the shared pool）

4.数据字典缓存（the data dictionary cache）以及其它各方面的信息

5.数据库表空间一般分为那几类？每一类的大概功能？

2.5.2.5表空间具有以下类型:

系统编目表空间(SysCatSpace)
系统临时表空间(SysTempSpace)
用户表空间(UserSpace)
用户临时表空间(UserTempSpace)
一个数据库中必须存在两个系统基本的表空间，分别是系统编目表空间与系统临时表空间。在数据库中创建的任何对象都以在系统编目表空间中增加记录的方式体现，对于临时表空间，其占用磁盘大小是根据使用情况动态伸缩的，即仅在需要时才分配磁盘空间，并在使用后进行回收。此外，若用户需要创建表，则需要创建用户表空间， 若需要使用临时表，则需要创建用户临时表空间。

2.5.2.6如何配置访问远程数据库的连接字符串？

1. 假设远程连接串为 ‘210.111.123.321: 1521: EMP’;
2. 测试是否可以连接成功 connect scott/tiger@210.111.123.321: 1521: EMP
3. 连接到本地数据库,执行以下的命令 create database link remote_db connect to scott identified by tiger using ‘REMOTE’;
4. DML测试 select count(*) from emp@remote_db

2.5.2.7访问远程数据库，在服务器端必须启动那一个进程用于网络监听？

lsnrctl

2.5.2.8ORACLE数据库有哪几种标准备分方式？

1.导出／导入（EXP/IMP）

2.热备份。

3.冷备份。 注释：导出备件是一种逻辑备份，冷备份和热备份是物理备份。

2.5.2.9导出／导入（Export／Import）

利用Export可将数据从数据库中提取出来，利用Import则可将提取出来的数据送回到Oracle数据库中去。

１、简单导出数据（Export）和导入数据（Import）

Oracle支持三种方式类型的输出：

（１）、表方式（T方式），将指定表的数据导出。

（２）、用户方式（U方式），将指定用户的所有对象及数据导出。

（３）、全库方式（Full方式），瘵数据库中的所有对象导出。

数据导入（Import）的过程是数据导出（Export）的逆过程，分别将数据文件导入数据库和将数据库数据导出到数据文件。

２、 增量导出／导入

增量导出是一种常用的数据备份方法，它只能对整个数据库来实施，并且必须作为SYSTEM来导出。导出文件名缺省为export.dmp，如果不希望自己的输出文件定名为export.dmp，必须在命令行中指出要用的文件名。

增量导出包括三种类型：

（１）、“完全”增量导出（Complete）

即备份三个数据库，比如： exp system/manager inctype=complete file=040731.dmp

（２）、“增量型”增量导出

备份上一次备份后改变的数据，比如： exp system/manager inctype=incremental file=040731.dmp

（３）、“累积型”增量导出

累计型导出方式是导出自上次“完全”导出之后数据库中变化了的信息。比如： exp system/manager inctype=cumulative file=040731.dmp

数据库管理员可以排定一个备份日程表，用数据导出的三个不同方式合理高效的完成。 比如数据库的被封任务可以做如下安排：

星期一：完全备份（A） 星期二：增量导出（B） 星期三：增量导出（C） 星期四：增量导出（D） 星期五：累计导出（E） 星期六：增量导出（F） 星期日：增量导出（G）

如果在星期日，数据库遭到意外破坏，数据库管理员可按一下步骤来回复数据库：

第一步：用命令CREATE DATABASE重新生成数据库结构；

第二步：创建一个足够大的附加回滚。

第三步：完全增量导入A： imp system/manager inctype=RESTORE FULL=y FILE=A

第四步：累计增量导入E： imp system/manager inctype=RESTORE FULL=Y FILE=E

第五步：最近增量导入F： imp system/manager inctype=RESTORE FULL=Y FILE=F

二、 冷备份

冷备份发生在数据库已经正常关闭的情况下，当正常关闭时会提供给我们一个完整的数据库。冷备份时将关键性文件拷贝到另外的位置的一种说法。对于备份Oracle信息而言，冷备份时最快和最安全的方法。冷备份的优点是：

１、 是非常快速的备份方法（只需拷文件）

２、 容易归档（简单拷贝即可）

３、 容易恢复到某个时间点上（只需将文件再拷贝回去）

４、 能与归档方法相结合，做数据库“最佳状态”的恢复。

５、 低度维护，高度安全。

但冷备份也有如下不足：

１、 单独使用时，只能提供到“某一时间点上”的恢复。

２、 再实施备份的全过程中，数据库必须要作备份而不能作其他工作。也就是说，在冷备份过程中，数据库必须是关闭状态。

３、 若磁盘空间有限，只能拷贝到磁带等其他外部存储设备上，速度会很慢。

４、 不能按表或按用户恢复。

如果可能的话（主要看效率），应将信息备份到磁盘上，然后启动数据库（使用户可以工作）并将备份的信息拷贝到磁带上（拷贝的同时，数据库也可以工作）。冷备份中必须拷贝的文件包括：

１、 所有数据文件

２、 所有控制文件

３、 所有联机REDO LOG文件

４、 Init.ora文件（可选）

值得注意的使冷备份必须在数据库关闭的情况下进行，当数据库处于打开状态时，执行数据库文件系统备份是无效的。

下面是作冷备份的完整例子。

（1） 关闭数据库 sqlplus /nolog sql>connect /as sysdba sql>shutdown normal；

（2） 用拷贝命令备份全部的时间文件、重做日志文件、控制文件、初始化参数文件 sql>cp

（3） 重启Oracle数据库 sql>startup

三、 热备份

热备份是在数据库运行的情况下，采用archivelog mode方式备份数据库的方法。所以，如果你有昨天夜里的一个冷备份而且又有今天的热备份文件，在发生问题时，就可以利用这些资料恢复更多的信息。热备份要求数据库在Archivelog方式下操作，并需要大量的档案空间。一旦数据库运行在archivelog状态下，就可以做备份了。热备份的命令文件由三部分组成：

1. 数据文件一个表空间一个表空间的备份。

（1） 设置表空间为备份状态

（2） 备份表空间的数据文件

（3） 回复表空间为正常状态

2. 备份归档log文件

（1） 临时停止归档进程

（2） log下那些在archive rede log目标目录中的文件

（3） 重新启动archive进程

（4） 备份归档的redo log文件

3. 用alter database bachup controlfile命令来备份控制文件

热备份的优点是：

1. 可在表空间或数据库文件级备份，备份的时间短。

2. 备份时数据库仍可使用。

3. 可达到秒级恢复（恢复到某一时间点上）。

4. 可对几乎所有数据库实体做恢复

5. 恢复是快速的，在大多数情况下爱数据库仍工作时恢复。

热备份的不足之处：

1. 不能出错，否则后果不堪设想。

2. 假如热备份不成功，所得结果则不能用于时间点的恢复。

3. 因为难于维护，所以需要特别仔细小心，不允许在操作时半途而废。

2.6Oracle临时表用法的经验心得

文章主要介绍的是Oracle临时表的实际用法的经验心得，我们目前所使用的 Oracle 是作为数据库支撑平台的实际应用，可以说其数据量还是算的上比较庞大的系统，即表的数据量一般情况下都是在百万级以上的数据量。
当然在 Oracle 中创建分区是一种不错的选择，但是当你发现你的应用有多张表关联的时候，并且这些表大部分都是比较庞大，而你关联的时候发现其中的某一张或者某几张表关联之后得到的结果集非常小并且查询得到这个结果集的速度非常快，那么这个时候我考虑在 Oracle 中创建“临时表”。
我对临时表的理解：在 Oracle 中创建一张表，这个表不用于其他的什么功能，主要用于自己的软件系统一些特有功能才用的，而当你用完之后表中的数据就没用了。 Oracle 的临时表创建之后基本不占用表空间，如果你没有指定Oracle临时表（包括临时表的索引）存放的表空的时候，你插入到临时表的数据是存放在 Oracle 系统的临时表空间中（ TEMP ）。
临时表的创建
创建Oracle 临时表，可以有两种类型的临时表：
会话级的Oracle临时表
事务级的临时表 。

2.6.1会话级的临时表

因为这这个临时表中的数据和你的当前会话有关系，当你当前SESSION 不退出的情况下，临时表中的数据就还存在，而当你退出当前SESSION 的时候，临时表中的数据就全部没有了，当然这个时候你如果以另外一个SESSION 登陆的时候是看不到另外一个SESSION 中插入到临时表中的数据的。
　　即两个不同的SESSION 所插入的数据是互不相干的。当某一个SESSION 退出之后临时表中的数据就被截断（truncate table ，即数据清空）了。会话级的临时表创建方法：
　　1.Create Global Temporary Table Table_Name
　　2.(Col1 Type1,Col2 Type2…) On Commit Preserve Rows ；
　　举例：
　　1.create global temporary table Student
　　2.(Stu_id Number(5),
　　3.Class_id Number(5),
　　4.Stu_Name Varchar2(8),
　　5.Stu_Memo varchar2(200)) on Commit Preserve Rows ;

2.6.2 事务级临时表

是指该临时表与事务相关，当进行事务提交或者事务回滚的时候，Oracle临时表中的数据将自行被截断，其他的内容和会话级的临时表的一致（包括退出SESSION 的时候，事务级的临时表也会被自动截断）。事务级临时表的创建方法：
　　1.Create Global Temporary Table Table_Name
　　2.(Col1 Type1,Col2 Type2…) On Commit Delete Rows ；
　　编辑特别推荐:
　　外部表在Oracle数据库中使用心得

举例：
　　1.create global temporary table Classes
　　2.(Class_id Number(5),
　　3.Class_Name Varchar2(8),
　　4.Class_Memo varchar2(200)) on Commit delete Rows ;

2.6.3两中类型临时表的区别

会话级临时表采用 on commit preserve rows ；而事务级则采用 on commit delete rows ；用法上，会话级别只有当会话结束临时表中的数据才会被截断，而且事务级临时表则不管是 commit 、 rollback 或者是会话结束，Oracle临时表中的数据都将被截断

2.6.4什么时候使用临时表

1 ）、当某一个 SQL 语句关联的表在 2 张及以上，并且和一些小表关联。可以采用将大表进行分拆并且得到比较小的结果集合存放在临时表中
2 ）、程序执行过程中可能需要存放一些临时的数据，这些数据在整个程序的会话过程中都需要用的等等。

2.6.5临时表的不足之处

1 ）不支持 lob 对象，这也许是设计者基于运行效率的考虑，但实际应用中确实需要此功能时就无法使用临时表了。
2 ）不支持主外键关系
　　所以，由于以上原因，我们可以自己创建Oracle临时表，以弥补 Oracle 临时表的不足之处
　　上面的都是本人经过测试的，但下面是在网上搜索到的方法，本人具体没有测试过，不过觉得可行性很强，有时间测试下
　　创建方法：
　　1 、以常规表的形式创建临时数据表的表结构，但要在每一个表的主键中加入一个 SessionID 列以区分不同的会话。（可以有 lob 列和主外键）
　　2 、写一个用户注销触发器，在用户结束会话的时候删除本次会话所插入的所有记录 (SessionID 等于本次会话 ID 的记录 ) 。
　　3 、程序写入数据时，要顺便将当前的会话 ID(SessionID) 写入表中。
　　4 、程序读取数据时，只读取与当前会话 ID 相同的记录即可。
　　功能增强的扩展设计：
　　1 、可以在数据表上建立一个视图，视图对记录的筛选条件就是当前会话的SessionID 。
　　2 、数据表中的SessionID 列可以通过Trigger 实现，以实现对应用层的透明性。
　　3 、高级用户可以访问全局数据，以实现更加复杂的功能。
　　扩展临时表的优点：
　　1 、实现了与Oracle 的基于会话的Oracle临时表相同的功能。
　　2 、支持SDO_GEOMETRY 等lob 数据类型。
　　3 、支持表间的主外键连接，且主外键连接也是基于会话的。
　　4 、高级用户可以访问全局数据，以实现更加复杂的功能

2.7oracle中序列和触发器的使用

在开始正题前，先加一点关于oracle client sqlplus的使用，就是如果执行多行语句的话一定要加“/”才能表示结束，并执行！！
　　本次的目的是通过创建序列和触发器实现表的主键自增。
　　首先创建序列，序列的语法格式为：
　　CREATE SEQUENCE 序列名
　　[INCREMENT BY n]
　　[START WITH n]
　　[{MAXVALUE/ MINVALUE n|NOMAXVALUE}]
　　[{CYCLE|NOCYCLE}]
　　[{CACHE n|NOCACHE}];
　　INCREMENT BY 用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表序列的值是按照此步长递减的。
　　START WITH 定义序列的初始值(即产生的第一个值)，默认为1。
　　MAXVALUE 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增序列，系统能够产生的最大值是10的27次方;对于递减序列，最大值是-1。
　　MINVALUE定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义，这时对于递减序列，系统能够产生的最小值是?10的26次方;对于递增序列，最小值是1。
　　CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值;对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。
　　CACHE(缓冲)定义存放序列的内存块的大小，默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。
　　删除序列的语法是DROP SEQUENCE 序列名;
　　假设有表TEST，其主键为TEST_ID，首先建立递增序列SEQ_TEST:
　　create sequence SEQ_TEST
　　increment by 1
　　start with 1
　　minvalue 1 nomaxvalue
　　nocylce
　　然后建立触发器，当有数据插入表TEST时，使用序列为其去的递增的主键值
　　create trigger TRG_TEST before insert on TEST
　　for each row
　　begin
　　select SEQ_TEST.nextval into :new.TEST_ID from dual;
　　end;
　　至此，创建完成！
　　当然也可以不使用触发器，而是在插入时在sql语句中调用序列，例如
　　insert into TEST values(SEQ_TEST.nextval, ……) ;

2.8常见的ORCALE的面试试题

1. 解释冷备份和热备份的不同点以及各自的优点
   解答：热备份针对归档模式的数据库，在数据库仍旧处于工作状态时进行备份。而冷备份指在数据库关闭后，进行备份，适用于所有模式的数据库。热备份的优点在于当备份时，数据库仍旧可以被使用并且可以将数据库恢复到任意一个时间点。冷备份的优点在于它的备份和恢复操作相当简单，并且由于冷备份的数据库可以工作在非归档模式下，数据库性能会比归档模式稍好。（因为不必将archive log写入硬盘）

2. 你必须利用备份恢复数据库，但是你没有控制文件，该如何解决问题呢？
   解答：重建控制文件，用带backup control file 子句的recover 命令恢复数据库。

3. 如何转换init.ora到spfile？
   解答：使用create spfile from pfile 命令。

4. 解释data block ， extent 和 segment的区别（这里建议用英文术语）
   解答：data block是数据库中最小的逻辑存储单元。当数据库的对象需要更多的物理存储空间时，连续的data block就组成了extent . 一个数据库对象拥有的所有extents被称为该对象的segment.

5. 给出两个检查表结构的方法
   解答：1.describe命令
   2.dbms_metadata.get_ddl 包

6. 怎样查看数据库引擎的报错
   解答：alert log.

7. 比较truncate和0delete 命令
   解答：两者都可以用来删除表中所有的记录。区别在于：truncate是ddl操作，它移动hwk，不需要 rollback segment .而0delete是dml操作， 需要rollback segment 且花费较长时间。

8. 使用索引的理由
   解答：快速访问表中的data block

9. 给出在star schema中的两种表及它们分别含有的数据
   解答：fact tables 和dimension tables. fact table 包含大量的主要的信息而 dimension tables 存放对fact table 某些属性描述的信息

10. fact table上需要建立何种索引？
    解答：位图索引（bitmap index）

11. 给出两种相关约束？
    解答：主键和外键

12. 如何在不影响子表的前提下，重建一个母表
    解答：子表的外键强制实效，重建母表，激活外键

13. 解释归档和非归档模式之间的不同和它们各自的优缺点
    解答：归档模式是指你可以备份所有的数据库 transactions并恢复到任意一个时间点。非归档模式则相反，不能恢复到任意一个时间点。但是非归档模式可以带来数据库性能上的少许提高。

14. 如何建立一个备份控制文件？
    解答：alter database backup control file to trace.

15. 给出数据库正常启动所经历的几种状态 ？
    解答：startup nomount 数据库实例启动 startup mount 数据库装载 startup open 数据库打开

16. 哪个column可以用来区别v$视图和gv$视图？
    解答： inst_id 指明集群环境中具体的某个instance .

17. 如何生成explain plan？
    解答：运行utlxplan.sql. 建立plan 表针对特定sql语句，使用 explain plan set statement_id = ’tst1’ into plan_table 运行
    utlxplp.sql 或 utlxpls.sql察看explain plan

18. 如何增加buffer cache的命中率？
    解答：在数据库较繁忙时，适用buffer cache advisory 工具，查询v$db_cache_advice . 如果有必要更改，可以使用 alter system set db_cache_size 命令

19. ora-01555的应对方法？
    解答：具体的出错信息是snapshot too old within rollback seg ， 通常可以通过增大rollback seg来解决问题。当然也需要察看一下具体造成错误的sql文本

20. 解释$oracl{e}_{h}ome和$oracle_base的区别？
    解答：oracle_base是oracle的根目录，oracle_home是oracle产品的目录。

最后

以上就是清爽电脑最近收集整理的关于Sql学习笔记：试题库1的全部内容，更多相关Sql学习笔记内容请搜索靠谱客的其他文章。