untitled

Oracle SQL Oracle SQL OCP Oracle Oracle8 CIP Oracle/SQL /. 2004 ISBN 7-302-07951-X... Oracle. TP311.138 CIP 2004 001611 http://www.tup.com.cn 100084 010-62770175 185 260 27.25 602 2004 2 1 2004 2 1 ISBN 7-302-07951-X/TP 5774 1 5000 34.00 010-62776969

SQL Structured Query Language Relational Database Management Systems Oracle WTO Oracle Oracle SQL Oracle SQL Oracle DBA Developer Oracle SQL OCP Oracle Certified Professional Oracle SQL Oracle DBA Developer Oracle SQL American National Standards Institute - ANSI International Standards Organization - ISO SQL Oracle SQL SQL Oracle OCP OCP Oracle Student Guide Oracle Oracle Student Guide Oracle Oracle GZ Comtech NZ LTD New Zealand Institute of Science and Technology Ltd - Oracle DBA PC PC CPU 300 MHz 64MB 8GB CD Oracle Oracle

UNIX Oracle Etake Technology Inc Unitec Institute of Technology - IT Oracle IT 5% 95% Oracle SQL OCP Relational Database Management Systems SQL Structured Query Language DML DDL DCL SQL Oracle8i Oracle9i Oracle Oracle Oracle Oracle Windows Oracle Windows II

Oracle Windows Windows Oracle Oracle IT Oracle SQL Oracle SQL SQL Oracle Unix Windows SQL Oracle Oracle III

SQL IT Oracle Oracle Oracle Oracle Oracle SQL 3 Oracle IT Oracle Oracle Oracle Oracle Oracle IV

Oracle 8.0.4 8.0.5 8.1.5 8.1.7 Oracle 9.0.1 Oracle Oracle SQL*PLUS Oracle Oracle sql_minghe@yahoo.com.cn Oracle SQL 2004 3 V

...1 1...4 1.1...4 1.2...5 1.3...7 1.4...9 1.5 SQL...10 1.6 SQL...12 1.7...13 1.8 DISTINCT...15 1.9...17 1.10...17 2...18 2.1...18 2.2 operators...19 2.3 BETWEEN AND operators...19 2.4 SQL...21 2.5 IN operators...22 2.6 LIKE operators...23 2.7 escape...25 2.8 ORDER BY...27 2.9 ORDER BY...28 2.10 ORDER BY...30 2.11 ORDER BY...31 2.12 ORDER BY SELECT...31 2.13...32 2.14...33 3 SQL*PLUS...34 3.1 DESC[RIBE]...34 3.2 SET LINE[SIZE]{80 n}...35 3.3 L LIST n text...36

3.4 / RUN...38 3.5 n A[PPEND]...38 3.6 del...41 3.7 C[HANGE]...42 3.8...45 3.9...47 3.10...49 3.11 SPOOL...49 3.12...50 4...52 4.1...52 4.2...52 4.3...53 4.4...58 4.5...59 4.6...62 4.7...65 4.8 ROUND TRUNC...68 4.9...71 4.10...71 4.11...79 5 NULL...80 5.1 NULL...80 5.2 NULL...81 5.3 NULL...83 5.4...85 5.5...88 5.6 AND OR BETWEEN AND IN...89 5.7 NVL...91 5.8 DECODE...93 5.9...94 5.10 Oracle9i...96 5.11...101 6...102 VIII

6.1...102 6.2 COUNT...102 6.3 AVE SUM...103 6.4 MIN MAX...104 6.5 GROUP BY...105 6.6 GROUP BY...105 6.7 GROUP BY...106 6.8 GROUP BY...107 6.9 HAVING...108 6.10...110 6.11...110 6.12 NVL...111 6.13 NVL...112 6.14...113 6.15...113 7...114 7.1 Normalization...114 7.2 Primary Key Entity Integrity...115 7.3 1NF...115 7.4...116 7.5 Foreign Key Referential Integrity...117 7.6 2NF...118 7.7 3NF...118 7.8...120 7.9...120 7.10...120 7.11...122 7.12...123 7.13 Self join...125 7.14...126 7.15...127 7.16...129 7.17 SQL:1999...130 7.18 SQL:1999...130 7.19 USING...131 IX

7.20 ON...132 7.21 ON...132 7.22...134 7.23...135 7.24...136 7.25...139 8...140 8.1...140 8.2 WHERE...141 8.3 HAVING...144 8.4 FROM...145 8.5...145 8.6 IN...146 8.7 ALL...147 8.8 ANY...148 8.9 NULL...150 8.10...152 8.11 Pairwise Comparison...152 8.12 Nonpairwise Comparison...153 8.13...154 8.14...155 9 SQL*PLUS...156 9.1 SQL*PLUS...156 9.2 SQL*PLUS ECHO...156 9.3 SQL*PLUS FEEDBACK...159 9.4 SQL*PLUS...160 9.5 SQL*PLUS COLUMN...160 9.6 SQL*PLUS...166 9.7...169 9.8...171 9.9...173 9.10...177 9.11...177 10...179 X

10.1...179 10.2...180 10.3...183 10.4...185 10.5...186 10.6...188 10.7...195 10.8...197 10.9...199 10.10...202 10.11...206 11...207 11.1...207 11.2 &...207 11.3...210 11.4 &&...213 11.5...215 11.6 DEFINE...216 11.7 ACCEPT...218 11.8 ACCEPT HIDE...221 11.9 ACCEPT...224 11.10...224 11.11...224 12...226 12.1...226 12.2 INSERT...227 12.3 INSERT NULL...229 12.4...232 12.5...233 12.6 INSERT...234 12.7 ACCEPT INSERT...236 12.8 UPDATE...238 12.9...241 12.10...243 XI

12.11 DELETE...245 12.12 DELETE...247 12.13...248 12.14 Transactions...249 12.15 Oracle Transactions...250 12.16 COMMIT ROLLBACK...250 12.17 DDL DCL...252 12.18 SQL*PLUS...255 12.19 AUTOCOMMIT...257 12.20...258 12.21...259 13 Indexes and Constraints...260 13.1...260 13.2...260 13.3...262 13.4...264 13.5...265 13.6 Oracle...267 13.7...269 13.8...272 13.9 NOT NULL...273 13.10...276 13.11 UNIQUE...278 13.12 CHECK...281 13.13 PRIMARY KEY...285 13.14 FOREIGN KEY...289 13.15 FOREIGN KEY INSERT...293 13.16 FOREIGN KEY DELETE...294 13.17 FOREIGN KEY UPDATE...295 13.18 FOREIGN KEY DDL...298 13.19 FOREIGN KEY ON DELETE SET NULL ON DELETE CASCADE...300 13.20...306 13.21...311 13.22...312 XII

14 Views...314 14.1 Views...314 14.2 Views...315 14.3 Views...317 14.4 Views...320 14.5 Oracle Views...321 14.6 Views DML...322 14.7 Views WITH CHECK OPTION...323 14.8 WITH READ ONLY...326 14.9 Views...328 14.10 Inline Views...330 14.11 n / Top n queries/analysis...331 14.12...332 15 Sequence Synonym...334 15.1 Sequence...334 15.2 Sequence...334 15.3 Sequence...335 15.4 Sequence...337 15.5 Sequence...340 15.6 NEXTVAL CURRVAL...343 15.7 Sequence...346 15.8 Sequence...349 15.9 Synonym...350 15.10 Synonym...351 15.11 Synonym...353 15.12 Synonym...355 15.13...356 16...358 16.1...358 16.2...358 16.3 Oracle...361 16.4...362 16.5...367 16.6 Role...369 XIII

16.7 Role...370 16.8...374 16.9...375 16.10...387 16.11...392 16.12...394 16.13 CONNECT RESOURCE...397 16.14...402...403...413...415...417 XIV

9 1 2 3 6 8 20 IT 3 Windows SQL*PLUS 4 RR YY 4 1 2 1~2 SELECT 5 1~2 NULL SQL NULL SQL NULL 6 1~2

7 Oracle Equijoin Self join Non- equijoin Outer join 4 SQL:1999 SQL SQL:1999 8 SQL 9 Oracle FORM REPORT Oracle Oracle 11 ACCEPT 1 2 5 6 7 Oracle 4 3 4 7 8 9 11 SQL*PLUS 10 DDL DDL 12 DML Transaction Control DML 13 14 2

15 16 12 10 13 15 13 14 16 3

SQL Structured Query Language Relational Database Management Systems SQL Data Retrieval Data Manipulation Language DML Data Definition Language DDL Data Control Language DCL Transaction Control SQL Sun & Moon Limited Corporation SQL Oracle IT 3 emp dept salgrade emp dept salgrade 3 SQL SQL*PLUS DBA SQL SELECT FROM 1-1 emp

1-1 SQL> SELECT * 2 FROM emp; 1-1 EMPNO ENAME JOB MGR HIREDATE ---------- -------------- ------------------------------- --------- SAL COMM DEPTNO --------- --------- ------------ 7369 SMITH CLERK 7902 17-12? -80 800 20 7499 ALLEN SALESMAN 7698 20-2? -81 1600 300 30 7521 WARD SALESMAN 7698 22-2? -81 1250 500 30 SQL SQL*PLUS * SELECT emp employee Oracle Unix C salary sal HIREDATE emp SELECT empno ename sal 1-2 5

1-2 SQL> SELECT empno, ename, sal 2 FROM emp; 1-2 EMPNO ENAME SAL ---------- ---------- ---------- 7369 SMITH 800 7499 ALLEN 1600 7521 WARD 1250 7566 JONES 2975 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 7876 ADAMS 1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 14 Project SELECT 1-3 1-3 SQL> SELECT sal,ename,empno 2 FROM emp; 1-3 SAL ENAME EMPNO ---------- -------------------- ---------- 800 SMITH 7369 1600 ALLEN 7499 1250 WARD 7521 2975 JONES 7566 1250 MARTIN 7654 2850 BLAKE 7698 2450 CLARK 7782 3000 SCOTT 7788 6

5000 KING 7839 1500 TURNER 7844 1100 ADAMS 7876 14 1-3 1-2 Keyword SELECT FROM Oracle SQL SELECT SEL SELEC FROM FRO FR SQL 1-4 1-4 SQL> select Sal, EName, EMPNO 2 From Emp; 1-4 SAL ENAME EMPNO ---------- -------------------- ---------- 800 SMITH 7369 1600 ALLEN 7499 1250 WARD 7521 2975 JONES 7566 1250 MARTIN 7654 2850 BLAKE 7698 2450 CLARK 7782 3000 SCOTT 7788 5000 KING 7839 1500 TURNER 7844 1100 ADAMS 7876 14 1-4 1-3 Oracle 1-3 SQL SQL 1-5 7

1-5 SQL> select sal, ename, empno from emp; 1-5 SAL ENAME EMPNO ---------- -------------------- ------ 800 SMITH 7369 1600 ALLEN 7499 1250 WARD 7521 2975 JONES 7566 1250 MARTIN 7654 2850 BLAKE 7698 2450 CLARK 7782 3000 SCOTT 7788 5000 KING 7839 1500 TURNER 7844 1100 ADAMS 7876 14 1-5 1-3 1-3 SQL 1-6 SQL SQL statement clause SELECT * FROM emp; SELECT * FROM emp 1-6 SQL> SELECT empno, ename, sal, 2 deptno, job 3 FROM emp; 1-6 EMPNO ENAME SAL DEPTNO JOB ---------- -------------------- ---------- ---------- --------- 7369 SMITH 800 20 CLERK 7499 ALLEN 1600 30 SALESMAN 7521 WARD 1250 30 SALESMAN 7566 JONES 2975 20 MANAGER 7654 MARTIN 1250 30 SALESMAN 7698 BLAKE 2850 30 MANAGER 7782 CLARK 2450 10 MANAGER 7788 SCOTT 3000 20 ANALYST 8

7839 KING 5000 10 PRESIDENT 7844 TURNER 1500 30 SALESMAN 7876 ADAMS 1100 20 CLERK 14 1-6 SELECT FROM 1-6 emp empno ename sal deptno job 1-7 SQL 1-7 SQL> alter session 2 set NLS_DATE_LANGUAGE = 'AMERICAN'; 1-7 1-8 1-9 SQL*PLUS 1-8 SQL> col hiredate for a15 1-9 SQL> COL ENAME FOR A8 SQL SQL*PLUS SQL*PLUS 1-10 1-10 SQL> SELECT empno, ename, sal, 2 hiredate, job 3 FROM emp; 9

1-10 EMPNO ENAME SAL HIREDATE JOB ---------- -------- ---------- --------------- --------- 7369 SMITH 800 17-DEC-80 CLERK 7499 ALLEN 1600 20-FEB-81 SALESMAN 7521 WARD 1250 22-FEB-81 SALESMAN 7566 JONES 2975 02-APR-81 MANAGER 7654 MARTIN 1250 28-SEP-81 SALESMAN 7698 BLAKE 2850 01-MAY-81 MANAGER 7782 CLARK 2450 09-JUN-81 MANAGER 7788 SCOTT 3000 19-APR-87 ANALYST 7839 KING 5000 17-NOV-81 PRESIDENT 7844 TURNER 1500 08-SEP-81 SALESMAN 7876 ADAMS 1100 23-MAY-87 CLERK 14 1-10 1-7 1-9 1-10 hiredate SQL 4 + * / 500 / 1-11 1-11 SQL> SELECT empno, ename, sal, 500+sal 2 FROM emp; 1-11 EMPNO ENAME SAL 500+SAL ---------- -------- ---------- ---------- 7369 SMITH 800 1300 7499 ALLEN 1600 2100 7521 WARD 1250 1750 7566 JONES 2975 3475 7654 MARTIN 1250 1750 10

7698 BLAKE 2850 3350 7782 CLARK 2450 2950 7788 SCOTT 3000 3500 7839 KING 5000 5500 7844 TURNER 1500 2000 7876 ADAMS 1100 1600 14 1-11 1-11 1-12 SQL 1-12 SQL> SELECT empno, ename, 500+sal*12 2 FROM emp; 1-12 EMPNO ENAME 500+SAL*12 ---------- -------- ----------- 7369 SMITH 10100 7499 ALLEN 19700 7521 WARD 15500 7566 JONES 36200 7654 MARTIN 15500 7698 BLAKE 34700 7782 CLARK 29900 7788 SCOTT 36500 7839 KING 60500 7844 TURNER 18500 7876 ADAMS 13700 14 1-12 1-13 SQL 11

1-13 SQL> SELECT empno, ename, (500+sal)*12 2 FROM emp; 1-13 EMPNO ENAME (500+SAL)*12 ---------- -------- ------------ 7369 SMITH 15600 7499 ALLEN 25200 7521 WARD 21000 7566 JONES 41700 7654 MARTIN 21000 7698 BLAKE 40200 7782 CLARK 35400 7788 SCOTT 42000 7839 KING 66000 7844 TURNER 24000 7876 ADAMS 19200 14 1-13 Oracle 1-13 1-14 SQL 1-14 SQL> SELECT empno AS "Employee Number", 2 ename name, (500+sal)*12 "Annual Salary" 3 FROM emp; 1-14 Employee Number NAME Annual Salary --------------- -------------------- ------------- 7369 SMITH 15600 7499 ALLEN 25200 7521 WARD 21000 12

7566 JONES 41700 7654 MARTIN 21000 7698 BLAKE 40200 7782 CLARK 35400 7788 SCOTT 42000 7839 KING 66000 7844 TURNER 24000 7876 ADAMS 19200 14 1-14 Oracle Unix C salary sal Oracle AS AS Oracle AS Employee s Salary 1-15 SQL 1-15 SQL> SELECT ename ' annual salary is ' (500+sal)*12 "Employee's Salary" 2 FROM emp; 1-15 Employee's Salary ----------------------------- SMITH annual salary is 15600 ALLEN annual salary is 25200 13

WARD annual salary is 21000 JONES annual salary is 41700 MARTIN annual salary is 21000 BLAKE annual salary is 40200 CLARK annual salary is 35400 SCOTT annual salary is 42000 KING annual salary is 66000 TURNER annual salary is 24000 ADAMS annual salary is 19200 1-16 1-16 SQL> SELECT ename ' ' (500+sal)*12 " " 2 FROM emp; 1-16 -------------------- SMITH 15600 ALLEN 25200 WARD 21000 JONES 41700 MARTIN 21000 BLAKE 40200 CLARK 35400 SCOTT 42000 KING 66000 TURNER 24000 ADAMS 19200 JAMES 17400 FORD 42000 MILLER 21600 14 literal SELECT 14

Oracle DBA 1-17 1-17 SQL> select deptno 2 from emp; 1-17 DEPTNO ----------- 20 30 30 20 30 30 10 20 10 30 14 Oracle Oracle DISTINCT 1-18 1-18 SQL> SELECT DISTINCT deptno 2 FROM emp; 1-18 DEPTNO ---------- 10 20 30 15

1-18 1-17 DISTINCT Oracle DISTINCT Oracle 1-19 1-19 SQL> SELECT deptno 2 FROM dept; 1-19 DEPTNO ---------- 10 20 30 40 1-19 Oracle deptno dept DISTINCT 1-20 1-20 SQL> SELECT DISTINCT deptno, job 2 FROM emp; 1-20 DEPTNO JOB ---------- --------- 10 CLERK 10 MANAGER 10 PRESIDENT 20 ANALYST 20 CLERK 20 MANAGER 30 CLERK 30 MANAGER 30 SALESMAN 9 16

SELECT FROM SELECT * {[DISTINCT] } FROM [ ] SELECT * FROM SELECT DISTINCT FROM SQL SQL literal DISTINCT 17

1 9.11 1500 1500 1500 2-1 2-1 SQL> SELECT empno, ename, sal 2 FROM emp 3 WHERE sal >= 1500; 2-1 EMPNO ENAME SAL ---------- ------------------ -------- 7499 ALLEN 1600 7566 JONES 2975 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 7902 FORD 3000 8 2-1 1500 empno ename sal 2-1 WHERE selection WHERE FROM WHERE

operators operator Oracle 6 operators > >= < <= = <> = Oracle 3 operators BETWEEN AND IN LIKE operators 2900 2900 2-2 2-2 SQL> SELECT empno, ename, sal 2 FROM emp 3 WHERE sal BETWEEN 1500 AND 2900; 2-2 EMPNO ENAME SAL ---------- -------------------- ------ 7499 ALLEN 1600 7698 BLAKE 2850 7782 CLARK 2450 7844 TURNER 1500 19

2-2 BETWEEN BETWEEN AND AND 1981 1 1 1982 5 31 2-3 2-3 SQL> SELECT empno, ename, sal, hiredate 2 FROM emp 3 WHERE hiredate BETWEEN '01-JAN-81' AND '31-MAY-82'; 2-3 EMPNO ENAME SAL HIREDATE ---------- ----------------- ---------- ------- 7499 ALLEN 1600 20-FEB-81 7521 WARD 1250 22-FEB-81 7566 JONES 2975 02-APR-81 7654 MARTIN 1250 28-SEP-81 7698 BLAKE 2850 01-MAY-81 7782 CLARK 2450 09-JUN-81 7839 KING 5000 17-NOV-81 7844 TURNER 1500 08-SEP-81 7900 JAMES 950 03-DEC-81 7902 FORD 3000 03-DEC-81 7934 MILLER 1300 23-JAN-82 11 1981 1 1982 5 HIREDATE 2-4 2-4 SQL> alter session set NLS_DATE_LANGUAGE = AMERICAN; 2-4 SQL 1 SQL BETWEEN NOT BETWEEN AND 20

2-3 1981 1 1 1982 5 31 2-5 2-5 SQL> SELECT empno, ename, sal, hiredate 2 FROM emp 3 WHERE hiredate NOT BETWEEN '01-JAN-81' AND '31-MAY-82'; 2-5 EMPNO ENAME SAL HIREDATE ---------- ----------------- ---------- -------- 7369 SMITH 800 17-DEC-80 7788 SCOTT 3000 19-APR-87 7876 ADAMS 1100 23-MAY-87 SQL 1981 1 1982 5 salesman 2-6 2-6 SQL> SELECT empno, ename, job 2 FROM emp; 3 WHERE JOB = 'salesman'; 2-6 emp WHERE WHERE Oracle9i DD-MON-YY Oracle9i DD-MON-RR 2-7 21

2-7 SQL> SELECT empno, ename, job, sal 2 FROM emp 3 WHERE job ='SALESMAN'; 2-7 EMPNO ENAME JOB SAL --------- ------------------ ---------------- --------- 7499 ALLEN SALESMAN 1600 7521 WARD SALESMAN 1250 7654 MARTIN SALESMAN 1250 7844 TURNER SALESMAN 1500 salesman IN 2-8 2-8 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE job IN ('SALESMAN', 'CLERK', 'MANAGER'); 2-8 EMPNO ENAME SAL JOB ---------- -------------------- -------- ------ 7369 SMITH 800 CLERK 7499 ALLEN 1600 SALESMAN 7521 WARD 1250 SALESMAN 7566 JONES 2975 MANAGER 7654 MARTIN 1250 SALESMAN 7698 BLAKE 2850 MANAGER 7782 CLARK 2450 MANAGER 7844 TURNER 1500 SALESMAN 7876 ADAMS 1100 CLERK 7900 JAMES 950 CLERK 7934 MILLER 1300 CLERK 11 22

IN SQL IN SQL JOB IN IN NOT NOT IN analyst 2-9 2-9 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE job NOT IN ('ANALYST', 'PRESIDENT'); 2-9 EMPNO ENAME SAL JOB ---------- ------------------ ---------- -------- 7369 SMITH 800 CLERK 7499 ALLEN 1600 SALESMAN 7521 WARD 1250 SALESMAN 7566 JONES 2975 MANAGER 7654 MARTIN 1250 SALESMAN 7698 BLAKE 2850 MANAGER 7782 CLARK 2450 MANAGER 7844 TURNER 1500 SALESMAN 7876 ADAMS 1100 CLERK 7900 JAMES 950 CLERK 7934 MILLER 1300 CLERK 11 WHERE job NOT IN 'ANALYST', 'PRESIDENT' Oracle JOB ANALYST PRESIDENT emp 5 ANALYST PRESIDENT SALESMAN CLERK MANAGER analyst 2-9 PRESIDENT 23

SALESMAN 3 SAL LIKE 2-10 SQL 2-10 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE job LIKE 'SAL%'; 2-10 EMPNO ENAME SAL JOB ---------- -------------------- ---------- ---- 7499 ALLEN 1600 SALESMAN 7521 WARD 1250 SALESMAN 7654 MARTIN 1250 SALESMAN 7844 TURNER 1500 SALESMAN LIKE wildcard 2 wildcard LIKE % - % - SALESMAN S L S 2-11 2-11 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE job LIKE 'S_L_S%'; 2-11 EMPNO ENAME SAL JOB ---------- -------------------- ---------- -------- 7499 ALLEN 1600 SALESMAN 7521 WARD 1250 SALESMAN 7654 MARTIN 1250 SALESMAN 7844 TURNER 1500 SALESMAN 2-11 LIKE % - 24

LIKE WHERE 1981 2-12 2-12 SQL> SELECT empno, ename, sal, hiredate 2 FROM emp 3 WHERE hiredate LIKE '%81'; 2-12 EMPNO ENAME SAL HIREDATE ---------- ------------------ ---------- --------- 7499 ALLEN 1600 20-FEB-81 7521 WARD 1250 22-FEB-81 7566 JONES 2975 02-APR-81 7654 MARTIN 1250 28-SEP-81 7698 BLAKE 2850 01-MAY-81 7782 CLARK 2450 09-JUN-81 7839 KING 5000 17-NOV-81 7844 TURNER 1500 08-SEP-81 7900 JAMES 950 03-DEC-81 7902 FORD 3000 03-DEC-81 11 - % escape 1 2-13 2-14 SQL 2-13 SQL> CREATE TABLE dept_temp 2 AS 3 SELECT * 4 FROM dept; 2-13 2-14 SQL> INSERT INTO dept_temp 25

2 VALUES (88,'IT_RESEARCH','BEIJING'); 2-14 1 2-15 dname IT_ 2-15 SQL> SELECT * 2 FROM dept_temp 3 WHERE dname LIKE 'IT\_%' escape '\'; 2-15 DEPTNO DNAME LOC ---------- ------------------------ ------- 88 IT_RESEARCH BEIJING 2-15 \ escape \ _ IT _ \ escape escape Oracle \ escape Unix C escape 2-16 2-16 SQL> SELECT * 2 FROM dept_temp 3 WHERE dname LIKE 'IT~_%' escape '~'; 2-16 DEPTNO DNAME LOC ---------- -------------- ------- 88 IT_RESEARCH BEIJING 2-16 ~ escape 2-16 2-15 SQL SQL*PLUS escape SQL 26

SQL ORDER BY 2-17 2-17 SQL> SELECT empno, ename, sal 2 FROM emp 3 WHERE sal >= 1500 4 ORDER BY sal; 2-17 EMPNO ENAME SAL ---------- -------------------- ------ 7844 TURNER 1500 7499 ALLEN 1600 7782 CLARK 2450 7698 BLAKE 2850 7566 JONES 2975 7788 SCOTT 3000 7902 FORD 3000 7839 KING 5000 8 2-18 2-18 SQL> SELECT empno, ename, sal 2 FROM emp 3 WHERE sal >= 1500 4 ORDER BY sal DESC; 2-18 EMPNO ENAME SAL ---------- -------------------- ------ 7839 KING 5000 27

7788 SCOTT 3000 7902 FORD 3000 7566 JONES 2975 7698 BLAKE 2850 7782 CLARK 2450 7499 ALLEN 1600 7844 TURNER 1500 8 2-18 DESC ORDER BY ASC ascending order ASC SQL ASCII A Z DESC descending order ORDER BY ORDER BY SQL 1 1-11 1-14 1 1-14 SQL 2-19 2-19 SQL>SELECT empno AS "Employee Number",ename name,(500+sal)*12 "Annual Salary" 2 FROM emp SQL 2-20 SQL 2-20 SQL>SELECT empno AS "Employee Number", ename name, (500+sal)*12 "Annual Salary" 2 FROM emp 3 ORDER BY "Annual Salary" DESC; 2-20 Employee Number NAME Annual Salary --------------- ---------- ------------- 7839 KING 66000 7788 SCOTT 42000 28

7902 FORD 42000 7566 JONES 41700 7698 BLAKE 40200 7782 CLARK 35400 7499 ALLEN 25200 7844 TURNER 24000 7934 MILLER 21600 7521 WARD 21000 7654 MARTIN 21000 7876 ADAMS 19200 7900 JAMES 17400 7369 SMITH 15600 14 2-20 ORDER BY ORDER BY 2-21 SQL 2-21 SQL>SELECT empno AS "Employee Number", ename name, (500+sal)*12 "Annual Salary" 2 FROM emp 3 ORDER BY (500+sal)*12 DESC; 2-21 Employee Number NAME Annual Salary --------------- ---------- ------------- 7839 KING 66000 7788 SCOTT 42000 7902 FORD 42000 7566 JONES 41700 7698 BLAKE 40200 7782 CLARK 35400 7499 ALLEN 25200 7844 TURNER 24000 7934 MILLER 21600 7521 WARD 21000 7654 MARTIN 21000 7876 ADAMS 19200 7900 JAMES 17400 7369 SMITH 15600 14 2-20 2-21 ORDER BY 29

2-22 3 3 ORDER BY 3 3 2-22 SQL> SELECT empno "Employee Number",ename name,(500+sal)*12 "Annual Salary" 2 FROM emp 3 ORDER BY 3 DESC; 2-22 Employee Number NAME Annual Salary --------------- ---------- ------------- 7839 KING 66000 7788 SCOTT 42000 7902 FORD 42000 7566 JONES 41700 7698 BLAKE 40200 7782 CLARK 35400 7499 ALLEN 25200 7844 TURNER 24000 7934 MILLER 21600 7521 WARD 21000 7654 MARTIN 21000 7876 ADAMS 19200 7900 JAMES 17400 7369 SMITH 15600 14 SQL ORDER BY Oracle SQL ORDER BY ORDER BY SQL 30

A Z 2-23 2-23 SQL> SELECT ename, job, sal 2 FROM emp 3 ORDER BY job, sal DESC; 2-23 ENAME JOB SAL ---------- --------- ---------- SCOTT ANALYST 3000 FORD ANALYST 3000 MILLER CLERK 1300 ADAMS CLERK 1100 JAMES CLERK 950 SMITH CLERK 800 JONES MANAGER 2975 BLAKE MANAGER 2850 CLARK MANAGER 2450 KING PRESIDENT 5000 ALLEN SALESMAN 1600 TURNER SALESMAN 1500 WARD SALESMAN 1250 MARTIN SALESMAN 1250 14 SELECT 2-24 2-24 SQL> SELECT ename, job, sal 2 FROM emp 31

3 ORDER BY empno; 2-24 ENAME JOB SAL ---------- --------- ---------- SMITH CLERK 800 ALLEN SALESMAN 1600 WARD SALESMAN 1250 JONES MANAGER 2975 MARTIN SALESMAN 1250 BLAKE MANAGER 2850 CLARK MANAGER 2450 SCOTT ANALYST 3000 KING PRESIDENT 5000 TURNER SALESMAN 1500 ADAMS CLERK 1100 JAMES CLERK 950 FORD ANALYST 3000 MILLER CLERK 1300 14 WHERE ORDER BY SELECT * {[DISTINCT] } FROM [WHERE ] [ORDER BY { }[ASC DESC]] ORDER BY SQL 32

selection WHERE 6 operators WHERE BETEEN AND IN operators NOT SQL LIKE operators wildcard escape ORDER BY SELECT 33

SQL SQL SQL SQL SQL SQL SQL*PLUS SQL SQL SQL*PLUS SQL SQL*PLUS SQL*PLUS DESC[RIBE] 3-1 emp 3-1 SQL> DESC emp 3-1? ----------------------------------------- -------- ------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) 3-1 NULL 3-1 emp 8 empno EMPNO 4 ENAME 10 JOB 9 MGR 4

HIREDATE SAL 7 COMM 7 DEPTNO 3-2 dept 3-2 SQL> DESC dept 3-2? ----------------------------------------- -------- ------------ DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) 3-2 dept 3 DEPTNO DEPTNO DNAME 14 LOC 13 3-1 3-2 SQL*PLUS DESC[RIBE] SQL*PLUS SQL DESC[RIBE] SQL SQL*PLUS SET LINE[SIZE] {80 n} n 80 n 80 3-3 SQL emp 3-3 SQL> SELECT * 2 FROM emp; 3-3 EMPNO ENAME JOB MGR HIREDATE SAL COMM 35

---------- ---------- --------- ---------- ---------- ---------- --------- DEPTNO ---------- 7369 SMITH CLERK 7902 17-12 -80 800 20 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2 -81 1250 500 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- ---------- ---------- --------- DEPTNO ---------- 7566 JONES MANAGER 7839 02-4 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5 -81 2850 30 SQL*PLUS SET LINE 100 3-4 3-4 SQL> SET line 100 3-3 SQL*PLUS 3-5 SQL 3-5 SQL> SELECT empno, ename, job, sal 2 FROM dept 3 WHERE sal >= 1500 4 ORDER BY job, sal DESC; 36

3-5 WHERE sal >= 1500 * ERROR 3 : ORA-00904:???? 3-5 emp dept Oracle Oracle SQL*PLUS L LIST SQL L LIST SQL 3-6 3-6 SQL> L 3-6 1 SELECT empno, ename, job, sal 2 FROM dept 3 WHERE sal >= 1500 4* ORDER BY job, sal DESC n text n SQL SQL text SQL L LIST 3-7 3-7 SQL> 2 FROM emp 3-8 L LIST SQL 3-8 SQL> L 3-8 1 SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= 1500 4* ORDER BY job, sal DESC 37

3-8 SQL Oracle SQL*PLUS / RUN SQL SQL 3-9 SQL*PLUS SQL 3-9 SQL> / 3-9 EMPNO ENAME JOB SAL ---------- -------------------- ------------------ ---------- 7788 SCOTT ANALYST 3000 7902 FORD ANALYST 3000 7566 JONES MANAGER 2975 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7839 KING PRESIDENT 5000 7499 ALLEN SALESMAN 1600 7844 TURNER SALESMAN 1500 8 SQL*PLUS Oracle SQL*PLUS 3-10 3-10 SQL> SELECT ename 2 FROM emp; 3-10 ENAME ------------- SMITH 38

ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER 14 SELECT job, sal SELECT SQL*PLUS L LIST SQL 3-11 SQL> L 3-11 1 SELECT ename 2* FROM emp 3-11 2 * 3-11 SELECT ename SQL ename job, sal 1 3-12 3-12 SQL> 1 3-12 1* SELECT ename 3-12 SQL 3-13 a job sal SELECT ename 3-13 SQL> a,job, sal 3-13 39

1* SELECT ename,job, sal 3-14 L 3-14 SQL> L 3-14 1 SELECT ename,job, sal 2* FROM emp 3-14 / R SOL 3-15 3-15 SQL> / 3-15 ENAME JOB SAL ---------- --------- ---------- SMITH CLERK 800 ALLEN SALESMAN 1600 WARD SALESMAN 1250 JONES MANAGER 2975 MARTIN SALESMAN 1250 BLAKE MANAGER 2850 CLARK MANAGER 2450 SCOTT ANALYST 3000 KING PRESIDENT 5000 TURNER SALESMAN 1500 ADAMS CLERK 1100 JAMES CLERK 950 FORD ANALYST 3000 MILLER CLERK 1300 14 SQL n n n 0 text SQL 40

del n n n del m n m n SQL*PLUS 3-5 3-16 SQL 3-16 SQL> SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= 1500 4 ORDER BY job, sal DESC; 3-17 SQL*PLUS 3-17 SQL> L 3-17 1 SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= 1500 4* ORDER BY job, sal DESC emp ORDER BY 3-18 SQL*PLUS 3-18 SQL> del 4 3-19 SQL*PLUS L 3-19 SQL> L 3-19 1 SELECT empno, ename, job, sal 2 FROM emp 3* WHERE sal >= 1500 3-19 SQL ORDER BY 3-20 SQL*PLUS / 41

3-20 SQL> / 3-20 EMPNO ENAME JOB SAL ---------- ------------------ --------------- -------- 7499 ALLEN SALESMAN 1600 7566 JONES MANAGER 2975 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7788 SCOTT ANALYST 3000 7839 KING PRESIDENT 5000 7844 TURNER SALESMAN 1500 7902 FORD ANALYST 3000 8 3-10 del m n del del m SQL*PLUS C[HANGE]/ / SQL 3-5 SQL 3-21 3-21 SQL> SELECT empno, ename, job, sal 2 FROM dept 3 WHERE sal >= 1500 4 ORDER BY job, sal DESC; 3-21 WHERE sal >= 1500 * ERROR 3 : ORA-00904: 42

C[HANGE] SQL dept emp 3-22 SQL*PLUS 3-22 SQL> C /dept/emp 3-22 SP2-0023: 3-22 SQL*PLUS 3-22 SQL*PLUS dept 3-23 SQL*PLUS SQL 3-23 SQL> 2 3-23 2* FROM dept 3-22 3-24 SQL*PLUS 3-24 SQL> C /dept/emp 3-24 2* FROM emp 3-24 SQL dept emp 3-25 L 3-25 SQL> l 3-25 1 SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= 1500 4* ORDER BY job, sal DESC 3-26 / 3-9 3-26 43

SQL> / 3-26 EMPNO ENAME JOB SAL ---------- ---------- --------- ---------- 7788 SCOTT ANALYST 3000 7902 FORD ANALYST 3000 7566 JONES MANAGER 2975 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7839 KING PRESIDENT 5000 7499 ALLEN SALESMAN 1600 7844 TURNER SALESMAN 1500 8 sal 3-27 SQL*PLUS SQL 3-27 SQL> 4 3-27 4* ORDER BY job, sal DESC 3-28 C job SQL 3-28 SQL> c /job,/ 3-28 4* ORDER BY sal DESC 3-29 L 3-29 SQL> l 3-29 1 SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= 1500 4* ORDER BY sal DESC 44

3-30 / SQL 3-30 SQL> / 3-30 EMPNO ENAME JOB SAL ---------- ---------- --------- ---------- 7839 KING PRESIDENT 5000 7788 SCOTT ANALYST 3000 7902 FORD ANALYST 3000 7566 JONES MANAGER 2975 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7499 ALLEN SALESMAN 1600 7844 TURNER SALESMAN 1500 8 C 3-31 3-31 SQL> SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= 1500 4 ORDER BY job, sal DESC; 3-31 EMPNO ENAME JOB SAL ---------- ------------------ ----------------- -------- 7788 SCOTT ANALYST 3000 7902 FORD ANALYST 3000 7566 JONES MANAGER 2975 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7839 KING PRESIDENT 5000 45

7499 ALLEN SALESMAN 1600 7844 TURNER SALESMAN 1500 8 3-32 SQL*PLUS SQL D:\SQL\SAMPLE.sql SQL*PLUS D:\SQL 3-32 SQL> SAVE D:\SQL\SAMPLE 3-32 D:\SQL\SAMPLE.sql SAVE SQL D:\SQL\SAMPLE.sql 3-33 SELECT empno, ename, job, sal FROM emp WHERE sal >= 1500 ORDER BY job, sal DESC / SQL*PLUS L SQL SQL*PLUS SAVE D:\SQL\SAMPLE SQL*PLUS SQL 3-34 3-34 SQL> L 3-34 1 SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= 1500 4* ORDER BY job, sal DESC 3-35 SQL SQL 3-35 SQL> SELECT * 46

2 FROM dept; 3-35 DEPTNO DNAME LOC ---------- -------------------------- ------ 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 3-36 SQL*PLUS L SQL 3-36 SQL> L 3-36 1 SELECT * 2* FROM dept SQL SQL D:\SQL\SAMPLE.sql 3-37 SQL*PLUS GET SQL 3-37 SQL> GET D:\SQL\SAMPLE.sql 3-37 1 SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= 1500 4* ORDER BY job, sal DESC 3-38 SQL*PLUS L D:\SQL\ SAMPLE.sql SQL 3-38 47

SQL> L 3-38 1 SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= 1500 4* ORDER BY job, sal DESC C A n DEL SQL 3-39 / SQL 3-39 SQL> / 3-39 EMPNO ENAME JOB SAL ---------- -------------------- --------------- -------- 7788 SCOTT ANALYST 3000 7902 FORD ANALYST 3000 7566 JONES MANAGER 2975 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7839 KING PRESIDENT 5000 7499 ALLEN SALESMAN 1600 7844 TURNER SALESMAN 1500 8 3-40 SQL*PLUS ed[it] D:\SQL\SAMPLE 3-40 SQL> ed D:\SQL\SAMPLE 3-40 D:\SQL\SAMPLE 48

