第1章
习题1
D A C C
第2章
习题1
执行edit命令打开文本编辑器；
使用column命令设置列显示宽度
习题2
CONN scott/tiger@orcl
SELECT * FROM tab; --本题严谨一些的答案是查询ALL_OBJECTS视图，带条件查询
习题3
@d:\\init_data\\init_data.sql
习题4
SET PAGESIZE 24
SET LINESIZE 100
COLUMN sal FORMAT $99,990.00
第3章
习题1
D B
习题2
CONN stu01/stu01pwd@orcl
COLUMN USERNAME FORMAT A10
SELECT username,granted_role FROM user_role_privs;
习题5
CREATE SESSION 系统权限
习题11
CONN scott/tiger@orcl
COLUMN GRANTEE FORMAT A10
SELECT grantee,table_name,grantor,privilege,grantable
FROM user_tab_privs_made;
习题12
CONN system/systempwd@orcl
COLUMN GRANTEE FORMAT A22
SELECT grantee,privilege,admin_option FROM dba_sys_privs
ORDER BY grantee,privilege;
SELECT grantee,granted_role FROM dba_role_privs;
使用REVOKE命令回收不必要的系统权限和角色。\
第4章
习题8
CONN /@cemerp AS SYSDBA
ALTER USER scott QUOTA 10M ON USERS;
习题9
CONN system/systempwd@orcl
CREATE TABLESPACE data_ts1
DATAFILE '%ORACLE_BASE%\\oradata\\orcl\data_ts1.dbf' SIZE 10000M REUSE;
CREATE TEMPORARY TABLESPACE temp_ts1
TEMPFILE '%ORACLE_BASE%\\oradata\\orcl\temp_ts1.dbf' SIZE 200M REUSE;
CREATE USER surtec IDENTIFIED BY surtecpwd
DEFAULT TABLESPACE data_ts1 TEMPORARY TABLESPACE temp_ts1;
GRANT CREATE SESSION TO surtec;
GRANT RESOURCE TO surtec;
CONN surtec/surtecpwd@orcl
--创建该用户的表emp……
--授权给SIB
GRANT SELECT ON emp TO sib;
GRANT UPDATE(flag) ON endowment_insurance TO sib;
习题10
CONN system/systempwd@orcl
CREATE USER test01 IDENTIFIED BY test1234;
GRANT CREATE SESSION TO test01;
GRANT CREATE TABLESPACE TO test01;
CONN test01/test1234@orcl
CREATE TABLESPACE test_ts
DATAFILE '%ORACLE_BASE%\\oradata\\orcl\est_ts.dbf' SIZE 100K
AUTOEXTEND ON NEXT 50K MAXSIZE 5M UNIFORM SIZE 50K;
ALTER TABLESPACE test_ts
ADD DATAFILE '%ORACLE_BASE%\\oradata\\orcl\est_ts2.dbf' SIZE 10M;
ALTER DATABASE
DATAFILE '%ORACLE_BASE%\\oradata\\orcl\est_ts2.dbf' RESIZE 15M ;
习题11
CONN system/systempwd@orcl
ALTER TABLESPACE pur_ts
ADD DATAFILE '%ORACLE_BASE%\\oradata\\orcl\\pur_data.dbf' SIZE 800M;
ALTER DATABASE
DATAFILE '%ORACLE_BASE%\\oradata\\orcl\\pur_data.dbf' RESIZE 2000M ;
ALTER DATABASE DATAFILE '%ORACLE_BASE%\\oradata\\orcl\\pur_data.dbf'
AUTOEXTEND ON NEXT 30M;
第5章
习题1
B A
习题8
(1)撤销表空间中不允许建立永久方案对象；将方案对象建立在数据表空间即可。
(2)在脱机表空间上不允许执行创建方案对象操作；将该表空间联机即可。
(3)使用ALTER USER修改用户在USERS表空间上的限额即可。
(4)使用ALTER USER修改用户，扩大在USERS表空间上的限额即可。
习题9
CONN hr/hrpwd@orcl
CREATE TABLE employees2 AS
SELECT employee_id id, first_name, last_name, salary,
department_id dept_id
FROM employees;
习题10
CONN scott/tiger@orcl
ALTER TABLE emp
ADD (dept_id NUMBER(7));
ALTER TABLE emp
ADD CONSTRAINT emp_dept_id_fk
FOREIGN KEY (dept_id) REFERENCES dept(deptno);
习题11
假定数据库用户为hr
(1)
CONN hr/hrpwd@orcl
CREATE TABLE g_emp(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(10),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(3))
PARTITION BY RANGE (sal)
(PARTITION SAL_1000 VALUES LESS THAN (1000) tablespace users,
PARTITION SAL_2000 VALUES LESS THAN (2000) tablespace users,
PARTITION SAL_3000 VALUES LESS THAN (3000) tablespace users,
PARTITION SAL_4000 VALUES LESS THAN (4000) tablespace users,
PARTITION SAL_5000 VALUES LESS THAN (5000) tablespace users,
PARTITION SAL_max VALUES LESS THAN (MAXVALUE) tablespace users);
(2)
DESC g_emp
(3)
--创建表空间
CONN system/systempwd@orcl
CREATE TABLESPACE hiredate_ts
DATAFILE '%ORACLE_BASE%\\oradata\\orcl\\hiredate_ts1.dbf' SIZE 1M REUSE;
--创建表
CONN hr/hrpwd@orcl
CREATE INDEX emp_hiredate_index
ON g_emp(HIREDATE DESC)
TABLESPACE hiredate_ts;
(4)
ALTER TABLE g_emp DROP (COMM);
ALTER TABLE g_emp ADD(bonus NUMBER(7,2));
第6章
略
第7章
习题1
(1)
CONN scott/tiger@orcl
CREATE SEQUENCE id_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 99999
NOCACHE
NOCYCLE;
(2)可以修改序列的定义；或者修改产品表主键的定义。
习题2
参见例7.1。
习题4
使用数据字典DBA_DB_LINKS
习题5
在总部建立指向各分店的数据库链接，然后在分店服务器建立实体化视图日志，在总店建立各分店个表的实体化视图。
第8章
习题1
A D B C B
习题4
(1)
SELECT worker.ename ename, manager.ename manager
FROM emp worker, emp manager
WHERE worker.mgr=manager.empno;
（2）
SELECT worker.ename, worker.empno, worker.deptno, manager.ename
FROM emp worker, emp manager
WHERE worker.mgr=manager.empno AND worker.hiredate< manager.hiredate;
（3）
SELECT e.ename
FROM emp e,dept d
WHERE e.deptno=d.deptno AND upper(d.dname)='IT';
（4）
SELECT ename,empno,deptno, job, sal
FROM emp
WHERE sal>
(SELECT avg(sal)
FROM emp );
（5）
SELECT d.deptno, count(e.ename), avg(e.sal),
avg(months_between(sysdate,e.hiredate))
FROM emp e, dept d
WHERE e.deptno(+)= d.deptno
GROUP BY d.deptno;
（6）
SELECT d.deptno,d.dname,d.loc,e.num
FROM (SELECT deptno,count(ename) num
FROM emp
GROUP BY deptno) e, dept d
WHERE e.deptno(+)= d.deptno;
（7）
SELECT job,min(sal+nvl(comm,0)) FROM emp GROUP BY job;
（8）
SELECT ename,deptno,minsal
FROM emp,(SELECT min(sal) minsal FROM emp
WHERE job=upper('manager')) b
WHERE emp.job=upper('manager') AND emp.sal=b.minsal;
或者用内嵌视图，执行Top-N查询
（9）
SELECT ename , sal*12 Annual_sal
FROM emp
ORDER BY Annual_sal ASC;
习题5
SELECT last_name, ROUND(MONTHS_BETWEEN(SYSDATE, hire_date))
MONTHS_WORKED
FROM employees
ORDER BY MONTHS_BETWEEN(SYSDATE, hire_date);
习题6
SELECT e.last_name, e.hire_date
FROM employees e, employees davies
WHERE davies.last_name = 'Davies'
AND davies.hire_date < e.hire_date;
习题8
SELECT last_name,department_id,TO_CHAR(null)
FROM employees
UNION
SELECT TO_CHAR(null),department_id,department_name
FROM departments;
习题9
创建表的语句如下
CREATE TABLE T_PRTSTRUDEF(
PRTNO Varchar2(24), --物料号
PRTPNO varchar2(24), --父件号
PRTPQTY number(20,7), --需求数量
PRTDESC Varchar2(30), --物料名称
PRTTYPE Varchar2(1), --物料类型
PRTPRC Number(18,3), --单价，下一字段是计量单位
PRTUM Varchar2(8));
--插入示例数据，P-表示产品 D-表示部件 M-表示零件 （仅为了举例）
INSERT INTO T_PRTSTRUDEF VALUES('WD01','',0,'床','P',850,'张');
INSERT INTO T_PRTSTRUDEF VALUES('WD0101','WD01',1,'床头','D',100,'套');
INSERT INTO T_PRTSTRUDEF VALUES('WD0102','WD01',2,'床头柜','D',80,'个');
INSERT INTO T_PRTSTRUDEF
VALUES('WD010201','WD0102',1,'柜门','D',15,'扇');
INSERT INTO T_PRTSTRUDEF
VALUES('WD010202','WD0102',1,'床头柜身','D',65,'个');
INSERT INTO T_PRTSTRUDEF
VALUES('WD01020101','WD010201',1,'柜门板','M',13,'张');
INSERT INTO T_PRTSTRUDEF
VALUES('WD01020102','WD010201',1,'拉手','M',2,'个');
COMMIT;
--查询整个产品
SELECT LEVEL 部件层次,SUBSTRB(LPAD(' ',2*(LEVEL-1))||PRTDESC,1,30)
产品结构, PRTPQTY 需求数量, PRTPRC 单价
FROM T_PRTSTRUDEF
START WITH PRTPNO IS NULL
CONNECT BY PRIOR PRTNO = PRTPNO;
执行结果如下：
部件层次 产品结构 需求数量 单价
-------- ------------------------ ---------- ---------
1 床 0 850
2 床头 1 100
2 床头柜 2 80
3 柜门 1 15
4 柜门板 1 13
4 拉手 1 2
3 床头柜身 1 65
--查询柜门的子件
SELECT LEVEL 部件层次,SUBSTRB(LPAD(' ',2*(LEVEL-1))||PRTDESC,1,30)
产品结构, PRTPQTY 需求数量, PRTPRC 单价
FROM T_PRTSTRUDEF
START WITH PRTNO='WD010201'
CONNECT BY PRIOR PRTNO = PRTPNO;
执行结果如下：
部件层次 产品结构 需求数量 单价
-------- -------------------------- -------- --------
1 柜门 1 15
2 柜门板 1 13
2 拉手 1 2
--查询柜门的父件（反查）
SELECT LEVEL 部件层次,SUBSTRB(LPAD(' ',2*(LEVEL-1))||PRTDESC,1,30)
产品结构, PRTPQTY 需求数量, PRTPRC 单价
FROM T_PRTSTRUDEF
START WITH PRTNO='WD010201'
CONNECT BY PRIOR PRTPNO = PRTNO;
执行结果如下：
部件层次 产品结构 需求数量 单价
-------- ------------------------ ---------- --------
1 柜门 1 15
2 床头柜 2 80
3 床 0 850
第9章
习题1
A C
习题2
CONN scott/tiger@orcl
SET serveroutput ON
CREATE OR REPLACE PROCEDURE check_sal
IS
CURSOR emp_cursor IS SELECT empno,ename,deptno,sal,job FROM emp;
BEGIN
FOR emp_rec IN emp_cursor LOOP
IF emp_rec.job = 'CLERK' AND emp_rec.sal NOT BETWEEN 1500 AND 2500 THEN
DBMS_OUTPUT.PUT_LINE(emp_rec.deptno||':'||emp_rec.empno||':'||emp_rec.ename||':'||emp_rec.sal);
ELSIF emp_rec.job = 'SALESMAN' AND emp_rec.sal NOT BETWEEN 800 AND 5000 THEN
DBMS_OUTPUT.PUT_LINE(emp_rec.deptno||':'||emp_rec.empno||':'||emp_rec.ename||':'||emp_rec.sal);
ELSIF emp_rec.job = 'MANAGER' AND emp_rec.sal NOT BETWEEN 3000 AND 4500 THEN
DBMS_OUTPUT.PUT_LINE(emp_rec.deptno||':'||emp_rec.empno||':'||emp_rec.ename||':'||emp_rec.sal);
ELSIF emp_rec.job = 'ANALYST' AND emp_rec.sal NOT BETWEEN 2500 AND 3500 THEN
DBMS_OUTPUT.PUT_LINE(emp_rec.deptno||':'||emp_rec.empno||':'||emp_rec.ename||':'||emp_rec.sal);
END IF;
END LOOP;
END;
/
EXECUTE check_sal
习题3
SET ECHO OFF
SET VERIFY OFF
DEFINE low_date = 01/01/1998
DEFINE high_date = 01/01/1999
SELECT last_name ||', '|| job_id 雇员职位, hire_date
FROM employees
WHERE hire_date BETWEEN TO_DATE('&low_date', 'MM/DD/YYYY')
AND TO_DATE('&high_date', 'MM/DD/YYYY')
/
UNDEFINE low_date
UNDEFINE high_date
SET VERIFY ON
SET ECHO ON
习题4
--创建表
CREATE TABLE messages(
results VARCHAR2(100));
--本题答案
DECLARE
v_ename employees.last_name%TYPE;
v_sal employees.salary%TYPE := &p_sal;
BEGIN
SELECT last_name
INTO v_ename
FROM employees
WHERE salary = v_sal;
INSERT INTO messages (results)
VALUES (v_ename || ' - ' || v_sal);
EXCEPTION
WHEN no_data_found THEN
INSERT INTO messages (results)
VALUES ('No employee with a salary of '|| TO_CHAR(v_sal));
WHEN too_many_rows THEN
INSERT INTO messages (results)
VALUES ('More than one employee with a salary of '||
TO_CHAR(v_sal));
WHEN others THEN
INSERT INTO messages (results)
VALUES ('Some other error occurred.');
END;
/
习题5
--创建相关表
CREATE TABLE call_fee_account(
telno VARCHAR2(20),
pay_date DATE,
charge NUMBER(7,2),
late_fee NUMBER(7,2));
--创建函数
CREATE OR REPLACE FUNCTION fee(p_date DATE) RETURN NUMBER IS
v_fee NUMBER;
BEGIN
SELECT SUM(charge+NVL(late_fee,0))
INTO v_fee
FROM call_fee_account
WHERE pay_date = p_date;
RETURN v_fee;
END;
/
习题6
可参考例9.20，该例子是删除（delete），本题中为修改（update）。
习题7
--创建存储过程
CREATE or REPLACE PROCEDURE show_dept_emp IS
CURSOR cur_dept is select deptno,dname from dept order by deptno;
CURSOR cur_emp(p_no number) is select ename,nvl(sal,0),nvl(comm,0) from emp where deptno=p_no;
v_deptno number(3);
v_dname varchar2(20);
v_ename varchar2(10);
v_sal number(7,2);
v_comm number(7,2);
BEGIN
--打开取部门数据的游标
OPEN cur_dept;
LOOP
FETCH cur_dept INTO v_deptno,v_dname;
EXIT WHEN cur_dept%NOTFOUND;
dbms_output.put_line(rpad(v_deptno,3,' ') || v_dname);
--打开取该部门员工信息的游标
OPEN cur_emp(v_deptno);
LOOP
FETCH cur_emp INTO v_ename,v_sal,v_comm;
EXIT WHEN cur_emp%NOTFOUND;
dbms_output.put_line(' '||rpad(v_ename,10,' ')||rpad(v_sal,10,
' ')||v_comm);
END LOOP;
CLOSE cur_emp;
--进入下一轮外层循环，取下一个部门的数据
END LOOP;
CLOSE cur_dept;
END;
/
--打开服务器输出开关,不允许压缩前导空格
SET SERVEROUTPUT ON
SET SERVEROUTPUT ON FORMAT WRAPPED
--执行定义的存储过程，显示结果
execute show_dept_emp;
习题8
SET 操作通常只对一个连接会话有效。
将SET SERVEROUTPUT ON移到连接数据库语句之后即可。
习题9
SPOOL d:\\data\\source_code.txt
参见例9.26 查看当前用户定义的对象。
SPOOL OFF
习题10
答案见9.10节 数据对象参照依赖与子程序重新编译。
第10章
习题1,2
略，参见书上例题。
习题3
--控制文件tel_zone.ctl的内容如下
load data
infile 'd:\\data.txt'
into table tel_zone
fields terminated by ',' optionally enclosed by '"'
(id, area, remark)
--对应的SQL*Loader命令为
SQLLDR scott/tiger@orcl control=d:\el_zone.ctl
第11章
习题1
B C
习题12
（1）提示“连接到空闲例程”，说明数据库例程处于启动但未装载数据文件状态；
（2）如果数据库是正常可用的，在该状态下下一步一般应发布以下命令以装载数据库，打开例程：
ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;
（3）如果是在创建数据库期间，下一步应该发布CREATE DATABASE命令执行创建数据库操作。
习题15,16
可结合本章相关章节与第1章第7节的数据字典、第2章的SQL*Plus命令操作。下载本文
