SQL> SQL> SQL> SQL> select * 2 from deptno='10'; from deptno='10' * 2행에 오류: ORA-00933: SQL 명령어가 올바르게 종료되지 않았습니다 SQL> select * 2 from deptno=10 3 SQL> select * 2 from dept 3 where deptno='10'; DEPTNO DNAME LOC ---------- ---------------------------- -------------------------- 10 ACCOUNTING NEW YORK SQL> SQL> SQL> select * 2 from salary 3 where hisal>=2000; from salary * 2행에 오류: ORA-00942: 테이블 또는 뷰가 존재하지 않습니다 SQL> select * 2 from salary 3 SQL> select * 2 from salgrade 3 where hisal>=2000; GRADE LOSAL HISAL ---------- ---------- ---------- 3 1401 2000 4 2001 3000 5 3001 9999 SQL> SQL> select empno,ename 2 from emp 3 where ename='FORD'; EMPNO ENAME ---------- -------------------- 7902 FORD SQL> select ename,hiredate 2 from emp; ENAME HIREDATE -------------------- ---------------------- SMITH 80/12/17 ALLEN 81/02/20 WARD 81/02/22 JONES 81/04/02 MARTIN 81/09/28 BLAKE 81/05/01 CLARK 81/06/09 SCOTT 87/04/19 KING 81/11/17 TURNER 81/09/08 ADAMS 87/05/23 ENAME HIREDATE -------------------- ---------------------- JAMES 81/12/03 FORD 81/12/03 MILLER 82/01/23 14 개의 행이 선택되었습니다. SQL> select * 2 from emp 3 where ename like 'K%'; EMPNO ENAME JOB MGR ---------- -------------------- ------------------ ---------- HIREDATE SAL COMM DEPTNO ---------------------- ---------- ---------- ---------- 7839 KING PRESIDENT 81/11/17 5000 10 SQL> select * 2 from emp 3 where comm in(300,500,1400); EMPNO ENAME JOB MGR ---------- -------------------- ------------------ ---------- HIREDATE SAL COMM DEPTNO ---------------------- ---------- ---------- ---------- 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 7521 WARD SALESMAN 7698 81/02/22 1250 500 30 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 SQL> select * 2 from emp 3 where sal between 500 and 400; 선택된 레코드가 없습니다. SQL> select 2 * 3 from emp 4 where sal between 500 and 400; 선택된 레코드가 없습니다. SQL> select 2 * 3 from emp 4 where sal between 500 and 4000; EMPNO ENAME JOB MGR ---------- -------------------- ------------------ ---------- HIREDATE SAL COMM DEPTNO ---------------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 80/12/17 800 20 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 7521 WARD SALESMAN 7698 81/02/22 1250 500 30 EMPNO ENAME JOB MGR ---------- -------------------- ------------------ ---------- HIREDATE SAL COMM DEPTNO ---------------------- ---------- ---------- ---------- 7566 JONES MANAGER 7839 81/04/02 2975 20 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 7698 BLAKE MANAGER 7839 81/05/01 2850 30 EMPNO ENAME JOB MGR ---------- -------------------- ------------------ ---------- HIREDATE SAL COMM DEPTNO ---------------------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 81/06/09 2450 10 7788 SCOTT ANALYST 7566 87/04/19 3000 20 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 EMPNO ENAME JOB MGR ---------- -------------------- ------------------ ---------- HIREDATE SAL COMM DEPTNO ---------------------- ---------- ---------- ---------- 7876 ADAMS CLERK 7788 87/05/23 1100 20 7900 JAMES CLERK 7698 81/12/03 950 30 7902 FORD ANALYST 7566 81/12/03 3000 20 EMPNO ENAME JOB MGR ---------- -------------------- ------------------ ---------- HIREDATE SAL COMM DEPTNO ---------------------- ---------- ---------- ---------- 7934 MILLER CLERK 7782 82/01/23 1300 10 13 개의 행이 선택되었습니다. SQL> select * 2 from emp 3 where mgr='null' 4 ; where mgr='null' * 3행에 오류: ORA-01722: 수치가 부적합합니다 SQL> select * 2 from emp 3 where mgr='NULL'; where mgr='NULL' * 3행에 오류: ORA-01722: 수치가 부적합합니다 SQL> select * 2 from emp 3 where mgr=' '; where mgr=' ' * 3행에 오류: ORA-01722: 수치가 부적합합니다 SQL> select * 2 from emp 3 where mgr is NULL; EMPNO ENAME JOB MGR ---------- -------------------- ------------------ ---------- HIREDATE SAL COMM DEPTNO ---------------------- ---------- ---------- ---------- 7839 KING PRESIDENT 81/11/17 5000 10 SQL> select * 2 from emp 3 SQL> select sal 2 from emp 3 SQL> select ename,hiredate 2 from emp 3 where hiredate >= '1982/01/01'; ENAME HIREDATE -------------------- ---------------------- SMITH 80/12/17 ALLEN 81/02/20 WARD 81/02/22 JONES 81/04/02 MARTIN 81/09/28 BLAKE 81/05/01 CLARK 81/06/09 SCOTT 87/04/19 KING 81/11/17 TURNER 81/09/08 ADAMS 87/05/23 ENAME HIREDATE -------------------- ---------------------- JAMES 81/12/03 FORD 81/12/03 MILLER 82/01/23 14 개의 행이 선택되었습니다. SQL> select ename,hiredate 2 from emp 3 where hiredate >= '82/01/01'; ENAME HIREDATE -------------------- ---------------------- SCOTT 87/04/19 ADAMS 87/05/23 MILLER 82/01/23 SQL> SQL> SQL> SQL> select sal 2 from emp 3 order by sal desc; SAL ---------- 5000 3000 3000 2975 2850 2450 1600 1500 1300 1250 1250 SAL ---------- 1100 950 800 14 개의 행이 선택되었습니다. SQL> select upper(ename) 2 from emp; UPPER(ENAME) -------------------- SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS UPPER(ENAME) -------------------- JAMES FORD MILLER 14 개의 행이 선택되었습니다. SQL> select ename, instr(ename,'S') 2 from emp; ENAME INSTR(ENAME,'S') -------------------- ---------------- SMITH 1 ALLEN 0 WARD 0 JONES 5 MARTIN 0 BLAKE 0 CLARK 0 SCOTT 1 KING 0 TURNER 0 ADAMS 5 ENAME INSTR(ENAME,'S') -------------------- ---------------- JAMES 5 FORD 0 MILLER 0 14 개의 행이 선택되었습니다. SQL> select sysdate 2 from dual; SYSDATE -------- 09/09/18 SQL> select ename,hirdate,sysdate-to_date(hiredate) 2 from emp; select ename,hirdate,sysdate-to_date(hiredate) * 1행에 오류: ORA-00904: "HIRDATE": 부적합한 식별자 SQL> select ename,hirdate,sysdate - to_date(hiredate) 2 from emp; select ename,hirdate,sysdate - to_date(hiredate) * 1행에 오류: ORA-00904: "HIRDATE": 부적합한 식별자 SQL> SQL> select ename,hiredate,sysdate - to_date(hiredate) 2 from emp; ENAME HIREDATE SYSDATE-TO_DATE(HIREDATE) -------------------- ---------------------- ------------------------- SMITH 80/12/17 10502.7087 ALLEN 81/02/20 10437.7087 WARD 81/02/22 10435.7087 JONES 81/04/02 10396.7087 MARTIN 81/09/28 10217.7087 BLAKE 81/05/01 10367.7087 CLARK 81/06/09 10328.7087 SCOTT 87/04/19 8188.70866 KING 81/11/17 10167.7087 TURNER 81/09/08 10237.7087 ADAMS 87/05/23 8154.70866 ENAME HIREDATE SYSDATE-TO_DATE(HIREDATE) -------------------- ---------------------- ------------------------- JAMES 81/12/03 10151.7087 FORD 81/12/03 10151.7087 MILLER 82/01/23 10100.7087 14 개의 행이 선택되었습니다. SQL> select comm,NVL(comm,0) 2 from emp; COMM NVL(COMM,0) ---------- ----------- 0 300 300 500 500 0 1400 1400 0 0 0 0 0 0 0 COMM NVL(COMM,0) ---------- ----------- 0 0 0 14 개의 행이 선택되었습니다. SQL> select ename,job 2 SQL> select ename,job, 2 decode(job, 'CLERK, sal*1.30, 3 'ANALYST', sal*1.15, 4 'MANAGER', sal*1.10, 5 sal) as "UP sal" 6 from emp; ERROR: ORA-01756: 단일 인용부를 지정해 주십시오 SQL> select ename,job, 2 decode(job, 'CLERK', sal*1.30, 3 'ANALYST', sal*1.15, 4 'MANAGER', sal*1.10, 5 sal) as "UP sal" 6 from emp; ENAME JOB UP sal -------------------- ------------------ ---------- SMITH CLERK 1040 ALLEN SALESMAN 1600 WARD SALESMAN 1250 JONES MANAGER 3272.5 MARTIN SALESMAN 1250 BLAKE MANAGER 3135 CLARK MANAGER 2695 SCOTT ANALYST 3450 KING PRESIDENT 5000 TURNER SALESMAN 1500 ADAMS CLERK 1430 ENAME JOB UP sal -------------------- ------------------ ---------- JAMES CLERK 1235 FORD ANALYST 3450 MILLER CLERK 1690 14 개의 행이 선택되었습니다. SQL> SQL> spool off