untitled

Transcription

1

2 Oracle SQL Oracle SQL OCP Oracle Oracle8 CIP Oracle/SQL / ISBN X... Oracle. TP CIP ISBN X/TP

3 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

4 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

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

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

7 Oracle Oracle Oracle Oracle SQL*PLUS Oracle Oracle Oracle SQL V

8 SQL SQL DISTINCT operators BETWEEN AND operators SQL IN operators LIKE operators escape ORDER BY ORDER BY ORDER BY ORDER BY ORDER BY SELECT SQL*PLUS DESC[RIBE] SET LINE[SIZE]{80 n} L LIST n text...36

9 3.4 / RUN n A[PPEND] del C[HANGE] SPOOL ROUND TRUNC NULL NULL NULL NULL AND OR BETWEEN AND IN NVL DECODE Oracle9i VIII

10 COUNT AVE SUM MIN MAX GROUP BY GROUP BY GROUP BY GROUP BY HAVING NVL NVL Normalization Primary Key Entity Integrity NF Foreign Key Referential Integrity NF NF Self join SQL: SQL: USING IX

11 7.20 ON ON WHERE HAVING FROM IN ALL ANY NULL Pairwise Comparison Nonpairwise Comparison SQL*PLUS SQL*PLUS SQL*PLUS ECHO SQL*PLUS FEEDBACK SQL*PLUS SQL*PLUS COLUMN SQL*PLUS X

12 & && DEFINE ACCEPT ACCEPT HIDE ACCEPT INSERT INSERT NULL INSERT ACCEPT INSERT UPDATE XI

13 12.11 DELETE DELETE Transactions Oracle Transactions COMMIT ROLLBACK DDL DCL SQL*PLUS AUTOCOMMIT Indexes and Constraints Oracle NOT NULL UNIQUE CHECK PRIMARY KEY FOREIGN KEY FOREIGN KEY INSERT FOREIGN KEY DELETE FOREIGN KEY UPDATE FOREIGN KEY DDL FOREIGN KEY ON DELETE SET NULL ON DELETE CASCADE XII

14 14 Views Views Views Views Views Oracle Views Views DML Views WITH CHECK OPTION WITH READ ONLY Views Inline Views n / Top n queries/analysis Sequence Synonym Sequence Sequence Sequence Sequence Sequence NEXTVAL CURRVAL Sequence Sequence Synonym Synonym Synonym Synonym Oracle Role XIII

15 16.7 Role CONNECT RESOURCE XIV

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

17 7 Oracle Equijoin Self join Non- equijoin Outer join 4 SQL:1999 SQL SQL: SQL 9 Oracle FORM REPORT Oracle Oracle 11 ACCEPT Oracle SQL*PLUS 10 DDL DDL 12 DML Transaction Control DML

18

19 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

20 1-1 SQL> SELECT * 2 FROM emp; 1-1 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO SMITH CLERK ? ALLEN SALESMAN ? WARD SALESMAN ? SQL SQL*PLUS * SELECT emp employee Oracle Unix C salary sal HIREDATE emp SELECT empno ename sal 1-2 5

21 1-2 SQL> SELECT empno, ename, sal 2 FROM emp; 1-2 EMPNO ENAME SAL SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER Project SELECT SQL> SELECT sal,ename,empno 2 FROM emp; 1-3 SAL ENAME EMPNO SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT

22 5000 KING TURNER ADAMS Keyword SELECT FROM Oracle SQL SELECT SEL SELEC FROM FRO FR SQL SQL> select Sal, EName, EMPNO 2 From Emp; 1-4 SAL ENAME EMPNO SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS Oracle 1-3 SQL SQL 1-5 7

23 1-5 SQL> select sal, ename, empno from emp; 1-5 SAL ENAME EMPNO SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS 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 SMITH CLERK 7499 ALLEN SALESMAN 7521 WARD SALESMAN 7566 JONES MANAGER 7654 MARTIN SALESMAN 7698 BLAKE MANAGER 7782 CLARK MANAGER 7788 SCOTT ANALYST 8

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

25 1-10 EMPNO ENAME SAL HIREDATE JOB SMITH DEC-80 CLERK 7499 ALLEN FEB-81 SALESMAN 7521 WARD FEB-81 SALESMAN 7566 JONES APR-81 MANAGER 7654 MARTIN SEP-81 SALESMAN 7698 BLAKE MAY-81 MANAGER 7782 CLARK JUN-81 MANAGER 7788 SCOTT APR-87 ANALYST 7839 KING NOV-81 PRESIDENT 7844 TURNER SEP-81 SALESMAN 7876 ADAMS MAY-87 CLERK hiredate SQL 4 + * / 500 / SQL> SELECT empno, ename, sal, 500+sal 2 FROM emp; 1-11 EMPNO ENAME SAL 500+SAL SMITH ALLEN WARD JONES MARTIN

26 7698 BLAKE CLARK SCOTT KING TURNER ADAMS SQL 1-12 SQL> SELECT empno, ename, 500+sal*12 2 FROM emp; 1-12 EMPNO ENAME 500+SAL* SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS SQL 11

27 1-13 SQL> SELECT empno, ename, (500+sal)*12 2 FROM emp; 1-13 EMPNO ENAME (500+SAL)* SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS Oracle 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 SMITH ALLEN WARD

28 7566 JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS 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 ALLEN annual salary is

29 WARD annual salary is JONES annual salary is MARTIN annual salary is BLAKE annual salary is CLARK annual salary is SCOTT annual salary is KING annual salary is TURNER annual salary is ADAMS annual salary is SQL> SELECT ename ' ' (500+sal)*12 " " 2 FROM emp; SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER literal SELECT 14

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

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

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

33 SQL> SELECT empno, ename, sal 2 FROM emp 3 WHERE sal >= 1500; 2-1 EMPNO ENAME SAL ALLEN JONES BLAKE CLARK SCOTT KING TURNER FORD empno ename sal 2-1 WHERE selection WHERE FROM WHERE

34 operators operator Oracle 6 operators > >= < <= = <> = Oracle 3 operators BETWEEN AND IN LIKE operators SQL> SELECT empno, ename, sal 2 FROM emp 3 WHERE sal BETWEEN 1500 AND 2900; 2-2 EMPNO ENAME SAL ALLEN BLAKE CLARK TURNER

35 2-2 BETWEEN BETWEEN AND AND 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 ALLEN FEB WARD FEB JONES APR MARTIN SEP BLAKE MAY CLARK JUN KING NOV TURNER SEP JAMES DEC FORD DEC MILLER JAN HIREDATE SQL> alter session set NLS_DATE_LANGUAGE = AMERICAN; 2-4 SQL 1 SQL BETWEEN NOT BETWEEN AND 20

36 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 SMITH DEC SCOTT APR ADAMS MAY-87 SQL salesman 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

37 2-7 SQL> SELECT empno, ename, job, sal 2 FROM emp 3 WHERE job ='SALESMAN'; 2-7 EMPNO ENAME JOB SAL ALLEN SALESMAN WARD SALESMAN MARTIN SALESMAN TURNER SALESMAN 1500 salesman IN SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE job IN ('SALESMAN', 'CLERK', 'MANAGER'); 2-8 EMPNO ENAME SAL JOB 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

38 IN SQL IN SQL JOB IN IN NOT NOT IN analyst SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE job NOT IN ('ANALYST', 'PRESIDENT'); 2-9 EMPNO ENAME SAL JOB 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

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

40 LIKE WHERE SQL> SELECT empno, ename, sal, hiredate 2 FROM emp 3 WHERE hiredate LIKE '%81'; 2-12 EMPNO ENAME SAL HIREDATE ALLEN FEB WARD FEB JONES APR MARTIN SEP BLAKE MAY CLARK JUN KING NOV TURNER SEP JAMES DEC FORD DEC % escape SQL 2-13 SQL> CREATE TABLE dept_temp 2 AS 3 SELECT * 4 FROM dept; SQL> INSERT INTO dept_temp 25

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

42 SQL ORDER BY SQL> SELECT empno, ename, sal 2 FROM emp 3 WHERE sal >= ORDER BY sal; 2-17 EMPNO ENAME SAL TURNER ALLEN CLARK BLAKE JONES SCOTT FORD KING SQL> SELECT empno, ename, sal 2 FROM emp 3 WHERE sal >= ORDER BY sal DESC; 2-18 EMPNO ENAME SAL KING

43 7788 SCOTT FORD JONES BLAKE CLARK ALLEN TURNER DESC ORDER BY ASC ascending order ASC SQL ASCII A Z DESC descending order ORDER BY ORDER BY SQL SQL 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 KING SCOTT

44 7902 FORD JONES BLAKE CLARK ALLEN TURNER MILLER WARD MARTIN ADAMS JAMES SMITH 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 KING SCOTT FORD JONES BLAKE CLARK ALLEN TURNER MILLER WARD MARTIN ADAMS JAMES SMITH ORDER BY 29

45 ORDER BY 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 KING SCOTT FORD JONES BLAKE CLARK ALLEN TURNER MILLER WARD MARTIN ADAMS JAMES SMITH SQL ORDER BY Oracle SQL ORDER BY ORDER BY SQL 30

46 A Z 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 SELECT SQL> SELECT ename, job, sal 2 FROM emp 31

47 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 WHERE ORDER BY SELECT * {[DISTINCT] } FROM [WHERE ] [ORDER BY { }[ASC DESC]] ORDER BY SQL 32

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

49 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

50 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 SQL*PLUS DESC[RIBE] SQL*PLUS SQL DESC[RIBE] SQL SQL*PLUS SET LINE[SIZE] {80 n} n 80 n SQL emp 3-3 SQL> SELECT * 2 FROM emp; 3-3 EMPNO ENAME JOB MGR HIREDATE SAL COMM 35

51 DEPTNO SMITH CLERK ALLEN SALESMAN WARD SALESMAN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO JONES MANAGER MARTIN SALESMAN BLAKE MANAGER SQL*PLUS SET LINE SQL> SET line SQL*PLUS 3-5 SQL 3-5 SQL> SELECT empno, ename, job, sal 2 FROM dept 3 WHERE sal >= ORDER BY job, sal DESC; 36

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

53 3-8 SQL Oracle SQL*PLUS / RUN SQL SQL 3-9 SQL*PLUS SQL 3-9 SQL> / 3-9 EMPNO ENAME JOB SAL SCOTT ANALYST FORD ANALYST JONES MANAGER BLAKE MANAGER CLARK MANAGER KING PRESIDENT ALLEN SALESMAN TURNER SALESMAN SQL*PLUS Oracle SQL*PLUS SQL> SELECT ename 2 FROM emp; 3-10 ENAME SMITH 38

54 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 SELECT ename 2* FROM emp * 3-11 SELECT ename SQL ename job, sal SQL> * SELECT ename 3-12 SQL 3-13 a job sal SELECT ename 3-13 SQL> a,job, sal

55 1* SELECT ename,job, sal 3-14 L 3-14 SQL> L SELECT ename,job, sal 2* FROM emp 3-14 / R SOL 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 SQL n n n 0 text SQL 40

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

57 3-20 SQL> / 3-20 EMPNO ENAME JOB SAL ALLEN SALESMAN JONES MANAGER BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT TURNER SALESMAN FORD ANALYST del m n del del m SQL*PLUS C[HANGE]/ / SQL 3-5 SQL SQL> SELECT empno, ename, job, sal 2 FROM dept 3 WHERE sal >= ORDER BY job, sal DESC; 3-21 WHERE sal >= 1500 * ERROR 3 : ORA-00904: 42

58 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> * FROM dept SQL*PLUS 3-24 SQL> C /dept/emp * FROM emp 3-24 SQL dept emp 3-25 L 3-25 SQL> l SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= * ORDER BY job, sal DESC 3-26 /

59 SQL> / 3-26 EMPNO ENAME JOB SAL SCOTT ANALYST FORD ANALYST JONES MANAGER BLAKE MANAGER CLARK MANAGER KING PRESIDENT ALLEN SALESMAN TURNER SALESMAN sal 3-27 SQL*PLUS SQL 3-27 SQL> * ORDER BY job, sal DESC 3-28 C job SQL 3-28 SQL> c /job,/ * ORDER BY sal DESC 3-29 L 3-29 SQL> l SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= * ORDER BY sal DESC 44

60 3-30 / SQL 3-30 SQL> / 3-30 EMPNO ENAME JOB SAL KING PRESIDENT SCOTT ANALYST FORD ANALYST JONES MANAGER BLAKE MANAGER CLARK MANAGER ALLEN SALESMAN TURNER SALESMAN C SQL> SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= ORDER BY job, sal DESC; 3-31 EMPNO ENAME JOB SAL SCOTT ANALYST FORD ANALYST JONES MANAGER BLAKE MANAGER CLARK MANAGER KING PRESIDENT

61 7499 ALLEN SALESMAN TURNER SALESMAN 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 SQL> L SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= * ORDER BY job, sal DESC 3-35 SQL SQL 3-35 SQL> SELECT * 46

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

63 SQL> L SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= * ORDER BY job, sal DESC C A n DEL SQL 3-39 / SQL 3-39 SQL> / 3-39 EMPNO ENAME JOB SAL SCOTT ANALYST FORD ANALYST JONES MANAGER BLAKE MANAGER CLARK MANAGER KING PRESIDENT ALLEN SALESMAN TURNER SALESMAN SQL*PLUS ed[it] D:\SQL\SAMPLE 3-40 SQL> ed D:\SQL\SAMPLE 3-40 D:\SQL\SAMPLE 48