3-41 SQL*PLUS D:\SQL\SAMPLE.sql 3-41 SQL> @D:\SQL\SAMPLE.sql 3-41 EMPNO ENAME JOB SAL ---------- -------------------- ------------------ ---------- 7788 SCOTT ANALYST 3000 7902 FORD ANALYST 3000 7566 JONES MANAGER 2975 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7839 KING PRESIDENT 5000 7499 ALLEN SALESMAN 1600 7844 TURNER SALESMAN 1500 8 @ START SQL SQL SQL SQL*PLUS SPOOL SQL 3-42 3-43 3-44 SQL*PLUS SQL 3-42 SQL> SPOOL D:\SQL\OUTPUT 3-43 SQL> SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= 1500 4 ORDER BY job, sal DESC; 49

3-44 SQL> SPOOL OFF; D:\SQL\OUTPUT SQL> SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= 1500 4 ORDER BY job, sal DESC; EMPNO ENAME JOB SAL ---------- -------------------- ------------------ -------- 7788 SCOTT ANALYST 3000 7902 FORD ANALYST 3000 7566 JONES MANAGER 2975 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7839 KING PRESIDENT 5000 7499 ALLEN SALESMAN 1600 7844 TURNER SALESMAN 1500 8 SQL> SPOOL OFF; SPOOL D:\SQL\OUTPUT SPOOL D:\SQL OUTPUT SPOOL OFF OUTPUT SPOOL OUT SQL*PLUS Windows SQL*PLUS SQL*PLUS Oracle Oracle SQL Oracle SQL SQL 50

SQL SQL SQL SQL SQL SQL SPOOL 51

Oracle Oracle SQL SQL SQL SQL [ 1 2 3 ] SELECT WHERE ORDER BY

LOWER UPPER INITCAP CONCAT SUBSTR LENGTH INSTR TRIM REPLACE LOWER 4-1 4-1 SQL> SELECT LOWER('SQL: Structural Query Language') 2 FROM dual; 4-1 LOWER('SQL:STRUCTURALQUERYLANGUAGE') ------------------------------------ sql: structural query language dual Oracle 1 SELECT FROM LOWER 'SQL: Structural Query Language' SQL Oracle dual UPPER 4-2 4-2 53

SQL> SELECT UPPER ('sql is used exclusively in rdbmses') 2 FROM dual; 4-2 UPPER('SQLISUSEDEXCLUSIVELYINRDBMSES') ------------------------------------- SQL IS USED EXCLUSIVELY IN RDBMSES INITCAP 4-3 4-3 SQL> SELECT INITCAP('SQL is an ENGLISH LIKE language') 2 FROM dual; 4-3 INITCAP('SQLISANENGLISHLIKELANGUAGE') ------------------------------------- Sql Is An English Like Language CONCAT 4-4 4-4 SQL> SELECT CONCAT('SQL allows you to manipulate the data in DB', 2 ' without any programming knowledge') 3 FROM dual; 4-4 CONCAT('SQLALLOWSYOUTOMANIPULATETHEDATAINDB','WITHOUTANYPROGRAMMINGKNOWLEDGE') --------------------------------------------------------------------------- SQL allows you to manipulate the data in DB without any programming knowledge SUBSTR m,[ n ] m n 4-5 4-5 SQL> SELECT SUBSTR('SQL lets you concentrate on what has to be done',14) 54

2 FROM dual; 4-5 SUBSTR('SQLLETSYOUCONCENTRATEONWHATHASTOBEDONE',14) --------------------------------------------------- concentrate on what has to be done n 14 LENGTH 4-6 4-6 SQL> SELECT LENGTH('SQL does not let you concentrate on how it will be achieved') 2 FROM dual; 4-6 LENGTH('SQLDOESNOTLETYOUCONCENTRATEONHOWITWILLBEACHIVED') --------------------------------------------------------- 58 INSTR [m], [n] m m n 1 4-7 4-7 SQL> SELECT INSTR('SQL allows for dynamic DB changes', 'F') 2 FROM dual; 4-7 INSTR('SQLALLOWSFORDYNAMICDBCHANGES','F') ----------------------------------------- 0 4-7 F f 4-8 4-8 SQL> SELECT INSTR('SQL allows for dynamic DB changes', 'f') 2 FROM dual; 4-8 INSTR('SQLALLOWSFORDYNAMICDBCHANGES','F') 55

----------------------------------------- 12 4-1 4-8 SQL 4-1 SQL 4-2 SQL 4-3 SQL 4-4 SQL 4-5 SQL 4-6 SQL 4-7 SQL 4-8 SQL SQL SQL TRIM [leading trailing both] FROM leading trailing leading trailing both leading trailing TRIM both 8i 8i LTRIM RTRIM 4-9 SQL*PLUS 4-9 SQL> SELECT TRIM('?' FROM '?SQL*PLUS is the SQL implementation 2 used in an Oracle RDBMS or ORDBMS.') 3 FROM dual; 4-9 TRIM('?'FROM'?SQL*PLUSISTHESQLIMPLEMENTATIONUSEDINANORACLERDBMSORORDBMS.') -------------------------------------------------------------------------- SQL*PLUS is the SQL implementation used in an Oracle RDBMS or ORDBMS. 4-10 rows 4-10 SQL> SELECT TRIM('?' FROM 'It can process data in sets of rows??') 2 FROM dual; 4-10 56

TRIM('?'FROM'ITCANPROCESSDATAINSETSOFROWS??') --------------------------------------------- It can process data in sets of rows Oracle Both 4-9? 4-10? Oracle 4-11 TRIM 4-11 SQL> SELECT TRIM('s' FROM 'sql*plus is a fourth generation query languages') 2 FROM dual; 4-11 TRIM('S'FROM'SQL*PLUSISAFOURTHGENERATIONQUERYLANGUAGES') -------------------------------------------------------- ql*plus is a fourth generation query language 4-11 SQL s Oracle s trailing 4-11 SQL 4-12 4-12 SQL> SELECT TRIM(trailing 's' FROM 'sql*plus is a fourth generation query languages') 2 FROM dual; 4-12 TRIM(TRAILING'S'FROM'SQL*PLUSISAFOURTHGENERATIONQUERYLANGUAGES') ---------------------------------------------------------------- sql*plus is a fourth generation query language TRIM Leading REPLACE 4-13 4-13 SQL> SELECT REPLACE('SQL*PLUS supports loops or if statements', 'supports', 2 'does not support') 3 FROM dual; 57

4-13 REPLACE('SQL*PLUSSUPPORTSLOOPSORIFSTATEMENTS','SUPPORTS','DOESNOTSUPPORT') ---------------------------------------------------------------------------- SQL*PLUS does not support loops or if statements 4-9 4-13 SQL*PLUS 4-9 SQL*PLUS SQL Oracle 4-10 4-11 SQL*PLUS 4-12 SQL*PLUS 4-13 SQL*PLUS SQL*PLUS SQL*PLUS SQL SQL*PLUS 2 2-6 4-14 4-14 SQL> SELECT empno, ename, job 2 FROM emp 3 WHERE JOB = 'salesman'; 4-14 2-7 Job SalesMAN 4-15 SQL 4-15 SQL> SELECT empno AS "Code", UPPER(ename) NAME, INITCAP(job) "Job" 2 FROM emp 3 WHERE LOWER(job) = 'salesman'; 58

4-15 Code NAME Job ---------- -------------------- -------- 7499 ALLEN Salesman 7521 WARD Salesman 7654 MARTIN Salesman 7844 TURNER Salesman 4-15 LOWER(job) WHERE UPPER job ='SALESMAN' 4-16 4-16 SQL> SELECT CONCAT(ename, job) "Employee", SUBSTR(job,1,5) "Title", 2 LENGTH(ename) "Length",INSTR(job, 'M') 3 FROM emp 4 WHERE LOWER(job) = 'salesman'; 4-16 Employee Title Length INSTR(JOB,'M') ------------------------------ ---------- ---------- -------------- ALLENSALESMAN SALES 5 6 WARDSALESMAN SALES 4 6 MARTINSALESMAN SALES 6 6 TURNERSALESMAN SALES 6 6 ROUND TRUNC MOD ROUND n n TRUNC n n 59

MOD m n m n 3 4-17 ROUND TRUNC 4-17 SQL> SELECT ROUND(168.888,1), TRUNC(168.888,1) 2 FROM dual; 4-17 ROUND(168.888,1) TRUNC(168.888,1) ---------------- ---------------- 168.9 168.8 4-17 ROUND TRUNC 4-18 ROUND TRUNC 4-18 SQL> SELECT ROUND(168.333,2), TRUNC(168.333,2) 2 FROM dual; 4-18 ROUND(168.333,2) TRUNC(168.333,2) ---------------- ---------------- 168.33 168.33 4-19 ROUND TRUNC 4-19 SQL> SELECT ROUND(168.888,0), TRUNC(168.888,0) 2 FROM dual; 4-19 ROUND(168.888,0) TRUNC(168.888,0) 60

---------------- ---------------- 169 168 4-20 ROUND TRUNC 4-20 SQL> SELECT ROUND(168.888), TRUNC(168.888) 2 FROM dual; 4-20 ROUND(168.888) TRUNC(168.888) -------------- -------------- 169 168 4-21 ROUND TRUNC 4-21 SQL> SELECT ROUND(168.888, -1), TRUNC(168.888, -1) 2 FROM dual; 4-21 ROUND(168.888,-1) TRUNC(168.888,-1) ----------------- ----------------- 170 160 4-22 MOD 1900 400 4-22 SQL> SELECT MOD(1900, 400) 2 FROM dual; 4-22 MOD(1900,400) ------------- 300 4-23 MOD 2000 400 4-23 61

SQL> SELECT MOD(2000, 400) 2 FROM dual; 4-23 MOD(2000,400) ------------- 0 2000 400 0 4-24 MOD 300 400 4-24 SQL> SELECT MOD(300, 400) 2 FROM dual; 4-24 MOD(300,400) ------------ 300 300 400 300 Oracle ROUND TRUNC ROUND TRUNC ROUND TRUNC Oracle Oracle Oracle9i DD-MON-RR DD-MON-YY 2000 Oracle 4712 1 1 9999 12 31 62

4-25 SQL 4-25 SQL> alter session set NLS_DATE_LANGUAGE = 'AMERICAN'; 4-25 Oracle SYSDATE 4-26 4-26 SQL> SELECT SYSDATE 2 FROM dual; 4-26 SYSDATE --------- 05-MAY-02 4-27 4-27 SQL> SELECT SYSDATE - 10 2 FROM dual; 4-27 SYSDATE-1 --------- 25-APR-02 2002 5 5 10 63

2002 4 25 4-28 4-28 SQL> SELECT SYSDATE + 10 2 FROM dual; 4-28 SYSDATE+1 --------- 15-MAY-02 4-29 4-29 SQL> SELECT TO_DATE('15-JUL-02') - SYSDATE 2 FROM dual; 4-29 TO_DATE('15-JUL-02')-SYSDATE ---------------------------- 70.373669 4-29 4-29a SQL 4-29a SQL> SELECT TO_DATE('15-5 -03') - SYSDATE 2 FROM dual; 4-29a TO_DATE('15-5 -03')-SYSDATE ----------------------------- 6.35358796 TO_DATE Oracle 24 4-30 4-31 64

4-30 SQL> SELECT SYSDATE - 22/24 2 FROM dual; 4-30 SYSDATE-2 --------- 04-MAY-02 4-31 SQL> SELECT SYSDATE + 22/24 2 FROM dual; 4-31 SYSDATE+2 --------- 06-MAY-02 4-32 4-32 SQL> SELECT empno, ename, job, sal, (SYSDATE-hiredate)/365 "Years" 2 FROM emp 3 WHERE job LIKE 'SAL%'; 4-32 EMPNO ENAME JOB SAL Years ---------- -------------------- ------------------ ---------- ---------- 7499 ALLEN SALESMAN 1600 21.2182074 7521 WARD SALESMAN 1250 21.212728 7654 MARTIN SALESMAN 1250 20.6154677 7844 TURNER SALESMAN 1500 20.6702622 MONTHS_BETWEEN ADD_MONTHS NEXT_DAY LAST_DAY 65

