Transcription

1

2

3

4 00 ii

5 Oracle DB Universal DatabaseDB UDB Oracle ISO/ANSI SQL DB UDB SQL Oracle DB UDB Oracle Database 0g DB UDB V8. DB UDB SQL SQL SQL Oracle DB UDB DB UDB Oracle SQL*Plus DB UDB Command Line ProcessorCLP GUI CLP iii

6 00 iv

7 NULL. NULL n v

8 .8.9 '.0 % ABS 0. ASCII 0. ASCIISTR ASCII. CHR. NCHR. COALESCE. CONCAT.8 COUNT.9 CURRENT_DATE 8.0 CURRENT_TIMESTAMP 9. DBTIMEZONE 80. DECODE 8. DENSE_RANK 8. EXTRACT 8 vi

9 . FIRST 8. FIRST_VALUE 8. FROM_TZ 8.8 GROUP_ID 8.9 GROUPING 89.0 GROUPING_ID 90. LAG 9. LAST 9. LAST_VALUE 9. LEAD 9. LENGTH 9. LENGTHB 9. LTRIM 98.8 LOWER 00.9 MAX 0.0 MIN 0. MOD 0. POWER 0. REPLACE 0. SIGN 0. RTRIM 0. SOUNDEX 09. SUBSTR 0.8 SUBSTRB.9 SUM.0 TRANSLATE. UPPER. USER. ACOS. ASIN. ATAN 8. ATAN 9. COS 0.8 COSH.9 SIN.0 SINH. TAN. TANH. CEIL. FLOOR. ROUND n n 8. ROUND 9. ROUND 0.8 ROUND.9 TRUNC m m vii

10 .0 TRUNC. TRUNC. TRUNC. EXP. LN 8. LOCALTIMESTAMP 9. SQRT 0. AVG.8 INSTR.9 INSTRB.0 INSTRB. LOG. RAWTOHEX. NVL. SYSDATE 8. TO_MULTI_BYTE 9. VSIZE 0. VARIANCE.8 BIN_TO_NUM.9 BITAND.80 CAST.8 INITCAP.8 LPAD.8 RPAD.8 NLS_INITCAP 8.8 NLS_UPPER 9.8 NLS_LOWER 0.8 NTILE.88 HARTOROWID.89 CONVERT.90 ROWIDTOCHAR.9 TO_DATE.9 TO_NUMBER.9 TO_SINGLE_BYTE.9 ADD_MONTH 8.9 LAST_DAY 9.9 LEAST 0.9 MONTHS_BETWEEN.98 NEXT_DAY.99 NEW_TIME.00 GREATEST.0 NULLIF.0 NUMTODSINTERVAL.0 NUMTOYMINTERVAL.0 NVL 8.0 UID 9 viii

11 .0 USERENV 80.0 MEDIAN 8 8. WHERE EXISTS 9. NOT EXISTS SET UPDATE 0. UPDATE 0. FROM JOIN.8 JOIN ix

12 . ORDER BY x

13 CHECK 8. CHECK NOT NULL 90.8 NOT NULL NULL xi

14 TRUNCATE NULL ' xii

15 xiii

16

17

18 . SELECT * FROM SQL> SELECT * FROM DEPT; DEPTNO DNAME LOC ACCOUNTING NEW YORK 0 RESEARCH DALLAS 0 SALES CHICAGO 0 OPERATIONS BOSTON DB SELECT * FROM db=> SELECT * FROM DEPT DEPTNO DNAME LOC ACCOUNTING NEW YORK 0. RESEARCH DALLAS 0. SALES CHICAGO 0. OPERATIONS BOSTON *

19 . SELECT, [, n,] FROM SQL> SELECT DNAME,LOC FROM DEPT; DNAME LOC ACCOUNTING NEW YORK RESEARCH DALLAS SALES CHICAGO OPERATIONS BOSTON DB SELECT, [, n,] FROM db=> SELECT DNAME,LOC FROM DEPT DNAME LOC ACCOUNTING NEW YORK RESEARCH DALLAS SALES CHICAGO OPERATIONS BOSTON SELECT, SELECT

20 . SELECT FROM WHERE = 'yy-mm-dd' yyyy-mm-dd SQL> SELECT ENAME FROM EMP WHERE HIREDATE = '80--'; ENAME SMITH DB SELECT FROM WHERE = 'yyyy-mm-dd' yyyy-mm-ddmm/dd/yyyydd.mm.yyyy SELECT ENAME FROM EMP WHERE HIREDATE = '980--'; ENAME SMITH Oracle yy-mm-dd ' DB UDB ISO JIS yyyy-mm-ddmm/dd/yyyydd.mm.yyyy '

21 . SELECT FROM WHERE = SQL> SELECT ENAME FROM EMP WHERE EMPNO = 9; ENAME SMITH DB SELECT FROM WHERE = db=> SELECT ENAME FROM EMP WHERE EMPNO = 9 ENAME SMITH ' Oracle ' DB UDB '

22 . SELECT FROM WHERE = '' SQL> SELECT EMPNO,HIREDATE FROM EMP WHERE ENAME = 'SMITH'; EMPNO HIREDATE DB SELECT FROM WHERE = '' db=> SELECT EMPNO,HIREDATE FROM EMP WHERE ENAME = 'SMITH' EMPNO HIREDATE '

23 . SELECT FROM WHERE NOT () DB SQL> SELECT * FROM DEPT WHERE NOT (DEPTNO = 0); DEPTNO DNAME LOC RESEARCH DALLAS 0 SALES CHICAGO 0 OPERATIONS BOSTON SELECT FROM WHERE NOT () db=> SELECT * FROM DEPT WHERE NOT (DEPTNO = 0) DEPTNO DNAME LOC RESEARCH DALLAS 0. SALES CHICAGO 0. OPERATIONS BOSTON NOT NOT NOTANDOR <>!= ^=

24 . AND SELECT FROM WHERE AND SQL> SELECT EMPNO,ENAME FROM EMP WHERE JOB = 'MANAGER' AND SAL >=900; EMPNO ENAME JONES DB SELECT FROM WHERE AND db=> SELECT EMPNO,ENAME FROM EMP WHERE JOB = 'MANAGER' AND SAL >=900 EMPNO ENAME JONES ANDORNOT AND OR AND ( ) 8

25 .8 OR SELECT FROM WHERE OR DB SQL> SELECT EMPNO,ENAME FROM EMP WHERE JOB = 'MANAGER' OR SAL >=900; EMPNO ENAME JONES 98 BLAKE 8 CLARK 88 SCOTT 89 KING 90 FORD SELECT FROM WHERE OR db=> SELECT EMPNO,ENAME FROM EMP WHERE JOB = 'MANAGER' OR SAL >=900 EMPNO ENAME JONES 98. BLAKE 8. CLARK 88. SCOTT 89. KING 90. FORD ANDORNOT OR AND AND ( ) 9

26 .9 SELECT FROM WHERE BETWEEN AND SQL> SELECT EMPNO,ENAME FROM EMP WHERE SAL BETWEEN 000 AND 000; EMPNO ENAME JONES 98 BLAKE 8 CLARK 88 SCOTT 90 FORD DB SELECT FROM WHERE BETWEEN AND db=> SELECT EMPNO,ENAME FROM EMP WHERE SAL BETWEEN 000 AND 000 EMPNO ENAME JONES 98. BLAKE 8. CLARK 88. SCOTT 90. FORD ' Oracle NLS_SORT DB UDB COLLATE 0

27 .0 SELECT FROM WHERE >= DB SQL> SELECT EMPNO,ENAME FROM EMP WHERE SAL >= 000; EMPNO ENAME JONES 98 BLAKE 8 CLARK 88 SCOTT 89 KING 90 FORD SELECT FROM WHERE >= db=> SELECT EMPNO,ENAME FROM EMP WHERE SAL >= 000 EMPNO ENAME JONES 98. BLAKE 8. CLARK 88. SCOTT 89. KING 90. FORD ' Oracle NLS_SORT DB UDB COLLATE >

28 . SELECT FROM WHERE <= SQL> SELECT EMPNO,ENAME FROM EMP WHERE SAL <= 000; EMPNO ENAME SMITH 900 JAMES DB SELECT FROM WHERE <= db=> SELECT EMPNO,ENAME FROM EMP WHERE SAL <= 000 EMPNO ENAME SMITH 900. JAMES ' <

29 . IN SELECT FROM WHERE IN (, ) SQL> SELECT ENAME,JOB FROM EMP WHERE EMPNO IN (9,99); ENAME JOB SMITH CLERK ALLEN SALESMAN DB SELECT FROM WHERE IN (, ) db=> SELECT ENAME,JOB FROM EMP WHERE EMPNO IN (9,99) ENAME JOB SMITH CLERK ALLEN SALESMAN ' IN = OR = Oracle IN ANY DB UDB ANY VALUES EMPNO = ANY (VALUES 9, 99)IN

30 . % SELECT FROM WHERE LIKE ' %' SQL> SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE 'S%'; EMPNO ENAME SMITH 88 SCOTT DB SELECT FROM WHERE LIKE ' %' db=> SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE 'S%' EMPNO ENAME SMITH 88. SCOTT % Oracle DB UDB LIKE

31 . _ SELECT FROM WHERE LIKE ' _ ' SQL> SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE 'SMI_H'; EMPNO ENAME SMITH DB SELECT FROM WHERE LIKE ' _ ' db=> SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE 'SMI_H' EMPNO ENAME SMITH _ Oracle _DB UDB Unicode

32 . SELECT FROM WHERE LIKE '% % ' SQL> SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE '%A%N'; EMPNO ENAME ALLEN MARTIN DB SELECT FROM WHERE LIKE '% % ' db=> SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE '%A%N' EMPNO ENAME ALLEN. MARTIN % _ %A%N AN

33 . NULL IS NULL SELECT FROM WHERE IS NULL SQL> SELECT EMPNO,ENAME,COMM FROM EMP WHERE COMM IS NULL; EMPNO ENAME COMM SMITH JONES 98 BLAKE 8 CLARK 88 SCOTT 89 KING 8 ADAMS 900 JAMES 90 FORD 9 MILLER 90 MARY DB SELECT FROM WHERE IS NULL db=> SELECT EMPNO,ENAME,COMM FROM EMP WHERE COMM IS NULL EMPNO ENAME COMM SMITH -. JONES BLAKE - 8. CLARK SCOTT KING - 8. ADAMS JAMES FORD - 9. MILLER MARY - NULL = NULLIS NULL Oracle = NULL DB UDB = NULL Oracle SQL*Plus NULL DB UDB CLP -

34 . NULL IS NOT NULL SELECT FROM WHERE IS NOT NULL SQL> SELECT EMPNO,ENAME,COMM FROM EMP WHERE COMM IS NOT NULL; EMPNO ENAME COMM ALLEN 00 WARD 00 MARTIN 00 8 TURNER 0 DB SELECT FROM WHERE IS NOT NULL db=> SELECT EMPNO,ENAME,COMM FROM EMP WHERE COMM IS NOT NULL EMPNO ENAME COMM ALLEN WARD MARTIN TURNER 0.00 NULL <> IS NOT NULL Oracle <> NULL DB UDB <> NULL 8

35 .8 SELECT SELECT + { } FROM SQL> SELECT SAL,SAL+00 FROM EMP WHERE EMPNO = 9; SAL SAL DB SELECT + { } FROM db=> SELECT SAL,SAL+00 FROM EMP WHERE EMPNO = 9 SAL Oracle DB UDB 9

36 .9 SELECT SELECT - { } FROM SQL> SELECT SAL,SAL-00 FROM EMP WHERE EMPNO = 9; SAL SAL DB SELECT - { } FROM db=> SELECT SAL,SAL-00 FROM EMP WHERE EMPNO = 9 SAL Oracle DB UDB DB UDB DATETIMETIMESTAMP DATE : DEC(8,0) yyyymmdd TIME : DEC(,0) hhmmss TIMESTAMP : DEC(0,) yyyymmddhhmmss.zzzzzz DECIMAL DATETIMETIMESTAMP.0 MEDIAN 0

37 .0 SELECT SELECT * { } FROM SQL> SELECT SAL,SAL*. FROM EMP WHERE EMPNO = 9; SAL SAL* DB SELECT * { } FROM db=> SELECT SAL,SAL*. FROM EMP WHERE EMPNO = 9 SAL * Oracle DB UDB

38 . SELECT SELECT / { } FROM SQL> SELECT SAL,SAL/ FROM EMP WHERE EMPNO = 9; SAL SAL/ DB SELECT / { } FROM db=> SELECT SAL,SAL/ FROM EMP WHERE EMPNO = 9 SAL / Oracle DB UDB 0 DECIMAL

39 . SELECT { } FROM SQL> SELECT EMPNO,ENAME JOB FROM EMP WHERE EMPNO = 9; EMPNO ENAME JOB SMITHCLERK DB SELECT { } FROM db=> SELECT EMPNO,ENAME JOB FROM EMP WHERE EMPNO = 9 EMPNO SMITHCLERK OracleDB UDB CONCAT

40 . n SELECT FROM WHERE ROWNUM <= SQL> SELECT EMPNO,ENAME FROM EMP WHERE ROWNUM <= ; EMPNO ENAME SMITH 99 ALLEN WARD DB SELECT FROM FETCH FIRST ROWS ONLY db=> SELECT EMPNO,ENAME FROM EMP FETCH FIRST ROWS ONLY EMPNO ENAME SMITH 99. ALLEN. WARD Oracle ROWNUM ROWNUM DB UDB FIRST n ROWS ONLY n

41 . SELECT DISTINCT FROM DB SQL> SELECT DISTINCT JOB FROM EMP; JOB ANALYST CLERK MANAGER PRESIDENT SALESMAN SELECT DISTINCT FROM db=> SELECT DISTINCT JOB FROM EMP JOB ANALYST CLERK MANAGER PRESIDENT SALESMAN

42 . SELECT. FROM [AS] SQL> SELECT E.EMPNO,E.ENAME FROM EMP E WHERE E.EMPNO = 9; EMPNO ENAME SMITH SQL> DB SELECT. FROM [AS] db=> SELECT E.EMPNO,E.ENAME FROM EMP E WHERE E.EMPNO = 9 EMPNO ENAME SMITH FROM AS AS DB UDB

43 . SELECT [AS] FROM SQL> SELECT ENAME,SAL* AS NENSYU FROM EMP WHERE EMPNO = 9; ENAME NENSYU SMITH 900 DB SELECT [AS] FROM db=> SELECT ENAME,SAL* AS NENSYU FROM EMP WHERE EMPNO = 9 ENAME NENSYU SMITH SELECT AS AS

44 . SELECT [AS] "" FROM SQL> SELECT ENAME AS "SELECT" FROM EMP WHERE EMPNO = 9; SELECT SMITH DB SELECT [AS] "" FROM db=> SELECT ENAME AS "SELECT" FROM EMP WHERE EMPNO = 9 SELECT SMITH " OracleDB UDB AS 8

45 .8 SELECT FROM ORDER BY [ASC] SQL> SELECT * FROM DEPT ORDER BY DNAME; DEPTNO DNAME LOC ACCOUNTING NEW YORK 0 OPERATIONS BOSTON 0 RESEARCH DALLAS 0 SALES CHICAGO DB SELECT FROM ORDER BY [ASC] db=> SELECT * FROM DEPT ORDER BY DNAME DEPTNO DNAME LOC ACCOUNTING NEW YORK 0. OPERATIONS BOSTON 0. RESEARCH DALLAS 0. SALES CHICAGO ORDER BY ORDER BY 9

