728x90
SQL> select ename, job, sal, decode(job, 'MANAGER', sal*0.2 2 , 'ANAYST', sal*0.15, 3 'CLERK', sal*0.1, 4 sal*0.05) from emp; ENAME JOB SAL ---------- --------- ---------- DECODE(JOB,'MANAGER',SAL*0.2,'ANAYST',SAL*0.15,'CLERK',SAL*0.1,SAL*0.05) ------------------------------------------------------------------------ SMITH CLERK 800 80 ALLEN SALESMAN 1600 80 WARD SALESMAN 1250 62.5 ENAME JOB SAL ---------- --------- ---------- DECODE(JOB,'MANAGER',SAL*0.2,'ANAYST',SAL*0.15,'CLERK',SAL*0.1,SAL*0.05) ------------------------------------------------------------------------ JONES MANAGER 2975 595 MARTIN SALESMAN 1250 62.5 BLAKE MANAGER 2850 570 ENAME JOB SAL ---------- --------- ---------- DECODE(JOB,'MANAGER',SAL*0.2,'ANAYST',SAL*0.15,'CLERK',SAL*0.1,SAL*0.05) ------------------------------------------------------------------------ CLARK MANAGER 2450 490 SCOTT ANALYST 3000 150 KING PRESIDENT 5000 250 ENAME JOB SAL ---------- --------- ---------- DECODE(JOB,'MANAGER',SAL*0.2,'ANAYST',SAL*0.15,'CLERK',SAL*0.1,SAL*0.05) ------------------------------------------------------------------------ TURNER SALESMAN 1500 75 ADAMS CLERK 1100 110 JAMES CLERK 950 95 ENAME JOB SAL ---------- --------- ---------- DECODE(JOB,'MANAGER',SAL*0.2,'ANAYST',SAL*0.15,'CLERK',SAL*0.1,SAL*0.05) ------------------------------------------------------------------------ FORD ANALYST 3000 150 MILLER CLERK 1300 130 14 개의 행이 선택되었습니다. SQL> set pagesize 200 SQL> select ename, job, sal, decode(job, 'MANAGER', sal*0.2 2 , 'ANAYST', sal*0.15, 3 'CLERK', sal*0.1, 4 sal*0.05) from emp; ENAME JOB SAL ---------- --------- ---------- DECODE(JOB,'MANAGER',SAL*0.2,'ANAYST',SAL*0.15,'CLERK',SAL*0.1,SAL*0.05) ------------------------------------------------------------------------ SMITH CLERK 800 80 ALLEN SALESMAN 1600 80 WARD SALESMAN 1250 62.5 JONES MANAGER 2975 595 MARTIN SALESMAN 1250 62.5 BLAKE MANAGER 2850 570 CLARK MANAGER 2450 490 SCOTT ANALYST 3000 150 KING PRESIDENT 5000 250 TURNER SALESMAN 1500 75 ADAMS CLERK 1100 110 JAMES CLERK 950 95 FORD ANALYST 3000 150 MILLER CLERK 1300 130 14 개의 행이 선택되었습니다. SQL> select ename, case when deptno = 10 then 'ACCOUNTING' 2 when deptno = 20 then 3 'RESEARCH' when deptno = 30 then 'SALES' 4 else 'no' 5 end 부서명 6 from emp; ENAME 부서명 ---------- ---------- SMITH RESEARCH ALLEN SALES WARD SALES JONES RESEARCH MARTIN SALES BLAKE SALES CLARK ACCOUNTING SCOTT RESEARCH KING ACCOUNTING TURNER SALES ADAMS RESEARCH JAMES SALES FORD RESEARCH MILLER ACCOUNTING 14 개의 행이 선택되었습니다. SQL> select ename, job, sal, case when job = 'MANAGER' then sal*0.2 2 when job='ANALYST' then sal*0.15 3 when job='CLERK' then sal*0.1 4 else sal*0.05 5 end from emp; ENAME JOB SAL ---------- --------- ---------- CASEWHENJOB='MANAGER'THENSAL*0.2WHENJOB='ANALYST'THENSAL*0.15WHENJOB='CLERK'THEN -------------------------------------------------------------------------------- SMITH CLERK 800 80 ALLEN SALESMAN 1600 80 WARD SALESMAN 1250 62.5 JONES MANAGER 2975 595 MARTIN SALESMAN 1250 62.5 BLAKE MANAGER 2850 570 CLARK MANAGER 2450 490 SCOTT ANALYST 3000 450 KING PRESIDENT 5000 250 TURNER SALESMAN 1500 75 ADAMS CLERK 1100 110 JAMES CLERK 950 95 FORD ANALYST 3000 450 MILLER CLERK 1300 130 14 개의 행이 선택되었습니다. SQL> select ename, job, sal, case when sal>=3000 then sal*0.2 2 when sal<3000 and sal>=2000 then sal*0.15 3 when sal<2000 then sal*0.1 4 end from emp; ENAME JOB SAL ---------- --------- ---------- CASEWHENSAL>=3000THENSAL*0.2WHENSAL<3000ANDSAL>=2000THENSAL*0.15WHENSAL<2000THEN -------------------------------------------------------------------------------- SMITH CLERK 800 80 ALLEN SALESMAN 1600 160 WARD SALESMAN 1250 125 JONES MANAGER 2975 446.25 MARTIN SALESMAN 1250 125 BLAKE MANAGER 2850 427.5 CLARK MANAGER 2450 367.5 SCOTT ANALYST 3000 600 KING PRESIDENT 5000 1000 TURNER SALESMAN 1500 150 ADAMS CLERK 1100 110 JAMES CLERK 950 95 FORD ANALYST 3000 600 MILLER CLERK 1300 130 14 개의 행이 선택되었습니다. SQL> select ename, job, sal, case when sal>=3000 then sal*0.2 2 when sal>=2000 then sal*0.15 3 when sal<2000 then sal*0.1 4 end from emp; ENAME JOB SAL ---------- --------- ---------- CASEWHENSAL>=3000THENSAL*0.2WHENSAL>=2000THENSAL*0.15WHENSAL<2000THENSAL*0.1END ------------------------------------------------------------------------------- SMITH CLERK 800 80 ALLEN SALESMAN 1600 160 WARD SALESMAN 1250 125 JONES MANAGER 2975 446.25 MARTIN SALESMAN 1250 125 BLAKE MANAGER 2850 427.5 CLARK MANAGER 2450 367.5 SCOTT ANALYST 3000 600 KING PRESIDENT 5000 1000 TURNER SALESMAN 1500 150 ADAMS CLERK 1100 110 JAMES CLERK 950 95 FORD ANALYST 3000 600 MILLER CLERK 1300 130 14 개의 행이 선택되었습니다. SQL> select ename, comm 2 from emp; ENAME COMM ---------- ---------- SMITH ALLEN 300 WARD 500 JONES MARTIN 1400 BLAKE CLARK SCOTT KING TURNER 0 ADAMS JAMES FORD MILLER 14 개의 행이 선택되었습니다. SQL> select ename, comm, mgr 2 from emp; ENAME COMM MGR ---------- ---------- ---------- SMITH 7902 ALLEN 300 7698 WARD 500 7698 JONES 7839 MARTIN 1400 7698 BLAKE 7839 CLARK 7839 SCOTT 7566 KING TURNER 0 7698 ADAMS 7788 JAMES 7698 FORD 7566 MILLER 7782 14 개의 행이 선택되었습니다. SQL> select ename, nvl(comm, 0), nvl(mgr, 0) 2 from emp; ENAME NVL(COMM,0) NVL(MGR,0) ---------- ----------- ---------- SMITH 0 7902 ALLEN 300 7698 WARD 500 7698 JONES 0 7839 MARTIN 1400 7698 BLAKE 0 7839 CLARK 0 7839 SCOTT 0 7566 KING 0 0 TURNER 0 7698 ADAMS 0 7788 JAMES 0 7698 FORD 0 7566 MILLER 0 7782 14 개의 행이 선택되었습니다. SQL> select ename, nvl(comm, 0), nvl(mgr, 79231) 2 from emp; ENAME NVL(COMM,0) NVL(MGR,79231) ---------- ----------- -------------- SMITH 0 7902 ALLEN 300 7698 WARD 500 7698 JONES 0 7839 MARTIN 1400 7698 BLAKE 0 7839 CLARK 0 7839 SCOTT 0 7566 KING 0 79231 TURNER 0 7698 ADAMS 0 7788 JAMES 0 7698 FORD 0 7566 MILLER 0 7782 14 개의 행이 선택되었습니다. SQL> select ename, nvl(comm, 0), nvl(to_char(mgr), 'CEO') from emp; ENAME NVL(COMM,0) NVL(TO_CHAR(MGR),'CEO') ---------- ----------- ---------------------------------------- SMITH 0 7902 ALLEN 300 7698 WARD 500 7698 JONES 0 7839 MARTIN 1400 7698 BLAKE 0 7839 CLARK 0 7839 SCOTT 0 7566 KING 0 CEO TURNER 0 7698 ADAMS 0 7788 JAMES 0 7698 FORD 0 7566 MILLER 0 7782 14 개의 행이 선택되었습니다. SQL> select ename, sal, nvl2(comm, comm*1.1, sal) bonus from emp; ENAME SAL BONUS ---------- ---------- ---------- SMITH 800 800 ALLEN 1600 330 WARD 1250 550 JONES 2975 2975 MARTIN 1250 1540 BLAKE 2850 2850 CLARK 2450 2450 SCOTT 3000 3000 KING 5000 5000 TURNER 1500 0 ADAMS 1100 1100 JAMES 950 950 FORD 3000 3000 MILLER 1300 1300 14 개의 행이 선택되었습니다. SQL> select ename, sal, nvl2(comm, comm*1.1, sal*0.01) bonus from emp; ENAME SAL BONUS ---------- ---------- ---------- SMITH 800 8 ALLEN 1600 330 WARD 1250 550 JONES 2975 29.75 MARTIN 1250 1540 BLAKE 2850 28.5 CLARK 2450 24.5 SCOTT 3000 30 KING 5000 50 TURNER 1500 0 ADAMS 1100 11 JAMES 950 9.5 FORD 3000 30 MILLER 1300 13 14 개의 행이 선택되었습니다. SQL> select ename, sal, nvl2(comm, comm*1.1, trunc(sal*0.01)) bonus from emp; ENAME SAL BONUS ---------- ---------- ---------- SMITH 800 8 ALLEN 1600 330 WARD 1250 550 JONES 2975 29 MARTIN 1250 1540 BLAKE 2850 28 CLARK 2450 24 SCOTT 3000 30 KING 5000 50 TURNER 1500 0 ADAMS 1100 11 JAMES 950 9 FORD 3000 30 MILLER 1300 13 14 개의 행이 선택되었습니다. SQL> select ename, job from emp 2 where deptno = (select deptno from dept where dname='ACCOUNTING'); ENAME JOB ---------- --------- CLARK MANAGER KING PRESIDENT MILLER CLERK SQL> select ename, job from emp 2 where deptno in (select deptno from dept where dname='ACCOUNTING' or dname = 'RESEARCH'); ENAME JOB ---------- --------- SMITH CLERK JONES MANAGER CLARK MANAGER SCOTT ANALYST KING PRESIDENT ADAMS CLERK FORD ANALYST MILLER CLERK 8 개의 행이 선택되었습니다. SQL> select deptno from emp 2 intersect select deptno from dept; DEPTNO ---------- 10 20 30 SQL> select deptno from emp 2 union select deptno from dept; DEPTNO ---------- 10 20 30 40 SQL> select deptno from emp 2 minus select deptno from dept; 선택된 레코드가 없습니다. SQL> select deptno from emp 2 intersect select deptno from dept; DEPTNO ---------- 10 20 30 SQL> select deptno from emp 2 union all select deptno from dept; DEPTNO ---------- 20 30 30 20 30 30 10 20 10 30 20 30 20 10 10 20 30 40 18 개의 행이 선택되었습니다. SQL> select dname, loc from dept; DNAME LOC -------------- ------------- ACCOUNTING NEW YORK RESEARCH DALLAS SALES CHICAGO OPERATIONS BOSTON SQL> select dname, loc from dept 2 where deptno in (select deptno from emp); DNAME LOC -------------- ------------- RESEARCH DALLAS SALES CHICAGO ACCOUNTING NEW YORK SQL> select dname, loc from dept d 2 where exists (select deptno from emp where deptno=d.deptno); DNAME LOC -------------- ------------- RESEARCH DALLAS SALES CHICAGO ACCOUNTING NEW YORK SQL> select dname, loc from dept d 2 where exists (select deptno from emp where deptno=d.deptno); DNAME LOC -------------- ------------- RESEARCH DALLAS SALES CHICAGO ACCOUNTING NEW YORK SQL> select dname, loc from dept d 2 where exists (select * from emp where deptno=d.deptno); DNAME LOC -------------- ------------- RESEARCH DALLAS SALES CHICAGO ACCOUNTING NEW YORK SQL> select dname, loc from dept d 2 where not exists (select * from emp where deptno=d.deptno); DNAME LOC -------------- ------------- OPERATIONS BOSTON SQL> select count(*) from emp where deptno = (select deptno from emp where ename = 'SCOTT'); COUNT(*) ---------- 5 SQL> select ename from emp where deptno = (select deptno from emp where ename = 'SCOTT'); ENAME ---------- SMITH JONES SCOTT ADAMS FORD SQL> select ename, job from emp where deptno = (select deptno from emp where ename = 'SCOTT'); ENAME JOB ---------- --------- SMITH CLERK JONES MANAGER SCOTT ANALYST ADAMS CLERK FORD ANALYST SQL> select ename, job from emp where (deptno, job) = (select deptno, job from emp where ename = 'SCOTT'); ENAME JOB ---------- --------- SCOTT ANALYST FORD ANALYST SQL> select ename, job from emp where job = (select job from emp where ename = 'SCOTT'); ENAME JOB ---------- --------- SCOTT ANALYST FORD ANALYST SQL> select ename, job from emp where (deptno, job) = (select deptno, job from emp where ename = 'SCOTT'); ENAME JOB ---------- --------- SCOTT ANALYST FORD ANALYST SQL> select ename, job from emp where (deptno, job) = (select deptno, job from emp where ename = 'SCOTT') and ename != 'SCOTT'; ENAME JOB ---------- --------- FORD ANALYST SQL> select ename, job from emp e where exists (select deptno, job from emp where ename = 'SCOTT' and job = e.job and deptno=e.deptno) and ename != 'SCOTT'; ENAME JOB ---------- --------- FORD ANALYST SQL> spool off |
SQL> select ename, job, deptno
2 from emp
3 where exists ( select deptno from dept
4 where dname in('ACCOUNTING', 'RESEARCH')
5 and deptno=emp.deptno);
ENAME JOB DEPTNO
---------- --------- ----------
MILLER CLERK 10
KING PRESIDENT 10
CLARK MANAGER 10
FORD ANALYST 20
ADAMS CLERK 20
SCOTT ANALYST 20
JONES MANAGER 20
SMITH CLERK 20
8 개의 행이 선택되었습니다.
SQL> select ename, sal from emp order by sal desc;
ENAME SAL
---------- ----------
KING 5000
FORD 3000
SCOTT 3000
JONES 2975
BLAKE 2850
CLARK 2450
ALLEN 1600
TURNER 1500
MILLER 1300
WARD 1250
MARTIN 1250
ADAMS 1100
JAMES 950
SMITH 800
14 개의 행이 선택되었습니다.
SQL> select ename, sal from emp where sal >= 2850 order by sal desc;
ENAME SAL
---------- ----------
KING 5000
SCOTT 3000
FORD 3000
JONES 2975
BLAKE 2850
SQL> select ename, sal from emp where rownum <=5 order by sal desc;
ENAME SAL
---------- ----------
JONES 2975
ALLEN 1600
MARTIN 1250
WARD 1250
SMITH 800
SQL> select ename, sal from (select ename, sal from emp order by sal desc)
2 where rownum <= 5;
ENAME SAL
---------- ----------
KING 5000
SCOTT 3000
FORD 3000
JONES 2975
BLAKE 2850
728x90