文字性习题答案参见相应章节内容第1章习题1D A C C第2章习题1执行edit命令打开文本编辑器；使用column命令设置列显示宽度习题2CONN scott/tiger@orclSELECT * FROM tab; --本题严谨一些的答案是查询ALL_OBJECTS视图，带条件查询习题3@d:\init_data\init_data.sql习题4SET PAGESIZE 24SET LINESIZE 100COLUMN sal FORMA T $99,990.00第3章习题1D B习题2CONN stu01/stu01pwd@orclCOLUMN USERNAME FORMA T A10SELECT username,granted_role FROM user_role_privs;习题5CREA TE SESSION 系统权限习题11CONN scott/tiger@orclCOLUMN GRANTEE FORMA T A10SELECT grantee,table_name,grantor,privilege,grantableFROM user_tab_privs_made;习题12CONN system/systempwd@orclCOLUMN GRANTEE FORMA T A22SELECT grantee,privilege,admin_option FROM dba_sys_privsORDER BY grantee,privilege;SELECT grantee,granted_role FROM dba_role_privs;使用REVOKE命令回收不必要的系统权限和角色。

\第4章习题8CONN /@cemerp AS SYSDBAALTER USER scott QUOTA 10M ON USERS;习题9CONN system/systempwd@orclCREA TE TABLESPACE data_ts1DA TAFILE '%ORACLE_BASE%\oradata\orcl\data_ts1.dbf' SIZE 10000M REUSE; CREA TE TEMPORARY TABLESPACE temp_ts1TEMPFILE '%ORACLE_BASE%\oradata\orcl\temp_ts1.dbf' SIZE 200M REUSE; CREA TE USER surtec IDENTIFIED BY surtecpwdDEFAULT TABLESPACE data_ts1 TEMPORARY TABLESPACE temp_ts1; GRANT CREA TE SESSION TO surtec;GRANT RESOURCE TO surtec;CONN surtec/surtecpwd@orcl--创建该用户的表emp……--授权给SIBGRANT SELECT ON emp TO sib;GRANT UPDA TE(flag) ON endowment_insurance TO sib;习题10CONN system/systempwd@orclCREA TE USER test01 IDENTIFIED BY test1234;GRANT CREA TE SESSION TO test01;GRANT CREA TE TABLESPACE TO test01;CONN test01/test1234@orclCREA TE TABLESPACE test_tsDA TAFILE '%ORACLE_BASE%\oradata\orcl\test_ts.dbf' SIZE 100KAUTOEXTEND ON NEXT 50K MAXSIZE 5M UNIFORM SIZE 50K;ALTER TABLESPACE test_tsADD DA TAFILE '%ORACLE_BASE%\oradata\orcl\test_ts2.dbf' SIZE 10M; ALTER DA TABASEDA TAFILE '%ORACLE_BASE%\oradata\orcl\test_ts2.dbf' RESIZE 15M ;习题11CONN system/systempwd@orclALTER TABLESPACE pur_tsADD DA TAFILE '%ORACLE_BASE%\oradata\orcl\pur_data.dbf' SIZE 800M; ALTER DA TABASEDA TAFILE '%ORACLE_BASE%\oradata\orcl\pur_data.dbf' RESIZE 2000M ; ALTER DA TABASE DA TAFILE '%ORACLE_BASE%\oradata\orcl\pur_data.dbf' AUTOEXTEND ON NEXT 30M;第5章习题1B A习题8(1)撤销表空间中不允许建立永久方案对象；将方案对象建立在数据表空间即可。

(2)在脱机表空间上不允许执行创建方案对象操作；将该表空间联机即可。

(3)使用ALTER USER修改用户在USERS表空间上的限额即可。

(4)使用ALTER USER修改用户，扩大在USERS表空间上的限额即可。