46 .9 SELECT FROM ORDER BY DESC SQL> SELECT * FROM DEPT ORDER BY DNAME DESC; DEPTNO DNAME LOC SALES CHICAGO 0 RESEARCH DALLAS 0 OPERATIONS BOSTON 0 ACCOUNTING NEW YORK DB SELECT FROM ORDER BY DESC db=> SELECT * FROM DEPT ORDER BY DNAME DESC DEPTNO DNAME LOC SALES CHICAGO 0. RESEARCH DALLAS 0. OPERATIONS BOSTON 0. ACCOUNTING NEW YORK ORDER BY ORDER BY DESCDESCENDING 0

47 .0 SELECT FROM ORDER BY [ASC], [ASC] SQL> SELECT DEPTNO,SAL FROM EMP WHERE JOB IN ('SALESMAN','MANAGER') ORDER BY DEPTNO,SAL; DEPTNO SAL DB SELECT FROM ORDER BY [ASC], [ASC] db=> SELECT DEPTNO,SAL FROM EMP WHERE JOB IN ('SALESMAN','MANAGER') ORDER BY DEPTNO,SAL DEPTNO SAL ORDER BY, SELECT

48 . SELECT FROM ORDER BY [ASC], DESC SQL> SELECT DEPTNO,SAL FROM EMP WHERE JOB IN ('SALESMAN','MANAGER') ORDER BY DEPTNO,SAL DESC; DEPTNO SAL DB SELECT FROM ORDER BY [ASC], DESC db=> SELECT DEPTNO,SAL FROM EMP WHERE JOB IN ('SALESMAN','MANAGER') ORDER BY DEPTNO DESC,SAL DESC DEPTNO SAL ORDER BY, ORDER BY ASCASCENDING DESCDESCENDING ORDER BY ASC

49 . ORDER BY SELECT FROM ORDER BY, SQL> SELECT DEPTNO,SAL FROM EMP WHERE JOB IN ('SALESMAN','MANAGER') ORDER BY,; DEPTNO SAL DB SELECT FROM ORDER BY, db=> SELECT DEPTNO,SAL FROM EMP WHERE JOB IN ('SALESMAN','MANAGER') ORDER BY, DEPTNO SAL SELECT ORDER BY SQL SELECT

50 . SELECT SELECT AS FROM ORDER BY SQL> SELECT ENAME,SAL* AS NENSYU FROM EMP WHERE DEPTNO = 0 ORDER BY NENSYU; ENAME NENSYU MILLER 00 CLARK 900 KING 0000 DB SELECT AS FROM ORDER BY db=> SELECT ENAME,SAL* AS NENSYU FROM EMP WHERE DEPTNO = 0 ORDER BY NENSYU ENAME NENSYU MILLER CLARK KING SELECT ORDER BY SQL SELECT ORDER BY

51 . SELECT FROM CONNECT BY PRIOR = START WITH = SQL> SELECT LEVEL,LPAD(' ',LEVEL*,' ') ENAME AS ENAME,DEPTNO FROM EMP CONNECT BY PRIOR EMPNO = MGR START WITH JOB = 'PRESIDENT'; LEVEL ENAME DEPTNO KING 0 JONES 0 SCOTT 0 ADAMS 0 FORD 0 SMITH 0 BLAKE 0 ALLEN 0 WARD 0 MARTIN 0 TURNER 0 JAMES 0 CLARK 0 MILLER 0 Oracle CONNECT BY START WITH CONNECT BY PRIOR START WITH SQL TOP LEVEL LPAD DB WITH AS ( UNION ALL SELECT FROM [, ] WHERE = ) SELECT WITH Recurse (level, empno, ename, deptno, ancestors) AS ( SELECT, empno, ename, deptno, CAST(CHAR(ename, 0) AS VARCHAR(00)) FROM EMP WHERE job = 'PRESIDENT' UNION ALL SELECT level +, new.empno, new.ename, new.deptno, pre.ancestors CHAR(new.ename, 0) FROM Recurse pre, EMP new

52 WHERE level < 0 AND pre.empno = new.mgr ) SELECT level, SUBSTR(' ',, level*) ename as ename, deptno FROM Recurse ORDER BY ancestors; LEVEL ENAME DEPTNO KING 0 BLAKE 0 ALLEN 0 JAMES 0 MARTIN 0 TURNER 0 WARD 0 CLARK 0 MILLER 0 JONES 0 FORD 0 SMITH 0 SCOTT 0 ADAMS 0 Oracle DB UDB ORDER BY SQL SELECT INSERT ROWNUM SELECT e.*, ROWNUMBER() OVER() rownum FROM EMP e; SELECT e.*, ROWNUMBER() OVER() rownum FROM EMP e EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ROWNUM 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 record(s) selected.

53 Oracle WITH Sequed (empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum) AS ( SELECT e.*, ROWNUMBER() OVER() rownum FROM EMP e ), Recurse (level, empno, ename, deptno, ancestors) AS ( SELECT, empno, ename, deptno, CAST(DIGITS(SMALLINT(rownum)) AS VARCHAR(0)) FROM Sequed WHERE job = 'PRESIDENT' UNION ALL SELECT level +, new.empno, new.ename, new.deptno, pre.ancestors DIGITS(SMALLINT(rownum)) FROM Recurse pre, Sequed new WHERE level < 0 AND pre.empno = new.mgr ) SELECT level, SUBSTR(' ',, level*) ename as ename, deptno FROM Recurse ORDER BY ancestors; LEVEL ENAME DEPTNO KING 0 JONES 0 SCOTT 0 ADAMS 0 FORD 0 SMITH 0 BLAKE 0 ALLEN 0 WARD 0 MARTIN 0 TURNER 0 JAMES 0 CLARK 0 MILLER 0

54 . SELECT FROM CONNECT BY PRIOR = AND START WITH = SQL> SELECT LEVEL,LPAD(' ',LEVEL*,' ') ENAME AS ENAME,DEPTNO FROM EMP CONNECT BY PRIOR EMPNO = MGR AND ENAME <> 'JONES' START WITH JOB = 'PRESIDENT'; LEVEL ENAME DEPTNO KING 0 BLAKE 0 ALLEN 0 WARD 0 MARTIN 0 TURNER 0 JAMES 0 CLARK 0 MILLER 0 9 WHERE CONNECT BY PRIOR DB WITH AS ( UNION ALL SELECT FROM [, ] WHERE = AND ) SELECT WITH Recurse (level, empno, ename, deptno, ancestors) AS ( SELECT, empno, ename, deptno, CAST(CHAR(ename, 0) AS VARCHAR(00)) FROM EMP WHERE job = 'PRESIDENT' UNION ALL SELECT level +, new.empno, new.ename, new.deptno, pre.ancestors CHAR(new.ename, 0) FROM Recurse pre, EMP new WHERE level < 0 AND pre.empno = new.mgr AND new.ename <> 'JONES' ) SELECT level, SUBSTR(' ',, level*) ename as ename, deptno FROM Recurse 8

55 ORDER BY ancestors; LEVEL ENAME DEPTNO KING 0 BLAKE 0 ALLEN 0 JAMES 0 MARTIN 0 TURNER 0 WARD 0 CLARK 0 MILLER 0 9 9

56 . SELECT FROM CONNECT BY PRIOR = START WITH = SQL> SELECT LEVEL,LPAD(' ',LEVEL*,' ') ENAME AS ENAME,DEPTNO FROM EMP CONNECT BY PRIOR EMPNO = MGR START WITH ENAME = 'JONES'; LEVEL ENAME DEPTNO JONES 0 SCOTT 0 ADAMS 0 FORD 0 SMITH 0 START WITH TOP DB WITH AS ( UNION ALL SELECT FROM [, ] WHERE = AND ) SELECT WITH Recurse (level, empno, ename, deptno, ancestors) AS ( SELECT, empno, ename, deptno, CAST(CHAR(ename, 0) AS VARCHAR(00)) FROM EMP WHERE ename = 'JONES' UNION ALL SELECT level +, new.empno, new.ename, new.deptno, pre.ancestors CHAR(new.ename, 0) FROM Recurse pre, EMP new WHERE level < 0 AND pre.empno = new.mgr ) SELECT level, SUBSTR(' ',, level*) ename as ename, deptno FROM Recurse ORDER BY ancestors; LEVEL ENAME DEPTNO 0

57 JONES 0 FORD 0 SMITH 0 SCOTT 0 ADAMS 0 WITH AS ( UNION ALLTOP WHERE ename = 'JONES' TOP

58 . SELECT FROM CONNECT BY PRIOR = START WITH = SQL> SELECT LEVEL,LPAD(' ',LEVEL*,' ') ENAME AS ENAME,DEPTNO FROM EMP CONNECT BY PRIOR MGR = EMPNO START WITH ENAME = 'SMITH'; LEVEL ENAME DEPTNO SMITH 0 FORD 0 JONES 0 KING 0 CONNECT BY PRIOR START WITH LEVEL DB WITH AS ( UNION ALL SELECT FROM [, ] WHERE = ) SELECT WITH Recurse (level, empno, ename, deptno, mgr, ancestors) AS ( SELECT, empno, ename, deptno, mgr, CAST(CHAR(ename, 0) AS VARCHAR(00)) FROM EMP WHERE ename = 'SMITH' UNION ALL SELECT level +, new.empno, new.ename, new.deptno, new.mgr, pre.ancestors CHAR(new.ename, 0) FROM Recurse pre, EMP new WHERE level < 0 AND pre.mgr = new.empno ) SELECT level, SUBSTR(' ',, level*) ename as ename, deptno FROM Recurse ORDER BY ancestors; LEVEL ENAME DEPTNO SMITH 0

59 FORD 0 JONES 0 KING 0

60 .8 SELECT - FROM SQL> SELECT TO_DATE('00-0-','YYYY-MM-DD')-TO_DATE('00-0-0','YYYY- MM-DD') FROM DUAL; TO_DATE('00-0-','YYYY-MM-DD')-TO_DATE('00-0-0','YYYY-MM-DD') DB SELECT DAYS( ) - DAYS( ) FROM SELECT DAYS('00-0-') - DAYS('00-0-0') FROM SYSIBM.SYSDUMMY; ' Oracle

61 .9 ' SELECT FROM WHERE LIKE '%''%' SQL> SELECT COL FROM TEST WHERE COL LIKE '%''%'; COL I'm a students DB SELECT FROM WHERE LIKE '%''%' db=> SELECT COL FROM TEST WHERE COL LIKE '%''%' COL I'm a students ' ' ''

62 .0 % _ SELECT FROM WHERE LIKE ' %' escape ' ' SQL> SELECT COL FROM TEST WHERE COL LIKE '% %' ESCAPE ' '; COL TAX RATE % DB SELECT FROM WHERE LIKE ' %' escape ' ' db=> SELECT COL FROM TEST WHERE COL LIKE '% %' ESCAPE ' ' COL TAX RATE % _ % _ %

63

64 . SELECT AVG() FROM SQL> SELECT AVG(SAL) FROM EMP; AVG(SAL) DB SELECT AVG() FROM db=> SELECT AVG(SAL) FROM EMP AVG NULL 8

65 . SELECT COUNT(*) FROM SQL> SELECT COUNT(*) FROM EMP; COUNT(*) DB SELECT COUNT(*) FROM db=> SELECT COUNT(*) FROM EMP AVGSUMMAXMINCOUNT NULL COUNT * NULL 9

66 . NULL SELECT COUNT() FROM SQL> SELECT COUNT(JOB),COUNT(COMM) FROM EMP; COUNT(JOB) COUNT(COMM) DB SELECT COUNT() FROM db=> SELECT COUNT(JOB),COUNT(COMM) FROM EMP COUNT NULL 0

67 . SELECT COUNT(DISTINCT ) FROM SQL> SELECT COUNT(DISTINCT JOB) FROM EMP; COUNT(DISTINCTJOB) DB SELECT COUNT(DISTINCT ) FROM db=> SELECT COUNT(DISTINCT JOB) FROM EMP COUNT NULL DISTINCT

68 . SELECT MAX() FROM SQL> SELECT MAX(SAL) FROM EMP; MAX(SAL) DB SELECT MAX() FROM db=> SELECT MAX(SAL) FROM EMP MAX NULL

69 . SELECT MIN() FROM SQL> SELECT MIN(SAL) FROM EMP; MIN(SAL) DB SELECT MIN() FROM db=> SELECT MIN(SAL) FROM EMP MIN NULL

70 . SELECT SUM() FROM SQL> SELECT SUM(SAL) FROM EMP; SUM(SAL) DB SELECT SUM() FROM db=> SELECT SUM(SAL) FROM EMP SUM NULL

71 .8 DB SELECT [,] FROM GROUP BY SQL> SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO; DEPTNO SUM(SAL) SELECT [,] FROM GROUP BY db=> SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO DEPTNO GROUP BY NULL GROUP BY

72 .9 SELECT [,] FROM GROUP BY HAVING () SQL> SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING SUM(SAL) >=0000; DEPTNO SUM(SAL) DB SELECT [,] FROM GROUP BY HAVING () db=> SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING SUM(SAL) >=0000 DEPTNO HAVING GROUP BY

73 .0 DB SELECT [,] FROM GROUP BY HAVING () > SQL> SELECT JOB,COUNT(*) FROM EMP GROUP BY JOB HAVING COUNT(*) >; JOB COUNT(*) ANALYST CLERK MANAGER SALESMAN SELECT [,] FROM GROUP BY HAVING () > db=> SELECT JOB,COUNT(*) FROM EMP GROUP BY JOB HAVING COUNT(*) > JOB ANALYST CLERK MANAGER SALESMAN GROUP BY

74 . SELECT FROM GROUP BY HAVING () = SQL> SELECT JOB FROM EMP GROUP BY JOB HAVING COUNT(*)=; JOB PRESIDENT DB SELECT FROM GROUP BY HAVING () = SELECT JOB FROM EMP GROUP BY JOB HAVING COUNT(*) = ; JOB PRESIDENT GROUP BY 8

75 . DB SELECT [,] FROM WHERE GROUP BY SQL> SELECT DEPTNO,COUNT(*) FROM EMP WHERE DEPTNO >= 0 GROUP BY DEPTNO; DEPTNO COUNT(*) SELECT [,] FROM WHERE GROUP BY db=> SELECT DEPTNO,COUNT(*) FROM EMP WHERE DEPTNO >= 0 GROUP BY DEPTNO DEPTNO WHERE WHERE FROM WHERE 9

76 . SELECT [,] FROM GROUP BY ROLLUP(, ) SQL> SELECT DEPTNO,JOB,SUM(SAL) FROM EMP WHERE DEPTNO IS NOT NULL GROUP BY ROLLUP(DEPTNO,JOB); DEPTNO JOB SUM(SAL) CLERK 00 0 MANAGER 0 0 PRESIDENT CLERK ANALYST MANAGER CLERK 90 0 MANAGER 80 0 SALESMAN DB SELECT [, AS ] FROM GROUP BY ROLLUP(, ) ORDER BY { } SELECT DEPTNO,JOB,SUM(SAL) FROM EMP WHERE DEPTNO IS NOT NULL GROUP BY ROLLUP(DEPTNO,JOB) ORDER BY DEPTNO, JOB; DEPTNO JOB CLERK MANAGER PRESIDENT ANALYST CLERK MANAGER CLERK MANAGER SALESMAN

77 GROUP BY ROLLUP(DEPTNO,JOB)DEPTNO JOB DEPTNO ORDER BY GROUP BY GROUP BY ROLLUP Oracle DB UDB DB UDB ORDER BY ORDER BY

78 . SELECT [,] FROM GROUP BY CUBE(, ) SQL> SELECT DEPTNO,JOB,SUM(SAL) FROM EMP WHERE DEPTNO IS NOT NULL GROUP BY CUBE(DEPTNO,JOB); DEPTNO JOB SUM(SAL) CLERK 0 ANALYST 000 MANAGER 8 SALESMAN 00 PRESIDENT CLERK 00 0 MANAGER 0 0 PRESIDENT CLERK ANALYST MANAGER CLERK 90 0 MANAGER 80 0 SALESMAN 00 8 DB SELECT [,] FROM GROUP BY CUBE(, ) ORDER BY { } SELECT DEPTNO,JOB,SUM(SAL) FROM EMP WHERE DEPTNO IS NOT NULL GROUP BY CUBE(DEPTNO,JOB) ORDER BY COALESCE(DEPTNO, 0), COALESCE(JOB, ''); DEPTNO JOB SUM(SAL) ANALYST CLERK MANAGER PRESIDENT SALESMAN 00.00

79 CLERK MANAGER PRESIDENT ANALYST CLERK MANAGER CLERK MANAGER SALESMAN GROUP BY CUBE(DEPTNO,JOB) DEPTNO JOB DEPTNO JOB ORDER BY GROUP BY GROUP BY CUBE Oracle DB UDB DB UDB ORDER BY ORDER BY DB UDB Oracle NULL COALESCE

80 . SELECT [,] FROM GROUP BY GROUPING SETS(, ) SQL> SELECT DEPTNO,JOB,SUM(SAL) FROM EMP WHERE DEPTNO IS NOT NULL GROUP BY GROUPING SETS(DEPTNO,JOB); DEPTNO JOB SUM(SAL) ANALYST 000 CLERK 0 MANAGER 8 PRESIDENT 000 SALESMAN 00 8 DB SELECT [,] FROM GROUP BY GROUPING SETS(, ) ORDER BY { } SELECT DEPTNO,JOB,SUM(SAL) FROM EMP WHERE DEPTNO IS NOT NULL GROUP BY GROUPING SETS(DEPTNO,JOB) ORDER BY DEPTNO,JOB; DEPTNO JOB ANALYST CLERK MANAGER PRESIDENT SALESMAN

81 GROUP BY GROUPING SETS(DEPTNO, JOB) DEPTNO JOB DEPTNO JOB ORDER BY GROUP BY GROUP BY GROUPING SET Oracle DB UDB DB UDB ORDER BY ORDER BY

82 . SELECT [,] FROM GROUP BY TO_CHAR(,'YY') SQL> SELECT TO_CHAR(HIREDATE,'YY'),SUM(SAL) FROM EMP GROUP BY TO_CHAR( HIREDATE,'YY'); TO SUM(SAL) DB SELECT [,] FROM GROUP BY YEAR() db=> SELECT YEAR(HIREDATE),SUM(SAL) FROM EMP GROUP BY YEAR(HIREDATE) GROUP BY

83 . SELECT FROM WHERE = (SELECT MAX( ) FROM ) SQL> SELECT ENAME,SAL FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP); ENAME SAL KING 000 DB SELECT FROM WHERE = (SELECT MAX( ) FROM ) db=> SELECT ENAME,SAL FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP) ENAME SAL KING