64 3-41 SQL*PLUS D:\SQL\SAMPLE.sql EMPNO ENAME JOB SAL SCOTT ANALYST FORD ANALYST JONES MANAGER BLAKE MANAGER CLARK MANAGER KING PRESIDENT ALLEN SALESMAN TURNER SALESMAN 1500 START SQL SQL SQL SQL*PLUS SPOOL SQL SQL*PLUS SQL 3-42 SQL> SPOOL D:\SQL\OUTPUT 3-43 SQL> SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= ORDER BY job, sal DESC; 49

65 3-44 SQL> SPOOL OFF; D:\SQL\OUTPUT SQL> SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= ORDER BY job, sal DESC; EMPNO ENAME JOB SAL SCOTT ANALYST FORD ANALYST JONES MANAGER BLAKE MANAGER CLARK MANAGER KING PRESIDENT ALLEN SALESMAN TURNER SALESMAN 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

66 SQL SQL SQL SQL SQL SQL SPOOL 51

67 Oracle Oracle SQL SQL SQL SQL [ ] SELECT WHERE ORDER BY

68 LOWER UPPER INITCAP CONCAT SUBSTR LENGTH INSTR TRIM REPLACE LOWER 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

69 SQL> SELECT UPPER ('sql is used exclusively in rdbmses') 2 FROM dual; 4-2 UPPER('SQLISUSEDEXCLUSIVELYINRDBMSES') SQL IS USED EXCLUSIVELY IN RDBMSES INITCAP SQL> SELECT INITCAP('SQL is an ENGLISH LIKE language') 2 FROM dual; 4-3 INITCAP('SQLISANENGLISHLIKELANGUAGE') Sql Is An English Like Language CONCAT 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 SQL> SELECT SUBSTR('SQL lets you concentrate on what has to be done',14) 54

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

71 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 rows 4-10 SQL> SELECT TRIM('?' FROM 'It can process data in sets of rows??') 2 FROM dual;

72 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 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 SQL> SELECT REPLACE('SQL*PLUS supports loops or if statements', 'supports', 2 'does not support') 3 FROM dual; 57

73 4-13 REPLACE('SQL*PLUSSUPPORTSLOOPSORIFSTATEMENTS','SUPPORTS','DOESNOTSUPPORT') SQL*PLUS does not support loops or if statements SQL*PLUS 4-9 SQL*PLUS SQL Oracle SQL*PLUS 4-12 SQL*PLUS 4-13 SQL*PLUS SQL*PLUS SQL*PLUS SQL SQL*PLUS SQL> SELECT empno, ename, job 2 FROM emp 3 WHERE JOB = 'salesman'; 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

74 4-15 Code NAME Job ALLEN Salesman 7521 WARD Salesman 7654 MARTIN Salesman 7844 TURNER Salesman 4-15 LOWER(job) WHERE UPPER job ='SALESMAN' 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

75 MOD m n m n ROUND TRUNC 4-17 SQL> SELECT ROUND( ,1), TRUNC( ,1) 2 FROM dual; 4-17 ROUND( ,1) TRUNC( ,1) ROUND TRUNC 4-18 ROUND TRUNC 4-18 SQL> SELECT ROUND( ,2), TRUNC( ,2) 2 FROM dual; 4-18 ROUND( ,2) TRUNC( ,2) ROUND TRUNC 4-19 SQL> SELECT ROUND( ,0), TRUNC( ,0) 2 FROM dual; 4-19 ROUND( ,0) TRUNC( ,0) 60

76 ROUND TRUNC 4-20 SQL> SELECT ROUND( ), TRUNC( ) 2 FROM dual; 4-20 ROUND( ) TRUNC( ) ROUND TRUNC 4-21 SQL> SELECT ROUND( , -1), TRUNC( , -1) 2 FROM dual; 4-21 ROUND( ,-1) TRUNC( ,-1) MOD SQL> SELECT MOD(1900, 400) 2 FROM dual; 4-22 MOD(1900,400) MOD

77 SQL> SELECT MOD(2000, 400) 2 FROM dual; 4-23 MOD(2000,400) MOD SQL> SELECT MOD(300, 400) 2 FROM dual; 4-24 MOD(300,400) Oracle ROUND TRUNC ROUND TRUNC ROUND TRUNC Oracle Oracle Oracle9i DD-MON-RR DD-MON-YY 2000 Oracle

78 4-25 SQL 4-25 SQL> alter session set NLS_DATE_LANGUAGE = 'AMERICAN'; 4-25 Oracle SYSDATE SQL> SELECT SYSDATE 2 FROM dual; 4-26 SYSDATE MAY SQL> SELECT SYSDATE FROM dual; 4-27 SYSDATE APR

79 SQL> SELECT SYSDATE FROM dual; 4-28 SYSDATE MAY SQL> SELECT TO_DATE('15-JUL-02') - SYSDATE 2 FROM dual; 4-29 TO_DATE('15-JUL-02')-SYSDATE a SQL 4-29a SQL> SELECT TO_DATE(' ') - SYSDATE 2 FROM dual; 4-29a TO_DATE(' ')-SYSDATE TO_DATE Oracle

80 4-30 SQL> SELECT SYSDATE - 22/24 2 FROM dual; 4-30 SYSDATE MAY SQL> SELECT SYSDATE + 22/24 2 FROM dual; 4-31 SYSDATE MAY 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 ALLEN SALESMAN WARD SALESMAN MARTIN SALESMAN TURNER SALESMAN MONTHS_BETWEEN ADD_MONTHS NEXT_DAY LAST_DAY 65

