一、公用信息
1、数据结构
（1）员工信息表 EMP
字段名称 注释 数据类型 是否主键 是否唯一索引 字典项
EMPNO 员工编号 NUMBER(4) 是 是
ENAME 员工姓名 VARHCHAR2(10)
JOB 岗位 VARHCHAR2(9)
MGR 主管编号 NUMBER(4)
HIREDATE 入职时间 DATE
SAL 工资 NUMBER(7,2)
COMM 通讯费 NUMBER(7,2)
DEPTNO 部门编号 NUMBER(2)
（2）部门信息表 DEPT
字段名称 注释 数据类型 是否主键 是否唯一索引 字典项
DEPTNO 部门编号 NUMBER(2)
DNAME 部门名称 VARHCHAR2(14)
LOC 位置 VARHCHAR2(13)
2、数据说明
（1）员工信息表数据
序号 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1 7566 JONES MANAGER 7839 1981/4/2 2975.00 20
2 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
3 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
4 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
5 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
6 7839 KINTURNERG PRESIDENT 1981/11/17 5000.00 10
7 7844 TURNER SALESMAN 7698 1981/9/8 2600.00 30
8 7902 FORD ANALYST 7566 1981/12/3 3000.00 20
9 1145 SAMS AAA 7788 2010/7/12 17:46:13 2500.00 300.00 10
10 1156 SAKL AAA 1145 2010/7/12 17:46:43 2500.00 300.00 20
（2）部门信息表数据
序号 DEPTNO DNAME LOC
1 10 accounting new york
2 20 research dalian
3 30 sales nanjing
二、考试题目
1、数据库操作（10分）
考察知识点：表及表空间的建立及维护、数据导入导出、建立用户、权限管理
（1）建立SDD用户，默认表空间为SDD，授权给SDD用户对数据库中的表进行增删改查；
（2）按数据结构要求，建立员工信息表、部门信息表，要求存放在SDD表空间下，将scott用户下emp、dept表的数据复制到当前用户下；
（3）使用sql*plus登录数据库，导出员工信息表、部门信息表的表结构及数据，分别导出到文件bak.dmp，导入到当前用户下。
（4）给部门信息表增加主键，以部门编号为主键。
（5）建立SDDTMP用户，授权对SDD用户下emp表的查询功能；
2、常用查询（10分）
考察知识点：常用统计分析函数的运用、模糊查询、树结构查询
（1）按部门分组查询员工基本信息；
（2）查询research部门下工资最高的人；
（3）展示每个部门收入排名前三位的员工信息（员工编号、员工姓名、部门名称、收入总和），收入=工资+通讯费；
（4）展示所有通讯费为空的员工信息（员工编号、员工姓名）；
（5）统计出平均工资高于3000的部门有哪些；
（6）统计在1981年4月至9月间入职了多少员工；
（7）共有多少名姓名以“S”开头的员工；
（8）统计员工人数高于各部门平均人数的部门；
（9）实现依据员工编号查询出所有上级信息的功能（注意，不仅直接主管）；
（10）实现依据员工编号查询出所有下级信息的功能（注意，不仅直属下级）。
3、程序设计I（20分）
考试知识点：包及过程的维护、游标、异常处理、事务处理、PL/SQL中逻辑处理
建立包及相应存储过程，分别实现员工信息的增、删、改、查操作。
（1）全部操作均要求能处理系统异常及自定义异常，对于失败的操作能够给予用户提示信息；
（2）其中查询操作要求以变量（游标变量、普调变量）的形式返回查询结果集；
（3）实现特定部门下员工信息分页查询（员工编号、员工姓名、部门名称，按入职日期升序排列，以游标变量形式返回查询结果）；
（4）实现指定员工的个人信息查询（员工编号、员工姓名、部门名称、工资、通讯费、岗位、工作地、入职时间）
（5）实现员工信息新增功能，要求检验部门信息的合理性；
（6）实现依据员工编号，修改工资、通讯费及主管编号的修改，
A、依据岗位不同按如下比例调整员工工资：
PRESIDENT 增加10%
MANAGER 增加20%
其余 增加 15%
B、岗位为SALESMAN的员工，可调整通讯费；
C、岗位为PRESIDENT的员工，不允许调整主管编号。
（7）实现依据员工编号，删除员工信息的功能。
（8）建立日志功能，在异常发生时能够记录日志（注意，事务要独立），日志表结构如下
字段名称 注释 数据类型 是否主键 是否唯一索引 字典项
INFONO 日志编号 NUMBER(9)
INFO 日志内容 VARHCHAR2(400)
INFODATE 日期 DATE
MODEL 模块名称 VARHCHAR2(35)
4、程序设计II（20分）
任务：从某一账户转出一定的金额到另一个账户。
Task: Transfer money from one account to another
（1）、业务说明
确定转出账户没有被锁。
确定转入账户的存在。
确定转入账户没有被锁。
确定转出账户中有足够余额满足转账
确定转出账户在转账后满足冻结额的要求
确定在转出账户上成功地扣去了转账的金额
确定在转入账户上成功地加上了转账的金额
上述的任何一个步骤失败，都会导致Transaction失败。用户应该得到一个很清晰的提示并记录日志，指出在哪个步骤出了问题，而不是简单地给一个提示说：“交易失败”。（2）表结构说明：Acct Locking Holding
字段名称 描述 字段名称 描述 字段名称 描述
Acct_No 账号 Acct_No 账号 Acct_No 账号
Balance 余额 Type 锁定类型 Amount 冻结金额
Acct: 包括所有的账户及其余额,
Locking: 包括所有的被锁定的账户. 账户一旦锁定，就不能做任何交易.
Holding: 被冻结的账户及其冻结的金额。被冻结的账户仍可做交易，但要保证交易后的余额大于冻结金额.
（3）建立包及相应存储过程完成以上功能的设计与编码，注意结构的划分：
A、参考入参:
转出账户的账号
转入账户的账号
1、数据结构
（1）员工信息表 EMP
字段名称 注释 数据类型 是否主键 是否唯一索引 字典项
EMPNO 员工编号 NUMBER(4) 是 是
ENAME 员工姓名 VARHCHAR2(10)
JOB 岗位 VARHCHAR2(9)
MGR 主管编号 NUMBER(4)
HIREDATE 入职时间 DATE
SAL 工资 NUMBER(7,2)
COMM 通讯费 NUMBER(7,2)
DEPTNO 部门编号 NUMBER(2)
（2）部门信息表 DEPT
字段名称 注释 数据类型 是否主键 是否唯一索引 字典项
DEPTNO 部门编号 NUMBER(2)
DNAME 部门名称 VARHCHAR2(14)
LOC 位置 VARHCHAR2(13)
2、数据说明
（1）员工信息表数据
序号 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
1 7566 JONES MANAGER 7839 1981/4/2 2975.00 20
2 7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
3 7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
4 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
5 7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
6 7839 KINTURNERG PRESIDENT 1981/11/17 5000.00 10
7 7844 TURNER SALESMAN 7698 1981/9/8 2600.00 30
8 7902 FORD ANALYST 7566 1981/12/3 3000.00 20
9 1145 SAMS AAA 7788 2010/7/12 17:46:13 2500.00 300.00 10
10 1156 SAKL AAA 1145 2010/7/12 17:46:43 2500.00 300.00 20
（2）部门信息表数据
序号 DEPTNO DNAME LOC
1 10 accounting new york
2 20 research dalian
3 30 sales nanjing
二、考试题目
1、数据库操作（10分）
考察知识点：表及表空间的建立及维护、数据导入导出、建立用户、权限管理
（1）建立SDD用户，默认表空间为SDD，授权给SDD用户对数据库中的表进行增删改查；
（2）按数据结构要求，建立员工信息表、部门信息表，要求存放在SDD表空间下，将scott用户下emp、dept表的数据复制到当前用户下；
（3）使用sql*plus登录数据库，导出员工信息表、部门信息表的表结构及数据，分别导出到文件bak.dmp，导入到当前用户下。
（4）给部门信息表增加主键，以部门编号为主键。
（5）建立SDDTMP用户，授权对SDD用户下emp表的查询功能；
2、常用查询（10分）
考察知识点：常用统计分析函数的运用、模糊查询、树结构查询
（1）按部门分组查询员工基本信息；
（2）查询research部门下工资最高的人；
（3）展示每个部门收入排名前三位的员工信息（员工编号、员工姓名、部门名称、收入总和），收入=工资+通讯费；
（4）展示所有通讯费为空的员工信息（员工编号、员工姓名）；
（5）统计出平均工资高于3000的部门有哪些；
（6）统计在1981年4月至9月间入职了多少员工；
（7）共有多少名姓名以“S”开头的员工；
（8）统计员工人数高于各部门平均人数的部门；
（9）实现依据员工编号查询出所有上级信息的功能（注意，不仅直接主管）；
（10）实现依据员工编号查询出所有下级信息的功能（注意，不仅直属下级）。
3、程序设计I（20分）
考试知识点：包及过程的维护、游标、异常处理、事务处理、PL/SQL中逻辑处理
建立包及相应存储过程，分别实现员工信息的增、删、改、查操作。
（1）全部操作均要求能处理系统异常及自定义异常，对于失败的操作能够给予用户提示信息；
（2）其中查询操作要求以变量（游标变量、普调变量）的形式返回查询结果集；
（3）实现特定部门下员工信息分页查询（员工编号、员工姓名、部门名称，按入职日期升序排列，以游标变量形式返回查询结果）；
（4）实现指定员工的个人信息查询（员工编号、员工姓名、部门名称、工资、通讯费、岗位、工作地、入职时间）
（5）实现员工信息新增功能，要求检验部门信息的合理性；
（6）实现依据员工编号，修改工资、通讯费及主管编号的修改，
A、依据岗位不同按如下比例调整员工工资：
PRESIDENT 增加10%
MANAGER 增加20%
其余 增加 15%
B、岗位为SALESMAN的员工，可调整通讯费；
C、岗位为PRESIDENT的员工，不允许调整主管编号。
（7）实现依据员工编号，删除员工信息的功能。
（8）建立日志功能，在异常发生时能够记录日志（注意，事务要独立），日志表结构如下
字段名称 注释 数据类型 是否主键 是否唯一索引 字典项
INFONO 日志编号 NUMBER(9)
INFO 日志内容 VARHCHAR2(400)
INFODATE 日期 DATE
MODEL 模块名称 VARHCHAR2(35)
4、程序设计II（20分）
任务：从某一账户转出一定的金额到另一个账户。
Task: Transfer money from one account to another
（1）、业务说明
确定转出账户没有被锁。
确定转入账户的存在。
确定转入账户没有被锁。
确定转出账户中有足够余额满足转账
确定转出账户在转账后满足冻结额的要求
确定在转出账户上成功地扣去了转账的金额
确定在转入账户上成功地加上了转账的金额
上述的任何一个步骤失败，都会导致Transaction失败。用户应该得到一个很清晰的提示并记录日志，指出在哪个步骤出了问题，而不是简单地给一个提示说：“交易失败”。（2）表结构说明：Acct Locking Holding
字段名称 描述 字段名称 描述 字段名称 描述
Acct_No 账号 Acct_No 账号 Acct_No 账号
Balance 余额 Type 锁定类型 Amount 冻结金额
Acct: 包括所有的账户及其余额,
Locking: 包括所有的被锁定的账户. 账户一旦锁定，就不能做任何交易.
Holding: 被冻结的账户及其冻结的金额。被冻结的账户仍可做交易，但要保证交易后的余额大于冻结金额.
（3）建立包及相应存储过程完成以上功能的设计与编码，注意结构的划分：
A、参考入参:
转出账户的账号
转入账户的账号
（1）建立SDD用户，默认表空间为SDD，授权给SDD用户对数据库SCOTT用户中的表进行增删改查；
CONN / AS SYSDBA
CREATE TABLESPACE SDD DATAFILE 'D:\oracle\product\10.2.0\oradata\tbwora\sdd01.dbf' SIZE 1024M; -- 创建SDD表空间
CREATE USER SDD DEFAULT TABLESPACE SDD IDENTIFIED BY SDD; -- 创建SDD用户，并设置默认表空间为SDD，密码为SDD
GRANT RESOURCE, CREATE SESSION TO SDD; -- 授予SDD用户 创建会话及RESOURCE角色 的权限
GRANT SELECT, UPDATE, DELETE ON SCOTT.EMP TO SDD; -- 授予SDD用户对SCOTT用户的EMP表的SELECT、UPDATE、DELETE 权限
GRANT SELECT, UPDATE, DELETE ON SCOTT.DEPT TO SDD; -- 授予SDD用户对SCOTT用户的DEPT表的SELECT、UPDATE、DELETE 权限ALTER USER SDD QUOTA UNLIMITED ON SDD; -- 分配SDD用户对SDD表空间的使用限额(不限制使用空间大小，可以无限使用)
（2）按数据结构要求，建立员工信息表、部门信息表，要求存放在SDD表空间下，将scott用户下emp、dept表的数据复制到当前用户下；
CONN SDD/SDD-- 创建 "SDD"."DEPT" 表
CREATE TABLE "SDD"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO") )
TABLESPACE "SDD";-- 为表 "SDD"."DEPT" 添加注释
COMMENT ON TALBE "SDD"."DEPT" IS '部门信息表';
COMMENT ON COLUMN "SDD"."DEPT".DEPTNO IS '部门编号';
COMMENT ON COLUMN "SDD"."DEPT".DNAME IS '部门名称';
COMMENT ON COLUMN "SDD"."DEPT".LOC IS '位置';-- 创建 "SDD"."EMP" 表
CREATE TABLE "SDD"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"),
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SDD"."DEPT" ("DEPTNO")
)
TABLESPACE "SDD";-- 为表 "SDD"."EMP" 添加注释
COMMENT ON TABLE "SDD"."EMP" IS '员工信息表';
COMMENT ON COLUMN "SDD"."EMP"."EMPNO" IS '员工编号';
COMMENT ON COLUMN "SDD"."EMP"."ENAME" IS '员工姓名';
COMMENT ON COLUMN "SDD"."EMP"."JOB" IS '岗位';
COMMENT ON COLUMN "SDD"."EMP"."MGR" IS '主管编号';
COMMENT ON COLUMN "SDD"."EMP"."HIREDATE" IS '入职时间';
COMMENT ON COLUMN "SDD"."EMP"."SAL" IS '工资';
COMMENT ON COLUMN "SDD"."EMP"."COMM" IS '通讯费';
COMMENT ON COLUMN "SDD"."EMP"."DEPTNO" IS '部门编号';-- 复制表 SCOTT.DEPT 的数据 到 SDD.DEPT
INSERT INTO SDD.DEPT(DEPTNO,DNAME,LOC)
SELECT DEPTNO,DNAME,LOC
FROM SCOTT.DEPT;-- 复制表 SCOTT.EMP 的数据 到 SDD.EMP
INSERT INTO SDD.EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
FROM SCOTT.EMP;