※ DML문장(Inser/Update/Delete)을 DBMS가 처리하는 방식
DML문장 입력들 → Rollback of Commit

※ Transaction 작업
개념 여러가지 소작업들로 이루어져 있고 일괄 처리되는 작업
All or Nothing (ex. 계좌이체)

create table reporttable
(
ord_id number(7) constraints s_item_ordid_prodid_uk unique not null,
item_id number(7) constraints s_item_ordid_itemid_pk primary key not null,
product_id number(7) not null,
price number(11, 2),
quantity number(9),
quantity_shipped number(9)
);

SQL> -- 부서 테이블과 사원 테이블에서 사번, 사원명, 부서코드, 부서명을 검색하시오. 단, 출력 시, 사원명을 기준으로 오름차순으로 정렬하시오.
SQL> select empno, ename, emp.deptno, dname from emp, dept where emp.deptno = dept.deptno order by ename;

EMPNO ENAME DEPTNO DNAME
---------- ---------- ---------- --------------
7876 ADAMS 20 RESEARCH
7499 ALLEN 30 SALES
7698 BLAKE 30 SALES
7782 CLARK 10 ACCOUNTING
7902 FORD 20 RESEARCH
7900 JAMES 30 SALES
7566 JONES 20 RESEARCH
7839 KING 10 ACCOUNTING
7654 MARTIN 30 SALES
7934 MILLER 10 ACCOUNTING
7788 SCOTT 20 RESEARCH
7369 SMITH 20 RESEARCH
7844 TURNER 30 SALES
7521 WARD 30 SALES

14 개의 행이 선택되었습니다.

SQL> -- 부서 테이블과 사원 테이블에서 사번, 사원명, 급여, 부서명을 검색하시오. 단, 급여가 2000이상인 사원에 대하여 급여를 기분으로 내림차순으로 정렬하시오.
SQL> select empno, ename, sal, dname from emp, dept where emp.deptno = dept.deptno and sal >= 2000 order by sal asc;

EMPNO ENAME SAL DNAME
---------- ---------- ---------- --------------
7782 CLARK 2450 ACCOUNTING
7698 BLAKE 2850 SALES
7566 JONES 2975 RESEARCH
7788 SCOTT 3000 RESEARCH
7902 FORD 3000 RESEARCH
7839 KING 5000 ACCOUNTING

6 개의 행이 선택되었습니다.

SQL> -- 부서 테이블과 사원 테이블에서 사번, 사원명, 업무, 급여, 부서명을 검색하시오. 단 업무가 MANAGER이며 급여가 2500이상인 사원에 대하여 사번을 기준으로 오름차순으로 정렬하시오.
SQL> select empno, ename, job, sal, dept.dname from emp, dept where upper(job) = upper('manager') and sal >= 2500 order by empno;

EMPNO ENAME JOB SAL DNAME
---------- ---------- --------- ---------- --------------
7566 JONES MANAGER 2975 ACCOUNTING
7566 JONES MANAGER 2975 RESEARCH
7566 JONES MANAGER 2975 OPERATIONS
7566 JONES MANAGER 2975 SALES
7698 BLAKE MANAGER 2850 ACCOUNTING
7698 BLAKE MANAGER 2850 RESEARCH
7698 BLAKE MANAGER 2850 OPERATIONS
7698 BLAKE MANAGER 2850 SALES

8 개의 행이 선택되었습니다.

SQL> SPOOL OFF

SQL> -- 사원 테이블의 사원명에서 2번째 문자부터 3개의 문자를 추출하시오
SQL> select substr(ename, 2, 3) from emp;

SUBSTR
------
MIT
LLE
ARD
ONE
ART
LAK
LAR
COT
ING
URN
DAM
AME
ORD
ILL

14 개의 행이 선택되었습니다.

SQL> -- 사원 테이블에서 입사일이 12월인 사원의 사번, 사원명, 입사일을 검색하시오
SQL> select empno, ename, hiredate from emp where to_char(hiredate,'mm') = '12';

EMPNO ENAME HIREDATE
---------- ---------- --------
7369 SMITH 80/12/17
7900 JAMES 81/12/03
7902 FORD 81/12/03

SQL> -- 다음과 같은 결과를 검색할 수 있는 SQL 문장을 작성하시오
SQL> select empno, ename, lpad(sal,10,'*') as "급여" from emp;

EMPNO ENAME 급여
---------- ---------- --------------------
7369 SMITH *******800
7499 ALLEN ******1600
7521 WARD ******1250
7566 JONES ******2975
7654 MARTIN ******1250
7698 BLAKE ******2850
7782 CLARK ******2450
7788 SCOTT ******3000
7839 KING ******5000
7844 TURNER ******1500
7876 ADAMS ******1100
7900 JAMES *******950
7902 FORD ******3000
7934 MILLER ******1300

14 개의 행이 선택되었습니다.

SQL> -- 다음과 같은 결과를 검색할 수 있는 SQL문장을 작성하시오
SQL> select empno, ename, to_char(hiredate,'yyyy-mm-dd') as "입사일" from emp;

EMPNO ENAME 입사일
---------- ---------- ----------
7369 SMITH 1980-12-17
7499 ALLEN 1981-02-20
7521 WARD 1981-02-22
7566 JONES 1981-04-02
7654 MARTIN 1981-09-28
7698 BLAKE 1981-05-01
7782 CLARK 1981-06-09
7788 SCOTT 1987-04-19
7839 KING 1981-11-17
7844 TURNER 1981-09-08
7876 ADAMS 1987-05-23
7900 JAMES 1981-12-03
7902 FORD 1981-12-03
7934 MILLER 1982-01-23

14 개의 행이 선택되었습니다.


SQL> -- 사원테이블 급여에 따라 사번, 이름, 급여, 등급을 검색하는 SQL문장을 작성하시오.
SQL> select empno, ename,
SQL> case when sal <= 1000 then 'E'
SQL> when sal <= 2000 then 'D'
SQL> when sal <= 3000 then 'C'
SQL> when sal <= 4000 then 'B'
SQL> else 'A' end from emp;

EMPNO ENAME C
---------- ---------- -
7369 SMITH E
7499 ALLEN D
7521 WARD D
7566 JONES C
7654 MARTIN D
7698 BLAKE C
7782 CLARK C
7788 SCOTT C
7839 KING A
7844 TURNER D
7876 ADAMS D
7900 JAMES E
7902 FORD C
7934 MILLER D

14 개의 행이 선택되었습니다.

SQL> spool off

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