81 MONTHS_BETWEEN SQL> SELECT MONTHS_BETWEEN('01-JUL-99','03-FEB-98') 2 FROM dual; 4-33 MONTHS_BETWEEN('01-JUL-99','03-FEB-98') ADD_MONTHS n n SQL> SELECT ADD_MONTHS('15-OCT-01', 8) 2 FROM dual; 4-34 ADD_MONTH JUN NEXT_DAY SQL> SELECT NEXT_DAY('10-MAY-02','MONDAY') 2 FROM dual; 4-35 NEXT_DAY(

82 13-MAY LAST_DAY SQL> SELECT LAST_DAY('08-FEB-02') 2 FROM dual; 4-36 LAST_DAY( FEB 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 DEC DEC MAR-81 ALLEN 20-FEB FEB FEB MAY-81 WARD 22-FEB FEB MAR MAY-81 JONES 02-APR APR APR JUL-81 MARTIN 28-SEP SEP OCT DEC-81 BLAKE 01-MAY MAY MAY AUG-81 CLARK 09-JUN JUN JUN SEP-81 SCOTT 19-APR APR APR JUL-87 KING 17-NOV NOV NOV FEB-82 TURNER 08-SEP SEP SEP DEC-81 ADAMS 23-MAY MAY MAY AUG SUNDAY SQL

83 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 ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER 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

84 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_ NOV MONTH ROUND TO_DATE ROUND 4-41 SQL> SELECT ROUND(TO_DATE('28-OCT-01'),'YEAR') 2 FROM dual; 4-41 ROUND(TO_ JAN YEAR ROUND TO_DATE TRUNC 4-42 SQL> SELECT TRUNC(TO_DATE('28-OCT-01'),'MONTH') 2 FROM dual;

85 TRUNC(TO_ OCT MONTH TRUNC TO_DATE TRUNC 4-43 SQL> SELECT TRUNC(TO_DATE('28-OCT-01'),'YEAR') 2 FROM dual; 4-43 TRUNC(TO_ JAN YEAR TRUNC 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 JAN JAN MAR FEB-81 WARD 22-FEB JAN JAN MAR FEB-81 JONES 02-APR JAN JAN APR APR-81 MARTIN 28-SEP JAN JAN OCT SEP-81 BLAKE 01-MAY JAN JAN MAY MAY-81 CLARK 09-JUN JAN JAN JUN JUN-81 KING 17-NOV JAN JAN DEC NOV-81 TURNER 08-SEP JAN JAN SEP SEP-81 70

86 JAMES FORD DEC JAN JAN DEC DEC DEC JAN JAN DEC 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

87 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/ ' DD/MM/YY ' YYYY 2001 YEAR NINETEEN EIGHTY-SEVEN MM MONTH DY 3 DAY DD SP TH MAY TWO THOUSAND TWO 4-46 SQL> SELECT TO_CHAR(SYSDATE,'fmDD MONTH YEAR') 2 FROM dual; 4-46 TO_CHAR(SYSDATE,'FMDDMONTHYEAR') MAY TWO THOUSAND TWO fm HIREDATE 4-47 SQL*PLUS HIREDATE 60 72

88 4-47 SQL> col hiredate for a 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 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

89 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, ALLEN $19, WARD $15, JONES $35, MARTIN $15, BLAKE $34, CLARK $29, SCOTT $36, KING $60, TURNER $18, ADAMS $13, JAMES $11, FORD $36, MILLER $15, L Oracle RMB SQL> SELECT ename "Name", TO_CHAR(sal*12, 'L99,999.00') "Annual Salary" 2 FROM emp; 4-50 Name Annual Salary SMITH RMB9, ALLEN RMB19, WARD RMB15, JONES RMB35,

90 MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER 14 RMB15, RMB34, RMB29, RMB36, RMB60, RMB18, RMB13, RMB11, RMB36, RMB15, 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, ALLEN ################### WARD ################### JONES ################### MARTIN ################### BLAKE ################### CLARK ################### SCOTT ################### KING ################### TURNER ################### ADAMS ################### JAMES ################### FORD ################### MILLER ################### 14 # TO_CHAR TO_NUMBER TO_DATE TO_NUMBER [ fmt] 75

91 TO_DATE [ fmt] TO_DATE TO_NUMBER Oracle RR RR 1 0~49 0~ OCT-08 RR YY ~49 50~ OCT-98 RR YY ~99 0~ OCT-08 RR YY ~99 50~ OCT-98 RR YY Name Salary hiredate 76

92 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 FEB-81 WARD SALESMAN FEB-81 JONES MANAGER APR-81 BLAKE MANAGER MAY-81 CLARK MANAGER JUN-81 TURNER SALESMAN SEP-81 MARTIN SALESMAN SEP-81 KING PRESIDENT NOV-81 JAMES CLERK DEC-81 FORD ANALYST DEC SQL> SELECT ename "Name", job, sal AS "Salary", hiredate 2 FROM emp 3 WHERE hiredate BETWEEN ' ' AND ' ' 4 ORDER BY hiredate; 4-53 Name JOB Salary HIREDATE ALLEN SALESMAN WARD SALESMAN JONES MANAGER BLAKE MANAGER CLARK MANAGER TURNER SALESMAN MARTIN SALESMAN KING PRESIDENT JAMES CLERK

93 FORD ANALYST 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 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 FEB-81 WARD SALESMAN FEB-81 JONES MANAGER APR-81 BLAKE MANAGER MAY-81 CLARK MANAGER JUN-81 TURNER SALESMAN SEP-81 MARTIN SALESMAN SEP-81 KING PRESIDENT NOV-81 JAMES CLERK DEC-81 FORD ANALYST DEC

94 4-52 Oracle9i RR Oracle dual SQL SQL*PLUS TO_CHAR ROUND TRUNC TO_CHAR RR YY 79

95 Oracle SQL NULL 3 NULL 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 WARD SALESMAN MARTIN SALESMAN TURNER SALESMAN COMM CLERK COMM SALESMAN CLERK CLERK Oracle NULL NULL NULL unavailable unassigned undefined unknown immeasurable inapplicable NULL

96 BMW NULL NULL NULL NULL 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

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

98 5-5 SQL> SELECT empno, ename, sal, job, comm 2 FROM emp 3 WHERE comm IS NULL; 5-5 EMPNO ENAME SAL JOB COMM 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 SQL> SELECT empno, ename, sal, job, comm 2 FROM emp 3 WHERE comm IS NOT NULL; 5-6 EMPNO ENAME SAL JOB COMM ALLEN 1600 SALESMAN WARD 1250 SALESMAN MARTIN 1250 SALESMAN TURNER 1500 SALESMAN NULL 5-7 ORDER BY 83

99 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 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

100 JAMES CLERK MILLER CLERK FORD ANALYST ADAMS CLERK MARTIN SALESMAN 1400 WARD SALESMAN 500 ALLEN SALESMAN 300 TURNER SALESMAN NULL Oracle AND / OR / NOT 3 AND / OR / 1 2 AND OR 1 2 = T F Oracle NULL 1AND 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

101 F T AND T PR Oracle NULL T AND NULL = NULL AND SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE sal >= AND job IN ('SALESMAN', 'CLERK', 'MANAGER') 5 ORDER BY job; 5-9 EMPNO ENAME SAL JOB 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

102 T NULL F OR T T T OR NULL NULL T F OR F PR 5-9 WHERE AND OR SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE sal >= OR job IN ('SALESMAN', 'CLERK', 'MANAGER') 5 ORDER BY job; 5-10 EMPNO ENAME SAL JOB 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

103 NOT T = F NOT F = T Oracle NULL NOT NULL NOT NULL NULL NOT NOT LIKE IS NOT NULL NOT BETWEEN AND IS NULL IS NOT NULL LIKE NOT LIKE IN NOT IN 5 BETWEEN NOT BETWEEN 6 NOT 7 AND 8 OR 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 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

104 WHERE 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 ALLEN 1600 SALESMAN 7844 TURNER 1500 SALESMAN 7934 MILLER 1300 CLERK Oracle CLERK SALESMAN 1300 Oracle 1300 CLERK SALESMAN SQL> SELECT empno, ename, sal 2 FROM emp 3 WHERE sal BETWEEN 1500 AND 2900; 5-13 EMPNO ENAME SAL ALLEN BLAKE CLARK TURNER

105 SQL> SELECT empno, ename, sal 2 FROM emp 3 WHERE sal >= AND sal <= 2900; 5-14 EMPNO ENAME SAL ALLEN BLAKE CLARK TURNER SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE job IN ('SALESMAN', 'CLERK', 'MANAGER'); 5-15 EMPNO ENAME SAL JOB 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 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE job = 'SALESMAN' 4 OR job = 'CLERK' 90

106 5 OR job = 'MANAGER'; 5-16 EMPNO ENAME SAL JOB 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 AND OR BETWEEN AND IN NVL 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

107 MILLER JONES BLAKE CLARK KING 10 CLERK MANAGER MANAGER MANAGER PRESIDENT sal+comm comm NULL Income Oracle NVL 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 NVL NULL NVL NULL NVL 2 1 NULL NVL comm 0 NVL TO_CHAR(comm) No Commission NVL hiredate 31-DEC-99 92

108 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 FORD ANALYST SMITH CLERK ADAMS CLERK MILLER CLERK JAMES CLERK JONES MANAGER CLARK MANAGER BLAKE MANAGER KING PRESIDENT ALLEN SALESMAN MARTIN SALESMAN TURNER SALESMAN WARD SALESMAN DECODE 1 job SALESMAN DECODE sal*

109 2 job CLERK DECODE sal* job ANALYST DECODE sal* DECODE sal*1.40 Oracle Oracle Oracle 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

110 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 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! Oracle

111 NVL2 NULLIF COALESCE Oracle9i CASE Oracle9i SQL SQL NVL2 Oracle9i NVL2 NVL 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 NVL2 comm,'sal+comm',sal "Income" 1 NVL2 comm 2 comm NULL sal+comm 3 comm NULL sal 96

112 4 Income NVL2 NVL NVL NULL NVL2 2 1 NULL NVL LONG Oracle Oracle NVL2 2 2 CHAR NVL2 VARCHAR2 NULLIF Oracle9i 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 WARD SALESMAN JONES MANAGER MARTIN SALESMAN BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT TURNER SALESMAN ADAMS CLERK 5 5 JAMES CLERK 5 5 FORD ANALYST MILLER CLERK NULLIF LENGTH ename LENGTH job NULLIF LENGTH ename 97

113 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 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); SQL emp_null ename QUEEN sal comm SQL> SELECT * FROM emp_null; 5-26 ENAME SAL COMM

114 SMITH 800 ALLEN WARD JONES 2975 MARTIN BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER ADAMS 1100 JAMES 950 FORD 3000 MILLER 1300 QUEEN 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 ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER

115 QUEEN 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 n n CASE Oracle9i DECODE CASE SQL> SELECT ename "Name", job, sal "Salary", 2 CASE job WHEN 'SALESMAN' THEN sal* WHEN 'CLERK' THEN sal* WHEN 'ANALYST' THEN sal* ELSE sal*1.40 END "New Salary" 6 FROM emp 7 ORDER BY job; 5-28 Name JOB Salary New Salary SCOTT ANALYST FORD ANALYST SMITH CLERK ADAMS CLERK MILLER CLERK JAMES CLERK JONES MANAGER CLARK MANAGER BLAKE MANAGER KING PRESIDENT

116 ALLEN SALESMAN MARTIN SALESMAN TURNER SALESMAN WARD SALESMAN 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

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

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

119 AVE SUM MAX MIN MAX MAX [DISTINCT ALL] MIN MIN [DISTINCT ALL] SQL> SELECT MIN(sal) "Lowest Salary", MAX(sal) "Highest Salary" 2 FROM emp; 6-4 Lowest Salary Highest Salary 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 SQL> SELECT MIN(hiredate) "First Day", MAX(hiredate) "Last Day" 104

120 2 FROM emp; 6-6 First Day Last Day GROUP BY job SQL> SELECT job, AVG(sal) "Average Salary" 2 FROM emp 3 GROUP BY job; 6-7 job Average Salary ANALYST 3000 CLERK MANAGER PRESIDENT 5000 SALESMAN president analyst job clerk 6-7 GROUP BY GROUP BY Oracle ORDER BY 6-8 job 105

121 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 SALESMAN 1400 CLERK 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

122 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 SQL> SELECT job, AVG(sal) 2 FROM emp 3 GROUP BY job; 6-11 job AVG(SAL) ANALYST 3000 CLERK MANAGER

123 PRESIDENT 5000 SALESMAN 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 ORDER BY deptno * ERROR 4 : ORA-00979: GROUP BY SQL> SELECT job, AVG(sal) 2 FROM emp 3 WHERE AVG(sal) > GROUP BY job; 6-13 WHERE AVG(sal) > 1500 * ERROR 3 : ORA-00934: 6-13 WHERE Oracle HAVING

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

125 6-7 job job job job job 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)) job job job 6-16 Oracle Oracle emp job PRESI 2 job 3 4 SQL GROUP BY Oracle COUNT * NULL 110

126 SQL> SELECT AVG(comm) "Average Commission" 2 FROM emp; 6-17 Average Commission 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 SALESMAN Oracle 2200/ = 550 NVL SQL 6-19 SQL> SELECT AVG(NVL(comm, 0)) "Average Commission" 111

127 2 FROM emp; 6-19 Average Commission 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 ANALYST CLERK MANAGER PRESIDENT SALESMAN / = NVL 6-19 NVL

128 Oracle STDDEV VARIANCE Oracle9i SQL Reference GROUP BY HAVING WHERE MIN MAX DSS Decision Support System 5 MIN MAX GROUP BY GROUP BY GROUP BY 113

129 Oracle Normalization Normalization Normalization XX XX XX XXXX XX XX XX XX XX

130 Primary Key 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 Order

131 Order 7-3 Order 1NF 1NF 1NF 1 2 Repeating Groups Order

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

133 1 NULL 2 Foreign Key Oracle Referential Integrity Repeating Groups 2NF 7-5 Order 2NF 2NF 1 1NF 2 1NF 2NF 7-5 Order Order 8 Order Order

134 Transitive Dependency Supplier Supplier Order Order 7-8 Supplier 7-9 Product Repeating Group 3NF Order Supplier Product 3NF 119

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

136 7-1 EMPNO ENAME SAL DEPTNO LOC ALLEN CHICAGO 7521 WARD CHICAGO 7654 MARTIN CHICAGO 7900 JAMES CHICAGO 7844 TURNER CHICAGO 7698 BLAKE CHICAGO 7369 SMITH DALLAS 7902 FORD DALLAS 7876 ADAMS DALLAS 7566 JONES DALLAS 7788 SCOTT DALLAS 7782 CLARK NEW YORK 7839 KING NEW YORK 7934 MILLER NEW YORK deptno FROM FROM emp dept WHERE WHERE emp.deptno = dept.deptno n n 1 Oracle 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 >= ORDER BY loc;

137 EMPNO ENAME SAL DEPTNO LOC ALLEN CHICAGO 7698 BLAKE CHICAGO 7844 TURNER CHICAGO 7566 JONES DALLAS 7902 FORD DALLAS 7788 SCOTT DALLAS 7782 CLARK NEW YORK 7839 KING NEW YORK WHERE AND 7-2 INVOICE-DETAILS Oracle 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 >= ORDER BY d.loc; 7-3 EMPNO ENAME SAL DEPTNO LOC ALLEN CHICAGO 7698 BLAKE CHICAGO 7844 TURNER CHICAGO 7566 JONES DALLAS 7902 FORD DALLAS 7788 SCOTT DALLAS 7782 CLARK NEW YORK 7839 KING NEW YORK 8 FROM 122

138 WHERE 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 SMITH BOSTON 7499 ALLEN BOSTON 7521 WARD BOSTON 7698 BLAKE BOSTON 7782 CLARK BOSTON 7654 MARTIN BOSTON 7566 JONES BOSTON 7788 SCOTT BOSTON 7844 TURNER BOSTON 7934 MILLER BOSTON 7902 FORD BOSTON 7900 JAMES BOSTON 7876 ADAMS BOSTON 7839 KING BOSTON 7369 SMITH CHICAGO 7499 ALLEN CHICAGO 7521 WARD CHICAGO 7566 JONES CHICAGO 7788 SCOTT CHICAGO 7844 TURNER CHICAGO 7900 JAMES CHICAGO 7844 TURNER NEW YORK 7876 ADAMS NEW YORK 7900 JAMES NEW YORK 123

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

140 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 SCOTT ANALYST 7566 JONES MANAGER 7902 FORD ANALYST 7566 JONES MANAGER Oracle Self join Self join SQL> SELECT ename, job, mgr 2 FROM emp 3 WHERE job LIKE 'ANA%'; 7-8 ENAME JOB MGR SCOTT ANALYST 7566 FORD ANALYST SQL> SELECT empno, ename, job 2 FROM emp 3 WHERE empno = 7566; 7-9 EMPNO ENAME JOB 125

141 JONES MANAGER MANAGER MANAGER EMP SQL> CREATE TABLE manager 2 AS 3 SELECT * 4 FROM emp; 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 SCOTT ANALYST 7566 JONES MANAGER 7902 FORD ANALYST 7566 JONES MANAGER Self join manager manager emp emp manager 126

142 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 SCOTT ANALYST JONES DALLAS 7902 FORD ANALYST JONES DALLAS 7369 SMITH CLERK FORD DALLAS 7900 JAMES CLERK BLAKE CHICAGO 7876 ADAMS CLERK SCOTT DALLAS 7934 MILLER CLERK CLARK NEW YORK emp w.mgr w.mgr manager deptno dept job CLERK ANALYST job sal = salgrade SQL*PLUS DESC SQL> DESC salgrade

143 ? GRADE NUMBER LOSAL NUMBER HISAL NUMBER SQL> SELECT * FROM salgrade; 7-14 GRADE LOSAL HISAL salgrade ~ 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 ALLEN SALESMAN TURNER SALESMAN JONES MANAGER BLAKE MANAGER CLARK MANAGER SCOTT ANALYST FORD ANALYST

144 7839 KING PRESIDENT BETWEEN AND = 7-16 dept 7-16 SQL> SELECT * 2 FROM dept; 7-16 DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON deptno emp deptno deptno 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 CLARK NEW YORK 7839 KING NEW YORK 7934 MILLER NEW YORK 7369 SMITH DALLAS 7876 ADAMS DALLAS 129

145 7902 FORD DALLAS 7788 SCOTT DALLAS 7566 JONES DALLAS 7499 ALLEN CHICAGO 7698 BLAKE CHICAGO 7654 MARTIN CHICAGO 7900 JAMES CHICAGO 7844 TURNER CHICAGO 7521 WARD CHICAGO 40 BOSTON BOSTON + emp.deptno SQL> SELECT empno, ename, sal, emp.deptno, dept.deptno, loc 2 FROM emp, dept 3 WHERE dept.deptno = emp.deptno(+); SQL*PLUS Oracle9i ANSI SQL SQL 1999 SQL SQL 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

146 SQL 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 ALLEN CHICAGO 7521 WARD CHICAGO 7654 MARTIN CHICAGO 7900 JAMES CHICAGO 7844 TURNER CHICAGO 7698 BLAKE CHICAGO 7369 SMITH DALLAS 7902 FORD DALLAS 7876 ADAMS DALLAS 7566 JONES DALLAS 7788 SCOTT DALLAS 7782 CLARK NEW YORK 7839 KING NEW YORK 7934 MILLER NEW YORK USING NATURAL JOIN USING 7-20 deptno USING 131

147 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 ALLEN CHICAGO 7521 WARD CHICAGO 7654 MARTIN CHICAGO 7900 JAMES CHICAGO 7844 TURNER CHICAGO 7698 BLAKE CHICAGO 7369 SMITH DALLAS 7902 FORD DALLAS 7876 ADAMS DALLAS 7566 JONES DALLAS 7788 SCOTT DALLAS 7782 CLARK NEW YORK 7839 KING NEW YORK 7934 MILLER NEW YORK 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

148 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 SCOTT ANALYST JONES DALLAS 7902 FORD ANALYST JONES DALLAS 7369 SMITH CLERK FORD DALLAS 7900 JAMES CLERK BLAKE CHICAGO 7876 ADAMS CLERK SCOTT DALLAS 7934 MILLER CLERK CLARK NEW YORK SQL WHERE AND 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 SCOTT ANALYST JONES DALLAS 7902 FORD ANALYST JONES DALLAS 7369 SMITH CLERK FORD DALLAS 133

149 7900 JAMES CLERK BLAKE CHICAGO 7876 ADAMS CLERK SCOTT DALLAS 7934 MILLER CLERK CLARK NEW YORK WHERE AND 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 SMITH DALLAS 7499 ALLEN CHICAGO 7521 WARD CHICAGO 7566 JONES DALLAS 7654 MARTIN CHICAGO 7698 BLAKE CHICAGO 7782 CLARK NEW YORK 7788 SCOTT DALLAS 7839 KING NEW YORK 7844 TURNER CHICAGO 7876 ADAMS DALLAS 7900 JAMES CHICAGO 7902 FORD DALLAS 7934 MILLER NEW YORK 40 BOSTON SQL

150 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 SMITH DALLAS 7499 ALLEN CHICAGO 7521 WARD CHICAGO 7566 JONES DALLAS 7654 MARTIN CHICAGO 7698 BLAKE CHICAGO 7782 CLARK NEW YORK 7788 SCOTT DALLAS 7839 KING NEW YORK 7844 TURNER CHICAGO 7876 ADAMS DALLAS 7900 JAMES CHICAGO 7902 FORD DALLAS 7934 MILLER NEW YORK 40 BOSTON 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

151 7-10 manager 30 NULL 7-27 SQL SQL DML 7-27 SQL> update manager 2 set deptno = NULL 3 WHERE deptno = 30; SQL> commit; 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 MILLER NEW YORK 7839 KING NEW YORK 7782 CLARK NEW YORK 7902 FORD DALLAS 7876 ADAMS DALLAS 7788 SCOTT DALLAS 7566 JONES DALLAS 7369 SMITH DALLAS 7900 JAMES

152 7844 TURNER BLAKE MARTIN WARD ALLEN manager manager dept deptno 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 SMITH DALLAS 7566 JONES DALLAS 7782 CLARK NEW YORK 7788 SCOTT DALLAS 7839 KING NEW YORK 7876 ADAMS DALLAS 7902 FORD DALLAS 7934 MILLER NEW YORK 30 CHICAGO 40 BOSTON manager dept dept manager 137

153 deptno 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 MILLER NEW YORK 7839 KING NEW YORK 7782 CLARK NEW YORK 7902 FORD DALLAS 7876 ADAMS DALLAS 7788 SCOTT DALLAS 7566 JONES DALLAS 7369 SMITH DALLAS 7900 JAMES TURNER BLAKE MARTIN WARD ALLEN CHICAGO 40 BOSTON manager dept SQL:1999 Oracle SQL: 1999 SQL Oracle 138

154 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

155 SMITH SMITH job SMITH job SMITH 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 SMITH 800 CLERK 7876 ADAMS 1100 CLERK 7900 JAMES 950 CLERK 7934 MILLER 1300 CLERK 8-2

156 Main query Subquery 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 SMITH 800 CLERK 7876 ADAMS 1100 CLERK 7900 JAMES 950 CLERK 7934 MILLER 1300 CLERK Subquery Inner query Main query Outer query Oracle Oracle SMITH job CLERK Main query CLERK WHERE > >= < <= = <> = WHERE HAVING FROM 141

157 ORDER BY 8-3 Primary Key job SMITH ADAMS 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 SMITH 800 CLERK 7876 ADAMS 1100 CLERK 7900 JAMES 950 CLERK 8-4 ADAMS 1100 CLERK ADAMS 8-5 SQL

158 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 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 GROUP BY GROUP BY Oracle 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 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE ename = 143

159 4 (SELECT ename 5 FROM emp 6 WHERE ename = 'ADAM'); 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 MANAGER SALESMAN CLERK 144

160 CLERK job 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 JONES 2975 MANAGER BLAKE 2850 MANAGER ALLEN 1600 SALESMAN TURNER 1500 SALESMAN IN ANY ALL 145

161 CLERK PRESIDENT job 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 ALLEN SALESMAN JONES MANAGER SCOTT ANALYST FORD ANALYST 3000 IN IN SQL> SELECT MAX(sal) 2 FROM emp 3 GROUP BY job; 8-11 MAX(SAL)

162 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 ALLEN SALESMAN JONES MANAGER SCOTT ANALYST FORD ANALYST SQL 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 ALLEN SALESMAN JONES MANAGER SCOTT ANALYST FORD ANALYST 3000 JOB

163 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 SMITH 800 CLERK 7900 JAMES 950 CLERK ALL < ALL > ALL = ALL 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 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

164 8-16 EMPNO ENAME SAL JOB 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 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 SCOTT 3000 ANALYST 7902 FORD 3000 ANALYST 7839 KING 5000 PRESIDENT = ANY IN SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE sal IN 4 (SELECT AVG(sal) 149

165 5 FROM emp 6 GROUP BY job); 8-18 EMPNO ENAME SAL JOB SCOTT 3000 ANALYST 7902 FORD 3000 ANALYST 7839 KING 5000 PRESIDENT 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); NULL NULL 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);