84

85

86 . n ABS(n) SQL> SELECT ABS(-0) FROM DUAL; ABS(-0) DB ABS(n) db=> SELECT ABS(-0) FROM SYSIBM.SYSDUMMY UDF Column Oracle DB UDFUser Definition Function Oracle DB UDF UDFSQLIBM DB Migration ToolkitMTK Oracle SQL UDF Sample UDFs for Migration MTK MTK 0

87 . 0 DB ASCII() SQL> SELECT ASCII('A') FROM DUAL; ASCII('A') ASCII() db=> SELECT ASCII('A') FROM SYSIBM.SYSDUMMY

88 . ASCII ASCIISTR() SQL> SELECT ASCIISTR(''),ASCIISTR(' ') FROM DUAL; ASCI ASCIISTR(' ') DB Oracle ASCIISTR ASCII xxxx xxxx UTF-

89 . CHR USINGNCHAR_CS n VARCHAR DB CHR(n) SQL> SELECT CHR(),CHR(),CHR() FROM DUAL; C C C A B C CHR(n) db=> SELECT CHR(),CHR(),CHR() FROM SYSIBM.SYSDUMMY A B C DB UDB ASCII

90 . number CHR USING NCHAR_CS NCHR(n) SQL> SELECT NCHR(),NCHR(),NCHR() FROM DUAL; NC NC NC A B C DB

91 . NULL NULL NULL COALESCE(,,) SQL> SELECT COMM,MGR,EMPNO,COALESCE(COMM,MGR,EMPNO) FROM EMP WHERE DEPTNO IN (0,0); DB COMM MGR EMPNO COALESCE(COMM,MGR,EMPNO) COALESCE(,,) db=> SELECT COMM,MGR,EMPNO,COALESCE(COMM,MGR,EMPNO) FROM EMP WHERE DEPTNO IN (0,0) COMM MGR EMPNO

92 . CONCAT(, ) SQL> SELECT CONCAT('POST','MAN') FROM DUAL; CONCAT( POSTMAN DB CONCAT(, ) db=> SELECT CONCAT('POST','MAN') FROM SYSIBM.SYSDUMMY POSTMAN CONCAT

93 .8 NULL COUNT([DISTICT ALL] *) SQL> SELECT COUNT(*) FROM EMP; COUNT(*) DB COUNT([DISTICT ALL] *) db=> SELECT COUNT(*) FROM EMP AVGSUMMAXMINCOUNT NULL COUNT * NULL

94 .9 DATE CURRENT_DATE SQL> SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL; SESSIONTIMEZONE CURRENT_ : DB CURRENT_DATE CURRENT DATE db=> SELECT CURRENT_TIMEZONE,CURRENT_DATE FROM SYSIBM.SYSDUMMY

95 .0 Oracle TIMESTAMP WITH TIME ZONE DB CURRENT_TIMESTAMP SQL> SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL; SESSIONTIMEZONE CURRENT_TIMESTAMP : :0: :00 CURRENT_TIMESTAMP CURRENT TIMESTAMP db=> SELECT CURRENT_TIMEZONE,CURRENT_TIMESTAMP FROM SYSIBM.SYSDUMMY DB UDB ZONE 9

96 . TIMESTAMP WITH TIME ZONE DBTIMEZONE SQL> SELECT DBTIMEZONE FROM DUAL; DBTIME :00 DB CURRENT TIMEZONE db=> SELECT CURRENT TIMEZONE FROM SYSIBM.SYSDUMMY Oracle DB UDB SQL Oracle DB UDB 80

97 . NULL DB DECODE(,,,,,) SQL> SELECT DEPTNO,DECODE(DEPTNO,0,'',0,'','') FROM EMP WHERE JOB = 'MANAGER'; DEPTNO DECO CASE SELECT DEPTNO, CASE DEPTNO WHEN 0 THEN '' WHEN 0 THEN '' ELSE '' END FROM EMP WHERE JOB = 'MANAGER'; DEPTNO DB UDB SQL CASE 8

98 . DENSE_RANK ( ) OVER ( ORDER BY {DESC ASC} ) SQL> SELECT empno, ename, sal, DENSE_RANK() OVER(ORDER BY sal) drank FROM EMP WHERE deptno = 0 ORDER BY drank, empno; DB DENSE_RANK ( ) OVER ( ORDER BY {DESC ASC} ) SELECT empno, ename, sal, DENSE_RANK() OVER(ORDER BY sal) drank FROM EMP WHERE deptno = 0 ORDER BY drank, empno; EMPNO ENAME SAL DRANK JAMES WARD 0.00 MARTIN TURNER ALLEN BLAKE

99 . DB EXTRACT( {YEAR MONTH DAY HOUR MINUTE SECOND TIMEZONE_HOUR TIMEZONE_MINUTE TIMEZONE_REGION TIMEZONE_ABBR} FROM { } ) SQL> SELECT EXTRACT(YEAR FROM HIREDATE) FROM EMP WHERE EMPNO = 9; EXTRACT(YEARFROMHIREDATE) YEAR MONTH DAY HOUR MINUTE SECOND MICROSECOND( ) db=> SELECT YEAR(HIREDATE) FROM EMP WHERE EMPNO = Oracle TO_CHAR DB UDB HOUR()MINUTE()SECOND() 8

100 . FIRST LAST aggregate_function KEEP (DENSE_RANK FIRST ORDER BY expr {DESC ASC} [NULLS] { FIRST LAST }) [OVER query_partition_clause] SQL> SELECT DEPTNO, MIN(SAL) KEEP (DENSE_RANK FIRST ORDER BY HIREDATE) "Worst" FROM EMP WHERE DEPTNO IS NOT NULL GROUP BY DEPTNO; DEPTNO Worst DB SELECT DEPTNO, MIN(SAL) "Worst" FROM (SELECT DEPTNO, SAL, DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY HIREDATE) drank FROM EMP WHERE DEPTNO IS NOT NULL ) Q WHERE drank = GROUP BY DEPTNO; DEPTNO Worst

101 . NULL IGNORE NULLS NULL FIRST_VALUE ( expr [IGNORE NULLS] ) OVER ( analytic_clause ) 0 SQL> SELECT DEPTNO, ENAME, SAL, FIRST_VALUE(ENAME) OVER (ORDER BY SAL ASC ROWS UNBOUNDED PRECEDING) AS lowest_sal FROM (SELECT * FROM EMP WHERE DEPTNO = 0 ORDER BY EMPNO); DB DEPTNO ENAME SAL LOWEST_SAL MILLER 00 MILLER 0 CLARK 0 MILLER 0 KING 000 MILLER WITH Q AS ( SELECT * FROM EMP WHERE DEPTNO = 0 ) SELECT DEPTNO, ENAME, SAL, (SELECT ENAME FROM (SELECT ENAME, ROWNUMBER() OVER(ORDER BY SAL ASC, EMPNO) rn FROM Q ) P WHERE rn = ) AS lowest_sal FROM Q ORDER BY SAL, EMPNO; DEPTNO ENAME SAL LOWEST_SAL MILLER MILLER 0 CLARK 0.00 MILLER 0 KING MILLER DB UDB OLAP 8

102 . TIMESTAMP WITH TIME ZONE FROM_TZ ( timestamp_value, time_zone_value ) SQL> SELECT FROM_TZ(TIMESTAMP ' :00:00', ':00') FROM DUAL; FROM_TZ(TIMESTAMP' :00:00',':00') :00: :00 DB TIMESTAMP WITH TIME ZONE db=> SELECT CURRENT TIMESTAMP, CURRENT TIMEZONE FROM SYSIBM.SYSDUMMY

103 .8 GROUP BY GROUP_ID() SQL> SELECT DEPTNO,JOB,SUM(SAL),GROUP_ID() FROM EMP WHERE DEPTNO IS NOT NULL GROUP BY DEPTNO,ROLLUP(DEPTNO,JOB); DEPTNO JOB SUM(SAL) GROUP_ID() CLERK MANAGER PRESIDENT CLERK ANALYST MANAGER CLERK MANAGER SALESMAN DB SELECT DEPTNO AS DEPTNO, JOB, DEC(SAL,0,) SAL, GROUP_ID FROM (SELECT DEPTNO, DEPTNO, JOB, SUM(SAL) SAL, GROUPING(DEPTNO) GROUP_ID, GROUPING(JOB) G_JOB FROM (SELECT DEPTNO DEPTNO, DEPTNO DEPTNO, JOB, SAL FROM EMP WHERE DEPTNO IS NOT NULL ) Q GROUP BY DEPTNO, ROLLUP(DEPTNO, JOB) ) R ORDER BY GROUP_ID, G_JOB, DEPTNO, JOB; DEPTNO JOB SAL GROUP_ID CLERK MANAGER PRESIDENT ANALYST

104 0 CLERK MANAGER CLERK MANAGER SALESMAN

105 .9 GROUPING GROUP BY NULL 0 ROLLUP CUBE GROUP BY NULL GROUPING NULL NULL GROUPING() SQL> SELECT DECODE(GROUPING(DEPTNO),,'',DEPTNO) AS DEPTNO,SUM(SAL) FROM EMP WHERE DEPTNO IS NOT NULL GROUP BY ROLLUP(DEPTNO); DB DEPTNO SUM(SAL) GROUPING() SELECT CASE GROUPING(DEPTNO) WHEN THEN '' ELSE CHAR(DEPTNO) END AS DEPTNO, SUM(SAL) "SUM(SAL)" FROM EMP WHERE DEPTNO IS NOT NULL GROUP BY ROLLUP(DEPTNO) ORDER BY DEPTNO; DEPTNO SUM(SAL) GROUP BY 89

106 .0 GROUPING 0 GROUPING_ID() SQL> SELECT DEPTNO,JOB,SUM(SAL),GROUPING_ID(DEPTNO,JOB) FROM EMP WHERE DEPTNO IS NOT NULL GROUP BY ROLLUP(DEPTNO,JOB); DEPTNO JOB SUM(SAL) GROUPING_ID(DEPTNO,JOB) CLERK MANAGER PRESIDENT CLERK ANALYST MANAGER CLERK MANAGER SALESMAN DB GROUP BY ROLLUP(,,..., n) GROUPING( n)+grouping( n-)* GROUPING( )*POWER(,n-) SELECT DEPTNO,JOB,SUM(SAL), GROUPING(JOB)+GROUPING(DEPTNO)* Grouping_ID FROM EMP WHERE DEPTNO IS NOT NULL GROUP BY ROLLUP(DEPTNO,JOB) ORDER BY DEPTNO, JOB; DEPTNO JOB GROUPING_ID CLERK MANAGER PRESIDENT ANALYST CLERK MANAGER CLERK MANAGER

107 0 SALESMAN DB UDB GROUPING_ID GROUPING 9

108 . LAG ( value_expr[, offset ][, default ]) OVER ([query_partition_clause] order_by_clause ) SQL> SELECT ENAME,HIREDATE,SAL,LAG(SAL,) OVER (ORDER BY HIREDATE) AS PREV FROM EMP WHERE DEPTNO = 0; ENAME HIREDATE SAL PREV CLARK KING MILLER DB SELECT ENAME,HIREDATE,SAL, MAX(SAL) OVER (ORDER BY HIREDATE ROWS BETWEEN PRECEDING AND PRECEDING) AS PREV FROM EMP WHERE DEPTNO = 0; ENAME HIREDATE SAL PREV CLARK KING MILLER

109 . FIRST LAST aggregate_function KEEP (DENSE_RANK LAST ORDER BY expr {DESC ASC}[NULLS] {FIRST LAST}) [OVER query_partition_clause] SQL> SELECT DEPTNO, MAX(SAL) KEEP (DENSE_RANK LAST ORDER BY HIREDATE) "Best" FROM EMP WHERE DEPTNO IS NOT NULL GROUP BY DEPTNO; DEPTNO Best DB SELECT DEPTNO, MAX(SAL) "Best" FROM (SELECT EMP.*, DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY HIREDATE DESC) drank FROM EMP) Q WHERE DEPTNO IS NOT NULL AND drank = GROUP BY DEPTNO; DEPTNO Best

