SQL> select empno, ename, sal from emp where deptno = 10;
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
SQL> select ename, hiredate, deptno from emp where empno = 7369;
ENAME HIREDATE DEPTNO
---------- -------- ----------
SMITH 80/12/17 20
SQL> select * from emp where ename = upper('allen');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
SQL> select ename, deptno, sal from emp where hiredate = '81/04/02';
ENAME DEPTNO SAL
---------- ---------- ----------
JONES 20 2975
SQL> select * from emp where job <> upper('manager');
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
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
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
7934 MILLER CLERK 7782 82/01/23 1300 10
11 개의 행이 선택되었습니다.
SQL> select ename, deptno, sal from emp where hiredate > '81/04/02';
ENAME DEPTNO SAL
---------- ---------- ----------
MARTIN 30 1250
BLAKE 30 2850
CLARK 10 2450
SCOTT 20 3000
KING 10 5000
TURNER 30 1500
ADAMS 20 1100
JAMES 30 950
FORD 20 3000
MILLER 10 1300
10 개의 행이 선택되었습니다.
SQL> select ename, sal, deptno from emp where sal >= 800;
ENAME SAL DEPTNO
---------- ---------- ----------
SMITH 800 20
ALLEN 1600 30
WARD 1250 30
JONES 2975 20
MARTIN 1250 30
BLAKE 2850 30
CLARK 2450 10
SCOTT 3000 20
KING 5000 10
TURNER 1500 30
ADAMS 1100 20
JAMES 950 30
FORD 3000 20
MILLER 1300 10
14 개의 행이 선택되었습니다.
SQL> select * from emp where deptno >= 20;
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
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
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
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
11 개의 행이 선택되었습니다.
SQL> select * from emp where ename > 'K%' and ename not like 'K%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7934 MILLER CLERK 7782 82/01/23 1300 10
6 개의 행이 선택되었습니다.
SQL> select * from emp where hiredate < '81/12/09';
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
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
7782 CLARK MANAGER 7839 81/06/09 2450 10
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
11 개의 행이 선택되었습니다.
SQL> select empno, ename from emp where empno <= 7698;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
6 개의 행이 선택되었습니다.
SQL> select ename, sal, deptno from emp where hiredate > '81/04/02' and hiredate <'82/12/09';
ENAME SAL DEPTNO
---------- ---------- ----------
MARTIN 1250 30
BLAKE 2850 30
CLARK 2450 10
KING 5000 10
TURNER 1500 30
JAMES 950 30
FORD 3000 20
MILLER 1300 10
8 개의 행이 선택되었습니다.
SQL> select ename, job, sal from emp where sal > 1600 and sal < 3000;
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
SQL> select * from emp where empno < 7654 or empno > 7782;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
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
7934 MILLER CLERK 7782 82/01/23 1300 10
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
7566 JONES MANAGER 7839 81/04/02 2975 20
11 개의 행이 선택되었습니다.
SQL> select * from emp where ename between 'B%' and 'J%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7902 FORD ANALYST 7566 81/12/03 3000 20
SQL> select * from emp where substr(hiredate,1,2) <> '81';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7876 ADAMS CLERK 7788 87/05/23 1100 20
7934 MILLER CLERK 7782 82/01/23 1300 10
SQL> select * from emp where job IN ('MANAGER','SALESMAN');
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
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
7782 CLARK MANAGER 7839 81/06/09 2450 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7 개의 행이 선택되었습니다.
SQL> select ename, empno, deptno from emp where deptno <> 20 and deptno <> 30;
ENAME EMPNO DEPTNO
---------- ---------- ----------
CLARK 7782 10
KING 7839 10
MILLER 7934 10
SQL> select empno, ename, hiredate, deptno from emp where ename like 'S%';
EMPNO ENAME HIREDATE DEPTNO
---------- ---------- -------- ----------
7369 SMITH 80/12/17 20
7788 SCOTT 87/04/19 20
SQL> select * from emp where hiredate like '81%';
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
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
7782 CLARK MANAGER 7839 81/06/09 2450 10
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
10 개의 행이 선택되었습니다.
SQL> select * from emp where ename like '%S%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7566 JONES MANAGER 7839 81/04/02 2975 20
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7876 ADAMS CLERK 7788 87/05/23 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
SQL> select * from emp where ename like 'S%T';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 87/04/19 3000 20
SQL> select * from emp where ename like 'S___T';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 87/04/19 3000 20
SQL> select * from emp where ename like '_A%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7900 JAMES CLERK 7698 81/12/03 950 30
SQL> select * from emp where comm is null;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7566 JONES MANAGER 7839 81/04/02 2975 20
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
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
7934 MILLER CLERK 7782 82/01/23 1300 10
10 개의 행이 선택되었습니다.
SQL> select * from emp where comm is not null;
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
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
SQL> select ename, deptno, sal from emp where deptno = 30 and sal >= 1500;
ENAME DEPTNO SAL
---------- ---------- ----------
ALLEN 30 1600
BLAKE 30 2850
TURNER 30 1500
SQL> select empno, ename, deptno from emp where ename like 'K%' or deptno = 30;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7499 ALLEN 30
7521 WARD 30
7654 MARTIN 30
7698 BLAKE 30
7839 KING 10
7844 TURNER 30
7900 JAMES 30
7 개의 행이 선택되었습니다.
SQL> select * from emp where sal >= 1500 and deptno = 30 and job = 'MANAGER';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 81/05/01 2850 30
SQL> select * from emp where deptno = 30 order by empno asc;
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
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7900 JAMES CLERK 7698 81/12/03 950 30
6 개의 행이 선택되었습니다.
SQL> select * from emp order by sal asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7900 JAMES CLERK 7698 81/12/03 950 30
7876 ADAMS CLERK 7788 87/05/23 1100 20
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7934 MILLER CLERK 7782 82/01/23 1300 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7902 FORD ANALYST 7566 81/12/03 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
14 개의 행이 선택되었습니다.
SQL> select * from emp order by deptno asc, sal desc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7839 KING PRESIDENT 81/11/17 5000 10
7782 CLARK MANAGER 7839 81/06/09 2450 10
7934 MILLER CLERK 7782 82/01/23 1300 10
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7902 FORD ANALYST 7566 81/12/03 3000 20
7566 JONES MANAGER 7839 81/04/02 2975 20
7876 ADAMS CLERK 7788 87/05/23 1100 20
7369 SMITH CLERK 7902 80/12/17 800 20
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7900 JAMES CLERK 7698 81/12/03 950 30
14 개의 행이 선택되었습니다.
SQL> select * from emp order by deptno desc, ename asc, sal desc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7900 JAMES CLERK 7698 81/12/03 950 30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7876 ADAMS CLERK 7788 87/05/23 1100 20
7902 FORD ANALYST 7566 81/12/03 3000 20
7566 JONES MANAGER 7839 81/04/02 2975 20
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7369 SMITH CLERK 7902 80/12/17 800 20
7782 CLARK MANAGER 7839 81/06/09 2450 10
7839 KING PRESIDENT 81/11/17 5000 10
7934 MILLER CLERK 7782 82/01/23 1300 10
14 개의 행이 선택되었습니다.
SQL> spool off
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
SQL> select ename, hiredate, deptno from emp where empno = 7369;
ENAME HIREDATE DEPTNO
---------- -------- ----------
SMITH 80/12/17 20
SQL> select * from emp where ename = upper('allen');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
SQL> select ename, deptno, sal from emp where hiredate = '81/04/02';
ENAME DEPTNO SAL
---------- ---------- ----------
JONES 20 2975
SQL> select * from emp where job <> upper('manager');
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
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
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
7934 MILLER CLERK 7782 82/01/23 1300 10
11 개의 행이 선택되었습니다.
SQL> select ename, deptno, sal from emp where hiredate > '81/04/02';
ENAME DEPTNO SAL
---------- ---------- ----------
MARTIN 30 1250
BLAKE 30 2850
CLARK 10 2450
SCOTT 20 3000
KING 10 5000
TURNER 30 1500
ADAMS 20 1100
JAMES 30 950
FORD 20 3000
MILLER 10 1300
10 개의 행이 선택되었습니다.
SQL> select ename, sal, deptno from emp where sal >= 800;
ENAME SAL DEPTNO
---------- ---------- ----------
SMITH 800 20
ALLEN 1600 30
WARD 1250 30
JONES 2975 20
MARTIN 1250 30
BLAKE 2850 30
CLARK 2450 10
SCOTT 3000 20
KING 5000 10
TURNER 1500 30
ADAMS 1100 20
JAMES 950 30
FORD 3000 20
MILLER 1300 10
14 개의 행이 선택되었습니다.
SQL> select * from emp where deptno >= 20;
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
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
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
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
11 개의 행이 선택되었습니다.
SQL> select * from emp where ename > 'K%' and ename not like 'K%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7934 MILLER CLERK 7782 82/01/23 1300 10
6 개의 행이 선택되었습니다.
SQL> select * from emp where hiredate < '81/12/09';
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
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
7782 CLARK MANAGER 7839 81/06/09 2450 10
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
11 개의 행이 선택되었습니다.
SQL> select empno, ename from emp where empno <= 7698;
EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
6 개의 행이 선택되었습니다.
SQL> select ename, sal, deptno from emp where hiredate > '81/04/02' and hiredate <'82/12/09';
ENAME SAL DEPTNO
---------- ---------- ----------
MARTIN 1250 30
BLAKE 2850 30
CLARK 2450 10
KING 5000 10
TURNER 1500 30
JAMES 950 30
FORD 3000 20
MILLER 1300 10
8 개의 행이 선택되었습니다.
SQL> select ename, job, sal from emp where sal > 1600 and sal < 3000;
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
SQL> select * from emp where empno < 7654 or empno > 7782;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
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
7934 MILLER CLERK 7782 82/01/23 1300 10
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
7566 JONES MANAGER 7839 81/04/02 2975 20
11 개의 행이 선택되었습니다.
SQL> select * from emp where ename between 'B%' and 'J%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7902 FORD ANALYST 7566 81/12/03 3000 20
SQL> select * from emp where substr(hiredate,1,2) <> '81';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7876 ADAMS CLERK 7788 87/05/23 1100 20
7934 MILLER CLERK 7782 82/01/23 1300 10
SQL> select * from emp where job IN ('MANAGER','SALESMAN');
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
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
7782 CLARK MANAGER 7839 81/06/09 2450 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7 개의 행이 선택되었습니다.
SQL> select ename, empno, deptno from emp where deptno <> 20 and deptno <> 30;
ENAME EMPNO DEPTNO
---------- ---------- ----------
CLARK 7782 10
KING 7839 10
MILLER 7934 10
SQL> select empno, ename, hiredate, deptno from emp where ename like 'S%';
EMPNO ENAME HIREDATE DEPTNO
---------- ---------- -------- ----------
7369 SMITH 80/12/17 20
7788 SCOTT 87/04/19 20
SQL> select * from emp where hiredate like '81%';
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
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
7782 CLARK MANAGER 7839 81/06/09 2450 10
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
10 개의 행이 선택되었습니다.
SQL> select * from emp where ename like '%S%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7566 JONES MANAGER 7839 81/04/02 2975 20
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7876 ADAMS CLERK 7788 87/05/23 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
SQL> select * from emp where ename like 'S%T';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 87/04/19 3000 20
SQL> select * from emp where ename like 'S___T';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 87/04/19 3000 20
SQL> select * from emp where ename like '_A%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7900 JAMES CLERK 7698 81/12/03 950 30
SQL> select * from emp where comm is null;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7566 JONES MANAGER 7839 81/04/02 2975 20
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
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
7934 MILLER CLERK 7782 82/01/23 1300 10
10 개의 행이 선택되었습니다.
SQL> select * from emp where comm is not null;
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
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
SQL> select ename, deptno, sal from emp where deptno = 30 and sal >= 1500;
ENAME DEPTNO SAL
---------- ---------- ----------
ALLEN 30 1600
BLAKE 30 2850
TURNER 30 1500
SQL> select empno, ename, deptno from emp where ename like 'K%' or deptno = 30;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7499 ALLEN 30
7521 WARD 30
7654 MARTIN 30
7698 BLAKE 30
7839 KING 10
7844 TURNER 30
7900 JAMES 30
7 개의 행이 선택되었습니다.
SQL> select * from emp where sal >= 1500 and deptno = 30 and job = 'MANAGER';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 81/05/01 2850 30
SQL> select * from emp where deptno = 30 order by empno asc;
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
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7900 JAMES CLERK 7698 81/12/03 950 30
6 개의 행이 선택되었습니다.
SQL> select * from emp order by sal asc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7900 JAMES CLERK 7698 81/12/03 950 30
7876 ADAMS CLERK 7788 87/05/23 1100 20
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7934 MILLER CLERK 7782 82/01/23 1300 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7902 FORD ANALYST 7566 81/12/03 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
14 개의 행이 선택되었습니다.
SQL> select * from emp order by deptno asc, sal desc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7839 KING PRESIDENT 81/11/17 5000 10
7782 CLARK MANAGER 7839 81/06/09 2450 10
7934 MILLER CLERK 7782 82/01/23 1300 10
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7902 FORD ANALYST 7566 81/12/03 3000 20
7566 JONES MANAGER 7839 81/04/02 2975 20
7876 ADAMS CLERK 7788 87/05/23 1100 20
7369 SMITH CLERK 7902 80/12/17 800 20
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7900 JAMES CLERK 7698 81/12/03 950 30
14 개의 행이 선택되었습니다.
SQL> select * from emp order by deptno desc, ename asc, sal desc;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7900 JAMES CLERK 7698 81/12/03 950 30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7876 ADAMS CLERK 7788 87/05/23 1100 20
7902 FORD ANALYST 7566 81/12/03 3000 20
7566 JONES MANAGER 7839 81/04/02 2975 20
7788 SCOTT ANALYST 7566 87/04/19 3000 20
7369 SMITH CLERK 7902 80/12/17 800 20
7782 CLARK MANAGER 7839 81/06/09 2450 10
7839 KING PRESIDENT 81/11/17 5000 10
7934 MILLER CLERK 7782 82/01/23 1300 10
14 개의 행이 선택되었습니다.
SQL> spool off