166 8-20 NOT IN < >ALL NULL NULL WHERE WHERE w.mgr IS NOT NULL 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); NULL NULL 8-21 WHERE WHERE e.mgr IN 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 SCOTT 3000 ANALYST 7902 FORD 3000 ANALYST 7499 ALLEN 1600 SALESMAN 7521 WARD 1250 SALESMAN 7900 JAMES 950 CLERK 7844 TURNER 1500 SALESMAN 151

167 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 SQL 8-23 SQL> UPDATE manager 2 SET sal = WHERE empno = 7521; SQL> UPDATE manager 2 SET sal = WHERE empno = 7782;

168 SQL> commit; job 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 MILLER 1300 CLERK 7499 ALLEN 1600 SALESMAN 7566 JONES 2975 MANAGER 7788 SCOTT 3000 ANALYST 7902 FORD 3000 ANALYST 7839 KING 5000 PRESIDENT job job job job job

169 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 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 job job job > >= < <= = <> IN ALL ANY WHERE HAVING FROM GROUP BY ORDER BY NULL 154

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

171 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 SQL> SELECT * FROM dept; 9-2 DEPTNO DNAME LOC

172 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 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 Oracle\ming\echo_sample 9-4 DEPTNO DNAME LOC 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 SQL> set echo on SHOW SQL*PLUS ECHO SQL> SHOW ECHO

173 echo ON 9-7 SQL*PLUS d:\ Oracle\ming\ echo_sample 9-7 Oracle\ming\echo_sample 9-7 SQL> SELECT * FROM dept 2 / DEPTNO DNAME LOC 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 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

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

175 ALLEN 1600 JONES 2975 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 FORD 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

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

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

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

179 30 SALES CHICAGO 40 OPERATIONS BOSTON COLUMN CLEAR SQL*PLUS COL loc An 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 SAL 9-26 SQL*PLUS SAL 9-26 SQL> col sal for $99, $, SQL> SELECT ename, job, sal 2 FROM emp 3 WHERE sal >= 1500; 9-27 ENAME JOB SAL 164

180 ALLEN SALESMAN $1, JONES MANAGER $2, BLAKE MANAGER $2, CLARK MANAGER $2, SCOTT ANALYST $3, KING PRESIDENT $5, TURNER SALESMAN $1, FORD ANALYST $3, COLUMN SQL> col sal for $009, SQL> SELECT ename, job, sal 2 FROM emp 3 WHERE sal >= 1500; 9-29 ENAME JOB SAL ALLEN SALESMAN $001, JONES MANAGER $002, BLAKE MANAGER $002, CLARK MANAGER $002, SCOTT ANALYST $003, KING PRESIDENT $005, TURNER SALESMAN $001, FORD ANALYST $003, COLUMN 0 0 COLUMN L SQL> col sal for L99,

181 9-31 SQL> SELECT ename, job, sal 2 FROM emp 3 WHERE sal >= 1500; 9-31 ENAME JOB SAL ALLEN SALESMAN RMB1, JONES MANAGER RMB2, BLAKE MANAGER RMB2, CLARK MANAGER RMB2, SCOTT ANALYST RMB3, KING PRESIDENT RMB5, TURNER SALESMAN RMB1, FORD ANALYST RMB3, 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 REM *** This is an employees' salary report for senior management team *** REM *** Strictly Confidential *** SET PAGESIZE 25 SET LINESIZE 80 SET FEEDBACK OFF 166

182 TTITLE '====== Sun_Moon IT Company ====== ===== Employee Salary Report =====' BTITLE 'Strictly Confidential!!!' BREAK ON deptno SKIP 2 COLUMN deptno HEADING 'Department Number' JUSTIFY CENTER FORMAT COLUMN job HEADING 'Job Category' FORMAT A15 COLUMN AVG(sal) HEADING 'Average Salary' FORMAT L99, COLUMN COUNT(sal) HEADING 'Employee Number' JUSTIFY CENTER FORMAT 999 COLUMN SUM(sal) HEADING 'Summary Salary' FORMAT L99, SELECT deptno, job, AVG(sal), COUNT(sal), SUM(sal) FROM emp GROUP BY deptno, job / SET FEEDBACK ON REM Oracle 2 SET PAGESIZE SET LINESIZE 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

183 9-32 report.sql d:\ Oracle\ming Oracle\ming\report ======= Sun_Moon IT Company ====== ===== Employee Salary Report ===== Department Job Average Employee Summary Number Category Salary Number Salary CLERK RMB1, RMB1, MANAGER RMB2, RMB2, PRESIDENT RMB5, RMB5, ANALYST RMB3, RMB6, CLERK RMB RMB1, MANAGER RMB2, RMB2, CLERK RMB RMB MANAGER RMB2, RMB2, SALESMAN RMB1, RMB5, Strictly Confidential!!! 9-33 SQL SQL SQL SQL SQL*PLUS SQL / SQL*PLUS SQL*PLUS 168

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

185 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 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?

186 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) user_objects OWNER all_objects dba_objects OWNER user_objects OWNER user_* all_* dba_* *_tables *_indexes SYSTEM SCOTT

187 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 VALID SCOTT DEPT VALID SCOTT EMP VALID OWNER OBJECT_NAME OBJECT_ID CREATED STATUS SCOTT EMP_NULL VALID SCOTT MANAGER VALID SCOTT PK_DEPT VALID OWNER OBJECT_NAME OBJECT_ID CREATED STATUS SCOTT PK_EMP VALID SCOTT SALGRADE VALID 8 172

188 9-38 SQL*PLUS COLUMN owner object_name object_type 9-39 SQL> col owner for a8 SQL> col object_name for a12 SQL> col object_type for a 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 VALID TABLE SCOTT DEPT VALID TABLE SCOTT EMP VALID TABLE SCOTT EMP_NULL VALID TABLE SCOTT MANAGER VALID TABLE SCOTT PK_DEPT VALID INDEX SCOTT PK_EMP VALID INDEX SCOTT SALGRADE VALID TABLE Oracle user_tables 9-41 SCOTT 173

189 9-41 SQL> SELECT table_name 2 FROM user_tables; 9-41 TABLE_NAME BONUS DEPT EMP EMP_NULL MANAGER SALGRADE 6 all_tables 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

190 SQL> desc user_catalog 9-43? TABLE_NAME NOT NULL VARCHAR2(30) TABLE_TYPE VARCHAR2(11) 9-43 user_catalog user_tables user_catalog 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 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

191 SALGRADE 7 TABLE Oracle Oracle v$database v$ Oracle Oracle DBA v$database SYS SYSTEM Oracle SQL> connect system/manager SQL> SELECT name, created, log_mode 2 FROM v$database; 9-47 NAME CREATED LOG_MODE Oracle9i NOARCHIVELOG 9-47 Oracle9i 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 STOPPED 9-48 Oracle9i CS-ZNAE5WTCSLHO

192 SQL> SELECT username, created 2 FROM dba_users; 9-49 USERNAME CREATED SYS SYSTEM DBSNMP AURORA$JIS$UTILITY$ AURORA$ORB$UNAUTHENTICATED SCOTT 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

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

194 9 dept emp 7 3 ORDER SUPPLIER PRODUCT Oracle CREATE TABLE DDL - Data Definition Language CREATE TABLE [.] [ DEFAULT ] [ ] 10-1 DDL product 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 SQL> DESC product 10-2? P_CODE NUMBER(6) P_NAME VARCHAR2(30) P_DESC VARCHAR2(100) P_PRICE NUMBER(5,2)

195 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: DDL from Oracle 10-4 DDL SCOTT 10-4 SQL> create table emp(xx number); 10-4 create table emp(xx number) * 180

196 ERROR 1 : ORA-00955: DDL emp SCOTT 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) dept emp DEPTNO FROM SQL> SELECT * 2 FROM dept; 181

197 10-7 DEPTNO DNAME LOC 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 SQL> SELECT * 2 FROM dept; 10-9 FROM dept * ERROR 2 : ORA-00942: 10-9 dept SYSTEM SCOTT SQL> SELECT * 2 FROM scott.dept; DEPTNO DNAME LOC ACCOUNTING NEW YORK 182

198 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON schema Oracle Oracle 4 VARCHAR2 size size Size CHAR size size DATE NUMBER p s p s p 1 38 s VARCHAR2 Oracle 10-1 product p_desc VARCHAR2(100) p_desc 100 NEW 3 Oracle NEW 3 VARCHAR2 CHAR 183

199 Oracle size Oracle 10-1 p_desc p_desc CHAR(100) p_desc 100 NEW 3 Oracle NEW 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

200 DDL 7 supplier ord er DDL 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)); 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)); SQL*PLUS DESC SQL> DESC supplier 10-13? S_CODE NUMBER(6) SNAME VARCHAR2(25) 185