习题9CONN hr/hrpwd@orclCREA TE TABLE employees2 ASSELECT employee_id id, first_name, last_name, salary,department_id dept_idFROM employees;习题10CONN scott/tiger@orclALTER TABLE empADD (dept_id NUMBER(7));ALTER TABLE empADD CONSTRAINT emp_dept_id_fkFOREIGN KEY (dept_id) REFERENCES dept(deptno);习题11假定数据库用户为hr(1)CONN hr/hrpwd@orclCREA TE TABLE g_emp(EMPNO NUMBER(4) NOT NULL,ENAME V ARCHAR2(10),JOB V ARCHAR2(10),MGR NUMBER(4),HIREDA TE DA TE,SAL NUMBER(7,2),COMM NUMBER(7,2),DEPTNO NUMBER(3))PARTITION BY RANGE (sal)(PARTITION SAL_1000 V ALUES LESS THAN (1000) tablespace users,PARTITION SAL_2000 V ALUES LESS THAN (2000) tablespace users,PARTITION SAL_3000 V ALUES LESS THAN (3000) tablespace users,PARTITION SAL_4000 V ALUES LESS THAN (4000) tablespace users,PARTITION SAL_5000 V ALUES LESS THAN (5000) tablespace users,PARTITION SAL_max V ALUES LESS THAN (MAXV ALUE) tablespace users);(2)DESC g_emp(3)--创建表空间CONN system/systempwd@orclCREA TE TABLESPACE hiredate_tsDA TAFILE '%ORACLE_BASE%\oradata\orcl\hiredate_ts1.dbf' SIZE 1M REUSE; --创建表CONN hr/hrpwd@orclCREA TE INDEX emp_hiredate_indexON g_emp(HIREDA TE 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@orclCREA TE SEQUENCE id_seqINCREMENT BY 1START WITH 1MAXV ALUE 99999NOCACHENOCYCLE;(2)可以修改序列的定义；或者修改产品表主键的定义。

习题2参见例7.1。

习题4使用数据字典DBA_DB_LINKS习题5在总部建立指向各分店的数据库链接，然后在分店服务器建立实体化视图日志，在总店建立各分店个表的实体化视图。

第8章习题1A DBC B习题4(1)SELECT worker.ename ename, manager.ename managerFROM emp worker, emp managerWHERE worker.mgr=manager.empno;（2）SELECT worker.ename, worker.empno, worker.deptno, manager.enameFROM emp worker, emp managerWHERE worker.mgr=manager.empno AND worker.hiredate< manager.hiredate;（3）SELECT e.enameFROM emp e,dept dWHERE e.deptno=d.deptno AND upper(d.dname)='IT';（4）SELECT ename,empno,deptno, job, salFROM empWHERE 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 dWHERE e.deptno(+)= d.deptnoGROUP BY d.deptno;（6）SELECT d.deptno,d.dname,d.loc,e.numFROM (SELECT deptno,count(ename) numFROM empGROUP BY deptno) e, dept dWHERE e.deptno(+)= d.deptno;（7）SELECT job,min(sal+nvl(comm,0)) FROM emp GROUP BY job;（8）SELECT ename,deptno,minsalFROM emp,(SELECT min(sal) minsal FROM empWHERE job=upper('manager')) bWHERE emp.job=upper('manager') AND emp.sal=b.minsal;或者用内嵌视图，执行Top-N查询（9）SELECT ename , sal*12 Annual_salFROM empORDER BY Annual_sal ASC;习题5SELECT last_name, ROUND(MONTHS_BETWEEN(SYSDA TE, hire_date)) MONTHS_WORKEDFROM employeesORDER BY MONTHS_BETWEEN(SYSDA TE, hire_date);习题6SELECT st_name, e.hire_dateFROM employees e, employees daviesWHERE st_name = 'Davies'AND davies.hire_date < e.hire_date;习题8SELECT last_name,department_id,TO_CHAR(null)FROM employeesUNIONSELECT TO_CHAR(null),department_id,department_nameFROM 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_PRTSTRUDEFVALUES('WD010201','WD0102',1,'柜门','D',15,'扇');INSERT INTO T_PRTSTRUDEFVALUES('WD010202','WD0102',1,'床头柜身','D',65,'个');INSERT INTO T_PRTSTRUDEFVALUES('WD01020101','WD010201',1,'柜门板','M',13,'张'); INSERT INTO T_PRTSTRUDEFVALUES('WD01020102','WD010201',1,'拉手','M',2,'个'); COMMIT;--查询整个产品SELECT LEVEL 部件层次,SUBSTRB(LPAD(' ',2*(LEVEL-1))||PRTDESC,1,30) 产品结构, PRTPQTY 需求数量, PRTPRC 单价FROM T_PRTSTRUDEFSTART WITH PRTPNO IS NULLCONNECT BY PRIOR PRTNO = PRTPNO;执行结果如下：部件层次产品结构需求数量单价-------- ------------------------ ---------- ---------1 床 0 8502 床头 1 1002 床头柜 2 803 柜门 1 154 柜门板 1 134 拉手 1 23 床头柜身 1 65--查询柜门的子件SELECT LEVEL 部件层次,SUBSTRB(LPAD(' ',2*(LEVEL-1))||PRTDESC,1,30) 产品结构, PRTPQTY 需求数量, PRTPRC 单价FROM T_PRTSTRUDEFSTART WITH PRTNO='WD010201'CONNECT BY PRIOR PRTNO = PRTPNO;执行结果如下：部件层次产品结构需求数量单价-------- -------------------------- -------- --------1 柜门 1 152 柜门板 1 132 拉手 1 2--查询柜门的父件（反查）SELECT LEVEL 部件层次,SUBSTRB(LPAD(' ',2*(LEVEL-1))||PRTDESC,1,30) 产品结构, PRTPQTY 需求数量, PRTPRC 单价FROM T_PRTSTRUDEFSTART WITH PRTNO='WD010201'CONNECT BY PRIOR PRTPNO = PRTNO;执行结果如下：部件层次产品结构需求数量单价-------- ------------------------ ---------- --------1 柜门 1 152 床头柜 2 803 床 0 850第9章习题1A C习题2CONN scott/tiger@orclSET serveroutput ONCREATE OR REPLACE PROCEDURE check_salISCURSOR emp_cursor IS SELECT empno,ename,deptno,sal,job FROM emp;BEGINFOR emp_rec IN emp_cursor LOOPIF emp_rec.job = 'CLERK' AND emp_rec.sal NOT BETWEEN 1500 AND 2500 THENDBMS_OUTPUT.PUT_LINE(emp_rec.deptno||':'||emp_rec.e mpno||':'||emp_rec.ename| |':'||emp_rec.sal);ELSIF emp_rec.job = 'SALESMAN' AND emp_rec.sal NOT BETWEEN 800 AND 5000 THENDBMS_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 THENDBMS_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 THENDBMS_OUTPUT.PUT_LINE(emp_rec.deptno||':'||emp_rec.empno||':'||emp_rec.ename| |':'||emp_rec.sal);END IF;END LOOP;END;/EXECUTE check_sal习题3SET ECHO OFFSET VERIFY OFFDEFINE low_date = 01/01/1998DEFINE high_date = 01/01/1999SELECT last_name ||', '|| job_id 雇员职位, hire_dateFROM employeesWHERE hire_date BETWEEN TO_DATE('&low_date', 'MM/DD/YYYY')AND TO_DATE('&high_date', 'MM/DD/YYYY')/UNDEFINE low_dateUNDEFINE high_dateSET VERIFY ONSET ECHO ON习题4--创建表CREATE TABLE messages(results VARCHAR2(100));--本题答案DECLAREv_ename st_name%TYPE;v_sal employees.salary%TYPE := &p_sal;BEGINSELECT last_nameINTO v_enameFROM employeesWHERE salary = v_sal;INSERT INTO messages (results)VALUES (v_ename || ' - ' || v_sal);EXCEPTIONWHEN no_data_found THENINSERT INTO messages (results)VALUES ('No employee with a salary of '|| TO_CHAR(v_sal)); WHEN too_many_rows THENINSERT INTO messages (results)VALUES ('More than one employee with a salary of '||TO_CHAR(v_sal));WHEN others THENINSERT 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 ISv_fee NUMBER;BEGINSELECT SUM(charge+NVL(late_fee,0))INTO v_feeFROM call_fee_accountWHERE pay_date = p_date;RETURN v_fee;END;/习题6可参考例9.20，该例子是删除（delete），本题中为修改（update）。