MONTHS_BETWEEN 1 2 1 2 1 2 1 2 4-33 4-33 SQL> SELECT MONTHS_BETWEEN('01-JUL-99','03-FEB-98') 2 FROM dual; 4-33 MONTHS_BETWEEN('01-JUL-99','03-FEB-98') --------------------------------------- 16.9354839 4-33 1999 7 1 1998 2 3 16.9354839 ADD_MONTHS n n 4-34 4-34 SQL> SELECT ADD_MONTHS('15-OCT-01', 8) 2 FROM dual; 4-34 ADD_MONTH --------- 15-JUN-02 4-34 2001 10 15 8 2002 6 15 NEXT_DAY 4-35 4-35 SQL> SELECT NEXT_DAY('10-MAY-02','MONDAY') 2 FROM dual; 4-35 NEXT_DAY( --------- 66

13-MAY-02 4-35 2002 5 10 2002 5 13 LAST_DAY 4-36 4-36 SQL> SELECT LAST_DAY('08-FEB-02') 2 FROM dual; 4-36 LAST_DAY( --------- 28-FEB-02 4-37 4-37 SQL> SELECT ename, hiredate, LAST_DAY(hiredate), NEXT_DAY(hiredate, 'SUNDAY'), 2 MONTHS_BETWEEN(SYSDATE, hiredate) "Months", 3 ADD_MONTHS(hiredate, 3) "Review" 4 FROM emp; 4-37 ENAME HIREDATE LAST_DAY( NEXT_DAY( Months Review -------------------- --------- --------- --------- ---------- --------- SMITH 17-DEC-80 31-DEC-80 21-DEC-80 256.656332 17-MAR-81 ALLEN 20-FEB-81 28-FEB-81 22-FEB-81 254.559558 20-MAY-81 WARD 22-FEB-81 28-FEB-81 01-MAR-81 254.495042 22-MAY-81 JONES 02-APR-81 30-APR-81 05-APR-81 253.140203 02-JUL-81 MARTIN 28-SEP-81 30-SEP-81 04-OCT-81 247.301493 28-DEC-81 BLAKE 01-MAY-81 31-MAY-81 03-MAY-81 252.172461 01-AUG-81 CLARK 09-JUN-81 30-JUN-81 14-JUN-81 250.914397 09-SEP-81 SCOTT 19-APR-87 30-APR-87 26-APR-87 180.591816 19-JUL-87 KING 17-NOV-81 30-NOV-81 22-NOV-81 245.656332 17-FEB-82 TURNER 08-SEP-81 30-SEP-81 13-SEP-81 247.946655 08-DEC-81 ADAMS 23-MAY-87 31-MAY-87 24-MAY-87 179.462784 23-AUG-87 4-37 SUNDAY SQL 4-38 67

4-38 SQL> SELECT ename, hiredate, LAST_DAY(hiredate), NEXT_DAY(hiredate, ' '), 2 MONTHS_BETWEEN(SYSDATE, hiredate) "Months", 3 ADD_MONTHS(hiredate, 3) "Review" 4 FROM emp; 4-38 ENAME HIREDATE LAST_DAY(H NEXT_DAY(H Months Review ---------- ---------- ---------- ---------- ---------- ------------ SMITH 17-12 -80 31-12 -80 21-12 -80 268.212076 17-3 -81 ALLEN 20-2 -81 28-2 -81 22-2 -81 266.115302 20-5 -81 WARD 22-2 -81 28-2 -81 01-3 -81 266.050786 22-5 -81 JONES 02-4 -81 30-4 -81 05-4 -81 264.695947 02-7 -81 MARTIN 28-9 -81 30-9 -81 04-10 -81 258.857238 28-12 -81 BLAKE 01-5 -81 31-5 -81 03-5 -81 263.728205 01-8 -81 CLARK 09-6 -81 30-6 -81 14-6 -81 262.470141 09-9 -81 SCOTT 19-4 -87 30-4 -87 26-4 -87 192.14756 19-7 -87 KING 17-11 -81 30-11 -81 22-11 -81 257.212076 17-2 -82 TURNER 08-9 -81 30-9 -81 13-9 -81 259.502399 08-12 -81 ADAMS 23-5 -87 31-5 -87 24-5 -87 191 23-8 -87 JAMES 03-12 -81 31-12 -81 06-12 -81 256.663689 03-3 -82 FORD 03-12 -81 31-12 -81 06-12 -81 256.663689 03-3 -82 MILLER 23-1 -82 31-1 -82 24-1 -82 255 23-4 -82 14 ROUND TRUNC ROUND TRUNC 4-39 ROUND 4-39 SQL> SELECT ROUND('28-OCT-01','MONTH') 2 FROM dual; 4-39 SELECT ROUND('28-OCT-01','MONTH') * ERROR 1 : ORA-01722:???? 68

4-39 28-OCT-01 ROUND TO_DATE ROUND TRUNC 4-40 TO_DATE ROUND 4-40 SQL> SELECT ROUND(TO_DATE('28-OCT-01'),'MONTH') 2 FROM dual; 4-40 ROUND(TO_ --------- 01-NOV-01 4-40 MONTH 10 28 10 ROUND 10 28 11 1 3-40 2001 11 1 4-41 TO_DATE ROUND 4-41 SQL> SELECT ROUND(TO_DATE('28-OCT-01'),'YEAR') 2 FROM dual; 4-41 ROUND(TO_ --------- 01-JAN-02 4-41 YEAR 10 28 ROUND 2001 10 28 2002 1 1 4-41 2002 1 1 4-42 TO_DATE TRUNC 4-42 SQL> SELECT TRUNC(TO_DATE('28-OCT-01'),'MONTH') 2 FROM dual; 4-42 69

TRUNC(TO_ --------- 01-OCT-01 4-42 MONTH 10 28 10 TRUNC 10 28 10 1 4-42 2001 10 1 4-43 TO_DATE TRUNC 4-43 SQL> SELECT TRUNC(TO_DATE('28-OCT-01'),'YEAR') 2 FROM dual; 4-43 TRUNC(TO_ --------- 01-JAN-01 4-43 YEAR 2001 10 28 2001 TRUNC 2001 10 28 2001 1 1 4-43 2001 1 1 4-44 4-44 SQL> SELECT ename, hiredate, ROUND(hiredate,'YEAR'), TRUNC(hiredate,'YEAR' 2 ROUND(hiredate, 'MONTH'), TRUNC(hiredate, 'MONTH') 3 FROM emp 4 WHERE hiredate LIKE '%81'; 4-44 ENAME HIREDATE ROUND(HIR TRUNC(HIR ROUND(HIR TRUNC(HIR -------------------- --------- --------- --------- --------- --------- ALLEN 20-FEB-81 01-JAN-81 01-JAN-81 01-MAR-81 01-FEB-81 WARD 22-FEB-81 01-JAN-81 01-JAN-81 01-MAR-81 01-FEB-81 JONES 02-APR-81 01-JAN-81 01-JAN-81 01-APR-81 01-APR-81 MARTIN 28-SEP-81 01-JAN-82 01-JAN-81 01-OCT-81 01-SEP-81 BLAKE 01-MAY-81 01-JAN-81 01-JAN-81 01-MAY-81 01-MAY-81 CLARK 09-JUN-81 01-JAN-81 01-JAN-81 01-JUN-81 01-JUN-81 KING 17-NOV-81 01-JAN-82 01-JAN-81 01-DEC-81 01-NOV-81 TURNER 08-SEP-81 01-JAN-82 01-JAN-81 01-SEP-81 01-SEP-81 70

JAMES FORD 10 03-DEC-81 01-JAN-82 01-JAN-81 01-DEC-81 01-DEC-81 03-DEC-81 01-JAN-82 01-JAN-81 01-DEC-81 01-DEC-81 Oracle VARCHAR2 CHAR NUMBER VARCHAR2 CHAR DATE NUMBER VARCHAR2 DATE VARCHAR2 Oracle VARCHAR2 CHAR NUMBER VARCHAR2 CHAR DATE NUMBER DATE Oracle SQL SQL Oracle Oracle Oracle 3 3 TO_CHAR TO_NUMBER TO_DATE 3 TO_CHAR fmt fmt 4-45 TO_CHAR 71

4-45 SQL> SELECT ename, TO_CHAR(hiredate, 'DD/MM/YY') 2 FROM emp 3 WHERE hiredate LIKE '%82'; 4-45 ENAME TO_CHAR(HIREDATE -------------------- ---------------- MILLER 23/01/82 4-45 ' DD/MM/YY ' YYYY 2001 YEAR NINETEEN EIGHTY-SEVEN MM MONTH DY 3 DAY DD SP TH 4-46 6 MAY TWO THOUSAND TWO 4-46 SQL> SELECT TO_CHAR(SYSDATE,'fmDD MONTH YEAR') 2 FROM dual; 4-46 TO_CHAR(SYSDATE,'FMDDMONTHYEAR') -------------------------------- 6 MAY TWO THOUSAND TWO fm HIREDATE 4-47 SQL*PLUS HIREDATE 60 72

4-47 SQL> col hiredate for a60 4-48 Name Salary HIREDATE 4-48 SQL> SELECT ename "Name", sal "Salary", 2 TO_CHAR(hiredate, 'fmddspth "of" Month Year fmhh:mi:ss AM') HIREDATE 3 FROM emp; 4-48 Name Salary HIREDATE -------- ---------- ---------------------------------------------------- SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER 14 800 Seventeenth of December Nineteen Eighty 12:00:00 AM 1600 Twentieth of February Nineteen Eighty-One 12:00:00 AM 1250 Twenty-Second of February Nineteen Eighty-One 12:00:00 AM 2975 Second of April Nineteen Eighty-One 12:00:00 AM 1250 Twenty-Eighth of September Nineteen Eighty-One 12:00:00 AM 2850 First of May Nineteen Eighty-One 12:00:00 AM 2450 Ninth of June Nineteen Eighty-One 12:00:00 AM 3000 Nineteenth of April Nineteen Eighty-Seven 12:00:00 AM 5000 Seventeenth of November Nineteen Eighty-One 12:00:00 AM 1500 Eighth of September Nineteen Eighty-One 12:00:00 AM 1100 Twenty-Third of May Nineteen Eighty-Seven 12:00:00 AM 950 Third of December Nineteen Eighty-One 12:00:00 AM 3000 Third of December Nineteen Eighty-One 12:00:00 AM 1300 Twenty-Third of January Nineteen Eighty-Two 12:00:00 AM TO_CHAR fmt fmt 9 0 $ L. MI 73

PR 4-49 Name Annual Salary $. 0 TO_CHAR 4-49 SQL> SELECT ename "Name", TO_CHAR(sal*12, '$99,999.00') "Annual Salary" 2 FROM emp; 4-49 Name Annual Salary ---------- ------------- SMITH $9,600.00 ALLEN $19,200.00 WARD $15,000.00 JONES $35,700.00 MARTIN $15,000.00 BLAKE $34,200.00 CLARK $29,400.00 SCOTT $36,000.00 KING $60,000.00 TURNER $18,000.00 ADAMS $13,200.00 JAMES $11,400.00 FORD $36,000.00 MILLER $15,600.00 14 L Oracle RMB 4-50 4-50 SQL> SELECT ename "Name", TO_CHAR(sal*12, 'L99,999.00') "Annual Salary" 2 FROM emp; 4-50 Name Annual Salary ---------- -------------------- SMITH RMB9,600.00 ALLEN RMB19,200.00 WARD RMB15,000.00 JONES RMB35,700.00 74

MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER 14 RMB15,000.00 RMB34,200.00 RMB29,400.00 RMB36,000.00 RMB60,000.00 RMB18,000.00 RMB13,200.00 RMB11,400.00 RMB36,000.00 RMB15,600.00 Oracle Server 4-51 Oracle Server 4-51 SQL> SELECT ename "Name", TO_CHAR(sal*12, 'L9,999.00') "Annual Salary" 2 FROM emp; 4-51 Name Annual Salary ---------- ------------------- SMITH RMB9,600.00 ALLEN ################### WARD ################### JONES ################### MARTIN ################### BLAKE ################### CLARK ################### SCOTT ################### KING ################### TURNER ################### ADAMS ################### JAMES ################### FORD ################### MILLER ################### 14 # TO_CHAR TO_NUMBER TO_DATE TO_NUMBER [ fmt] 75

TO_DATE [ fmt] 4-40 4-43 TO_DATE TO_NUMBER 73 1973 73 2073 Oracle RR RR 1 0~49 0~49 2002 01-OCT-08 RR 2008 10 1 YY 2008 10 1 2 0~49 50~99 2002 01-OCT-98 RR 1998 10 1 YY 2098 10 1 3 50~99 0~49 1999 01-OCT-08 RR 2008 10 1 YY 1908 10 1 4 50~99 50~99 1999 01-OCT-98 RR 1998 10 1 YY 1998 10 1 4-52 1981 Name Salary hiredate 76

4-52 SQL> SELECT ename "Name", job, sal AS "Salary", hiredate 2 FROM emp 3 WHERE hiredate BETWEEN '01-Jan-81' AND '31-Dec-81' 4 ORDER BY hiredate; 4-52 Name JOB Salary HIREDATE -------------------- ------------------ ---------- --------- ALLEN SALESMAN 1600 20-FEB-81 WARD SALESMAN 1250 22-FEB-81 JONES MANAGER 2975 02-APR-81 BLAKE MANAGER 2850 01-MAY-81 CLARK MANAGER 2450 09-JUN-81 TURNER SALESMAN 1500 08-SEP-81 MARTIN SALESMAN 1250 28-SEP-81 KING PRESIDENT 5000 17-NOV-81 JAMES CLERK 950 03-DEC-81 FORD ANALYST 3000 03-DEC-81 10 4-52 4-53 4-53 SQL> SELECT ename "Name", job, sal AS "Salary", hiredate 2 FROM emp 3 WHERE hiredate BETWEEN '01-1 -81' AND '31-12 -81' 4 ORDER BY hiredate; 4-53 Name JOB Salary HIREDATE ---------- --------- ---------- ----------- ALLEN SALESMAN 1600 20-2 -81 WARD SALESMAN 1250 22-2 -81 JONES MANAGER 2975 02-4 -81 BLAKE MANAGER 2850 01-5 -81 CLARK MANAGER 2450 09-6 -81 TURNER SALESMAN 1500 08-9 -81 MARTIN SALESMAN 1250 28-9 -81 KING PRESIDENT 5000 17-11 -81 JAMES CLERK 950 03-12 -81 77

FORD ANALYST 3000 03-12 -81 10 Oracle9i Oracle9i YY emp 2081 Oracle9i RR 4-54 YY Oracle9i 4-54 SQL> SELECT ename "Name", job, sal AS "Salary", hiredate 2 FROM emp 3 WHERE hiredate BETWEEN TO_DATE('01-Jan-81', 'DD-MON-YY') 4 AND TO_DATE('31-Dec-81', 'DD-MON-YY') 5 ORDER BY hiredate; 4-54 RR 4-55 4-55 SQL> SELECT ename "Name", job, sal AS "Salary", hiredate 2 FROM emp 3 WHERE hiredate BETWEEN TO_DATE('01-Jan-81', 'DD-MON-RR') 4 AND TO_DATE('31-Dec-81', 'DD-MON-RR') 5 ORDER BY hiredate; 4-55 Name JOB Salary HIREDATE -------------------- ------------------ ---------- --------- ALLEN SALESMAN 1600 20-FEB-81 WARD SALESMAN 1250 22-FEB-81 JONES MANAGER 2975 02-APR-81 BLAKE MANAGER 2850 01-MAY-81 CLARK MANAGER 2450 09-JUN-81 TURNER SALESMAN 1500 08-SEP-81 MARTIN SALESMAN 1250 28-SEP-81 KING PRESIDENT 5000 17-NOV-81 JAMES CLERK 950 03-DEC-81 FORD ANALYST 3000 03-DEC-81 10 78

4-52 Oracle9i RR 2002 02 1998 98 Oracle dual SQL SQL*PLUS TO_CHAR ROUND TRUNC TO_CHAR RR YY 79

Oracle SQL NULL 3 NULL 5-1 5-1 SQL> SELECT ename, job, sal, comm. 2 FROM emp 3 WHERE job IN ('CLERK','SALESMAN') 4 ORDER BY job; 5-1 ENAME JOB SAL COMM ---------- ---------- ---------- ---------- SMITH CLERK 800 ADAMS CLERK 1100 MILLER CLERK 1300 JAMES CLERK 950 ALLEN SALESMAN 1600 300 WARD SALESMAN 1250 500 MARTIN SALESMAN 1250 1400 TURNER SALESMAN 1500 0 8 5-1 COMM CLERK COMM SALESMAN CLERK CLERK Oracle NULL NULL NULL unavailable unassigned undefined unknown immeasurable inapplicable NULL

BMW NULL NULL NULL NULL + 5-2 5-2 SQL> SELECT ename "Name", sal+comm "Income", job 2 FROM emp 3 WHERE job NOT LIKE 'SALES%' 4 ORDER BY job; 5-2 Name Income JOB -------------------- ---------- --------- SCOTT ANALYST FORD ANALYST SMITH CLERK JAMES CLERK ADAMS CLERK MILLER CLERK JONES MANAGER BLAKE MANAGER CLARK MANAGER 81

KING 10 PRESIDENT 5-2 21 NULL NULL NULL 10% NULL NULL 5-3 5-3 SQL> SELECT empno, ename, sal, job, comm 2 FROM emp 3 WHERE comm = NULL; 5-3 Oracle 5-4 5-4 SQL> SELECT empno, ename, sal, job, comm 2 FROM emp 3 WHERE comm!= NULL; 5-4 NULL NULL Oracle IS NULL IS NULL 5-3 5-5 82

5-5 SQL> SELECT empno, ename, sal, job, comm 2 FROM emp 3 WHERE comm IS NULL; 5-5 EMPNO ENAME SAL JOB COMM -------- --------------- ---------- ------------------ ---------- 7369 SMITH 800 CLERK 7566 JONES 2975 MANAGER 7698 BLAKE 2850 MANAGER 7782 CLARK 2450 MANAGER 7788 SCOTT 3000 ANALYST 7839 KING 5000 PRESIDENT 7876 ADAMS 1100 CLERK 7900 JAMES 950 CLERK 7902 FORD 3000 ANALYST 7934 MILLER 1300 CLERK 10 IS NOT NULL 5-4 5-6 5-6 SQL> SELECT empno, ename, sal, job, comm 2 FROM emp 3 WHERE comm IS NOT NULL; 5-6 EMPNO ENAME SAL JOB COMM ---------- -------------------- ---------- -------------- ------- 7499 ALLEN 1600 SALESMAN 300 7521 WARD 1250 SALESMAN 500 7654 MARTIN 1250 SALESMAN 1400 7844 TURNER 1500 SALESMAN 0 5-6 NULL 5-7 ORDER BY 83

5-7 SQL> SELECT ename, job, comm 2 FROM emp 3 ORDER BY comm; 5-7 ENAME JOB COMM -------------------- ------------------ ---------- TURNER SALESMAN 0 ALLEN SALESMAN 300 WARD SALESMAN 500 MARTIN SALESMAN 1400 SMITH CLERK JONES MANAGER JAMES CLERK MILLER CLERK FORD ANALYST ADAMS CLERK BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT 14 5-7 NULL NULL 5-8 ORDER BY DESC 5-8 SQL> SELECT ename, job, comm 2 FROM emp 3 ORDER BY comm DESC; 5-8 ENAME JOB COMM -------------------- --------------- --------- SMITH CLERK JONES MANAGER CLARK MANAGER BLAKE MANAGER SCOTT ANALYST KING PRESIDENT 84

JAMES CLERK MILLER CLERK FORD ANALYST ADAMS CLERK MARTIN SALESMAN 1400 WARD SALESMAN 500 ALLEN SALESMAN 300 TURNER SALESMAN 0 14 5-7 NULL Oracle AND / OR / NOT 3 AND / OR / 1 2 AND OR 1 2 = 2 1 5.1 T F Oracle NULL 1AND 2 1 2 NULL AND F AND F = F F AND T = F F AND NULL = F T AND F = F T AND T = T T AND NULL IS NULL NULL AND F = F NULL AND T IS NULL NULL AND NULL IS NULL 5.1 AND AND F NULL T AND F F F AND NULL NULL 85

F T AND T PR Oracle NULL T AND NULL = NULL 2 2-1 2-8 2-1 1500 2-8 AND 5-9 5-9 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE sal >= 1500 4 AND job IN ('SALESMAN', 'CLERK', 'MANAGER') 5 ORDER BY job; 5-9 EMPNO ENAME SAL JOB ---------- -------------------- ---------- -------- 7566 JONES 2975 MANAGER 7698 BLAKE 2850 MANAGER 7782 CLARK 2450 MANAGER 7499 ALLEN 1600 SALESMAN 7844 TURNER 1500 SALESMAN OR OR T OR T = T T OR F = T T OR NULL = T F OR T = T F OR F = F F OR NULL IS NULL NULL OR T = T NULL OR F IS NULL NULL AND NULL IS NULL 5.1 OR OR 86

T NULL F OR T T T OR NULL NULL T F OR F PR 5-9 WHERE AND OR 5-10 5-10 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE sal >= 1500 4 OR job IN ('SALESMAN', 'CLERK', 'MANAGER') 5 ORDER BY job; 5-10 EMPNO ENAME SAL JOB ---------- ---------- ---------- --------- 7788 SCOTT 3000 ANALYST 7902 FORD 3000 ANALYST 7369 SMITH 800 CLERK 7876 ADAMS 1100 CLERK 7934 MILLER 1300 CLERK 7900 JAMES 950 CLERK 7566 JONES 2975 MANAGER 7782 CLARK 2450 MANAGER 7698 BLAKE 2850 MANAGER 7839 KING 5000 PRESIDENT 7499 ALLEN 1600 SALESMAN 7654 MARTIN 1250 SALESMAN 7844 TURNER 1500 SALESMAN 7521 WARD 1250 SALESMAN 14 NOT NOT 87

NOT T = F NOT F = T Oracle NULL NOT NULL NOT NULL NULL NOT NOT LIKE IS NOT NULL NOT BETWEEN AND 1 2 3 4 IS NULL IS NOT NULL LIKE NOT LIKE IN NOT IN 5 BETWEEN NOT BETWEEN 6 NOT 7 AND 8 OR 5-11 5-11 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE job = 'CLERK' 4 OR job = 'SALESMAN' 5 AND sal >= 1300; 5-11 EMPNO ENAME SAL JOB ---------- -------------------- ---------- -------- 7369 SMITH 800 CLERK 7499 ALLEN 1600 SALESMAN 7844 TURNER 1500 SALESMAN 7876 ADAMS 1100 CLERK 7900 JAMES 950 CLERK 7934 MILLER 1300 CLERK 6 Oracle 1300 SALESMAN CLERK Oracle CLERK 1300 SALESMAN 88

WHERE 5-12 5-12 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE (job = 'CLERK' 4 OR job = 'SALESMAN') 5 AND sal >= 1300; 5-12 EMPNO ENAME SAL JOB ---------- -------------------- ---------- -------- 7499 ALLEN 1600 SALESMAN 7844 TURNER 1500 SALESMAN 7934 MILLER 1300 CLERK Oracle CLERK SALESMAN 1300 Oracle 1300 CLERK SALESMAN 2 2-2 5-13 5-13 SQL> SELECT empno, ename, sal 2 FROM emp 3 WHERE sal BETWEEN 1500 AND 2900; 5-13 EMPNO ENAME SAL ---------- -------------------- ---------- 7499 ALLEN 1600 7698 BLAKE 2850 7782 CLARK 2450 7844 TURNER 1500 5-14 5-13 5-14 89

SQL> SELECT empno, ename, sal 2 FROM emp 3 WHERE sal >= 1500 4 AND sal <= 2900; 5-14 EMPNO ENAME SAL ---------- -------------------- ---------- 7499 ALLEN 1600 7698 BLAKE 2850 7782 CLARK 2450 7844 TURNER 1500 2 2-8 5-15 5-15 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE job IN ('SALESMAN', 'CLERK', 'MANAGER'); 5-15 EMPNO ENAME SAL JOB ---------- ---------- ---------- -------- 7369 SMITH 800 CLERK 7499 ALLEN 1600 SALESMAN 7521 WARD 1250 SALESMAN 7566 JONES 2975 MANAGER 7654 MARTIN 1250 SALESMAN 7698 BLAKE 2850 MANAGER 7782 CLARK 2450 MANAGER 7844 TURNER 1500 SALESMAN 7876 ADAMS 1100 CLERK 7900 JAMES 950 CLERK 7934 MILLER 1300 CLERK 11 5-16 5-15 5-16 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE job = 'SALESMAN' 4 OR job = 'CLERK' 90

5 OR job = 'MANAGER'; 5-16 EMPNO ENAME SAL JOB ---------- ---------- ---------- -------- 7369 SMITH 800 CLERK 7499 ALLEN 1600 SALESMAN 7521 WARD 1250 SALESMAN 7566 JONES 2975 MANAGER 7654 MARTIN 1250 SALESMAN 7698 BLAKE 2850 MANAGER 7782 CLARK 2450 MANAGER 7844 TURNER 1500 SALESMAN 7876 ADAMS 1100 CLERK 7900 JAMES 950 CLERK 7934 MILLER 1300 CLERK 11 5-14 5-16 AND OR BETWEEN AND IN NVL 5-2 5-17 5-17 SQL> SELECT ename "Name", sal+comm "Income", job 2 FROM emp 3 WHERE job NOT LIKE 'SALES%' 4 ORDER BY job; 5-17 Name Income JOB -------------------- ---------- --------- SCOTT ANALYST FORD ANALYST SMITH CLERK JAMES CLERK ADAMS CLERK 91

MILLER JONES BLAKE CLARK KING 10 CLERK MANAGER MANAGER MANAGER PRESIDENT sal+comm comm NULL Income Oracle NVL 5-17 5-18 SQL 5-18 SQL> SELECT ename "Name", sal+nvl(comm,0) "Income", job 2 FROM emp 3 WHERE job NOT LIKE 'SALES%' 4 ORDER BY job; 5-18 Name Income JOB ---------- ---------- --------- SCOTT 3000 ANALYST FORD 3000 ANALYST SMITH 800 CLERK JAMES 950 CLERK ADAMS 1100 CLERK MILLER 1300 CLERK JONES 2975 MANAGER BLAKE 2850 MANAGER CLARK 2450 MANAGER KING 5000 PRESIDENT 10 5-18 NVL NULL NVL 1 2 1 NULL NVL 2 1 NULL 1 2 1 2 NVL comm 0 NVL TO_CHAR(comm) No Commission NVL hiredate 31-DEC-99 92

Oracle SQL NVL NULL SQL Oracle DECODE 5-19 DECODE job 5-19 SQL> SELECT ename "Name", job, sal "Salary", 2 DECODE(job, 'SALESMAN', sal*1.15, 3 'CLERK', sal*1.20, 4 'ANALYST', sal*1.25, 5 sal*1.40) "New Salary" 6 FROM emp 7 ORDER BY job; 5-19 Name JOB Salary New Salary ---------- --------- ---------- ---------- SCOTT ANALYST 3000 3750 FORD ANALYST 3000 3750 SMITH CLERK 800 960 ADAMS CLERK 1100 1320 MILLER CLERK 1300 1560 JAMES CLERK 950 1140 JONES MANAGER 2975 4165 CLARK MANAGER 2450 3430 BLAKE MANAGER 2850 3990 KING PRESIDENT 5000 7000 ALLEN SALESMAN 1600 1840 MARTIN SALESMAN 1250 1437.5 TURNER SALESMAN 1500 1725 WARD SALESMAN 1250 1437.5 14 5-19 DECODE 1 job SALESMAN DECODE sal*1.15 2 93

2 job CLERK DECODE sal*1.20 3 3 job ANALYST DECODE sal*1.25 4 4 DECODE sal*1.40 Oracle Oracle Oracle 5-20 5-20 SQL> SELECT ename "Name", NVL(TO_CHAR(comm), ename ' is not a Salesperson!') "Commission" 2 FROM emp 3 ORDER BY 2; 5-20 Name Commission ---------- ---------------------------- TURNER 0 MARTIN 1400 ALLEN 300 WARD 500 ADAMS BLAKE CLARK FORD JAMES JONES KING MILLER SCOTT SMITH 14 ADAMS is not a Salesperson! BLAKE is not a Salesperson! CLARK is not a Salesperson! FORD is not a Salesperson! JAMES is not a Salesperson! JONES is not a Salesperson! KING is not a Salesperson! MILLER is not a Salesperson! SCOTT is not a Salesperson! SMITH is not a Salesperson! 5-20 NVL TO_CHAR(comm), ename ' is not a Salesperson!' 1 Oracle TO_CHAR comm 94

2 NVL TO_CHAR comm 3 TO_CHAR comm TO_CHAR comm 4 TO_CHAR comm ename ' is not a Salesperson!' TO_CHAR comm ASCII 5-20 5-21 5-21 SQL> SELECT ename "Name", NVL(TO_CHAR(comm), ename ' is not a Salesperson!') "Commission" 2 FROM emp 3 ORDER BY "Commission"; 5-21 Name Commission ---------- --------------------------- TURNER 0 MARTIN 1400 ALLEN 300 WARD 500 ADAMS ADAMS is not a Salesperson! BLAKE BLAKE is not a Salesperson! CLARK CLARK is not a Salesperson! FORD FORD is not a Salesperson! JAMES JAMES is not a Salesperson! JONES JONES is not a Salesperson! KING KING is not a Salesperson! MILLER MILLER is not a Salesperson! SCOTT SCOTT is not a Salesperson! SMITH SMITH is not a Salesperson! 14 5-21 5-20 Oracle 9.0 95

NVL2 NULLIF COALESCE Oracle9i CASE Oracle9i SQL SQL NVL2 Oracle9i NVL2 NVL 5-22 5-22 SQL> SELECT ename "Name", NVL2(comm,'sal+comm',sal) "Income", job 2 FROM emp 3 WHERE job NOT LIKE 'SALES%' 4 ORDER BY job; 5-22 Name Income JOB ---------- --------------------------------- --------- SCOTT 3000 ANALYST FORD 3000 ANALYST SMITH 800 CLERK JAMES 950 CLERK ADAMS 1100 CLERK MILLER 1300 CLERK JONES 2975 MANAGER BLAKE 2850 MANAGER CLARK 2450 MANAGER KING 5000 PRESIDENT 10 5-22 NVL2 comm,'sal+comm',sal "Income" 1 NVL2 comm 2 comm NULL sal+comm 3 comm NULL sal 96

4 Income NVL2 NVL2 1 2 3 NVL2 1 2 3 1 NULL NVL2 2 1 NULL NVL2 3 2 3 LONG 1 2 3 Oracle 3 2 3 Oracle NVL2 2 2 CHAR NVL2 VARCHAR2 NULLIF Oracle9i 5-23 5-23 SQL> SELECT ename, job, LENGTH(ename) "Name_Length", LENGTH(job) "Job_Lenght", 2 NULLIF(LENGTH(ename),LENGTH(job)) "Comparision" 3 FROM emp; 5-23 ENAME JOB Name_Length Job_Lenght Comparision ---------- --------- ----------- ---------- ----------- SMITH CLERK 5 5 ALLEN SALESMAN 5 8 5 WARD SALESMAN 4 8 4 JONES MANAGER 5 7 5 MARTIN SALESMAN 6 8 6 BLAKE MANAGER 5 7 5 CLARK MANAGER 5 7 5 SCOTT ANALYST 5 7 5 KING PRESIDENT 4 9 4 TURNER SALESMAN 6 8 6 ADAMS CLERK 5 5 JAMES CLERK 5 5 FORD ANALYST 4 7 4 MILLER CLERK 6 5 6 14 5-23 NULLIF LENGTH ename LENGTH job NULLIF LENGTH ename 97

LENGTH job NULL LENGTH ename ename NULLIF NULLIF 1 2 NULLIF 1 2 NULLIF 1 2 NULL 1 NULLIF 1 NULL Oracle9i COALESCE emp_null SQL 5-24 5-25 SQL SQL DDL 5-24 SQL> create table emp_null 2 as select ename, sal, comm 3 from emp; 5-24 emp_null emp 3 ename sal comm 5-25 SQL> insert into emp_null(ename, sal, comm) 2 values ('QUEEN', NULL, NULL); 5-25 1 5-25 SQL emp_null ename QUEEN sal comm 5-26 5-25 5-26 SQL> SELECT * FROM emp_null; 5-26 ENAME SAL COMM ---------- ---------- ---------- 98

SMITH 800 ALLEN 1600 300 WARD 1250 500 JONES 2975 MARTIN 1250 1400 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500 0 ADAMS 1100 JAMES 950 FORD 3000 MILLER 1300 QUEEN 15 5-26 QUEEN sal comm QUEEN 5-27 COALESCE 5-27 SQL> SELECT ename "Name", sal "Salary", comm "Commission", 2 COALESCE(comm, sal*0.1, 100) "New Commission" 3 FROM emp_null; 5-27 Name Salary Commission New Commission ---------- ---------- ---------- -------------- SMITH 800 80 ALLEN 1600 300 300 WARD 1250 500 500 JONES 2975 297.5 MARTIN 1250 1400 1400 BLAKE 2850 285 CLARK 2450 245 SCOTT 3000 300 KING 5000 500 TURNER 1500 0 0 ADAMS 1100 110 JAMES 950 95 FORD 3000 300 MILLER 1300 130 99

QUEEN 100 15 5-27 COALESCE comm COALESCE comm ALLEN comm 300 New Commission 300 comm sal*0.1 sae COALESCE sal*0.1 SMITH comm sal=800 COALESCE 800*0.1=80 comm sal*0.1 sae COALESCE 100 QUEEN comm sal COALESCE 100 COALESCE COALESCE 1 2 3 n 1 2 3 n CASE Oracle9i DECODE CASE 5-28 5-19 5-28 5-28 SQL> SELECT ename "Name", job, sal "Salary", 2 CASE job WHEN 'SALESMAN' THEN sal*1.15 3 WHEN 'CLERK' THEN sal*1.20 4 WHEN 'ANALYST' THEN sal*1.25 5 ELSE sal*1.40 END "New Salary" 6 FROM emp 7 ORDER BY job; 5-28 Name JOB Salary New Salary ---------- --------- ---------- ---------- SCOTT ANALYST 3000 3750 FORD ANALYST 3000 3750 SMITH CLERK 800 960 ADAMS CLERK 1100 1320 MILLER CLERK 1300 1560 JAMES CLERK 950 1140 JONES MANAGER 2975 4165 CLARK MANAGER 2450 3430 BLAKE MANAGER 2850 3990 KING PRESIDENT 5000 7000 100

ALLEN SALESMAN 1600 1840 MARTIN SALESMAN 1250 1437.5 TURNER SALESMAN 1500 1725 WARD SALESMAN 1250 1437.5 14 5-28 CASE job SALESMAN CASE sal*1.15 job CLERK CASE sal*1.20 job ANALYST CASE sal*1.25 CASE sal*1.40 NULL NULL NULL NULL 3 NULL AND NULL OR NULL NOT NVL DECODE Oracle9i SQL NVL2 NULLIF COALESCE CASE 101

/ Oracle 5 COUNT AVE SUM MAX MIN 5 COUNT COUNT COUNT {*[ DISTINCT ALL] } 2 6-1 Oracle Oracle 6-1 SQL> SELECT COUNT(*) 2 FROM emp; 6-1 COUNT(*) ---------- 14

COUNT(*) 6-2 6-2 SQL> SELECT COUNT(mgr) 2 FROM emp; 6-2 COUNT(MGR) ---------- 13 mgr NULL COUNT COUNT(mgr) mgr COUNT AVE SUM AVE AVE [DISTINCT ALL] SUM SUM [DISTINCT ALL] 6-3 6-3 SQL> SELECT AVG(sal) "Average Salary", SUM(sal) "Summary", COUNT(sal) "Records" 2 FROM emp; 6-3 Average Salary Summary Records -------------- ---------- ---------- 2073.21429 29025 14 6-3 103

AVE SUM MAX MIN MAX MAX [DISTINCT ALL] MIN MIN [DISTINCT ALL] 6-4 6-4 SQL> SELECT MIN(sal) "Lowest Salary", MAX(sal) "Highest Salary" 2 FROM emp; 6-4 Lowest Salary Highest Salary ------------- -------------- 800 5000 AVE SUM MIN MAX 6-5 MIN MAX 6-5 SQL> SELECT MIN(job), MAX(job) 2 FROM emp; 6-5 MIN(job) MAX(job) --------- --------- ANALYST SALESMAN 6-6 6-6 SQL> SELECT MIN(hiredate) "First Day", MAX(hiredate) "Last Day" 104

2 FROM emp; 6-6 First Day Last Day ---------- ---------- 17-12 -1980 23-5 1987 GROUP BY job 6-7 6-7 SQL> SELECT job, AVG(sal) "Average Salary" 2 FROM emp 3 GROUP BY job; 6-7 job Average Salary --------- -------------- ANALYST 3000 CLERK 1037.5 MANAGER 2758.33333 PRESIDENT 5000 SALESMAN 1400 6-7 president analyst job clerk 6-7 GROUP BY GROUP BY Oracle ORDER BY 6-8 job 105

average salary 6-8 SQL> SELECT job, AVG(sal) "Average Salary" 2 FROM emp 3 GROUP BY job 4 ORDER BY "Average Salary" DESC; 6-8 job Average Salary --------- -------------- PRESIDENT 5000 ANALYST 3000 MANAGER 2758.33333 SALESMAN 1400 CLERK 1037.5 6-8 ORDER BY Oracle DESC ORDER BY GROUP BY SELECT 6-9 job job SELECT job 6-9 SQL> SELECT AVG(sal) "Average Salary" 2 FROM emp 3 GROUP BY job; 6-9 Average Salary -------------- 3000 1037.5 2758.33333 5000 1400 106

6-9 6-10 Oracle 6-10 SQL> SELECT job, AVG(sal) 2 FROM emp; 6-10 SELECT job, AVG(sal) * ERROR 1 : ORA-00937: GROUP BY 6-10 Oracle SELECT job Oracle job emp 14 SELECT AVG(sal) Oracle emp Oracle 6-10 GROUP BY job 6-11 6-11 SQL> SELECT job, AVG(sal) 2 FROM emp 3 GROUP BY job; 6-11 job AVG(SAL) --------- ---------- ANALYST 3000 CLERK 1037.5 MANAGER 2758.33333 107

PRESIDENT 5000 SALESMAN 1400 6-11 emp job AVG(sal) 6-12 GROUP BY 6-12 SQL> SELECT job, AVG(sal) 2 FROM emp 3 GROUP BY job 4 ORDER BY deptno; 6-12? 6-12 ORDER BY deptno GROUP BY 6-12 6-12 ORDER BY deptno * ERROR 4 : ORA-00979: GROUP BY 1500 6-7 6-13 6-13 SQL> SELECT job, AVG(sal) 2 FROM emp 3 WHERE AVG(sal) > 1500 4 GROUP BY job; 6-13 WHERE AVG(sal) > 1500 * ERROR 3 : ORA-00934: 6-13 WHERE Oracle HAVING 6-13 108

WHERE HAVING 6-14 6-14 SQL> SELECT job, AVG(sal) 2 FROM emp 3 HAVING AVG(sal) > 1500 4 GROUP BY job; 6-14 JOB AVG(SAL) --------- ---------- ANALYST 3000 MANAGER 2758.33333 PRESIDENT 5000 HAVING Oracle 1 2 3 HAVING HAVING 6-14 Oracle HAVING HAVING GROUP BY SQL 6-15 6-15 SQL> SELECT job, AVG(sal) 2 FROM emp 3 GROUP BY job 4 HAVING AVG(sal) > 1500 5 ORDER BY 2; 6-15 JOB AVG(SAL) --------- ---------- MANAGER 2758.33333 ANALYST 3000 PRESIDENT 5000 6-15 ORDER BY 2 ORDER BY AVG(sal) OCP Oracle CERTIFIED PROFESSIONAL Oracle SQL 109

6-7 job job job job job 6-16 6-16 SQL> SELECT MIN(AVG(sal)), MAX(AVG(sal)) 2 FROM emp 3 WHERE job NOT LIKE 'PRESI%' 4 GROUP BY job; 6-16 MIN(AVG(SAL)) MAX(AVG(SAL)) ------------- ------------- 1037.5 3000 6-16 3 job job job 6-16 Oracle Oracle 6-16 1 emp job PRESI 2 job 3 4 SQL GROUP BY Oracle COUNT * NULL 110

6-17 6-17 SQL> SELECT AVG(comm) "Average Commission" 2 FROM emp; 6-17 Average Commission ------------------ 550 6-17 AVG(comm) comm NULL SALESMAN 6-17 SALESMAN 6-18 SQL 6-18 SQL> SELECT AVG(comm) "Average Commission", SUM(comm) "Summary Commission", 2 job, COUNT(comm) "Records" 3 FROM emp 4 GROUP BY job; 6-18 Average Commission Summary Commission job Records ------------------ ------------------ --------- ---------- ANALYST 0 CLERK 0 MANAGER 0 PRESIDENT 0 550 2200 SALESMAN 4 6-18 Oracle 2200/ 4+0+0+0+0 = 550 NVL 6-17 6-19 SQL 6-19 SQL> SELECT AVG(NVL(comm, 0)) "Average Commission" 111

2 FROM emp; 6-19 Average Commission ------------------ 157.142857 6-19 6-20 SQL 6-20 SQL> SELECT AVG(NVL(comm, 0)) "Average Commission", 2 SUM(NVL(comm, 0)) "Summary Commission", 3 job, COUNT(NVL(comm, 0)) "Records" 4 FROM emp 5 GROUP BY job; 6-20 Average Commission Summary Commission job Records ------------------ ------------------ --------- ---------- 0 0 ANALYST 2 0 0 CLERK 4 0 0 MANAGER 3 0 0 PRESIDENT 1 550 2200 SALESMAN 4 6-20 2200+0+0+0+0 / 2+4+3+1+4 =157.142871 6-17 NVL 6-19 NVL 6-19 6-17 112

Oracle STDDEV VARIANCE Oracle9i SQL Reference 6-139 337 6-195 393 GROUP BY HAVING WHERE MIN MAX DSS Decision Support System 5 MIN MAX GROUP BY GROUP BY GROUP BY 113

Oracle Normalization Normalization Normalization 7-1 234510 XX XX XX 666 174 XXXX XX XX 168268 2560 1000 0.20 200.00 2351 5000 0.10 500.00 2354 1000 0.20 200.00 2546 100 1.00 100.00 2200 20 2.00 40.00 1 040.00 XX XX XX 368 666 1744 666 9444 7-1

Primary Key 7-2 168268 2560 0.20 234510 2351 0.10 2354 0.20 2546 1.00 2200 2.00 168269 2560 0.20 234510 2351 0.10 2354 0.20 168288 2351 0.10 234521 2546 1.00 2200 2.00 7-2 7-2 Entity Integrity 7-2 Repeating Groups Repeating Groups Primary Key Entity Integrity Primary Key Entity Integrity Primary Key NULL Primary Key Primary Key Oracle Entity Integrity Entity Integrity 7-2 7-2 Order 7-3 115

168268 2560 0.20 234510 168268 2351 0.10 168268 2354 0.20 168268 2546 1.00 168268 2200 2.00 168269 2560 0.20 234510 168269 2351 0.10 168269 2354 0.20 168288 2351 0.10 234521 168288 2546 1.00 168288 2200 2.00 7-3 7-3 2351 2351 2351 Order 7-3 Order 1NF 1NF 1NF 1 2 Repeating Groups 3 7-3 Order 7-4 116

7-4 7-4 Partial Dependency Product Product Order Order Order Product 7-5 7-6 7-5 Order 7-6 Product Product Product Order Order Product Product Order Foreign Key Primary Key NULL Referential Integrity 117

1 NULL 2 Foreign Key Oracle Referential Integrity Repeating Groups 2NF 7-5 Order 2NF 2NF 1 1NF 2 1NF 2NF 7-5 Order 234510 Order 8 Order 7.6 7-6 Order 7.6 118

Transitive Dependency Supplier Supplier Order 7-7 7-8 7-9 3 7-7 Order 7-8 Supplier 7-9 Product Repeating Group 3NF 168268 2560 234510 168268 2351 234510 168268 2354 234510 168268 2546 234510 168268 2200 234510 168269 2560 234510 168269 2351 234510 168269 2354 234510 168288 2351 234521 168288 2546 234521 168288 2200 234521 7-7 Order 234510 234521 234530 7-8 Supplier 2560 0.20 2351 0.10 2354 0.20 2546 1.00 2200 2.00 7-9 Product 3NF 119

1 2NF 2 3NF 1NF 2NF DML 3NF Oracle Join Oracle 4 Equijoin Self join Nonequijoin Outer join 7-1 7-1 SQL> SELECT empno, ename, sal, emp.deptno, loc 2 FROM emp, dept 3 WHERE emp.deptno = dept.deptno 4 ORDER BY loc; 120

7-1 EMPNO ENAME SAL DEPTNO LOC ---------- ---------- ---------- ---------- -------- 7499 ALLEN 1600 30 CHICAGO 7521 WARD 1250 30 CHICAGO 7654 MARTIN 1250 30 CHICAGO 7900 JAMES 950 30 CHICAGO 7844 TURNER 1500 30 CHICAGO 7698 BLAKE 2850 30 CHICAGO 7369 SMITH 800 20 DALLAS 7902 FORD 3000 20 DALLAS 7876 ADAMS 1100 20 DALLAS 7566 JONES 2975 20 DALLAS 7788 SCOTT 3000 20 DALLAS 7782 CLARK 2450 10 NEW YORK 7839 KING 5000 10 NEW YORK 7934 MILLER 1300 10 NEW YORK 14 7-1 deptno FROM FROM emp dept WHERE WHERE emp.deptno = dept.deptno n n 1 Oracle 1500 7-2 7-2 SQL> SELECT emp.empno, emp.ename, emp.sal, 2 emp.deptno, dept.loc 3 FROM emp, dept 4 WHERE emp.deptno = dept.deptno 5 AND sal >= 1500 6 ORDER BY loc; 7-2 121

EMPNO ENAME SAL DEPTNO LOC ---------- ---------- ---------- ---------- -------- 7499 ALLEN 1600 30 CHICAGO 7698 BLAKE 2850 30 CHICAGO 7844 TURNER 1500 30 CHICAGO 7566 JONES 2975 20 DALLAS 7902 FORD 3000 20 DALLAS 7788 SCOTT 3000 20 DALLAS 7782 CLARK 2450 10 NEW YORK 7839 KING 5000 10 NEW YORK 8 7-2 WHERE AND 7-2 INVOICE-DETAILS Oracle 7-2 7-3 7-3 SQL> SELECT e.empno, e.ename, e.sal, e.deptno, d.loc 2 FROM emp e, dept d 3 WHERE e.deptno = d.deptno 4 AND e.sal >= 1500 5 ORDER BY d.loc; 7-3 EMPNO ENAME SAL DEPTNO LOC ---------- ---------- ---------- ---------- -------- 7499 ALLEN 1600 30 CHICAGO 7698 BLAKE 2850 30 CHICAGO 7844 TURNER 1500 30 CHICAGO 7566 JONES 2975 20 DALLAS 7902 FORD 3000 20 DALLAS 7788 SCOTT 3000 20 DALLAS 7782 CLARK 2450 10 NEW YORK 7839 KING 5000 10 NEW YORK 8 FROM 122

30 7-3 WHERE 7-4 7-4 SQL> SELECT e.empno, e.ename, e.sal, e.deptno, d.loc 2 FROM emp e, dept d 3 ORDER BY d.loc; 7-4 EMPNO ENAME SAL DEPTNO LOC ---------- ---------- ---------- ---------- -------- 7369 SMITH 800 20 BOSTON 7499 ALLEN 1600 30 BOSTON 7521 WARD 1250 30 BOSTON 7698 BLAKE 2850 30 BOSTON 7782 CLARK 2450 10 BOSTON 7654 MARTIN 1250 30 BOSTON 7566 JONES 2975 20 BOSTON 7788 SCOTT 3000 20 BOSTON 7844 TURNER 1500 30 BOSTON 7934 MILLER 1300 10 BOSTON 7902 FORD 3000 20 BOSTON 7900 JAMES 950 30 BOSTON 7876 ADAMS 1100 20 BOSTON 7839 KING 5000 10 BOSTON 7369 SMITH 800 20 CHICAGO 7499 ALLEN 1600 30 CHICAGO 7521 WARD 1250 30 CHICAGO 7566 JONES 2975 20 CHICAGO 7788 SCOTT 3000 20 CHICAGO 7844 TURNER 1500 30 CHICAGO 7900 JAMES 950 30 CHICAGO 7844 TURNER 1500 30 NEW YORK 7876 ADAMS 1100 20 NEW YORK 7900 JAMES 950 30 NEW YORK 123

7902 FORD 3000 20 NEW YORK 7934 MILLER 1300 10 NEW YORK 56 7-4 56 7-5 7-6 emp dept 7-5 SQL> SELECT COUNT(*) 2 FROM emp; 7-5 COUNT(*) ---------- 14 7-6 SQL> SELECT COUNT(*) 2 FROM dept; 7-6 COUNT(*) ---------- 4 7-5 7-6 emp 14 dept 4 7-4 14 4=56 7-4 1,000,000 10,000=10,000,000,000 join condition join condition WHERE 124

7-7 SQL> SELECT w.empno, w.ename, w.job, w.mgr, m.ename "M_Name", m.job "M_Job" 2 FROM emp w, emp m 3 WHERE w.mgr = m.empno 4 AND w.job LIKE 'ANA%'; 7-7 EMPNO ENAME JOB MGR M_Name M_Job ---------- ---------- --------- ---------- ---------- ------- 7788 SCOTT ANALYST 7566 JONES MANAGER 7902 FORD ANALYST 7566 JONES MANAGER Oracle Self join Self join 7-8 7-9 7-7 7-8 SQL> SELECT ename, job, mgr 2 FROM emp 3 WHERE job LIKE 'ANA%'; 7-8 ENAME JOB MGR ---------- --------- ---------- SCOTT ANALYST 7566 FORD ANALYST 7566 7-9 SQL> SELECT empno, ename, job 2 FROM emp 3 WHERE empno = 7566; 7-9 EMPNO ENAME JOB 125

---------- ---------- --------- 7566 JONES MANAGER MANAGER MANAGER EMP 7-10 7-10 SQL> CREATE TABLE manager 2 AS 3 SELECT * 4 FROM emp; 7-10 7-11 manager emp 7-7 Self join 7-11 SQL> SELECT w.empno, w.ename, w.job, w.mgr, m.ename "M_Name", m.job "M_Job" 2 FROM emp w, manager m 3 WHERE w.mgr = m.empno 4 AND w.job LIKE 'ANA%'; 7-11 EMPNO ENAME JOB MGR M_Name M_Job ---------- ---------- --------- ---------- ---------- ------- 7788 SCOTT ANALYST 7566 JONES MANAGER 7902 FORD ANALYST 7566 JONES MANAGER 7-11 7-7 Self join manager manager emp emp manager 126

7-12 7-12 SQL> SELECT w.empno "W_Number",w.ename "W_Name",w.job "W_Job",w.sal "W_Salary" 2,m.empno "M_Number", m.ename "M_Name", d.loc "Location" 3 FROM emp w, manager m, dept d 4 WHERE w.mgr = m.empno 5 AND m.deptno = d.deptno 6 AND w.job IN ('CLERK', 'ANALYST') 7 ORDER BY w.job, w.sal; 7-12 W_Number W_Name W_Job W_Salary M_Number M_Name Location ---------- --------- --------- ---------- ---------- ---------- -------- 7788 SCOTT ANALYST 3000 7566 JONES DALLAS 7902 FORD ANALYST 3000 7566 JONES DALLAS 7369 SMITH CLERK 800 7902 FORD DALLAS 7900 JAMES CLERK 950 7698 BLAKE CHICAGO 7876 ADAMS CLERK 1100 7788 SCOTT DALLAS 7934 MILLER CLERK 1300 7782 CLARK NEW YORK 6 7-12 emp w.mgr w.mgr manager deptno dept job CLERK ANALYST job sal = salgrade SQL*PLUS DESC 7-13 7-13 SQL> DESC salgrade 7-13 127

? ---------------------------------- ------- ------ GRADE NUMBER LOSAL NUMBER HISAL NUMBER 7-14 7-14 SQL> SELECT * FROM salgrade; 7-14 GRADE LOSAL HISAL -------- ---------- ---------- 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999 salgrade 3 2000 1401 7-15 3~5 7-15 SQL> SELECT e.empno, e.ename, e.job, e.sal, s.grade 2 FROM emp e, salgrade s 3 WHERE e.sal BETWEEN s.losal AND s.hisal 4 AND s.grade > 2; 7-15 EMPNO ENAME JOB SAL GRADE ---------- ---------- --------- ---------- ------- 7499 ALLEN SALESMAN 1600 3 7844 TURNER SALESMAN 1500 3 7566 JONES MANAGER 2975 4 7698 BLAKE MANAGER 2850 4 7782 CLARK MANAGER 2450 4 7788 SCOTT ANALYST 3000 4 7902 FORD ANALYST 3000 4 128

7839 KING PRESIDENT 5000 5 8 7-15 BETWEEN AND = 7-16 dept 7-16 SQL> SELECT * 2 FROM dept; 7-16 DEPTNO DNAME LOC ---------- -------------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 7-16 40 7-1 deptno emp deptno 40 40 deptno 40 7-17 7-17 SQL> SELECT empno, ename, sal, emp.deptno, dept.deptno, loc 2 FROM emp, dept 3 WHERE emp.deptno(+) = dept.deptno; 7-17 EMPNO ENAME SAL DEPTNO DEPTNO LOC ---------- ---------- ---------- ---------- -------- ----- 7782 CLARK 2450 10 10 NEW YORK 7839 KING 5000 10 10 NEW YORK 7934 MILLER 1300 10 10 NEW YORK 7369 SMITH 800 20 20 DALLAS 7876 ADAMS 1100 20 20 DALLAS 129

7902 FORD 3000 20 20 DALLAS 7788 SCOTT 3000 20 20 DALLAS 7566 JONES 2975 20 20 DALLAS 7499 ALLEN 1600 30 30 CHICAGO 7698 BLAKE 2850 30 30 CHICAGO 7654 MARTIN 1250 30 30 CHICAGO 7900 JAMES 950 30 30 CHICAGO 7844 TURNER 1500 30 30 CHICAGO 7521 WARD 1250 30 30 CHICAGO 40 BOSTON 15 7-17 40 BOSTON + emp.deptno 7-17 7-18 7-18 SQL> SELECT empno, ename, sal, emp.deptno, dept.deptno, loc 2 FROM emp, dept 3 WHERE dept.deptno = emp.deptno(+); SQL*PLUS 7-18 7-18 7-17 Oracle9i ANSI SQL SQL 1999 SQL 1999 7-19 SQL 1999 7-19 SQL> SELECT e.empno, e.ename, e.sal, e.deptno, d.loc 2 FROM emp e 3 CROSS JOIN dept d 4 ORDER BY d.loc; 130

7-19 7-4 7-19 SQL 1999 7-20 USING 7-20 SQL> SELECT e.empno, e.ename, e.sal, deptno, d.loc 2 FROM emp e 3 JOIN dept d 4 USING (deptno) 5 ORDER BY d.loc; 7-20 EMPNO ENAME SAL DEPTNO LOC ---------- ---------- ---------- ---------- -------- 7499 ALLEN 1600 30 CHICAGO 7521 WARD 1250 30 CHICAGO 7654 MARTIN 1250 30 CHICAGO 7900 JAMES 950 30 CHICAGO 7844 TURNER 1500 30 CHICAGO 7698 BLAKE 2850 30 CHICAGO 7369 SMITH 800 20 DALLAS 7902 FORD 3000 20 DALLAS 7876 ADAMS 1100 20 DALLAS 7566 JONES 2975 20 DALLAS 7788 SCOTT 3000 20 DALLAS 7782 CLARK 2450 10 NEW YORK 7839 KING 5000 10 NEW YORK 7934 MILLER 1300 10 NEW YORK 14 7-20 7-1 USING NATURAL JOIN USING 7-20 deptno USING 131

7-21 ON 7-21 SQL> SELECT e.empno, e.ename, e.sal, e.deptno, d.loc 2 FROM emp e 3 JOIN dept d 4 ON (e.deptno = d.deptno) 5 ORDER BY d.loc; 7-21 EMPNO ENAME SAL DEPTNO LOC ---------- ---------- ---------- ---------- --- 7499 ALLEN 1600 30 CHICAGO 7521 WARD 1250 30 CHICAGO 7654 MARTIN 1250 30 CHICAGO 7900 JAMES 950 30 CHICAGO 7844 TURNER 1500 30 CHICAGO 7698 BLAKE 2850 30 CHICAGO 7369 SMITH 800 20 DALLAS 7902 FORD 3000 20 DALLAS 7876 ADAMS 1100 20 DALLAS 7566 JONES 2975 20 DALLAS 7788 SCOTT 3000 20 DALLAS 7782 CLARK 2450 10 NEW YORK 7839 KING 5000 10 NEW YORK 7934 MILLER 1300 10 NEW YORK 14 7-21 7-1 ON 7-21 e.deptno d.deptno ON 7-22 ON 7-22 SQL> SELECT w.empno "W_Number",w.ename "W_Name",w.job "W_Job",w.sal "W_Salary" 132

2,m.empno "M_Number", m.ename "M_Name", d.loc "Location" 3 FROM emp w 4 JOIN manager m 5 ON w.mgr = m.empno 6 JOIN dept d 7 ON m.deptno = d.deptno 8 WHERE w.job IN ('CLERK', 'ANALYST') 9 ORDER BY w.job, w.sal; 7-22 W_Number W_Name W_Job W_Salary M_Number M_Name Location ------- --------- --------- ---------- ---------- ---------- -------- 7788 SCOTT ANALYST 3000 7566 JONES DALLAS 7902 FORD ANALYST 3000 7566 JONES DALLAS 7369 SMITH CLERK 800 7902 FORD DALLAS 7900 JAMES CLERK 950 7698 BLAKE CHICAGO 7876 ADAMS CLERK 1100 7788 SCOTT DALLAS 7934 MILLER CLERK 1300 7782 CLARK NEW YORK 6 7-22 SQL 1999 7-12 7-22 WHERE AND 7-23 7-23 SQL>SELECT w.empno "W_Number", w.ename "W_Name",w.job "W_Job" w.sal "W_Salary" 2,m.empno "M_Number", m.ename "M_Name", d.loc "Location" 3 FROM emp w 4 JOIN manager m 5 ON w.mgr = m.empno 6 JOIN dept d 7 ON m.deptno = d.deptno 8 AND w.job IN ('CLERK', 'ANALYST') 9 ORDER BY w.job, w.sal; 7-23 W_Number W_Name W_Job W_Salary M_Number M_Name Location ---------- --------- -------- ---------- ---------- ---------- -------- 7788 SCOTT ANALYST 3000 7566 JONES DALLAS 7902 FORD ANALYST 3000 7566 JONES DALLAS 7369 SMITH CLERK 800 7902 FORD DALLAS 133

7900 JAMES CLERK 950 7698 BLAKE CHICAGO 7876 ADAMS CLERK 1100 7788 SCOTT DALLAS 7934 MILLER CLERK 1300 7782 CLARK NEW YORK 6 7-23 7-22 7-22 WHERE AND 7-24 7-24 SQL> SELECT empno, ename, sal, emp.deptno, dept.deptno, loc 2 FROM dept 3 LEFT OUTER JOIN emp 4 ON (emp.deptno = dept.deptno); 7-24 EMPNO ENAME SAL DEPTNO DEPTNO LOC ---------- ---------- ---------- ---------- ---------- -------- 7369 SMITH 800 20 20 DALLAS 7499 ALLEN 1600 30 30 CHICAGO 7521 WARD 1250 30 30 CHICAGO 7566 JONES 2975 20 20 DALLAS 7654 MARTIN 1250 30 30 CHICAGO 7698 BLAKE 2850 30 30 CHICAGO 7782 CLARK 2450 10 10 NEW YORK 7788 SCOTT 3000 20 20 DALLAS 7839 KING 5000 10 10 NEW YORK 7844 TURNER 1500 30 30 CHICAGO 7876 ADAMS 1100 20 20 DALLAS 7900 JAMES 950 30 30 CHICAGO 7902 FORD 3000 20 20 DALLAS 7934 MILLER 1300 10 10 NEW YORK 40 BOSTON 15 7-24 7-17 SQL 1999 134

7-25 7-25 SQL> SELECT e.empno, e.ename, e.sal, e.deptno, d.deptno, d.loc 2 FROM emp e 3 RIGHT OUTER JOIN dept d 4 ON (e.deptno = d.deptno); 7-25 EMPNO ENAME SAL DEPTNO DEPTNO LOC ---------- ---------- ---------- ---------------- ------ 7369 SMITH 800 20 20 DALLAS 7499 ALLEN 1600 30 30 CHICAGO 7521 WARD 1250 30 30 CHICAGO 7566 JONES 2975 20 20 DALLAS 7654 MARTIN 1250 30 30 CHICAGO 7698 BLAKE 2850 30 30 CHICAGO 7782 CLARK 2450 10 10 NEW YORK 7788 SCOTT 3000 20 20 DALLAS 7839 KING 5000 10 10 NEW YORK 7844 TURNER 1500 30 30 CHICAGO 7876 ADAMS 1100 20 20 DALLAS 7900 JAMES 950 30 30 CHICAGO 7902 FORD 3000 20 20 DALLAS 7934 MILLER 1300 10 10 NEW YORK 40 BOSTON 15 7-25 7-17 7-25 7-26 7-26 SQL> SELECT e.empno, e.ename, e.sal, e.deptno, d.deptno, d.loc 2 FROM emp e, dept d 3 WHERE d.deptno = e.deptno(+); 135

7-10 manager 30 NULL 7-27 SQL 7-27 7-28 SQL DML 7-27 SQL> update manager 2 set deptno = NULL 3 WHERE deptno = 30; 7-27 6 7-28 SQL> commit; 7-28 7-29 manager dept 7-29 SQL> SELECT empno, ename, sal, manager.deptno, dept.deptno, loc 2 FROM manager 3 LEFT OUTER JOIN dept 4 ON (manager.deptno = dept.deptno); 7-29 EMPNO ENAME SAL DEPTNO DEPTNO LOC ---------- ---------- ---------- ------ ---------- -------- 7934 MILLER 1300 10 10 NEW YORK 7839 KING 5000 10 10 NEW YORK 7782 CLARK 2450 10 10 NEW YORK 7902 FORD 3000 20 20 DALLAS 7876 ADAMS 1100 20 20 DALLAS 7788 SCOTT 3000 20 20 DALLAS 7566 JONES 2975 20 20 DALLAS 7369 SMITH 800 20 20 DALLAS 7900 JAMES 950 136

7844 TURNER 1500 7698 BLAKE 2850 7654 MARTIN 1250 7521 WARD 1250 7499 ALLEN 1600 14 7-29 manager manager dept deptno 7-30 7-30 SQL> SELECT empno, ename, sal, manager.deptno, dept.deptno, loc 2 FROM manager, dept 3 WHERE dept.deptno(+) = manager.deptno; 7-31 manager dept 7-31 SQL> SELECT empno, ename, sal, manager.deptno, dept.deptno, loc 2 FROM manager 3 RIGHT OUTER JOIN dept 4 ON (manager.deptno = dept.deptno); 7-31 EMPNO ENAME SAL DEPTNO DEPTNO LOC ---------- ---------- ------- ---------- ------- -------- 7369 SMITH 800 20 20 DALLAS 7566 JONES 2975 20 20 DALLAS 7782 CLARK 2450 10 10 NEW YORK 7788 SCOTT 3000 20 20 DALLAS 7839 KING 5000 10 10 NEW YORK 7876 ADAMS 1100 20 20 DALLAS 7902 FORD 3000 20 20 DALLAS 7934 MILLER 1300 10 10 NEW YORK 30 CHICAGO 40 BOSTON 10 7-31 manager dept dept manager 137

deptno 7-32 7-32 SQL> SELECT empno, ename, sal, manager.deptno, dept.deptno, loc 2 FROM manager, dept 3 WHERE dept.deptno = manager.deptno(+); 7-33 manager dept 7-33 SQL> SELECT empno, ename, sal, manager.deptno, dept.deptno, loc 2 FROM manager 3 FULL OUTER JOIN dept 4 ON (manager.deptno = dept.deptno); 7-33 EMPNO ENAME SAL DEPTNO DEPTNO LOC ---------- ---------- ------- ---------- ------- -------- 7934 MILLER 1300 10 10 NEW YORK 7839 KING 5000 10 10 NEW YORK 7782 CLARK 2450 10 10 NEW YORK 7902 FORD 3000 20 20 DALLAS 7876 ADAMS 1100 20 20 DALLAS 7788 SCOTT 3000 20 20 DALLAS 7566 JONES 2975 20 20 DALLAS 7369 SMITH 800 20 20 DALLAS 7900 JAMES 950 7844 TURNER 1500 7698 BLAKE 2850 7654 MARTIN 1250 7521 WARD 1250 7499 ALLEN 1600 30 CHICAGO 40 BOSTON 16 7-33 manager dept SQL:1999 Oracle SQL: 1999 SQL Oracle 138

SQL:1999 Normalization Primary Key Entity Integrity Repeating Groups 1NF Foreign Key Referential Integrity 2NF 2NF Equijoin Self join Non- equijoin Outer join SQL 1999 SQL 1999 USING SQL 1999 ON SQL 1999 SQL 1999 SQL 1999 139

SMITH SMITH job SMITH job SMITH 8-1 8-2 8-1 SQL> SELECT job 2 FROM emp 3 WHERE ename = 'SMITH'; 8-1 JOB --------- CLERK 8-1 SMITH job CLERK CLERK 8-2 WHERE 8-2 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE job = 'CLERK'; 8-2 EMPNO ENAME SAL JOB ---------- ---------- ---------- --------- 7369 SMITH 800 CLERK 7876 ADAMS 1100 CLERK 7900 JAMES 950 CLERK 7934 MILLER 1300 CLERK 8-2

8-1 8-2 8-1 8-2 Main query Subquery 8-3 8-3 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE job = 4 (SELECT job 5 FROM emp 6 WHERE ename = 'SMITH'); 8-3 EMPNO ENAME SAL JOB ---------- ---------- ---------- --------- 7369 SMITH 800 CLERK 7876 ADAMS 1100 CLERK 7900 JAMES 950 CLERK 7934 MILLER 1300 CLERK 8-3 8-2 8-3 Subquery Inner query Main query Outer query Oracle Oracle SMITH job CLERK Main query CLERK WHERE > >= < <= = <> = WHERE HAVING FROM 141

ORDER BY 8-3 Primary Key job SMITH ADAMS 8-4 8-4 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE job = 4 (SELECT job 5 FROM emp 6 WHERE ename = 'SMITH') 7 AND sal <= 8 (SELECT sal 9 FROM emp 10 WHERE ename = 'ADAMS'); 8-4 EMPNO ENAME SAL JOB ---------- ---------- ---------- --------- 7369 SMITH 800 CLERK 7876 ADAMS 1100 CLERK 7900 JAMES 950 CLERK 8-4 ADAMS 1100 CLERK ADAMS 8-5 SQL 8-5 142

SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE sal < 4 (SELECT AVG(sal) 5 FROM emp); 8-5 EMPNO ENAME SAL JOB ---------- ---------- ---------- --------- 7369 SMITH 800 CLERK 7499 ALLEN 1600 SALESMAN 7521 WARD 1250 SALESMAN 7654 MARTIN 1250 SALESMAN 7844 TURNER 1500 SALESMAN 7876 ADAMS 1100 CLERK 7900 JAMES 950 CLERK 7934 MILLER 1300 CLERK 8 8-5 GROUP BY GROUP BY Oracle 8-6 8-6 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE sal < 4 (SELECT AVG(sal) 5 FROM emp 6 GROUP BY job); 8-6 (SELECT AVG(sal) * ERROR 4 : ORA-01427: WHERE 8-7 8-7 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE ename = 143

4 (SELECT ename 5 FROM emp 6 WHERE ename = 'ADAM'); 8-7 8-7 ADAMS ADAM emp ename ADAM WHERE WHERE ename = NULL NULL WHERE WHERE WHERE HAVING job job job 8-8 SELECT job AVG sal MIN sal MAX sal 8-8 SQL> SELECT job, MIN(sal), AVG(sal), MAX(sal) 2 FROM emp 3 WHERE job NOT LIKE 'PRESID%' 4 GROUP BY job 5 HAVING AVG(sal) > ( 6 SELECT MIN(AVG(sal)) 7 FROM emp 8 GROUP BY job); 8-8 JOB MIN(SAL) AVG(SAL) MAX(SAL) --------- ---------- -------- ---------- ANALYST 3000 3000 3000 MANAGER 2450 2758.33333 2975 SALESMAN 1250 1400 1600 CLERK 144

CLERK job 8-9 8-9 SQL> SELECT e.empno, e.ename, e.sal, e.job, a.avesal 2 FROM emp e, (SELECT job, AVG(sal) avesal 3 FROM emp 4 GROUP BY job) a 5 WHERE e.job = a.job 6 AND e.sal > a.avesal 7 AND e.job!= 'CLERK'; 8-9 EMPNO ENAME SAL JOB AVESAL ---------- ---------- ---------- --------- ---------- 7566 JONES 2975 MANAGER 2758.33333 7698 BLAKE 2850 MANAGER 2758.33333 7499 ALLEN 1600 SALESMAN 1400 7844 TURNER 1500 SALESMAN 1400 8-9 8-8 IN ANY ALL 145

CLERK PRESIDENT job 8-10 8-10 SQL> SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal IN (SELECT MAX(sal) 4 FROM emp 5 GROUP BY job) 6 AND job <> 'CLERK' 7 AND job NOT LIKE 'PRES%'; 8-10 EMPNO ENAME JOB SAL ---------- ---------- --------- ---------- 7499 ALLEN SALESMAN 1600 7566 JONES MANAGER 2975 7788 SCOTT ANALYST 3000 7902 FORD ANALYST 3000 IN IN 8-10 8-11 8-12 8-11 SQL> SELECT MAX(sal) 2 FROM emp 3 GROUP BY job; 8-11 MAX(SAL) ---------- 3000 1300 2975 5000 1600 146

8-12 SQL> SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal IN (3000, 1300, 2975, 5000, 1600) 4 AND job <> 'CLERK' 5 AND job NOT LIKE 'PRES%'; 8-12 EMPNO ENAME JOB SAL ---------- ---------- --------- ---------- 7499 ALLEN SALESMAN 1600 7566 JONES MANAGER 2975 7788 SCOTT ANALYST 3000 7902 FORD ANALYST 3000 8-10 8-13 SQL 8-13 8-10 8-13 SQL> SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal IN (SELECT MAX(sal) 4 FROM emp 5 WHERE job <> 'CLERK' 6 AND job NOT LIKE 'PRES%' 7 GROUP BY job); 8-13 EMPNO ENAME JOB SAL ---------- ---------- --------- ---------- 7499 ALLEN SALESMAN 1600 7566 JONES MANAGER 2975 7788 SCOTT ANALYST 3000 7902 FORD ANALYST 3000 JOB 8-14 147

8-14 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE sal < ALL 4 (SELECT AVG(sal) 5 FROM emp 6 GROUP BY job); 8-14 EMPNO ENAME SAL JOB ---------- ---------- ---------- ----- 7369 SMITH 800 CLERK 7900 JAMES 950 CLERK ALL < ALL > ALL = ALL 8-15 8-15 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE sal = ALL 4 (SELECT AVG(sal) 5 FROM emp 6 GROUP BY job); 8-15 Oracle 8-16 8-16 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE sal > ANY 4 (SELECT AVG(sal) 5 FROM emp 6 GROUP BY job); 148

8-16 EMPNO ENAME SAL JOB ---------- ---------- ---------- --------- 7499 ALLEN 1600 SALESMAN 7521 WARD 1250 SALESMAN 7566 JONES 2975 MANAGER 7654 MARTIN 1250 SALESMAN 7698 BLAKE 2850 MANAGER 7782 CLARK 2450 MANAGER 7788 SCOTT 3000 ANALYST 7839 KING 5000 PRESIDENT 7844 TURNER 1500 SALESMAN 7876 ADAMS 1100 CLERK 7902 FORD 3000 ANALYST 7934 MILLER 1300 CLERK 12 ANY < ANY > ANY = ANY 8-17 8-17 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE sal = ANY 4 (SELECT AVG(sal) 5 FROM emp 6 GROUP BY job); 8-17 EMPNO ENAME SAL JOB ---------- ---------- ---------- --------- 7788 SCOTT 3000 ANALYST 7902 FORD 3000 ANALYST 7839 KING 5000 PRESIDENT = ANY IN 8-18 8-18 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE sal IN 4 (SELECT AVG(sal) 149

5 FROM emp 6 GROUP BY job); 8-18 EMPNO ENAME SAL JOB ---------- ---------- ---------- --------- 7788 SCOTT 3000 ANALYST 7902 FORD 3000 ANALYST 7839 KING 5000 PRESIDENT 8-19 8-19 SQL> SELECT e.empno, e.ename, e.sal, e.job 2 FROM emp e 3 WHERE e.mgr IN 4 (SELECT w.mgr 5 FROM emp w 6 WHERE w.mgr IS NULL); 8-19 8-19 NULL NULL 8-19 8-19 8-20 8-20 SQL> SELECT e.empno, e.ename, e.sal, e.job 2 FROM emp e 3 WHERE e.mgr NOT IN 4 (SELECT w.mgr 5 FROM emp w 6 WHERE w.mgr IS NULL); 8-20 150

8-20 NOT IN < >ALL NULL NULL 8-20 8-20 WHERE WHERE w.mgr IS NOT NULL 8-21 8-21 SQL> SELECT e.empno, e.ename, e.sal, e.job 2 FROM emp e 3 WHERE e.mgr NOT IN 4 (SELECT w.mgr 5 FROM emp w 6 WHERE w.mgr IS NOT NULL); 8-21 8-21 8-21 NULL NULL 8-21 WHERE WHERE e.mgr IN 8-22 8-22 SQL> SELECT e.empno, e.ename, e.sal, e.job 2 FROM emp e 3 WHERE e.mgr IN 4 (SELECT w.mgr 5 FROM emp w 6 WHERE w.mgr IS NOT NULL); 8-22 EMPNO ENAME SAL JOB --------- ---------- ---------- ------- 7788 SCOTT 3000 ANALYST 7902 FORD 3000 ANALYST 7499 ALLEN 1600 SALESMAN 7521 WARD 1250 SALESMAN 7900 JAMES 950 CLERK 7844 TURNER 1500 SALESMAN 151

7654 MARTIN 1250 SALESMAN 7934 MILLER 1300 CLERK 7876 ADAMS 1100 CLERK 7566 JONES 2975 MANAGER 7782 CLARK 2450 MANAGER 7698 BLAKE 2850 MANAGER 7369 SMITH 800 CLERK 13 SQL NULL SQL NULL manager 8-23 8-24 8-25 SQL 8-23 SQL> UPDATE manager 2 SET sal = 1300 3 WHERE empno = 7521; 8-23 1 8-24 SQL> UPDATE manager 2 SET sal = 1600 3 WHERE empno = 7782; 8-24 1 8-25 152

SQL> commit; 8-25 8-23 8-25 job 8-26 8-26 SQL> SELECT empno, ename, sal, job 2 FROM manager 3 WHERE (sal, job) IN 4 (SELECT MAX(sal), job 5 FROM manager 6 GROUP BY job); 8-26 EMPNO ENAME SAL JOB ---------- ---------- ---------- ----- 7934 MILLER 1300 CLERK 7499 ALLEN 1600 SALESMAN 7566 JONES 2975 MANAGER 7788 SCOTT 3000 ANALYST 7902 FORD 3000 ANALYST 7839 KING 5000 PRESIDENT 6 8-26 job job job job job 8-27 8-27 153

SQL> SELECT empno, ename, sal, job 2 FROM manager 3 WHERE sal IN (SELECT MAX(sal) 4 FROM manager 5 GROUP BY job) 6 AND job IN (SELECT DISTINCT job 7 FROM manager); 8-27 EMPNO ENAME SAL JOB ---------- ---------- ------ --------- 7788 SCOTT 3000 ANALYST 7902 FORD 3000 ANALYST 7934 MILLER 1300 CLERK 7782 CLARK 1600 MANAGER 7566 JONES 2975 MANAGER 7839 KING 5000 PRESIDENT 7521 WARD 1300 SALESMAN 7499 ALLEN 1600 SALESMAN 8 8-27 1600 1600 1300 1300 8-27 8-26 8-27 job job job > >= < <= = <> IN ALL ANY WHERE HAVING FROM GROUP BY ORDER BY NULL 154

Main query Subquery Main query Subquery WHERE HAVING FROM NULL GROUP BY ORDER BY 155

SQL*PLUS SQL*PLUS SQL*PLUS SET SQL*PLUS SQL*PLUS SET SET SHOW SQL*PLUS SHOW SHOW ALL SET SQL*PLUS ECHO 9-1 SQL*PLUS 9-1 SQL> show echo 9-1 echo OFF SQL*PLUS 9-2 9-2 SQL> SELECT * FROM dept; 9-2 DEPTNO DNAME LOC ---------- -------------- --------

10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 9-2 9-3 d:\ Oracle\ming\ echo_sample 9-3 SQL> save "d:\ Oracle\ming\echo_sample" 9-3 d:\ Oracle\ming\echo_sample.sql d:\ Oracle\ming 9-3 d:\ Oracle\ming\echo_sample 9-4 SQL*PLUS 9-4 SQL> @d:\ Oracle\ming\echo_sample 9-4 DEPTNO DNAME LOC ---------- -------------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 9-4 d:\ Oracle\ming\echo_sample Oracle SET SQL*PLUS ECHO ON 9-5 9-5 SQL> set echo on SHOW SQL*PLUS ECHO 9-6 9-6 SQL> SHOW ECHO 9-6 157

echo ON 9-7 SQL*PLUS d:\ Oracle\ming\ echo_sample 9-7 SQL> @d:\ Oracle\ming\echo_sample 9-7 SQL> SELECT * FROM dept 2 / DEPTNO DNAME LOC ---------- -------------- ------ 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 9-7 d:\ Oracle\ming\echo_sample Oracle SQL*PLUS ECHO SET SHOW ALL 9-8 9-8 SQL> show all 9-8 appinfo OFF "SQL*Plus" arraysize 15 autocommit OFF autoprint OFF autorecovery OFF autotrace OFF blockterminator "." (hex 2e) btitle OFF and SELECT SQL*PLUS ECHO FEEDBACK 158

SHOW FEEDBACK 9-9 9-9 SQL> SHOW FEEDBACK 9-9 6 FEEDBACK ON SET FEEDBACK SET FEED[BACK]{6 n OFF ON} n n 6 Oracle 9-9 n 6 6 6 9-10 9-11 SQL 9-10 SQL> SELECT * 2 FROM dept; 9-10 DEPTNO DNAME LOC ---------- -------------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 9-10 4 6 OREACLE 9-11 SQL> SELECT ename, sal 2 FROM emp 3 WHERE sal > 1500; 9-11 ENAME SAL ---------- ---------- 159

ALLEN 1600 JONES 2975 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 FORD 3000 7 9-11 7 6 OREACLE 7 ECHO FEEDBACK SET HEA[DING] { ON OFF } SET ARRAY[SIZE] { 20 n } SQL*PLUS 5000 SET LINE[SIZE] { 80 n } SET PAGE[SIZE] { 24 n } SET LONG { 80 n } LONG CLOB NCLOB 2G SQL*PLUS COLUMN COLUMN COL[UMN] [{ } [ ] ] CLE[AR] FOR[MAT] HEA[DING] JUS[TIFY] { } NUL[L] PRI[NT] NOPRI[NT] COLUMN 9-12 SQL> select * from dept; 9-12 DEPTNO DNAME LOC 160

---------- -------------- ---------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 9-12 DEPTNO 9-13 SQL*PLUS 6 9-12 9-14 9-13 SQL> col deptno for 999999 9-14 SQL> select * from dept; 9-14 DEPTNO DNAME LOC ------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 9-14 DEPTNO 6 DEPTNO 9-13 FOR[MAT] 999999 999999 9 0 6 9 6 9-14 LOC 9-15 SQL*PLUS 9 9-12 9-16 9-15 SQL> col loc for a9 9-16 SQL> select * from dept; 9-16 DEPTNO DNAME LOC ------- -------------- --------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 161

30 SALES CHICAGO 40 OPERATIONS BOSTON 9-16 LOC 9 LOC 9-16 FOR[MAT] A9 A9 A9 9 9-16 LOC 9-17 SQL*PLUS LOC Location 9-16 9-18 9-17 SQL> col loc HEADING 'Location' FOR A9 9-18 SQL> select * from dept; 9-18 DEPTNO DNAME Location ------- -------------- --------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 9-18 LOC Location HEA[DING] HEA[DING] 9-19 9-20 LOC dept 9-19 SQL> col loc HEADING ' ' for a9 9-20 SQL> select * from dept; 9-20 DEPTNO DNAME ---------- -------------- --------- 10 ACCOUNTING NEW YORK 162

20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 9-19 9-20 DEPTNO LOC LOC 9-21 9-22 SQL*PLUS 9-21 SQL> COL loc 9-21 COLUMN loc ON HEADING 'Location' FORMAT A9 9-22 SQL> col deptno 9-22 COLUMN deptno ON FORMAT 999999 9-21 9-22 SQL*PLUS CLEAR 9-23 9-24 CLEAR 9-23 SQL> col loc clear 9-12 9-24 9-24 SQL> select * from dept; 9-24 DEPTNO DNAME LOC ------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 163

30 SALES CHICAGO 40 OPERATIONS BOSTON 9-24 9-12 COLUMN CLEAR SQL*PLUS COL loc An 9 9-25 9-25 SQL> SELECT ename, job, sal 2 FROM emp 3 WHERE sal >= 1500; 9-25 ENAME JOB SAL ---------- --------- ---------- ALLEN SALESMAN 1600 JONES MANAGER 2975 BLAKE MANAGER 2850 CLARK MANAGER 2450 SCOTT ANALYST 3000 KING PRESIDENT 5000 TURNER SALESMAN 1500 FORD ANALYST 3000 8 9-25 SAL 9-26 SQL*PLUS SAL 9-26 SQL> col sal for $99,999.99 $,. 9 0 9-23 9-27 9-27 SQL> SELECT ename, job, sal 2 FROM emp 3 WHERE sal >= 1500; 9-27 ENAME JOB SAL 164

---------- --------- ----------- ALLEN SALESMAN $1,600.00 JONES MANAGER $2,975.00 BLAKE MANAGER $2,850.00 CLARK MANAGER $2,450.00 SCOTT ANALYST $3,000.00 KING PRESIDENT $5,000.00 TURNER SALESMAN $1,500.00 FORD ANALYST $3,000.00 8 9-27 9-25 9-28 9-29 COLUMN 0 9-28 SQL> col sal for $009,999.99 9-29 SQL> SELECT ename, job, sal 2 FROM emp 3 WHERE sal >= 1500; 9-29 ENAME JOB SAL ---------- --------- ------------ ALLEN SALESMAN $001,600.00 JONES MANAGER $002,975.00 BLAKE MANAGER $002,850.00 CLARK MANAGER $002,450.00 SCOTT ANALYST $003,000.00 KING PRESIDENT $005,000.00 TURNER SALESMAN $001,500.00 FORD ANALYST $003,000.00 8 9-29 COLUMN 0 0 COLUMN L 9-30 9-31 9-30 SQL> col sal for L99,999.99 165

9-31 SQL> SELECT ename, job, sal 2 FROM emp 3 WHERE sal >= 1500; 9-31 ENAME JOB SAL ---------- --------- ------------------ ALLEN SALESMAN RMB1,600.00 JONES MANAGER RMB2,975.00 BLAKE MANAGER RMB2,850.00 CLARK MANAGER RMB2,450.00 SCOTT ANALYST RMB3,000.00 KING PRESIDENT RMB5,000.00 TURNER SALESMAN RMB1,500.00 FORD ANALYST RMB3,000.00 8 9-31 COLUMN L RMB COLUMN SQL*PLUS TTITLE Top Title BTITLE Bottom Title BREAK TTI[TLE] [ OFF ON] BTI[TLE] [ OFF ON] BREAK ON [ ] [SKIP n] n CLEAR BREAK BREAK BREAK ORDER BY SQL*PLUS 9-32 9-32 REM *** This is an employees' salary report for senior management team *** REM *** Strictly Confidential *** SET PAGESIZE 25 SET LINESIZE 80 SET FEEDBACK OFF 166

TTITLE '====== Sun_Moon IT Company ====== ===== Employee Salary Report =====' BTITLE 'Strictly Confidential!!!' BREAK ON deptno SKIP 2 COLUMN deptno HEADING 'Department Number' JUSTIFY CENTER FORMAT 99999999999 COLUMN job HEADING 'Job Category' FORMAT A15 COLUMN AVG(sal) HEADING 'Average Salary' FORMAT L99,999.00 COLUMN COUNT(sal) HEADING 'Employee Number' JUSTIFY CENTER FORMAT 999 COLUMN SUM(sal) HEADING 'Summary Salary' FORMAT L99,999.00 SELECT deptno, job, AVG(sal), COUNT(sal), SUM(sal) FROM emp GROUP BY deptno, job / SET FEEDBACK ON 9-32 1 REM Oracle 2 SET PAGESIZE 25 25 3 SET LINESIZE 80 80 4 SET FEEDBACK OFF 5 TTITLE ====== Sun_Moon IT Company ===== ====== Employee Salary Report ===== 6 BTITLE Strictly Confidential!!! 7 BREAK ON deptno SKIP 2 deptno 8 COLUMN deptno deptno Department 11 Number 9 COLUMN job job Job 15 Category 10 COLUMN AVG(sal) AVG sal Average 7 Salary 11 COLUMN COUNT(sal) COUNT sal Employee 3 Number 12 COLUMN SUM(sal) SUM sal Summary 7 Salary 13 SET FEEDBACK ON 167

9-32 report.sql d:\ Oracle\ming 9-33 9-33 SQL> @d:\ Oracle\ming\report 9-33 9 09 1 ======= Sun_Moon IT Company ====== ===== Employee Salary Report ===== Department Job Average Employee Summary Number Category Salary Number Salary ------------ ------------ ---------------- -------- -------- ------ 10 CLERK RMB1,300.00 1 RMB1,300.00 MANAGER RMB2,450.00 1 RMB2,450.00 PRESIDENT RMB5,000.00 1 RMB5,000.00 20 ANALYST RMB3,000.00 2 RMB6,000.00 CLERK RMB950.00 2 RMB1,900.00 MANAGER RMB2,975.00 1 RMB2,975.00 30 CLERK RMB950.00 1 RMB950.00 MANAGER RMB2,850.00 1 RMB2,850.00 SALESMAN RMB1,400.00 4 RMB5,600.00 Strictly Confidential!!! 9-33 SQL SQL SQL SQL SQL*PLUS SQL / SQL*PLUS SQL*PLUS 168

SQL*PLUS SQL*PLUS Oracle SYS Oracle DBA USER ALL DBA 9-1 DBA_* ALL_* USER_* 9-1 USER_* ALL_* 169

DBA_* * TABLES INDEXES OBJECTS USER ALL DBA ALL DBA USER OWNER *_OBJECTS DBA DBA DBA_* 9-34 SQL*PLUS SYSTEM MANAGER 9-34 SQL> connect system/manager 9-34 9-35 9-37 user_objects all_objects dba_objects 9-35 SQL> desc user_objects 9-35? --------------------------------- -------- ---------------------------- OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(18) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) 9-36 SQL> desc all_objects 9-36? ----------------------- -------- ---------------------------- 170

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY 9-37 NOT NULL VARCHAR2(30) NOT NULL VARCHAR2(30) VARCHAR2(30) NOT NULL NUMBER NUMBER VARCHAR2(18) NOT NULL DATE NOT NULL DATE VARCHAR2(19) VARCHAR2(7) VARCHAR2(1) VARCHAR2(1) VARCHAR2(1) SQL> desc dba_objects 9-37? -------------------------------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(18) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) 9-35 9-36 9-37 user_objects OWNER all_objects dba_objects OWNER user_objects OWNER user_* all_* dba_* *_tables *_indexes SYSTEM SCOTT 9-38 171

9-38 SQL> SELECT owner, object_name, object_id, created, status 2 FROM all_objects 3 WHERE owner = 'SCOTT'; 9-38 OWNER OBJECT_NAME OBJECT_ID ----------------------------- ------------------------------ ----- CREATED STATUS ---------- ------- SCOTT BONUS 32122 04-9 -01 VALID SCOTT DEPT 32118 04-9 -01 VALID SCOTT EMP 32120 04-9 -01 VALID OWNER OBJECT_NAME OBJECT_ID ------------------------------ ----------------------------- ----- CREATED STATUS ---------- ------- SCOTT EMP_NULL 32130 14-7 -02 VALID SCOTT MANAGER 32137 13-8 -02 VALID SCOTT PK_DEPT 32119 04-9 -01 VALID OWNER OBJECT_NAME OBJECT_ID ------------------------------ ----------------------------- ---- CREATED STATUS ---------- ------- SCOTT PK_EMP 32121 04-9 -01 VALID SCOTT SALGRADE 32123 04-9 -01 VALID 8 172

9-38 SQL*PLUS 9-39 3 COLUMN owner object_name object_type 9-39 SQL> col owner for a8 SQL> col object_name for a12 SQL> col object_type for a10 9-39 9-40 SCOTT 9-40 SQL> SELECT owner, object_name, object_id, created, status, object_type 2 FROM all_objects 3 WHERE owner = 'SCOTT'; 9-40 OWNER OBJECT_NAME OBJECT_ID CREATED STATUS OBJECT_TYP -------- ------------ ---------- ---------- ------- ---------- SCOTT BONUS 32122 04-9 -01 VALID TABLE SCOTT DEPT 32118 04-9 -01 VALID TABLE SCOTT EMP 32120 04-9 -01 VALID TABLE SCOTT EMP_NULL 32130 14-7 -02 VALID TABLE SCOTT MANAGER 32137 13-8 -02 VALID TABLE SCOTT PK_DEPT 32119 04-9 -01 VALID INDEX SCOTT PK_EMP 32121 04-9 -01 VALID INDEX SCOTT SALGRADE 32123 04-9 -01 VALID TABLE 8 9-40 Oracle user_tables 9-41 SCOTT 173

9-41 SQL> SELECT table_name 2 FROM user_tables; 9-41 TABLE_NAME ------------ BONUS DEPT EMP EMP_NULL MANAGER SALGRADE 6 all_tables 9-42 9-42 SQL> SELECT table_name, owner 2 FROM all_tables 3 WHERE owner NOT LIKE '%SYS'; 9-42 TABLE_NAME OWNER --------------------------- ------ DEF$_TEMP$LOB SYSTEM HELP SYSTEM DEPT SCOTT EMP SCOTT BONUS SCOTT SALGRADE SCOTT MANAGER SCOTT EMP_NULL SCOTT 8 user_catalog 9-43 9-43 174

SQL> desc user_catalog 9-43? ----------------------------------------- -------- ------------ TABLE_NAME NOT NULL VARCHAR2(30) TABLE_TYPE VARCHAR2(11) 9-43 user_catalog user_tables user_catalog 9-44 9-44 SQL> select * from user_catalog; 9-44 TABLE_NAME TABLE_TYPE ------------------------------ ---------- BONUS TABLE DEPT TABLE EMP TABLE EMP_NULL TABLE MANAGER TABLE PLAN_TABLE TABLE SALGRADE TABLE 7 user_catalog cat user_catalog 9-45 9-45 SQL> select * from cat; 9-45 TABLE_NAME TABLE_TYPE ------------------------------ ---------- BONUS TABLE DEPT TABLE EMP TABLE EMP_NULL TABLE MANAGER TABLE PLAN_TABLE TABLE 175

SALGRADE 7 TABLE Oracle Oracle v$database v$ Oracle Oracle DBA v$database SYS SYSTEM Oracle 9-46 9-46 SQL> connect system/manager 9-47 9-47 SQL> SELECT name, created, log_mode 2 FROM v$database; 9-47 NAME CREATED LOG_MODE --------- ---------- ------------ Oracle9i 11-7 -02 NOARCHIVELOG 9-47 Oracle9i 2002 7 11 Oracle Oracle 9-48 v$instance 9-48 SQL> SELECT instance_name, host_name, version, archiver 2 FROM v$instance; 9-48 INSTANCE_NAME HOST_NAME VERSION ARCHIVE --------------- -------------------- ----------------- ------- Oracle9i CS-ZNAE5WTCSLHO 9.0.1.3.1 STOPPED 9-48 Oracle9i CS-ZNAE5WTCSLHO 9.0.1.3.1 176

9-49 9-49 SQL> SELECT username, created 2 FROM dba_users; 9-49 USERNAME CREATED ------------------------------ ---------- SYS 04-9 01 SYSTEM 04-9 01 DBSNMP 04-9 01 AURORA$JIS$UTILITY$ 04-9 01 AURORA$ORB$UNAUTHENTICATED 04-9 01 SCOTT 04-9 01 9-49 SET SHOW SQL*PLUS SQL*PLUS SET SQL*PLUS SHOW SQL*PLUS SQL*PLUS COLUMN Oracle Oracle Oracle SYS Oracle SQL*PLUS SQL*PLUS 177

SQL*PLUS SET SQL*PLUS SHOW ECHO FEEDBACK COLUMN COLUMN 178

9 dept emp 7 3 ORDER SUPPLIER PRODUCT Oracle CREATE TABLE DDL - Data Definition Language CREATE TABLE [.] [ DEFAULT ] [ ] 10-1 DDL product 7 3 10-1 SQL> CREATE TABLE product 2 (p_code NUMBER(6), 3 p_name VARCHAR2(30), 4 p_desc VARCHAR2(100), 5 P_price NUMBER(5,2)); 10-1 SQL*PLUS DESC product 10-2 10-2 SQL> DESC product 10-2? ----------------------------------------- -------- ------------- P_CODE NUMBER(6) P_NAME VARCHAR2(30) P_DESC VARCHAR2(100) P_PRICE NUMBER(5,2)

10-2 product Oracle CREATE TABLE Oracle DBA Oracle GRANT Oracle DBA CREATE USER/ALTER USER QUOTA Oracle ARCHITECTURE Oracle ARCHITECTURE Oracle Oracle # $, _ 30 Oracle 10-3 DDL SCOTT 10-3 SQL> CREATE TABLE from (aa char(18)); 10-3 CREATE TABLE from (aa char(18)) * ERROR 1 : ORA-00903: 10-3 10-3 DDL from Oracle 10-4 DDL SCOTT 10-4 SQL> create table emp(xx number); 10-4 create table emp(xx number) * 180

ERROR 1 : ORA-00955: 10-4 10-4 DDL emp SCOTT 10-5 10-6 SQL*PLUS SCOTT 10-5 SQL> DESC dept 10-5? ---------- -------- ------------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) 10-6 SQL> DESC emp 10-6? ---------------- -------- ------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) 10-5 10-6 dept emp DEPTNO FROM 10-7 10-7 SQL> SELECT * 2 FROM dept; 181

10-7 DEPTNO DNAME LOC ---------- -------------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 10-8 SCOTT SYSTEM manager SYSTEM 10-8 SQL> connect system/manager 10-8 dept 10-9 10-9 SQL> SELECT * 2 FROM dept; 10-9 FROM dept * ERROR 2 : ORA-00942: 10-9 dept SYSTEM SCOTT. 10-9 10-10 10-10 SQL> SELECT * 2 FROM scott.dept; 10-10 DEPTNO DNAME LOC ---------- -------------- -------- 10 ACCOUNTING NEW YORK 182

20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 10-10. schema Oracle Oracle 4 VARCHAR2 size size Size 1 4000 CHAR size size 1 2000 DATE 1471 1 1 9999 12 31 NUMBER p s p s p 1 38 s 84 124 VARCHAR2 Oracle 10-1 product p_desc VARCHAR2(100) p_desc 100 NEW 3 Oracle NEW 3 VARCHAR2 CHAR 183

Oracle size Oracle 10-1 p_desc p_desc CHAR(100) p_desc 100 NEW 3 Oracle NEW 3 97 100 CHAR VARCHAR2 Oracle CHAR Oracle VARCHAR2 CHAR CHAR gender M-MALE F FEMALE 4 LOB Large Object Oracle LOB CLOB Character Large Object BLOB Binary Large Object CLOB BLOB Oracle Oracle DBMS_LOB PL/SQL LOB BFILE Binary File BFILE BFILE Oracle Oracle RAW RAW Oracle Oracle Oracle LONG LONG RAW LONG LONG RAW Oracle8 Oracle8 LOB LONG Oracle LOB LONG LONG LOB LONG 2G LOB 4G LOB CLOB BLOB LONG LONG LONG RAW 184

DDL 7 supplier ord er DDL 10-11 10-12 10-11 SQL> CREATE TABLE supplier 2 ( s_code NUMBER(6), 3 sname VARCHAR2(25), 4 contact VARCHAR2(15), 5 phone VARCHAR2(15), 6 fax VARCHAR2(15)); 10-11 10-12 SQL> CREATE TABLE ord 2 (ordno NUMBER(8), 3 p_code NUMBER(6), 4 s_code NUMBER(6), 5 ordate DATE, 6 unit NUMBER(6), 7 price NUMBER(8,2)); 10-12 SQL*PLUS DESC 10-13 10-14 10-13 SQL> DESC supplier 10-13? ----------------------------- -------- ------------ S_CODE NUMBER(6) SNAME VARCHAR2(25) 185

CONTACT PHONE FAX VARCHAR2(15) VARCHAR2(15) VARCHAR2(15) 10-14 SQL> DESC ord 10-14? ----------------------------------------- -------- --- ORDNO NUMBER(8) P_CODE NUMBER(6) S_CODE NUMBER(6) ORDATE DATE UNIT NUMBER(6) PRICE NUMBER(8,2) cat SQL 10-15 10-15 SQL> SELECT * 2 FROM cat; 10-15 TABLE_NAME TABLE_TYPE ------------------------------ ----------- BONUS DEPT EMP ORD PRODUCT SALGRADE SUPPLIER TABLE TABLE TABLE TABLE TABLE TABLE TABLE 3 Primary Key emp emp 186

emp Oracle emp Oracle worker Oracle 10-16 DDL worker 10-16 SQL> CREATE TABLE worker 2 AS 3 SELECT empno, ename name, job, sal+nvl(comm,0) income 4 FROM emp 5 WHERE job NOT IN ('MANAGER', 'PRESIDENT'); 10-16 SQL*PLUS DESC worker 10-17 10-17 SQL> DESC worker 10-17? ----------------------------------------------- -------- ---- EMPNO NUMBER(4) NAME VARCHAR2(10) JOB VARCHAR2(9) INCOME NUMBER 10-18 worker 10-18 SQL> SELECT * 2 FROM worker; 10-18 EMPNO NAME JOB INCOME ---------- ---------- --------- ---------- 187

7369 SMITH CLERK 800 7499 ALLEN SALESMAN 1900 7521 WARD SALESMAN 1750 7654 MARTIN SALESMAN 2650 7788 SCOTT ANALYST 3000 7844 TURNER SALESMAN 1500 7876 ADAMS CLERK 1100 7900 JAMES CLERK 950 7902 FORD ANALYST 3000 7934 MILLER CLERK 1300 10 CREATE TABLE [ ] AS 1 Oracle 2 3 4 5 Oracle ALTER TABLE Oracle ALTER TABLE 4 1 ALTER TABLE ADD [ DEFAULT ] [ ] ALTER TABLE 10-19 worker hiredate 188

10-19 SQL> ALTER TABLE worker 2 ADD (hiredate DATE); 10-19 10-20 DDL 10-20 SQL> SELECT * 2 FROM worker; 10-20 EMPNO NAME JOB INCOME HIREDATE ---------- ---------- --------- ---------- ------- 7369 SMITH CLERK 800 7499 ALLEN SALESMAN 1900 7521 WARD SALESMAN 1750 7654 MARTIN SALESMAN 2650 7788 SCOTT ANALYST 3000 7844 TURNER SALESMAN 1500 7876 ADAMS CLERK 1100 7900 JAMES CLERK 950 7902 FORD ANALYST 3000 7934 MILLER CLERK 1300 10 2 ALTER TABLE MODIFY [ DEFAULT ] [ ] 189

CHAR VARCHAR2 VARCHAR2 CHAR 10-21 worker hiredate 10-21 SQL> ALTER TABLE worker 2 MODIFY (hiredate DEFAULT SYSDATE); 10-21 worker hiredate 10-22 hiredate 10-22 SQL> SELECT * 2 FROM worker; 10-22 EMPNO NAME JOB INCOME HIREDATE ---------- ---------- --------- --------- -------- 7369 SMITH CLERK 800 7499 ALLEN SALESMAN 1900 7521 WARD SALESMAN 1750 7654 MARTIN SALESMAN 2650 7788 SCOTT ANALYST 3000 7844 TURNER SALESMAN 1500 7876 ADAMS CLERK 1100 7900 JAMES CLERK 950 7902 FORD ANALYST 3000 7934 MILLER CLERK 1300 10 10-22 hiredate SYSDATE worker hiredate worker hiredate 10-23 10-23 SQL> INSERT INTO worker(empno, name, job, income, hiredate) 2 VALUES (9000, 'MARY','CLERK', 1000, DEFAULT); 190

10-23 1 INSERT INTO DML SQL*PLUS 10-22 hiredate 10-24 10-24 SQL> SELECT * 2 FROM worker; 10-24 EMPNO NAME JOB INCOME HIREDATE ---------- ---------- --------- ---------- ---------- 7369 SMITH CLERK 800 7499 ALLEN SALESMAN 1900 7521 WARD SALESMAN 1750 7654 MARTIN SALESMAN 2650 7788 SCOTT ANALYST 3000 7844 TURNER SALESMAN 1500 7876 ADAMS CLERK 1100 7900 JAMES CLERK 950 7902 FORD ANALYST 3000 7934 MILLER CLERK 1300 9000 MARY CLERK 1000 10-11 -02 11 10-24 hiredate hiredate SYSDATE worker name hiredate 10-25 10-25 SQL> INSERT INTO worker(empno, name, job, income, hiredate) 2 VALUES (9000, ' ','CLERK', 600, DEFAULT); 10-25 1 10-22 hiredate 10-26 191

10-26 SQL> SELECT * 2 FROM worker 10-26 EMPNO NAME JOB INCOME HIREDATE ---------- ---------- --------- ---------- ---------- 7369 SMITH CLERK 800 7499 ALLEN SALESMAN 1900 7521 WARD SALESMAN 1750 7654 MARTIN SALESMAN 2650 7788 SCOTT ANALYST 3000 7844 TURNER SALESMAN 1500 7876 ADAMS CLERK 1100 7900 JAMES CLERK 950 7902 FORD ANALYST 3000 7934 MILLER CLERK 1300 9000 MARY CLERK 1000 10-11 -02 9000 CLERK 600 10-11 -02 12 10-26 hiredate SYSDATE 3 ALTER TABLE DROP COLUMN ALTER TABLE ALTER TABLE ALTER TABLE DDL Oracle8i 10-27 DDL worker hiredate 10-27 SQL> ALTER TABLE worker 2 DROP COLUMN hiredate; 10-27 192

10-22 worker 10-28 10-28 SQL> SELECT * 2 FROM worker; 10-28 EMPNO NAME JOB INCOME ---------- ---------- --------- ---------- 7369 SMITH CLERK 800 7499 ALLEN SALESMAN 1900 7521 WARD SALESMAN 1750 7654 MARTIN SALESMAN 2650 7788 SCOTT ANALYST 3000 7844 TURNER SALESMAN 1500 7876 ADAMS CLERK 1100 7900 JAMES CLERK 950 7902 FORD ANALYST 3000 7934 MILLER CLERK 1300 10 10-28 hiredate worker 10-28 worker DDL Oracle ALTER TABLE SET UNUSED 4 UNUSED ALTER TABLE SET UNUSED ALTER TABLE SET UNUSED COLUMN DDL UNUSED ALTER TABLE DROP UNUSED COLUMNS SET UNUSED UNUSED Oracle8i UNUSED 193

UNUSED SQL*PLUS SQL Oracle UNUSED UNUSED DROP UNUSED UNUSED DDL UNUSED 10-29 DDL worker income UNUSED 10-29 SQL> ALTER TABLE worker 2 SET UNUSED (income); 10-29 SQL*PLUS DESC worker 10-30 10-30 SQL> DESC worker 10-30? ------------------------------------- -------- --------- EMPNO NUMBER(4) NAME VARCHAR2(10) JOB VARCHAR2(9) 10-30 worker income 10-22 worker 10-31 10-31 SQL> SELECT * FROM worker; 10-31 EMPNO NAME JOB ---------- ---------- --------- 7369 SMITH CLERK 7499 ALLEN SALESMAN 7521 WARD SALESMAN 194

7654 MARTIN SALESMAN 7788 SCOTT ANALYST 7844 TURNER SALESMAN 7876 ADAMS CLERK 7900 JAMES CLERK 7902 FORD ANALYST 7934 MILLER CLERK 10 10-31 worker income income Oracle 10-32 worker 10-32 SQL> ALTER TABLE worker 2 DROP UNUSED COLUMNS; 10-32 Oracle Oracle Oracle Oracle 5 1 Table 2 Index 3 View 4 Sequence 5 Synonym Table 4 5 Oracle Procedure Function Trigger PL/SQL Oracle RENAME RENAME RENAME 10-33 worker staff 195

10-33 SQL> RENAME worker to staff; 10-33 SQL*PLUS DESC worker worker 10-34 10-34 SQL> DESC worker 10-34 ERROR ORA-04043: worker staff worker 10-35 10-35 SQL> DESC staff 10-35? ------------------------------------- -------- ------- EMPNO NUMBER(4) NAME VARCHAR2(10) JOB VARCHAR2(9) 10-36 10-31 10-36 SQL> SELECT * 2 FROM staff; 10-36 EMPNO NAME JOB ---------- ---------- -------- 7369 SMITH CLERK 7499 ALLEN SALESMAN 196

7521 WARD SALESMAN 7654 MARTIN SALESMAN 7788 SCOTT ANALYST 7844 TURNER SALESMAN 7876 ADAMS CLERK 7900 JAMES CLERK 7902 FORD ANALYST 7934 MILLER CLERK 10 Oracle COMMENT COMMENT COMMENT ON TABLE COLUMN. IS 10-37 SQL staff 10-37 SQL> COMMENT ON TABLE staff 2 IS 'Are you believed comments can help you to understand the designs?'; 10-37 10-38 staff 10-38 SQL> SELECT comments 2 FROM user_tab_comments 3 WHERE table_name = 'STAFF'; 10-38 COMMENTS ------------------------------------------------------------------ Are you believed comments can help you to understand the designs? 197

10-39 staff job 10-39 SQL> COMMENT ON COLUMN staff.job 2 IS 'If your answer is yes, you will meet a big trouble'; 10-39 10-40 staff job 10-40 SQL> SELECT comments 2 FROM user_col_comments 3 WHERE table_name = 'STAFF' 4 AND column_name = 'JOB'; 10-40 COMMENTS -------------------------------------------------- If your answer is yes, you will meet a big trouble Oracle 10-41 staff job 10-41 SQL> COMMENT ON COLUMN staff.job 2 IS ''; 10-41 10-42 10-42 SQL> SELECT comments 2 FROM user_col_comments 3 WHERE table_name = 'STAFF' 4 AND column_name = 'JOB'; 10-42 COMMENTS ---------------------------------------------------------------------- 198

10-42 staff job TRUNCATE TABLE DDL TRUNCATE TABLE TRUNCATE TABLE TRUNCATE TABLE DROP TABLE DDL DROP TABLE DROP TABLE DROP TABLE Indexes Views Synonyms TRUNCATE TABLE DROP TABLE Oracle Oracle Oracle TRUNCATE TABLE DROP TABLE 10-43 staff1 10-43 SQL> CREATE TABLE staff1 2 AS 3 SELECT * 4 FROM staff; 199

10-43 10-44 staff1 staff 10-44 SQL> SELECT * 2 FROM staff1; 10-44 EMPNO NAME JOB ---------- ---------- -------- 7369 SMITH CLERK 7499 ALLEN SALESMAN 7521 WARD SALESMAN 7654 MARTIN SALESMAN 7788 SCOTT ANALYST 7844 TURNER SALESMAN 7876 ADAMS CLERK 7900 JAMES CLERK 7902 FORD ANALYST 7934 MILLER CLERK 10 TRUNCATE TABLE staff1 10-45 10-45 SQL> TRUNCATE TABLE staff1; 10-45 10-44 10-46 10-46 SQL> SELECT * 2 FROM staff1; 10-46 SQL*PLUS DESC staff1 200

10-47 10-47 SQL> DESC staff1; 10-47? ----------------------------------------- ------ ------ EMPNO NUMBER(4) NAME VARCHAR2(10) JOB VARCHAR2(9) DROP TABLE staff1 staff1 10-48 10-49 10-48 SQL> DROP TABLE staff1; 10-48 10-49 SQL> DESC staff1 10-49 ERROR ORA-04043: staff1 10-49 staff1 TRUNCATE TABLE DROP TABLE DROP TABLE DROP TABLE staff 10-50 10-50 SQL> DROP TABLE staff; 10-50 10-51 10-51 SQL> SELECT * 201

2 FROM staff; 10-51 FROM staff * ERROR 2 : ORA-00942: SQL*PLUS DESC staff 10-52 10-52 SQL> DESC staff 10-52 ERROR ORA-04043: staff TRUNCATE TABLE DROP TABLE TRUNCATE TABLE DROP TABLE DDL TRUNCATE TABLE DROP TABLE Oracle 202

e_m_shell e_id m_id e_id primary key 10-53 10-53 SQL> create table e_m_shell 2 (e_id number(4) primary key, 3 m_id number(4)); 10-53 e_m_shell 10-54 10-54 SQL> desc e_m_shell 10-54? ------------------------------------ -------- --------- E_ID NOT NULL NUMBER(4) M_ID NUMBER(4) 10-54 e_m_shell emp empno, mgr e_m_shell 10-55 10-55 SQL> INSERT INTO e_m_shell(e_id, m_id) 2 SELECT empno, mgr 3 FROM emp; 10-55 14 e_m_shell 10-56 10-56 SQL> select * from e_m_shell; 10-56 E_ID M_ID ---------- ---------- 203

7369 7902 7499 7698 7521 7698 7566 7839 7654 7698 7698 7839 7782 7839 7788 7566 7839 7844 7698 7876 7788 7900 7698 7902 7566 7934 7782 14 10-56 e_m_shell emp_shell emp 10-57 DDL emp_shell 10-57 SQL> create table emp_shell 2 as select * from emp; 10-57 10-58 DDL emp_shell mgr 10-58 SQL> alter table emp_shell 2 drop column mgr; 10-58 10-59 emp_shell 10-59 SQL> SELECT * 2 FROM emp_shell; 204

10-59 EMPNO ENAME JOB HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ---------- --------- ------- 7369 SMITH CLERK 17-12 -80 800 20 7499 ALLEN SALESMAN 20-2 -81 1600 300 30 7521 WARD SALESMAN 22-2 -81 1250 500 30 7566 JONES MANAGER 02-4 -81 2975 20 7654 MARTIN SALESMAN 28-9 -81 1250 1400 30 7698 BLAKE MANAGER 01-5 -81 2850 30 7782 CLARK MANAGER 09-6 -81 2450 10 7788 SCOTT ANALYST 19-4 -87 3000 20 7839 KING PRESIDENT 17-11 -81 5000 10 7844 TURNER SALESMAN 08-9 -81 1500 0 30 7876 ADAMS CLERK 23-5 -87 1100 20 7900 JAMES CLERK 03-12 -81 950 30 7902 FORD ANALYST 03-12 -81 3000 20 7934 MILLER CLERK 23-1 -82 1300 10 14 10-59 emp_shell e_shell e_m_shell 10-60 10-60 SQL> SELECT e.empno, e.ename, e.job, m.empno, m.ename, m.job 2 FROM emp_shell e, e_m_shell, (SELECT empno, ename, job 3 FROM emp) m 4 WHERE e.empno = e_m_shell.e_id 5 AND e_m_shell.m_id = m.empno; 10-60 EMPNO ENAME JOB EMPNO ENAME JOB ---------- ---------- --------- ---------- ---------- ------- 7369 SMITH CLERK 7902 FORD ANALYST 7499 ALLEN SALESMAN 7698 BLAKE MANAGER 7521 WARD SALESMAN 7698 BLAKE MANAGER 7566 JONES MANAGER 7839 KING PRESIDENT 7654 MARTIN SALESMAN 7698 BLAKE MANAGER 7698 BLAKE MANAGER 7839 KING PRESIDENT 7782 CLARK MANAGER 7839 KING PRESIDENT 7788 SCOTT ANALYST 7566 JONES MANAGER 7844 TURNER SALESMAN 7698 BLAKE MANAGER 205

7876 ADAMS CLERK 7788 SCOTT ANALYST 7900 JAMES CLERK 7698 BLAKE MANAGER 7902 FORD ANALYST 7566 JONES MANAGER 7934 MILLER CLERK 7782 CLARK MANAGER 13 10-60 INSERT DML DML DDL DML DDL CREATE TABLE 4 UNUSED UNUSED UNUSED TRUNCATE TABLE DROP TABLE 206

11 SQL SQL SQL SQL*PLUS 11.1 2 1500 1500 1500 11-1 11-1 SQL> SELECT empno, ename, sal 2 FROM emp 3 WHERE sal >= 1500; 11-1 EMPNO ENAME SAL ---------- ------------------- --------- 7499 ALLEN 1600 7566 JONES 2975 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 7902 FORD 3000 8 1600 1600 1600 SQL SQL 11.2 & Oracle Oracle 11-2

11-2 SQL> SELECT empno, ename, sal 2 FROM emp 3 WHERE sal >= &v_salary; 11-2 &v_salary & 11-2 SQL Oracle v_salary : 1600 11-2 v_salary 1600 11-2 EMPNO ENAME SAL ---------- ---------- ---------- 7499 ALLEN 1600 7566 JONES 2975 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7902 FORD 3000 7 11-3 SQL*PLUS 11-2 SQL sal 11-3 SQL> save d:\sql\sal replace 11-3 d:\sql\sal.sql 2000 2000 sal Oracle v_salary : 2000 11-4 11-4 SQL> @d:\sql\sal 208

11-4 v_salary 2000 11-4 EMPNO ENAME SAL ---------- ---------- --------- 7566 JONES 2975 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7902 FORD 3000 6 11-4 sal 11-5 SQL*PLUS 11-5 SQL> SET VERIFY ON 11-6 SQL*PLUS sal 11-6 SQL> @d:\sql\sal 11-6 v_salary 1700 3: WHERE sal >= &v_salary 3: WHERE sal >= 1700 11-6 EMPNO ENAME SAL ---------- ---------- ---------- 7566 JONES 2975 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 209

7902 FORD 3000 6 VERIFY ON SQL VERIFY OFF 11.3 11-7 11-7 SQL> SELECT empno, ename, sal, job, deptno 2 FROM emp 3 WHERE job = &v_job; 11-7 v_job : CLERK 11-7 3: WHERE job = &v_job 3: WHERE job = CLERK WHERE job = CLERK * ERROR 3 : ORA-00904 11-7 SQL CLERK Oracle SQL*PLUS &v_job CLERK job CLERK 11-7 SQL JOB 11-8 11-8 SQL> SAVE D:\SQL\JOB 11-8 D:\SQL\JOB.sql 210

VERIFY OFF 11-9 11-9 SQL> set verify off 11-10 SQL*PLUS job 11-10 SQL> @D:\SQL\JOB CLERK 11-10 v_job 'CLERK' 11-10 EMPNO ENAME SAL JOB DEPTNO ---------- ---------- ---------- --------- ------- 7369 SMITH 800 CLERK 20 7876 ADAMS 1100 CLERK 20 7900 JAMES 950 CLERK 30 7934 MILLER 1300 CLERK 10 SQL*PLUS 11-11 SQL*PLUS job 11-11 SQL> @D:\SQL\JOB CLERK 11-11 v_job 'CLERK'; 11-11 WHERE job = 'CLERK'; * ERROR 3 : ORA-00911: 211

Oracle SQL SQL SQL SQL*PLUS SQL 11-12 SQL*PLUS job 11-12 SQL> EDIT D:\SQL\JOB 11-12 &v_job 11-13 SQL*PLUS job 11-13 SQL> @D:\SQL\JOB CLERK 11-13 v_job CLERK 11-13 EMPNO ENAME SAL JOB DEPTNO ---------- ---------- ---------- --------- -------- 7369 SMITH 800 CLERK 20 7876 ADAMS 1100 CLERK 20 7900 JAMES 950 CLERK 30 7934 MILLER 1300 CLERK 10 212

CLERK 11.4 && & && 11-14 11-14 SQL> SELECT ename, job, &&v_col 2 FROM emp 3 ORDER BY &v_col; sal 11-14 v_col sal 11-14 ENAME JOB SAL ---------- --------- ---------- SMITH CLERK 800 JAMES CLERK 950 ADAMS CLERK 1100 WARD SALESMAN 1250 MARTIN SALESMAN 1250 MILLER CLERK 1300 TURNER SALESMAN 1500 ALLEN SALESMAN 1600 CLARK MANAGER 2450 BLAKE MANAGER 2850 JONES MANAGER 2975 SCOTT ANALYST 3000 FORD ANALYST 3000 KING PRESIDENT 5000 14 11-14 SQL v_col Oracle Oracle & & Oracle Oracle 213

11-14 SQL Oracle 11-14 Oracle sal comm SQL*PLUS UNDEFINE 11-15 11-15 SQL> undefine v_col 11-14 SQL 11-16 11-16 SQL> SELECT ename, job, &&v_col 2 FROM emp 3 ORDER BY &v_col; comm 11-16 v_col comm 11-16 ENAME JOB COMM ---------- --------- ---------- TURNER SALESMAN 0 ALLEN SALESMAN 300 WARD SALESMAN 500 MARTIN SALESMAN 1400 SMITH CLERK JONES MANAGER JAMES CLERK MILLER CLERK FORD ANALYST ADAMS CLERK BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT 14 11-16 SQL*PLUS DEFINE ACCEPT 214

SQL 11.5 SQL SELECT ORDER BY WHERE 11-17 11-17 SQL> SELECT &column1, &column2 2 FROM &table_name 3 WHERE &condition 4 ORDER BY &sorting; SQL 11-17 column1 dname column2 loc table_name dept condition deptno <> 10 sorting dname 11-17 DNAME LOC -------------- ------- OPERATIONS BOSTON RESEARCH DALLAS SALES CHICAGO 11-17 dept SQL emp 11-18 11-18 215

SQL> SELECT &column1, &column2 2 FROM &table_name 3 WHERE &condition 4 ORDER BY &sorting; SQL 11-18 column1 ename column2 sal table_name emp condition job = 'CLERK' sorting sal 11-18 ENAME SAL ---------- ---------- SMITH 800 JAMES 950 ADAMS 1100 MILLER 1300 11-18 emp 11.6 DEFINE SQL*PLUS DEFINE DEFINE 11-19 11-19 SQL> DEFINE v_job = CLERK 11-20 SQL 11-20 SQL> SELECT ename, sal, job 2 FROM emp 3 WHERE job = '&v_job' 4 ORDER BY sal; 11-19 SQL v_job CLERK Oracle 216

CLERK v_job 11-20 SQL Oracle 11-20 11-20 ENAME SAL JOB ---------- ---------- ----- SMITH 800 CLERK JAMES 950 CLERK ADAMS 1100 CLERK MILLER 1300 CLERK SQL*PLUS DEFINE = 11-19 SQL*PLUS DEFINE 11-21 11-21 SQL> DEFINE v_job 11-21 DEFINE V_JOB = "CLERK" (CHAR) SQL*PLUS DEFINE 11-22 11-22 SQL> DEFINE 11-22 DEFINE _SQLPLUS_RELEASE = "900010001" (CHAR) DEFINE _EDITOR = "Notepad" (CHAR) With the Partitioning option JServer Release 9.0.1.1.1 - Production" (CHAR) DEFINE _O_RELEASE = "900010301" (CHAR) DEFINE V_JOB = "CLERK" (CHAR) Oracle Oracle ACCEPT 217

11.7 ACCEPT SQL*PLUS ACCEPT 11-23 11-32 11-23 SQL> SELECT ename, sal, job 2 FROM emp 3 WHERE job = UPPER('&v_job') 4 ORDER BY sal; SQL clerk 11-23 v_job clerk 11-23 ENAME SAL JOB ---------- ---------- ----- SMITH JAMES ADAMS MILLER 800 CLERK 950 CLERK 1100 CLERK 1300 CLERK 11-24 SQL*PLUS 11-23 SQL acc_job 11-24 SQL> save d:\sql\acc_job 11-24 d:\sql\acc_job.sql 11-25 SQL*PLUS acc_job 11-25 218

SQL> edit d:\sql\acc_job ACCEPT v_job PROMPT 'Please Enter the Job Title:' 11-26 11-26 11-27 SQL*PLUS acc_job 11-27 SQL> @d:\sql\acc_job SQL*PLUS 11-27 Job clerk 11-27 ENAME SAL JOB ---------- ---------- ----- SMITH 800 CLERK JAMES 950 CLERK ADAMS 1100 CLERK MILLER 1300 CLERK 11-28 SQL*PLUS acc_job.sql SQL 11-28 SQL> get d:\sql\acc_job 11-28 1 ACCEPT v_job PROMPT 'Please Enter the Job Title:' 219

2 SELECT ename, sal, job 3 FROM emp 4 WHERE job = UPPER('&v_job') 5* ORDER BY sal 11-29 SQL*PLUS SQL SQL chacc_job 11-29 SQL> save d:\sql\chacc_job replace 11-29 d:\sql\chacc_job.sql 11-30 SQL*PLUS chacc_job.sql 11-30 SQL> edit d:\sql\chacc_job.sql ACCEPT v_job PROMPT ' Job :' 11-31 11-31 11-32 SQL*PLUS chacc_job.sql 11-32 SQL> @d:\sql\chacc_job.sql SQL*PLUS 11-32 11-32 220

Job clerk 11-32 ENAME SAL JOB ---------- ---------- ----- SMITH 800 CLERK JAMES 950 CLERK ADAMS 1100 CLERK MILLER 1300 CLERK 11.8 ACCEPT HIDE ACCEPT HIDE ACCEPT 11-33 MANAGER emp ename sal job 11-33 SQL> SELECT ename, sal, job 2 FROM emp 3 WHERE '&pwd' = 'MANAGER'; SQL MANAGER 11-33 pwd MANAGER ename sal job 11-33 ENAME SAL JOB ---------- ---------- --------- SMITH 800 CLERK ALLEN 1600 SALESMAN WARD 1250 SALESMAN JONES 2975 MANAGER MARTIN 1250 SALESMAN BLAKE 2850 MANAGER CLARK 2450 MANAGER SCOTT 3000 ANALYST KING 5000 PRESIDENT 221

TURNER 1500 SALESMAN ADAMS 1100 CLERK JAMES 950 CLERK FORD 3000 ANALYST MILLER 1300 CLERK 14 11-34 SQL*PLUS SQL D:\SQL PWD 11-34 SQL> SAVE D:\SQL\PWD 11-35 SQL*PLUS PWD SQL 11-35 SQL> EDIT D:\SQL\PWD SQL ACCEPT pwd PROMPT 'Please Enter Your Password:' HIDE 11-36 11-36 11-37 SQL*PLUS PWD SQL 11-37 SQL> @d:\sql\pwd PWD MANAGER 11-37 222

******* 11-33 MANAGER ******* Password 11-37 11-33 11-37 ENAME SAL JOB ---------- ---------- --------- SMITH 800 CLERK ALLEN 1600 SALESMAN WARD 1250 SALESMAN JONES 2975 MANAGER MARTIN 1250 SALESMAN BLAKE 2850 MANAGER CLARK 2450 MANAGER SCOTT 3000 ANALYST KING 5000 PRESIDENT TURNER 1500 SALESMAN ADAMS 1100 CLERK JAMES 950 CLERK FORD 3000 ANALYST MILLER 1300 CLERK 14 MANAGER MANAGER 11-38 11-38 SQL> @D:\SQL\PWD manager 11-38 ******* 11-38 MANAGER 11-38 Oracle Oracle 223

11.9 ACCEPT ACCEPT ACCEPT ACCEPT [ ] [FORMAT ] [ PROMPT ] [HIDE] SQL*PLUS 240 FORMAT 9 99,999.00 A28 PROMPT HIDE PASSWORD ACCEPT & & 11.10 SQL SQL*PLUS SQL*PLUS ACCEPT ACCEPT SQL*PLUS SQL*PLUS SQL 11.11 SQL*PLUS & SET VERIFY ON/OFF && & && DEFINE 224

ACCEPT ACCEPT ACCEPT HIDE DEFINE ACCEPT 225

DML Data Manipulating Language Transaction Control SQL DML DML 3 INSERT UPDATE DELETE 12-1 12-3 SQL emp_dml dept_dml 12-1 SQL> CREATE TABLE emp_dml 2 AS 3 SELECT * 4 FROM emp; 12-1 SQL*PLUS 12-2 emp_dml 12-2 SQL> SELECT ename, job, sal 2 FROM emp_dml; 12-2 emp_dml 12-3 SQL dept_dml 12-3 SQL> CREATE TABLE dept_dml

2 AS 3 SELECT * 4 FROM dept; 12-3 SQL*PLUS 12-4 dept_dml 12-4 SQL> select * from dept_dml; 12-4 DEPTNO DNAME LOC ---------- -------------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON DML INSERT INSERT INTO [ [ ] ] VALUES [ ] INSERT 12-5 DML dept_dml 12-5 SQL> INSERT INTO dept_dml (deptno, dname, loc) 2 VALUES (66, ' ', ' '); 12-5 1 227

dept_dml 12-6 12-5 DML 12-6 SQL> SELECT * 2 FROM dept_dml; 12-6 DEPTNO DNAME LOC ---------- -------------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 66 12-6 12-5 DML INSERT INTO VALUES INSERT INTO 12-7 12-7 SQL> INSERT INTO dept_dml 2 VALUES (77, ' ', ' '); 12-7 1 dept_dml 12-8 12-7 DML 12-8 SQL> SELECT * 2 FROM dept_dml; 12-8 DEPTNO DNAME LOC 228

---------- -------------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 66 77 6 12-7 NULL NULL INSERT NULL NULL VALUES ' ' dept_dml NULL 12-9 DML 12-9 SQL> INSERT INTO dept_dml (deptno, dname, loc) 2 VALUES (88, '', ' '); 12-9 1 12-9 DML dname NULL 12-9 DML dept_dml 12-10 12-10 SQL> SELECT * 2 FROM dept_dml; 12-10 DEPTNO DNAME LOC 229

---------- -------------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 66 77 88 7 12-10 NULL VALUES NULL dept_dml NULL 12-11 DML 12-11 SQL> INSERT INTO dept_dml (deptno, dname, loc) 2 VALUES (44, NULL, ' '); 12-11 1 12-12 12-12 SQL> SELECT * 2 FROM dept_dml; 12-12 DEPTNO DNAME LOC ---------- -------------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 66 77 88 44 8 12-12 230

NULL dept_dml NULL 12-13 DML 12-13 SQL> INSERT INTO dept_dml (deptno, dname) 2 VALUES (33, ' '); 12-13 1 12-14 NULL 12-14 SQL> SELECT * 2 FROM dept_dml; 12-14 DEPTNO DNAME LOC ---------- -------------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 66 77 88 44 33 9 12-14 NULL 12-15 SQL 12-15 SQL> COMMIT; 12-15 231

SQL COMMIT dept_dml emp_dml hiredate Oracle SYSDATE 12-16 12-16 SQL> INSERT INTO emp_dml (empno, ename, job, mgr, 2 hiredate, sal, comm, deptno) 3 VALUES (7800, ' ', ' ',7900, SYSDATE, 666, 77, 66); 12-16 1 12-17 SQL*PLUS 12-17 SQL> col empno for 99999 SQL> col sal for 99999 SQL> col comm for 99999 SQL> col deptno for 99999 12-18 SQL*PLUS 12-18 SQL> set line 100 12-19 SYSDATE 12-19 SQL> SELECT * 2 FROM emp_dml; 12-19 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 232

------ ---------- --------- ---------- ---------- ------ ------ --- 7369 SMITH CLERK 7902 17-12 -80 800 20 7499 ALLEN SALESMAN 7698 20-2 81 1600 300 30 7521 WARD SALESMAN 7698 22-2 -81 1250 500 30 7566 JONES MANAGER 7839 02-4 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9 81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5 -81 2850 30 7782 CLARK MANAGER 7839 09-6 -81 2450 10 7788 SCOTT ANALYST 7566 19-4 -87 3000 20 7839 KING PRESIDENT 17-11 -81 5000 10 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5 -87 1100 20 7900 JAMES CLERK 7698 03-12 -81 950 30 7902 FORD ANALYST 7566 03-12 -81 3000 20 7934 MILLER CLERK 7782 23-1 -82 1300 10 7800 7900 17-12 -02 666 77 66 15 12-19 hiredate 12-20 SQL sales 12-20 SQL> CREATE TABLE sales (code, name, salary, commission) 2 AS 3 SELECT empno, ename, sal, comm 4 FROM emp; 12-20 12-20 SQL sales sales sales 12-21 SQL sales 12-21 SQL> TRUNCATE TABLE sales; 12-21 233

12-22 sales 12-22 SQL> SELECT * FROM SALES; 12-22 sales 12-23 INSERT 12-23 SQL> INSERT INTO sales (code, name, salary, commission) 2 SELECT empno, ename, sal, comm 3 FROM emp 4 WHERE job LIKE 'SALE%'; 12-23 4 12-24 12-24 SQL> SELECT * FROM SALES; 12-24 CODE NAME SALARY COMMISSION ---------- ---------- ---------- ---------- 7499 ALLEN 1600 300 7521 WARD 1250 500 7654 MARTIN 1250 1400 7844 TURNER 1500 0 VALUES INSERT INSERT 234

VALUES INSERT SQL*PLUS Oracle SQL 12-25 12-25 SQL> INSERT INTO emp_dml (empno, ename, job, mgr, 2 hiredate, sal, comm, deptno) 3 VALUES (&id, '&name', '&job', 7689, 4 TO_DATE('&hiredate', 'YYYY MM DD'), 5 666, 77, 66); 12-25 SQL Oracle Oracle 1 Oracle 12-25 id 1001 name job hiredate 2002 07 08 12-25 1 12-25 TO_DATE 12-26 SQL*PLUS 12-25 SQL d:\sql\insert.sql 12-26 SQL> save d:\sql\insert 12-26 d:\sql\insert.sql 235

12-27 12-27 SQL> SELECT * 2 FROM emp_dml; 12-27 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------- -------- ---------- ---- ------ ------- 7369 SMITH CLERK 7902 17-12 -80 800 20 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2 -81 1250 500 30 7566 JONES MANAGER 7839 02-4 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5 -81 2850 30 7782 CLARK MANAGER 7839 09-6 -81 2450 10 7788 SCOTT ANALYST 7566 19-4 -87 3000 20 7839 KING PRESIDENT 17-11 -81 5000 10 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5 -87 1100 20 7900 JAMES CLERK 7698 03-12 -81 950 30 7902 FORD ANALYST 7566 03-12 -81 3000 20 7934 MILLER CLERK 7782 23-1 -82 1300 10 7800 7900 17-12 -02 666 77 66 1001 7689 08-7 -02 666 77 66 16 12-25 SQL SQL*PLUS ACCEPT 12-25 SQL 12-28 SQL*PLUS d:\sql\insert.sql 12-28 SQL> edit d:\sql\insert.sql 12-28 236

d:\sql\insert.sql INSERT 12-28 ACCEPT 12-29 SQL*PLUS 12-29 SQL> @d:\sql\insert.sql 12-29 1002 2001 03 08 2002 12 09 12-29 1 12-29 12-25 12-30 12-30 SQL> SELECT * 2 FROM emp_dml; 12-30 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------- ---------- ---------- ------ --------- 7369 SMITH CLERK 7902 17-12 -80 800 20 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2 -81 1250 500 30 237

7566 JONES MANAGER 7839 02-4 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5 -81 2850 30 7782 CLARK MANAGER 7839 09-6 -81 2450 10 7788 SCOTT ANALYST 7566 19-4 -87 3000 20 7839 KING PRESIDENT 17-11 -81 5000 10 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5 -87 1100 20 7900 JAMES CLERK 7698 03-12 -81 950 30 7902 FORD ANALYST 7566 03-12 -81 3000 20 7934 MILLER CLERK 7782 23-1 -82 1300 10 7800 7900 17-12 -02 666 77 66 1001 7689 08-7 -02 666 77 66 1002 7689 09-12 -02 666 77 66 17 2 9.11 12-31 UPDATE 12-31 SQL> UPDATE emp_dml 2 SET sal = sal * 0.9; 12-31 17 12-32 sal 12-32 SQL> SELECT ename, job, sal 2 FROM emp_dml; 12-32 ENAME JOB SAL ---------- --------- ---------- 238

SMITH CLERK 720 ALLEN SALESMAN 1440 WARD SALESMAN 1125 JONES MANAGER 2677.5 MARTIN SALESMAN 1125 BLAKE MANAGER 2565 CLARK MANAGER 2205 SCOTT ANALYST 2700 KING PRESIDENT 4500 TURNER SALESMAN 1350 ADAMS CLERK 990 JAMES CLERK 855 FORD ANALYST 2700 MILLER CLERK 1170 599.4 599.4 599.4 17 12-32 emp_dml UPDATE UPDATE SET = [ = ] [WHERE ] INSERT UPDATE UPDATE WHERE emp_dml 12-33 12-33 SQL> select * from emp_dml; 12-33 EMPNO ENAME JOB MGR HIREDATE SAL COMM --------- --------- -------- ---------- ---------- ---------- --------- 239

7369 SMITH CLERK 7902 17-12 -80 800 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 7521 WARD SALESMAN 7698 22-2 -81 1250 500 7566 JONES MANAGER 7839 02-4 -81 2975 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 7698 BLAKE MANAGER 7839 01-5 -81 2850 7782 CLARK MANAGER 7839 09-6 -81 2450 7788 SCOTT ANALYST 7566 19-4 -87 3000 7839 KING PRESIDENT 17-11 -81 5000 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 7876 ADAMS CLERK 7788 23-5 -87 1100 7900 JAMES CLERK 7698 03-12 -81 950 7902 FORD ANALYST 7566 03-12 -81 3000 7934 MILLER CLERK 7782 23-1 -82 1300 7800 7900 17-12 -02 666 77 7810 CTO 7900 17-12 -02 4444 1001 7689 08-7 -02 666 77 1002 7689 09-12 -02 666 77 18 12-33, emp_dml 666 CTO 4444 666 1000 12-34 DML 12-34 SQL> UPDATE emp_dml 2 SET sal = 1000 3 WHERE ename = ' '; 12-34 2 12-34 Oracle 2 1000 12-35 12-35 SQL> SELECT * 2 FROM emp_dml; 240

12-35 EMPNO ENAME JOB MGR HIREDATE SAL COMM --------- --------- --------- ---------- --------- ---------- -------- 7369 SMITH CLERK 7902 17-12 -80 800 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 7521 WARD SALESMAN 7698 22-2 -81 1250 500 7566 JONES MANAGER 7839 02-4 -81 2975 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 7698 BLAKE MANAGER 7839 01-5 -81 2850 7782 CLARK MANAGER 7839 09-6 -81 2450 7788 SCOTT ANALYST 7566 19-4 -87 3000 7839 KING PRESIDENT 17-11 -81 5000 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 7876 ADAMS CLERK 7788 23-5 -87 1100 7900 JAMES CLERK 7698 03-12 -81 950 7902 FORD ANALYST 7566 03-12 -81 3000 7934 MILLER CLERK 7782 23-1 -82 1300 7800 7900 17-12 -02 1000 77 7810 CTO 7900 17-12 -02 1000 1001 7689 08-7 -02 666 77 1002 7689 09-12 -02 666 77 18 12-35 emp_dml 1000 CTO 1000 emp_dml ename WHERE Primary Key UPDATE WHERE WHERE UPDATE WHERE 666 241

12-36 salgrade 12-36 SQL> SELECT * 2 FROM salgrade; 12-36 GRADE LOSAL HISAL ---------- ---------- -------- 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999 salgrade GRADE LOSAL HISAL 12-36 700 12-37 UPDATE 12-37 SQL> UPDATE emp_dml 2 SET sal = (SELECT losal 3 FROM salgrade 4 WHERE grade = 1) 5 WHERE sal < (SELECT losal 6 FROM salgrade 7 WHERE grade = 1); 12-37 3 12-37 UPDATE WHERE SET sal UPDATE 12-38 12-37 242

12-38 SQL> SELECT * 2 FROM emp_dml; 12-38 EMPNO ENAME JOB MGR HIREDATE SAL COMM --------- --------- --------- ---------- ---------- ---------- ----- 7369 SMITH CLERK 7902 17-12 -80 800 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 7521 WARD SALESMAN 7698 22-2 -81 1250 500 7566 JONES MANAGER 7839 02-4 -81 2975 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 7698 BLAKE MANAGER 7839 01-5 -81 2850 7782 CLARK MANAGER 7839 09-6 -81 2450 7788 SCOTT ANALYST 7566 19-4 -87 3000 7839 KING PRESIDENT 17-11 -81 5000 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 7876 ADAMS CLERK 7788 23-5 -87 1100 7900 JAMES CLERK 7698 03-12 -81 950 7902 FORD ANALYST 7566 03-12 -81 3000 7934 MILLER CLERK 7782 23-1 -82 1300 7800 7900 17-12 -02 700 77 7810 CTO 7900 17-12 -02 4444 1001 7689 08-7 -02 700 77 1002 7689 09-12 -02 700 77 18 12-38 emp_dml 700 CLERK 800 SMITH EMPNO 7369 12-39 UPDATE 243

12-39 SQL> UPDATE emp_dml 2 SET (job, sal) = (SELECT job, sal 3 FROM emp_dml 4 WHERE empno = 7369) 5 WHERE job = ' '; 12-39 2 UPDATE SET 12-40 12-39 12-40 SQL> SELECT * 2 FROM emp_dml; 12-40 EMPNO ENAME JOB MGR HIREDATE SAL COMM --------- --------- --------- ---------- ---------- ---------- -------- 7369 SMITH CLERK 7902 17-12 -80 800 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 7521 WARD SALESMAN 7698 22-2 -81 1250 500 7566 JONES MANAGER 7839 02-4 -81 2975 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 7698 BLAKE MANAGER 7839 01-5 -81 2850 7782 CLARK MANAGER 7839 09-6 -81 2450 7788 SCOTT ANALYST 7566 19-4 -87 3000 7839 KING PRESIDENT 17-11 -81 5000 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 7876 ADAMS CLERK 7788 23-5 -87 1100 7900 JAMES CLERK 7698 03-12 -81 950 7902 FORD ANALYST 7566 03-12 -81 3000 7934 MILLER CLERK 7782 23-1 -82 1300 7800 CLERK 7900 17-12 -02 800 77 7810 CTO 7900 17-12 -02 4444 1001 7689 08-7 -02 700 77 1002 CLERK 7689 09-12 -02 800 77 18 244

12-40 emp_dml CLERK 800 2500 1300 12-41 emp_dml 12-41 SQL> SELECT * 2 FROM emp_dml 3 WHERE (job = 'MANAGER') OR (JOB = 'SALESMAN') 4 ORDER BY sal; 12-41 EMPNO ENAME JOB MGR HIREDATE SAL COMM --------- --------- -------- ---------- ---------- ---------- ------- 7521 WARD SALESMAN 7698 22-2 -81 1250 500 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 7844 TURNER SALESMAN 7698 08-9 -81 1500 0 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 7782 CLARK MANAGER 7839 09-6 -81 2450 7698 BLAKE MANAGER 7839 01-5 -81 2850 7566 JONES MANAGER 7839 02-4 -81 2975 7 12-41 12-42 DELETE 12-42 SQL> DELETE FROM emp_dml 245

2 WHERE (job = 'MANAGER' AND sal > 2500) 3 OR (JOB = 'SALESMAN' AND sal > 1300); 12-42 4 12-43 12-42 12-43 SQL> SELECT * 2 FROM emp_dml 3 WHERE (job = 'MANAGER') OR (JOB = 'SALESMAN') 4 ORDER BY sal; 12-43 EMPNO ENAME JOB MGR HIREDATE SAL COMM --------- --------- --------- ---------- ---------- ---------- ------- 7521 WARD SALESMAN 7698 22-2 -81 1250 500 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 7782 CLARK MANAGER 7839 09-6 -81 2450 12-43 DML / DML DELETE DELETE [FROM] [WHERE ] SQL FROM Oracle Unix C Oracle 246

UPDATE DELETE 12-42 DELETE WHERE 12-44 DML 12-44 SQL> DELETE FROM emp_dml 2 WHERE (job = 'MANAGER' AND sal < 2500) 3 OR (JOB = 'SALESMAN' AND sal > 1300); 12-44 3 12-45 12-45 SQL> SELECT * 2 FROM emp_dml 3 WHERE (job = 'MANAGER') OR (JOB = 'SALESMAN') 4 ORDER BY sal; 12-45 EMPNO ENAME JOB MGR HIREDATE SAL COMM --------- --------- --------- ---------- ---------- ---------- ------------ 7521 WARD SALESMAN 7698 22-2 -81 1250 500 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 7698 BLAKE MANAGER 7839 01-5 -81 2850 7566 JONES MANAGER 7839 02-4 -81 2975 12-45, WHERE 12-46 DML 12-46 SQL> DELETE emp_dml; 12-46 18 12-46 12-47 247

12-47 SQL> SELECT * 2 FROM emp_dml; 12-47 12-47 emp_dml 12-44 12-47 DELETE WHERE DELETE WHERE DELETE WHERE 12-48 DML 12-48 SQL> DELETE FROM emp_dml 2 WHERE deptno = 3 (SELECT deptno 4 FROM dept_dml 5 WHERE dname = UPPER(' ')); 12-48 4 12-49 12-49 SQL> SELECT ename, job, sal, comm, deptno 2 FROM emp_dml; 12-49 ENAME JOB SAL COMM DEPTNO ---------- --------- ---------- ---------- ------ 248

SMITH CLERK 800 20 ALLEN SALESMAN 1600 300 30 WARD SALESMAN 1250 500 30 JONES MANAGER 2975 20 MARTIN SALESMAN 1250 1400 30 BLAKE MANAGER 2850 30 CLARK MANAGER 2450 10 SCOTT ANALYST 3000 20 KING PRESIDENT 5000 10 TURNER SALESMAN 1500 0 30 ADAMS CLERK 1100 20 JAMES CLERK 950 30 FORD ANALYST 3000 20 MILLER CLERK 1300 10 14 12-49 66 Oracle Transactions 8888 30 Oracle 6800 6800 ATM Oracle 1 2 3 6800 4 6800 3 6800 Oracle Oracle 3 4 Oracle 249

Transactions Transactions Oracle Transactions COMMIT ROLLBACK Oracle Transactions DML DDL DCL Transactions Oracle OLTP Oracle Transactions Oracle SQL 1 COMMIT 2 ROLLBACK 3 DDL 4 DCL 5 SQL*PLUS 6 SQL*PLUS 7 Oracle COMMIT ROLLBACK 3 7 12.14 ATM Oracle COMMIT ROLLBACK SCOTT 66 88 12-50 DML 250

12-50 SQL> UPDATE emp_dml 2 SET deptno = 88 3 WHERE deptno = 66; 12-50 4 Oracle Oracle 4 12-51 12-51 SQL> SELECT empno, ename, deptno 2 FROM emp_dml 3 WHERE deptno > 30; 12-51 EMPNO ENAME DEPTNO ---------- ---------- ---------- 7800 88 7810 88 1001 88 1002 88 12-51 SYSTEM/MANAGER 12-51 12-52 12-52 SQL> SELECT empno, ename, deptno 2 FROM scott.emp_dml 3 WHERE deptno > 30; 12-52 EMPNO ENAME DEPTNO ---------- ---------- ---------- 7800 66 7810 66 1001 66 1002 66 251

12-52 66 12-53 COMMIT 12-53 SQL> COMMIT; 12-53 12-52 12-54 12-54 SQL> SELECT empno, ename, deptno 2 FROM scott.emp_dml 3 WHERE deptno > 30; 12-54 EMPNO ENAME DEPTNO ---------- ---------- ---------- 7800 88 7810 88 1001 88 1002 88 88 12.9 UPDATE COMMIT 800 700 88 12-55 DML 252

12-55 SQL> UPDATE emp_dml 2 SET sal = 800 3 WHERE deptno = 88; 12-55 4 Oracle 4 Oracle 12-56 12-56 SQL> SELECT ename, job, sal 2 FROM emp_dml 3 WHERE deptno = 88; 12-56 ENAME JOB SAL ---------- --------- ------- MANAGER 800 CTO 800 800 800 SYSTEM/MANAGER 12-57 12-57 SQL> SELECT ename, job, sal 2 FROM scott.emp_dml 3 WHERE deptno = 88; 12-57 ENAME JOB SAL ---------- --------- ------- MANAGER 666 CTO 4444 666 666 253

12-57 COMMIT 12-58 DDL dept_ddl SCOTT COMMIT 12-58 SQL> CREATE TABLE dept_ddl 2 AS 3 SELECT * 4 FROM dept; 12-58 SYSTEM 12-59 12-59 SQL> SELECT ename, job, sal 2 FROM scott.emp_dml 3 WHERE deptno = 88; 12-59 ENAME JOB SAL ---------- --------- ---------- MANAGER 800 CTO 800 800 800 12-59 job CTO 3000 UPDATE DCL 1 DCL 2 DCL DDL DCL DCL 254

Oracle SQL SQL*PLUS SQL*PLUS SQL*PLUS SQL*PLUS 12-60 DML job MANAGER 12-60 SQL> UPDATE emp_dml 2 SET job = 'MANAGER' 3 WHERE empno = 1001; 12-60 1 12-61 12-61 SQL> SELECT empno, ename, job, sal 2 FROM emp_dml 3 WHERE empno <= 7000; 12-61 EMPNO ENAME JOB SAL ---------- ---------- --------- -------- 1001 MANAGER 800 1002 800 12-61 SQL*PLUS SQL*PLUS 12-62 12-62 255

SCOTT Oracle 12-61 12-63 12-63 SQL> SELECT empno, ename, job, sal 2 FROM emp_dml 3 WHERE empno <= 7000; 12-63 EMPNO ENAME JOB SAL ---------- ---------- --------- ---------- 1001 800 1002 800 job MANAGER SQL*PLUS SQL*PLUS Oracle SQL*PLUS SQL*PLUS 12-60 DML 12-64 job MANAGER 12-64 SQL> UPDATE emp_dml 2 SET job = 'MANAGER' 3 WHERE empno = 1001; 12-64 1 SQL*PLUS EXIT SQL*PLUS 12-65 12-65 SQL> exit SCOTT Oracle 12-61 12-66 12-66 SQL> SELECT empno, ename, job, sal 2 FROM emp_dml 3 WHERE empno <= 7000; 256

12-66 EMPNO ENAME JOB SAL ---------- ---------- --------- ---------- 1001 MANAGER 800 1002 800 12-66 SQL*PLUS EXIT SQL*PLUS SQL*PLUS SQL*PLUS DML DDL DCL COMMIT ROLLBACK Oracle DML 12-67 Oracle DML 12-67 SQL> SET AUTOCOMMIT ON 12-68 DML 1002 MANAGER 12-68 SQL> UPDATE emp_dml 2 SET job = 'MANAGER' 3 WHERE empno = 1002; 12-68 1 12-68 SYSTEM/MANAGER 12-69 257

12-69 SQL> SELECT empno, ename, job, sal 2 FROM scott.emp_dml 3 WHERE empno <= 7000; 12-69 EMPNO ENAME JOB SAL ---------- ---------- --------- ---------- 1001 MANAGER 800 1002 MANAGER 800 12-69 AUTOCOMMIT ON DML DML DML Oracle Oracle AUTOCOMMIT OFF DML exclusive lock 24 7 DML Oracle DML Oracle Oracle Oracle Oracle9i ROLLBACK SEGMENT Oracle9i UNDO SEGMENT DML Oracle 258

Oracle DML DML DELETE DML Oracle TRUNCATE DELETE TRUNCATE DDL DML 3 DML DML DML NULL DML DML Script UPDATE DELETE UPDATE DELETE WHERE Transactions Transactions COMMIT ROLLBACK Transactions Transactions AUTOCOMMIT Oracle 259

Oracle SQL / I/O Oracle Oracle Oracle Oracle I/O I/O I/O Oracle Oracle Oracle Oracle Oracle Oracle Oracle Oracle 1 Oracle PRIMARY KEY

UNIQUE Oracle UNIQUE INDEX 2 CREATE INDEX NONUNIQUE INDEX CREATE INDEX ON [ ] 13-1 DDL empcon 13-1 SQL> CREATE TABLE empcon 2 AS 3 SELECT * 4 FROM EMP; 13-1 13-2 SQL*PLUS empcon 13-2 SQL> DESC empcon 13-2? --------------------------------------- -------- ------------ EMPNO NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) empcon ename 13-3 DDL ename 13-3 261

SQL> CREATE INDEX empcon_ename_idx 2 ON empcon(ename); 13-3 13-3 Oracle aa x1 13-3 empcon_ename_idx Oracle user_indexes 13-4 13-5 SQL*PLUS SELECT 13-4 SQL> COL INDEX_TYPE FOR A10 13-5 SQL> COL TABLE_NAME FOR A10 13-6 SCOTT 13-6 SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS 2 FROM user_indexes; 13-6 INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES ------------------------------ ---------- ---------- --------- EMPCON_ENAME_IDX NORMAL EMPCON NONUNIQUE PK_DEPT NORMAL DEPT UNIQUE PK_EMP NORMAL EMP UNIQUE SYS_C002718 NORMAL E_M_SHELL UNIQUE 13-6 4 EMPCON_ENAME_ IDX EMPCON Oracle 262

SYS_C002718 E_M_SHELL Oracle EMPCON ENAME 13-6 user_ind_columns 13-7 SQL*PLUS SELECT 13-7 SQL> col column_name for a15 13-8 SCOTT 13-8 SQL> SELECT index_name, table_name, column_name, column_position 2 FROM user_ind_columns; 13-8 INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ ---------- --------------- -------- PK_DEPT DEPT DEPTNO 1 PK_EMP EMP EMPNO 1 EMPCON_ENAME_IDX EMPCON ENAME 1 SYS_C002718 E_M_SHELL E_ID 1 13-8 EMPCON_ENAME_IDX EMPCON ENAME PK_DEPT DEPT DEPTNO COLUMN_POSITION 13-9 DDL job sal 13-9 SQL> CREATE INDEX empcon_job_sal_idx 2 ON empcon(job,sal); 13-9 13-10 SCOTT 263

13-10 SQL> SELECT index_name, table_name, column_name, column_position 2 FROM user_ind_columns; 13-10 INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ ---------- ------------ --------------- PK_DEPT DEPT DEPTNO 1 PK_EMP EMP EMPNO 1 EMP_ENAME_IDX EMPCON ENAME 1 EMPCON_JOB_SAL_IDX EMPCON JOB 1 EMPCON_JOB_SAL_IDX EMPCON SAL 2 SYS_C002718 E_M_SHELL E_ID 1 6 13-10 EMPCON_JOB_SAL_IDX JOB COLUMN_POSITION 1 EMPCON_JOB_SAL_IDX SAL COLUMN_POSITION 2 COLUMN_POSITION DML DML Oracle DML Over Indexes DML DML DML Oracle Oracle SELECT WHERE Oracle SELECT WHERE Oracle Oracle9i SELECT Oracle 264

Oracle9i Oracle Oracle9i Oracle9i Oracle9i YES/NO Oracle9i Oracle Oracle age 18 60 65 18 20 WHERE DML NULL SELECT SELECT WHERE Oracle8i Oracle8i 2000 13-11 DDL sal-2000 13-11 SQL> CREATE INDEX empcon_salgt_idx 2 ON empcon(sal-2000); 13-11 ON empcon(sal-2000) * ERROR 2 : ORA-01031: 265

13-11 13-12 SQL*PLUS DBA 13-12 SQL> connect system/manager 13-12 13-11 SQL*PLUS 13-13 sal-2000 13-13 SQL> CREATE INDEX empcon_salgt_idx 2 ON scott.empcon(sal-2000); 13-13 13-13 13-14 13-16 SQL*PLUS SELECT 13-14 SQL> COL INDEX_TYPE FOR A10 13-15 SQL> COL INDEX_TYPE FOR A25 13-16 SQL> COL index_name for a20 13-17 13-17 SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS 2 FROM user_indexes 3 WHERE table_owner = 'SCOTT'; 13-17 INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES 266

-------------------- ----------------------- ---------- --------- EMPCON_SALGT_IDX FUNCTION-BASED NORMAL EMPCON NONUNIQUE 13-17 INDEX_TYPE FUNCTION-BASED NORMAL 13-18 13-18 SQL> SELECT index_name, table_name, column_name, column_position 2 FROM user_ind_columns 3 WHERE table_name = 'EMPCON'; 13-18 INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION -------------------- ---------- --------------- --------------- EMPCON_SALGT_IDX EMPCON SYS_NC00009$ 1 13-18 COLUMN_NAME SYS_NC00009$ Oracle sal-2000 Oracle Oracle Oracle SQL*PLUS EXPLAIN 13-19 utlxplan.sql plan_table 13-19 @d:\oracle\ora90\rdbms\admin\utlxplan 13-19 d:\oracle\ora90 Oracle $Oracle_HOME Oracle find 13-20 SQL*PLUS 13-20 SQL> EXPLAIN plan for 2 SELECT ename, job, sal, comm, deptno 3 FROM empcon 4 WHERE (sal-2000) < 0; 267

13-20 13-21 13-24 SQL*PLUS SELECT 13-21 SQL> col id for 999 13-22 SQL> col operation for a20 13-23 SQL> col options for a15 13-24 SQL> col object_name for a18 13-25 plan_table Oracle empcon_salgt_idx 13-25 SQL> SELECT id, operation, options, object_name, position 2 FROM plan_table; 13-25 ID OPERATION OPTIONS OBJECT_NAM POSITION --- -------------------- ---------- ---------- ---------- 1 TABLE ACCESS FULL EMPCON 1 0 SELECT STATEMENT 13-25 Oracle empcon_salgt_idx EMPCON Full Table Scan Oracle empcon_ ename_idx 13-26 DDL plan_table 13-26 SQL> truncate table plan_table; 13-26 268

13-27 SQL*PLUS 13-27 SQL> EXPLAIN plan for 2 SELECT empno, ename, job, sal 3 FROM empcon 4 WHERE ename LIKE 'J%'; 13-27 13-28 plan_table Oracle empcon_ename_idx 13-28 SQL> SELECT id, operation, options, object_name, position 2 FROM plan_table; 13-28 ID OPERATION OPTIONS OBJECT_NAME POSITION ---- -------------------- --------------- ------------------ ------ 0 SELECT STATEMENT 1 TABLE ACCESS BY INDEX ROWID EMPCON 1 2 INDEX RANGE SCAN EMPCON_ENAME_IDX 1 13-28 Oracle EMPCON_ENAME_IDX Oracle DROP INDEX DDL DROP ANY INDEX empcon_job_sal_idx 13-29 DDL 269

13-29 SQL> DROP INDEX EMPCON_JOB_SAL_IDX; 13-29 13-29 empcon_job_sal_idx 13-30 13-31 SQL*PLUS SELECT 13-30 SQL> COL INDEX_TYPE FOR A10 13-31 SQL> COL TABLE_NAME FOR A10 13-32 13-32 SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS 2 FROM user_indexes; 13-32 INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES -------------------------------- ------------ ----------- ------------ EMPCON_ENAME_IDX NORMAL EMPCON NONUNIQUE PK_DEPT NORMAL DEPT UNIQUE PK_EMP NORMAL EMP UNIQUE SYS_C002718 NORMAL E_M_SHELL UNIQUE 13-32 empcon_job_sal_idx DBA EMPCON EMPCON EMPCON_ ENAME_IDX 13-33 DBA 13-33 SQL> CONNECT SYSTEM/MANAGER 13-33 13-34 DDL SCOTT EMPCON_ 270

ENAME_IDX 13-34 SQL> DROP INDEX SCOTT.EMPCON_ENAME_IDX 13-34 13-32 13-35 SCOTT 13-35 SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS 2 FROM dba_indexes 3 WHERE owner = 'SCOTT'; 13-35 INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES ---------------------------- ---------- ---------- --------- PK_DEPT NORMAL DEPT UNIQUE PK_EMP NORMAL EMP UNIQUE SYS_C002718 NORMAL E_M_SHELL UNIQUE 13-35 EMPCON EMPCON_ ENAME_IDX SYSTEM 13-36 13-38 EMPCON 13-36 SQL> COL INDEX_NAME FOR A18 13-37 SQL> COL INDEX_TYPE FOR A22 13-38 SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS 2 FROM dba_indexes 3 WHERE table_name = 'EMPCON'; 13-38 INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES ------------------ ---------------------- ---------- --------- 271

EMPCON_SALGT_IDX FUNCTION-BASED NORMAL EMPCON NONUNIQUE 13-38 EMPCON EMPCON_SALGT_IDX DDL 13-39 SCOTT EMPCON 13-39 SQL> DROP TABLE scott.empcon; 13-39 13-38 13-40 EMPCON 13-40 SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS 2 FROM dba_indexes 3 WHERE table_name = 'EMPCON'; 13-40 13-40 EMPCON EMPCON DML DDL Oracle Oracle Oracle 5 NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY Child Table Parent Table Referential Integrity CHECK CREATE TABLE 272

ALTER TABLE Oracle Oracle SYS_Cn n 13-41 CREATE TABLE NOT NULL 13-41 SQL> CREATE TABLE deptcon( 2 deptno NUMBER(3), 3 dname VARCHAR2(15) NOT NULL, 4 loc VARCHAR2(20)); 13-41 13-42 SQL*PLUS deptcon dname NOT NULL 13-42 SQL> DESC deptcon 13-42? ---------------------------------- -------- ------------ DEPTNO NUMBER(3) DNAME NOT NULL VARCHAR2(15) LOC VARCHAR2(20) 13-42 deptcon dname NOT NULL Oracle 5 NOT NULL deptcon DML NOT NULL 13-43 DML deptcon 273

13-43 SQL> INSERT INTO deptcon(deptno, dname, loc) 2 VALUES (10, 'ACCOUNTING', 'BEIJING'); 13-43 1 13-43 deptcon 13-44 DML deptcon 13-44 SQL> INSERT INTO deptcon(deptno, dname, loc) 2 VALUES (20, '', 'GUANGZGOU'); 13-44 INSERT INTO deptcon(deptno, dname, loc) * ERROR 1 : 13-44 Oracle UNKNOWN Oracle 13-45 13-45 SQL> INSERT INTO deptcon(deptno, dname, loc) 2 VALUES (20, 'UNKNOWN', 'GUANGZGOU'); 13-45 1 13-46 deptcon 13-46 SQL> SELECT * 2 FROM deptcon; 13-46 DEPTNO DNAME LOC ---------- --------------- --------- 10 ACCOUNTING BEIJING 274

20 UNKNOWN GUANGZGOU INSERT UPDATE 13-47 DML 20 13-47 SQL> UPDATE deptcon 2 SET dname = NULL 3 WHERE deptno = 20; 13-47 UPDATE deptcon * ERROR 1 : ORA-01407: ("SCOTT"."DEPTCON"."DNAME") NULL 13-47 Oracle 13-47 13-48 UPDATE 20 ACCOUNTING Oracle 13-48 SQL> UPDATE deptcon 2 SET dname = 'ACCOUNTING' 3 WHERE deptno = 20; 13-48 1 13-48 deptcon 20 ACCOUNTING 13-49 deptcon 13-49 SQL> SELECT * 2 FROM deptcon; 13-49 DEPTNO DNAME LOC ---------- --------------- --------- 10 ACCOUNTING BEIJING 275

20 ACCOUNTING GUANGZGOU 13-49 deptcon ACCOUNTING Oracle DNAME deptcon UNIQUE DELETE NOT NULL NOT NULL SQL*PLUS DESC NOT NULL USER_CONSTRAINTS 13-50 13-51 SQL*PLUS 13-50 SQL> COL owner FOR A10 13-51 SQL> COL table_name FOR A10 13-52 SCOTT 13-52 SQL> SELECT owner, constraint_name, constraint_type, table_name 2 FROM user_constraints; 13-52 OWNER CONSTRAINT_NAME C TABLE_NAME ---------- ------------------------------ - ---------- SCOTT PK_DEPT P DEPT SCOTT SYS_C002719 C DEPTCON SCOTT PK_EMP P EMP SCOTT FK_DEPTNO R EMP SCOTT SYS_C002718 P E_M_SHELL SCOTT SYS_C002715 C MANAGER SCOTT SYS_C002716 C MANAGER 276

SCOTT SYS_C002717 C MANAGER 8 13-52 deptcon C Oracle SYS_C002719 13-52 C constraint_type C CHECK NOT NULL P PRIMARY KEY R REFERENTIAL INTEGRITY FOREIGN KEY U UNIQUE USER_CONS_COLUMNS 13-54 SCOTT 13-53 SQL> COL column_name for a15 13-54 SQL> SELECT owner, constraint_name, table_name, column_name 2 FROM user_cons_columns; 13-54 OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME ---------- ------------------------------ ------- ----------- SCOTT FK_DEPTNO EMP DEPTNO SCOTT PK_DEPT DEPT DEPTNO SCOTT PK_EMP EMP EMPNO SCOTT SYS_C002715 MANAGER EMPNO SCOTT SYS_C002716 MANAGER ENAME SCOTT SYS_C002717 MANAGER HIREDATE SCOTT SYS_C002718 E_M_SHELL E_ID SCOTT SYS_C002719 DEPTCON DNAME 8 13-54 deptcon DNAME Oracle SYS_C002719 277

DNAME UNIQUE 13-55 DDL deptcon DNAME NOT NULL 13-55 SQL> ALTER TABLE deptcon 2 DROP CONSTRAINT SYS_C002719; 13-55 13-54 13-56 13-56 SQL> SELECT owner, constraint_name, table_name, column_name 2 FROM user_cons_columns; 13-56 OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME --------- ---------------------------- --------- ----------- SCOTT FK_DEPTNO EMP DEPTNO SCOTT PK_DEPT DEPT DEPTNO SCOTT PK_EMP EMP EMPNO SCOTT SYS_C002715 MANAGER EMPNO SCOTT SYS_C002716 MANAGER ENAME SCOTT SYS_C002717 MANAGER HIREDATE SCOTT SYS_C002718 E_M_SHELL E_ID 7 13-56 deptcon 13-57 DDL deptcon dname UNIQUE 13-57 SQL> ALTER TABLE deptcon 2 ADD CONSTRAINT deptcon_dname_uk UNIQUE(dname); 13-57 ADD CONSTRAINT deptcon_dname_uk UNIQUE(dname) 278

* ERROR 2 : ORA-02299: (SCOTT.DEPTCON_DNAME_UK) - 13-57 13-58 13-58 SQL> SELECT * FROM DEPTCON 13-58 DEPTNO DNAME LOC --------- ------------- --------- 10 ACCOUNTING BEIJING 20 ACCOUNTING GUANGZGOU 13-58 deptcon ACCOUNTING UNIQUE 13-59 DML ACCOUNTING 13-59 SQL> DELETE FROM deptcon 2 WHERE deptno = 20; 13-59 1 13-59 1 13-57 DDL 13-60 deptcon dname UNIQUE 13-60 SQL> ALTER TABLE deptcon 2 ADD CONSTRAINT deptcon_dname_uk UNIQUE(dname); 13-60 13-60 deptcon dname UNIQUE 13-61 13-63 13-61 SQL> COL SEARCH_CONDITION FOR A23 279

13-62 SQL> COL CONSTRAINT_NAME FOR A20 13-63 SQL> SELECT owner, constraint_name, constraint_type, table_name, 2 search_condition 3 FROM user_constraints; 13-63 OWNER CONSTRAINT_NAME C TABLE_NAME SEARCH_CONDITION ----------- --------------------- - --------- ------------------ SCOTT PK_DEPT P DEPT SCOTT DEPTCON_DNAME_UK U DEPTCON SCOTT PK_EMP P EMP SCOTT FK_DEPTNO R EMP SCOTT SYS_C002718 P E_M_SHELL SCOTT SYS_C002715 C MANAGER "EMPNO" IS NOT NULL SCOTT SYS_C002716 C MANAGER "ENAME" IS NOT NULL SCOTT SYS_C002717 C MANAGER "HIREDATE" IS NOT NULL 8 13-63 deptcon UNIQUE DEPTCON_ DNAME_UK DEPTCON_DNAME_UK DEPTCON DNAME UNIQUE Oracle 3 _ UK UNIQUE KEY PK PRIMARY KEY FK FOREIGN KEY CK CHECK NN NOT NULL 13-57 UNIQUE UNIQUE KEY NULL 13-64 DML deptcon 13-64 SQL> INSERT INTO deptcon(deptno, dname, loc) 2 VALUES (20, NULL, ' '); 280

13-64 1 13-64 UNIQUE KEY NULL UNIQUE KEY NULL 13-64 13-65 DML deptcon 13-65 SQL> INSERT INTO deptcon(deptno, dname, loc) 2 VALUES (20, NULL, ' '); 13-65 1 13-65 UNIQUE KEY NULL UNIQUE KEY NULL NULL NULL NULL NULL 5 N UPDATE UNIQUE KEY NOT NULL DELETE UNIQUE KEY CHECK CHECK CHECK CURRVAL NEXTVAL LEVEL ROWNUM PSEUDOCOLUMNS SYSDATE USER USERENV UID CHECK CHECK 281

18~35 13-66 DDL 13-66 SQL> CREATE TABLE person( 2 id VARCHAR2(10), 3 name VARCHAR2(20), 4 gender CHAR(1), 5 age NUMBER, 6 CONSTRAINT person_gender_ck 7 CHECK(gender = 'F'), 8 CONSTRAINT person_age_ck 9 CHECK(age BETWEEN 18 AND 35)); 13-66 13-66 Oracle DBA 13-67 SQL*PLUS person 13-67 SQL> DESC person 13-67? -------------------------------------------- -------- ------------ ID VARCHAR2(10) NAME VARCHAR2(20) GENDER CHAR(1) AGE NUMBER 13-66 13-72 person CHECK 13-68 13-71 282

13-68 SQL> COL owner FOR A8 13-69 SQL> COL CONSTRAINT_NAME FOR A20 13-70 SQL> COL TABLE_NAME FOR A10 13-71 SQL> COL SEARCH_CONDITION FOR A25 13-72 SQL> SELECT owner, constraint_name, constraint_type, table_name, 2 search_condition 3 FROM user_constraints 4 WHERE table_name = 'PERSON'; 13-72 OWNER CONSTRAINT_NAME C TABLE_NAME SEARCH_CONDITION -------- -------------------- - ---------- --------------------- SCOTT PERSON_GENDER_CK C PERSON gender = 'F' SCOTT PERSON_AGE_CK C PERSON age BETWEEN 18 AND 35 13-72 SCOTT PERSON PERSON_GENDER_CK CHECK gender = 'F' PERSON_AGE_CK CHECK age BETWEEN 18 AND 35 person CHECK 13-73 DML person 13-73 SQL> INSERT INTO person(id, name, gender, age) 2 VALUES (1001, ' ','F', 22); 13-73 1 13-73 person 283

20 13-74 DML person 13-74 SQL> INSERT INTO person(id, name, gender, age) 2 VALUES (1002, ' ', 'M', 19); 13-74 INSERT INTO person(id, name, gender, age) * ERROR 1 : ORA-02290: (SCOTT.PERSON_GENDER_CK) 13-74 Oracle INSERT Oracle INSERT 13-75 INSERT person Oracle 13-75 SQL> INSERT INTO person(id, name, gender, age) 2 VALUES (1001, ' ', 'F', 36); 13-75 INSERT INTO person(id, name, gender, age) * ERROR 1 : ORA-02290: (SCOTT.PERSON_AGE_CK) 13-75 Oracle INSERT 35 13-76 INSERT person Oracle 13-76 SQL> INSERT INTO person(id, name, gender, age) 2 VALUES (1001, ' ', 'F', 17); 13-76 284

INSERT INTO person(id, name, gender, age) * ERROR 1 : ORA-02290: (SCOTT.PERSON_AGE_CK) 13-76 Oracle INSERT 18 UPDATE CHECK NOT NULL UNIQUE KEY DELETE CHECK PRIMARY KEY PRIMARY KEY Oracle PRIMARY KEY deptcon 13-77 deptcon 13-77 SQL> select * from deptcon; 13-77 DEPTNO DNAME LOC ---------- --------------- ------ 10 ACCOUNTING EIJING 20 20 13-78 DDL deptcon deptno PRIMARY KEY 13-78 SQL> ALTER TABLE deptcon 2 ADD CONSTRAINT deptcon_deptno_pk 3 PRIMARY KEY (deptno); 13-78 ADD CONSTRAINT deptcon_deptno_pk 285

* ERROR 2 : ORA-02437: (SCOTT.DEPTCON_DEPTNO_PK) - 13-78 deptcon PRIMARY KEY 13-77 deptcon 30 20 13-79 DML 30 13-79 SQL> UPDATE deptcon 2 SET deptno = 30 3 WHERE loc = ' '; 13-79 1 13-79 1 13-78 13-80 DDL deptcon deptno PRIMARY KEY 13-80 SQL> ALTER TABLE deptcon 2 ADD CONSTRAINT deptcon_deptno_pk 3 PRIMARY KEY (deptno); 13-80 13-80 deptcon deptno PRIMARY KEY 13-81 13-83 deptcon 13-81 SQL> SELECT owner, constraint_name, constraint_type, table_name, 2 search_condition 3 FROM user_constraints 4 WHERE table_name = 'DEPTCON'; 13-81 OWNER CONSTRAINT_NAME C TABLE_NAME SEARCH_CONDITION -------- -------------------- - ---------- -------------- SCOTT DEPTCON_DEPTNO_PK P DEPTCON 286

SCOTT DEPTCON_DNAME_UK U DEPTCON 13-82 SQL> COL COLUMN_NAME FOR A10 13-83 SQL> SELECT owner, constraint_name, table_name, column_name, position 2 FROM user_cons_columns 3 WHERE table_name = 'DEPTCON'; 13-83 OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAM POSITION -------- -------------------- --------- ---------- --------- SCOTT DEPTCON_DEPTNO_PK DEPTCON DEPTNO 1 SCOTT DEPTCON_DNAME_UK DEPTCON DNAME 1 13-81 13-83 SCOTT DEPTCON DEPTCON_DEPTNO_PK PRIMARY KEY DEPTNO DEPTCON_DNAME_UK UNIQUE DNAME 13-78 PRIMARY KEY PRIMARY KEY NULL 13-84 DML deptcon 13-84 SQL> INSERT INTO deptcon(deptno, dname, loc) 2 VALUES (NULL, ' ', ' '); 13-84 INSERT INTO deptcon(deptno, dname, loc) * ERROR 1 : ORA-01400: NULL ("SCOTT"."DEPTCON"."DEPTNO") 13-84 PRIMARY KEY NULL UNIQUE deptno 13-84 13-85 DML deptcon 13-85 SQL> INSERT INTO deptcon(deptno, dname, loc) 287

2 VALUES (88, ' ', ' '); 13-85 1 13-86 deptcon 13-86 SQL> SELECT * 2 FROM deptcon; 13-86 DEPTNO DNAME LOC ---------- --------------- ------- 10 ACCOUNTING BEIJING 88 20 30 2 13.2 PRIMARY KEY UNIQUE Oracle UNIQUE INDEX 13-87 13-92 13-87 SQL> COL INDEX_TYPE FOR A10 13-88 SQL> COL INDEX_TYPE FOR A25 13-89 SQL> COL index_name for a20 13-90 SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS 2 FROM user_indexes 3 WHERE table_name = 'DEPTCON'; 13-90 INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES -------------------- ------------------------- ---------- ----- DEPTCON_DEPTNO_PK NORMAL DEPTCON UNIQUE 288

DEPTCON_DNAME_UK NORMAL DEPTCON UNIQUE 13-91 SQL> col column_name for a15 13-92 SQL> SELECT index_name, table_name, column_name, column_position 2 FROM user_ind_columns 3 WHERE table_name = 'DEPTCON'; 13-92 INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION -------------------- ---------- --------------- ------------- DEPTCON_DNAME_UK DEPTCON DNAME 1 DEPTCON_DEPTNO_PK DEPTCON DEPTNO 1 13-90 13-92 DEPTCON UNIQUE INDEX UPDATE NOT NULL UNIQUE KEY CHECK DELETE PRIMARY KEY DELETE Entity Integrity FOREIGN KEY Child Table Parent Table Referential Integrity FOREIGN KEY FOREIGN KEY FOREIGN KEY FOREIGN KEY 13-93 DDL empcon empcon deptcon 13-93 SQL> CREATE TABLE empcon 2 AS 3 SELECT * 4 FROM emp_dml; 13-93 289

13-94 DDL empcon deptno FOREIGN KEY 13-94 SQL> ALTER TABLE empcon 2 ADD CONSTRAINT empcon_deptno_fk 3 FOREIGN KEY(deptno) REFERENCES deptcon(deptno); 13-94 ADD CONSTRAINT empcon_deptno_fk * ERROR 2 : ORA-02298: (SCOTT.EMPCON_DEPTNO_FK) - 13-94 empcon Referential Integrity 13-95 empcon deptno 13-95 SQL> SELECT DISTINCT deptno 2 FROM empcon; 13-95 DEPTNO ---------- 10 20 30 88 13-96 deptcon deptno 13-96 SQL> SELECT * 2 FROM deptcon; 13-96 DEPTNO DNAME LOC ---------- --------------- ------- 10 ACCOUNTING BEIJING 290

20 30 13-95 13-96 empcon deptno 88 deptcon empcon deptno 88 Referential Integrity Referential Integrity 7 13-97 DML deptcon deptno 88 13-97 SQL> INSERT INTO deptcon 2 VALUES (88, ' ', ' '); 13-97 1 13-97 1 13-94 13-98 DDL empcon deptno FOREIGN KEY 13-98 SQL> ALTER TABLE empcon 2 ADD CONSTRAINT empcon_deptno_fk 3 FOREIGN KEY(deptno) REFERENCES deptcon(deptno); 13-98 13-98 13-103 13-105 empcon 13-99 SQL> COL owner FOR A8 13-100 SQL> COL CONSTRAINT_NAME FOR A20 13-101 SQL> COL TABLE_NAME FOR A10 13-102 291

SQL> COL SEARCH_CONDITION FOR A25 13-103 SQL> SELECT owner, constraint_name, constraint_type, table_name, 2 r_constraint_name 3 FROM user_constraints 4 WHERE table_name = 'EMPCON'; 13-103 OWNER CONSTRAINT_NAME C TABLE_NAME R_CONSTRAINT_NAME -------- -------------------- - ---------- --------------- SCOTT EMPCON_DEPTNO_FK R EMPCON DEPTCON_DEPTNO_PK 13-104 SQL> COL COLUMN_NAME FOR A10 13-105 SQL> SELECT owner, constraint_name, table_name, column_name, position 2 FROM user_cons_columns 3 WHERE table_name = 'EMPCON'; 13-105 OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAM POSITION -------- -------------------- ---------- ---------- ---------- SCOTT EMPCON_DEPTNO_FK EMPCON DEPTNO 1 13-103 13-105 SCOTT EMPCON FOREIGN KEY EMPCON_DEPTNO_FK DEPTNO DEPTCON_DEPTNO_PK DEPTCON_ DEPTNO_PK DEPTCON DEPTNO PRIMARY KEY EMPCON DEPTNO FOREIGN KEY FOREIGN KEY INSERT 13-106 INSERT empcon Referential Integrity 13-106 292

SQL> INSERT INTO empcon(empno, ename, mgr, hiredate, sal, comm, deptno) 2 VALUES (1010, ' ', 7839, SYSDATE, 5000, 1500, 10); 13-106 1 13-106 Referential Integrity 13-107 INSERT empcon Referential Integrity 13-107 SQL> INSERT INTO empcon(empno, ename, mgr, hiredate, sal, comm, deptno) 2 VALUES (1010, ' ', 7839, SYSDATE, 5000, 1500, 15); 13-107 INSERT INTO empcon(empno, ename, mgr, hiredate, sal, comm, deptno) * ERROR 1 : ORA-02291: (SCOTT.EMPCON_DEPTNO_FK) - 13-107 deptcon 15 deptcon deptno 15 10 10 13-108 INSERT empcon Referential Integrity 13-108 SQL> INSERT INTO empcon(empno, ename, mgr, hiredate, sal, comm, deptno) 2 VALUES (1010, ' ', 7839, SYSDATE, 5000, 1500, 10); 13-108 1 13-108 1 13-109 empcon 13-109 SQL> SELECT empno, ename, mgr, hiredate, sal, comm, deptno 2 FROM empcon 3 WHERE sal >= 5000; 293

13-109 EMPNO ENAME MGR HIREDATE SAL COMM DEPTNO ------ --------- ----- --------- --------- -------- --------- 7839 KING 17-11 -81 5000 10 1010 7839 13-2 -03 5000 1500 10 1010 7839 13-2 -03 5000 1500 10 13-109 empcon deptcon Referential Integrity deptcon PARENT TABLE CHILD TABLE Referential Integrity PARENT TABLE FOREIGN KEY DELETE 13.12 13-110 DELETE deptcon 13-110 SQL> DELETE FROM deptcon 2 WHERE deptno = '88'; 13-110 DELETE FROM deptcon * ERROR 1 : ORA-02292: (SCOTT.EMPCON_DEPTNO_FK) - 13-110 empcon 88 empcon deptno 88 Referential Integrity empcon Referential Integrity 294

Oracle FOREIGN KEY Parent Table DELETE Oracle Child Table PARENT TABLE Referential Integrity CHILD TABLE FOREIGN KEY UPDATE CEO 88 44 13-111 UPDATE deptcon deptno 88 44 13-111 SQL> UPDATE deptcon 2 SET deptno = 44 3 WHERE deptno = 88; 13-111 UPDATE deptcon * ERROR 1 : ORA-02292: (SCOTT.EMPCON_DEPTNO_FK) - 13-111 13-112 empcon 88 13-112 SQL> SELECT ename, ename, job, sal, deptno 295

2 FROM empcon 3 WHERE deptno = 88; 13-112 ENAME ENAME JOB SAL DEPTNO --------- -------- -------- --------- -------- MANAGER 800 88 CTO 800 88 MANAGER 800 88 MANAGER 800 88 13-112 empcon 4 88 13-111 UPDATE Referential Integrity empcon 13-113 UPDATE empcon 88 deptno 44 13-113 SQL> UPDATE empcon 2 SET deptno = 44 3 WHERE deptno = 88; 13-113 UPDATE empcon * ERROR 1 : ORA-02291: (SCOTT.EMPCON_DEPTNO_FK) - 13-113 empcon Referential Integrity deptcon 44 UPDATE empcon 88 deptcon CEO 13-114 UPDATE empcon 88 NULL 13-114 SQL> UPDATE empcon 2 SET deptno = NULL 3 WHERE deptno = 88; 13-114 296

4 13-115 UPDATE deptcon 88 44 13-115 SQL> UPDATE deptcon 2 SET deptno = 44 3 WHERE deptno = 88; 13-115 1 13-116 UPDATE empcon NULL 44 13-116 SQL> UPDATE empcon 2 SET deptno = 44 3 WHERE deptno IS NULL; 13-116 4 13-116 4 13-117 13-118 deptcon empcon 88 44 13-117 SQL> SELECT * 2 FROM deptcon; 13-117 DEPTNO DNAME LOC ---------- -------------- ----- 10 ACCOUNTING BEIJING 44 20 30 13-118 SQL> SELECT ename, ename, job, sal, deptno 2 FROM empcon 3 WHERE deptno = 44; 297

13-118 ENAME ENAME JOB SAL DEPTNO ---------- ---------- --------- --------- -------- MANAGER 800 44 CTO 800 44 MANAGER 800 44 MANAGER 800 44 13-117 13-118 Oracle PARENT TABLE CHILD TABLE Referential Integrity FOREIGN KEY DML DDL 13-119 DDL deptcon 13-119 SQL> DROP TABLE deptcon; 13-119 DROP TABLE deptcon * ERROR 1 : ORA-02449: / 13-119 DROP TABLE / deptno empcon FOREIGN KEY Oracle Referential Integrity empcon deptcon deptcon 298

DROP TABLE TRUNCATE TABLE 13-120 DDL deptcon 13-121 deptcon deptno 13-120 SQL> TRUNCATE TABLE deptcon; 13-120 TRUNCATE TABLE deptcon * ERROR 1 : ORA-02266: / 13-121 SQL> ALTER TABLE deptcon 2 DROP COLUMN deptno; 13-121 DROP COLUMN deptno * ERROR 2 : ORA-12992: deptcon deptno deptcon PARENT TABLE Referential Integrity CHILD TABLE FOREIGN KEY FOREIGN KEY Oracle DML DDL Oracle Referential Integrity Referential Integrity Oracle Parent Table Child Table Oracle ON DELETE SET NULL ON DELETE CASCADE 299

ON DELETE SET NULL Parent Table Oracle Child Table FOREIGN KEY NULL Oracle empcon FOREIGN KEY ON DELETE SET NULL 13-122 DDL empcon deptno FOREIGN KEY 13-122 SQL> ALTER TABLE empcon 2 DROP CONSTRAINT empcon_deptno_fk; 13-122 13-123 DDL empcon ON DELETE SET NULL FOREIGN KEY 13-123 SQL> ALTER TABLE empcon 2 ADD CONSTRAINT empcon_deptno_fk 3 FOREIGN KEY(deptno) REFERENCES deptcon (deptno) 4 ON DELETE SET NULL; 13-123 CEO Oracle 13-123 empcon ON DELETE SET NULL 13-124 13-125 deptcon 44 empcon 44 13-124 SQL> SELECT * 300

2 FROM deptcon 3 ORDER BY deptno; 13-124 DEPTNO DNAME LOC ---------- --------------- ------- 10 ACCOUNTING BEIJING 20 30 44 13-125 SQL> SELECT empno, ename, job, sal, deptno 2 FROM empcon 3 WHERE deptno > 20 4 ORDER BY deptno; 13-125 EMPNO ENAME JOB SAL DEPTNO ---------- ---------- --------- ---------- ------- 7499 ALLEN SALESMAN 1600 30 7521 WARD SALESMAN 1250 30 7654 MARTIN SALESMAN 1250 30 7698 BLAKE MANAGER 2850 30 7844 TURNER SALESMAN 1500 30 7900 JAMES CLERK 950 30 7800 MANAGER 800 44 7810 CTO 800 44 1001 MANAGER 800 44 1002 MANAGER 800 44 10 deptcon empcon 13-126 DML 13-126 SQL> DELETE FROM deptcon 2 WHERE deptno = 44; 13-126 1 13-127 deptcon 44 301

13-127 SQL> SELECT * 2 FROM deptcon; 13-127 DEPTNO DNAME LOC ---------- --------------- ------- 10 ACCOUNTING BEIJING 20 30 13-127 44 13-125 13-128 Oracle empcon 13-128 SQL> SELECT empno, ename, job, sal, deptno 2 FROM empcon 3 WHERE deptno > 20 4 ORDER BY deptno; 13-128 EMPNO ENAME JOB SAL DEPTNO ---------- ---------- --------- ---------- ------ 7499 ALLEN SALESMAN 1600 30 7521 WARD SALESMAN 1250 30 7654 MARTIN SALESMAN 1250 30 7698 BLAKE MANAGER 2850 30 7844 TURNER SALESMAN 1500 30 7900 JAMES CLERK 950 30 6 13-128 44 Oracle Oracle NULL 13-128 13-129 OR deptno IS NULL Oracle empcon 13-129 302

SQL> SELECT empno, ename, sal, job, deptno 2 FROM empcon 3 WHERE deptno > 20 4 OR deptno IS NULL; 13-129 EMPNO ENAME SAL JOB DEPTNO ---------- ---------- ---------- --------- -------- 7499 ALLEN 1600 SALESMAN 30 7521 WARD 1250 SALESMAN 30 7654 MARTIN 1250 SALESMAN 30 7698 BLAKE 2850 MANAGER 30 7844 TURNER 1500 SALESMAN 30 7900 JAMES 950 CLERK 30 7800 800 MANAGER 7810 800 CTO 1001 800 MANAGER 1002 800 MANAGER 10 13-130 DML 13-130 SQL> rollback 13-130 13-131 DDL empcon deptno 13-131 SQL> ALTER TABLE empcon 2 DROP CONSTRAINT empcon_deptno_fk; 13-131 13-132 DDL empcon deptno ON DELETE CASCADE 13-132 SQL> ALTER TABLE empcon 2 ADD CONSTRAINT empcon_deptno_fk 303

3 FOREIGN KEY(deptno) REFERENCES deptcon (deptno) 4 ON DELETE CASCADE; 13-132 13-133 DML 44 13-133 SQL> DELETE FROM deptcon 2 WHERE deptno = 44; 13-133 1 13-134 deptcon 44 13-134 SQL> SELECT * 2 FROM deptcon; 13-134 DEPTNO DNAME LOC ---------- --------------- ------- 10 ACCOUNTING BEIJING 20 30 13-134 44 13-135 Oracle empcon 13-135 SQL> SELECT empno, ename, sal, job, deptno 2 FROM empcon; 13-135 EMPNO ENAME SAL JOB DEPTNO ---------- --------- ---------- -------- --------- 7369 SMITH 800 CLERK 20 7499 ALLEN 1600 SALESMAN 30 7521 WARD 1250 SALESMAN 30 7566 JONES 2975 MANAGER 20 7654 MARTIN 1250 SALESMAN 30 304

7698 BLAKE 2850 MANAGER 30 7782 CLARK 2450 MANAGER 10 7788 SCOTT 3000 ANALYST 20 7839 KING 5000 PRESIDENT 10 7844 TURNER 1500 SALESMAN 30 7876 ADAMS 1100 CLERK 20 7900 JAMES 950 CLERK 30 7902 FORD 3000 ANALYST 20 7934 MILLER 1300 CLERK 10 1010 5000 10 1010 5000 10 16 13-135 Oracle empcon 44 ON DELETE CASCADE ON DELETE CASCADE Parent Table Child Table ON DELETE SET NULL ON DELETE CASCADE ON DELETE SET NULL ON DELETE CASCADE PARENT TABLE CHILD TABLE Referential Integrity 305

Oracle Oracle ALTER TABLE DISABLE CONSTRAINT [CASCADE] CASCADE DISABLE CREATE TABLE ALTER TABLE Parent Table 13-136 13-137 STATUS 13-136 SQL> SELECT owner, constraint_name, constraint_type, table_name, 2 r_constraint_name, status 3 FROM user_constraints 4 WHERE table_name = 'EMPCON'; 13-136 OWNER CONSTRAINT_NAME C TABLE_NAME R_CONSTRAINT_NAME STATUS -------- ------------------- - --------- ------------------- ------- SCOTT EMPCON_DEPTNO_FK R EMPCON DEPTCON_DEPTNO_PK ENABLED 13-137 SQL> SELECT owner, constraint_name, constraint_type, table_name, 2 r_constraint_name, status 3 FROM user_constraints 4 WHERE table_name = 'DEPTCON'; 13-137 OWNER CONSTRAINT_NAME C TABLE_NAME R_CONSTRAINT_NAME STATUS -------- ----------------- - ---------- ------------------- ------- SCOTT DEPTCON_DEPTNO_PK P DEPTCON ENABLED SCOTT DEPTCON_DNAME_UK U DEPTCON ENABLED 306

13-136 empcon EMPCON_DEPTNO_FK STATUS ENABLED 13-137 eptcon DEPTCON_DEPTNO_PK STATUS ENABLED 13-138 DDL DEPTCON_DEPTNO_PK 13-138 SQL> ALTER TABLE deptcon 2 DISABLE CONSTRAINT deptcon_deptno_pk; 13-138 ALTER TABLE deptcon * ERROR 1 : ORA-02297: (SCOTT.DEPTCON_DEPTNO_PK) - Oracle DDL 13-138 Child Table DEPTCON_DEPTNO_PK CASCADE 13-139 CASCADE DDL DEPTCON_DEPTNO_PK 13-139 SQL> ALTER TABLE deptcon 2 DISABLE CONSTRAINT deptcon_deptno_pk CASCADE; 13-139 Oracle 13-140 13-141 STATUS 13-140 SQL> SELECT owner, constraint_name, constraint_type, table_name, 2 r_constraint_name, status 3 FROM user_constraints 4 WHERE table_name = 'DEPTCON'; 13-140 OWNER CONSTRAINT_NAME C TABLE_NAME R_CONSTRAINT_NAME STATUS -------- ---------------- - ---------- -------------------- -------- SCOTT DEPTCON_DEPTNO_PK P DEPTCON DISABLED SCOTT DEPTCON_DNAME_UK U DEPTCON ENABLED 307

13-141 SQL> SELECT owner, constraint_name, constraint_type, table_name, 2 r_constraint_name, status 3 FROM user_constraints 4 WHERE table_name = 'EMPCON'; 13-141 OWNER CONSTRAINT_NAME C TABLE_NAME R_CONSTRAINT_NAME STATUS -------- ------------------- - ---------- ----------------- -------- SCOTT EMPCON_DEPTNO_FK R EMPCON DEPTCON_DEPTNO_PK DISABLED 13-141 empcon EMPCON_DEPTNO_FK STATUS DISABLED 13-140 deptcon DEPTCON_ DEPTNO_PK STATUS DISABLED ALTER TABLE ENABLE CONSTRAINT ENABLE CREATE TABLE ALTER TABLE UNIQUE KEY PRIMARY KEY Oracle UNIQUE KEY PRIMARY KEY INDEX 13-142 DDL ENABLED DEPTCON_DEPTNO_PK Oracle 13-142 SQL> ALTER TABLE deptcon 2 ENABLE CONSTRAINT deptcon_deptno_pk; 13-142 Oracle 13-143 13-144 STATUS 13-143 SQL> SELECT owner, constraint_name, constraint_type, table_name, 308

2 r_constraint_name, status 3 FROM user_constraints 4 WHERE table_name = 'DEPTCON'; 13-143 OWNER CONSTRAINT_NAME C TABLE_NAME R_CONSTRAINT_NAME STATUS -------- ----------------- - --------- -------------------- ------- SCOTT DEPTCON_DEPTNO_PK P DEPTCON ENABLED SCOTT DEPTCON_DNAME_UK U DEPTCON ENABLED 13-144 SQL> SELECT owner, constraint_name, constraint_type, table_name, 2 r_constraint_name, status 3 FROM user_constraints 4 WHERE table_name = 'EMPCON'; 13-144 OWNER CONSTRAINT_NAME C TABLE_NAME R_CONSTRAINT_NAME STATUS -------- ----------------- - --------- -------------------- -------- SCOTT EMPCON_DEPTNO_FK R EMPCON DEPTCON_DEPTNO_PK DISABLED 13-143 13-144 deptcon deptno Primary Key ENABLED empcon deptno Forein Key DISABLED ENABLING Primary Key CASCADE DISABLED Oracle ENABLING Primary Key Forein Key 13-145 DDL ENABLING Forein Key EMPCON_DEPTNO_FK 13-145 SQL> ALTER TABLE empcon 2 ENABLE CONSTRAINT empcon_deptno_fk; 13-145 13-146 Forein Key EMPCON_DEPTNO_FK STATUS 13-146 SQL> SELECT owner, constraint_name, constraint_type, table_name, 309

2 r_constraint_name, status 3 FROM user_constraints 4 WHERE table_name = 'EMPCON'; 13-146 OWNER CONSTRAINT_NAME C TABLE_NAME R_CONSTRAINT_NAME STATUS -------- ------------------- - -------- -------------------- ------- SCOTT EMPCON_DEPTNO_FK R EMPCON DEPTCON_DEPTNO_PK ENABLED 13-146 empcon deptno Forein Key ENABLED Oracle ALTER TABLE DROP CONSTRAINT [CASCADE] 13-147 DDL DEPTCON_DEPTNO_PK 13-147 SQL> ALTER TABLE deptcon 2 DROP CONSTRAINT deptcon_deptno_pk; 13-147 DROP CONSTRAINT deptcon_deptno_pk * ERROR 2 : ORA-02273: / Oracle DDL 13-147 Child Table DEPTCON_DEPTNO_PK CASCADE 13-148 CASCADE DDL DEPTCON_DEPTNO_PK 13-148 SQL> ALTER TABLE deptcon 2 DROP CONSTRAINT deptcon_deptno_pk CASCADE; 13-148 310

Oracle 13-148 13-149 13-150 13-149 SQL> SELECT owner, constraint_name, constraint_type, table_name, 2 r_constraint_name, status 3 FROM user_constraints 4 WHERE table_name = 'DEPTCON'; 13-149 OWNER CONSTRAINT_NAME C TABLE_NAME R_CONSTRAINT_NAME STATUS -------- ------------------- - --------- ------------------ ------- SCOTT DEPTCON_DNAME_UK U DEPTCON ENABLED 13-150 SQL> SELECT owner, constraint_name, constraint_type, table_name, 2 r_constraint_name, status 3 FROM user_constraints 4 WHERE table_name = 'EMPCON'; 13-150 13-149 13-150 Oracle 13-109 EMPNO 1010 EMPNO PRIMARY KEY DML DDL 1 2 Oracle Oracle 3 Oracle 311

Oracle Oracle Oracle 5 Oracle DML DDL Entity Integrity Referential Integrity Oracle Oracle Oracle Oracle Primary key Forein Key Oracle Primary key Forein Key Entity Integrity Referential Integrity Oracle user_indexes user_ind_columns 5 INSERT Oracle DELETE Oracle UPDATE Oracle 312

DDL Oracle ON DELETE SET NULL ON DELETE CASCADE user_constraints user_cons_columns 313

/ Normalization SQL 14.1

Model1 Model 2 Model 3 Model n View1 View2 View3 Viewn Table1 Table2 Table3 Table4 Tablen 14.1 14.1 14-1 14-1 SQL> SELECT d.dname, AVG(e.sal), AVG(NVL(comm,0)), COUNT(*) 2 FROM emp e, dept d 3 WHERE e.deptno = d.deptno 4 GROUP BY d.dname; 14-1 DNAME AVG(E.SAL) AVG(NVL(COMM,0)) COUNT(*) -------------- ---------- -------------- ---------- ACCOUNTING 2916.66667 0 3 315

RESEARCH 2175 0 5 SALES 1566.66667 366.666667 6 14-1 Oracle Oracle SQL 14-1 14-2 14-2 SQL> CREATE VIEW average 2 AS 3 SELECT d.dname " ", AVG(e.sal) " ", 4 AVG(NVL(comm,0)) " ", COUNT(*) " " 5 FROM emp e, dept d 6 WHERE e.deptno = d.deptno 7 GROUP BY d.dname; 14-2 14-2 Oracle 14-3 SQL*PLUS 14-3 SQL> DESC average 14-3? ----------------------------------------- -------- ----- VARCHAR2(14) NUMBER NUMBER NUMBER 316

14-3 SQL 14-4 14-4 SQL> SELECT * 2 FROM average; 14-4 -------------- ---------- --------- ----------- ACCOUNTING 2916.66667 0 3 RESEARCH 2175 0 5 SALES 1566.66667 366.666667 6 SQL Oracle Oracle Oracle SQL 14-2 SQL Group Functions GROUP BY 14-4 average average 4 14.1 / 14.2 CREATE [OR REPLACE] [FORCE NOFORCE] VIEW [( [ ] )] AS 317

[WITH CHECK OPTION [CONSTRAINT ]] [WITH READ ONLY] OR REPLACE Oracle OR REPLACE SQL*PLUS DESC FORCE Oracle NOFORCE Oracle ORACL WITH CHECK OPTION WITH CHECK OPTION WITH READ ONLY DML ORDER BY ORDER BY CREATE OR REPLACE VIEW Oracle Oracle 14-5 SQL acct 4 14-5 SQL> CREATE OR REPLACE VIEW acct 2 (" ", " ", " ", " ") 3 AS 4 SELECT ename, sal, job, hiredate 5 FROM emp 318

6 WHERE deptno = 10; 14-5 14-6 SQL*PLUS 14-6 SQL> DESC acct 14-6? ----------------------------------- -------- ------------ VARCHAR2(10) NUMBER(7,2) VARCHAR2(9) DATE 14-6 14-7 14-7 SQL> SELECT * 2 FROM acct; 14-7 ---------- ---------- --------- ----------- CLARK 2450 MANAGER 09-6 81 KING 5000 PRESIDENT 17-11 -81 MILLER 1300 CLERK 23-1 82 14-7 1 IT 319

IT 2 ASCII SQL 14-8 DDL 14-8 SQL> CREATE OR REPLACE VIEW acct 2 (" ", " ", " ", " ", " ", " ") 3 AS 4 SELECT ename, sal, job, hiredate, dname, loc 5 FROM emp, dept 6 WHERE emp.deptno = dept.deptno 7 AND emp.deptno = 10; 14-8 Oracle 14-8 SQL 14-9 SQL*PLUS 14-9 SQL> DESC acct 14-9? ---------------------------------- -------- ------------ VARCHAR2(10) NUMBER(7,2) VARCHAR2(9) 320

DATE VARCHAR2(14) VARCHAR2(13) 14-9 acct 14-10 14-10 SQL> SELECT * 2 FROM acct; 14-10 ---------- ---------- --------- ---------- -------------- -------- CLARK 2450 MANAGER 09-6 81 ACCOUNTING NEW YORK KING 5000 PRESIDENT 17-11 -81 ACCOUNTING NEW YORK MILLER 1300 CLERK 23-1 82 ACCOUNTING NEW YORK 14-10 CREATE VIEW Oracle / user_views 14-11 14-11 SQL> SELECT view_name, text_length, text 2 FROM user_views; 14-11 VIEW_NAME TEXT_LENGTH ------------------------------ --------------- TEXT ---------------------------------------------- ACCT 112 SELECT ename, sal, job, hiredate, dname, loc FROM emp, dept WHERE emp.deptno = d 321

AVERAGE 162 SELECT d.dname " ", AVG(e.sal) " ", AVG(NVL(comm,0)) " 14-11 Oracle Oracle 1 2 3 Oracle I/O Views DML Views Views DML DML DML Oracle DML DML DML DML GROUP BY DISTINCT DELETE 322

GROUP BY DISTINCT UPDATE ROWNUM UPDATE GROUP BY DISTINCT INSERT ROWNUM INSERT NOT NULL INSERT DML DML Oracle DML WITH CHECK OPTION 14-12 DDL WHERE WITH CHECK OPTION CONSTRAINT 14-12 SQL> CREATE VIEW sales30 2 AS 3 SELECT * 4 FROM emp 5 WHERE deptno = 30 6 WITH CHECK OPTION CONSTRAINT sales30_ck; 14-12 14-13 SQL*PLUS 14-13 SQL> DESC sales30 14-13? 323

----------------------------------------- -------- ------------ EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) 14-13 14-14 14-14 SQL> SELECT empno, ename, job, sal, deptno, comm, deptno 2 FROM sales30; 14-14 EMPNO ENAME JOB SAL DEPTNO COMM DEPTNO ---------- ---------- -------- ---------- ---------- -------- ------ 7499 ALLEN SALESMAN 1600 30 300 30 7521 WARD SALESMAN 1250 30 500 30 7654 MARTIN SALESMAN 1250 30 1400 30 7698 BLAKE MANAGER 2850 30 30 7844 TURNER SALESMAN 1500 30 0 30 7900 JAMES CLERK 950 30 30 6 14-14 deptno 10 14-15 DML 14-15 SQL> UPDATE sales30 2 SET deptno = 10 3 WHERE job = 'MANAGER'; 14-15 UPDATE sales30 * ERROR 1 : 324

ORA-01402: WITH CHECK OPTIDN where 14-15 14-15 where WHERE deptno = 30 Oracle WITH CHECK OPTION CONSTRAINT DML where 12 12.8 WITH CHECK OPTION CONSTRAINT 14-16 DDL 14-16 SQL> CREATE VIEW salary_limit 2 AS 3 SELECT * 4 FROM emp 5 WHERE sal >= 800 6 WITH CHECK OPTION CONSTRAINT salary_limit_ck; 14-16 salary_limit 14-17 SQL> INSERT INTO salary_limit (empno, ename, job, mgr, 2 hiredate, sal, comm, deptno) 3 VALUES (7800, ' ', ' ',7900, SYSDATE, 666, 77, 66); 14-17 INSERT INTO salary_limit (empno, ename, job, mgr, * ERROR 1 : ORA-01402: WITH CHECK OPTIDN where 14-17 Oracle salary_limit where sal >= 800 666 325

SQL DML acct 14-18 UPDATE 14-18 SQL> UPDATE acct 2 SET " " = 9999; 14-18 3 14-18 14-19 14-19 SQL> SELECT * 2 FROM acct; 14-19 ---------- --------- --------- ---------- ------------- ------ CLARK 9999 MANAGER 09-6 -81 ACCOUNTING NEW YORK KING 9999 PRESIDENT 17-11 -81 ACCOUNTING NEW YORK MILLER 9999 CLERK 23-1 -82 ACCOUNTING NEW YORK 14-19 Oracle DBA 14-20 emp 14-20 SQL> SELECT ename, sal, job 2 FROM emp 3 WHERE deptno = 10; 14-20 ENAME SAL JOB ---------- ---------- ------- 326

CLARK KING MILLER 9999 MANAGER 9999 PRESIDENT 9999 CLERK 14-20 14-21 SQL 14-21 SQL> ROLLBACK; 14-21 DML DML WITH READ ONLY WITH READ ONLY CREATE VIEW 14-22 acct 14-22 SQL> CREATE OR REPLACE VIEW acct 2 (" ", " ", " ", " ", " ", " ") 3 AS 4 SELECT ename, sal, job, hiredate, dname, loc 5 FROM emp, dept 6 WHERE emp.deptno = dept.deptno 7 AND emp.deptno = 10 8 WITH READ ONLY; 14-22 acct 14-23 UPDATE acct 14-23 SQL> UPDATE acct 2 SET " " = 9999; 14-23 SET " " = 9999 * 327

ERROR 2 : ORA-01733: Oracle UPDATE acct WITH READ ONLY 14-24 DELETE acct ORACLE DELETE 14-24 SQL> DELETE FROM acct; 14-24 DELETE FROM acct * ERROR 1 : ORA-01752: WITH READ ONLY DML Oracle WITH READ ONLY DML DML WITH READ ONLY DROP VIEW DROP VIEW 14-25 14-25 SQL> SELECT view_name, text_length 2 FROM user_views; 14-25 VIEW_NAME TEXT_LENGTH ---------------------------- ----------- ACCT 127 328

AVERAGE 162 SALARY_LIMIT 112 SALES30 113 14-25 acct Oracle 14-26 DDL 14-26 SQL> DROP VIEW acct; 14-26 14-27 user_views acct 14-27 SQL> SELECT view_name, text_length 2 FROM user_views; 14-27 VIEW_NAME TEXT_LENGTH ------------------------------ --------- AVERAGE 162 SALARY_LIMIT 112 SALES30 113 14-28 SQL*PLUS DESC 14-28 SQL> DESC acct 14-28 ERROR ORA-04043: acct Views DSS Data Warehouse Oracle OLTP 329

DSS Data Warehouse 20 90 90 DSS Data Warehouse Oracle8i OLTP DSS Data Warehouse Oracle8i Internet DSS Data Warehouse SUM AVG GROUP BY HAVING ORDER BY Oracle8i Inline Views 10 10 10-60 14-29 14-29 SQL> SELECT e.empno, e.ename, e.job, m.empno, m.ename, m.job 2 FROM emp_shell e, e_m_shell, (SELECT empno, ename, job 3 FROM emp) m 4 WHERE e.empno = e_m_shell.e_id 5 AND e_m_shell.m_id = m.empno; 14-29 Inline Views FROM Inline Views 14-19 m SELECT Inline Views n / Top n queries/analysis 330

20% 80% 20% 20% ORDER BY Oracle8i n / Top n queries/analysis n / Top n queries/analysis 20% n / Top n queries/analysis 200 1000 10 5 10 n / Top n queries/analysis SELECT ROWNUM [ [ ]] FROM [ [ ]] FROM ORDER BY Top n WHERE ROWNUM <= N Inline Views Inline Views ORDER BY ORDER BY DESC ROWNUM ROWNUM Inline Views 1 WHERE n < <= 331

5 5 14-30 14-30 SQL> SELECT rownum "Order NO.", ename "Name", sal "Salary", job "Job" 2 FROM (SELECT ename,sal,job 3 FROM emp 4 WHERE job NOT LIKE 'PRESI%' 5 ORDER BY sal DESC) 6 WHERE ROWNUM <= 5; 14-30 Order NO. Name Salary Job -------- --------- ---------- ------ 1 SCOTT 3000 ANALYST 2 FORD 3000 ANALYST 3 JONES 2975 MANAGER 4 BLAKE 2850 MANAGER 5 CLARK 2450 MANAGER emp 14-30 n / Top n queries/analysis 14-30 Views Views Views Views Views 332

Oracle Views Views DML WITH CHECK OPTION WITH READ ONLY WITH READ ONLY Views Inline Views Inline Views n / Top n queries/analysis n / Top n queries/analysis n / Top n queries/analysis 333

Sequence Synonym 1 Oracle Sequence Oracle Sequence Oracle Sequence Oracle CREATE SEQUENCE Sequence CREATE SEQUENCE DDL CREATE SEQUENCE CREATE SEQUENCE

[START WITH n] [INCREMENT BY n] [{MAXVALUE n NOMAXVALUE} [{MINVALUE n NOMINVALUE}] [{CACHE n NOCACHE}] [{CYCLE n NOCYCLE CYCLE 20}] START WITH n n 1 INCREMENT BY n 1 MAXVALUE n NOMAXVALUE 10 27 1 MINVALUE n NOMINVALUE 1 10 26 CACHE n n Oracle NOCACHE Oracle CACHE 20 CYCLE n NOCYCLE 10 10-11 supplier 15-1 SQL*PLUS 15-1 SQL> desc supplier 15-1? --------------------------------- -------- ------------ 335

S_CODE SNAME CONTACT PHONE FAX NUMBER(6) VARCHAR2(25) VARCHAR2(15) VARCHAR2(15) VARCHAR2(15) 15-2 supplier 15-2 SQL> select * from supplier; 15-2 15-2 supplier Oracle s_code 15-3 DDL supplier_s_code 15-3 SQL> CREATE SEQUENCE supplier_s_code 2 START WITH 2000 3 INCREMENT BY 10 4 MAXVALUE 100000 5 NOCACHE 6 NOCYCLE; 15-3 15-3 Oracle user_sequences 15-5 15-4 SQL*PLUS sequence_name 15-4 SQL> col sequence_name for a18 15-5 SQL> SELECT sequence_name, min_value, max_value, 2 increment_by, last_number 336

3 FROM user_sequences; 15-5 SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER ------------------ ---------- ---------- ------------ ----------- SUPPLIER_S_CODE 1 100000 10 2000 15-5 supplier_s_code Oracle user_objects supplier_s_code 15-6 15-6 SQL> SELECT object_name, object_type, created, 2 last_ddl_time, status 3 FROM user_objects 4 WHERE object_type!= 'TABLE' 5 AND object_type!= 'INDEX'; 15-6 OBJECT_NAME OBJECT_TYPE CREATED LAST_DDL_T STATUS ------------------ ------------------ ---------- ---------- ------ AVERAGE VIEW 15-4 -03 15-4 -03 VALID SALARY_LIMIT VIEW 15-4 -03 15-4 -03 VALID SALES30 VIEW 15-4 -03 15-4 -03 VALID SUPPLIER_S_CODE SEQUENCE 26-4 -03 26-4 -03 VALID 1 SELECT SELECT SELECT FROM SELECT Oracle NEXTVAL CURRVAL NEXTVAL 337

CURRVAL dual supplier_s_code 15-7 15-7 SQL> SELECT supplier_s_code.currval 2 FROM dual; 15-7 SELECT supplier_s_code.currval * ERROR 1 : ORA-08002: SUPPLIER_S_CODE.CURRVAL 15-7 15-7 SQL Oracle CURRVAL NEXTVAL supplier_s_code supplier 15-8 DML supplier_s_ code.nextval 15-8 SQL> INSERT INTO supplier(s_code, sname, contact, phone, fax) 2 VALUES (supplier_s_code.nextval, ' ', 3 ' ', 4444944, 4444844); 15-8 1 15-10 15-9 SQL*PLUS 15-9 SQL> col sname for a16 SQL> col contact for a12 SQL> col phone for a10 SQL> col fax for a10 15-10 SQL> SELECT * 338

2 FROM supplier; 15-10 S_CODE SNAME CONTACT PHONE FAX ---------- ---------------- ------------ ---------- -------- 2000 4444944 4444844 15-10 dual supplier_s_code 15-11 15-11 SQL> SELECT supplier_s_code.currval 2 FROM dual; 15-11 CURRVAL ---------- 2000 supplier_s_code NEXTVAL CURRVAL 15-12 DML supplier 15-12 SQL> INSERT INTO supplier(s_code, sname, contact, phone, fax) 2 VALUES (supplier_s_code.nextval, ' ', 3 ' ', 1741741, 1741742); 15-12 1 15-13 15-13 SQL> SELECT * 2 FROM supplier; 15-13 S_CODE SNAME CONTACT PHONE FAX ---------- ---------------- ------------ ---------- ------- 339

2000 4444944 4444844 2010 1741741 1741742 15-13 Oracle dual supplier_s_code 15-14 15-14 SQL> SELECT supplier_s_code.currval 2 FROM dual; 15-14 CURRVAL ---------- 2010 NEXTVAL CURRVAL ord 15-15 SQL*PLUS ord 15-15 SQL> DESC ord 15-15? ---------------------------------------- -------- ----------- ORDNO NUMBER(8) P_CODE NUMBER(6) S_CODE NUMBER(6) ORDATE DATE UNIT NUMBER(6) PRICE NUMBER(8,2) CREATE TABLE ord 340

Oracle ordno 15-16 DDL ord_ordno 15-16 SQL> CREATE SEQUENCE ord_ordno 2 START WITH 1000 3 INCREMENT BY 1 4 MAXVALUE 100000 5 NOCYCLE; 15-16 15-16 Oracle user_sequences 15-17 15-17 SQL> SELECT sequence_name, min_value, max_value, 2 increment_by, last_number, cache_size 3 FROM user_sequences; 15-17 SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER CACHE_SIZE ------ ---------- ---------- ------------ ----------- ---------- ORD_ORDNO 1 100000 1 1000 20 SUPPLIER_S_CODE 1 100000 10 2020 0 15-17 CACHE_SIZE NOCACHE NOCACHE 15-3 DDL supplier_s_code NOCACHE 15-17 SUPPLIER_S_CODE CACHE_SIZE 0 15-16 DDL ord_ordno NOCACHE 15-17 ORD_ORDNO CACHE_SIZE 20 ord_ordno ord 15-18 DML 15-18 SQL> INSERT INTO ord(ordno, p_code, s_code, ordate, unit, price) 2 VALUES (ord_ordno.nextval, 881, supplier_s_code.currval, 3 SYSDATE, 10, 2.5); 341

15-18 1 15-18 1 15-19 15-19 SQL> SELECT * 2 FROM ord; 15-19 ORDNO P_CODE S_CODE ORDATE UNIT PRICE ---------- ---------- ---------- ---------- ---------- ---------- 1000 881 2010 27-4 -03 10 2.5 15-20 DML ord ordno, s_code ordate 15-20 SQL> INSERT INTO ord(ordno, p_code, s_code, ordate, unit, price) 2 VALUES (ord_ordno.currval, 882, supplier_s_code.currval, 3 SYSDATE, 15, 6.12); 15-20 1 15-20 1 15-21 15-21 SQL> SELECT * 2 FROM ord; 15-21 ORDNO P_CODE S_CODE ORDATE UNIT PRICE ---------- ---------- ---------- ---------- ---------- ---------- 1000 881 2010 27-4 -03 10 2.5 1000 882 2010 27-4 -03 15 6.12 15-21 ord 15-20 DML 342

DML CURRVAL NEXTVAL NEXTVAL NOCYCLE Oracle CYCLE CYCLE NEXTVAL CURRVAL NEXTVAL CURRVAL NEXTVAL NEXTVAL NEXTVAL.NEXTVAL CURRVAL CURRVAL CURRVAL NEXTVAL NEXTVAL CURRVAL SELECT SELECT UPDATE SET INSERT SELECT INSERT VALUES NEXTVAL CURRVAL SELECT SELECT UPDATE DELETE DISTINCT SELECT ORDER BY SELECT GROUP BY SELECT HAVING SELECT 343

DEFAULT CREATE TABLE DEFAULT ALTER TABLE CREATE SEQUENCE CACHE n n Oracle 1 Oracle n 2 3 Oracle n SCOTT/TIGER SQL*PLUS 15-22 15-22 SQL> SELECT sequence_name, min_value, max_value, 2 increment_by, last_number, cache_size 3 FROM user_sequences; 15-22 SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER CACHE_SIZE ----------------- ---------- ------------ ----------- ---------------- ORD_ORDNO 1 100000 1 1020 20 SUPPLIER_S_CODE 1 100000 10 2020 0 15-22 LAST_NUMBER ORD_ORDNO 1020 15-21 ORDNO 1000 20 1000 Oracle 20 SQL*PLUS ord_ordno dept_dml 15-23 DML 15-23 SQL> INSERT INTO dept_dml(deptno, dname, loc) 2 VALUES (ord_ordno.nextval, ' ', ' '); 15-23 VALUES (ord_ordno.nextval, ' ', ' ') * 344

ERROR 2 : ORA-01438: 15-23 15-23 DML Oracle ord_ordno 15-24 ord_ordno 15-24 SQL> SELECT ord_ordno.currval 2 FROM dual; 15-24 CURRVAL ---------- 1020 15-24 SQL deptno 0~99 Oracle 15-25 15-25 SQL> SELECT sequence_name, min_value, max_value, 2 increment_by, last_number, cache_size 3 FROM user_sequences; 15-25 SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER CACHE_SIZE -------- ---------- ---------- ------------ ----------- ---------------- ORD_ORDNO 1 100000 1 1040 20 SUPPLIER_S_CODE 1 100000 10 2020 0 15-25 LAST_NUMBER ORD_ORDNO 1040 Sequence NOCACHE user_sequences cache_size 0 user_sequences LAST_NUMBER COMMIT ROLLBACK 345

Sequence Oracle ALTER SEQUENCE SUPPLIER_S_CODE 15-26 DDL 15-26 SQL> ALTER SEQUENCE supplier_s_code 2 INCREMENT BY 11 3 MAXVALUE 999999 4 CACHE 50; 15-26 user_sequences 15-27 15-27 SQL> SELECT sequence_name, min_value, max_value, 2 increment_by, last_number, cache_size 3 FROM user_sequences; 15-27 SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER CACHE_SIZE ----- ---------- ---------- ------------ ----------- ------------------ ORD_ORDNO 1 100000 1 1040 20 SUPPLIER_S_CODE 1 999999 11 2021 50 15-27 SUPPLIER_S_CODE SUPPLIER_S_CODE Oracle 15-28 INSERT supplier_s_ code.nextval supplier 346

15-28 SQL> INSERT INTO supplier(s_code, sname, contact, phone, fax) 2 VALUES (supplier_s_code.nextval, ' ', 3 ' ', 1671671, 1671674); 15-28 1 15-29 supplier 15-29 SQL> SELECT * 2 FROM supplier; 15-29 S_CODE SNAME CONTACT PHONE FAX ---------- ---------------- --------------- --------------- ------- 2000 4444944 4444844 2010 1741741 1741742 2021 1671671 1671674 15-29 15-30 SUPPLIER_S_CODE 15-30 SQL> SELECT supplier_s_code.currval 2 FROM dual; 15-30 CURRVAL ---------- 2021 15-31 SUPPLIER_S_CODE 15-31 SQL> SELECT sequence_name, min_value, max_value, 2 increment_by, last_number, cache_size 3 FROM user_sequences; 347

15-31 SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER CACHE_SIZE ------ ---------- ---------- ------------ ----------- ----------------- ORD_ORDNO 1 100000 1 1040 20 SUPPLIER_S_CODE 1 999999 11 2571 50 15-31 LAST_NUMBER 2021 2571 2021+11 50=2021+550=2571 15-32 INSERT supplier_s_code.nextval supplier 15-32 SQL> INSERT INTO supplier(s_code, sname, contact, phone, fax) 2 VALUES (supplier_s_code.nextval, ' ', 3 ' ', 1681684, 1681684); 15-32 1 15-33 supplier 15-33 SQL> SELECT * 2 FROM supplier; 15-33 S_CODE SNAME CONTACT PHONE FAX ---------- --------------- --------------- --------------- ------- 2000 4444944 4444844 2010 1741741 1741742 2021 1671671 1671674 2032 1681684 1681684 ALTER SEQUENCE ALTER SEQUENCE [INCREMENT BY n] [{MAXVALUE n NOMAXVALUE} [{MINVALUE n NOMINVALUE}] [{CACHE n NOCACHE}] 348

[{CYCLE n NOCYCLE}] ALTER ANY SEQUENCE START WITH ALTER SEQUENCE Oracle MAXVALUE Sequence DROP SEQUENCE 15-34 DDL supplier_s_code 15-34 SQL> DROP SEQUENCE supplier_s_code; 15-34 15-35 SUPPLIER_ S_CODE 15-35 SQL> SELECT sequence_name, min_value, max_value, 2 increment_by, last_number, cache_size 3 FROM user_sequences; 15-35 SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER CACHE_SIZE ------- ---------- --------- ------------ ----------- ----------------- ORD_ORDNO 1 100000 1 1040 20 15-35 SUPPLIER_S_CODE DROP SEQUENCE supplier_s_code SUPPLIER_S_CODE Oracle SUPPLIER_S_CODE 15-36 15-36 SQL> SELECT * 349

2 FROM supplier; 15-36 S_CODE SNAME CONTACT PHONE FAX ---------- ---------------- --------------- --------------- ------- 2000 4444944 4444844 2010 1741741 1741742 2021 1671671 1671674 2032 1681684 1681684 15-37 DML 15-37 SQL> INSERT INTO supplier(s_code, sname, contact, phone, fax) 2 VALUES (supplier_s_code.nextval, ' ', 3 '', 1234567, 1234567); 15-37 VALUES (supplier_s_code.nextval, ' ', * ERROR 2 : ORA-02289: DROP SEQUENCE Sequence Sequence Sequence 1 START WITH 1 1 0 350

Oracle Synonym supplier Synonym 15-38 CREATE SYNONYM supplier s 15-38 SQL> CREATE SYNONYM s 2 FOR supplier; 15-38 s supplier 15-39 15-39 SQL> SELECT * 2 FROM s; 15-39 S_CODE SNAME CONTACT PHONE FAX ---------- ----------------- --------------- --------------- ------- 2000 4444944 4444844 2010 1741741 1741742 2021 1671671 1671674 2032 1681684 1681684 Synonym user_objects Synonym 15-41 user_objects Synonym SQL*PLUS 15-40 SQL*PLUS 15-40 351

SQL> col object_name for a20 15-41 SQL> SELECT object_name, object_type, created, status 2 FROM user_objects 3 WHERE object_type LIKE 'SYN%'; 15-41 OBJECT_NAME OBJECT_TYPE CREATED STATUS -------------------- ------------------ ---------- ------ S SYNONYM 28-4 -03 VALID 15-41 Synonym Synonym s 15-41 Synonym Synonym 15-43 user_synonyms SQL*PLUS 15-42 SQL*PLUS 15-42 SQL> col table_owner for a12 SQL> col table_name for a12 15-43 SQL> SELECT synonym_name, table_owner, table_name 2 FROM user_synonyms; 15-43 SYNONYM_NAME TABLE_OWNER TABLE_NAME ------------------------------ ------------ ---------- S SCOTT SUPPLIER 15-43 Synonym Synonym s Synonym supplier SCOTT (Synonym) CREATE [PUBLIC] SYNONYM FOR PUBLIC 352

Synonym s SCOTT scott.s supplier supplier Synonym SCOTT SYSTEM 15-44 SQL*PLUS 15-44 SQL> CONNECT SYSTEM/MANAGER 15-44 15-45 15-45 SQL> SELECT * 2 FROM s; 15-45 FROM s * ERROR 2 : ORA-00942: 15-46 15-46 SQL> SELECT * 2 FROM supplier; 353

15-46 FROM supplier * ERROR 2 : ORA-00942: Synonym s scott. 15-47 15-47 SQL> SELECT * 2 FROM scott.s; 15-47 S_CODE SNAME CONTACT PHONE FAX ---------- ------------------- --------------- --------------- ------ 2000 4444944 4444844 2010 1741741 1741742 2021 1671671 1671674 2032 1681684 1681684 Synonym s. 15-48 DDL scott supplier Synonym ss 15-48 SQL> CREATE PUBLIC SYNONYM ss 2 FOR scott.supplier; 15-48 Synonym ss 15-49 15-49 SQL> SELECT * 2 FROM ss; 15-49 S_CODE SNAME CONTACT PHONE FAX ---------- ---------------- --------------- --------------- ------- 354

2000 4444944 4444844 2010 1741741 1741742 2021 1671671 1671674 2032 1681684 1681684 Synonym ss 15-50 SQL*PLUS SYS Oracle 15-50 SQL> CONNECT SYS/Oracle AS SYSDBA; 15-50 Synonym ss 15-51 15-51 SQL> SELECT * 2 FROM ss; 15-51 S_CODE SNAME CONTACT PHONE FAX ---------- --------------- --------------- --------------- ------- 2000 4444944 4444844 2010 1741741 1741742 2021 1671671 1671674 2032 1681684 1681684 Oracle Synonym Synonym Synonym Synonym s 15-53 DDL 15-52 SQL*PLUS SCOTT 15-52 355

SQL> CONNECT SCOTT/TIGER 15-52 15-53 SQL> DROP SYNONYM s; 15-53 15-53 15-54 user_objects 15-54 SQL> SELECT object_name, object_type, created, status 2 FROM user_objects 3 WHERE object_type LIKE 'SYN%'; 15-54 15-55 user_ synonyms 15-55 SQL> SELECT synonym_name, table_owner, table_name 2 FROM user_synonyms; 15-55 15-54 15-55 Synonym s Synonym DROP SYNONYM Sequence Sequence 356

Sequence CREATE SEQUENCE Sequence Sequence NEXTVAL CURRVAL NEXTVAL CURRVAL NEXTVAL CURRVAL NEXTVAL CURRVAL Synonym Synonym Synonym Synonym 357

Internet Oracle Oracle Oracle Oracle Oracle Oracle 1 2 3 4 5 6 Oracle 1 Oracle 2 Oracle Oracle Oracle Oracle Oracle

Oracle Oracle Oracle Oracle DBA CREATE USER CREATE USER CREATE USER IDENTIFIED BY DDL dog wangwang SCOTT/TIGER 16-1 CREATE USER SCOTT DBA 16-1 SQL> CREATE USER dog 2 IDENTIFIED BY wangwang; 16-1 IDENTIFIED BY wangwang * ERROR 2 : ORA-01031: 16-2 SQL*PLUS DBA SYSTEM/MANAGER 16-2 SQL> CONNECT SYSTEM/MANAGER 16-2 16-3 16-1 DDL 16-3 SQL> CREATE USER dog 2 IDENTIFIED BY wangwang; 16-3 359

dog dog dba_users 16-4 16-4 SQL> SELECT username, created 2 FROM dba_users 3 WHERE ROUND(created, 'DAY') = ROUND(SYSDATE, 'DAY'); 16-4 USERNAME CREATED ------------------------------ ------------ DOG 29-4 03 16-4 dog 16-4 WHERE ROUND WHERE dog SQL*PLUS 16-5 SQL*PLUS 16-5 SQL> connect dog/wangwang; 16-5 ERROR ORA-01045: user DOG lacks CREATE SESSION privilege; logon denied : Oracle 16-5 16-5 16-5 ORA-01045 DOG CREATE SESSION DBA CREATE USER DBA 360

Oracle SQL DBA DBA DBA Oracle8 Oracle8i 120 CREATE USER DBA DROP USER SELECT ANY TABLE CREATE ANY TABLE DROP ANY TABLE CREATE SESSION CREATE TABLE CREATE VIEW CREATE SEQUENCE CREATE PROCEDURE ANY Oracle8 SELECT ANY TABLE 361

SELECT ANY TABLE Oracle8 Oracle8i SELECT ANY TABLE DBA SELECT ANY TABLE DBA Oracle9i SELECT ANY TABLE DBA Oracle8 Oracle DCL: Data Control Language GRANT dog 16-6 SQL*PLUS SYSTEM 16-6 SQL> connect system/manager; 16-6 16-7 DCL CREATE SESSION dog 16-7 SQL> GRANT CREATE SESSION TO dog; 16-7 16-8 SQL*PLUS dog 16-8 SQL> connect dog/wangwang; 16-8 16-8 Oracle dog 16-9 362

16-9 SQL> SELECT object_name, created 2 FROM user_objects; 16-9 16-9 dog dog dog 16-10 SQL*PLUS SYSTEM 16-10 SQL> connect system/manager; 16-10 GRANT 16-11 CREATE USER cat miaomiao 16-11 SQL> CREATE USER cat 2 IDENTIFIED BY miaomiao; 16-11 16-12 DCL CREATE SESSION cat 16-12 SQL> GRANT CREATE SESSION 2 TO cat; 16-12 16-13 DCL SELECT ANY TABLE CREATE TABLE CREATE VIEW 3 dog cat 16-13 SQL> GRANT SELECT ANY TABLE, CREATE TABLE, CREATE VIEW 363

2 TO dog, cat; 16-13 dog cat cat dog 16-14 SQL*PLUS cat 16-14 SQL> connect cat/miaomiao; 16-14 cat SELECT ANY TABLE 16-15 scott dept 16-15 SQL> select * from scott.dept; 16-15 DEPTNO DNAME LOC ---------- -------------- --------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON cat SELECT ANY TABLE CREATE TABLE 16-15 DDL baby_cat 16-15 SQL> CREATE TABLE baby_cat 2 AS 3 SELECT * 4 FROM scott.emp; 16-15 FROM scott.emp * ERROR 4 : ORA-01950: 'SYSTEM' 364

16-15 Oracle 16-16 SQL*PLUS SYSTEM DBA 16-16 SQL> connect system/manager; 16-16 16-17 tablespace 16-17 SQL> select tablespace_name from dba_tablespaces; 16-17 TABLESPACE_NAME --------------- SYSTEM UNDOTBS CWMLITE DRSYS EXAMPLE INDX TEMP TOOLS USERS 9 16-18 DDL cat USERS cat USERS cat USERS 20 M 16-18 SQL> ALTER USER cat 2 DEFAULT TABLESPACE USERS 365

3 QUOTA 20m ON USERS; 16-18 16-19 SQL*PLUS cat 16-19 SQL> connect cat/miaomiao; 16-19 16-20 DDL baby_cat 16-20 SQL> CREATE TABLE baby_cat 2 AS 3 SELECT * 4 FROM scott.emp; 16-20 16-20 SQL 16-21 16-21 SQL> SELECT ename 2 FROM baby_cat; 16-21 ENAME ------------- SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING 366

TURNER ADAMS JAMES FORD MILLER 14 baby_cat cat CREATE SESSION SELECT ANY TABLE CREATE TABLE SESSION_PRIVS 16-22 cat 16-22 SQL> SELECT * 2 FROM SESSION_PRIVS; 16-22 PRIVILEGE ---------------- CREATE SESSION CREATE TABLE SELECT ANY TABLE CREATE VIEW 16-22 cat 4 GRANT cat dog 16-23 SQL*PLUS dog 16-23 SQL> CONNECT dog/wangwang; 16-23 16-24 dog 16-24 SQL> SELECT * 367

2 FROM SESSION_PRIVS; 16-24 PRIVILEGE ---------------- CREATE SESSION CREATE TABLE SELECT ANY TABLE CREATE VIEW 16-24 dog 4 GRANT 4 cat SCOTT SCOTT SCOTT 16-25 SQL*PLUS scott 16-25 SQL> CONNECT scott/tiger 16-25 16-26 scott 16-26 SQL> SELECT * 2 FROM SESSION_PRIVS; 16-26 PRIVILEGE -------------------- CREATE SESSION ALTER SESSION UNLIMITED TABLESPACE CREATE TABLE CREATE CLUSTER CREATE SYNONYM CREATE VIEW CREATE SEQUENCE CREATE DATABASE LINK 368

CREATE PROCEDURE CREATE TRIGGER CREATE TYPE CREATE OPERATOR CREATE INDEXTYPE 14 16-26 SCOTT SCOTT 1000 20 20 1000 20 1000=20000 Oracle Role Role Role Role 20 Role Role 1000 20+1000 = 1020 Role Role Role 20000 Role 1020 I/O Oracle 369

Role dog cat SYSTEM DBA 16-27 SQL*PLUS SYSTEM 16-27 SQL> connect system/manager 16-27 16-28 DCL dog cat REVOKE 16-28 SQL> REVOKE SELECT ANY TABLE, CREATE TABLE, 2 CREATE VIEW, CREATE SESSION 3 FROM dog, cat; 16-28 16-28 16-29 16-30 SQL*PLUS 16-29 SQL> connect dog/wangwang 16-29 ERROR ORA-01045: user DOG lacks CREATE SESSION privilege; logon denied : Oracle 16-30 SQL> connect cat/miaomiao 370

16-30 ERROR ORA-01045: user CAT lacks CREATE SESSION privilege; logon denied 16-31 SQL*PLUS SYSTEM 16-31 SQL> connect system/manager 16-31 Role pig hengheng 16-32 DDL pig 16-32 SQL> CREATE USER pig 2 IDENTIFIED BY hengheng; 16-32 16-33 DDL ROLE 16-33 SQL> CREATE ROLE animal; 16-33 animal 16-34 DCL SELECT ANY TABLE CREATE TABLE CREATE VIEW CREATE SESSION animal 16-34 SQL> GRANT SELECT ANY TABLE, CREATE TABLE, 2 CREATE VIEW, CREATE SESSION 3 TO animal; 16-34 371

16-35 DCL animal dog cat pig 16-35 SQL> GRANT animal TO dog, cat, pig; 16-35 animal dog cat pig animal 16-36 ROLE_SYS_PRIVS animal 16-36 SQL> SELECT * 2 FROM ROLE_SYS_PRIVS 3 WHERE ROLE LIKE 'ANI%'; 16-36 ROLE PRIVILEGE ADM --------------- ---------------------------------------- -------- ANIMAL CREATE SESSION NO ANIMAL CREATE TABLE NO ANIMAL CREATE VIEW NO ANIMAL SELECT ANY TABLE NO 16-37 dba_role_privs animal 16-37 SQL> SELECT * 2 FROM dba_role_privs 3 WHERE GRANTED_ROLE LIKE 'ANI%'; 16-37 GRANTEE GRANTED_ROLE ADM DEF ---------------------------- ------------------------- --- --- CAT ANIMAL NO YES DOG ANIMAL NO YES PIG ANIMAL NO YES SYSTEM ANIMAL YES YES 372

DBA SYSTEM 16-38 16-43 dog cat pig 16-38 SQL> connect dog/wangwang 16-38 16-39 SQL> SELECT * 2 FROM SESSION_PRIVS; 16-39 PRIVILEGE ---------------- CREATE SESSION CREATE TABLE SELECT ANY TABLE CREATE VIEW 16-40 SQL> connect cat/miaomiao 16-40 16-41 SQL> SELECT * 2 FROM SESSION_PRIVS; 16-41 PRIVILEGE ---------------- CREATE SESSION CREATE TABLE SELECT ANY TABLE CREATE VIEW 16-42 SQL> connect pig/hengheng 373

16-42 16-43 SQL> SELECT * 2 FROM SESSION_PRIVS; 16-43 PRIVILEGE ---------------- CREATE SESSION CREATE TABLE SELECT ANY TABLE CREATE VIEW Oracle 8 Object Privilege EXECUTE ALTER SELECT INDEX REFERENCES INSERT UPDATE DELETE 16.1 16.1 Object Privilege Procedure Sequence View Table EXECUTE ALTER SELECT INSERT UPDATE DELETE INDEX REFERENCES 16.1 Procedure EXECUTE 374

Procedure Function Package Sequence EXECUTE NEXTVAL CURRVAL SELECT ALTER View Oracle ALTER EXECUTE INDEX REFERENCES SELECT DML INSERT UPDATE DELETE TABLE EXECUTE GRANT GRANT ALL[ [ ] ] ON TO [ PUBLIC] [WITH GRANT OPTION] ALL ON TO [ ] [ ] PUBLIC WITH GRANT OPTION 16-44 SQL*PLUS pig 16-44 SQL> connect pig/hengheng; 16-44 375

16-45 pig 16-45 SQL> select * from cat; 16-45 16-45 pig pig 16-46 cat baby_cat pig animal animal SELECT ANY TABLE 16-46 SQL> select * from cat.baby_cat; 16-46 EMPNO ENAME JOB MGR HIREDATE ---------- ---------- --------- ---------- ---------- --- 7369 SMITH CLERK 7902 17-12 -1980 7499 ALLEN SALESMAN 7698 20-2 -1981 7521 WARD SALESMAN 7698 22-2 -1981 7566 JONES MANAGER 7839 02-4 -1981 7654 MARTIN SALESMAN 7698 28-9 -1981 7698 BLAKE MANAGER 7839 01-5 -1981 7782 CLARK MANAGER 7839 09-6 -1981 7788 SCOTT ANALYST 7566 19-4 -1987 7839 KING PRESIDENT 17-11 -1981 7844 TURNER SALESMAN 7698 08-9 -1981 7876 ADAMS CLERK 7788 23-5 -1987 7900 JAMES CLERK 7698 03-12 -1981 7902 FORD ANALYST 7566 03-12 -1981 7934 MILLER CLERK 7782 23-1 -1982 14 16-46 animal SELECT ANY TABLE SYSTEM DBA 376

SQL REVOKE 16-47 SQL*PLUS SYSTEM 16-47 SQL> connect system/manager 16-47 16-48 DCL animal SELECT ANY TABLE 16-48 SQL> REVOKE SELECT ANY TABLE 2 FROM animal; 16-48 pig SELECT ANY TABLE 16-49 SQL*PLUS pig 16-49 SQL> connect pig/hengheng; 16-49 16-50 pig SELECT ANY TABLE 16-50 SQL> SELECT ename 2 FROM cat.baby_cat; 16-50 FROM cat.baby_cat * ERROR 2 : ORA-00942: 16-50 pig SELECT ANY TABLE 377

16-51 16-51 SQL> SELECT * 2 FROM SESSION_PRIVS; 16-51 PRIVILEGE -------------- CREATE SESSION CREATE TABLE CREATE VIEW 16-51 16-52 SQL*PLUS cat 16-52 SQL> connect cat/miaomiao; 16-52 16-53 DCL cat baby_cat SELECT pig WITH GRANT OPTION pig 16-53 SQL> GRANT select 2 ON baby_cat 3 TO pig 4 WITH GRANT OPTION; 16-53 GRANT WITH GRANT OPTION cat pig pig pig cat baby_cat 378

16-54 SQL*PLUS pig 16-54 SQL> connect pig/hengheng; 16-54 16-55 pig cat baby_cat 16-55 SQL> SELECT empno, ename 2 FROM cat.baby_cat; 16-55 EMPNO ENAME ---------- ------ 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER 14 dog cat baby_cat 16-56 SQL*PLUS dog 16-56 SQL> connect dog/wangwang; 16-56 379

16-57 dog cat baby_cat 16-57 SQL> SELECT empno, ename 2 FROM cat.baby_cat; 16-57 FROM cat.baby_cat * ERROR 2 : ORA-00942: 16-57 cat baby_cat SELECT dog 16-58 SQL*PLUS pig 16-58 SQL> connect pig/hengheng; 16-58 16-59 DCL cat baby_cat SELECT dog 16-59 SQL> GRANT select 2 ON cat.baby_cat 3 TO dog; 16-59 16-59 pig cat pig 16-60 SQL*PLUS dog 16-60 SQL> connect dog/wangwang; 380

16-60 16-61 dog cat baby_cat 16-61 SQL> SELECT empno, ename 2 FROM cat.baby_cat; 16-61 EMPNO ENAME ---------- ------ 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER 14 scott supplier 16-62 scott supplier 16-62 SQL> SELECT * 2 FROM scott.supplier; 16-62 FROM scott.supplier * ERROR 2 : ORA-00942: 381

16-62 scott supplier SELECT cat dog pig 3 16-63 SQL*PLUS scott 16-63 SQL> connect scott/tiger; 16-63 16-64 DCL scott supplier SELECT scott 16-64 SQL> GRANT select 2 ON supplier 3 TO PUBLIC; 16-64 16-65 SQL*PLUS dog 16-65 SQL> connect dog/wangwang 16-65 16-66 dog scott supplier 16-66 SQL> SELECT * 2 FROM scott.supplier; 16-66 S_CODE SNAME CONTACT PHONE FAX ---------- --------------- --------------- --------------- ------- 2000 4444944 4444844 2010 1741741 1741742 2021 1671671 1671674 382

2032 1681684 1681684 scott supplier 16-67 SQL*PLUS cat 16-67 SQL> connect cat/miaomiao 16-67 16-68 cat scott supplier 16-68 SQL> SELECT * 2 FROM scott.supplier; 16-68 S_CODE SNAME CONTACT PHONE FAX ---------- ---------------- --------------- --------------- ------- 2000 4444944 4444844 2010 1741741 1741742 2021 1671671 1671674 2032 1681684 1681684 pig scott supplier 16-64 DCL scott supplier SYSTEM DBA 16-69 SQL*PLUS SYSTEM 16-69 SQL> connect system/manager 16-69 16-70 DDL fox loveyou 383

16-70 SQL> CREATE USER fox 2 IDENTIFIED BY loveyou; 16-70 16-71 DCL animal fox 16-71 SQL> GRANT animal TO fox; 16-71 fox scott supplier 16-72 SQL*PLUS fox 16-72 SQL> connect fox/loveyou; 16-72 16-73 fox scott supplier 16-73 SQL> SELECT * 2 FROM scott.supplier; 16-73 S_CODE SNAME CONTACT PHONE FAX ---------- --------------- --------------- --------------- ------- 2000 4444944 4444844 2010 1741741 1741742 2021 1671671 1671674 2032 1681684 1681684 16-73 user_tab_privs_made 384

16-74 SQL*PLUS cat 16-74 SQL> connect cat/miaomiao; 16-74 16-75 SQL*PLUS 16-75 SQL> col GRANTEE for a8 SQL> col TABLE_NAME for a10 SQL> col GRANTOR for a8 SQL> col PRIVILEGE for a18 16-76 cat 16-76 SQL> SELECT * 2 FROM user_tab_privs_made; 16-76 GRANTEE TABLE_NAME GRANTOR PRIVILEGE GRA HIE -------- ---------- -------- ------------------ --- --- PIG BABY_CAT CAT SELECT YES NO DOG BABY_CAT PIG SELECT NO NO 16-76 cat BABY_CAT SELECT pig pig 16-76 pig BABY_CAT SELECT dog dog 16-77 SQL*PLUS SCOTT 16-77 SQL> connect scott/tiger 16-78 DCL scott supplier phone fax update cat 385

16-78 SQL> GRANT update(phone, fax) 2 ON supplier 3 TO cat; 16-78 16-79 SQL*PLUS cat 16-79 SQL> connect cat/miaomiao; 16-79 16-80 16-81 DML scott supplier phone fax 16-80 SQL> UPDATE scott.supplier 2 SET phone = '168 cat' 3 WHERE s_code = 2000; 16-80 1 16-81 SQL> UPDATE scott.supplier 2 SET fax = 'fox and dog' 3 WHERE s_code = 2010; 16-81 1 16-82 16-82 SQL> SELECT * 2 FROM scott.supplier; 16-82 S_CODE SNAME CONTACT PHONE FAX 386

---------- ------------- --------------- --------------- ------------ 2000 168 cat 4444844 2010 1741741 fox and dog 2021 1671671 1671674 2032 1681684 1681684 16-82 scott supplier phone fax 16-82 PHONE FAX REVOKE REVOKE { } [ { }] FROM { PUBLIC } [ { PUBLIC}] 16-48 animal SELECT ANY TABLE REVOKE { [ ] ALL} ON FROM { PUBLIC } [ { PUBLIC}] CASCAD CONSTRAINTS] CASCAD CONSTRAINTS pig dog 16-83 SQL*PLUS pig 387

16-83 SQL> connect pig/hengheng; 16-83 16-84 DCL cat baby_cat SELECT dog 16-84 SQL> REVOKE select 2 ON cat.baby_cat 3 FROM dog; 16-84 16-85 WITH GRANT OPTION DCL cat baby_cat SELECT dog 16-85 SQL> GRANT select 2 ON cat.baby_cat 3 TO dog 4 WITH GRANT OPTION; 16-85 16-86 SQL*PLUS dog 16-86 SQL> connect dog/wangwang; 16-86 16-87 16-87 SQL> SELECT rownum "Cat NO.", ename "Cat Name" 2 FROM (SELECT ename 3 FROM cat.baby_cat 388

4 ORDER BY ename) 5 WHERE ROWNUM <= 6; 16-87 Cat NO. Cat Name ---------- -------- 1 ADAMS 2 ALLEN 3 BLAKE 4 CLARK 5 FORD 6 JAMES 6 16-87 n 6 Top n queries 16-88 DCL cat baby_cat SELECT fox 16-88 SQL> GRANT select 2 ON cat.baby_cat 3 TO fox; 16-88 16-89 SQL*PLUS fox 16-89 SQL> connect fox/loveyou 16-89 16-90 16-90 SQL> SELECT rownum "Cat NO.", ename "Cat Name" 2 FROM (SELECT ename 3 FROM cat.baby_cat 4 ORDER BY ename) 5 WHERE ROWNUM <= 6; 389

16-90 Cat NO. Cat Name ---------- -------- 1 ADAMS 2 ALLEN 3 BLAKE 4 CLARK 5 FORD 6 JAMES 6 WITH GRANT OPTION 16-91 SQL*PLUS cat 16-91 SQL> connect cat/miaomiao 16-91 16-92 DCL pig baby_cat SELECT 16-92 SQL> REVOKE select 2 ON baby_cat 3 FROM pig; 16-92 pig dog fox 3 cat baby_cat SELECT 16-93 SQL*PLUS pig 16-93 SQL> connect pig/hengheng; 16-93 390

16-94 pig cat baby_cat cat baby_cat SELECT 16-94 SQL> SELECT * 2 FROM cat.baby_cat; 16-94 FROM cat.baby_cat * ERROR 2 : ORA-00942: pig dog 16-95 SQL*PLUS dog 16-95 SQL> connect dog/wangwang 16-95 16-96 dog cat baby_cat cat baby_cat SELECT 16-96 SQL> SELECT * 2 FROM cat.baby_cat; 16-96 FROM cat.baby_cat * ERROR 2 : ORA-00942: fox 16-97 SQL*PLUS fox 16-97 SQL> connect fox/loveyou 391

16-97 16-98 fox cat baby_cat cat baby_cat SELECT 16-98 SQL> SELECT * 2 FROM cat.baby_cat; 16-98 FROM cat.baby_cat * ERROR 2 : ORA-00942: 1 cat WITH GRANT OPTION baby_cat pig 2 pig WITH GRANT OPTION cat baby_cat dog 3 cat baby_cat fox cat pig baby_cat baby_cat DBA CREATE USER ALTER USER ALTER USER ALTER USER IDENTIFIED BY miaomiao miaomiao guagua 392

cat 16-99 SQL*PLUS cat 16-99 SQL> connect cat/miaomiao 16-99 16-100 DDL cat guagua 16-100 SQL> ALTER USER cat 2 IDENTIFIED BY guagua; 16-100 16-101 SQL*PLUS dog 16-101 SQL> connect dog/wangwang 16-101 16-102 SQL*PLUS miaomiao cat 16-102 SQL> connect cat/miaomiao 16-102 ERROR ORA-01017: invalid username/password; logon denied : Oracle 16-102 cat miaomiao 16-103 SQL*PLUS guagua cat 16-103 SQL> connect cat/guagua; 393

16-103 DBA fox 16-104 SQL*PLUS system DBA 16-104 SQL> connect system/manager 16-104 16-105 DDL fox fox 16-105 SQL> ALTER USER fox 2 IDENTIFIED BY fox; 16-105 fox 16-106 SQL*PLUS fox 16-106 SQL> connect fox/fox; 16-106 fox ALTER USER fox DDL 16-108 fox 16-107 SQL*PLUS SYSTEM DBA 394

16-107 SQL> connect system/manager; 16-107 16-108 SQL> DROP USER fox; 16-108 16-109 SQL*PLUS fox fox 16-109 SQL> connect fox/fox; 16-109 ERROR ORA-01017: invalid username/password; logon denied : Oracle 16-109 fox/fox fox dba_users 16-110 SQL*PLUS SYSTEM DBA 16-110 SQL> connect system/manager 16-110 16-111 fox 16-111 SQL> SELECT username, created 2 FROM dba_users 3 WHERE ROUND(created, 'DAY') >= ROUND(SYSDATE, 'DAY') - 7; 395

16-111 USERNAME CREATED ------------------------------ ----------- DOG 29-4 -03 CAT 29-4 -03 PIG 30-4 03 16-111 fox. 16-111 SQL 16-112 DDL cat 16-112 SQL> DROP USER cat; 16-112 DROP USER cat * ERROR 1 : ORA-01922: CASCADE 'CAT' 16-112 cat baby_cat DROP USER CASCADE 16-113 DDL cat 16-113 SQL> DROP USER cat CASCADE; 16-113 16-113 cat 16-114 cat 16-114 SQL> SELECT username, created 2 FROM dba_users 3 WHERE ROUND(created, 'DAY') >= ROUND(SYSDATE, 'DAY') - 7; 16-114 USERNAME CREATED 396

------------------------------ ---------- DOG 29-4 -03 PIG 30-4 03 16-114 / / Oracle CONNECT RESOURCE Oracle CONNECT RESOURCE 16-115 SQL*PLUS SYS DBA 16-115 SQL> CONNECT SYS/Oracle AS SYSDBA 16-115 16-116 CONNECT RESOURCE 16-116 SQL> SELECT * 2 FROM role_sys_privs 3 WHERE role IN ('CONNECT', 'RESOURCE'); 16-116 ROLE PRIVILEGE ADM ---------- ------------------------- --- CONNECT ALTER SESSION NO CONNECT CREATE CLUSTER NO 397

CONNECT CREATE DATABASE LINK NO CONNECT CREATE SEQUENCE NO CONNECT CREATE SESSION NO CONNECT CREATE SYNONYM NO CONNECT CREATE TABLE NO CONNECT CREATE VIEW NO RESOURCE CREATE CLUSTER NO RESOURCE CREATE INDEXTYPE NO RESOURCE CREATE OPERATOR NO RESOURCE CREATE PROCEDURE NO RESOURCE CREATE SEQUENCE NO RESOURCE CREATE TABLE NO RESOURCE CREATE TRIGGER NO RESOURCE CREATE TYPE NO 16 DBA CONNECT CONNECT RESOURCE 16-117 CREATE USER cat miaomiao 16-117 SQL> CREATE USER cat 2 IDENTIFIED BY miaomiao; 16-117 16-118 DCL CONNECT cat 16-118 SQL> GRANT CONNECT TO cat; 16-118 cat SQL*PLUS 16-119 SQL*PLUS 16-119 SQL> connect cat/miaomiao; 16-119 398

SESSION_PRIVS cat 16-120 cat 16-120 SQL> SELECT * 2 FROM SESSION_PRIVS; 16-120 PRIVILEGE -------------------- CREATE SESSION ALTER SESSION CREATE TABLE CREATE CLUSTER CREATE SYNONYM CREATE VIEW CREATE SEQUENCE CREATE DATABASE LINK 8 16-120 cat 16-116 CONNECT 8 CONNECT RESOURCE 16-121 SQL*PLUS SYS DBA 16-121 SQL> CONNECT SYS/Oracle AS SYSDBA 16-121 16-122 CREATE USER fox devloper 16-122 SQL> CREATE USER fox 2 IDENTIFIED BY devloper; 16-122 16-123 DCL RESOURCE fox 399

16-123 SQL> GRANT RESOURCE TO fox; 16-123 fox SQL*PLUS 16-124 SQL*PLUS 16-124 SQL> connect fox/devloper; 16-124 ERROR ORA-01045: user FOX lacks CREATE SESSION privilege; logon denied 16-124 16-116 RESOURCE CREATE SESSION fox 16-125 SQL*PLUS SYS DBA 16-125 SQL> CONNECT SYS/Oracle AS SYSDBA 16-125 16-126 DCL CONNECT fox 16-126 SQL> GRANT CONNECT TO fox; 16-126 fox SQL*PLUS 16-127 SQL*PLUS 16-127 SQL> CONNECT FOX/DEVLOPER; 16-127 400

SESSION_PRIVS fox 16-128 fox 16-128 SQL> SELECT * 2 FROM SESSION_PRIVS; 16-128 PRIVILEGE -------------------- CREATE SESSION ALTER SESSION UNLIMITED TABLESPACE CREATE TABLE CREATE CLUSTER CREATE SYNONYM CREATE VIEW CREATE SEQUENCE CREATE DATABASE LINK CREATE PROCEDURE CREATE TRIGGER CREATE TYPE CREATE OPERATOR CREATE INDEXTYPE 14 CONNECT RESOURCE DBA CONNECT RESOURCE CONNECT RESOURCE / CONNECT 401

RESOURCE Oracle dba_users Oracle Role Role ROLE 8 GRANT WITH GRANT OPTION WITH GRANT OPTION DBA 402

SQL Structured Query Language SQL SQL Structured Query Language SQL Structured Query Language Data Manipulation Language - DML Data Definition Language - DDL Transaction Control Data Control Language DCL SELECT INSERT UPDATE DELETE CREATE ALTER TRUNCATE RENAME DROP COMMIT ROLLBACK GRANT REVOKE 1

S_CODE SNAME CONTACT PHONE FAX ------------- --------------------------- ------------------ -------------------- ---------- 2000 168 cat 4444844 2010 1741741 1741742 2021 1671671 1671674 2032 1681684 1681684 1 1 table row column value CONTACT table = entity = relation row = record column = attribute ORACLE SERVER =ORACLE =ORACLE SQL> SELECT * FROM supplier; SQL> ORACLE SQL*PLUS 2 SQL SQL*PLUS SQL> SELECT * 2 FROM supplier; SQL*PLUS SQL SQL S_CODE SNAME CONTACT PHONE FAX ------- ---------------- ------------ ---------- ------ 2000 168 cat 4444844 2010 1741741 1741742 2021 1671671 1671674 2032 1681684 1681684 404

Oracle SQL*PLUS SQL*PLUS SQL Oracle9i isql*plus SQL*PLUS Oracle Oracle SQL*PLUS SQL*PLUS Oracle IT Oracle ORACLE SQL*PLUS Oracle SQL*PLUS Windows 2 2 P Oracle OraHome90 3 3 405

Application Development; SQL Plus 4 4 Oracle Oracle Oracle ORACLE scott TIGER emp dept [U] scott [P] tiger 5 tiger 6 5 406

6 SQL*Plus SQL> SQL SQL* PLUS scott Oracle / / scott.sql scott Oracle8 $ORACLE_HOME\rdbms80\admin $ORACLE_HOME Oracle Oracle8i $ORACLE_HOME\rdbms\admin $Oracle_HOME Oracle Oracle $Oracle_HOME d:\oracle\ora90 Oracle Oracle9.01 d:\ oracle\ora90\rdbms\adminscott.sql SYSTEM/MANAGER SQL> SQL> @d:\ oracle\ora90\rdbms\adminscott.sql 407

Windows NT Windows 2000 Server Windows 2000 Professional Oracle8 128M CPU 500MHz 64M Oracle8i 128M 256M Oracle9i 256M 512M SQL Oracle7 Oracle9i SQL Oracle8i Oracle8 Windows Oracle Oracle Oracle Oracle Oracle Oracle9.01 Oracle9.01 Oracle8 Oracle8i Windows Oracle Windows Oracle setup Windows 7 7 8 408

8 9 Oracle D F F Oracle 10 11 10 100% 12 13 409

11 12 13 410

14 14 100% 15 15 16 17 Oracle 411

16 17 Oracle Oracle Oracle Oracle 8.17 ora817 Oracle Oracle / Oracle Oracle 412

1. Amoroso E. G. (1994). Fundamentals of Computer Security Technology. New Jersey, USA: P T R Prentice Hall, Inc. 2. Austin D., & Dyke R. van el. (2001). Oracle9i New Features for Administrators: Student Guide Volume 1 & 2. USA: Oracle Corporation. 3. Austin D., & Ernst B. el. (1999). Oracle8i New Features for Administrators: Instructor Guide Volume 1-3. USA: Oracle Corporation. 4. Barker R. (1994). CASE*METHOD: Entity Relationship Modelling. USA: R.R. Donnelley & Sons Company. 5. Chon S. & Green R. (1999). Data Warehousing Fundamentals: Student Guide Volume 1 & 2. USA: Oracle Corporation. 6. Couchman J. S. (2002). OCP Oracle9i SQL 7. Ernest B. & Rasmussen H. R. (1999). Enterprise DBA Part 1A: Architecture and Administration: Student Guide Volume 1 & 2. USA: Oracle Corporation. 8. Gossett S. & Jang S. (1999). Oracle8 Advanced Replication: Instructor Guide Volume 1&2. USA: Oracle Corporation. 9. Greenberg N. & Nathan P. (2001). Introduction to Oracle9i: SQL: Student Guide Volume 1 & 2. USA: Oracle Corporation. 10. Kochhar N. & Gravina E. (1998). Introduction to Oracle: SQL and PL/SQL: Student Guide Volume 1 & 2. USA: Oracle Corporation. 11. Kochhar N. & Kramer D. (1996). Introduction to Oracle: SQL and PL/SQL Using Procedure Builder: Participant Guide Volume 1-4. USA: Oracle Corporation. 12. Lorentz D. (2000). SQL Reference Release 3 (8.1.7). USA: Oracle Corporation. 13. Lorentz D. (2001). Oracle9i SQL Reference Release 1 (9.0.1). USA: Oracle Corporation. 14. Rob P. & Coronel C. (1993). Database Systems Design, Implementation, and Management. Belmont, California: Wadsworth Publishing Company. 15. Schwinn U. & Venkatachalam V. (1998). Oracle8 Database Administration: Student Guide Volume 1-3. USA: Oracle Corporation. 16. Watt S. (2001). isql*plus User s Guide and Reference Release 9.0.1. USA: Oracle Corporation. 17. Watt S. (2001). SQL*Plus Getting Started Release 9.0.1 for Windows. USA: Oracle

Corporation. 18. Watt S. (2001). SQL*Plus User s Guide and Reference Release 9.0.1. USA: Oracle Corporation. 414

Oracle SQL SQL Oracle SQL Oracle Oracle SQL ORACLE SQL SQL Oracle SQL Oracle

ORACLE Oracle Oracle Oracle SQL SQL Oracle SQL OCP / / / SQL Oracle OCP Oracle OCP Oracle7.x Oracle9i Oracle SQL Oracle Oracle sql_minghe@yahoo.com.cn Oracle SQL 416

GZ Comtech NZ LTD New Zealand Institute of Science and Technology Ltd - Unitec Institute of Technology - UNIX

1 2 3 4 5 6 7 8 2 5 6 10 10 9 DOS Windows OS/2 Macintosh Unix Linux 10 11 PC 12 CD-ROM (E-mail th_press@263.net) Oracle/SQL 100084 010 62791976/77 221 010 62788903 www.thjd.com.cn 15%