201 CONTACT PHONE FAX VARCHAR2(15) VARCHAR2(15) VARCHAR2(15) 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 SQL> SELECT * 2 FROM cat; TABLE_NAME TABLE_TYPE BONUS DEPT EMP ORD PRODUCT SALGRADE SUPPLIER TABLE TABLE TABLE TABLE TABLE TABLE TABLE 3 Primary Key emp emp 186

202 emp Oracle emp Oracle worker Oracle DDL worker 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'); SQL*PLUS DESC worker SQL> DESC worker 10-17? EMPNO NUMBER(4) NAME VARCHAR2(10) JOB VARCHAR2(9) INCOME NUMBER worker SQL> SELECT * 2 FROM worker; EMPNO NAME JOB INCOME

203 7369 SMITH CLERK ALLEN SALESMAN WARD SALESMAN MARTIN SALESMAN SCOTT ANALYST TURNER SALESMAN ADAMS CLERK JAMES CLERK FORD ANALYST MILLER CLERK CREATE TABLE [ ] AS 1 Oracle Oracle ALTER TABLE Oracle ALTER TABLE 4 1 ALTER TABLE ADD [ DEFAULT ] [ ] ALTER TABLE worker hiredate 188

204 10-19 SQL> ALTER TABLE worker 2 ADD (hiredate DATE); DDL SQL> SELECT * 2 FROM worker; EMPNO NAME JOB INCOME HIREDATE SMITH CLERK ALLEN SALESMAN WARD SALESMAN MARTIN SALESMAN SCOTT ANALYST TURNER SALESMAN ADAMS CLERK JAMES CLERK FORD ANALYST MILLER CLERK ALTER TABLE MODIFY [ DEFAULT ] [ ] 189

205 CHAR VARCHAR2 VARCHAR2 CHAR worker hiredate SQL> ALTER TABLE worker 2 MODIFY (hiredate DEFAULT SYSDATE); worker hiredate hiredate SQL> SELECT * 2 FROM worker; EMPNO NAME JOB INCOME HIREDATE SMITH CLERK ALLEN SALESMAN WARD SALESMAN MARTIN SALESMAN SCOTT ANALYST TURNER SALESMAN ADAMS CLERK JAMES CLERK FORD ANALYST MILLER CLERK hiredate SYSDATE worker hiredate worker hiredate SQL> INSERT INTO worker(empno, name, job, income, hiredate) 2 VALUES (9000, 'MARY','CLERK', 1000, DEFAULT); 190

206 INSERT INTO DML SQL*PLUS hiredate SQL> SELECT * 2 FROM worker; EMPNO NAME JOB INCOME HIREDATE SMITH CLERK ALLEN SALESMAN WARD SALESMAN MARTIN SALESMAN SCOTT ANALYST TURNER SALESMAN ADAMS CLERK JAMES CLERK FORD ANALYST MILLER CLERK MARY CLERK hiredate hiredate SYSDATE worker name hiredate SQL> INSERT INTO worker(empno, name, job, income, hiredate) 2 VALUES (9000, ' ','CLERK', 600, DEFAULT); hiredate

207 10-26 SQL> SELECT * 2 FROM worker EMPNO NAME JOB INCOME HIREDATE SMITH CLERK ALLEN SALESMAN WARD SALESMAN MARTIN SALESMAN SCOTT ANALYST TURNER SALESMAN ADAMS CLERK JAMES CLERK FORD ANALYST MILLER CLERK MARY CLERK CLERK hiredate SYSDATE 3 ALTER TABLE DROP COLUMN ALTER TABLE ALTER TABLE ALTER TABLE DDL Oracle8i DDL worker hiredate SQL> ALTER TABLE worker 2 DROP COLUMN hiredate;

208 10-22 worker SQL> SELECT * 2 FROM worker; EMPNO NAME JOB INCOME SMITH CLERK ALLEN SALESMAN WARD SALESMAN MARTIN SALESMAN SCOTT ANALYST TURNER SALESMAN ADAMS CLERK JAMES CLERK FORD ANALYST MILLER CLERK hiredate worker 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

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

210 7654 MARTIN SALESMAN 7788 SCOTT ANALYST 7844 TURNER SALESMAN 7876 ADAMS CLERK 7900 JAMES CLERK 7902 FORD ANALYST 7934 MILLER CLERK worker income income Oracle worker SQL> ALTER TABLE worker 2 DROP UNUSED COLUMNS; 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 worker staff 195

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

212 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 SQL staff SQL> COMMENT ON TABLE staff 2 IS 'Are you believed comments can help you to understand the designs?'; staff SQL> SELECT comments 2 FROM user_tab_comments 3 WHERE table_name = 'STAFF'; COMMENTS Are you believed comments can help you to understand the designs? 197

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

214 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 staff SQL> CREATE TABLE staff1 2 AS 3 SELECT * 4 FROM staff; 199

215 staff1 staff SQL> SELECT * 2 FROM staff1; EMPNO NAME JOB 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 staff SQL> TRUNCATE TABLE staff1; SQL> SELECT * 2 FROM staff1; SQL*PLUS DESC staff1 200

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

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

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

219 e_m_shell emp_shell emp DDL emp_shell SQL> create table emp_shell 2 as select * from emp; DDL emp_shell mgr SQL> alter table emp_shell 2 drop column mgr; emp_shell SQL> SELECT * 2 FROM emp_shell; 204

220 10-59 EMPNO ENAME JOB HIREDATE SAL COMM DEPTNO SMITH CLERK ALLEN SALESMAN WARD SALESMAN JONES MANAGER MARTIN SALESMAN BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT TURNER SALESMAN ADAMS CLERK JAMES CLERK FORD ANALYST MILLER CLERK emp_shell e_shell e_m_shell 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; EMPNO ENAME JOB EMPNO ENAME JOB 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

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

222 11 SQL SQL SQL SQL*PLUS SQL> SELECT empno, ename, sal 2 FROM emp 3 WHERE sal >= 1500; 11-1 EMPNO ENAME SAL ALLEN JONES BLAKE CLARK SCOTT KING TURNER FORD SQL SQL 11.2 & Oracle Oracle 11-2

223 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 : v_salary EMPNO ENAME SAL ALLEN JONES BLAKE CLARK SCOTT KING FORD SQL*PLUS 11-2 SQL sal 11-3 SQL> save d:\sql\sal replace 11-3 d:\sql\sal.sql sal Oracle v_salary :

224 11-4 v_salary EMPNO ENAME SAL JONES BLAKE CLARK SCOTT KING FORD sal 11-5 SQL*PLUS 11-5 SQL> SET VERIFY ON 11-6 SQL*PLUS sal v_salary : WHERE sal >= &v_salary 3: WHERE sal >= EMPNO ENAME SAL JONES BLAKE CLARK SCOTT KING

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

226 VERIFY OFF SQL> set verify off SQL*PLUS job CLERK v_job 'CLERK' EMPNO ENAME SAL JOB DEPTNO SMITH 800 CLERK ADAMS 1100 CLERK JAMES 950 CLERK MILLER 1300 CLERK 10 SQL*PLUS SQL*PLUS job CLERK v_job 'CLERK'; WHERE job = 'CLERK'; * ERROR 3 : ORA-00911: 211

227 Oracle SQL SQL SQL SQL*PLUS SQL SQL*PLUS job SQL> EDIT D:\SQL\JOB &v_job SQL*PLUS job CLERK v_job CLERK EMPNO ENAME SAL JOB DEPTNO SMITH 800 CLERK ADAMS 1100 CLERK JAMES 950 CLERK MILLER 1300 CLERK

228 CLERK 11.4 && & && SQL> SELECT ename, job, &&v_col 2 FROM emp 3 ORDER BY &v_col; sal v_col sal 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 SQL v_col Oracle Oracle & & Oracle Oracle 213

229 11-14 SQL Oracle Oracle sal comm SQL*PLUS UNDEFINE SQL> undefine v_col SQL SQL> SELECT ename, job, &&v_col 2 FROM emp 3 ORDER BY &v_col; comm v_col comm 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 SQL*PLUS DEFINE ACCEPT 214

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

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

232 CLERK v_job SQL Oracle ENAME SAL JOB SMITH 800 CLERK JAMES 950 CLERK ADAMS 1100 CLERK MILLER 1300 CLERK SQL*PLUS DEFINE = SQL*PLUS DEFINE SQL> DEFINE v_job DEFINE V_JOB = "CLERK" (CHAR) SQL*PLUS DEFINE SQL> DEFINE DEFINE _SQLPLUS_RELEASE = " " (CHAR) DEFINE _EDITOR = "Notepad" (CHAR) With the Partitioning option JServer Release Production" (CHAR) DEFINE _O_RELEASE = " " (CHAR) DEFINE V_JOB = "CLERK" (CHAR) Oracle Oracle ACCEPT 217

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

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

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

236 Job clerk ENAME SAL JOB SMITH 800 CLERK JAMES 950 CLERK ADAMS 1100 CLERK MILLER 1300 CLERK 11.8 ACCEPT HIDE ACCEPT HIDE ACCEPT MANAGER emp ename sal job SQL> SELECT ename, sal, job 2 FROM emp 3 WHERE '&pwd' = 'MANAGER'; SQL MANAGER pwd MANAGER ename sal job 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

237 TURNER 1500 SALESMAN ADAMS 1100 CLERK JAMES 950 CLERK FORD 3000 ANALYST MILLER 1300 CLERK SQL*PLUS SQL D:\SQL PWD SQL> SAVE D:\SQL\PWD SQL*PLUS PWD SQL SQL> EDIT D:\SQL\PWD SQL ACCEPT pwd PROMPT 'Please Enter Your Password:' HIDE SQL*PLUS PWD SQL PWD MANAGER

238 ******* MANAGER ******* Password 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 manager ******* MANAGER Oracle Oracle 223

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

240 ACCEPT ACCEPT ACCEPT HIDE DEFINE ACCEPT 225

241 DML Data Manipulating Language Transaction Control SQL DML DML 3 INSERT UPDATE DELETE 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

242 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 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, ' ', ' ');

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

244 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 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, '', ' '); DML dname NULL 12-9 DML dept_dml SQL> SELECT * 2 FROM dept_dml; DEPTNO DNAME LOC 229

245 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON NULL VALUES NULL dept_dml NULL DML SQL> INSERT INTO dept_dml (deptno, dname, loc) 2 VALUES (44, NULL, ' '); SQL> SELECT * 2 FROM dept_dml; DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

246 NULL dept_dml NULL DML SQL> INSERT INTO dept_dml (deptno, dname) 2 VALUES (33, ' '); NULL SQL> SELECT * 2 FROM dept_dml; DEPTNO DNAME LOC ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON NULL SQL SQL> COMMIT;

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

248 SMITH CLERK ALLEN SALESMAN WARD SALESMAN JONES MANAGER MARTIN SALESMAN BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT TURNER SALESMAN ADAMS CLERK JAMES CLERK FORD ANALYST MILLER CLERK hiredate SQL sales SQL> CREATE TABLE sales (code, name, salary, commission) 2 AS 3 SELECT empno, ename, sal, comm 4 FROM emp; SQL sales sales sales SQL sales SQL> TRUNCATE TABLE sales;

249 12-22 sales SQL> SELECT * FROM SALES; sales INSERT SQL> INSERT INTO sales (code, name, salary, commission) 2 SELECT empno, ename, sal, comm 3 FROM emp 4 WHERE job LIKE 'SALE%'; SQL> SELECT * FROM SALES; CODE NAME SALARY COMMISSION ALLEN WARD MARTIN TURNER VALUES INSERT INSERT 234

250 VALUES INSERT SQL*PLUS Oracle SQL 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); SQL Oracle Oracle 1 Oracle id 1001 name job hiredate TO_DATE SQL*PLUS SQL d:\sql\insert.sql SQL> save d:\sql\insert d:\sql\insert.sql 235

251 SQL> SELECT * 2 FROM emp_dml; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO SMITH CLERK ALLEN SALESMAN WARD SALESMAN JONES MANAGER MARTIN SALESMAN BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT TURNER SALESMAN ADAMS CLERK JAMES CLERK FORD ANALYST MILLER CLERK SQL SQL*PLUS ACCEPT SQL SQL*PLUS d:\sql\insert.sql SQL> edit d:\sql\insert.sql

252 d:\sql\insert.sql INSERT ACCEPT SQL*PLUS SQL> SELECT * 2 FROM emp_dml; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO SMITH CLERK ALLEN SALESMAN WARD SALESMAN

253 7566 JONES MANAGER MARTIN SALESMAN BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT TURNER SALESMAN ADAMS CLERK JAMES CLERK FORD ANALYST MILLER CLERK UPDATE SQL> UPDATE emp_dml 2 SET sal = sal * 0.9; sal SQL> SELECT ename, job, sal 2 FROM emp_dml; ENAME JOB SAL

254 SMITH CLERK 720 ALLEN SALESMAN 1440 WARD SALESMAN 1125 JONES MANAGER 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 emp_dml UPDATE UPDATE SET = [ = ] [WHERE ] INSERT UPDATE UPDATE WHERE emp_dml SQL> select * from emp_dml; EMPNO ENAME JOB MGR HIREDATE SAL COMM

