Oracle SQL Oracle SQL OCP Oracle Oracle8 CIP Oracle/SQL /. 2004 ISBN 7-302-07951-X... Oracle. TP311.138 CIP 2004 001611 http://www.tup.com.cn 100084 010-62770175 185 260 27.25 602 2004 2 1 2004 2 1 ISBN 7-302-07951-X/TP 5774 1 5000 34.00 010-62776969
SQL Structured Query Language Relational Database Management Systems Oracle WTO Oracle Oracle SQL Oracle SQL Oracle DBA Developer Oracle SQL OCP Oracle Certified Professional Oracle SQL Oracle DBA Developer Oracle SQL American National Standards Institute - ANSI International Standards Organization - ISO SQL Oracle SQL SQL Oracle OCP OCP Oracle Student Guide Oracle Oracle Student Guide Oracle Oracle GZ Comtech NZ LTD New Zealand Institute of Science and Technology Ltd - Oracle DBA PC PC CPU 300 MHz 64MB 8GB CD Oracle Oracle
UNIX Oracle Etake Technology Inc Unitec Institute of Technology - IT Oracle IT 5% 95% Oracle SQL OCP Relational Database Management Systems SQL Structured Query Language DML DDL DCL SQL Oracle8i Oracle9i Oracle Oracle Oracle Oracle Windows Oracle Windows II
Oracle Windows Windows Oracle Oracle IT Oracle SQL Oracle SQL SQL Oracle Unix Windows SQL Oracle Oracle III
SQL IT Oracle Oracle Oracle Oracle Oracle SQL 3 Oracle IT Oracle Oracle Oracle Oracle Oracle IV
Oracle 8.0.4 8.0.5 8.1.5 8.1.7 Oracle 9.0.1 Oracle Oracle SQL*PLUS Oracle Oracle sql_minghe@yahoo.com.cn Oracle SQL 2004 3 V
...1 1...4 1.1...4 1.2...5 1.3...7 1.4...9 1.5 SQL...10 1.6 SQL...12 1.7...13 1.8 DISTINCT...15 1.9...17 1.10...17 2...18 2.1...18 2.2 operators...19 2.3 BETWEEN AND operators...19 2.4 SQL...21 2.5 IN operators...22 2.6 LIKE operators...23 2.7 escape...25 2.8 ORDER BY...27 2.9 ORDER BY...28 2.10 ORDER BY...30 2.11 ORDER BY...31 2.12 ORDER BY SELECT...31 2.13...32 2.14...33 3 SQL*PLUS...34 3.1 DESC[RIBE]...34 3.2 SET LINE[SIZE]{80 n}...35 3.3 L LIST n text...36
3.4 / RUN...38 3.5 n A[PPEND]...38 3.6 del...41 3.7 C[HANGE]...42 3.8...45 3.9...47 3.10...49 3.11 SPOOL...49 3.12...50 4...52 4.1...52 4.2...52 4.3...53 4.4...58 4.5...59 4.6...62 4.7...65 4.8 ROUND TRUNC...68 4.9...71 4.10...71 4.11...79 5 NULL...80 5.1 NULL...80 5.2 NULL...81 5.3 NULL...83 5.4...85 5.5...88 5.6 AND OR BETWEEN AND IN...89 5.7 NVL...91 5.8 DECODE...93 5.9...94 5.10 Oracle9i...96 5.11...101 6...102 VIII
6.1...102 6.2 COUNT...102 6.3 AVE SUM...103 6.4 MIN MAX...104 6.5 GROUP BY...105 6.6 GROUP BY...105 6.7 GROUP BY...106 6.8 GROUP BY...107 6.9 HAVING...108 6.10...110 6.11...110 6.12 NVL...111 6.13 NVL...112 6.14...113 6.15...113 7...114 7.1 Normalization...114 7.2 Primary Key Entity Integrity...115 7.3 1NF...115 7.4...116 7.5 Foreign Key Referential Integrity...117 7.6 2NF...118 7.7 3NF...118 7.8...120 7.9...120 7.10...120 7.11...122 7.12...123 7.13 Self join...125 7.14...126 7.15...127 7.16...129 7.17 SQL:1999...130 7.18 SQL:1999...130 7.19 USING...131 IX
7.20 ON...132 7.21 ON...132 7.22...134 7.23...135 7.24...136 7.25...139 8...140 8.1...140 8.2 WHERE...141 8.3 HAVING...144 8.4 FROM...145 8.5...145 8.6 IN...146 8.7 ALL...147 8.8 ANY...148 8.9 NULL...150 8.10...152 8.11 Pairwise Comparison...152 8.12 Nonpairwise Comparison...153 8.13...154 8.14...155 9 SQL*PLUS...156 9.1 SQL*PLUS...156 9.2 SQL*PLUS ECHO...156 9.3 SQL*PLUS FEEDBACK...159 9.4 SQL*PLUS...160 9.5 SQL*PLUS COLUMN...160 9.6 SQL*PLUS...166 9.7...169 9.8...171 9.9...173 9.10...177 9.11...177 10...179 X
10.1...179 10.2...180 10.3...183 10.4...185 10.5...186 10.6...188 10.7...195 10.8...197 10.9...199 10.10...202 10.11...206 11...207 11.1...207 11.2 &...207 11.3...210 11.4 &&...213 11.5...215 11.6 DEFINE...216 11.7 ACCEPT...218 11.8 ACCEPT HIDE...221 11.9 ACCEPT...224 11.10...224 11.11...224 12...226 12.1...226 12.2 INSERT...227 12.3 INSERT NULL...229 12.4...232 12.5...233 12.6 INSERT...234 12.7 ACCEPT INSERT...236 12.8 UPDATE...238 12.9...241 12.10...243 XI
12.11 DELETE...245 12.12 DELETE...247 12.13...248 12.14 Transactions...249 12.15 Oracle Transactions...250 12.16 COMMIT ROLLBACK...250 12.17 DDL DCL...252 12.18 SQL*PLUS...255 12.19 AUTOCOMMIT...257 12.20...258 12.21...259 13 Indexes and Constraints...260 13.1...260 13.2...260 13.3...262 13.4...264 13.5...265 13.6 Oracle...267 13.7...269 13.8...272 13.9 NOT NULL...273 13.10...276 13.11 UNIQUE...278 13.12 CHECK...281 13.13 PRIMARY KEY...285 13.14 FOREIGN KEY...289 13.15 FOREIGN KEY INSERT...293 13.16 FOREIGN KEY DELETE...294 13.17 FOREIGN KEY UPDATE...295 13.18 FOREIGN KEY DDL...298 13.19 FOREIGN KEY ON DELETE SET NULL ON DELETE CASCADE...300 13.20...306 13.21...311 13.22...312 XII
14 Views...314 14.1 Views...314 14.2 Views...315 14.3 Views...317 14.4 Views...320 14.5 Oracle Views...321 14.6 Views DML...322 14.7 Views WITH CHECK OPTION...323 14.8 WITH READ ONLY...326 14.9 Views...328 14.10 Inline Views...330 14.11 n / Top n queries/analysis...331 14.12...332 15 Sequence Synonym...334 15.1 Sequence...334 15.2 Sequence...334 15.3 Sequence...335 15.4 Sequence...337 15.5 Sequence...340 15.6 NEXTVAL CURRVAL...343 15.7 Sequence...346 15.8 Sequence...349 15.9 Synonym...350 15.10 Synonym...351 15.11 Synonym...353 15.12 Synonym...355 15.13...356 16...358 16.1...358 16.2...358 16.3 Oracle...361 16.4...362 16.5...367 16.6 Role...369 XIII
16.7 Role...370 16.8...374 16.9...375 16.10...387 16.11...392 16.12...394 16.13 CONNECT RESOURCE...397 16.14...402...403...413...415...417 XIV
9 1 2 3 6 8 20 IT 3 Windows SQL*PLUS 4 RR YY 4 1 2 1~2 SELECT 5 1~2 NULL SQL NULL SQL NULL 6 1~2
7 Oracle Equijoin Self join Non- equijoin Outer join 4 SQL:1999 SQL SQL:1999 8 SQL 9 Oracle FORM REPORT Oracle Oracle 11 ACCEPT 1 2 5 6 7 Oracle 4 3 4 7 8 9 11 SQL*PLUS 10 DDL DDL 12 DML Transaction Control DML 13 14 2
15 16 12 10 13 15 13 14 16 3
SQL Structured Query Language Relational Database Management Systems SQL Data Retrieval Data Manipulation Language DML Data Definition Language DDL Data Control Language DCL Transaction Control SQL Sun & Moon Limited Corporation SQL Oracle IT 3 emp dept salgrade emp dept salgrade 3 SQL SQL*PLUS DBA SQL SELECT FROM 1-1 emp
1-1 SQL> SELECT * 2 FROM emp; 1-1 EMPNO ENAME JOB MGR HIREDATE ---------- -------------- ------------------------------- --------- SAL COMM DEPTNO --------- --------- ------------ 7369 SMITH CLERK 7902 17-12? -80 800 20 7499 ALLEN SALESMAN 7698 20-2? -81 1600 300 30 7521 WARD SALESMAN 7698 22-2? -81 1250 500 30 SQL SQL*PLUS * SELECT emp employee Oracle Unix C salary sal HIREDATE emp SELECT empno ename sal 1-2 5
1-2 SQL> SELECT empno, ename, sal 2 FROM emp; 1-2 EMPNO ENAME SAL ---------- ---------- ---------- 7369 SMITH 800 7499 ALLEN 1600 7521 WARD 1250 7566 JONES 2975 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 7876 ADAMS 1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 14 Project SELECT 1-3 1-3 SQL> SELECT sal,ename,empno 2 FROM emp; 1-3 SAL ENAME EMPNO ---------- -------------------- ---------- 800 SMITH 7369 1600 ALLEN 7499 1250 WARD 7521 2975 JONES 7566 1250 MARTIN 7654 2850 BLAKE 7698 2450 CLARK 7782 3000 SCOTT 7788 6
5000 KING 7839 1500 TURNER 7844 1100 ADAMS 7876 14 1-3 1-2 Keyword SELECT FROM Oracle SQL SELECT SEL SELEC FROM FRO FR SQL 1-4 1-4 SQL> select Sal, EName, EMPNO 2 From Emp; 1-4 SAL ENAME EMPNO ---------- -------------------- ---------- 800 SMITH 7369 1600 ALLEN 7499 1250 WARD 7521 2975 JONES 7566 1250 MARTIN 7654 2850 BLAKE 7698 2450 CLARK 7782 3000 SCOTT 7788 5000 KING 7839 1500 TURNER 7844 1100 ADAMS 7876 14 1-4 1-3 Oracle 1-3 SQL SQL 1-5 7
1-5 SQL> select sal, ename, empno from emp; 1-5 SAL ENAME EMPNO ---------- -------------------- ------ 800 SMITH 7369 1600 ALLEN 7499 1250 WARD 7521 2975 JONES 7566 1250 MARTIN 7654 2850 BLAKE 7698 2450 CLARK 7782 3000 SCOTT 7788 5000 KING 7839 1500 TURNER 7844 1100 ADAMS 7876 14 1-5 1-3 1-3 SQL 1-6 SQL SQL statement clause SELECT * FROM emp; SELECT * FROM emp 1-6 SQL> SELECT empno, ename, sal, 2 deptno, job 3 FROM emp; 1-6 EMPNO ENAME SAL DEPTNO JOB ---------- -------------------- ---------- ---------- --------- 7369 SMITH 800 20 CLERK 7499 ALLEN 1600 30 SALESMAN 7521 WARD 1250 30 SALESMAN 7566 JONES 2975 20 MANAGER 7654 MARTIN 1250 30 SALESMAN 7698 BLAKE 2850 30 MANAGER 7782 CLARK 2450 10 MANAGER 7788 SCOTT 3000 20 ANALYST 8
7839 KING 5000 10 PRESIDENT 7844 TURNER 1500 30 SALESMAN 7876 ADAMS 1100 20 CLERK 14 1-6 SELECT FROM 1-6 emp empno ename sal deptno job 1-7 SQL 1-7 SQL> alter session 2 set NLS_DATE_LANGUAGE = 'AMERICAN'; 1-7 1-8 1-9 SQL*PLUS 1-8 SQL> col hiredate for a15 1-9 SQL> COL ENAME FOR A8 SQL SQL*PLUS SQL*PLUS 1-10 1-10 SQL> SELECT empno, ename, sal, 2 hiredate, job 3 FROM emp; 9
1-10 EMPNO ENAME SAL HIREDATE JOB ---------- -------- ---------- --------------- --------- 7369 SMITH 800 17-DEC-80 CLERK 7499 ALLEN 1600 20-FEB-81 SALESMAN 7521 WARD 1250 22-FEB-81 SALESMAN 7566 JONES 2975 02-APR-81 MANAGER 7654 MARTIN 1250 28-SEP-81 SALESMAN 7698 BLAKE 2850 01-MAY-81 MANAGER 7782 CLARK 2450 09-JUN-81 MANAGER 7788 SCOTT 3000 19-APR-87 ANALYST 7839 KING 5000 17-NOV-81 PRESIDENT 7844 TURNER 1500 08-SEP-81 SALESMAN 7876 ADAMS 1100 23-MAY-87 CLERK 14 1-10 1-7 1-9 1-10 hiredate SQL 4 + * / 500 / 1-11 1-11 SQL> SELECT empno, ename, sal, 500+sal 2 FROM emp; 1-11 EMPNO ENAME SAL 500+SAL ---------- -------- ---------- ---------- 7369 SMITH 800 1300 7499 ALLEN 1600 2100 7521 WARD 1250 1750 7566 JONES 2975 3475 7654 MARTIN 1250 1750 10
7698 BLAKE 2850 3350 7782 CLARK 2450 2950 7788 SCOTT 3000 3500 7839 KING 5000 5500 7844 TURNER 1500 2000 7876 ADAMS 1100 1600 14 1-11 1-11 1-12 SQL 1-12 SQL> SELECT empno, ename, 500+sal*12 2 FROM emp; 1-12 EMPNO ENAME 500+SAL*12 ---------- -------- ----------- 7369 SMITH 10100 7499 ALLEN 19700 7521 WARD 15500 7566 JONES 36200 7654 MARTIN 15500 7698 BLAKE 34700 7782 CLARK 29900 7788 SCOTT 36500 7839 KING 60500 7844 TURNER 18500 7876 ADAMS 13700 14 1-12 1-13 SQL 11
1-13 SQL> SELECT empno, ename, (500+sal)*12 2 FROM emp; 1-13 EMPNO ENAME (500+SAL)*12 ---------- -------- ------------ 7369 SMITH 15600 7499 ALLEN 25200 7521 WARD 21000 7566 JONES 41700 7654 MARTIN 21000 7698 BLAKE 40200 7782 CLARK 35400 7788 SCOTT 42000 7839 KING 66000 7844 TURNER 24000 7876 ADAMS 19200 14 1-13 Oracle 1-13 1-14 SQL 1-14 SQL> SELECT empno AS "Employee Number", 2 ename name, (500+sal)*12 "Annual Salary" 3 FROM emp; 1-14 Employee Number NAME Annual Salary --------------- -------------------- ------------- 7369 SMITH 15600 7499 ALLEN 25200 7521 WARD 21000 12
7566 JONES 41700 7654 MARTIN 21000 7698 BLAKE 40200 7782 CLARK 35400 7788 SCOTT 42000 7839 KING 66000 7844 TURNER 24000 7876 ADAMS 19200 14 1-14 Oracle Unix C salary sal Oracle AS AS Oracle AS Employee s Salary 1-15 SQL 1-15 SQL> SELECT ename ' annual salary is ' (500+sal)*12 "Employee's Salary" 2 FROM emp; 1-15 Employee's Salary ----------------------------- SMITH annual salary is 15600 ALLEN annual salary is 25200 13
WARD annual salary is 21000 JONES annual salary is 41700 MARTIN annual salary is 21000 BLAKE annual salary is 40200 CLARK annual salary is 35400 SCOTT annual salary is 42000 KING annual salary is 66000 TURNER annual salary is 24000 ADAMS annual salary is 19200 1-16 1-16 SQL> SELECT ename ' ' (500+sal)*12 " " 2 FROM emp; 1-16 -------------------- SMITH 15600 ALLEN 25200 WARD 21000 JONES 41700 MARTIN 21000 BLAKE 40200 CLARK 35400 SCOTT 42000 KING 66000 TURNER 24000 ADAMS 19200 JAMES 17400 FORD 42000 MILLER 21600 14 literal SELECT 14
Oracle DBA 1-17 1-17 SQL> select deptno 2 from emp; 1-17 DEPTNO ----------- 20 30 30 20 30 30 10 20 10 30 14 Oracle Oracle DISTINCT 1-18 1-18 SQL> SELECT DISTINCT deptno 2 FROM emp; 1-18 DEPTNO ---------- 10 20 30 15
1-18 1-17 DISTINCT Oracle DISTINCT Oracle 1-19 1-19 SQL> SELECT deptno 2 FROM dept; 1-19 DEPTNO ---------- 10 20 30 40 1-19 Oracle deptno dept DISTINCT 1-20 1-20 SQL> SELECT DISTINCT deptno, job 2 FROM emp; 1-20 DEPTNO JOB ---------- --------- 10 CLERK 10 MANAGER 10 PRESIDENT 20 ANALYST 20 CLERK 20 MANAGER 30 CLERK 30 MANAGER 30 SALESMAN 9 16
SELECT FROM SELECT * {[DISTINCT] } FROM [ ] SELECT * FROM SELECT DISTINCT FROM SQL SQL literal DISTINCT 17
1 9.11 1500 1500 1500 2-1 2-1 SQL> SELECT empno, ename, sal 2 FROM emp 3 WHERE sal >= 1500; 2-1 EMPNO ENAME SAL ---------- ------------------ -------- 7499 ALLEN 1600 7566 JONES 2975 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 7902 FORD 3000 8 2-1 1500 empno ename sal 2-1 WHERE selection WHERE FROM WHERE
operators operator Oracle 6 operators > >= < <= = <> = Oracle 3 operators BETWEEN AND IN LIKE operators 2900 2900 2-2 2-2 SQL> SELECT empno, ename, sal 2 FROM emp 3 WHERE sal BETWEEN 1500 AND 2900; 2-2 EMPNO ENAME SAL ---------- -------------------- ------ 7499 ALLEN 1600 7698 BLAKE 2850 7782 CLARK 2450 7844 TURNER 1500 19
2-2 BETWEEN BETWEEN AND AND 1981 1 1 1982 5 31 2-3 2-3 SQL> SELECT empno, ename, sal, hiredate 2 FROM emp 3 WHERE hiredate BETWEEN '01-JAN-81' AND '31-MAY-82'; 2-3 EMPNO ENAME SAL HIREDATE ---------- ----------------- ---------- ------- 7499 ALLEN 1600 20-FEB-81 7521 WARD 1250 22-FEB-81 7566 JONES 2975 02-APR-81 7654 MARTIN 1250 28-SEP-81 7698 BLAKE 2850 01-MAY-81 7782 CLARK 2450 09-JUN-81 7839 KING 5000 17-NOV-81 7844 TURNER 1500 08-SEP-81 7900 JAMES 950 03-DEC-81 7902 FORD 3000 03-DEC-81 7934 MILLER 1300 23-JAN-82 11 1981 1 1982 5 HIREDATE 2-4 2-4 SQL> alter session set NLS_DATE_LANGUAGE = AMERICAN; 2-4 SQL 1 SQL BETWEEN NOT BETWEEN AND 20
2-3 1981 1 1 1982 5 31 2-5 2-5 SQL> SELECT empno, ename, sal, hiredate 2 FROM emp 3 WHERE hiredate NOT BETWEEN '01-JAN-81' AND '31-MAY-82'; 2-5 EMPNO ENAME SAL HIREDATE ---------- ----------------- ---------- -------- 7369 SMITH 800 17-DEC-80 7788 SCOTT 3000 19-APR-87 7876 ADAMS 1100 23-MAY-87 SQL 1981 1 1982 5 salesman 2-6 2-6 SQL> SELECT empno, ename, job 2 FROM emp; 3 WHERE JOB = 'salesman'; 2-6 emp WHERE WHERE Oracle9i DD-MON-YY Oracle9i DD-MON-RR 2-7 21
2-7 SQL> SELECT empno, ename, job, sal 2 FROM emp 3 WHERE job ='SALESMAN'; 2-7 EMPNO ENAME JOB SAL --------- ------------------ ---------------- --------- 7499 ALLEN SALESMAN 1600 7521 WARD SALESMAN 1250 7654 MARTIN SALESMAN 1250 7844 TURNER SALESMAN 1500 salesman IN 2-8 2-8 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE job IN ('SALESMAN', 'CLERK', 'MANAGER'); 2-8 EMPNO ENAME SAL JOB ---------- -------------------- -------- ------ 7369 SMITH 800 CLERK 7499 ALLEN 1600 SALESMAN 7521 WARD 1250 SALESMAN 7566 JONES 2975 MANAGER 7654 MARTIN 1250 SALESMAN 7698 BLAKE 2850 MANAGER 7782 CLARK 2450 MANAGER 7844 TURNER 1500 SALESMAN 7876 ADAMS 1100 CLERK 7900 JAMES 950 CLERK 7934 MILLER 1300 CLERK 11 22
IN SQL IN SQL JOB IN IN NOT NOT IN analyst 2-9 2-9 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE job NOT IN ('ANALYST', 'PRESIDENT'); 2-9 EMPNO ENAME SAL JOB ---------- ------------------ ---------- -------- 7369 SMITH 800 CLERK 7499 ALLEN 1600 SALESMAN 7521 WARD 1250 SALESMAN 7566 JONES 2975 MANAGER 7654 MARTIN 1250 SALESMAN 7698 BLAKE 2850 MANAGER 7782 CLARK 2450 MANAGER 7844 TURNER 1500 SALESMAN 7876 ADAMS 1100 CLERK 7900 JAMES 950 CLERK 7934 MILLER 1300 CLERK 11 WHERE job NOT IN 'ANALYST', 'PRESIDENT' Oracle JOB ANALYST PRESIDENT emp 5 ANALYST PRESIDENT SALESMAN CLERK MANAGER analyst 2-9 PRESIDENT 23
SALESMAN 3 SAL LIKE 2-10 SQL 2-10 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE job LIKE 'SAL%'; 2-10 EMPNO ENAME SAL JOB ---------- -------------------- ---------- ---- 7499 ALLEN 1600 SALESMAN 7521 WARD 1250 SALESMAN 7654 MARTIN 1250 SALESMAN 7844 TURNER 1500 SALESMAN LIKE wildcard 2 wildcard LIKE % - % - SALESMAN S L S 2-11 2-11 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE job LIKE 'S_L_S%'; 2-11 EMPNO ENAME SAL JOB ---------- -------------------- ---------- -------- 7499 ALLEN 1600 SALESMAN 7521 WARD 1250 SALESMAN 7654 MARTIN 1250 SALESMAN 7844 TURNER 1500 SALESMAN 2-11 LIKE % - 24
LIKE WHERE 1981 2-12 2-12 SQL> SELECT empno, ename, sal, hiredate 2 FROM emp 3 WHERE hiredate LIKE '%81'; 2-12 EMPNO ENAME SAL HIREDATE ---------- ------------------ ---------- --------- 7499 ALLEN 1600 20-FEB-81 7521 WARD 1250 22-FEB-81 7566 JONES 2975 02-APR-81 7654 MARTIN 1250 28-SEP-81 7698 BLAKE 2850 01-MAY-81 7782 CLARK 2450 09-JUN-81 7839 KING 5000 17-NOV-81 7844 TURNER 1500 08-SEP-81 7900 JAMES 950 03-DEC-81 7902 FORD 3000 03-DEC-81 11 - % escape 1 2-13 2-14 SQL 2-13 SQL> CREATE TABLE dept_temp 2 AS 3 SELECT * 4 FROM dept; 2-13 2-14 SQL> INSERT INTO dept_temp 25
2 VALUES (88,'IT_RESEARCH','BEIJING'); 2-14 1 2-15 dname IT_ 2-15 SQL> SELECT * 2 FROM dept_temp 3 WHERE dname LIKE 'IT\_%' escape '\'; 2-15 DEPTNO DNAME LOC ---------- ------------------------ ------- 88 IT_RESEARCH BEIJING 2-15 \ escape \ _ IT _ \ escape escape Oracle \ escape Unix C escape 2-16 2-16 SQL> SELECT * 2 FROM dept_temp 3 WHERE dname LIKE 'IT~_%' escape '~'; 2-16 DEPTNO DNAME LOC ---------- -------------- ------- 88 IT_RESEARCH BEIJING 2-16 ~ escape 2-16 2-15 SQL SQL*PLUS escape SQL 26
SQL ORDER BY 2-17 2-17 SQL> SELECT empno, ename, sal 2 FROM emp 3 WHERE sal >= 1500 4 ORDER BY sal; 2-17 EMPNO ENAME SAL ---------- -------------------- ------ 7844 TURNER 1500 7499 ALLEN 1600 7782 CLARK 2450 7698 BLAKE 2850 7566 JONES 2975 7788 SCOTT 3000 7902 FORD 3000 7839 KING 5000 8 2-18 2-18 SQL> SELECT empno, ename, sal 2 FROM emp 3 WHERE sal >= 1500 4 ORDER BY sal DESC; 2-18 EMPNO ENAME SAL ---------- -------------------- ------ 7839 KING 5000 27
7788 SCOTT 3000 7902 FORD 3000 7566 JONES 2975 7698 BLAKE 2850 7782 CLARK 2450 7499 ALLEN 1600 7844 TURNER 1500 8 2-18 DESC ORDER BY ASC ascending order ASC SQL ASCII A Z DESC descending order ORDER BY ORDER BY SQL 1 1-11 1-14 1 1-14 SQL 2-19 2-19 SQL>SELECT empno AS "Employee Number",ename name,(500+sal)*12 "Annual Salary" 2 FROM emp SQL 2-20 SQL 2-20 SQL>SELECT empno AS "Employee Number", ename name, (500+sal)*12 "Annual Salary" 2 FROM emp 3 ORDER BY "Annual Salary" DESC; 2-20 Employee Number NAME Annual Salary --------------- ---------- ------------- 7839 KING 66000 7788 SCOTT 42000 28
7902 FORD 42000 7566 JONES 41700 7698 BLAKE 40200 7782 CLARK 35400 7499 ALLEN 25200 7844 TURNER 24000 7934 MILLER 21600 7521 WARD 21000 7654 MARTIN 21000 7876 ADAMS 19200 7900 JAMES 17400 7369 SMITH 15600 14 2-20 ORDER BY ORDER BY 2-21 SQL 2-21 SQL>SELECT empno AS "Employee Number", ename name, (500+sal)*12 "Annual Salary" 2 FROM emp 3 ORDER BY (500+sal)*12 DESC; 2-21 Employee Number NAME Annual Salary --------------- ---------- ------------- 7839 KING 66000 7788 SCOTT 42000 7902 FORD 42000 7566 JONES 41700 7698 BLAKE 40200 7782 CLARK 35400 7499 ALLEN 25200 7844 TURNER 24000 7934 MILLER 21600 7521 WARD 21000 7654 MARTIN 21000 7876 ADAMS 19200 7900 JAMES 17400 7369 SMITH 15600 14 2-20 2-21 ORDER BY 29
2-22 3 3 ORDER BY 3 3 2-22 SQL> SELECT empno "Employee Number",ename name,(500+sal)*12 "Annual Salary" 2 FROM emp 3 ORDER BY 3 DESC; 2-22 Employee Number NAME Annual Salary --------------- ---------- ------------- 7839 KING 66000 7788 SCOTT 42000 7902 FORD 42000 7566 JONES 41700 7698 BLAKE 40200 7782 CLARK 35400 7499 ALLEN 25200 7844 TURNER 24000 7934 MILLER 21600 7521 WARD 21000 7654 MARTIN 21000 7876 ADAMS 19200 7900 JAMES 17400 7369 SMITH 15600 14 SQL ORDER BY Oracle SQL ORDER BY ORDER BY SQL 30
A Z 2-23 2-23 SQL> SELECT ename, job, sal 2 FROM emp 3 ORDER BY job, sal DESC; 2-23 ENAME JOB SAL ---------- --------- ---------- SCOTT ANALYST 3000 FORD ANALYST 3000 MILLER CLERK 1300 ADAMS CLERK 1100 JAMES CLERK 950 SMITH CLERK 800 JONES MANAGER 2975 BLAKE MANAGER 2850 CLARK MANAGER 2450 KING PRESIDENT 5000 ALLEN SALESMAN 1600 TURNER SALESMAN 1500 WARD SALESMAN 1250 MARTIN SALESMAN 1250 14 SELECT 2-24 2-24 SQL> SELECT ename, job, sal 2 FROM emp 31
3 ORDER BY empno; 2-24 ENAME JOB SAL ---------- --------- ---------- SMITH CLERK 800 ALLEN SALESMAN 1600 WARD SALESMAN 1250 JONES MANAGER 2975 MARTIN SALESMAN 1250 BLAKE MANAGER 2850 CLARK MANAGER 2450 SCOTT ANALYST 3000 KING PRESIDENT 5000 TURNER SALESMAN 1500 ADAMS CLERK 1100 JAMES CLERK 950 FORD ANALYST 3000 MILLER CLERK 1300 14 WHERE ORDER BY SELECT * {[DISTINCT] } FROM [WHERE ] [ORDER BY { }[ASC DESC]] ORDER BY SQL 32
selection WHERE 6 operators WHERE BETEEN AND IN operators NOT SQL LIKE operators wildcard escape ORDER BY SELECT 33
SQL SQL SQL SQL SQL SQL SQL*PLUS SQL SQL SQL*PLUS SQL SQL*PLUS SQL*PLUS DESC[RIBE] 3-1 emp 3-1 SQL> DESC emp 3-1? ----------------------------------------- -------- ------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) 3-1 NULL 3-1 emp 8 empno EMPNO 4 ENAME 10 JOB 9 MGR 4
HIREDATE SAL 7 COMM 7 DEPTNO 3-2 dept 3-2 SQL> DESC dept 3-2? ----------------------------------------- -------- ------------ DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) 3-2 dept 3 DEPTNO DEPTNO DNAME 14 LOC 13 3-1 3-2 SQL*PLUS DESC[RIBE] SQL*PLUS SQL DESC[RIBE] SQL SQL*PLUS SET LINE[SIZE] {80 n} n 80 n 80 3-3 SQL emp 3-3 SQL> SELECT * 2 FROM emp; 3-3 EMPNO ENAME JOB MGR HIREDATE SAL COMM 35
---------- ---------- --------- ---------- ---------- ---------- --------- DEPTNO ---------- 7369 SMITH CLERK 7902 17-12 -80 800 20 7499 ALLEN SALESMAN 7698 20-2 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2 -81 1250 500 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- ---------- ---------- --------- DEPTNO ---------- 7566 JONES MANAGER 7839 02-4 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5 -81 2850 30 SQL*PLUS SET LINE 100 3-4 3-4 SQL> SET line 100 3-3 SQL*PLUS 3-5 SQL 3-5 SQL> SELECT empno, ename, job, sal 2 FROM dept 3 WHERE sal >= 1500 4 ORDER BY job, sal DESC; 36
3-5 WHERE sal >= 1500 * ERROR 3 : ORA-00904:???? 3-5 emp dept Oracle Oracle SQL*PLUS L LIST SQL L LIST SQL 3-6 3-6 SQL> L 3-6 1 SELECT empno, ename, job, sal 2 FROM dept 3 WHERE sal >= 1500 4* ORDER BY job, sal DESC n text n SQL SQL text SQL L LIST 3-7 3-7 SQL> 2 FROM emp 3-8 L LIST SQL 3-8 SQL> L 3-8 1 SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= 1500 4* ORDER BY job, sal DESC 37
3-8 SQL Oracle SQL*PLUS / RUN SQL SQL 3-9 SQL*PLUS SQL 3-9 SQL> / 3-9 EMPNO ENAME JOB SAL ---------- -------------------- ------------------ ---------- 7788 SCOTT ANALYST 3000 7902 FORD ANALYST 3000 7566 JONES MANAGER 2975 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7839 KING PRESIDENT 5000 7499 ALLEN SALESMAN 1600 7844 TURNER SALESMAN 1500 8 SQL*PLUS Oracle SQL*PLUS 3-10 3-10 SQL> SELECT ename 2 FROM emp; 3-10 ENAME ------------- SMITH 38
ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER 14 SELECT job, sal SELECT SQL*PLUS L LIST SQL 3-11 SQL> L 3-11 1 SELECT ename 2* FROM emp 3-11 2 * 3-11 SELECT ename SQL ename job, sal 1 3-12 3-12 SQL> 1 3-12 1* SELECT ename 3-12 SQL 3-13 a job sal SELECT ename 3-13 SQL> a,job, sal 3-13 39
1* SELECT ename,job, sal 3-14 L 3-14 SQL> L 3-14 1 SELECT ename,job, sal 2* FROM emp 3-14 / R SOL 3-15 3-15 SQL> / 3-15 ENAME JOB SAL ---------- --------- ---------- SMITH CLERK 800 ALLEN SALESMAN 1600 WARD SALESMAN 1250 JONES MANAGER 2975 MARTIN SALESMAN 1250 BLAKE MANAGER 2850 CLARK MANAGER 2450 SCOTT ANALYST 3000 KING PRESIDENT 5000 TURNER SALESMAN 1500 ADAMS CLERK 1100 JAMES CLERK 950 FORD ANALYST 3000 MILLER CLERK 1300 14 SQL n n n 0 text SQL 40
del n n n del m n m n SQL*PLUS 3-5 3-16 SQL 3-16 SQL> SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= 1500 4 ORDER BY job, sal DESC; 3-17 SQL*PLUS 3-17 SQL> L 3-17 1 SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= 1500 4* ORDER BY job, sal DESC emp ORDER BY 3-18 SQL*PLUS 3-18 SQL> del 4 3-19 SQL*PLUS L 3-19 SQL> L 3-19 1 SELECT empno, ename, job, sal 2 FROM emp 3* WHERE sal >= 1500 3-19 SQL ORDER BY 3-20 SQL*PLUS / 41
3-20 SQL> / 3-20 EMPNO ENAME JOB SAL ---------- ------------------ --------------- -------- 7499 ALLEN SALESMAN 1600 7566 JONES MANAGER 2975 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7788 SCOTT ANALYST 3000 7839 KING PRESIDENT 5000 7844 TURNER SALESMAN 1500 7902 FORD ANALYST 3000 8 3-10 del m n del del m SQL*PLUS C[HANGE]/ / SQL 3-5 SQL 3-21 3-21 SQL> SELECT empno, ename, job, sal 2 FROM dept 3 WHERE sal >= 1500 4 ORDER BY job, sal DESC; 3-21 WHERE sal >= 1500 * ERROR 3 : ORA-00904: 42
C[HANGE] SQL dept emp 3-22 SQL*PLUS 3-22 SQL> C /dept/emp 3-22 SP2-0023: 3-22 SQL*PLUS 3-22 SQL*PLUS dept 3-23 SQL*PLUS SQL 3-23 SQL> 2 3-23 2* FROM dept 3-22 3-24 SQL*PLUS 3-24 SQL> C /dept/emp 3-24 2* FROM emp 3-24 SQL dept emp 3-25 L 3-25 SQL> l 3-25 1 SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= 1500 4* ORDER BY job, sal DESC 3-26 / 3-9 3-26 43
SQL> / 3-26 EMPNO ENAME JOB SAL ---------- ---------- --------- ---------- 7788 SCOTT ANALYST 3000 7902 FORD ANALYST 3000 7566 JONES MANAGER 2975 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7839 KING PRESIDENT 5000 7499 ALLEN SALESMAN 1600 7844 TURNER SALESMAN 1500 8 sal 3-27 SQL*PLUS SQL 3-27 SQL> 4 3-27 4* ORDER BY job, sal DESC 3-28 C job SQL 3-28 SQL> c /job,/ 3-28 4* ORDER BY sal DESC 3-29 L 3-29 SQL> l 3-29 1 SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= 1500 4* ORDER BY sal DESC 44
3-30 / SQL 3-30 SQL> / 3-30 EMPNO ENAME JOB SAL ---------- ---------- --------- ---------- 7839 KING PRESIDENT 5000 7788 SCOTT ANALYST 3000 7902 FORD ANALYST 3000 7566 JONES MANAGER 2975 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7499 ALLEN SALESMAN 1600 7844 TURNER SALESMAN 1500 8 C 3-31 3-31 SQL> SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= 1500 4 ORDER BY job, sal DESC; 3-31 EMPNO ENAME JOB SAL ---------- ------------------ ----------------- -------- 7788 SCOTT ANALYST 3000 7902 FORD ANALYST 3000 7566 JONES MANAGER 2975 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7839 KING PRESIDENT 5000 45
7499 ALLEN SALESMAN 1600 7844 TURNER SALESMAN 1500 8 3-32 SQL*PLUS SQL D:\SQL\SAMPLE.sql SQL*PLUS D:\SQL 3-32 SQL> SAVE D:\SQL\SAMPLE 3-32 D:\SQL\SAMPLE.sql SAVE SQL D:\SQL\SAMPLE.sql 3-33 SELECT empno, ename, job, sal FROM emp WHERE sal >= 1500 ORDER BY job, sal DESC / SQL*PLUS L SQL SQL*PLUS SAVE D:\SQL\SAMPLE SQL*PLUS SQL 3-34 3-34 SQL> L 3-34 1 SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= 1500 4* ORDER BY job, sal DESC 3-35 SQL SQL 3-35 SQL> SELECT * 46
2 FROM dept; 3-35 DEPTNO DNAME LOC ---------- -------------------------- ------ 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 3-36 SQL*PLUS L SQL 3-36 SQL> L 3-36 1 SELECT * 2* FROM dept SQL SQL D:\SQL\SAMPLE.sql 3-37 SQL*PLUS GET SQL 3-37 SQL> GET D:\SQL\SAMPLE.sql 3-37 1 SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= 1500 4* ORDER BY job, sal DESC 3-38 SQL*PLUS L D:\SQL\ SAMPLE.sql SQL 3-38 47
SQL> L 3-38 1 SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= 1500 4* ORDER BY job, sal DESC C A n DEL SQL 3-39 / SQL 3-39 SQL> / 3-39 EMPNO ENAME JOB SAL ---------- -------------------- --------------- -------- 7788 SCOTT ANALYST 3000 7902 FORD ANALYST 3000 7566 JONES MANAGER 2975 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7839 KING PRESIDENT 5000 7499 ALLEN SALESMAN 1600 7844 TURNER SALESMAN 1500 8 3-40 SQL*PLUS ed[it] D:\SQL\SAMPLE 3-40 SQL> ed D:\SQL\SAMPLE 3-40 D:\SQL\SAMPLE 48
3-41 SQL*PLUS D:\SQL\SAMPLE.sql 3-41 SQL> @D:\SQL\SAMPLE.sql 3-41 EMPNO ENAME JOB SAL ---------- -------------------- ------------------ ---------- 7788 SCOTT ANALYST 3000 7902 FORD ANALYST 3000 7566 JONES MANAGER 2975 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7839 KING PRESIDENT 5000 7499 ALLEN SALESMAN 1600 7844 TURNER SALESMAN 1500 8 @ START SQL SQL SQL SQL*PLUS SPOOL SQL 3-42 3-43 3-44 SQL*PLUS SQL 3-42 SQL> SPOOL D:\SQL\OUTPUT 3-43 SQL> SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= 1500 4 ORDER BY job, sal DESC; 49
3-44 SQL> SPOOL OFF; D:\SQL\OUTPUT SQL> SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal >= 1500 4 ORDER BY job, sal DESC; EMPNO ENAME JOB SAL ---------- -------------------- ------------------ -------- 7788 SCOTT ANALYST 3000 7902 FORD ANALYST 3000 7566 JONES MANAGER 2975 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7839 KING PRESIDENT 5000 7499 ALLEN SALESMAN 1600 7844 TURNER SALESMAN 1500 8 SQL> SPOOL OFF; SPOOL D:\SQL\OUTPUT SPOOL D:\SQL OUTPUT SPOOL OFF OUTPUT SPOOL OUT SQL*PLUS Windows SQL*PLUS SQL*PLUS Oracle Oracle SQL Oracle SQL SQL 50
SQL SQL SQL SQL SQL SQL SPOOL 51
Oracle Oracle SQL SQL SQL SQL [ 1 2 3 ] SELECT WHERE ORDER BY
LOWER UPPER INITCAP CONCAT SUBSTR LENGTH INSTR TRIM REPLACE LOWER 4-1 4-1 SQL> SELECT LOWER('SQL: Structural Query Language') 2 FROM dual; 4-1 LOWER('SQL:STRUCTURALQUERYLANGUAGE') ------------------------------------ sql: structural query language dual Oracle 1 SELECT FROM LOWER 'SQL: Structural Query Language' SQL Oracle dual UPPER 4-2 4-2 53
SQL> SELECT UPPER ('sql is used exclusively in rdbmses') 2 FROM dual; 4-2 UPPER('SQLISUSEDEXCLUSIVELYINRDBMSES') ------------------------------------- SQL IS USED EXCLUSIVELY IN RDBMSES INITCAP 4-3 4-3 SQL> SELECT INITCAP('SQL is an ENGLISH LIKE language') 2 FROM dual; 4-3 INITCAP('SQLISANENGLISHLIKELANGUAGE') ------------------------------------- Sql Is An English Like Language CONCAT 4-4 4-4 SQL> SELECT CONCAT('SQL allows you to manipulate the data in DB', 2 ' without any programming knowledge') 3 FROM dual; 4-4 CONCAT('SQLALLOWSYOUTOMANIPULATETHEDATAINDB','WITHOUTANYPROGRAMMINGKNOWLEDGE') --------------------------------------------------------------------------- SQL allows you to manipulate the data in DB without any programming knowledge SUBSTR m,[ n ] m n 4-5 4-5 SQL> SELECT SUBSTR('SQL lets you concentrate on what has to be done',14) 54
2 FROM dual; 4-5 SUBSTR('SQLLETSYOUCONCENTRATEONWHATHASTOBEDONE',14) --------------------------------------------------- concentrate on what has to be done n 14 LENGTH 4-6 4-6 SQL> SELECT LENGTH('SQL does not let you concentrate on how it will be achieved') 2 FROM dual; 4-6 LENGTH('SQLDOESNOTLETYOUCONCENTRATEONHOWITWILLBEACHIVED') --------------------------------------------------------- 58 INSTR [m], [n] m m n 1 4-7 4-7 SQL> SELECT INSTR('SQL allows for dynamic DB changes', 'F') 2 FROM dual; 4-7 INSTR('SQLALLOWSFORDYNAMICDBCHANGES','F') ----------------------------------------- 0 4-7 F f 4-8 4-8 SQL> SELECT INSTR('SQL allows for dynamic DB changes', 'f') 2 FROM dual; 4-8 INSTR('SQLALLOWSFORDYNAMICDBCHANGES','F') 55
----------------------------------------- 12 4-1 4-8 SQL 4-1 SQL 4-2 SQL 4-3 SQL 4-4 SQL 4-5 SQL 4-6 SQL 4-7 SQL 4-8 SQL SQL SQL TRIM [leading trailing both] FROM leading trailing leading trailing both leading trailing TRIM both 8i 8i LTRIM RTRIM 4-9 SQL*PLUS 4-9 SQL> SELECT TRIM('?' FROM '?SQL*PLUS is the SQL implementation 2 used in an Oracle RDBMS or ORDBMS.') 3 FROM dual; 4-9 TRIM('?'FROM'?SQL*PLUSISTHESQLIMPLEMENTATIONUSEDINANORACLERDBMSORORDBMS.') -------------------------------------------------------------------------- SQL*PLUS is the SQL implementation used in an Oracle RDBMS or ORDBMS. 4-10 rows 4-10 SQL> SELECT TRIM('?' FROM 'It can process data in sets of rows??') 2 FROM dual; 4-10 56
TRIM('?'FROM'ITCANPROCESSDATAINSETSOFROWS??') --------------------------------------------- It can process data in sets of rows Oracle Both 4-9? 4-10? Oracle 4-11 TRIM 4-11 SQL> SELECT TRIM('s' FROM 'sql*plus is a fourth generation query languages') 2 FROM dual; 4-11 TRIM('S'FROM'SQL*PLUSISAFOURTHGENERATIONQUERYLANGUAGES') -------------------------------------------------------- ql*plus is a fourth generation query language 4-11 SQL s Oracle s trailing 4-11 SQL 4-12 4-12 SQL> SELECT TRIM(trailing 's' FROM 'sql*plus is a fourth generation query languages') 2 FROM dual; 4-12 TRIM(TRAILING'S'FROM'SQL*PLUSISAFOURTHGENERATIONQUERYLANGUAGES') ---------------------------------------------------------------- sql*plus is a fourth generation query language TRIM Leading REPLACE 4-13 4-13 SQL> SELECT REPLACE('SQL*PLUS supports loops or if statements', 'supports', 2 'does not support') 3 FROM dual; 57
4-13 REPLACE('SQL*PLUSSUPPORTSLOOPSORIFSTATEMENTS','SUPPORTS','DOESNOTSUPPORT') ---------------------------------------------------------------------------- SQL*PLUS does not support loops or if statements 4-9 4-13 SQL*PLUS 4-9 SQL*PLUS SQL Oracle 4-10 4-11 SQL*PLUS 4-12 SQL*PLUS 4-13 SQL*PLUS SQL*PLUS SQL*PLUS SQL SQL*PLUS 2 2-6 4-14 4-14 SQL> SELECT empno, ename, job 2 FROM emp 3 WHERE JOB = 'salesman'; 4-14 2-7 Job SalesMAN 4-15 SQL 4-15 SQL> SELECT empno AS "Code", UPPER(ename) NAME, INITCAP(job) "Job" 2 FROM emp 3 WHERE LOWER(job) = 'salesman'; 58
4-15 Code NAME Job ---------- -------------------- -------- 7499 ALLEN Salesman 7521 WARD Salesman 7654 MARTIN Salesman 7844 TURNER Salesman 4-15 LOWER(job) WHERE UPPER job ='SALESMAN' 4-16 4-16 SQL> SELECT CONCAT(ename, job) "Employee", SUBSTR(job,1,5) "Title", 2 LENGTH(ename) "Length",INSTR(job, 'M') 3 FROM emp 4 WHERE LOWER(job) = 'salesman'; 4-16 Employee Title Length INSTR(JOB,'M') ------------------------------ ---------- ---------- -------------- ALLENSALESMAN SALES 5 6 WARDSALESMAN SALES 4 6 MARTINSALESMAN SALES 6 6 TURNERSALESMAN SALES 6 6 ROUND TRUNC MOD ROUND n n TRUNC n n 59
MOD m n m n 3 4-17 ROUND TRUNC 4-17 SQL> SELECT ROUND(168.888,1), TRUNC(168.888,1) 2 FROM dual; 4-17 ROUND(168.888,1) TRUNC(168.888,1) ---------------- ---------------- 168.9 168.8 4-17 ROUND TRUNC 4-18 ROUND TRUNC 4-18 SQL> SELECT ROUND(168.333,2), TRUNC(168.333,2) 2 FROM dual; 4-18 ROUND(168.333,2) TRUNC(168.333,2) ---------------- ---------------- 168.33 168.33 4-19 ROUND TRUNC 4-19 SQL> SELECT ROUND(168.888,0), TRUNC(168.888,0) 2 FROM dual; 4-19 ROUND(168.888,0) TRUNC(168.888,0) 60
---------------- ---------------- 169 168 4-20 ROUND TRUNC 4-20 SQL> SELECT ROUND(168.888), TRUNC(168.888) 2 FROM dual; 4-20 ROUND(168.888) TRUNC(168.888) -------------- -------------- 169 168 4-21 ROUND TRUNC 4-21 SQL> SELECT ROUND(168.888, -1), TRUNC(168.888, -1) 2 FROM dual; 4-21 ROUND(168.888,-1) TRUNC(168.888,-1) ----------------- ----------------- 170 160 4-22 MOD 1900 400 4-22 SQL> SELECT MOD(1900, 400) 2 FROM dual; 4-22 MOD(1900,400) ------------- 300 4-23 MOD 2000 400 4-23 61
SQL> SELECT MOD(2000, 400) 2 FROM dual; 4-23 MOD(2000,400) ------------- 0 2000 400 0 4-24 MOD 300 400 4-24 SQL> SELECT MOD(300, 400) 2 FROM dual; 4-24 MOD(300,400) ------------ 300 300 400 300 Oracle ROUND TRUNC ROUND TRUNC ROUND TRUNC Oracle Oracle Oracle9i DD-MON-RR DD-MON-YY 2000 Oracle 4712 1 1 9999 12 31 62
4-25 SQL 4-25 SQL> alter session set NLS_DATE_LANGUAGE = 'AMERICAN'; 4-25 Oracle SYSDATE 4-26 4-26 SQL> SELECT SYSDATE 2 FROM dual; 4-26 SYSDATE --------- 05-MAY-02 4-27 4-27 SQL> SELECT SYSDATE - 10 2 FROM dual; 4-27 SYSDATE-1 --------- 25-APR-02 2002 5 5 10 63
2002 4 25 4-28 4-28 SQL> SELECT SYSDATE + 10 2 FROM dual; 4-28 SYSDATE+1 --------- 15-MAY-02 4-29 4-29 SQL> SELECT TO_DATE('15-JUL-02') - SYSDATE 2 FROM dual; 4-29 TO_DATE('15-JUL-02')-SYSDATE ---------------------------- 70.373669 4-29 4-29a SQL 4-29a SQL> SELECT TO_DATE('15-5 -03') - SYSDATE 2 FROM dual; 4-29a TO_DATE('15-5 -03')-SYSDATE ----------------------------- 6.35358796 TO_DATE Oracle 24 4-30 4-31 64
4-30 SQL> SELECT SYSDATE - 22/24 2 FROM dual; 4-30 SYSDATE-2 --------- 04-MAY-02 4-31 SQL> SELECT SYSDATE + 22/24 2 FROM dual; 4-31 SYSDATE+2 --------- 06-MAY-02 4-32 4-32 SQL> SELECT empno, ename, job, sal, (SYSDATE-hiredate)/365 "Years" 2 FROM emp 3 WHERE job LIKE 'SAL%'; 4-32 EMPNO ENAME JOB SAL Years ---------- -------------------- ------------------ ---------- ---------- 7499 ALLEN SALESMAN 1600 21.2182074 7521 WARD SALESMAN 1250 21.212728 7654 MARTIN SALESMAN 1250 20.6154677 7844 TURNER SALESMAN 1500 20.6702622 MONTHS_BETWEEN ADD_MONTHS NEXT_DAY LAST_DAY 65
MONTHS_BETWEEN 1 2 1 2 1 2 1 2 4-33 4-33 SQL> SELECT MONTHS_BETWEEN('01-JUL-99','03-FEB-98') 2 FROM dual; 4-33 MONTHS_BETWEEN('01-JUL-99','03-FEB-98') --------------------------------------- 16.9354839 4-33 1999 7 1 1998 2 3 16.9354839 ADD_MONTHS n n 4-34 4-34 SQL> SELECT ADD_MONTHS('15-OCT-01', 8) 2 FROM dual; 4-34 ADD_MONTH --------- 15-JUN-02 4-34 2001 10 15 8 2002 6 15 NEXT_DAY 4-35 4-35 SQL> SELECT NEXT_DAY('10-MAY-02','MONDAY') 2 FROM dual; 4-35 NEXT_DAY( --------- 66
13-MAY-02 4-35 2002 5 10 2002 5 13 LAST_DAY 4-36 4-36 SQL> SELECT LAST_DAY('08-FEB-02') 2 FROM dual; 4-36 LAST_DAY( --------- 28-FEB-02 4-37 4-37 SQL> SELECT ename, hiredate, LAST_DAY(hiredate), NEXT_DAY(hiredate, 'SUNDAY'), 2 MONTHS_BETWEEN(SYSDATE, hiredate) "Months", 3 ADD_MONTHS(hiredate, 3) "Review" 4 FROM emp; 4-37 ENAME HIREDATE LAST_DAY( NEXT_DAY( Months Review -------------------- --------- --------- --------- ---------- --------- SMITH 17-DEC-80 31-DEC-80 21-DEC-80 256.656332 17-MAR-81 ALLEN 20-FEB-81 28-FEB-81 22-FEB-81 254.559558 20-MAY-81 WARD 22-FEB-81 28-FEB-81 01-MAR-81 254.495042 22-MAY-81 JONES 02-APR-81 30-APR-81 05-APR-81 253.140203 02-JUL-81 MARTIN 28-SEP-81 30-SEP-81 04-OCT-81 247.301493 28-DEC-81 BLAKE 01-MAY-81 31-MAY-81 03-MAY-81 252.172461 01-AUG-81 CLARK 09-JUN-81 30-JUN-81 14-JUN-81 250.914397 09-SEP-81 SCOTT 19-APR-87 30-APR-87 26-APR-87 180.591816 19-JUL-87 KING 17-NOV-81 30-NOV-81 22-NOV-81 245.656332 17-FEB-82 TURNER 08-SEP-81 30-SEP-81 13-SEP-81 247.946655 08-DEC-81 ADAMS 23-MAY-87 31-MAY-87 24-MAY-87 179.462784 23-AUG-87 4-37 SUNDAY SQL 4-38 67
4-38 SQL> SELECT ename, hiredate, LAST_DAY(hiredate), NEXT_DAY(hiredate, ' '), 2 MONTHS_BETWEEN(SYSDATE, hiredate) "Months", 3 ADD_MONTHS(hiredate, 3) "Review" 4 FROM emp; 4-38 ENAME HIREDATE LAST_DAY(H NEXT_DAY(H Months Review ---------- ---------- ---------- ---------- ---------- ------------ SMITH 17-12 -80 31-12 -80 21-12 -80 268.212076 17-3 -81 ALLEN 20-2 -81 28-2 -81 22-2 -81 266.115302 20-5 -81 WARD 22-2 -81 28-2 -81 01-3 -81 266.050786 22-5 -81 JONES 02-4 -81 30-4 -81 05-4 -81 264.695947 02-7 -81 MARTIN 28-9 -81 30-9 -81 04-10 -81 258.857238 28-12 -81 BLAKE 01-5 -81 31-5 -81 03-5 -81 263.728205 01-8 -81 CLARK 09-6 -81 30-6 -81 14-6 -81 262.470141 09-9 -81 SCOTT 19-4 -87 30-4 -87 26-4 -87 192.14756 19-7 -87 KING 17-11 -81 30-11 -81 22-11 -81 257.212076 17-2 -82 TURNER 08-9 -81 30-9 -81 13-9 -81 259.502399 08-12 -81 ADAMS 23-5 -87 31-5 -87 24-5 -87 191 23-8 -87 JAMES 03-12 -81 31-12 -81 06-12 -81 256.663689 03-3 -82 FORD 03-12 -81 31-12 -81 06-12 -81 256.663689 03-3 -82 MILLER 23-1 -82 31-1 -82 24-1 -82 255 23-4 -82 14 ROUND TRUNC ROUND TRUNC 4-39 ROUND 4-39 SQL> SELECT ROUND('28-OCT-01','MONTH') 2 FROM dual; 4-39 SELECT ROUND('28-OCT-01','MONTH') * ERROR 1 : ORA-01722:???? 68
4-39 28-OCT-01 ROUND TO_DATE ROUND TRUNC 4-40 TO_DATE ROUND 4-40 SQL> SELECT ROUND(TO_DATE('28-OCT-01'),'MONTH') 2 FROM dual; 4-40 ROUND(TO_ --------- 01-NOV-01 4-40 MONTH 10 28 10 ROUND 10 28 11 1 3-40 2001 11 1 4-41 TO_DATE ROUND 4-41 SQL> SELECT ROUND(TO_DATE('28-OCT-01'),'YEAR') 2 FROM dual; 4-41 ROUND(TO_ --------- 01-JAN-02 4-41 YEAR 10 28 ROUND 2001 10 28 2002 1 1 4-41 2002 1 1 4-42 TO_DATE TRUNC 4-42 SQL> SELECT TRUNC(TO_DATE('28-OCT-01'),'MONTH') 2 FROM dual; 4-42 69
TRUNC(TO_ --------- 01-OCT-01 4-42 MONTH 10 28 10 TRUNC 10 28 10 1 4-42 2001 10 1 4-43 TO_DATE TRUNC 4-43 SQL> SELECT TRUNC(TO_DATE('28-OCT-01'),'YEAR') 2 FROM dual; 4-43 TRUNC(TO_ --------- 01-JAN-01 4-43 YEAR 2001 10 28 2001 TRUNC 2001 10 28 2001 1 1 4-43 2001 1 1 4-44 4-44 SQL> SELECT ename, hiredate, ROUND(hiredate,'YEAR'), TRUNC(hiredate,'YEAR' 2 ROUND(hiredate, 'MONTH'), TRUNC(hiredate, 'MONTH') 3 FROM emp 4 WHERE hiredate LIKE '%81'; 4-44 ENAME HIREDATE ROUND(HIR TRUNC(HIR ROUND(HIR TRUNC(HIR -------------------- --------- --------- --------- --------- --------- ALLEN 20-FEB-81 01-JAN-81 01-JAN-81 01-MAR-81 01-FEB-81 WARD 22-FEB-81 01-JAN-81 01-JAN-81 01-MAR-81 01-FEB-81 JONES 02-APR-81 01-JAN-81 01-JAN-81 01-APR-81 01-APR-81 MARTIN 28-SEP-81 01-JAN-82 01-JAN-81 01-OCT-81 01-SEP-81 BLAKE 01-MAY-81 01-JAN-81 01-JAN-81 01-MAY-81 01-MAY-81 CLARK 09-JUN-81 01-JAN-81 01-JAN-81 01-JUN-81 01-JUN-81 KING 17-NOV-81 01-JAN-82 01-JAN-81 01-DEC-81 01-NOV-81 TURNER 08-SEP-81 01-JAN-82 01-JAN-81 01-SEP-81 01-SEP-81 70
JAMES FORD 10 03-DEC-81 01-JAN-82 01-JAN-81 01-DEC-81 01-DEC-81 03-DEC-81 01-JAN-82 01-JAN-81 01-DEC-81 01-DEC-81 Oracle VARCHAR2 CHAR NUMBER VARCHAR2 CHAR DATE NUMBER VARCHAR2 DATE VARCHAR2 Oracle VARCHAR2 CHAR NUMBER VARCHAR2 CHAR DATE NUMBER DATE Oracle SQL SQL Oracle Oracle Oracle 3 3 TO_CHAR TO_NUMBER TO_DATE 3 TO_CHAR fmt fmt 4-45 TO_CHAR 71
4-45 SQL> SELECT ename, TO_CHAR(hiredate, 'DD/MM/YY') 2 FROM emp 3 WHERE hiredate LIKE '%82'; 4-45 ENAME TO_CHAR(HIREDATE -------------------- ---------------- MILLER 23/01/82 4-45 ' DD/MM/YY ' YYYY 2001 YEAR NINETEEN EIGHTY-SEVEN MM MONTH DY 3 DAY DD SP TH 4-46 6 MAY TWO THOUSAND TWO 4-46 SQL> SELECT TO_CHAR(SYSDATE,'fmDD MONTH YEAR') 2 FROM dual; 4-46 TO_CHAR(SYSDATE,'FMDDMONTHYEAR') -------------------------------- 6 MAY TWO THOUSAND TWO fm HIREDATE 4-47 SQL*PLUS HIREDATE 60 72
4-47 SQL> col hiredate for a60 4-48 Name Salary HIREDATE 4-48 SQL> SELECT ename "Name", sal "Salary", 2 TO_CHAR(hiredate, 'fmddspth "of" Month Year fmhh:mi:ss AM') HIREDATE 3 FROM emp; 4-48 Name Salary HIREDATE -------- ---------- ---------------------------------------------------- SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER 14 800 Seventeenth of December Nineteen Eighty 12:00:00 AM 1600 Twentieth of February Nineteen Eighty-One 12:00:00 AM 1250 Twenty-Second of February Nineteen Eighty-One 12:00:00 AM 2975 Second of April Nineteen Eighty-One 12:00:00 AM 1250 Twenty-Eighth of September Nineteen Eighty-One 12:00:00 AM 2850 First of May Nineteen Eighty-One 12:00:00 AM 2450 Ninth of June Nineteen Eighty-One 12:00:00 AM 3000 Nineteenth of April Nineteen Eighty-Seven 12:00:00 AM 5000 Seventeenth of November Nineteen Eighty-One 12:00:00 AM 1500 Eighth of September Nineteen Eighty-One 12:00:00 AM 1100 Twenty-Third of May Nineteen Eighty-Seven 12:00:00 AM 950 Third of December Nineteen Eighty-One 12:00:00 AM 3000 Third of December Nineteen Eighty-One 12:00:00 AM 1300 Twenty-Third of January Nineteen Eighty-Two 12:00:00 AM TO_CHAR fmt fmt 9 0 $ L. MI 73
PR 4-49 Name Annual Salary $. 0 TO_CHAR 4-49 SQL> SELECT ename "Name", TO_CHAR(sal*12, '$99,999.00') "Annual Salary" 2 FROM emp; 4-49 Name Annual Salary ---------- ------------- SMITH $9,600.00 ALLEN $19,200.00 WARD $15,000.00 JONES $35,700.00 MARTIN $15,000.00 BLAKE $34,200.00 CLARK $29,400.00 SCOTT $36,000.00 KING $60,000.00 TURNER $18,000.00 ADAMS $13,200.00 JAMES $11,400.00 FORD $36,000.00 MILLER $15,600.00 14 L Oracle RMB 4-50 4-50 SQL> SELECT ename "Name", TO_CHAR(sal*12, 'L99,999.00') "Annual Salary" 2 FROM emp; 4-50 Name Annual Salary ---------- -------------------- SMITH RMB9,600.00 ALLEN RMB19,200.00 WARD RMB15,000.00 JONES RMB35,700.00 74
MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER 14 RMB15,000.00 RMB34,200.00 RMB29,400.00 RMB36,000.00 RMB60,000.00 RMB18,000.00 RMB13,200.00 RMB11,400.00 RMB36,000.00 RMB15,600.00 Oracle Server 4-51 Oracle Server 4-51 SQL> SELECT ename "Name", TO_CHAR(sal*12, 'L9,999.00') "Annual Salary" 2 FROM emp; 4-51 Name Annual Salary ---------- ------------------- SMITH RMB9,600.00 ALLEN ################### WARD ################### JONES ################### MARTIN ################### BLAKE ################### CLARK ################### SCOTT ################### KING ################### TURNER ################### ADAMS ################### JAMES ################### FORD ################### MILLER ################### 14 # TO_CHAR TO_NUMBER TO_DATE TO_NUMBER [ fmt] 75
TO_DATE [ fmt] 4-40 4-43 TO_DATE TO_NUMBER 73 1973 73 2073 Oracle RR RR 1 0~49 0~49 2002 01-OCT-08 RR 2008 10 1 YY 2008 10 1 2 0~49 50~99 2002 01-OCT-98 RR 1998 10 1 YY 2098 10 1 3 50~99 0~49 1999 01-OCT-08 RR 2008 10 1 YY 1908 10 1 4 50~99 50~99 1999 01-OCT-98 RR 1998 10 1 YY 1998 10 1 4-52 1981 Name Salary hiredate 76
4-52 SQL> SELECT ename "Name", job, sal AS "Salary", hiredate 2 FROM emp 3 WHERE hiredate BETWEEN '01-Jan-81' AND '31-Dec-81' 4 ORDER BY hiredate; 4-52 Name JOB Salary HIREDATE -------------------- ------------------ ---------- --------- ALLEN SALESMAN 1600 20-FEB-81 WARD SALESMAN 1250 22-FEB-81 JONES MANAGER 2975 02-APR-81 BLAKE MANAGER 2850 01-MAY-81 CLARK MANAGER 2450 09-JUN-81 TURNER SALESMAN 1500 08-SEP-81 MARTIN SALESMAN 1250 28-SEP-81 KING PRESIDENT 5000 17-NOV-81 JAMES CLERK 950 03-DEC-81 FORD ANALYST 3000 03-DEC-81 10 4-52 4-53 4-53 SQL> SELECT ename "Name", job, sal AS "Salary", hiredate 2 FROM emp 3 WHERE hiredate BETWEEN '01-1 -81' AND '31-12 -81' 4 ORDER BY hiredate; 4-53 Name JOB Salary HIREDATE ---------- --------- ---------- ----------- ALLEN SALESMAN 1600 20-2 -81 WARD SALESMAN 1250 22-2 -81 JONES MANAGER 2975 02-4 -81 BLAKE MANAGER 2850 01-5 -81 CLARK MANAGER 2450 09-6 -81 TURNER SALESMAN 1500 08-9 -81 MARTIN SALESMAN 1250 28-9 -81 KING PRESIDENT 5000 17-11 -81 JAMES CLERK 950 03-12 -81 77
FORD ANALYST 3000 03-12 -81 10 Oracle9i Oracle9i YY emp 2081 Oracle9i RR 4-54 YY Oracle9i 4-54 SQL> SELECT ename "Name", job, sal AS "Salary", hiredate 2 FROM emp 3 WHERE hiredate BETWEEN TO_DATE('01-Jan-81', 'DD-MON-YY') 4 AND TO_DATE('31-Dec-81', 'DD-MON-YY') 5 ORDER BY hiredate; 4-54 RR 4-55 4-55 SQL> SELECT ename "Name", job, sal AS "Salary", hiredate 2 FROM emp 3 WHERE hiredate BETWEEN TO_DATE('01-Jan-81', 'DD-MON-RR') 4 AND TO_DATE('31-Dec-81', 'DD-MON-RR') 5 ORDER BY hiredate; 4-55 Name JOB Salary HIREDATE -------------------- ------------------ ---------- --------- ALLEN SALESMAN 1600 20-FEB-81 WARD SALESMAN 1250 22-FEB-81 JONES MANAGER 2975 02-APR-81 BLAKE MANAGER 2850 01-MAY-81 CLARK MANAGER 2450 09-JUN-81 TURNER SALESMAN 1500 08-SEP-81 MARTIN SALESMAN 1250 28-SEP-81 KING PRESIDENT 5000 17-NOV-81 JAMES CLERK 950 03-DEC-81 FORD ANALYST 3000 03-DEC-81 10 78
4-52 Oracle9i RR 2002 02 1998 98 Oracle dual SQL SQL*PLUS TO_CHAR ROUND TRUNC TO_CHAR RR YY 79
Oracle SQL NULL 3 NULL 5-1 5-1 SQL> SELECT ename, job, sal, comm. 2 FROM emp 3 WHERE job IN ('CLERK','SALESMAN') 4 ORDER BY job; 5-1 ENAME JOB SAL COMM ---------- ---------- ---------- ---------- SMITH CLERK 800 ADAMS CLERK 1100 MILLER CLERK 1300 JAMES CLERK 950 ALLEN SALESMAN 1600 300 WARD SALESMAN 1250 500 MARTIN SALESMAN 1250 1400 TURNER SALESMAN 1500 0 8 5-1 COMM CLERK COMM SALESMAN CLERK CLERK Oracle NULL NULL NULL unavailable unassigned undefined unknown immeasurable inapplicable NULL
BMW NULL NULL NULL NULL + 5-2 5-2 SQL> SELECT ename "Name", sal+comm "Income", job 2 FROM emp 3 WHERE job NOT LIKE 'SALES%' 4 ORDER BY job; 5-2 Name Income JOB -------------------- ---------- --------- SCOTT ANALYST FORD ANALYST SMITH CLERK JAMES CLERK ADAMS CLERK MILLER CLERK JONES MANAGER BLAKE MANAGER CLARK MANAGER 81
KING 10 PRESIDENT 5-2 21 NULL NULL NULL 10% NULL NULL 5-3 5-3 SQL> SELECT empno, ename, sal, job, comm 2 FROM emp 3 WHERE comm = NULL; 5-3 Oracle 5-4 5-4 SQL> SELECT empno, ename, sal, job, comm 2 FROM emp 3 WHERE comm!= NULL; 5-4 NULL NULL Oracle IS NULL IS NULL 5-3 5-5 82
5-5 SQL> SELECT empno, ename, sal, job, comm 2 FROM emp 3 WHERE comm IS NULL; 5-5 EMPNO ENAME SAL JOB COMM -------- --------------- ---------- ------------------ ---------- 7369 SMITH 800 CLERK 7566 JONES 2975 MANAGER 7698 BLAKE 2850 MANAGER 7782 CLARK 2450 MANAGER 7788 SCOTT 3000 ANALYST 7839 KING 5000 PRESIDENT 7876 ADAMS 1100 CLERK 7900 JAMES 950 CLERK 7902 FORD 3000 ANALYST 7934 MILLER 1300 CLERK 10 IS NOT NULL 5-4 5-6 5-6 SQL> SELECT empno, ename, sal, job, comm 2 FROM emp 3 WHERE comm IS NOT NULL; 5-6 EMPNO ENAME SAL JOB COMM ---------- -------------------- ---------- -------------- ------- 7499 ALLEN 1600 SALESMAN 300 7521 WARD 1250 SALESMAN 500 7654 MARTIN 1250 SALESMAN 1400 7844 TURNER 1500 SALESMAN 0 5-6 NULL 5-7 ORDER BY 83
5-7 SQL> SELECT ename, job, comm 2 FROM emp 3 ORDER BY comm; 5-7 ENAME JOB COMM -------------------- ------------------ ---------- TURNER SALESMAN 0 ALLEN SALESMAN 300 WARD SALESMAN 500 MARTIN SALESMAN 1400 SMITH CLERK JONES MANAGER JAMES CLERK MILLER CLERK FORD ANALYST ADAMS CLERK BLAKE MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT 14 5-7 NULL NULL 5-8 ORDER BY DESC 5-8 SQL> SELECT ename, job, comm 2 FROM emp 3 ORDER BY comm DESC; 5-8 ENAME JOB COMM -------------------- --------------- --------- SMITH CLERK JONES MANAGER CLARK MANAGER BLAKE MANAGER SCOTT ANALYST KING PRESIDENT 84
JAMES CLERK MILLER CLERK FORD ANALYST ADAMS CLERK MARTIN SALESMAN 1400 WARD SALESMAN 500 ALLEN SALESMAN 300 TURNER SALESMAN 0 14 5-7 NULL Oracle AND / OR / NOT 3 AND / OR / 1 2 AND OR 1 2 = 2 1 5.1 T F Oracle NULL 1AND 2 1 2 NULL AND F AND F = F F AND T = F F AND NULL = F T AND F = F T AND T = T T AND NULL IS NULL NULL AND F = F NULL AND T IS NULL NULL AND NULL IS NULL 5.1 AND AND F NULL T AND F F F AND NULL NULL 85
F T AND T PR Oracle NULL T AND NULL = NULL 2 2-1 2-8 2-1 1500 2-8 AND 5-9 5-9 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE sal >= 1500 4 AND job IN ('SALESMAN', 'CLERK', 'MANAGER') 5 ORDER BY job; 5-9 EMPNO ENAME SAL JOB ---------- -------------------- ---------- -------- 7566 JONES 2975 MANAGER 7698 BLAKE 2850 MANAGER 7782 CLARK 2450 MANAGER 7499 ALLEN 1600 SALESMAN 7844 TURNER 1500 SALESMAN OR OR T OR T = T T OR F = T T OR NULL = T F OR T = T F OR F = F F OR NULL IS NULL NULL OR T = T NULL OR F IS NULL NULL AND NULL IS NULL 5.1 OR OR 86
T NULL F OR T T T OR NULL NULL T F OR F PR 5-9 WHERE AND OR 5-10 5-10 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE sal >= 1500 4 OR job IN ('SALESMAN', 'CLERK', 'MANAGER') 5 ORDER BY job; 5-10 EMPNO ENAME SAL JOB ---------- ---------- ---------- --------- 7788 SCOTT 3000 ANALYST 7902 FORD 3000 ANALYST 7369 SMITH 800 CLERK 7876 ADAMS 1100 CLERK 7934 MILLER 1300 CLERK 7900 JAMES 950 CLERK 7566 JONES 2975 MANAGER 7782 CLARK 2450 MANAGER 7698 BLAKE 2850 MANAGER 7839 KING 5000 PRESIDENT 7499 ALLEN 1600 SALESMAN 7654 MARTIN 1250 SALESMAN 7844 TURNER 1500 SALESMAN 7521 WARD 1250 SALESMAN 14 NOT NOT 87
NOT T = F NOT F = T Oracle NULL NOT NULL NOT NULL NULL NOT NOT LIKE IS NOT NULL NOT BETWEEN AND 1 2 3 4 IS NULL IS NOT NULL LIKE NOT LIKE IN NOT IN 5 BETWEEN NOT BETWEEN 6 NOT 7 AND 8 OR 5-11 5-11 SQL> SELECT empno, ename, sal, job 2 FROM emp 3 WHERE job = 'CLERK' 4 OR job = 'SALESMAN' 5 AND sal >= 1300; 5-11 EMPNO ENAME SAL JOB ---------- -------------------- ---------- -------- 7369 SMITH 800 CLERK 7499 ALLEN 1600 SALESMAN 7844 TURNER 1500 SALESMAN 7876 ADAMS 1100 CLERK 7900 JAMES 950 CLERK 7934 MILLER 1300 CLERK 6 Oracle 1300 SALESMAN CLERK Oracle CLERK 1300 SALESMAN 88