110 . NULL IGNORE NULLS NULL LAST_VALUE ( expr [IGNORE NULLS] ) OVER ( analytic_clause ) SQL>SELECT ename, sal, hiredate, LAST_VALUE(hiredate) OVER (ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv FROM (SELECT * FROM EMP WHERE deptno = 0 ORDER BY hiredate); DB WITH Q AS ( SELECT * FROM EMP WHERE deptno = 0 ) SELECT ename, sal, hiredate, (SELECT hiredate FROM (SELECT hiredate, ROWNUMBER() OVER(ORDER BY sal DESC, hiredate DESC) AS rn FROM Q ) AS P WHERE rn = ) AS lv FROM Q ORDER BY sal, hiredate; ENAME SAL HIREDATE LV MILLER CLARK KING

111 . LEAD ( value_expr[, offset ][, default]) OVER ([query_partition_clause] order_by_clause ) SQL> SELECT ENAME,HIREDATE,SAL,LEAD(SAL,) OVER (ORDER BY HIREDATE) AS NEXT FROM EMP WHERE DEPTNO = 0; ENAME HIREDATE SAL NEXT CLARK KING MILLER DB SELECT ENAME,HIREDATE,SAL, MAX(SAL) OVER(ORDER BY HIREDATE ROWS BETWEEN FOLLOWING AND FOLLOWING) AS NEXT FROM EMP WHERE DEPTNO = 0; ENAME HIREDATE SAL NEXT CLARK KING MILLER

112 . LENGTH() SQL> SELECT LENGTH('ABC'),LENGTH('') FROM DUAL; LENGTH('ABC') LENGTH('') DB Oracle LENGTH LENGTHB DB UDB LENGTH 9

113 . LENGTHB() SQL> SELECT LENGTHB('ABC'),LENGTHB('') FROM DUAL; LENGTHB('ABC') LENGTHB('') DB LENGTH() db=> SELECT LENGTH('ABC'),LENGTH('') FROM SYSIBM.SYSDUMMY Oracle LENGTH LENGTHB DB UDB LENGTH 9

114 . LTRIM(, ) SQL> SELECT LTRIM('ERASER','ER') FROM DUAL; LTRI ---- ASER DB UDF WITH Recurse (seq, str) AS ( VALUES (0, 'ERASER') UNION ALL SELECT new_seq, SUBSTR(str,) FROM (SELECT seq+ AS new_seq, str, LOCATE(SUBSTR(str,,),'ER') AS exist FROM Recurse WHERE seq < 0000 ) Q WHERE exist > 0 ) SELECT str FROM Recurse WHERE seq = (SELECT MAX(seq) FROM Recurse); STR ASER DB UDB LTRIM Oracle DB UDB LTRIM MTK0 ora8.ltrim UDF UDF Commands Entered CREATE FUNCTION MyOra.LTRIM(str VARCHAR(000), srch VARCHAR(000)) RETURNS VARCHAR(000) CONTAINS SQL 98

115 BEGIN ATOMIC MAIN_LOOP: FOR ltrim_loop AS SELECT SEQ + AS pos FROM (SELECT N*000+N*00+N*0+N AS SEQ FROM (VALUES 0,,,,,,,,8,9) P(N), (VALUES 0,,,,,,,,8,9) P(N), (VALUES 0,,,,,,,,8,9) P(N), (VALUES 0,,,) P(N) ) Q WHERE SEQ < LENGTH(str) ORDER BY SEQ DO IF LOCATE(SUBSTR(str,pos,), srch) = 0 THEN RETURN SUBSTR(str,pos); END IF; END FOR; END@ DB0000I The SQL command completed successfully. UDF Commands Entered SELECT SUBSTR(MyOra.LTRIM(' xyxxxylast WORD',' xy'),,0) AS "LTRIM example" FROM SYSIBM.SYSDUMMY; LTRIM example XxyLAST WORD record(s) selected. 99

116 .8 LOWER() SQL> SELECT LOWER('ABC') FROM DUAL; LOW --- abc DB LOWER() LCASE() db=> SELECT LOWER('ABC') FROM SYSIBM.SYSDUMMY --- abc db=> SELECT LCASE('ABC') FROM SYSIBM.SYSDUMMY --- abc 00

117 .9 MXA() SQL> SELECT MAX(SAL) FROM EMP; MAX(SAL) DB MAX() db=> SELECT MAX(SAL) FROM EMP MAX NULL 0

118 .0 MIN() SQL> SELECT MIN(SAL) FROM EMP; MIN(SAL) DB MIN() db=> SELECT MIN(SAL) FROM EMP MIN NULL 0

119 . m n n 0 m MOD(m,n) SQL> SELECT MOD(,) FROM DUAL; MOD(,) DB MOD(m,n) db=> SELECT MOD(,) FROM SYSIBM.SYSDUMMY DB UDB n 0 0

120 . m n m n m n POWER(m,n) SQL> SELECT POWER(,) FROM DUAL; POWER(,) DB POWER(m,n) db=> SELECT POWER(,) FROM SYSIBM.SYSDUMMY

121 . NULL NULL REPLACE(,,) SQL> SELECT REPLACE('()','()','') FROM DUAL; DB REPLACE('() REPLACE(,,) db=> SELECT REPLACE('()','()','') FROM SYSIBM.SYSDUMY REPLACE TRANSLATE 0

122 . n SIGN(n) NUMBER n<0- n=0 0 n>0 SQL> SELECT SIGN(-0),SIGN(0) FROM DUAL; SIGN(-0) SIGN(0) DB SIGN(n) db=> SELECT SIGN(-0),SIGN(0) FROM SYSIBM.SYSDUMMY

123 . DB RTRIM (, ) () SQL> SELECT RTRIM('ERASER','ER') FROM DUAL; RTRI ---- ERAS UDF db=> WITH Recurse (seq, str) AS ( VALUES (0, 'ERASER') UNION ALL SELECT new_seq, SUBSTR(str,,LENGTH(str)-) FROM (SELECT seq+ AS new_seq, str, LOCATE(SUBSTR(str,LENGTH(str),),'ER') AS exist FROM Recurse WHERE seq < 0000 ) Q WHERE exist > 0 ) SELECT str FROM Recurse WHERE seq = (SELECT MAX(seq) FROM Recurse); STR ASER DB UDB RTRIM Oracle DB UDB RTRIM MTK0 ora8.rtrim UDF UDF Commands Entered CREATE FUNCTION MyOra.RTRIM(str VARCHAR(000), srch VARCHAR(000)) RETURNS VARCHAR(000) CONTAINS SQL BEGIN ATOMIC MAIN_LOOP: FOR rtrim_loop AS 0

124 SELECT SEQ + AS pos FROM (SELECT N*000+N*00+N*0+N AS SEQ FROM (VALUES 0,,,,,,,,8,9) P(N), (VALUES 0,,,,,,,,8,9) P(N), (VALUES 0,,,,,,,,8,9) P(N), (VALUES 0,,,) P(N) ) Q WHERE SEQ < LENGTH(str) ORDER BY SEQ DESC DO IF LOCATE(SUBSTR(str,pos,), srch) = 0 THEN RETURN SUBSTR(str,,pos); END IF; END FOR; END@ DB0000I The SQL command completed successfully. UDF Commands Entered SELECT SUBSTR(MyOra.RTRIM('ERASER','ER'),,0) AS "RTRIM Sample" FROM SYSIBM.SYSDUMMY; RTRIM Sample ERAS record(s) selected. 08

125 . SOUNDEX() SQL> SELECT ENAME FROM EMP WHERE SOUNDEX(ENAME) = SOUNDEX('ALLAN'); ENAME ALLEN SOUNDEX() SELECT ENAME FROM EMP WHERE SOUNDEX(ENAME) = SOUNDEX('ALLAN'); ENAME ALLEN DB 09

126 . SUBSTR(,,) SQL> SELECT SUBSTR('CORPORATE SECTOR',,),SUBSTR('',,) FROM DUAL; SUBS SUBSTR(' RPOR DB SUBSTR(,,) db=> SELECT SUBSTR('CORPORATE SECTOR',,),SUBSTR('',,8) FROM SYSIBM.SYSDUMMY RPOR SUBSTR Oracle DB UDB SUBSTRB SUBSTR 0

127 .8 SUBSTRB(,,) DB SQL> SELECT SUBSTRB('CORPORATE SECTOR',,),SUBSTRB('',,8) FROM DUAL; SUBS SUBSTRB( RPOR SUBSTR(,,) db=> SELECT SUBSTR('CORPORATE SECTOR',,),SUBSTR('',,8) FROM SYSIBM.SYSDUMMY RPOR SUBSTR Oracle DB UDB SUBSTRB SUBSTR

128 .9 SUM() SQL> SELECT SUM(SAL) FROM EMP; SUM(SAL) DB SUM() db=> SELECT SUM(SAL) FROM EMP SUM NULL

129 .0 TRANSLATE(,,) SQL> SELECT TRANSLATE('A.B C/D','. /','-,_') FROM DUAL; DB TRANSLA A-B,C_D TRANSLATE(,,) db=> SELECT TRANSLATE('A.B C/D','-,_','. /') FROM SYSIBM.SYSDUMMY A-B,C_D REPLACE TRANSLATE TRANSLATE

130 . UPPER() SQL> SELECT UPPER('abc') FROM DUAL; UPP --- ABC DB UPPER() UCASE() db=> SELECT UPPER('abc') FROM SYSIBM.SYSDUMMY --- ABC db=> SELECT UCASE('abc') FROM SYSIBM.SYSDUMMY --- ABC

131 . USER SQL> SELECT USER FROM DUAL; USER USER USER db=> SELECT USER FROM SYSIBM.SYSDUMMY USER DB

132 . n ACOS(n) SQL> SELECT ACOS(.) FROM DUAL; ACOS(.) DB ACOS(n) db=> SELECT ACOS(.) FROM SYSIBM.SYSDUMMY E+000

133 . n ASIN(n) SQL> SELECT ATAN(.) FROM DUAL; ATAN(.) ASIN(n) db=> SELECT ATAN(.) FROM SYSIBM.SYSDUMMY E-00 DB

134 . n ATAN(n) SQL> SELECT ATAN(.) FROM DUAL; ATAN(.) DB ATAN(n) db=> SELECT ATAN(.) FROM SYSIBM.SYSDUMMY E-00 8

135 . n n n m -ATAN(n, m)atan(n/m) ATAN(n,m) SQL> SELECT ATAN(.,.) FROM DUAL; ATAN(.,.) ATAN(m,n) SELECT ATAN(.,.) FROM SYSIBM.SYSDUMMY; E-00 DB UDB Oracle DB 9

136 . n COS(n) SQL> SELECT COS(80 *.9/80) FROM DUAL; COS(80*.9/80) DB COS(n) db=> SELECT COS(80 *.9/80) FROM SYSIBM.SYSDUMMY E-00 0

137 .8 n COSH(n) SQL> SELECT COSH(0) FROM DUAL; COSH(0) COSH(n) db=> SELECT COSH(0) FROM SYSIBM.SYSDUMMY E+000 DB

138 .9 n SIN(n) SQL> SELECT SIN(0 *.9/80) FROM DUAL; SIN(0*.9/80) DB SIN(n) db=> SELECT SIN(0 *.9/80) FROM SYSIBM.SYSDUMMY E-00

139 .0 n SINH(n) SQL> SELECT SINH() FROM DUAL; SINH() SINH(n) db=> SELECT SINH() FROM SYSIBM.SYSDUMMY E+000 DB

140 . n TAN(n) SQL> SELECT TAN(0 *.9/80) FROM DUAL; TAN(0*.9/80) DB TAN(n) db=> SELECT TAN(0 *.9/80) FROM SYSIBM.SYSDUMMY E-00

141 . n TANH(n) SQL> SELECT TANH(.) FROM DUAL; TANH(.) TANH(n) db=> SELECT TANH(.) FROM SYSIBM.SYSDUMMY E-00 DB

142 . n CEIL(n) SQL> SELECT CEIL(.) FROM DUAL; CEIL(.) DB CEIL(n) db=> SELECT CEIL(.) FROM SYSIBM.SYSDUMMY

143 . n FLOOR(n) SQL> SELECT FLOOR(.) FROM DUAL; FLOOR(.) FLOOR(n) db=> SELECT FLOOR(.) FROM SYSIBM.SYSDUMMY DB

144 . n n n n ROUND(,n) SQL> SELECT ROUND(.,) FROM DUAL; ROUND(.,) DB ROUND(,n) db=> SELECT ROUND(.,) FROM SYSIBM.SYSDUMMY ROUND Oracle 0DB UDB Oracle DB UDB 8

145 . fmt fmt 'DD' 'MM' 'YY' ROUND(,fmt) SQL> SELECT TO_CHAR(SYSDATE,'YY-MM-DD HH:MI:SS'),ROUND(SYSDATE,'DD') FROM DUAL; TO_CHAR(SYSDATE,' ROUND(SY :: 0-0- DB UDF SELECT timestamp, DATE(timestamp + HOURS) AS ROUND_DD FROM TABLE(VALUES TIMESTAMP(' ')) Q(timestamp); TIMESTAMP ROUND_DD record(s) selected. Oracle ROUND DB UDB MTK0 ora8.round(date[,format]) UDF 9

146 . fmt ROUND(,'MM') SQL> SELECT TO_CHAR(SYSDATE,'YY-MM-DD HH:MI:SS'),ROUND(SYSDATE,'MM') FROM DUAL; TO_CHAR(SYSDATE,' ROUND(SY :: DB UDF SELECT date, date - DAYS - (DAY(date - DAYS) - ) DAYS + MONTH AS ROUND_MM FROM TABLE(VALUES DATE('00-0-0'), DATE('00-0-'), DATE('00-0-'), DATE('00-0-8'), DATE('00-0-'), DATE('00-0-'), DATE('00-0-'), DATE('00--'), DATE('00--'), DATE('00--'), DATE('00-0-'), DATE('00-0-'), DATE('00-0-0') ) Q (date); DATE ROUND_MM record(s) selected. 0

147 Oracle ROUND DB UDB MTK0 ora8.round(date[,format]) UDF

148 .8 fmt ROUND(,'YY') SQL> SELECT TO_CHAR(SYSDATE,'YY-MM-DD HH:MI:SS'),ROUND(SYSDATE,'YY') FROM DUAL; TO_CHAR(SYSDATE,' ROUND(SY :: DB UDF SELECT date, date + MONTHS - (DAYOFYEAR(date + MONTHS) -) DAYS AS ROUND_YY FROM TABLE(VALUES DATE('00-0-0'), DATE('00-0-8'), DATE('00-0-'), DATE('00-0-0'), DATE('00-0-'), DATE('00-0-0'), DATE('00--'), DATE('00--') ) Q (date); DATE ROUND_YY record(s) selected. Oracle ROUND DB UDB MTK0 ora8.round(date[,format]) UDF

149 .9 m m m m 0 DB TRUNC(,m) SQL> SELECT TRUNC(.,) FROM DUAL; TRUNC(.,) TRUNC(,m) db=> SELECT TRUNC(.,) FROM SYSIBM.SYSDUMMY TRUNC Oracle 0DB UDB Oracle DB UDB

150 .0 fmt fmt 'DD' 'MM' 'YY' TRUNC(,fmt) SQL> SELECT TO_CHAR(SYSDATE,'YY-MM-DD HH:MI:SS'),TRUNC(SYSDATE,'DD') FROM DUAL; TO_CHAR(SYSDATE,' TRUNC(SY :: DB UDF SELECT CURRENT_TIMESTAMP AS CURRENT_TIMESTAMP, TIMESTAMP(DATE(CURRENT_TIMESTAMP),' ') AS TRUNC_DD FROM SYSIBM.SYSDUMMY; CURRENT_TIMESTAMP TRUNC_DD record(s) selected. Oracle TRUNC DB UDB MTK0 ora8.trunc(date) UDF

151 . fmt TRUNC(,'MM') DB SQL> SELECT TO_CHAR(SYSDATE,'YY-MM-DD HH:MI:SS'),TRUNC(SYSDATE,'MM') FROM DUAL; TO_CHAR(SYSDATE,' TRUNC(SY :: UDF SELECT date, date - (DAY(date)-) DAYS AS TRUNC_MM FROM TABLE(VALUES DATE('00-0-0'), DATE('00-0-'), DATE('00-0-'), DATE('00-0-8'), DATE('00-0-'), DATE('00-0-'), DATE('00-0-') ) Q (date); DATE TRUNC_MM record(s) selected. Oracle TRUNC DB UDB MTK0 ora8.trunc(date) UDF

152 . fmt TRUNC(,'YY') SQL> SELECT TO_CHAR(SYSDATE,'YY-MM-DD HH:MI:SS'),TRUNC(SYSDATE,'YY') FROM DUAL; TO_CHAR(SYSDATE,' TRUNC(SY :: DB UDF SELECT date, date - (DAYOFYEAR(date)-) DAYS AS TRUNC_YY FROM TABLE(VALUES DATE('00-0-0'), DATE('00-0-8'), DATE('00-0-'), DATE('00-0-0'), DATE('00-0-'), DATE('00-0-0'), DATE('00--'), DATE('00--') ) Q (date); DATE TRUNC_YY record(s) selected. Oracle TRUNC DB UDB MTK0 ora8.trunc(date) UDF

153 . EXP e n e.888 EXP(n) SQL> SELECT EXP() FROM DUAL; EXP() DB EXP(n) db=> SELECT EXP() FROM SYSIBM.SYSDUMMY E+000

154 . n n LN(n) SQL> SELECT LN(90) FROM DUAL; LN(90) DB LN(n) db=> SELECT LN(90) FROM SYSIBM.SYSDUMMY E+000 8

155 . TIMESTAMP LOCALTIMESTAMP SQL> SELECT LOCALTIMESTAMP FROM DUAL; LOCALTIMESTAMP ::.000 CURRENT_TIMESTAMP CURRENT TIMESTAMP db=> SELECT CURRENT_TIMESTAMP FROM SYSIBM.SYSDUMMY DB 9

156 . n SQRT(n) SQL> SELECT SQRT() FROM DUAL; SQRT() DB SQRT(n) SELECT SQRT() FROM SYSIBM.SYSDUMMY; E+000 0

157 . AVG() SQL> SELECT AVG(SAL) FROM EMP; AVG(SAL) AVG() db=> SELECT AVG(SAL) FROM EMP AVG NULL DB

158 .8 INSTR INSTR(,,,) SQL> SELECT INSTR('CORPORATE SECTOR','OR',,),INSTR('','',,) FROM DUAL; INSTR('CORPORATESECTOR','OR',,) INSTR('','',,) DB UDF DB UDB LOCATE Oracle INSTR Oracle DB UDB MTK0 ora8.instr UDF

159 .9 INSTRB INSTRB(,,,) SQL> SELECT INSTRB('CORPORATE SECTOR','OR',,),INSTRB('','',,) FROM DUAL; INSTRB('CORPORATESECTOR','OR',,) INSTRB('','',,) UDF DB UDB LOCATE Oracle INSTR INSTRB Sample UDFs for Migration0 INSTRB UDF DB

160 .0 INSTRB INSTRB(,,,) SQL> SELECT INSTRB('CORPORATE SECTOR','OR',,),INSTRB('','',,) FROM DUAL; INSTRB('CORPORATESECTOR','OR',,) INSTRB('','',,) DB LOCATE(,,) db=> SELECT LOCATE('OR','CORPORATE SECTOR',),LOCATE('','', ) FROM SYSIBM.SYSDUMMY DB UDB LOCATE Oracle INSTR INSTRB DB UDB LOCATE Oracle INSTR DB UDB LOCATE DB UDB LOCATE Oracle INSTRB

161 . 0 DB LOG(, ) SQL> SELECT LOG(0,00) FROM DUAL; LOG(0,00) LOG( )/LOG( ) 0 LOG0() e LOG() LN() SELECT LOG(00)/LOG(0) FROM SYSIBM.SYSDUMMY; E SELECT LOG0(00) FROM SYSIBM.SYSDUMMY; E+000 LOG Oracle DB UDB LOG elog0 0 DB UDB LOG DB UDB LOG

162 . raw raw RAW RAWTOHEX(raw) DB hex db=> db select hex('aaa') from table(values()) as x

163 . NULL NULL NVL NULL NVL NVL(, ) SQL> SELECT COMM,SAL,NVL(COMM,0),NVL(SAL,COMM) FROM EMP WHERE EMPNO = 9; COMM SAL NVL(COMM,0) NVL(SAL,COMM) COALESCE(, ) db=> SELECT COMM,SAL,COALESCE(COMM,0),COALESCE(SAL,COMM) FROM EMP WHERE EMPNO = 9 COMM SAL DB

164 . DATE SYSDATE SQL> SELECT SYSDATE FROM DUAL; SYSDATE SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YY-MM-DD HH:MI:SS'; SQL> SELECT SYSDATE FROM DUAL; SYSDATE :: DB CURRENT_DATE CURRENT DATE CURRENT_TIMESTAMP CURRENT TIMESTAMP db=> SELECT CURRENT_DATE FROM SYSIBM.SYSDUMMY db=> SELECT CURRENT_DATE,CURRENT_TIME FROM SYSIBM.SYSDUMMY ::0 Oracle DATE SYSDATE 'YY-MMDD' DB UDB CUURENT_ DATE CURRENT_TIME CURRENT_TIMESTAMP Oracle CURRENT _DATE CURRENT_TIME 8

165 . TO_MULTI_BYTE() SQL> SELECT TO_MULTI_BYTE('ABC') FROM DUAL; TO_MUL ABC VARGRAPHIC() db=> SELECT VARGRAPHIC('ABC') FROM SYSIBM.SYSDUMMY ABC DB 9

166 . NULL NULL VSIZE() SQL> SELECT VSIZE('ABC'),VSIZE('') FROM DUAL; VSIZE('ABC') VSIZE('') DB LENGTH() db=> SELECT LENGTH('ABC'),LENGTH('') FROM SYSIBM.SYSDUMMY

167 . VARIANCE() SQL> SELECT VARIANCE(SAL) FROM EMP; VARIANCE(SAL) VARIANCE() * COUNT(*) / (COUNT(*) - ) db=> SELECT VARIANCE(SAL)*COUNT(*)/(COUNT(*)-) FROM EMP E+00 Oracle DB UDB DB UDB Oracle * COUNT(*) / (COUNT(*) - ) DB

168 .8 0 BIN_TO_NUM(,, n) SQL> SELECT BIN_TO_NUM(,0),BIN_TO_NUM(,0,0) FROM DUAL; BIN_TO_NUM(,0) BIN_TO_NUM(,0,0) DB Oracle BIN_TO_NUM NUMBER 0 Oracle NUMBER BIN_TO_NUM

169 .9 AND BITAND(, ) UDF Sample UDFs for Migration0 BITAND UDF DB

170 .80 CAST( AS ) SQL> SELECT CAST(SYSDATE AS TIMESTAMP WITH LOCAL TIME ZONE) FROM DUAL; CAST(SYSDATEASTIMESTAMPWITHLOCALTIMEZONE) :8: DB CAST( AS ) db=> SELECT CAST(CURRENT_TIMESTAMP AS TIMESTAMP) FROM SYSIBM.SYSDUMMY

171 .8 INITCAP() SQL> SELECT INITCAP('RELATIONAL DATABASE-SYSTEM') FROM DUAL; INITCAP('RELATIONALDATABAS Relational Database-System UDF Sample UDFs for Migration0 INITCAP UDF DB

172 .8 LPAD(,, ) SQL> SELECT LPAD(SAL,,'-') FROM EMP WHERE DEPTNO = 0; LPAD(SAL,,'-' DB UDF Sample UDFs for Migration0 LPAD UDF

173 .8 RPAD(,, ) SQL> SELECT RPAD(SAL,,'-') FROM EMP WHERE DEPTNO = 0; RPAD(SAL,,'-' UDF Sample UDFs for Migration0 RPAD UDF DB

174 .8 NLS_INITCAP(,'NLS ') SQL> SELECT NLS_INITCAP('ijsland','NLS_SORT = XDutch') FROM DUAL; NLS_INI Ijsland DB 8

175 .8 NLS_UPPER() SQL> SELECT NLS_UPPER('abc') FROM DUAL; NLS_UP ABC DB 9

176 .8 NLS_LOWER() SQL> SELECT NLS_LOWER('ABC') FROM DUAL; NLS_LO abc DB 0

177 .8 NTILE() SQL> SELECT ENAME,SAL,NTILE() OVER(ORDER BY SAL) FROM EMP WHERE DEPTNO = 0; DB ENAME SAL NTILE()OVER(ORDERBYSAL) JAMES 90 WARD 0 MARTIN 0 TURNER 00 ALLEN 00 BLAKE SELECT ENAME, SAL, CASE WHEN rn <= r*(d+) THEN (rn -)/(d+) + ELSE (rn-r-)/d + END NTILE FROM (SELECT EMP.*, ROW_NUMBER() OVER(ORDER BY SAL) AS rn, COUNT(*) OVER() / AS d, MOD(COUNT(*) OVER(), ) AS r FROM EMP WHERE DEPTNO = 0) AS S; ENAME SAL NTILE JAMES WARD 0.00 MARTIN 0.00 TURNER ALLEN BLAKE 80.00

178 .88 CHARVARCHARNCHARNVARCHAR ROWID CHARTOROWID() SQL> SELECT EMPNO,ENAME FROM EMP WHERE ROWID = CHARTOROWID('AAAMZYAAEAAAAF kaaa'); EMPNO ENAME SMITH DB DB UDB ROWID CHARTROWID

179 .89 CONVERT(,,) SQL> SELECT CONVERT('ABCDE','UTF8','USASCII') FROM DUAL; CONVE ABCDE DB

180 .90 ROWID VARCHAR ROWIDTOCHAR(ROWID) SQL> SELECT ROWIDTOCHAR(ROWID) FROM EMP WHERE EMPNO = 9; ROWIDTOCHAR(ROWID) AAAMZYAAEAAAAFkAAA DB DB UDB ROWID ROWID

181 .9 DATE TO_DATE(,) SQL> SELECT TO_DATE('0000','YYYYMMDD') FROM DUAL; TO_DATE( 'YYYY-MM-DD HH:MI:SS' db=> SELECT DATE(INSERT(INSERT('0000',,0,'-'),8,0,'-')) FROM SYSIBM. SYSDUMMY DB UDB CAST TO_DATE (, ) DB

182 .9 NUMBER TO_NUMBER(,) SQL> SELECT TO_NUMBER(',','99,999') FROM DUAL; TO_NUMBER(',','99,999') DB DB UDB CAST TO_DATE (, )

183 .9 TO_SINGLE_BYTE() SQL> SELECT TO_SINGLE_BYTE('ABC') FROM DUAL; TO_ --- ABC DB

184 .9 n ADD_MONTH(DATE,n) SQL> SELECT SYSDATE,ADD_MONTHS(SYSDATE,) FROM DUAL; SYSDATE ADD_MONT DB UDF Sample UDFs for Migration0 ADD_MONTH UDF 8

185 .9 LAST_DAY() SQL> SELECT SYSDATE,LAST_DAY(SYSDATE) FROM DUAL; SYSDATE LAST_DAY UDF Sample UDFs for Migration0 LAST_DAY UDF DB 9

186 .9 LEAST() SQL> SELECT LEAST(00,0,0) FROM DUAL; LEAST(00,0,0) DB UDF Sample UDFs for Migration0 LEAST UDF 0

187 .9 Oracle MONTHS_BETWEEN(, ) SQL> SELECT MONTHS_BETWEEN('0-0-0','0-0-0') FROM DUAL; MONTHS_BETWEEN('0-0-0','0-0-0') UDF DB

188 .98 weekday date NEXT_DAY(date,weekday) SQL> SELECT SYSDATE,NEXT_DAY(SYSDATE,) FROM DUAL; SYSDATE NEXT_DAY DB UDF Sample UDFs for Migration0 NEXT_DAY UDF

189 .99 zone date zone NLS_DATE_FORMAT NEW_TIME(date,zone,zone) SQL> SELECT NEW_TIME(TO_DATE('0-0- ::','YY-MM-DD HH:MI:SS'), 'AST','GMT') FROM DUAL; NEW_TIME(TO_DATE( :: UDF Sample UDFs for Migration0 NEW_TIME UDF DB

190 .00 GREATEST() SQL> SELECT GREATEST(00,0,0) FROM DUAL; GREATEST(00,0,0) DB UDF Sample UDFs for Migration0 GREATEST UDF

191 .0 NULLIF NULL DB NULLIF(, ) SQL> SELECT E.ENAME,E.JOB "Current Job",NULLIF(J.JOB,E.JOB) "Old Job" FROM EMP E,JOB_HISTORY J WHERE E.EMPNO = J.EMPNO; ENAME Current J Old Job SMITH CLERK ANALYST ALLEN SALESMAN CLERK JONES MANAGER NULLIF(, ) db=> db => SELECT E.ENAME,E.JOB "Current Job",NULLIF(J.JOB,E.JOB) "Old Job" FROM EMP E,JOB_HISTORY J WHERE E.EMPNO = J.EMPNO ENAME Current Job Old Job SMITH CLERK ANALYST ALLEN SALESMAN CLERK JONES MANAGER -

192 .0 n NUMTODSINTERVAL(n [,'DAY' 'HOUR' 'MINUTE' 'SECOND']) SQL> SELECT NUMTODSINTERVAL(0,'HOUR') FROM DUAL; NUMTODSINTERVAL(0,'HOUR') :00: DB UDF SELECT DIGITS(DAYS(TIMESTAMP(' ') + 0 HOURS) -) ' ' SUBSTR(CHAR(TIMESTAMP(' ') + 0 HOURS),,) FROM SYSIBM.SYSDUMMY;

193 .0 n NUMTOYMINTERVAL(n [,'YEAR' 'MONTH']) SQL> SELECT NUMTOYMINTERVAL(0,'MONTH') FROM DUAL; NUMTOYMINTERVAL(0,'MONTH') SELECT DIGITS(0/) '-' SUBSTR(DIGITS(MOD(0,)),9,) FROM SYSIBM.SYSDUMMY; DB

194 .0 NULL NULL NVL NULL NVL NVL(,, ) SQL> SELECT COMM,SAL,NVL(COMM,SAL+COMM,SAL) FROM EMP WHERE DEPTNO = 0; COMM SAL NVL(COMM,SAL+COMM,SAL) DB CASE SELECT COMM, SAL, CASE WHEN COMM IS NOT NULL THEN SAL+COMM ELSE SAL END FROM EMP WHERE DEPTNO = 0; COMM SAL

195 .0 UID SQL> SELECT UID FROM DUAL; UID DB 9

196 .0 USERENV SQL> SELECT USERENV('LANGUAGE') FROM DUAL; USERENV('LANGUAGE') JAPANESE_JAPAN.JASJIS DB 80

197 .0 MEDIAN NULL MEDIAN() SQL> SELECT MEDIAN(SAL),MIN(SAL),MAX(SAL) FROM EMP WHERE DEPTNO = 0; DB MEDIAN(SAL) MIN(SAL) MAX(SAL) SELECT MAX(fsal) + (MAX(csal) - MAX(fsal)) / AS MEDIAN, MIN(sal) AS MIN_SAL MAX(sal) AS MAX_SAL FROM (SELECT sal, CASE rn WHEN (cnt + MOD(cnt,)) / THEN sal END AS fsal, CASE rn WHEN (cnt - MOD(cnt,)) / + THEN sal END AS csal FROM (SELECT sal, COUNT(sal) OVER() cnt, ROWNUMBER() OVER(ORDER BY sal) rn FROM EMP WHERE DEPTNO = 0 ) Q ) P; MEDIAN MIN_SAL MAX_SAL DB UDB MAX(fsal)+(MAX(csal) - MAX(fsal)) / (MAX(fsal)+MAX(csal)) / DEC(8,0) CAST MAX(fhiredate) + CAST((MAX(chiredate) - MAX(fhiredate)) / AS DEC(8,0)) AS MEDIAN 8

198

199

200 . ACCOUNTING SELECT FROM WHERE = (SELECT FROM WHERE ) SQL> SELECT DEPTNO,ENAME FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'ACCOUNTING'); DEPTNO ENAME CLARK 0 KING 0 MILLER DB SELECT FROM WHERE = (SELECT FROM WHERE ) SELECT. FROM, WHERE. =. AND ) SELECT DEPTNO,ENAME FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'ACCOUNTING'); DEPTNO ENAME CLARK 0 KING 0 MILLE SELECT E.DEPTNO, ENAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND D.DNAME = 'ACCOUNTING'; DEPTNO ENAME CLARK 0 KING 0 MILLE 8

201 SELECT WHERE 8

202 . SELECT FROM WHERE = (SELECT FROM WHERE ) SQL> SELECT DEPTNO,DNAME FROM DEPT WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE JOB = 'PRESIDENT'); DEPTNO DNAME ACCOUNTING DB SELECT FROM WHERE = (SELECT FROM WHERE ) SELECT. FROM, WHERE. = AND ) db=> SELECT DEPTNO,DNAME FROM DEPT WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE JOB = 'PRESIDENT') DEPTNO DNAME ACCOUNTING =<>><>=<= DB UDB 8

203 . 0 SELECT FROM WHERE IN (SELECT FROM WHERE ) SQL> SELECT JOB,ENAME FROM EMP WHERE JOB IN (SELECT JOB FROM EMP WHERE DEP TNO = 0); JOB ENAME SALESMAN TURNER SALESMAN MARTIN SALESMAN WARD SALESMAN ALLEN MANAGER CLARK MANAGER BLAKE MANAGER JONES CLERK MARY CLERK MILLER CLERK JAMES CLERK ADAMS CLERK SMITH DB SELECT FROM WHERE IN (SELECT FROM WHERE ) db=> SELECT JOB,ENAME FROM EMP WHERE JOB IN (SELECT JOB FROM EMP WHERE DE PTNO = 0) JOB ENAME CLERK SMITH SALESMAN ALLEN SALESMAN WARD MANAGER JONES SALESMAN MARTIN MANAGER BLAKE MANAGER CLARK SALESMAN TURNER CLERK ADAMS CLERK JAMES CLERK MILLER CLERK MARY 8

204 =<>><>=<= INNOT IN ANYALL IN EXISTS SELECT JOB, ENAME FROM EMP A WHERE A.JOB IN (SELECT B.JOB FROM EMP.B WHERE B.DEPTNO = 0 ) ; EXISTS SELECT JOB, ENAME FROM EMP A WHERE EXISTS (SELECT * FROM EMP.B WHERE A.JOB = B.JOB AND B.DEPTNO = 0 ) ; 88

205 . SELECT FROM WHERE =ANY (SELECT FROM WHERE ) SQL> SELECT DEPTNO,ENAME,HIREDATE,SAL FROM EMP WHERE SAL =ANY (SELECT SAL FROM EMP WHERE TO_CHAR(HIREDATE,'MM') = '0'); DEPTNO ENAME HIREDATE SAL ALLEN MARTIN WARD DB SELECT FROM WHERE =ANY (SELECT FROM WHERE ) db=> SELECT DEPTNO,ENAME,HIREDATE,SAL FROM EMP WHERE SAL =ANY (SELECT SAL FROM EMP WHERE MONTH(HIREDATE) = ) DEPTNO ENAME HIREDATE SAL ALLEN WARD MARTIN IN =ANY 89

206 . SELECT FROM WHERE =ALL (SELECT FROM WHERE ) SQL> SELECT DEPTNO,ENAME,HIREDATE,SAL FROM EMP WHERE SAL =ALL (SELECT SAL FROM EMP WHERE TO_CHAR(HIREDATE,'MM') = '0'); DB SELECT FROM WHERE =ALL (SELECT FROM WHERE ) db=> SELECT DEPTNO,ENAME,HIREDATE,SAL FROM EMP WHERE SAL =ALL (SELECT SAL FROM EMP WHERE MONTH(HIREDATE) = ) DEPTNO ENAME HIREDATE SAL =ALL 90

207 . DB SELECT FROM WHERE >ANY (SELECT FROM WHERE ) SQL> SELECT DEPTNO,ENAME,HIREDATE,SAL FROM EMP WHERE SAL >=ANY (SELECT SAL FROM EMP WHERE TO_CHAR(HIREDATE,'MM') = '0'); DEPTNO ENAME HIREDATE SAL KING SCOTT FORD JONES BLAKE CLARK ALLEN TURNER MILLER MARY WARD MARTIN SELECT FROM WHERE >ANY (SELECT FROM WHERE ) db=> SELECT DEPTNO,ENAME,HIREDATE,SAL FROM EMP WHERE SAL >=ANY (SELECT SAL FROM EMP WHERE MONTH(HIREDATE) = ) DEPTNO ENAME HIREDATE SAL ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER FORD MILLER MARY

208 >ANY Oracle SQL SQL SELECT DEPTNO,ENAME,HIREDATE,SAL FROM EMP WHERE SAL >= (SELECT MIN(SAL) FROM EMP WHERE TO_CHAR(HIREDATE,'MM') = '0'); 9

209 . DB SELECT FROM WHERE >=ALL (SELECT FROM WHERE ) SQL> SELECT DEPTNO,ENAME,HIREDATE,SAL FROM EMP WHERE SAL >=ALL (SELECT SAL FROM EMP WHERE TO_CHAR(HIREDATE,'MM') = '0'); DEPTNO ENAME HIREDATE SAL ALLEN JONES BLAKE CLARK SCOTT KING FORD SELECT FROM WHERE >=ALL (SELECT FROM WHERE ) db=> SELECT DEPTNO,ENAME,HIREDATE,SAL FROM EMP WHERE SAL >=ALL (SELECT SAL FROM EMP WHERE MONTH(HIREDATE) = ) DEPTNO ENAME HIREDATE SAL ALLEN JONES BLAKE CLARK SCOTT KING FORD >ALL Oracle SQL SQL SELECT DEPTNO,ENAME,HIREDATE,SAL FROM EMP WHERE SAL >= (SELECT MAX(SAL) FROM EMP WHERE TO_CHAR(HIREDATE,'MM') = '0'); 9

210 .8 SELECT FROM WHERE <=ALL (SELECT FROM WHERE ) SQL> SELECT DEPTNO,ENAME,HIREDATE,SAL FROM EMP WHERE SAL <=ALL (SELECT SAL FROM EMP WHERE TO_CHAR(HIREDATE,'MM') = '0'); DEPTNO ENAME HIREDATE SAL SMITH WARD MARTIN ADAMS JAMES DB SELECT FROM WHERE <=ALL (SELECT FROM WHERE ) db=> SELECT DEPTNO,ENAME,HIREDATE,SAL FROM EMP WHERE SAL <=ALL (SELECT SAL FROM EMP WHERE MONTH(HIREDATE) = ) DEPTNO ENAME HIREDATE SAL SMITH WARD MARTIN ADAMS JAMES <ALL Oracle SQL SQL SELECT DEPTNO,ENAME,HIREDATE,SAL FROM EMP WHERE SAL <= (SELECT MIX(SAL) FROM EMP WHERE TO_CHAR(HIREDATE,'MM') = '0'); 9

211 .9 DB SELECT FROM WHERE <=ANY (SELECT FROM WHERE ) SQL> SELECT DEPTNO,ENAME,HIREDATE,SAL FROM EMP WHERE SAL <=ANY (SELECT SAL FROM EMP WHERE TO_CHAR(HIREDATE,'MM') = '0'); DEPTNO ENAME HIREDATE SAL SMITH JAMES ADAMS WARD MARTIN MILLER MARY TURNER ALLEN SELECT FROM WHERE <=ANY (SELECT FROM WHERE ) db=> SELECT DEPTNO,ENAME,HIREDATE,SAL FROM EMP WHERE SAL <=ANY (SELECT SAL FROM EMP WHERE MONTH(HIREDATE) = ) DEPTNO ENAME HIREDATE SAL SMITH ALLEN WARD MARTIN TURNER ADAMS JAMES MILLER MARY <ANY SQL SQL 9

212 SELECT DEPTNO,ENAME,HIREDATE,SAL FROM EMP WHERE SAL <= (SELECT MAX(SAL) FROM EMP WHERE TO_CHAR(HIREDATE,'MM') = '0'); DB UDB SQL SQL 9

213 .0 DB SELECT FROM WHERE EXISTS (SELECT FROM WHERE. =.) SQL> SELECT * FROM DEPT WHERE EXISTS (SELECT * FROM EMP WHERE EMP.DEPTNO = DEPT.DEPTNO); DEPTNO DNAME LOC RESEARCH DALLAS 0 SALES CHICAGO 0 ACCOUNTING NEW YORK SELECT FROM WHERE EXISTS (SELECT FROM WHERE. =.) db=> SELECT * FROM DEPT WHERE EXISTS (SELECT * FROM EMP WHERE EMP.DEPTNO = DEPT.DEPTNO) DEPTNO DNAME LOC ACCOUNTING NEW YORK 0. RESEARCH DALLAS 0. SALES CHICAGO EXISTS TRUE IN 9

214 . SELECT FROM WHERE NOT EXISTS (SELECT FROM WHERE. =.) SQL> SELECT * FROM DEPT WHERE NOT EXISTS (SELECT * FROM EMP WHERE EMP.DEPT NO = DEPT.DEPTNO); DEPTNO DNAME LOC OPERATIONS BOSTON DB SELECT FROM WHERE NOT EXISTS (SELECT FROM WHERE. =.) db=> SELECT * FROM DEPT WHERE NOT EXISTS (SELECT * FROM EMP WHERE EMP.DEP TNO = DEPT.DEPTNO) DEPTNO DNAME LOC OPERATIONS BOSTON NOT EXISTS TRUE NOT IN 98

215 . 8 SELECT FROM WHERE (, ) (SELECT, FROM WHERE ) SQL> SELECT ORDER_ID,PRODUCT_ID,QUANTITY FROM ORDER_ITEMS WHERE (PRODUCT_ID,QUANTITY) IN (SELECT PRODUCT_ID,QUANTITY FROM ORDER _ITEMS WHERE ORDER_ID = 8); DB ORDER_ID PRODUCT_ID QUANTITY SELECT FROM WHERE (, ) (SELECT, FROM WHERE ) db=> SELECT ORDER_ID,PRODUCT_ID,QUANTITY FROM ORDER_ITEMS WHERE (PRODUCT_ ID,QUANTITY) IN (SELECT PRODUCT_ID,QUANTITY FROM ORDER_ITEMS WHERE ORDER_ ID = 8) ORDER_ID PRODUCT_ID QUANTITY

216 9, 9,,, 0,0 0,0 0,0 0,0,,0,0 00

217 . 8 8 SELECT FROM WHERE (SELECT FROM WHERE ) AND (SELECT FROM WHERE ) SQL> SELECT ORDER_ID,PRODUCT_ID,QUANTITY FROM ORDER_ITEMS WHERE PRODUCT_ID IN (SELECT PRODUCT_ID FROM ORDER_ITEMS WHERE ORDER_ ID = 8) AND QUANTITY IN (SELECT QUANTITY FROM ORDER_ITEMS WHERE ORDER_ ID = 8); DB ORDER_ID PRODUCT_ID QUANTITY SELECT FROM WHERE (SELECT FROM WHERE ) AND (SELECT FROM WHERE ) db=> SELECT ORDER_ID,PRODUCT_ID,QUANTITY FROM ORDER_ITEMS WHERE PRODUCT_ ID IN (SELECT PRODUCT_ID FROM ORDER_ITEMS WHERE ORDER_ID = 8) AND QU ANTITY IN (SELECT QUANTITY FROM ORDER_ITEMS WHERE ORDER_ID = 8) ORDER_ID PRODUCT_ID QUANTITY

218 WHERE PRODUCT_ID 9,,,0,0,,, QUANTITY,,0,0,,0 9,,,0,0,,,,,0,0,,0 0

219 . SELECT FROM WHERE (SELECT FROM WHERE. =.) SQL> SELECT E.ENAME,E.SAL FROM EMP E WHERE E.SAL >=(SELECT AVG(SAL) FR OM EMP E WHERE E.DEPTNO = E.DEPTNO); ENAME SAL ALLEN 00 JONES 9 BLAKE 80 SCOTT 000 KING 000 FORD 000 DB SELECT FROM WHERE (SELECT FROM WHERE. =.) db=> SELECT E.ENAME,E.SAL FROM EMP E WHERE E.SAL >=(SELECT AVG(SAL) FROM EMP E WHERE E.DEPTNO = E.DEPTNO) ENAME SAL ALLEN JONES 9.00 BLAKE SCOTT KING FORD

220 . 9 SET UPDATE SET = (SELECT FROM ) SQL> UPDATE EMP SET SAL = (SELECT SAL*0.9 FROM EMP WHERE JOB = 'PRESIDENT '); DB UPDATE SET = (SELECT FROM ) db=> UPDATE EMP SET SAL = (SELECT SAL*0.9 FROM EMP WHERE JOB = 'PRESIDEN T') DB0000I SQL WHERE SET SQL 0

221 . UPDATE SET = (SELECT FROM WHERE. =.) SQL> UPDATE EMP E SET E.SAL = (SELECT AVG(E.SAL) FROM EMP E WHERE E. DEPTNO = E.DEPTNO); DB UPDATE SET = (SELECT FROM WHERE. =.) db=> UPDATE EMP E SET E.SAL = (SELECT AVG(E.SAL) FROM EMP E WHERE E. DEPTNO = E.DEPTNO) DB0000I SQL WHERE SET 0

222 . SELECT FROM (SELECT FROM ) SQL> SELECT D.DEPTNO,DNAME,SUM_SAL,MAX_SAL FROM DEPT D,(SELECT DEPTNO,SUM(SAL) AS SUM_SAL,MAX(SAL) AS MAX_SAL FROM EMP GROUP BY DEPTNO) SUMMARY WHERE D.DEPTNO = SUMMARY.DEPTNO; DEPTNO DNAME SUM_SAL MAX_SAL ACCOUNTING RESEARCH SALES DB SELECT FROM (SELECT FROM ) [AS] db=> SELECT D.DEPTNO,DNAME,SUM_SAL,MAX_SAL FROM DEPT D,(SELECT DEPTNO, SUM(SAL) AS SUM_SAL,MAX(SAL) AS MAX_SAL FROM EMP GROUP BY DEPTNO) SUMMARY WHERE D.DEPTNO = SUMMARY.DEPTNO DEPTNO DNAME SUM_SAL MAX_SAL ACCOUNTING RESEARCH SALES WHERE FROM FROM DB UDB FROM 0

223 .8 SELECT ROWNUM, FROM (SELECT FROM ORDER BY ) SQL> SELECT ROWNUM,EMPNO,SAL,ENAME FROM (SELECT EMPNO,SAL,ENAME FROM EMP ORDER BY SAL); ROWNUM EMPNO SAL ENAME SMITH JAMES 8 00 ADAMS 0 WARD 0 MARTIN 9 00 MILLER MARY TURNER ALLEN CLARK BLAKE 9 JONES SCOTT FORD KING DB SELECT ROW_NUMBER OVER(), FROM (SELECT FROM ORDER BY ) db=> SELECT row_number() over(),empno,sal,ename FROM (SELECT EMPNO,SAL,EN AME FROM EMP ORDER BY SAL) TBL EMPNO SAL ENAME SMITH JAMES ADAMS WARD MARTIN MILLER MARY TURNER ALLEN CLARK BLAKE JONES SCOTT FORD KING 0

224 Oracle ROWNUM DB UDB ROW_NUMBER FROM DB UDB FROM 08

225 .9 n SELECT FROM (SELECT FROM ORDER BY ) WHERE ROWNUM <= n SQL> SELECT EMPNO,SAL,ENAME FROM (SELECT EMPNO,SAL,ENAME FROM EMP ORDER BY SAL DESC) WHERE ROWNUM <= ; EMPNO SAL ENAME KING SCOTT FORD 9 JONES BLAKE DB SELECT FROM ORDER BY FETCH FIRST n ROWS ONLY SELECT EMPNO,SAL,ENAME FROM EMP ORDER BY SAL DESC FETCH FIRST ROWS ONLY; EMPNO SAL ENAME KING SCOTT FORD 9.00 JONES BLAKE n Oracle ROWNUM DB UDB FETCH FIRST n ROWS ONLY Oracle ORDER BY FROM DB UDB FROM 09

226

227

228 . SELECT FROM NATURAL JOIN SQL> SELECT DEPTNO,DNAME,ENAME FROM DEPT NATURAL JOIN EMP; DEPTNO DNAME ENAME RESEARCH SMITH 0 SALES ALLEN 0 SALES WARD 0 RESEARCH JONES 0 SALES MARTIN 0 SALES BLAKE 0 ACCOUNTING CLARK 0 RESEARCH SCOTT 0 ACCOUNTING KING 0 SALES TURNER 0 RESEARCH ADAMS 0 SALES JAMES 0 RESEARCH FORD 0 ACCOUNTING MILLER DB SELECT FROM INNER JOIN ON. =. db=> SELECT DEPT.DEPTNO,DNAME,ENAME FROM DEPT INNER JOIN EMP ON DEPT.DEP TNO = EMP.DEPTNO DEPTNO DNAME ENAME ACCOUNTING CLARK 0. ACCOUNTING MILLER 0. ACCOUNTING KING 0. RESEARCH SMITH 0. RESEARCH FORD 0. RESEARCH ADAMS 0. RESEARCH SCOTT 0. RESEARCH JONES 0. SALES ALLEN 0. SALES JAMES 0. SALES TURNER 0. SALES BLAKE 0. SALES MARTIN 0. SALES WARD

229 Oracle NATURAL JOIN NATURAL JOIN DB UDB NATURAL JOIN INNER JOIN ON

230 . SELECT FROM JOIN USING() SQL> SELECT DEPTNO,DNAME,ENAME FROM DEPT JOIN EMP USING(DEPTNO); DEPTNO DNAME ENAME RESEARCH SMITH 0 SALES ALLEN 0 SALES WARD 0 RESEARCH JONES 0 SALES MARTIN 0 SALES BLAKE 0 ACCOUNTING CLARK 0 RESEARCH SCOTT 0 ACCOUNTING KING 0 SALES TURNER 0 RESEARCH ADAMS 0 SALES JAMES 0 RESEARCH FORD 0 ACCOUNTING MILLER DB SELECT FROM INNER JOIN ON. =. db=> SELECT DEPT.DEPTNO,DNAME,ENAME FROM DEPT INNER JOIN EMP ON DEPT.DEP TNO = EMP.DEPTNO DEPTNO DNAME ENAME ACCOUNTING CLARK 0. ACCOUNTING MILLER 0. ACCOUNTING KING 0. RESEARCH SMITH 0. RESEARCH FORD 0. RESEARCH ADAMS 0. RESEARCH SCOTT 0. RESEARCH JONES 0. SALES ALLEN 0. SALES JAMES 0. SALES TURNER 0. SALES BLAKE 0. SALES MARTIN 0. SALES WARD

231 Oracle USING USING USING DB UDB USING INNER JOIN ON

232 . SELECT FROM JOIN USING() SQL> SELECT DEPTNO,DNAME,ENAME FROM DEPT JOIN EMP USING(DEPTNO); DEPTNO DNAME ENAME RESEARCH SMITH 0 SALES ALLEN 0 SALES WARD 0 RESEARCH JONES 0 SALES MARTIN 0 SALES BLAKE 0 ACCOUNTING CLARK 0 RESEARCH SCOTT 0 ACCOUNTING KING 0 SALES TURNER 0 RESEARCH ADAMS 0 SALES JAMES 0 RESEARCH FORD 0 ACCOUNTING MILLER DB SELECT FROM INNER JOIN ON. =. db=> SELECT DEPT.DEPTNO,DNAME,ENAME FROM DEPT INNER JOIN EMP ON DEPT.DEP TNO = EMP.DEPTNO DEPTNO DNAME ENAME ACCOUNTING CLARK 0. ACCOUNTING MILLER 0. ACCOUNTING KING 0. RESEARCH SMITH 0. RESEARCH FORD 0. RESEARCH ADAMS 0. RESEARCH SCOTT 0. RESEARCH JONES 0. SALES ALLEN 0. SALES JAMES 0. SALES TURNER 0. SALES BLAKE 0. SALES MARTIN 0. SALES WARD

233 Oracle USING USING USING DB UDB USING INNER JOIN ON

234 . = SELECT FROM JOIN ON. BETWEEN. AND. SQL> SELECT ENAME,SAL,GRADE,LOSAL,HISAL FROM EMP JOIN SALGRADE ON SAL BETWEEN LOSAL AND HISAL; ENAME SAL GRADE LOSAL HISAL SMITH JAMES ADAMS WARD MARTIN MILLER MARY TURNER ALLEN CLARK BLAKE JONES SCOTT FORD KING DB SELECT FROM [INNER] JOIN ON. BETWEEN. AND. db=> SELECT ENAME,SAL,GRADE,LOSAL,HISAL FROM EMP JOIN SALGRADE ON SAL BETWEEN LOSAL AND HISAL ENAME SAL GRADE LOSAL HISAL SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES

235 FORD MILLER MARY Oracle = ON NATURAL JOIN INNER JOIN DB UDB INNER JOIN ON JOIN INNER INNER 9

236 . SELECT FROM JOIN ON. =. SQL> SELECT M.EMPNO AS MGR_NO,M.ENAME AS MGR_NAME,E.EMPNO AS EMP_NO,E.ENAM E AS EMP_NAME FROM EMP M JOIN EMP E ON M.EMPNO = E.MGR; MGR_NO MGR_NAME EMP_NO EMP_NAME FORD 9 SMITH 98 BLAKE 99 ALLEN 98 BLAKE WARD 89 KING JONES 98 BLAKE MARTIN 89 KING 98 BLAKE 89 KING 8 CLARK JONES 88 SCOTT 98 BLAKE 8 TURNER 88 SCOTT 8 ADAMS 98 BLAKE 900 JAMES JONES 90 FORD 8 CLARK 9 MILLER DB SELECT FROM JOIN ON. =. db=> SELECT M.EMPNO AS MGR_NO,M.ENAME AS MGR_NAME,E.EMPNO AS EMP_NO,E.ENA ME AS EMP_NAME FROM EMP M JOIN EMP E ON M.EMPNO = E.MGR MGR_NO MGR_NAME EMP_NO EMP_NAME FORD 9. SMITH 98. BLAKE 99. ALLEN 98. BLAKE. WARD 89. KING. JONES 98. BLAKE. MARTIN 89. KING 98. BLAKE 89. KING 8. CLARK. JONES 88. SCOTT 98. BLAKE 8. TURNER 88. SCOTT 8. ADAMS 98. BLAKE 900. JAMES. JONES 90. FORD 8. CLARK 9. MILLER 0

237 M EOracle DB UDB JOIN ON

238 . SELECT FROM JOIN ON JOIN ON SQL> SELECT D.DEPTNO,D.DNAME,E.ENAME,S.GRADE FROM DEPT D JOIN EMP E ON D.DEPTNO = E.DEPTNO JOIN SALGRADE S ON SAL BETWEEN LOSAL AND HISAL; DEPTNO DNAME ENAME GRADE ACCOUNTING KING 0 ACCOUNTING CLARK 0 ACCOUNTING MILLER 0 RESEARCH FORD 0 RESEARCH SCOTT 0 RESEARCH JONES 0 RESEARCH ADAMS 0 RESEARCH SMITH 0 SALES BLAKE 0 SALES ALLEN 0 SALES TURNER 0 SALES MARTIN 0 SALES WARD 0 SALES JAMES DB SELECT FROM JOIN ON JOIN ON db=> SELECT D.DEPTNO,D.DNAME,E.ENAME,S.GRADE FROM DEPT D JOIN EMP E ON D.DEPTNO = E.DEPTNO JOIN SALGRADE S ON SAL BETWEEN LOSAL AND HISAL DEPTNO DNAME ENAME GRADE RESEARCH SMITH. 0. SALES ALLEN. 0. SALES WARD. 0. RESEARCH JONES. 0. SALES MARTIN. 0. SALES BLAKE. 0. ACCOUNTING CLARK. 0. RESEARCH SCOTT. 0. ACCOUNTING KING. 0. SALES TURNER. 0. RESEARCH ADAMS. 0. SALES JAMES.

239 0. RESEARCH FORD. 0. ACCOUNTING MILLER. JOIN ON Oracle NATURAL JOIN USING DB UDB INNERJOINLEFT OUTERJOINRIGHT OUTERJOINFULL OUTERJOIN

240 . OPERATIONS FROM JOIN FROM SELECT FROM LEFT OUTER JOIN ON. =. SQL> SELECT D.DEPTNO,D.DNAME,E.ENAME FROM DEPT D LEFT OUTER JOIN EMP E ON D.DEPTNO = E.DEPTNO; DEPTNO DNAME ENAME RESEARCH SMITH 0 SALES ALLEN 0 SALES WARD 0 RESEARCH JONES 0 SALES MARTIN 0 SALES BLAKE 0 ACCOUNTING CLARK 0 RESEARCH SCOTT 0 ACCOUNTING KING 0 SALES TURNER 0 RESEARCH ADAMS 0 SALES JAMES 0 RESEARCH FORD 0 ACCOUNTING MILLER 0 OPERATIONS DB SELECT FROM LEFT OUTER JOIN ON. =. db=> SELECT D.DEPTNO,D.DNAME,E.ENAME FROM DEPT D LEFT OUTER JOIN EMP E ON D.DEPTNO = E.DEPTNO DEPTNO DNAME ENAME ACCOUNTING CLARK 0. ACCOUNTING MILLER 0. ACCOUNTING KING 0. RESEARCH SMITH 0. RESEARCH FORD 0. RESEARCH ADAMS 0. RESEARCH SCOTT 0. RESEARCH JONES 0. SALES ALLEN 0. SALES JAMES

241 0. SALES TURNER 0. SALES BLAKE 0. SALES MARTIN 0. SALES WARD 0. OPERATIONS - OUTER JOINFROM JOIN FROM Oracle DB UDB LEFTOUTERJOINRIGHTOUTERJOINFULLOUTER JOIN

242 .8 MARY JOIN FROM JOIN SELECT FROM RIGHT OUTER JOIN ON. =. SQL> SELECT D.DEPTNO,D.DNAME,E.ENAME FROM DEPT D RIGHT OUTER JOIN EMP E ON D.DEPTNO = E.DEPTNO; DEPTNO DNAME ENAME ACCOUNTING MILLER 0 ACCOUNTING KING 0 ACCOUNTING CLARK 0 RESEARCH FORD 0 RESEARCH ADAMS 0 RESEARCH SCOTT 0 RESEARCH JONES 0 RESEARCH SMITH 0 SALES JAMES 0 SALES TURNER 0 SALES BLAKE 0 SALES MARTIN 0 SALES WARD 0 SALES ALLEN MARY DB SELECT FROM RIGHT OUTER JOIN ON. =. db=> SELECT D.DEPTNO,D.DNAME,E.ENAME FROM DEPT D RIGHT OUTER JOIN EMP E ON D.DEPTNO = E.DEPTNO DEPTNO DNAME ENAME RESEARCH SMITH 0. SALES ALLEN 0. SALES WARD 0. RESEARCH JONES 0. SALES MARTIN 0. SALES BLAKE 0. ACCOUNTING CLARK 0. RESEARCH SCOTT 0. ACCOUNTING KING 0. SALES TURNER

243 0. RESEARCH ADAMS 0. SALES JAMES 0. RESEARCH FORD 0. ACCOUNTING MILLER - - MARY OUTER JOINJOIN FROM JOIN Oracle DB UDB RIGHT OUTER JOIN

244 .9 OPERATIONS MARY FROM JOIN FROM JOIN SELECT FROM FULL OUTER JOIN ON. =. SQL> SELECT D.DEPTNO,D.DNAME,E.ENAME FROM DEPT D FULL OUTER JOIN EMP E ON D.DEPTNO = E.DEPTNO; DEPTNO DNAME ENAME RESEARCH SMITH 0 SALES ALLEN 0 SALES WARD 0 RESEARCH JONES 0 SALES MARTIN 0 SALES BLAKE 0 ACCOUNTING CLARK 0 RESEARCH SCOTT 0 ACCOUNTING KING 0 SALES TURNER 0 RESEARCH ADAMS 0 SALES JAMES 0 RESEARCH FORD 0 ACCOUNTING MILLER 0 OPERATIONS MARY DB SELECT FROM FULL OUTER JOIN ON. =. db=> SELECT D.DEPTNO,D.DNAME,E.ENAME FROM DEPT D FULL OUTER JOIN EMP E ON D.DEPTNO = E.DEPTNO DEPTNO DNAME ENAME RESEARCH SMITH 0. SALES ALLEN 0. SALES WARD 0. RESEARCH JONES 0. SALES MARTIN 0. SALES BLAKE 0. ACCOUNTING CLARK 0. RESEARCH SCOTT 0. ACCOUNTING KING 8

245 0. SALES TURNER 0. RESEARCH ADAMS 0. SALES JAMES 0. RESEARCH FORD 0. ACCOUNTING MILLER - - MARY 0. OPERATIONS - OUTER JOINFROM JOIN FROM JOIN Oracle DB UDB FULL OUTER JOIN 9

246 .0 OPERATIONS SELECT FROM LEFT OUTER JOIN ON. =. WHERE. IS NULL SQL> SELECT D.DEPTNO,D.DNAME,E.ENAME FROM DEPT D LEFT OUTER JOIN EMP E ON D.DEPTNO = E.DEPTNO WHERE E.EMPNO IS NULL; DEPTNO DNAME ENAME OPERATIONS DB SELECT FROM LEFT OUTER JOIN ON. =. WHERE. IS NULL db=> SELECT D.DEPTNO,D.DNAME,E.ENAME FROM DEPT D LEFT OUTER JOIN EMP E ON D.DEPTNO = E.DEPTNO WHERE E.EMPNO IS NULL DEPTNO DNAME ENAME OPERATIONS - OUTER JOINFROM JOIN FROM Oracle DB UDB LEFT OUTER JOIN JOIN NULL JOIN. IS NULL NULL SQL SELECT FROM WHERE NOT EXISTS (SELECT * FROM WHERE. =.) SELECT D.DEPTNO, D.DNAME FROM DEPT D WHERE NOT EXISTS 0

247 (SELECT * FROM EMP E WHERE D.DEPTNO = E.DEPTNO); DEPTNO DNAME OPERATIONS

248 . 0 MANAGER SELECT FROM JOIN ON. =. WHERE SQL> SELECT DEPTNO,DNAME,ENAME FROM DEPT NATURAL JOIN EMP WHERE DEPTNO = 0 AND JOB = 'MANAGER'; DEPTNO DNAME ENAME ACCOUNTING CLARK DB SELECT FROM [INNER] JOIN ON. =. WHERE db=> SELECT DEPT.DEPTNO,DNAME,ENAME FROM DEPT INNER JOIN EMP ON DEPT.DEPT NO = EMP.DEPTNO WHERE DEPT.DEPTNO = 0 AND JOB = 'MANAGER' DEPTNO DNAME ENAME ACCOUNTING CLARK ON WHERE

249 . SELECT FROM JOIN ON. =. ORDER BY SQL> SELECT DEPTNO,DNAME,ENAMEi FROM DEPT NATURAL JOIN EMP ORDER BY DEPTNO; DEPTNO DNAME ENAME ACCOUNTING CLARK 0 ACCOUNTING KING 0 ACCOUNTING MILLER 0 RESEARCH SMITH 0 RESEARCH ADAMS 0 RESEARCH FORD 0 RESEARCH SCOTT 0 RESEARCH JONES 0 SALES ALLEN 0 SALES BLAKE 0 SALES MARTIN 0 SALES JAMES 0 SALES TURNER 0 SALES WARD DB SELECT FROM INNER JOIN ON. =. ORDER BY db=> SELECT DEPT.DEPTNO,DNAME,ENAME FROM DEPT INNER JOIN EMP ON DEPT.DEPT NO = EMP.DEPTNO ORDER BY DEPTNO DEPTNO DNAME ENAME ACCOUNTING CLARK 0. ACCOUNTING KING 0. ACCOUNTING MILLER 0. RESEARCH SMITH 0. RESEARCH JONES 0. RESEARCH SCOTT 0. RESEARCH ADAMS 0. RESEARCH FORD 0. SALES ALLEN 0. SALES WARD 0. SALES MARTIN 0. SALES BLAKE

250 0. SALES TURNER 0. SALES JAMES ORDER BY SQL

251 . SELECT FROM UNION SELECT FROM SQL> SELECT DEPTNO FROM DEPT UNION SELECT DEPTNO FROM EMP; DB DEPTNO SELECT FROM UNION SELECT FROM db=> SELECT DEPTNO FROM DEPT UNION SELECT DEPTNO FROM EMP DEPTNO UNION

252 . SELECT FROM UNION ALL SELECT FROM SQL> SELECT DEPTNO FROM DEPT UNION ALL SELECT DEPTNO FROM EMP; DEPTNO DB SELECT FROM UNION ALL SELECT FROM db=> SELECT DEPTNO FROM DEPT UNION ALL SELECT DEPTNO FROM EMP DEPTNO

253 UNION ALL

254 . SELECT FROM MINUS SELECT FROM SQL> SELECT DEPTNO FROM DEPT MINUS SELECT DEPTNO FROM EMP; DEPTNO DB SELECT FROM EXCEPT SELECT FROM db=> SELECT DEPTNO FROM DEPT EXCEPT SELECT DEPTNO FROM EMP DEPTNO Oracle MINUS DB UDB EXCEPT 8

255 . SELECT FROM INTERSECT SELECT FROM SQL> SELECT DEPTNO FROM DEPT INTERSECT SELECT DEPTNO FROM EMP; DEPTNO DB SELECT FROM INTERSECT SELECT FROM db=> SELECT DEPTNO FROM DEPT INTERSECT SELECT DEPTNO FROM EMP DEPTNO INTERSECT 9

256 . SELECT FROM CROSS JOIN SQL> SELECT DEPT.DEPTNO,DNAME,ENAME FROM DEPT CROSS JOIN EMP; DEPTNO DNAME ENAME ACCOUNTING SMITH 0 ACCOUNTING ALLEN 0 ACCOUNTING WARD 0 ACCOUNTING JONES 0 ACCOUNTING MARTIN 0 ACCOUNTING BLAKE 0 ACCOUNTING CLARK 0 ACCOUNTING SCOTT 0 ACCOUNTING KING 0 ACCOUNTING TURNER 0 ACCOUNTING ADAMS 0 ACCOUNTING JAMES 0 ACCOUNTING FORD 0 ACCOUNTING MILLER 0 ACCOUNTING MARY 0 RESEARCH SMITH 0 RESEARCH ALLEN 0 RESEARCH WARD 0 RESEARCH JONES 0 RESEARCH MARTIN 0 RESEARCH BLAKE DEPTNO DNAME ENAME RESEARCH CLARK 0 RESEARCH SCOTT 0 RESEARCH KING 0 RESEARCH TURNER 0 RESEARCH ADAMS 0 RESEARCH JAMES 0 RESEARCH FORD 0 RESEARCH MILLER 0 RESEARCH MARY 0 SALES SMITH 0 SALES ALLEN 0 SALES WARD 0 SALES JONES 0 SALES MARTIN 0 SALES BLAKE 0 SALES CLARK 0 SALES SCOTT 0 SALES KING 0 SALES TURNER 0

257 DB 0 SALES ADAMS 0 SALES JAMES DEPTNO DNAME ENAME SALES FORD 0 SALES MILLER 0 SALES MARY 0 OPERATIONS SMITH 0 OPERATIONS ALLEN 0 OPERATIONS WARD 0 OPERATIONS JONES 0 OPERATIONS MARTIN 0 OPERATIONS BLAKE 0 OPERATIONS CLARK 0 OPERATIONS SCOTT 0 OPERATIONS KING 0 OPERATIONS TURNER 0 OPERATIONS ADAMS 0 OPERATIONS JAMES 0 OPERATIONS FORD 0 OPERATIONS MILLER 0 OPERATIONS MARY 0 SELECT FROM, SELECT FROM INNER JOIN ON 0=0 db=> SELECT DEPT.DEPTNO,DNAME,ENAME FROM DEPT,EMP DEPTNO DNAME ENAME ACCOUNTING SMITH 0. RESEARCH SMITH 0. SALES SMITH 0. OPERATIONS SMITH 0. ACCOUNTING ALLEN 0. RESEARCH ALLEN 0. SALES ALLEN 0. OPERATIONS ALLEN 0. ACCOUNTING WARD 0. RESEARCH WARD 0. SALES WARD 0. OPERATIONS WARD 0. ACCOUNTING JONES 0. RESEARCH JONES 0. SALES JONES 0. OPERATIONS JONES 0. ACCOUNTING MARTIN 0. RESEARCH MARTIN 0. SALES MARTIN 0. OPERATIONS MARTIN 0. ACCOUNTING BLAKE 0. RESEARCH BLAKE 0. SALES BLAKE 0. OPERATIONS BLAKE 0. ACCOUNTING CLARK

258 0. RESEARCH CLARK 0. SALES CLARK 0. OPERATIONS CLARK 0. ACCOUNTING SCOTT 0. RESEARCH SCOTT 0. SALES SCOTT 0. OPERATIONS SCOTT 0. ACCOUNTING KING 0. RESEARCH KING 0. SALES KING 0. OPERATIONS KING 0. ACCOUNTING TURNER 0. RESEARCH TURNER 0. SALES TURNER 0. OPERATIONS TURNER 0. ACCOUNTING ADAMS 0. RESEARCH ADAMS 0. SALES ADAMS 0. OPERATIONS ADAMS 0. ACCOUNTING JAMES 0. RESEARCH JAMES 0. SALES JAMES 0. OPERATIONS JAMES 0. ACCOUNTING FORD 0. RESEARCH FORD 0. SALES FORD 0. OPERATIONS FORD 0. ACCOUNTING MILLER 0. RESEARCH MILLER 0. SALES MILLER 0. OPERATIONS MILLER 0. ACCOUNTING MARY 0. RESEARCH MARY 0. SALES MARY 0. OPERATIONS MARY 0 Oracle CORSS JOIN DB UDB FROM DB UDB CROSS JOIN ON 0=0 CROSS JOIN

259

260 . SELECT TABLE_NAME FROM USER_TABLES; SQL> SELECT TABLE_NAME FROM USER_TABLES; TABLE_NAME DUMMY SALGRADE BONUS DEPT EMP DB SELECT TABNAME FROM SYSCAT.TABLES SELECT SUBSTR(TABNAME,,0) AS TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA = 'IS'; TABNAME ANIMALS_PRIV_INDEXES BASE_INDEX_SIMULATE TABLE_VAR_DEFN Oracle USER_TABLES TABCAT DB DB LIST TABLES DB LIST TABLES FOR ALL DB UDB SYSCAT.TABLES

261 . CREATE TABLE ( (),[, (),] ) SQL> CREATE TABLE TEST (COL NUMBER, COL VARCHAR(0), COL DATE); DB CREATE TABLE ( (),[, (),] ) CREATE TABLE TEST (COL FLOAT,COL VARCHAR(0),COL TIMESTAMP); DB0000I SQL Oracle DB UDB SQL

262 . CREATE TABLE ( {CHAR VARCHAR}() DEFAULT '') SQL> CREATE TABLE TEST (COL NUMBER, COL VARCHAR(0) DEFAULT 'Hello', COL DATE); DB CREATE TABLE ( {CHAR VARCHAR}() DEFAULT '') CREATE TABLE TEST (COL FLOAT,COL VARCHAR(0) DEFAULT 'Hello',COL TIMESTAMP); DB0000I SQL

263 . DB CREATE TABLE ( DATE DEFAULT SYSDATE) SQL> CREATE TABLE TEST (COL NUMBER, COL VARCHAR(0), COL DATE DEFAULT SYSDATE); CREATE TABLE ( TIMESTAMP DEFAULT CURRENT_TIMESTAMP) CREATE TABLE TEST (COL FLOAT,COL VARCHAR(0),COL TIMESTAMP DEFAULT CURRENT_TIMESTAMP); DB0000I SQL

264 . CREATE TABLE AS SELECT * FROM SQL> CREATE TABLE EMP AS SELECT * FROM EMP; SQL> SELECT COUNT(*) FROM EMP; COUNT(*) DB CREATE TABLE EMP LIKE EMP; DB0000I SQL INSERT INTO EMP SELECT * FROM EMP; DB0000I SQL SELECT COUNT(*) FROM EMP; DB UDB IXF EXPORT IMPORT EXPORT TO OF IXF SELECT * FROM EMP; IMPORT FROM OF IXF CREATE INTO EMP in index in ; GUI 8

265 . SELECT CREATE TABLE AS SELECT [,,...] FROM SQL> CREATE TABLE EMP AS SELECT DEPTNO,EMPNO,ENAME FROM EMP; DB. EXPORT/ IMPORT db=> EXPORT TO OF IXF SELECT DEPTNO,EMPNO,ENAME FROM EMP db=> IMPORT FROM OF IXF CREATE INTO EMP 9

266 . CREATE TABLE () AS SELECT FROM SQL> CREATE TABLE EMP (DEPT_ID,EMP_ID,EMP_NAME) AS SELECT DEPTNO,EMPNO,ENAME FROM EMP; DB. IMPORT EXPORT/ db=> EXPORT TO OF IXF SELECT DEPTNO,EMPNO,ENAME FROM EMP db=> IMPORT FROM OF IXF CREATE INTO EMP(DEPT_ID,EMP_ID,EMP_NAME) 0

267 .8 SELECT CREATE TABLE AS SELECT FROM WHERE SQL> CREATE TABLE EMP AS SELECT * FROM EMP WHERE DEPTNO = 0; DB CREATE TABLE EMP LIKE EMP; DB0000I SQL INSERT INTO EMP SELECT * FROM EMP WHERE DEPTNO = 0; DB0000I SQL. EXPORT/IMPORT db=> EXPORT TO OF IXF SELECT * FROM EMP WHERE DEPTNO=0 db=> IMPORT FROM OF IXF CREATE INTO EMP

268 .9 CREATE TABLE AS SELECT FROM WHERE = SQL> CREATE TABLE EMP AS SELECT * FROM EMP WHERE =; SQL> SELECT COUNT(*) FROM EMP; COUNT(*) DB CREATE TABLE LIKE CREATE TABLE EMP LIKE EMP; DB0000I SQL SELECT COUNT(*) FROM EMP; DB UDB CREATE TABLE LIKE

269 .0 DROP TABLE SQL> DROP TABLE EMP; DB DROP TABLE DROP TABLE EMP DB0000I SQL

270 . DROP TABLE CASCADE CONSTRAINTS SQL> DROP TABLE DEPT CASCADE CONSTRAINTS; DB DROP TABLE DROP TABLE DEPT; DB0000I SQL DB UDB CASCADE CONSTRAINTS

271 . RENAME TO SQL> RENAME SALGRADE TO GRADE; DB RENAME [TABLE] TO RENAME TABLE SALGRADE TO GRADE; DB0000I SQL DB UDB SQL SQL

272 . ALTER TABLE. RENAME TO SQL> ALTER TABLE SCOTT.SALGRADE RENAME TO GRADE; DB RENAME TABLE. TO RENAME TABLE SCOTT.SALGRADE TO GRADE; DB0000I SQL DB UDB RENAME CONTROL ALTERIN SYSADM DBADM

273 . ALTER TABLE ADD () SQL> ALTER TABLE EMP ADD VARCHAR(0); DB ALTER TABLE ADD () ALTER TABLE EMP ADD VARCHAR(0); DB0000I SQL

274 . ALTER TABLE DROP COLUMN SQL> ALTER TABLE EMP DROP COLUMN ; DB DB UDB GUI ALTOBJ 8