255 7369 SMITH CLERK ALLEN SALESMAN WARD SALESMAN JONES MANAGER MARTIN SALESMAN BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT TURNER SALESMAN ADAMS CLERK JAMES CLERK FORD ANALYST MILLER CLERK CTO , emp_dml 666 CTO DML SQL> UPDATE emp_dml 2 SET sal = WHERE ename = ' '; Oracle SQL> SELECT * 2 FROM emp_dml; 240

256 12-35 EMPNO ENAME JOB MGR HIREDATE SAL COMM SMITH CLERK ALLEN SALESMAN WARD SALESMAN JONES MANAGER MARTIN SALESMAN BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT TURNER SALESMAN ADAMS CLERK JAMES CLERK FORD ANALYST MILLER CLERK CTO emp_dml 1000 CTO 1000 emp_dml ename WHERE Primary Key UPDATE WHERE WHERE UPDATE WHERE

257 12-36 salgrade SQL> SELECT * 2 FROM salgrade; GRADE LOSAL HISAL salgrade GRADE LOSAL HISAL UPDATE 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); UPDATE WHERE SET sal UPDATE

258 12-38 SQL> SELECT * 2 FROM emp_dml; EMPNO ENAME JOB MGR HIREDATE SAL COMM SMITH CLERK ALLEN SALESMAN WARD SALESMAN JONES MANAGER MARTIN SALESMAN BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT TURNER SALESMAN ADAMS CLERK JAMES CLERK FORD ANALYST MILLER CLERK CTO emp_dml 700 CLERK 800 SMITH EMPNO UPDATE 243

259 12-39 SQL> UPDATE emp_dml 2 SET (job, sal) = (SELECT job, sal 3 FROM emp_dml 4 WHERE empno = 7369) 5 WHERE job = ' '; UPDATE SET SQL> SELECT * 2 FROM emp_dml; EMPNO ENAME JOB MGR HIREDATE SAL COMM SMITH CLERK ALLEN SALESMAN WARD SALESMAN JONES MANAGER MARTIN SALESMAN BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT TURNER SALESMAN ADAMS CLERK JAMES CLERK FORD ANALYST MILLER CLERK CLERK CTO CLERK

260 12-40 emp_dml CLERK emp_dml SQL> SELECT * 2 FROM emp_dml 3 WHERE (job = 'MANAGER') OR (JOB = 'SALESMAN') 4 ORDER BY sal; EMPNO ENAME JOB MGR HIREDATE SAL COMM WARD SALESMAN MARTIN SALESMAN TURNER SALESMAN ALLEN SALESMAN CLARK MANAGER BLAKE MANAGER JONES MANAGER DELETE SQL> DELETE FROM emp_dml 245

261 2 WHERE (job = 'MANAGER' AND sal > 2500) 3 OR (JOB = 'SALESMAN' AND sal > 1300); SQL> SELECT * 2 FROM emp_dml 3 WHERE (job = 'MANAGER') OR (JOB = 'SALESMAN') 4 ORDER BY sal; EMPNO ENAME JOB MGR HIREDATE SAL COMM WARD SALESMAN MARTIN SALESMAN CLARK MANAGER DML / DML DELETE DELETE [FROM] [WHERE ] SQL FROM Oracle Unix C Oracle 246

262 UPDATE DELETE DELETE WHERE DML SQL> DELETE FROM emp_dml 2 WHERE (job = 'MANAGER' AND sal < 2500) 3 OR (JOB = 'SALESMAN' AND sal > 1300); SQL> SELECT * 2 FROM emp_dml 3 WHERE (job = 'MANAGER') OR (JOB = 'SALESMAN') 4 ORDER BY sal; EMPNO ENAME JOB MGR HIREDATE SAL COMM WARD SALESMAN MARTIN SALESMAN BLAKE MANAGER JONES MANAGER , WHERE DML SQL> DELETE emp_dml;

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

264 SMITH CLERK ALLEN SALESMAN WARD SALESMAN JONES MANAGER MARTIN SALESMAN BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT TURNER SALESMAN ADAMS CLERK JAMES CLERK FORD ANALYST MILLER CLERK Oracle Transactions Oracle ATM Oracle Oracle Oracle 3 4 Oracle 249

265 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 ATM Oracle COMMIT ROLLBACK SCOTT DML 250

266 12-50 SQL> UPDATE emp_dml 2 SET deptno = 88 3 WHERE deptno = 66; Oracle Oracle SQL> SELECT empno, ename, deptno 2 FROM emp_dml 3 WHERE deptno > 30; EMPNO ENAME DEPTNO SYSTEM/MANAGER SQL> SELECT empno, ename, deptno 2 FROM scott.emp_dml 3 WHERE deptno > 30; EMPNO ENAME DEPTNO

267 COMMIT SQL> COMMIT; SQL> SELECT empno, ename, deptno 2 FROM scott.emp_dml 3 WHERE deptno > 30; EMPNO ENAME DEPTNO UPDATE COMMIT DML 252

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

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

270 Oracle SQL SQL*PLUS SQL*PLUS SQL*PLUS SQL*PLUS DML job MANAGER SQL> UPDATE emp_dml 2 SET job = 'MANAGER' 3 WHERE empno = 1001; SQL> SELECT empno, ename, job, sal 2 FROM emp_dml 3 WHERE empno <= 7000; EMPNO ENAME JOB SAL MANAGER SQL*PLUS SQL*PLUS

271 SCOTT Oracle SQL> SELECT empno, ename, job, sal 2 FROM emp_dml 3 WHERE empno <= 7000; EMPNO ENAME JOB SAL job MANAGER SQL*PLUS SQL*PLUS Oracle SQL*PLUS SQL*PLUS DML job MANAGER SQL> UPDATE emp_dml 2 SET job = 'MANAGER' 3 WHERE empno = 1001; SQL*PLUS EXIT SQL*PLUS SQL> exit SCOTT Oracle SQL> SELECT empno, ename, job, sal 2 FROM emp_dml 3 WHERE empno <= 7000; 256

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

273 12-69 SQL> SELECT empno, ename, job, sal 2 FROM scott.emp_dml 3 WHERE empno <= 7000; EMPNO ENAME JOB SAL MANAGER MANAGER 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

274 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

275 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

276 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; 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

277 SQL> CREATE INDEX empcon_ename_idx 2 ON empcon(ename); Oracle aa x empcon_ename_idx Oracle user_indexes SQL*PLUS SELECT 13-4 SQL> COL INDEX_TYPE FOR A SQL> COL TABLE_NAME FOR A 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_C NORMAL E_M_SHELL UNIQUE EMPCON_ENAME_ IDX EMPCON Oracle 262

278 SYS_C E_M_SHELL Oracle EMPCON ENAME 13-6 user_ind_columns 13-7 SQL*PLUS SELECT 13-7 SQL> col column_name for a 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_C E_M_SHELL E_ID 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); SCOTT 263

279 13-10 SQL> SELECT index_name, table_name, column_name, column_position 2 FROM user_ind_columns; 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_C E_M_SHELL E_ID 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

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

281 SQL*PLUS DBA SQL> connect system/manager SQL*PLUS sal SQL> CREATE INDEX empcon_salgt_idx 2 ON scott.empcon(sal-2000); SQL*PLUS SELECT SQL> COL INDEX_TYPE FOR A SQL> COL INDEX_TYPE FOR A SQL> COL index_name for a SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS 2 FROM user_indexes 3 WHERE table_owner = 'SCOTT'; INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES 266

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

283 SQL*PLUS SELECT SQL> col id for SQL> col operation for a SQL> col options for a SQL> col object_name for a plan_table Oracle empcon_salgt_idx SQL> SELECT id, operation, options, object_name, position 2 FROM plan_table; ID OPERATION OPTIONS OBJECT_NAM POSITION TABLE ACCESS FULL EMPCON 1 0 SELECT STATEMENT Oracle empcon_salgt_idx EMPCON Full Table Scan Oracle empcon_ ename_idx DDL plan_table SQL> truncate table plan_table;

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

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

286 ENAME_IDX SQL> DROP INDEX SCOTT.EMPCON_ENAME_IDX SCOTT SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS 2 FROM dba_indexes 3 WHERE owner = 'SCOTT'; INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES PK_DEPT NORMAL DEPT UNIQUE PK_EMP NORMAL EMP UNIQUE SYS_C NORMAL E_M_SHELL UNIQUE EMPCON EMPCON_ ENAME_IDX SYSTEM EMPCON SQL> COL INDEX_NAME FOR A SQL> COL INDEX_TYPE FOR A SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS 2 FROM dba_indexes 3 WHERE table_name = 'EMPCON'; INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES

287 EMPCON_SALGT_IDX FUNCTION-BASED NORMAL EMPCON NONUNIQUE EMPCON EMPCON_SALGT_IDX DDL SCOTT EMPCON SQL> DROP TABLE scott.empcon; EMPCON SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS 2 FROM dba_indexes 3 WHERE table_name = 'EMPCON'; 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

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

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

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

291 20 ACCOUNTING GUANGZGOU deptcon ACCOUNTING Oracle DNAME deptcon UNIQUE DELETE NOT NULL NOT NULL SQL*PLUS DESC NOT NULL USER_CONSTRAINTS SQL*PLUS SQL> COL owner FOR A SQL> COL table_name FOR A SCOTT SQL> SELECT owner, constraint_name, constraint_type, table_name 2 FROM user_constraints; OWNER CONSTRAINT_NAME C TABLE_NAME SCOTT PK_DEPT P DEPT SCOTT SYS_C C DEPTCON SCOTT PK_EMP P EMP SCOTT FK_DEPTNO R EMP SCOTT SYS_C P E_M_SHELL SCOTT SYS_C C MANAGER SCOTT SYS_C C MANAGER 276

292 SCOTT SYS_C C MANAGER deptcon C Oracle SYS_C C constraint_type C CHECK NOT NULL P PRIMARY KEY R REFERENTIAL INTEGRITY FOREIGN KEY U UNIQUE USER_CONS_COLUMNS SCOTT SQL> COL column_name for a SQL> SELECT owner, constraint_name, table_name, column_name 2 FROM user_cons_columns; OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME SCOTT FK_DEPTNO EMP DEPTNO SCOTT PK_DEPT DEPT DEPTNO SCOTT PK_EMP EMP EMPNO SCOTT SYS_C MANAGER EMPNO SCOTT SYS_C MANAGER ENAME SCOTT SYS_C MANAGER HIREDATE SCOTT SYS_C E_M_SHELL E_ID SCOTT SYS_C DEPTCON DNAME deptcon DNAME Oracle SYS_C

293 DNAME UNIQUE DDL deptcon DNAME NOT NULL SQL> ALTER TABLE deptcon 2 DROP CONSTRAINT SYS_C002719; SQL> SELECT owner, constraint_name, table_name, column_name 2 FROM user_cons_columns; OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME SCOTT FK_DEPTNO EMP DEPTNO SCOTT PK_DEPT DEPT DEPTNO SCOTT PK_EMP EMP EMPNO SCOTT SYS_C MANAGER EMPNO SCOTT SYS_C MANAGER ENAME SCOTT SYS_C MANAGER HIREDATE SCOTT SYS_C E_M_SHELL E_ID deptcon DDL deptcon dname UNIQUE SQL> ALTER TABLE deptcon 2 ADD CONSTRAINT deptcon_dname_uk UNIQUE(dname); ADD CONSTRAINT deptcon_dname_uk UNIQUE(dname) 278

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

295 13-62 SQL> COL CONSTRAINT_NAME FOR A SQL> SELECT owner, constraint_name, constraint_type, table_name, 2 search_condition 3 FROM user_constraints; 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_C P E_M_SHELL SCOTT SYS_C C MANAGER "EMPNO" IS NOT NULL SCOTT SYS_C C MANAGER "ENAME" IS NOT NULL SCOTT SYS_C C MANAGER "HIREDATE" IS NOT NULL 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 UNIQUE UNIQUE KEY NULL DML deptcon SQL> INSERT INTO deptcon(deptno, dname, loc) 2 VALUES (20, NULL, ' '); 280

296 UNIQUE KEY NULL UNIQUE KEY NULL DML deptcon SQL> INSERT INTO deptcon(deptno, dname, loc) 2 VALUES (20, NULL, ' '); 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

297 18~ DDL 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)); Oracle DBA SQL*PLUS person SQL> DESC person 13-67? ID VARCHAR2(10) NAME VARCHAR2(20) GENDER CHAR(1) AGE NUMBER person CHECK

298 13-68 SQL> COL owner FOR A SQL> COL CONSTRAINT_NAME FOR A SQL> COL TABLE_NAME FOR A SQL> COL SEARCH_CONDITION FOR A SQL> SELECT owner, constraint_name, constraint_type, table_name, 2 search_condition 3 FROM user_constraints 4 WHERE table_name = 'PERSON'; 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 SCOTT PERSON PERSON_GENDER_CK CHECK gender = 'F' PERSON_AGE_CK CHECK age BETWEEN 18 AND 35 person CHECK DML person SQL> INSERT INTO person(id, name, gender, age) 2 VALUES (1001, ' ','F', 22); person 283

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

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

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

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

303 2 VALUES (88, ' ', ' '); deptcon SQL> SELECT * 2 FROM deptcon; DEPTNO DNAME LOC ACCOUNTING BEIJING PRIMARY KEY UNIQUE Oracle UNIQUE INDEX SQL> COL INDEX_TYPE FOR A SQL> COL INDEX_TYPE FOR A SQL> COL index_name for a SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS 2 FROM user_indexes 3 WHERE table_name = 'DEPTCON'; INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENES DEPTCON_DEPTNO_PK NORMAL DEPTCON UNIQUE 288

304 DEPTCON_DNAME_UK NORMAL DEPTCON UNIQUE SQL> col column_name for a SQL> SELECT index_name, table_name, column_name, column_position 2 FROM user_ind_columns 3 WHERE table_name = 'DEPTCON'; INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION DEPTCON_DNAME_UK DEPTCON DNAME 1 DEPTCON_DEPTNO_PK DEPTCON DEPTNO 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 DDL empcon empcon deptcon SQL> CREATE TABLE empcon 2 AS 3 SELECT * 4 FROM emp_dml;

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

306 empcon deptno 88 deptcon empcon deptno 88 Referential Integrity Referential Integrity DML deptcon deptno SQL> INSERT INTO deptcon 2 VALUES (88, ' ', ' '); DDL empcon deptno FOREIGN KEY SQL> ALTER TABLE empcon 2 ADD CONSTRAINT empcon_deptno_fk 3 FOREIGN KEY(deptno) REFERENCES deptcon(deptno); empcon SQL> COL owner FOR A SQL> COL CONSTRAINT_NAME FOR A SQL> COL TABLE_NAME FOR A

307 SQL> COL SEARCH_CONDITION FOR A SQL> SELECT owner, constraint_name, constraint_type, table_name, 2 r_constraint_name 3 FROM user_constraints 4 WHERE table_name = 'EMPCON'; OWNER CONSTRAINT_NAME C TABLE_NAME R_CONSTRAINT_NAME SCOTT EMPCON_DEPTNO_FK R EMPCON DEPTCON_DEPTNO_PK SQL> COL COLUMN_NAME FOR A SQL> SELECT owner, constraint_name, table_name, column_name, position 2 FROM user_cons_columns 3 WHERE table_name = 'EMPCON'; OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAM POSITION SCOTT EMPCON_DEPTNO_FK EMPCON DEPTNO 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 INSERT empcon Referential Integrity

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

309 EMPNO ENAME MGR HIREDATE SAL COMM DEPTNO KING empcon deptcon Referential Integrity deptcon PARENT TABLE CHILD TABLE Referential Integrity PARENT TABLE FOREIGN KEY DELETE DELETE deptcon SQL> DELETE FROM deptcon 2 WHERE deptno = '88'; DELETE FROM deptcon * ERROR 1 : ORA-02292: (SCOTT.EMPCON_DEPTNO_FK) empcon 88 empcon deptno 88 Referential Integrity empcon Referential Integrity 294

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

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

312 UPDATE deptcon SQL> UPDATE deptcon 2 SET deptno = 44 3 WHERE deptno = 88; UPDATE empcon NULL SQL> UPDATE empcon 2 SET deptno = 44 3 WHERE deptno IS NULL; deptcon empcon SQL> SELECT * 2 FROM deptcon; DEPTNO DNAME LOC ACCOUNTING BEIJING SQL> SELECT ename, ename, job, sal, deptno 2 FROM empcon 3 WHERE deptno = 44; 297

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

314 DROP TABLE TRUNCATE TABLE DDL deptcon deptcon deptno SQL> TRUNCATE TABLE deptcon; TRUNCATE TABLE deptcon * ERROR 1 : ORA-02266: / SQL> ALTER TABLE deptcon 2 DROP COLUMN deptno; 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

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

316 2 FROM deptcon 3 ORDER BY deptno; DEPTNO DNAME LOC ACCOUNTING BEIJING SQL> SELECT empno, ename, job, sal, deptno 2 FROM empcon 3 WHERE deptno > 20 4 ORDER BY deptno; EMPNO ENAME JOB SAL DEPTNO ALLEN SALESMAN WARD SALESMAN MARTIN SALESMAN BLAKE MANAGER TURNER SALESMAN JAMES CLERK MANAGER CTO MANAGER MANAGER deptcon empcon DML SQL> DELETE FROM deptcon 2 WHERE deptno = 44; deptcon

317 SQL> SELECT * 2 FROM deptcon; DEPTNO DNAME LOC ACCOUNTING BEIJING Oracle empcon SQL> SELECT empno, ename, job, sal, deptno 2 FROM empcon 3 WHERE deptno > 20 4 ORDER BY deptno; EMPNO ENAME JOB SAL DEPTNO ALLEN SALESMAN WARD SALESMAN MARTIN SALESMAN BLAKE MANAGER TURNER SALESMAN JAMES CLERK Oracle Oracle NULL OR deptno IS NULL Oracle empcon

318 SQL> SELECT empno, ename, sal, job, deptno 2 FROM empcon 3 WHERE deptno > 20 4 OR deptno IS NULL; EMPNO ENAME SAL JOB DEPTNO ALLEN 1600 SALESMAN WARD 1250 SALESMAN MARTIN 1250 SALESMAN BLAKE 2850 MANAGER TURNER 1500 SALESMAN JAMES 950 CLERK MANAGER CTO MANAGER MANAGER DML SQL> rollback DDL empcon deptno SQL> ALTER TABLE empcon 2 DROP CONSTRAINT empcon_deptno_fk; DDL empcon deptno ON DELETE CASCADE SQL> ALTER TABLE empcon 2 ADD CONSTRAINT empcon_deptno_fk 303

319 3 FOREIGN KEY(deptno) REFERENCES deptcon (deptno) 4 ON DELETE CASCADE; DML SQL> DELETE FROM deptcon 2 WHERE deptno = 44; deptcon SQL> SELECT * 2 FROM deptcon; DEPTNO DNAME LOC ACCOUNTING BEIJING Oracle empcon SQL> SELECT empno, ename, sal, job, deptno 2 FROM empcon; EMPNO ENAME SAL JOB DEPTNO SMITH 800 CLERK ALLEN 1600 SALESMAN WARD 1250 SALESMAN JONES 2975 MANAGER MARTIN 1250 SALESMAN

320 7698 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 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

321 Oracle Oracle ALTER TABLE DISABLE CONSTRAINT [CASCADE] CASCADE DISABLE CREATE TABLE ALTER TABLE Parent Table STATUS SQL> SELECT owner, constraint_name, constraint_type, table_name, 2 r_constraint_name, status 3 FROM user_constraints 4 WHERE table_name = 'EMPCON'; OWNER CONSTRAINT_NAME C TABLE_NAME R_CONSTRAINT_NAME STATUS SCOTT EMPCON_DEPTNO_FK R EMPCON DEPTCON_DEPTNO_PK ENABLED SQL> SELECT owner, constraint_name, constraint_type, table_name, 2 r_constraint_name, status 3 FROM user_constraints 4 WHERE table_name = 'DEPTCON'; 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

322 empcon EMPCON_DEPTNO_FK STATUS ENABLED eptcon DEPTCON_DEPTNO_PK STATUS ENABLED DDL DEPTCON_DEPTNO_PK SQL> ALTER TABLE deptcon 2 DISABLE CONSTRAINT deptcon_deptno_pk; ALTER TABLE deptcon * ERROR 1 : ORA-02297: (SCOTT.DEPTCON_DEPTNO_PK) - Oracle DDL Child Table DEPTCON_DEPTNO_PK CASCADE CASCADE DDL DEPTCON_DEPTNO_PK SQL> ALTER TABLE deptcon 2 DISABLE CONSTRAINT deptcon_deptno_pk CASCADE; Oracle STATUS SQL> SELECT owner, constraint_name, constraint_type, table_name, 2 r_constraint_name, status 3 FROM user_constraints 4 WHERE table_name = 'DEPTCON'; 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

323 SQL> SELECT owner, constraint_name, constraint_type, table_name, 2 r_constraint_name, status 3 FROM user_constraints 4 WHERE table_name = 'EMPCON'; OWNER CONSTRAINT_NAME C TABLE_NAME R_CONSTRAINT_NAME STATUS SCOTT EMPCON_DEPTNO_FK R EMPCON DEPTCON_DEPTNO_PK DISABLED empcon EMPCON_DEPTNO_FK STATUS DISABLED 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 DDL ENABLED DEPTCON_DEPTNO_PK Oracle SQL> ALTER TABLE deptcon 2 ENABLE CONSTRAINT deptcon_deptno_pk; Oracle STATUS SQL> SELECT owner, constraint_name, constraint_type, table_name, 308

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

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

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

327 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

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

329 / Normalization SQL 14.1

330 Model1 Model 2 Model 3 Model n View1 View2 View3 Viewn Table1 Table2 Table3 Table4 Tablen 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

331 RESEARCH SALES Oracle Oracle SQL 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; Oracle 14-3 SQL*PLUS 14-3 SQL> DESC average 14-3? VARCHAR2(14) NUMBER NUMBER NUMBER 316

332 14-3 SQL SQL> SELECT * 2 FROM average; ACCOUNTING RESEARCH SALES SQL Oracle Oracle Oracle SQL 14-2 SQL Group Functions GROUP BY 14-4 average average / 14.2 CREATE [OR REPLACE] [FORCE NOFORCE] VIEW [( [ ] )] AS 317

333 [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 SQL> CREATE OR REPLACE VIEW acct 2 (" ", " ", " ", " ") 3 AS 4 SELECT ename, sal, job, hiredate 5 FROM emp 318

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

335 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

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

337 AVERAGE 162 SELECT d.dname " ", AVG(e.sal) " ", AVG(NVL(comm,0)) " Oracle Oracle Oracle I/O Views DML Views Views DML DML DML Oracle DML DML DML DML GROUP BY DISTINCT DELETE 322

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

339 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) SQL> SELECT empno, ename, job, sal, deptno, comm, deptno 2 FROM sales30; EMPNO ENAME JOB SAL DEPTNO COMM DEPTNO ALLEN SALESMAN WARD SALESMAN MARTIN SALESMAN BLAKE MANAGER TURNER SALESMAN JAMES CLERK deptno DML SQL> UPDATE sales30 2 SET deptno = 10 3 WHERE job = 'MANAGER'; UPDATE sales30 * ERROR 1 : 324

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

341 SQL DML acct UPDATE SQL> UPDATE acct 2 SET " " = 9999; SQL> SELECT * 2 FROM acct; CLARK 9999 MANAGER ACCOUNTING NEW YORK KING 9999 PRESIDENT ACCOUNTING NEW YORK MILLER 9999 CLERK ACCOUNTING NEW YORK Oracle DBA emp SQL> SELECT ename, sal, job 2 FROM emp 3 WHERE deptno = 10; ENAME SAL JOB

342 CLARK KING MILLER 9999 MANAGER 9999 PRESIDENT 9999 CLERK SQL SQL> ROLLBACK; DML DML WITH READ ONLY WITH READ ONLY CREATE VIEW acct 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; acct UPDATE acct SQL> UPDATE acct 2 SET " " = 9999; SET " " = 9999 * 327

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

344 AVERAGE 162 SALARY_LIMIT 112 SALES acct Oracle DDL SQL> DROP VIEW acct; user_views acct SQL> SELECT view_name, text_length 2 FROM user_views; VIEW_NAME TEXT_LENGTH AVERAGE 162 SALARY_LIMIT 112 SALES SQL*PLUS DESC SQL> DESC acct ERROR ORA-04043: acct Views DSS Data Warehouse Oracle OLTP 329

345 DSS Data Warehouse DSS Data Warehouse Oracle8i OLTP DSS Data Warehouse Oracle8i Internet DSS Data Warehouse SUM AVG GROUP BY HAVING ORDER BY Oracle8i Inline Views 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; Inline Views FROM Inline Views m SELECT Inline Views n / Top n queries/analysis 330

346 20% 80% 20% 20% ORDER BY Oracle8i n / Top n queries/analysis n / Top n queries/analysis 20% n / Top n queries/analysis 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

347 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; Order NO. Name Salary Job SCOTT 3000 ANALYST 2 FORD 3000 ANALYST 3 JONES 2975 MANAGER 4 BLAKE 2850 MANAGER 5 CLARK 2450 MANAGER emp n / Top n queries/analysis Views Views Views Views Views 332

348 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

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

350 [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 MINVALUE n NOMINVALUE CACHE n n Oracle NOCACHE Oracle CACHE 20 CYCLE n NOCYCLE supplier 15-1 SQL*PLUS 15-1 SQL> desc supplier 15-1?

351 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; supplier Oracle s_code 15-3 DDL supplier_s_code 15-3 SQL> CREATE SEQUENCE supplier_s_code 2 START WITH INCREMENT BY 10 4 MAXVALUE NOCACHE 6 NOCYCLE; Oracle user_sequences SQL*PLUS sequence_name 15-4 SQL> col sequence_name for a SQL> SELECT sequence_name, min_value, max_value, 2 increment_by, last_number 336

352 3 FROM user_sequences; 15-5 SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER SUPPLIER_S_CODE supplier_s_code Oracle user_objects supplier_s_code 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 VALID SALARY_LIMIT VIEW VALID SALES30 VIEW VALID SUPPLIER_S_CODE SEQUENCE VALID 1 SELECT SELECT SELECT FROM SELECT Oracle NEXTVAL CURRVAL NEXTVAL 337

353 CURRVAL dual supplier_s_code SQL> SELECT supplier_s_code.currval 2 FROM dual; 15-7 SELECT supplier_s_code.currval * ERROR 1 : ORA-08002: SUPPLIER_S_CODE.CURRVAL 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 ' ', , ); SQL*PLUS 15-9 SQL> col sname for a16 SQL> col contact for a12 SQL> col phone for a10 SQL> col fax for a SQL> SELECT * 338

354 2 FROM supplier; S_CODE SNAME CONTACT PHONE FAX dual supplier_s_code SQL> SELECT supplier_s_code.currval 2 FROM dual; CURRVAL supplier_s_code NEXTVAL CURRVAL DML supplier SQL> INSERT INTO supplier(s_code, sname, contact, phone, fax) 2 VALUES (supplier_s_code.nextval, ' ', 3 ' ', , ); SQL> SELECT * 2 FROM supplier; S_CODE SNAME CONTACT PHONE FAX

355 Oracle dual supplier_s_code SQL> SELECT supplier_s_code.currval 2 FROM dual; CURRVAL NEXTVAL CURRVAL ord SQL*PLUS ord 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

356 Oracle ordno DDL ord_ordno SQL> CREATE SEQUENCE ord_ordno 2 START WITH INCREMENT BY 1 4 MAXVALUE NOCYCLE; Oracle user_sequences SQL> SELECT sequence_name, min_value, max_value, 2 increment_by, last_number, cache_size 3 FROM user_sequences; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER CACHE_SIZE ORD_ORDNO SUPPLIER_S_CODE CACHE_SIZE NOCACHE NOCACHE 15-3 DDL supplier_s_code NOCACHE SUPPLIER_S_CODE CACHE_SIZE DDL ord_ordno NOCACHE ORD_ORDNO CACHE_SIZE 20 ord_ordno ord DML 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

357 SQL> SELECT * 2 FROM ord; ORDNO P_CODE S_CODE ORDATE UNIT PRICE DML ord ordno, s_code ordate 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); SQL> SELECT * 2 FROM ord; ORDNO P_CODE S_CODE ORDATE UNIT PRICE ord DML 342

358 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

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

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

361 Sequence Oracle ALTER SEQUENCE SUPPLIER_S_CODE DDL SQL> ALTER SEQUENCE supplier_s_code 2 INCREMENT BY 11 3 MAXVALUE CACHE 50; user_sequences SQL> SELECT sequence_name, min_value, max_value, 2 increment_by, last_number, cache_size 3 FROM user_sequences; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER CACHE_SIZE ORD_ORDNO SUPPLIER_S_CODE SUPPLIER_S_CODE SUPPLIER_S_CODE Oracle INSERT supplier_s_ code.nextval supplier 346

362 15-28 SQL> INSERT INTO supplier(s_code, sname, contact, phone, fax) 2 VALUES (supplier_s_code.nextval, ' ', 3 ' ', , ); supplier SQL> SELECT * 2 FROM supplier; S_CODE SNAME CONTACT PHONE FAX SUPPLIER_S_CODE SQL> SELECT supplier_s_code.currval 2 FROM dual; CURRVAL SUPPLIER_S_CODE SQL> SELECT sequence_name, min_value, max_value, 2 increment_by, last_number, cache_size 3 FROM user_sequences; 347

363 15-31 SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER CACHE_SIZE ORD_ORDNO SUPPLIER_S_CODE LAST_NUMBER = = INSERT supplier_s_code.nextval supplier SQL> INSERT INTO supplier(s_code, sname, contact, phone, fax) 2 VALUES (supplier_s_code.nextval, ' ', 3 ' ', , ); supplier SQL> SELECT * 2 FROM supplier; S_CODE SNAME CONTACT PHONE FAX ALTER SEQUENCE ALTER SEQUENCE [INCREMENT BY n] [{MAXVALUE n NOMAXVALUE} [{MINVALUE n NOMINVALUE}] [{CACHE n NOCACHE}] 348

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

365 2 FROM supplier; S_CODE SNAME CONTACT PHONE FAX DML SQL> INSERT INTO supplier(s_code, sname, contact, phone, fax) 2 VALUES (supplier_s_code.nextval, ' ', 3 '', , ); VALUES (supplier_s_code.nextval, ' ', * ERROR 2 : ORA-02289: DROP SEQUENCE Sequence Sequence Sequence 1 START WITH

366 Oracle Synonym supplier Synonym CREATE SYNONYM supplier s SQL> CREATE SYNONYM s 2 FOR supplier; s supplier SQL> SELECT * 2 FROM s; S_CODE SNAME CONTACT PHONE FAX Synonym user_objects Synonym user_objects Synonym SQL*PLUS SQL*PLUS

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

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

369 15-46 FROM supplier * ERROR 2 : ORA-00942: Synonym s scott SQL> SELECT * 2 FROM scott.s; S_CODE SNAME CONTACT PHONE FAX Synonym s DDL scott supplier Synonym ss SQL> CREATE PUBLIC SYNONYM ss 2 FOR scott.supplier; Synonym ss SQL> SELECT * 2 FROM ss; S_CODE SNAME CONTACT PHONE FAX

370 Synonym ss SQL*PLUS SYS Oracle SQL> CONNECT SYS/Oracle AS SYSDBA; Synonym ss SQL> SELECT * 2 FROM ss; S_CODE SNAME CONTACT PHONE FAX Oracle Synonym Synonym Synonym Synonym s DDL SQL*PLUS SCOTT

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

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

373 Internet Oracle Oracle Oracle Oracle Oracle Oracle Oracle 1 Oracle 2 Oracle Oracle Oracle Oracle Oracle

374 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 DDL 16-3 SQL> CREATE USER dog 2 IDENTIFIED BY wangwang;

375 dog dog dba_users SQL> SELECT username, created 2 FROM dba_users 3 WHERE ROUND(created, 'DAY') = ROUND(SYSDATE, 'DAY'); 16-4 USERNAME CREATED DOG 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 ORA DOG CREATE SESSION DBA CREATE USER DBA 360

376 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

377 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; DCL CREATE SESSION dog 16-7 SQL> GRANT CREATE SESSION TO dog; SQL*PLUS dog 16-8 SQL> connect dog/wangwang; Oracle dog

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

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

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

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

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

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

384 CREATE PROCEDURE CREATE TRIGGER CREATE TYPE CREATE OPERATOR CREATE INDEXTYPE SCOTT SCOTT =20000 Oracle Role Role Role Role 20 Role Role = 1020 Role Role Role Role 1020 I/O Oracle 369

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

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

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

388 DBA SYSTEM dog cat pig SQL> connect dog/wangwang SQL> SELECT * 2 FROM SESSION_PRIVS; PRIVILEGE CREATE SESSION CREATE TABLE SELECT ANY TABLE CREATE VIEW SQL> connect cat/miaomiao SQL> SELECT * 2 FROM SESSION_PRIVS; PRIVILEGE CREATE SESSION CREATE TABLE SELECT ANY TABLE CREATE VIEW SQL> connect pig/hengheng 373

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

390 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 SQL*PLUS pig SQL> connect pig/hengheng;

391 16-45 pig SQL> select * from cat; pig pig cat baby_cat pig animal animal SELECT ANY TABLE SQL> select * from cat.baby_cat; EMPNO ENAME JOB MGR HIREDATE SMITH CLERK ALLEN SALESMAN WARD SALESMAN JONES MANAGER MARTIN SALESMAN BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT TURNER SALESMAN ADAMS CLERK JAMES CLERK FORD ANALYST MILLER CLERK animal SELECT ANY TABLE SYSTEM DBA 376

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

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

394 16-54 SQL*PLUS pig SQL> connect pig/hengheng; pig cat baby_cat SQL> SELECT empno, ename 2 FROM cat.baby_cat; EMPNO ENAME 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 SQL*PLUS dog SQL> connect dog/wangwang;

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

396 dog cat baby_cat SQL> SELECT empno, ename 2 FROM cat.baby_cat; EMPNO ENAME 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 scott supplier SQL> SELECT * 2 FROM scott.supplier; FROM scott.supplier * ERROR 2 : ORA-00942: 381

397 16-62 scott supplier SELECT cat dog pig SQL*PLUS scott SQL> connect scott/tiger; DCL scott supplier SELECT scott SQL> GRANT select 2 ON supplier 3 TO PUBLIC; SQL*PLUS dog SQL> connect dog/wangwang dog scott supplier SQL> SELECT * 2 FROM scott.supplier; S_CODE SNAME CONTACT PHONE FAX

398 scott supplier SQL*PLUS cat SQL> connect cat/miaomiao cat scott supplier SQL> SELECT * 2 FROM scott.supplier; S_CODE SNAME CONTACT PHONE FAX pig scott supplier DCL scott supplier SYSTEM DBA SQL*PLUS SYSTEM SQL> connect system/manager DDL fox loveyou 383

399 16-70 SQL> CREATE USER fox 2 IDENTIFIED BY loveyou; DCL animal fox SQL> GRANT animal TO fox; fox scott supplier SQL*PLUS fox SQL> connect fox/loveyou; fox scott supplier SQL> SELECT * 2 FROM scott.supplier; S_CODE SNAME CONTACT PHONE FAX user_tab_privs_made 384

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

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

402 cat fox and dog scott supplier phone fax PHONE FAX REVOKE REVOKE { } [ { }] FROM { PUBLIC } [ { PUBLIC}] animal SELECT ANY TABLE REVOKE { [ ] ALL} ON FROM { PUBLIC } [ { PUBLIC}] CASCAD CONSTRAINTS] CASCAD CONSTRAINTS pig dog SQL*PLUS pig 387

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

404 4 ORDER BY ename) 5 WHERE ROWNUM <= 6; Cat NO. Cat Name ADAMS 2 ALLEN 3 BLAKE 4 CLARK 5 FORD 6 JAMES n 6 Top n queries DCL cat baby_cat SELECT fox SQL> GRANT select 2 ON cat.baby_cat 3 TO fox; SQL*PLUS fox SQL> connect fox/loveyou 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

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

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

407 fox cat baby_cat cat baby_cat SELECT SQL> SELECT * 2 FROM cat.baby_cat; 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

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

409 DBA fox SQL*PLUS system DBA SQL> connect system/manager DDL fox fox SQL> ALTER USER fox 2 IDENTIFIED BY fox; fox SQL*PLUS fox SQL> connect fox/fox; fox ALTER USER fox DDL fox SQL*PLUS SYSTEM DBA 394

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

411 USERNAME CREATED DOG CAT PIG fox SQL DDL cat SQL> DROP USER cat; DROP USER cat * ERROR 1 : ORA-01922: CASCADE 'CAT' cat baby_cat DROP USER CASCADE DDL cat SQL> DROP USER cat CASCADE; cat cat SQL> SELECT username, created 2 FROM dba_users 3 WHERE ROUND(created, 'DAY') >= ROUND(SYSDATE, 'DAY') - 7; USERNAME CREATED 396

412 DOG PIG / / Oracle CONNECT RESOURCE Oracle CONNECT RESOURCE SQL*PLUS SYS DBA SQL> CONNECT SYS/Oracle AS SYSDBA CONNECT RESOURCE SQL> SELECT * 2 FROM role_sys_privs 3 WHERE role IN ('CONNECT', 'RESOURCE'); ROLE PRIVILEGE ADM CONNECT ALTER SESSION NO CONNECT CREATE CLUSTER NO 397

413 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 CREATE USER cat miaomiao SQL> CREATE USER cat 2 IDENTIFIED BY miaomiao; DCL CONNECT cat SQL> GRANT CONNECT TO cat; cat SQL*PLUS SQL*PLUS SQL> connect cat/miaomiao;

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

415 SQL> GRANT RESOURCE TO fox; fox SQL*PLUS SQL*PLUS SQL> connect fox/devloper; ERROR ORA-01045: user FOX lacks CREATE SESSION privilege; logon denied RESOURCE CREATE SESSION fox SQL*PLUS SYS DBA SQL> CONNECT SYS/Oracle AS SYSDBA DCL CONNECT fox SQL> GRANT CONNECT TO fox; fox SQL*PLUS SQL*PLUS SQL> CONNECT FOX/DEVLOPER;

416 SESSION_PRIVS fox fox SQL> SELECT * 2 FROM SESSION_PRIVS; 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

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

418 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

419 S_CODE SNAME CONTACT PHONE FAX cat 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 cat

420 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 OraHome

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

422 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> oracle\ora90\rdbms\adminscott.sql 407

423 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

424 8 9 Oracle D F F Oracle %

425

426 % Oracle 411

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

428 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 USA: Oracle Corporation. 17. Watt S. (2001). SQL*Plus Getting Started Release for Windows. USA: Oracle

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

430 Oracle SQL SQL Oracle SQL Oracle Oracle SQL ORACLE SQL SQL Oracle SQL Oracle

431 ORACLE Oracle Oracle Oracle SQL SQL Oracle SQL OCP / / / SQL Oracle OCP Oracle OCP Oracle7.x Oracle9i Oracle SQL Oracle Oracle [email protected] Oracle SQL 416

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

433 DOS Windows OS/2 Macintosh Unix Linux PC 12 CD-ROM ( [email protected]) Oracle/SQL / %