3일동안 배운 내용을 올립니다.
[ Part1 - 오라클 설치 및 접근법 ]

1. 오라클 설치 (10g)
   (1) 디렉토리 이름(경로)이 한글이면X
       1) oracle 설치파일이 존재하는 경로에 한글X
       2) oracle 설치경로에 한글X

   (2) 디렉토리에 공백이 있으면 X
       1) oracle 설치파일이 존재하는 경로에 공백X
       2) oracle 설치경로에 공백X

   (3) 설치시에 에러 해결 방법
       1) OS부터 밀고 다시 설치
       2) oracle를 다시 설치
          <1> 제어판 -> 프로그램 추가제거 -> 관련 프로그램 제거
      <2> 관리도구 -> 서비스 -> 오라클 관련 서비스 정지
      <3> oracle설치 디렉토리를 삭제
          ( 만약, 삭제가 안되면 '안전모드'에서 삭제 )
      <4> 시작 -> 모든프로그램 -> oracle 관련 메뉴 삭제
      <5> 실행 -> regedit 에서 oracle 관련 파일을 모두 제거
      <6> 다시 설치

2. 오라클 접근법
   (1) sqlplus 이용법 ( local관리 )
       1)oracle Application 이용법
          ( oracle -> 응용프로그램개발 -> SQL Plus )
       2)도스창 이용법
          ( 실행 -> sqlplus scott/tiger )

   (2) isqlplus 이용법 ( WEB-remote관리)
       1) oracle의 Apache Web Server 작동
       2) 접근 : http://ip:5560/isqlplus
          ex) http://192.168.0.45:5560/isqlplus

   (3) utility 이용법 ( local / remote 관리 )
       1) sqlgate 
       2) toad
       3) orange
         ......

   cf1) scott 계정 unlock 방법
     SQL> conn system/pwd;
     SQL> alter user scott identified by tiger account unlock;

   cf2) 오라클 관리자 모드 ( SYSTEM/SYS )
     Enterprise Manager Console ( EM 법 )
     1) http://khs-com:1158/em
     2) http://192.168.0.45:1158/em
    


[ Part2 - Oracle의 개요 ]
1. DBMS (DataBase Management System)의 개념
    (1) DB(DataBase)
        - 지속적으로 유지관리해야 할 데이터의 집합
    (2) DBMS
        - DB를 편리하게 관리하고, 효율적으로 저장하고
      검색할 수 있는 환경을 제공해주는 시스템 소프트웨어
      를 의미 ( ex: oracle, ms-sql, mysql, db2, ... )

2. DBMS 역사
    (1) 1960년 : File System
    (2) 1970년 : Network-DBMS
    (3) 1980년 : 관계형-DBMS
    (4) 1990년 : 관계형-DBMS, 객체관계형-DBMS
    (5) 2000년 : 관계형/객체관계형/객체지향-DBMS
   
    ex) Oracle 6.X, 7.X -> 관계형(RDBMS)
        Oracle 8.X, 9.X, 10.X -> 객체관계형(OO-RDBMS)

    cf) OODB
       
3. 데이터베이스 설계 단계
   실세계(업무분석) -> 개념적설계 -> 논리적설계 -> 물리적설계
                                     ( ERD )       (DBMS결정)

4. SQL( Structured Query Language )
   데이터를 Access 하기 위해 DBMS와 통신하는 언어

5. 기본 사용자 계정
   (1) SYS
       오라클 super사용자 ID이며, 데이터베이스에서 발생하는
       모든 문제를 처리할 수 있는 권한
   (2) SYSTEM
       SYS과 같은데, 차이는 데이터베이스를 생성할 수 있는
       권한이 없음
   (3) SCOTT
       처음 오라클을 사용하는 user들을 위한 SAMPLE계정이며,
       일반적인 프로그램을 작성할 때 사용되는 계정
       (for Developer )
   (4) HR ( 인적관리계정 )
       SAMPLE계정

6. 주요 용어
   (1) TABLE
       관계형 DBMS에서 기본 데이터저장구조로써 Entity(실체)의
       집합 저장소 (ex: DETP, EMP, SALGRADE, .... )

   (2) ROW
       테이블의 행( 하나의 유효 데이터 )
       (ex: 20 RESEARCH  DALLAS )

   (3) COLUMN
       테이블의 열명
       (ex: DEPTNO DNAME LOC )

   (4) PRIMARY-KEY
       테이블에서 각 ROW를 유일하게 구분하는
       COLUMN에 부여하는 제약조건

   (5) FOREIGN-KEY
       다른 테이블의 COLUMN값을 참조하는
       테이블에 부여하는 제약조건

   (6) FIELD
       테이블의 ROW와 COLUMN이 교차하는 데이터
       (ex: dept테이블의 'RESEARCH'이란 값 )
      
   (7) NULL
       데이터가 존재하지 않는 FIELD값

       cf) DETP : 부서테이블 (부모 테이블 )
           EMP  : 자식 테이블 (자식 테이블 )

7. SQL의 구분
   (1) DQL ( Data Query Language )
       -> 테이블의 데이터를 조회할 때 사용
       (ex: select ~ )
   (2) DML ( Data Manipulation Language )
       -> 테이블의 데이터를 입력, 수정, 삭제할 때 사용
       (ex: insert~, update~, delete~ )
   (3) DDL ( Data Definition Language )
       -> 테이블등의 객체를 생성, 변경, 삭제할 때 사용
       (ex: create~, alter~, drop~ )
   (4) TCL ( Transaction Control Language )
       -> 테이블내의 DML문을 DB에 저장 or 취소할 때 사용
       (ex: commit~, rollback~, savepoint )
   (5) DCL ( Data Control Language )
       -> DB사용자에게 권한을 부여 or 취소할 때 사용
       (ex: grant~, revoke~ )

8. PL-SQL
   제어문(조건문, 반복문)이 들어있는 SQL로써, 오라클 DBMS에서
   지원하는 확장된 SQL
   (1) Stored Procedure
       -> 일련의 작업처리순서를 정의해 놓은 것으로
        미리 DBMS에 컴파일되어져있어 프로그램 or 사용자로부터
    호출되면 실행되는 일종의 작업처리순서 로직(기능)

   (2) Anonymous Procedure
       -> Stored Procedure 와 차이점은 DBMS에 자동으로 포함되어
        있는 이름이 없는 Procedure

   (3) Stored Function
       -> 저장된 기능덩어리로써 Procedure와 차이점은 수행되고
        나서 return 값이 존재함
   
   (4) Trigger
       -> 일련의 작업처리순서를 정의해 놓은 것으로 미리 DBMS에
        컴파일되어져있어, 어떤 조건에 만족되어지는 상황(DML수행)
    이 발생되면 자동으로 호출되어 작동되는 로직(기능)

   (5) Package
       -> Procedure/Function/Trigger 등의 기능별 묶음
  

[ Part 3 - SQL ]
1. DQL
  (1) 기본 구조
     SQL> select * from TAB;
     SQL> select * from DETP;
     SQL> select * from EMP;

  (2) DISTINCT / ALL
     SQL> select DISTINCT JOB from EMP;
     SQL> select ALL JOB from EMP;
       ( SQL> select JOB from EMP; )

  (3) ORDER BY
     SQL> select EMPNO, SAL from EMP;
     SQL> select EMPNO, SAL from EMP order by SAL;
     SQL> select EMPNO, SAL from EMP order by SAL asc;
     SQL> select EMPNO, SAL from EMP order by SAL desc;

     //JOB desc 정렬하고, SAL desc 정렬하라
     //JOB을 알파벳Z부터 정렬하고, 그 정렬결과를
       다시 봉급이 많을 순서대로 출력하라
     SQL> select JOB,SAL from EMP order by JOB desc, SAL desc;

  (4) ALIAS
     SQL> select JOB "직업", SAL "봉급" from EMP;
     SQL> select JOB 직업, SAL "봉급" from EMP;
     SQL> select JOB 직업 , SAL "봉급 !@#$%^" from EMP;
     SQL> select JOB as 직업 , SAL as "봉급 !@#$%^" from EMP;
     SQL> select JOB as 직업, SAL as "1234" from EMP;
     SQL> select JOB as 1234, SAL as "봉급" from EMP;(X)
     SQL> select JOB as !@#$^%, SAL as "1234" from EMP;(X)

     cf) 숫자 or 특수문자 ALIAS는 반드시 ""안에 넣어줘야한다.

  (5) WHERE
     SQL> select EMPNO, DEPTNO from EMP where DEPTNO=20;
     SQL> select EMPNO, SAL from EMP where DEPTNO=30;
     //30번 부서 사람의 사번과 봉급을 봉급이 많은 순서대로 출력
     SQL> select EMPNO, SAL from EMP where DEPTNO=30 order by SAL desc;
     //직업이 'SALESMAN'인 사람의 직업과 이름을 출력(이름의 알파펫 순서)
     SQL> select JOB, ENAME from EMP where JOB='SALESMAN' order by ENAME;
     //입사일 '81/12/03'사원의 사번과 이름을 사번의 내림차순으로 출력
     SQL> select EMPNO, ENAME from EMP where HIREDATE='81/12/03'
         order by EMPNO desc;
     //입사일이 '81/12/03'이후인 사원의 사번과 이름을 사번의
         내림차순으로 출력
     SQL> select EMPNO, ENAME, HIREDATE from EMP
        where HIREDATE>='81/12/03' order by EMPNO desc;
     //사번이 7900 인 사원의 이름과 사번을 출력
     SQL> select EMPNO, ENAME from EMP where EMPNO=7900;
 
   (6) 연산자 ( Operator )
     1) 산술 연산자 ( + , -, * , / )
        //부서번호가 30번 부서사람들의 봉급을 10%인상 이름/봉급 출력
    SQL> select ENAME, SAL*1.1 from EMP where DEPTNO=30;
        //30번 부서의 모든 사원의 '연말보너스'를 높은 순으로 출력
    ( 단, 연말보너스는 급여의 2배와 comm 의 1/2의 합이다. )
        SQL> select EMPNO, SAL/12*2+NVL(COMM,0)/2 "연말보너스"
      from EMP where DEPTNO=30 order by "연말보너스" desc;

     2) 비교 연산자 ( =, !=, >, >=, <, <= )
        SQL> select ENAME, SAL from EMP where SAL=950;
    //연봉이 3000 이상인 사원의 이름과 연봉을 출력
        SQL> select ENAME, SAL from EMP where SAL>=3000;
        //부서번호가 30번이 아닌 사원의 이름과 연봉을 출력
    SQL> select ENAME, SAL from EMP where DEPTNO != 30;  

     3) 논리 연산자 ( AND, OR, NOT )
        //20번 부서이고 연봉인 3000 이상인 사람을 부서번호/이름/연봉 출력
        SQL> select DEPTNO, ENAME, SAL from EMP where DEPTNO=20 AND SAL>=3000;
    //직업이 'SALESMAN'이고 부서번호가 30인 사원의 이름/직업/부서번호 출력
    SQL> select ENAME, JOB, DEPTNO from EMP where DEPTNO=30 and JOB='SALESMAN';
    //연봉이 1000을 미만 또는 4000이상의 사원의 이름과 연봉을 출력
    SQL> select ENAME, SAL from EMP where SAL<1000 or SAL>=4000;
    //연봉이 1000이상 4000미만의 사원의 이름과 연봉을 출력
    SQL> select ENAME, SAL from EMP where NOT (SAL<1000 or SAL>=4000);
   
     
     4) SQL 연산자
       ( IN, ANY, ALL, BETWEEN, LIKE, IS NULL, IS NOT NULL, EXISTS,..)
       //부서번호가 10이나 20부서의 사원 이름과 부서번호를 출력
       SQL> select ENAME, DEPTNO from EMP where DEPTNO IN(10, 20, 100);
       SQL> select ENAME, DEPTNO from EMP where DEPTNO=ANY(10, 20, 100);
       //30번부서 사원중 가장높은 연봉을 받는 사람보다 더 많은
        연봉의 사원이름, 연봉 출력
       SQL> select ENAME, SAL from EMP
          where SAL>ALL(select SAL from EMP where DEPTNO=30);
       //연봉이 1000 이상 3000 미만인 사원이름과 연봉 출력
       SQL> select ENAME, SAL from EMP where SAL BETWEEN 1000 AND 3000;
       //'FORD'와 'SCOTT' 사이의 이름을 알파벳순서로 출력
       SQL> select ENAME from EMP where ENAME BETWEEN 'FORD' AND 'SCOTT'
         order by ENAME;
       //이름이 J로 시작되는 사원이름을 출력
       SQL> select ENAME from EMP where ENAME LIKE 'J%';
       //이름에 T가 들어있는 사원이름을 출력
       SQL> select ENAME from EMP where ENAME LIKE '%T%';
       //세번째 글자가 A인 사원이름을 출력
       SQL> select ENAME from EMP where ENAME LIKE '__A%';
       //연봉에 8자가 들어있는 사원의 이름과 연봉을 출력
       SQL> select ENAME, SAL from EMP where SAL LIKE '%8%';
       //연봉의 십의자리가 5인 사원의 이름과 연봉을 출력
       SQL> select ENAME, SAL from EMP where SAL LIKE '%5_';
       //커미션이 NULL인 사원의 이름과 커미션을 출력
       SQL> select ENAME, COMM from EMP where COMM IS NULL;
       //커미션이 NULL이 아닌 사원의 이름과 커미션을 출력
       SQL> select ENAME, COMM from EMP where COMM IS NOT NULL;

       //이름이 'FORD'라는 사원이 있으면 사원 이름과 커미션 출력
       SQL> select ENAME, COMM from EMP
        where EXISTS(select ENAME from EMP where ENAME='FORD');
       //이름이 'AAA'라는 사원이 있으면 사원 이름과 커미션 출력
       SQL> select ENAME, COMM from EMP
        where EXISTS(select ENAME from EMP where ENAME='AAA');

      
     5) 결합 연산자 ( || )
       //XXX의 연봉은 YYY입니다.
       SQL> select ENAME||'의 연봉은'||SAL||'입니다' from EMP;
       SQL> select ENAME||11111||SAL||222222 from EMP;

     6) 집합 연산자 ( UNION(합), UNION ALL, INTERSECT(교), MINUS(차) )
       <1> UNION
        SQL> select EMPNO, ENAME from EMP
        UNION
        select DEPTNO, DNAME from DEPT;

       <2> UNION ALL
    SQL> select EMPNO, ENAME from EMP
      2  UNION
      3  select EMPNO, ENAME from EMP;

    SQL> select EMPNO, ENAME from EMP
      2  UNION ALL
      3  select EMPNO, ENAME from EMP;

       <3> INTERSECT
        SQL> select EMPNO, ENAME from EMP
      2  INTERSECT
      3  select DEPTNO, DNAME from DEPT;
        SQL> select EMPNO, ENAME from EMP
      2  INTERSECT
      3  select EMPNO, ENAME from EMP where DEPTNO=20;
     
       <4> MINUS
        SQL> select EMPNO, ENAME from EMP
      2  MINUS
      3  select EMPNO, ENAME from EMP where DEPTNO=20;



   (7) 연산자 우선순위
      1) 1순위 : 비교연산자, SQL연산자, 산술연산자
      2) 2순위 : NOT
      3) 3순위 : AND
      4) 4순위 : OR
      5) 5순위 : 결합연산자, 집합연산자

      SQL> select ENAME, SAL from EMP
       2  where NOT SAL>1000 AND SAL<3000;
      SQL> select ENAME, SAL from EMP
       2  where NOT (SAL>1000 AND SAL<3000);

   (8) SQL문장의 실행방법
      1) 문장의 끝에 ;을 붙여주고 enter!
      2) 문장의 다음 라인에 /를 붙여주고 enter!
        SQL> select ENAME, SAL from EMP
     2  /
      3) 가장 최근(직전)의 버퍼에 담겨있는 SQL문장을 실행 
        SQL> run
      4) 버퍼의 SQL 보기 / 편집
        SQL> edit
  
        <1> 메모리 버퍼에 메모장에 SQL보기
    <2> 편집
    <3> 메모장을 저장(메모리버퍼에 쓴다)하고 종료
    <4> run


/////////////////// 문제 1 ////////////////////////


2. SQLPlus
   (1) SQL*Plus 란 ?
       오라클에서만 지원되는 SQL의 추가 명령어

   (2) SQL과 SQL*Plus 차이
      1) SQL
        <1> DB내의 테이블에 데이터를 입력/수정/삭제/조회
        <2> 버퍼에 저장됨(edit)
    <3> 여러 라인에 걸쳐서 작성 가능
    <4> 실행시 ; 또는 / 로 실행됨
    <5> 명령어의 요약할 수 없음
     
      2) SQL*Plus
        <1> SQL명령어를 DBMS에 전송
    <2> oracle 에서만 지원
    <3> DB의 데이터에 대한 직접적인 조작이 불가능
    <4> 버퍼에 저장되지 않음
    <5> 반드시 하나의 라인만 사용해야 함
    <6> 실행시 ; 이 필요없음
    <7> 명령어를 요약해서 작성할 수 있음
       ex) SQL> describe EMP
           SQL> desc EMP

   (3) SQL*Plus 종류
      1) 파일명령어
         -> 실행된 SQL을 편집/저장/실행할 때 사용하는 명령어
    <1> SAVE
        예)
            SQL> save E:\SOO\oracle\temp\splplus\scott_emp.sql
            SQL> save E:\SOO\oracle\temp\splplus\scott_emp2.sql

    <2> GET
        예)
            SQL> get E:\SOO\oracle\temp\splplus\scott_emp.sql

    <3> SPOOL ~ SPOOL OFF
            SQL> SPOOL E:\SOO\oracle\temp\splplus\scott_spool.txt
            SQL> select * from DEPT;
            SQL> select ENAME from EMP where SAL>=3000;
            SQL> SPOOL OFF

      2) (버퍼) 편집 명령어
          -> SQL문을 수정/삭제할 때 사용하는 명령어
    <1> i
        SQL> select * from DEPT;
            SQL> i
         2  where DEPTNO=30;

    <2> L
        SQL> L
             1  select * from DEPT
         2* where DEPTNO=30
    <3> N
        SQL> 2
         2* where DEPTNO=30

    <4> C
        SQL> C/30/20
         2* where DEPTNO=20
            SQL> /

    <5> DEL N( 임의의 숫자 - line번호 ) 
        SQL> DEL 2
        SQL> L
         1* select * from DEPT
        SQL> /

    <6> CLEAR BUFFER
       SQL> clear buffer
          buffer 소거되었습니다.
       SQL> L
          SP2-0223: SQL 버퍼에 줄이 없습니다.
       SQL> /
          SP2-0103: SQL 버퍼에 실행할 사항이 없습니다.

      3) 실행 명령어( ***** )
        -> SQL문을 실행할 때 사용하는 명령어
        <1> START
       SQL> start E:\SOO\oracle\temp\splplus\scott_emp.sql

    <2> @
       SQL> @E:\SOO\oracle\temp\splplus\scott_emp2.sql

    <3> RUN
       SQL> run

    <4> /
       SQL> /

         cf) run과 /는 특정 SQL파일을 수행하는 기능이 없고,
         오로지 버퍼내용만을 수행한다.

      4) 환경 명령어
         -> SQL문의 결과를 출력시, 출력되는 내용을 다르게 할 때 사용
    <1> SET FEEDBACK ON 과 SET FEEDBACK OFF
       SQL> SET FEEDBACK ON
       SQL> select * from DEPT;
       SQL> SET FEEDBACK OFF
       SQL> select * from DEPT;

    <2> SET HEADING ON 과 SET HEADING OFF
           SQL> SET HEADING ON
       SQL> select * from DEPT;
           SQL> SET HEADING OFF
           SQL> select * from DEPT;

      5) 형식 명령어
        -> SQL문의 결과를 보고서 형태로 출력할 때 사용


3. 함수 ( FUNCTION )
   (1) 함수(function)란? ( 처리/반환 )
       어떠한 일을 수행하는 기능으로써 주어진 인수(argument)를 재료로
       처리를 하여 그 결과를 반환하는 일을 수행

   (2) 함수 기능의 구체적 표현
      1) Data에 대한 계산
      2) Data를 다른 형태로 변환
      3) Data를 결과를 출력

   (3) 함수의 종류
      1) 단일행 함수
         -> 하나의 행(ROW)당, 하나의 결과값을 반환하는 함수

      2) 복수행 함수
         -> 여러개의 행당, 하나의 결과값을 반환하는 함수

   (4) 단일행 함수
      1) 문자함수
        <1> CHR(아스키코드)
          //65와 매핑되어있는 문자를 출력
          SQL> select CHR(65) from DUAL;

    <2> CONCAT(컬럼명, '붙일문자열')
          SQL> select ENAME||' is a '|| JOB from EMP;
          SQL> select CONCAT(ENAME, ' is a '), JOB from EMP;

    <3> INITCAP('문자열')
      SQL> select 'the lion' from dual;
          SQL> select INITCAP('the lion') from dual;

    <4> LOWER('문자열')
      SQL> select LOWER('MY NAME IS KHS') from DUAL;

    <5> LPAD('문자열1', 자리수, '문자열2')
      SQL> select LPAD('khs', 13, '*#') from DUAL;
          SQL> select LPAD('khs', 13, ' ') from DUAL;

    <6> RPAD('문자열1', 자리수, '문자열2') 
          SQL> select RPAD('khs', 13, '*#') from DUAL;

    <7> LTRIM('문자열1', '문자열2')
      SQL> select LTRIM('xyxXxyLAST WORDxy', 'xy') from DUAL;
          SQL> select LTRIM('xyyxXxyLAST WORDxy', 'xy') from DUAL;
      SQL> select LTRIM('xyyxXxyLAST WORDxy', 'xy') from DUAL;
          SQL> select LTRIM('   LAST WORDxy', ' ') from DUAL;
      SQL> select LTRIM('   LAST WORDxy') from DUAL;

        <8> RTRIM('문자열1', '문자열2')
      SQL> select RTRIM('xyxXxyLAST WORDxy', 'xy') from DUAL;
          SQL> select RTRIM('xyxXxyLAST WORDxyx', 'xy') from DUAL;
          SQL> select RTRIM('WORD  ', ' ') from DUAL;
          SQL> select RTRIM('WORD  ') from DUAL;
          SQL> select length('WORD  ') from DUAL;
          SQL> select length(RTRIM('WORD  ')) from DUAL;

        <9> REPLACE('문자열1', '문자열2', '문자열3')
          SQL> select REPLACE('JACK and JUE', 'J', 'BL') from DUAL;
          SQL> select REPLACE(DNAME, 'A', 'i') from DEPT;

        <10> SUBSTR('문자열', 자리수, 갯수)
      SQL> select SUBSTR('ABCDEFG', 3, 2) from DUAL;

      //ename의 두번째 자리가 'A'인 사원의 이름 출력
      SQL> select ENAME from EMP where ENAME LIKE '_A%';
          SQL> select ENAME from EMP where SUBSTR(ENAME, '2', '1')='A';

    <11> ASCII('문자')
      SQL> select ASCII('A') from DUAL;
          SQL> select ASCII('"') from DUAL;
          SQL> select ASCII('#') from DUAL;
          SQL> select ASCII('3') from DUAL;

    <12> LENGTH('문자')
      SQL> select LENGTH('진달래 꽃') from DUAL;
      SQL> select LENGTH('abC D') from DUAL;

    <13> GREATEST('문자열1', '문자열2', '문자열3')
      SQL> select GREATEST('CC', 'ABCDE', 'CA') from DUAL;
          SQL> select GREATEST('12', '132', '119') from DUAL;
          SQL> select GREATEST('가나', '가다', '가자') from DUAL;

        <14> LEAST('문자열1', '문자열2', '문자열3')
          SQL> select LEAST('CC', 'ABCDE', 'CA') from DUAL;
          SQL> select LEAST('12', '132', '119') from DUAL;
          SQL> select LEAST('가나', '가다', '가자') from DUAL;

    <15> INSTR('문자열1', '문자열2', 자리수1, 자리수2)
      SQL> select INSTR('CORPORATE FLOOR', 'OR', 3, 2) from DUAL;
          SQL> select INSTR('CORPORATE FLOOR', 'OR', 6, 1) from DUAL;

          cf) '자리수1'부터 '자리수2'번째의 '문자열2'를 찾음

        <16> NVL(컬럼명, 숫자)
      SQL> select ENAME, NVL(COMM, 100) from EMP;
          SQL> select ENAME, NVL(COMM, '100') from EMP;
          SQL> select ENAME, NVL(COMM, 0) from EMP;

      2) 숫자함수
        <1> ABS(숫자)
      SQL> select ABS(-15) from DUAL;

    <2> CEIL(숫자)
      SQL> select CEIL(11.012) from DUAL;
          SQL> select CEIL(-11.012) from DUAL;

    <3> FLOOR(숫자)
      SQL> select FLOOR(11.012) from DUAL;
      SQL> select FLOOR(-11.012) from DUAL;

    <4> ROUND(숫자)
      SQL> select ROUND(11.012) from DUAL;
          SQL> select ROUND(-11.48754321, 4) from DUAL;
          SQL> select ROUND(-11.48756321, 4) from DUAL;

    <5> COS(숫자[rad])
      SQL> select COS(90 * 3.14/180) from DUAL;

    <6> SIN(숫자[rad])
      SQL> select SIN(90 * 3.14/180) from DUAL;

        <7> TAN(수자[rad])
      SQL> select TAN(45 * 3.14/180) from DUAL;

        <8> EXP(숫자)
      SQL> select EXP(4) from DUAL;

    <9> LOG(숫자1, 숫자2)
      SQL> select LOG(10, 100) from DUAL;

    <10> MOD(숫자1, 숫자2)
      SQL> select MOD(11, 4) from DUAL;
     
    <11> POWER(숫자1, 숫자2)
      SQL> select POWER(3, 2) from DUAL;
      SQL> select POWER(3, 3) from DUAL;

    <12> TRUNC(숫자1, 숫자2)
      SQL> select TRUNC(15.789, 2) from DUAL;
          SQL> select TRUNC(15.789, 0) from DUAL;
          SQL> select TRUNC(-15.789, 0) from DUAL;
      
      3) 날짜함수(*****)
        <1> SYSDATE
       SQL> select SYSDATE from DUAL;
      
    <2> ADD_MONTHS(날짜컬럼 or 날짜데이터, 숫자)
       SQL> select HIREDATE, ADD_MONTHS(hiredate, 3) from EMP
            2  where EMPNO=7782;
           SQL> select ADD_MONTHS('90/11/05', 2) from DUAL;
      
        <3> LAST_DAY(날짜컬럼 or 날짜데이터)
       SQL> select LAST_DAY('10/12/01') from DUAL;
           SQL> select HIREDATE, LAST_DAY(hiredate) from EMP;

        <4> NEW_TIME(날짜컬럼 or 날짜데이터, 'GMT', 'PDT')
       SQL> select NEW_TIME('08/12/05', 'GMT', 'PDT') from DUAL;
           SQL> select NEW_TIME(hiredate, 'GMT', 'PDT') from EMP;

    <5> MONTHS_BETWEEN(날짜컬럼or날짜데이터1, 날짜컬럼or날짜데이터2)
       SQL> select MONTHS_BETWEEN('80/02/03', '82/02/27') from DUAL;

       //empno 가 7782인 사원의 현재까지의 근무월수
           SQL> select MONTHS_BETWEEN(SYSDATE, hiredate) from EMP
            2  where EMPNO=7782;//결과의 단위(월)

    <6> NEXT_DAY(날짜컬럼or날짜데이터, 숫자)
       SQL> select SYSDATE, NEXT_DAY(SYSDATE, 4) from DUAL;
           //4는 수요일

           -> 기준날짜 초과의 해당 요일(숫자)의 날짜를 반환

      4) 문자변환함수(*****)
         -> TO_CHAR(날짜컬럼or날짜데이터, '변환포멧')
    <1> 'D'
      SQL> select SYSDATE, TO_CHAR(SYSDATE, 'D') from DUAL;

    <2> 'DAY'
      SQL> select SYSDATE, TO_CHAR(SYSDATE, 'DAY') from DUAL;

    <3> 'DY'
      SQL> select SYSDATE, TO_CHAR(SYSDATE, 'DY') from DUAL;

    <4> 'DD'
      SQL> select SYSDATE, TO_CHAR(SYSDATE, 'DD') from DUAL;

    <5> 'MM'
      SQL> select SYSDATE, TO_CHAR(SYSDATE, 'MM') from DUAL;
     
    <6> 'MONTH'
      SQL> select SYSDATE, TO_CHAR(SYSDATE, 'MONTH') from DUAL;

        <7> 'YY'
      SQL> select SYSDATE, TO_CHAR(SYSDATE, 'YY') from DUAL;

    <8> 'YYYY'
      SQL> select SYSDATE, TO_CHAR(SYSDATE, 'YYYY') from DUAL;

    <9> 'DD-MM-YY'(원하는 순서로 조합 가능)
      SQL> select SYSDATE, TO_CHAR(SYSDATE, 'DD-MM-YY') from DUAL;
      SQL> select SYSDATE, TO_CHAR(hiredate, 'DD-MM-YY') from EMP;

    <10> 'fmMM-DD-YY'(원하는 순서로 조합 가능)
          SQL> select SYSDATE, TO_CHAR(SYSDATE, 'fmDD-MM-YY') from DUAL;

    <11> 'HH' or 'HH12'
      SQL> select SYSDATE, TO_CHAR(SYSDATE, 'HH') from DUAL;

    <12> 'HH24'
      SQL> select SYSDATE, TO_CHAR(SYSDATE, 'HH24') from DUAL;

        <13> 'MI'
      SQL> select SYSDATE, TO_CHAR(SYSDATE, 'MI') from DUAL;
     
        <14> 'SS'
      SQL> select SYSDATE, TO_CHAR(SYSDATE, 'SS') from DUAL;

    <15> 'AM' or 'PM'
      SQL> select SYSDATE, TO_CHAR(SYSDATE, 'AM HH:MI:SS') from DUAL;
          SQL> select SYSDATE, TO_CHAR(SYSDATE, 'PM HH:MI:SS') from DUAL;

    <16> YYYY-MM-DD PM HH:MI:SS (DY)
          //2010-12-01 오후 02:20:15 (수)
          SQL> select TO_CHAR(SYSDATE, 'YYYY-MM-DD PM HH:MI:SS (DY)')
         from DUAL;
          SQL> select TO_CHAR(SYSDATE, 'fmYYYY-MM-DD PM HH:MI:SS (DY)')
         from DUAL;

     cf) Tip
       - 출력에 임의의 문자열 삽입
        SQL> select TO_CHAR(SYSDATE, 'DD "of" MONTH') from DUAL;
            SQL> select TO_CHAR(SYSDATE, 'DD "임의의 문자열" MONTH') from DUAL;

       - one, two, three,...
        SQL> select TO_CHAR(SYSDATE, 'DDSP') from DUAL;

       - simple 서수 ( 1st, 2nd, .. )
             SQL> select TO_CHAR(SYSDATE, 'DDTH') from DUAL;
             SQL> select TO_CHAR(SYSDATE, 'SSTH') from DUAL;

       - 서수 ( first, second, .. )
         SQL> select TO_CHAR(SYSDATE, 'DDSPTH') from DUAL;
         SQL> select TO_CHAR(SYSDATE, 'SSSPTH') from DUAL;
         
      5) 숫자변환함수 (무시!!)
     -> TO_NUMBER('숫자')
     SQL> select '100' from DUAL; //100
         SQL> select '100'+12 from DUAL; //112
         SQL> select TO_NUMBER('100') from DUAL; //100
         SQL> select TO_NUMBER('100')+12 from DUAL; //112

         SQL> select 'A' from DUAL;
         SQL> select 'A'+1 from DUAL;//에러
         SQL> select TO_NUMBER('A') from DUAL;//에러

      6) 날짜변환함수
         -> TO_DATE('날짜 형태의 문자열')
       '날짜변환포멧(앞 문자열을 해석하는 순서)'
     SQL> select TO_DATE('01-05-99', 'DD/MM/YYYY') from DUAL;
     SQL> select TO_DATE('12-30-99', 'MM/DD/YYYY') from DUAL;

      7) 시스템함수
         -> USER ( field의 데이터 사용자를 리턴 )
     SQL> select USER from DUAL;
         SQL> select USER from EMP;

      8) 최신추가함수
        <1> NVL2(컬럼명, 표현식1, 표현식2)
     SQL> select COMM, NVL2(comm, comm*1.1, 0) from EMP;

        <2> NULLIF(비교값1, 비교값2)
     SQL> select ENAME, COMM, NULLIF(comm, 0) from EMP;

        <3> COALESCE(표현식1, 표현식2, ... , 표현식n)
     SQL> select comm, sal, coalesce(comm, sal) from EMP;

    <4> TRIM(LEADING 문자 FROM 대상컬럼) //왼쪽부터..
     SQL> select TRIM(LEADING 'A' FROM ENAME) from EMP;
         SQL> select TRIM(LEADING 'x' FROM 'xxyxXxyLAST') from DUAL;

        <5> TRIM(TRAILING 문자 FROM 대상컬럼) //오른쪽부터..
     SQL> select TRIM(TRAILING 'S' FROM ENAME) from EMP;
        
    <5> TRIM(BOTH 문자 FROM 대상컬럼) //양쪽에서..
     SQL> insert into EMP(EMPNO, ENAME) values(1000, 'TOMCAT');
         SQL> select TRIM(BOTH 'T' FROM ENAME) from EMP;
     SQL> rollback;//위의 insert문을 DB에 적용하지 않음

   (5) 복수행(그룹) 함수( ***** )
      1) COUNT(컬럼명)
        //사원들의 이름갯수
        SQL> select COUNT(ename) from EMP;
        SQL> select COUNT(comm) from EMP;
        SQL> select count(empno) from emp; //전체행의 개수
        SQL> select count(*) from emp; //전체행의 개수
   
      2) SUM(컬럼명)
        SQL> select sum(comm) from emp;
        SQL> select sum(sal) from emp;
 
      3) AVG(컬럼명)
        SQL> select AVG(sal) from EMP;
    SQL> select AVG(comm) from EMP; //NULL갯수 제외
    SQL> select AVG(NVL(comm,0)) from EMP; //NULL갯수 포함
 
      4) MAX(컬럼명) 와 MIN(컬럼명)
        SQL> select MAX(sal) from EMP;
        SQL> select MIN(sal) from EMP;
        SQL> select MAX(sal)-MIN(sal) from EMP;

    //최고 연봉자의 사원이름을 구하세요.
        SQL> select ENAME from EMP where SAL=MAX(sal);
        ORA-00934: 그룹 함수는 허가되지 않습니다
        SQL> select ENAME from EMP
        where SAL=(select MAX(sal) from EMP);

      5) STDDEV(컬럼명)
        SQL> select STDDEV(comm) from EMP;
   
      6) VARIANCE(컬럼명)
        SQL> select VARIANCE(comm) from EMP;
   
    <1> Tip
       SQL> select AVG(SAL), DEPTNO from EMP;
             ORA-00937: 단일 그룹의 그룹 함수가 아닙니다
       
       //부서별 평균 연봉 출력
           SQL> select AVG(SAL), DEPTNO from EMP group by DEPTNO;
           SQL> select ROUND(AVG(SAL)), DEPTNO from EMP group by DEPTNO;

    <2> 문제
       - Q1:연봉 2000 이상인 사원들의 부서별 평균연봉의 반올림값
            SQL> select ROUND(AVG(SAL)), DEPTNO from EMP
            where SAL>=2000 group by DEPTNO;
     
           - Q2:연봉 2000 이상인 사원들의 부서별 평균연봉의 반올림값
           을 부서번호의 오름차순으로 정렬하라
        SQL> select ROUND(AVG(SAL)), DEPTNO from EMP where SAL>=2000 group by DEPTNO
                order by DEPTNO;
 
           - Q3:연봉 2000 이상인 사원들의 부서별 평균연봉의 반올림값
           을 평균연봉의 반올림값으로 오름차순 정렬하라
        SQL> select ROUND(AVG(SAL)) aa, DEPTNO
          2  from EMP
          3  where SAL>=2000
          4  group by DEPTNO
          5  order by aa;
     
            SQL> select ROUND(AVG(SAL)), DEPTNO
          2  from EMP
          3  where SAL>=2000
          4  group by DEPTNO
          5  order by 1;

       - Q4:연봉 1000이상인 사원들의 부서별 평균연봉의 반올림값
          을 부서번호로 내림차순 정렬하라
          (단, 부서별 평균연봉이 2000 이상인 값만 출력)
            SQL> select ROUND(AVG(SAL)), DEPTNO
          2  from EMP
          3  where SAL>=1000
          4  group by DEPTNO
          5  having ROUND(AVG(SAL))>=2000
          6  order by DEPTNO desc;

            #주의 : having 절에서는 alias를 쓸 수 없음
            SQL> select ROUND(AVG(SAL)) aa, DEPTNO
          2  from EMP
          3  where SAL>=1000
          4  group by DEPTNO
          5  having aa>=2000
          6  order by DEPTNO desc;//에러!!

           - Q5:연봉 1000이상인 사원들의 부서별 평균연봉의 반올림값
           을 부서번호로 내림차순 정렬하라
           (단, 부서번호가 20 이상인 값만 출력! )
            SQL> select ROUND(AVG(SAL)), DEPTNO
         2  from EMP
         3  where SAL>=1000
         4  group by DEPTNO
         5  having DEPTNO>=20
         6  order by DEPTNO desc;

            - Q6:각 부서별 같은 업무를 하는 사원의 인원수를 구하여
           부서번호, 업무명, 인원수를 출력하라
           (단, 부서번호와 업무명으로 각각 내림차순 정렬! )
            SQL> select DEPTNO, JOB, COUNT(EMPNO) from EMP
         2  group by DEPTNO, JOB
         3  order by DEPTNO desc, JOB desc;
             
         < 일반화 규칙 1 >
            [1] S-> F -> W -> G -> H -> O
        [2] where 절에는 그룹함수를 쓸 수 없음
        [3] having 절에서는 alias를 쓸 수 없음


4. 조인(JOIN) & 서브쿼리(SubQuery)
   (1) 조인(JOIN)
      1) 설명
         하나의 테이블로는 원하는 컬럼정보를 참조할 수 없는 경우
     관련된 테이블을 논리적으로 결합하여 원하는 컬럼정보를
     참조하는 방법을 조인이라 한다.
 
      2) 조건
         논리적으로 결합되는 2개 이상의 테이블에는 반드시 '공통컬럼'
     이 존재해야하며 이 '공통컬럼'은 동일한 데이터 타입과
     공통된 데이터를 포함해야 한다.
      3) 예
         //(부서번호가 같은) 사원번호와 부서이름 출력!
         SQL> select EMP.EMPNO, DEPT.DNAME from EMP, DEPT
      2  where EMP.DEPTNO=DEPT.DEPTNO;
         SQL> select e.EMPNO, d.DNAME from EMP e, DEPT d
      2  where e.DEPTNO=d.DEPTNO;
         SQL> select e.EMPNO, d.DNAME from EMP e join DEPT d
      2  on e.DEPTNO=d.DEPTNO;
    
      4) 종류
         <1> CROSS 조인 ( CARTESIAN 조인 )
         2개 이상의 테이블이 조인될 때 where절에 의해
         공통되는 컬럼에 의한 결합이 발생하지 않는 경우
         즉, 테이블 전체행의 전체컬럼이 조인에 사용되는
         조인을 말한다.
         따라서, 모든 데이터가 검색된다.
            
         SQL> select e.EMPNO, e.ENAME, d.DNAME
             from EMP e, DEPT d;
             SQL> select e.EMPNO, e.ENAME, s.GRADE
             from EMP e, SALGRADE s;
            
     <2> NATURAL 조인 ( EQUI 조인 ) - 가장 일반적
        where 절이 사용된 '공통된 컬럼'들이 동등 연산자
        ( equals, = )에 의해 비교되는 조인을 말한다.
            1> 형태1(*****)
         SQL> select e.EMPNO, d.DNAME from EMP e, DEPT d
          2  where e.DEPTNO=d.DEPTNO; //14개
             //(부서번호가 같은) 사원번호와 부서이름 출력!(단, 30부서만)
             SQL> select e.EMPNO, d.DNAME from EMP e, DEPT d
          2  where e.DEPTNO=d.DEPTNO and d.DEPTNO=30;

        2> 형태2
         SQL> select EMPNO, ENAME, DNAME from EMP
          2  natural join DEPT;
         //사원번호와 사원이름과 부서이름 출력!(단, 20부서만)
             SQL> select EMPNO, ENAME, DNAME from EMP
          2  natural join DEPT
          3  where DEPTNO=20;

        3> 형태3 ( oracle 9i )
         SQL> select e.EMPNO, e.ENAME, d.DNAME
          2  from EMP e join DEPT d  using(DEPTNO);
         //사원번호와 사원이름과 부서이름 출력!(단, 10부서만)
             SQL> select e.EMPNO, e.ENAME, d.DNAME
          2  from EMP e join DEPT d  using(DEPTNO)
          3  where DEPTNO=10;

        4> 형태4
         SQL> select e.EMPNO, d.DNAME from EMP e join DEPT d
          2  on e.DEPTNO=d.DEPTNO;
         //(부서번호가 같은) 사원번호와 부서이름 출력!(단, 30부서만)
             SQL> select e.EMPNO, d.DNAME from EMP e join DEPT d
          2  on e.DEPTNO=d.DEPTNO
          3  where d.DEPTNO=30;

        cf) INNER조인 ( 형태4와 비교 )
        SQL> select e.EMPNO, d.DNAME from EMP e inner join DEPT d
         2  on e.DEPTNO=d.DEPTNO
         3  where d.DEPTNO=30;


     <3> SELF 조인
        참조해야 할 컬럼이 자신의 테이블에 있는 다른 컬럼인
        경우 사용하는 조인
        1> 형태1
           //사원번호가 같은 사원번호, 사원이름을 출력
               SQL> select e1.EMPNO, e2.ENAME from EMP e1, EMP e2
                2  where e1.EMPNO=e2.EMPNO;

        2> 형태2
               SQL> select e1.EMPNO, e2.ENAME from EMP e1 join EMP e2
        2  on e1.EMPNO=e2.EMPNO;
 
            여기서.. sqlForSelfJoin.sql을 작성해서 해당 검색을 하자
        //'취업반'이면서 '국어 and 미술'을 듣는 학생이름을 출력!
        답1> select NAME from HACKSA where DEPT='취업반'
         2  INTERSECT
         3  select NAME from HACKSA where SUBJECT='국어'
         4  INTERSECT
         5  select NAME from HACKSA where SUBJECT='미술';
 
            답2> select NAME from HACKSA where DEPT='취업반' and SUBJECT='국어'
         2  INTERSECT
         3  select NAME from HACKSA where DEPT='취업반' and SUBJECT='미술';

            답3> select a.NAME from HACKSA a, HACKSA b
         2  where a.NAME=b.NAME
         3  and a.DEPT='취업반'
         4  and a.SUBJECT='국어'
         5  and b.SUBJECT='미술';

        답4> select a.NAME from HACKSA a join HACKSA b
         2  on a.NAME=b.NAME
         3  and a.DEPT='취업반'
         4  and a.SUBJECT='국어'
         5  and b.SUBJECT='미술';

     <4> OUTER 조인
        한쪽 테이블에는 해당하는 데이터가 존재하는 데
        ( ex: DEPT의 deptno=40 - 존재 O)
        다른쪽 테이블에는 데이터가 존재하지 않을 경우
        ( ex: EMP의 deptno=40 - 존재 X)
        모든 데이터를 출력하게 하는 조인

       1>형태1
        //사원번호, 사원이름, 부서이름 출력!
            SQL> select e.EMPNO, e.ENAME, d.DNAME from EMP e, DEPT d
        2  where e.DEPTNO (+)= d.DEPTNO; //15개
         //dept deptno컬럼이 기준

            비교> select e.EMPNO, e.ENAME, d.DNAME
              from EMP e, DEPT d
                  where d.DEPTNO (+)= e.DEPTNO; //14개
                 //emp deptno컬럼이 기준
         
            즉, right outer join 과 유사
        (주의 : (+)= 으로는 full outer join은 안됨 )

      2>형태2
        SQL> select e.EMPNO, e.ENAME, d.DNAME
        2  from EMP e right outer join DEPT d
        3  on e.DEPTNO=d.DEPTNO; //15개
 
            SQL> select e.EMPNO, e.ENAME, d.DNAME
        2  from EMP e left outer join DEPT d
        3  on e.DEPTNO=d.DEPTNO; //14개

      여기서.. sqlForOuterJoin.sql을 작성해서 해당 검색을 하자
            SQL> select T1.NO, T1.NAME, T2.NO, T2.NAME
         2  from T1 left outer join T2
         3  on T1.NO=T2.NO; //T1기준

            SQL> select T1.NO, T1.NAME, T2.NO, T2.NAME
         2  from T1 right outer join T2
         3  on T1.NO=T2.NO; //T2기준

            SQL> select T1.NO, T1.NAME, T2.NO, T2.NAME
         2  from T1 full outer join T2
         3  on T1.NO=T2.NO; //T1과 T2 기준
 
         < 일반화 규칙 2 >
     OUTER JOIN의 옵션
     - right : 오른쪽 테이블의 해당컬럼을 기준
     - left : 왼쪽 테이블의 해당컬럼을 기준
     - full : 두 테이블 모두의 해당컬럼을 기준

     < 일반화 규칙 3>
         DQL문의 구문배치순서 및 실행순서(NUMBER)
     - select XX         -----> 6
     - from XX (XX join) -----> 1
     - on XX             -----> 2
     - where XX          -----> 3
     - group by XX       -----> 4
     - having XX         -----> 5
     - order by XX       -----> 7
     
   (2) 서브쿼리(SubQuery)
      1) 설명
        하나의 DQL문장절에 부속된(포함된) 또 다른 SELECT문장
    따라서, 두 번 이상 질의를 해야 얻을 수 있는 결과를
    한번의 질의로 해결이 가능케하는 쿼리
    
      2) 용어
        <1> Main-Query or Outer-Query
    <2> Sub-Query  or Inner-Query
 
      3) 특징
        <1> 괄호로 묶어야 한다.
    <2> 실행순서는 대부분 Sub-Query가 먼저 수행되고,
        Main-Query 수행된다.
    <3> Sub-Query는 Main-Query의 다음 부분에 위치된다.
        - select/delete/update문장의 from절/where절
        - insert문장의 into절
        - update문장의 set절
        <4> Sub-Query는 order by절을 사용할 수 없다.
        ( 예외: select/delete/update문장의 from절 )

      4) 종류
        <1> 단일행 Sub-Query
        -> Sub-Query의 결과가 하나의 컬럼 and
         하나의 행만을 리턴해 주는 경우의 쿼리
         즉, 하나의 데이터를 리턴해 주는 쿼리
        
         //사원번호가 7900 인 사원의 부서이름
             SUB> select DEPTNO from EMP where EMPNO=7900
         MAIN> select DNAME from DEPT where DEPTNO=30
         SQL> select DNAME from DEPT
           where DEPTNO=(select DEPTNO from EMP where EMPNO=7900);

         cf)SQL> select d.DNAME from DEPT d, EMP e
                 2  where d.DEPTNO=e.DEPTNO and e.EMPNO=7900;

         //부서번호가 10번인 사원연봉과 연봉이 같은
          사원의 이름과 커미션을 구하라.
         SUB> select SAL from EMP where DEPTNO=10
         error> select ENAME, COMM from EMP
           where SAL=(select SAL from EMP where DEPTNO=10);
           -> 행(ROW)이 여러개 return 되었기 때문(복수행)

             SUB> select SAL, COMM from EMP where EMPNO=7369;
             error> select ENAME, COMM from EMP
        where SAL=(select SAL, COMM from EMP where EMPNO=7369);
               -> 컬럼이 여러개 return 되었기 때문(복수컬럼)

         //평균급여보다 많은 급여의 사원의 이름과 급여 출력!
         SQL> select ENAME, SAL from EMP
            where SAL>(select AVG(SAL) from EMP);

             //생각해 보자
         SQL> select DNAME,
          2   (select SAL from EMP where EMPNO=7900)
          3   from DEPT where DEPTNO=10;
         출력은 되지만, MAIN과 SUB쿼리의 연관관계가 없기에
         의미없는 결과이다.

    <2> 복수행 Sub-Query
        -> Sub-Query의 실행결과가 여러개의 행을 return
        하는 경우의 쿼리
        (연산자 IN[=ANY], ANY, EXISTS,
           ALL[>:가장큰값,<:가장작은값,=:값이한개일때])
            1> IN
          SUB> select SAL from EMP where DEPTNO=10;
              SQL> select ENAME, SAL from EMP
               2  where SAL IN(select SAL from EMP where DEPTNO=10);
              SQL> select ENAME, SAL from EMP
              where SAL=2450 OR SAL=5000 OR SAL=1300;

        2> ALL
          //급여가 가장 많은 'MANAGER'보다 급여가
            같거나, 많은 사원의 JOB, SAL 을 출력!
              SUB> select SAL from EMP where JOB='MANAGER';
          SQL> select JOB, SAL from EMP where SAL>=
               ALL(select SAL from EMP where JOB='MANAGER');
              효과> select JOB, SAL from EMP where
               SAL>=2975 AND SAL>=2850 AND SAL>=2450;
          
              //급여가 가장 적은 'MANAGER'보다 급여가
            적은 사원의 JOB, SAL 을 출력!
              SUB> select SAL from EMP where JOB='MANAGER';
          SQL> select JOB, SAL from EMP where SAL<
               ALL(select SAL from EMP where JOB='MANAGER');
              효과> select JOB, SAL from EMP where
               SAL<2975 AND SAL<2850 AND SAL<2450;

              //급여가 가장 적은 'MANAGER'와 급여가 
            같은 사원의 JOB, SAL 을 출력!
              SUB> select SAL from EMP where JOB='MANAGER';
              SQL> select JOB, SAL from EMP where SAL=
               ALL(select SAL from EMP where JOB='MANAGER');
              효과> select JOB, SAL from EMP where
               SAL=2975 AND SAL=2850 AND SAL=2450;

        3> ANY
          //급여가 가장 큰 'SALESMAN'보다 급여가 작은 사원의
            JOB, SAL을 출력!
          SUB> select SAL from EMP where JOB='SALESMAN';
              SQL> select JOB, SAL from EMP
           where SAL<ANY(select SAL from EMP where JOB='SALESMAN');
          효과> select JOB, SAL from EMP where
               SAL<1600 OR SAL<1250 OR SAL<1500;

        4> EXISTS
          //'부서번호'가 10인 사원이 존재하면
            모든 부서의 이름을 출력!
          SUB> select * from DEPT where DEPTNO=10;
              SQL> select DNAME from DEPT
              2  where EXISTS(select * from DEPT where DEPTNO=10);//true
          SQL> select DNAME from DEPT
              2  where EXISTS(select * from DEPT where DEPTNO=50);//false

    <3> 복수컬럼 Sub-Query
        -> Sub-Query의 실행결과가 여러개의 컬럼 값(
         AND 여러개의 행)을 리턴해 주는 쿼리

         //'부서번호'가 30인 사원의 SAL과 COMM이
           같은 사원들의 '이름'과 '부서번호'를 출력!
         SUB> select SAL, COMM from EMP where DEPTNO=30; 
             SQL> select ENAME, DEPTNO from EMP
              2  where (SAL, COMM)
          3  IN(select SAL, COMM from EMP where DEPTNO=30);

             비교> select ENAME, DEPTNO from EMP
                where (SAL, NVL(COMM, -1))
            IN(select SAL, NVL(COMM, -1) from EMP where DEPTNO=30);

    <4> 상호관련 Sub-Query
        -> Main-Query절에 사용된 테이블이 Sub-Query절에 다시
         재사용되는 경우의 서브쿼리
       
        SUB> select AVG(e1.sal) from EMP e2, EMP e1
              2  where e2.DEPTNO=e1.DEPTNO; //독립적 수행 쿼리
        SUB-error> select AVG(sal) from EMP e2
              2  where e2.DEPTNO=e1.DEPTNO; //독립적 수행 안되는 쿼리
           
        SQL> select e1.EMPNO, e1.SAL from EMP e1
              2  where SAL>(select AVG(sal) from EMP e2
             where e2.DEPTNO=e1.DEPTNO); //수행
           

///////////////////////// 문제 3 ///////////////////////////

5. DML ( Data Manipulation Language )
  (1) 설명
     테이블내의 데이터를 입력,수정,삭제할 수 있는 SQL언어
   
  (2) 종류
      1) INSERT 문
         -> 테이블의 데이터를 저장(입력)할 때 사용
     SQL> insert into EMP values(8888,
      '이현록', 'ANALYST', 7788, SYSDATE, 2400, 100, 30);  
         SQL> insert into EMP values(9000, '이효준', SYSDATE);
     error> insert into EMP(ENAME) values('홍길동');

      2) UPDATE 문
         -> 테이블의 데이터를 변경(수정)할 때 사용
         SQL> select * from EMP where EMPNO=8888 or EMPNO=9000;
         SQL> update EMP set ENAME='서은경', JOB='학생' where EMPNO=8888;
         SQL> update EMP set EMPNO=8999 where EMPNO=8888;//pk field도 변경가능
         error> update DEPT set DEPTNO=50 where DEPTNO=30;
     //불가 - 30번부서를 참조하는 자식 ROW가 있기에

         SQL> update DEPT set DEPTNO=50 where DEPTNO=40;
     //가능 - 40번 부서의 사원이 없기에

      3) DELETE 문
         -> 테이블의 데이터를 삭제할 때 사용
         SQL> delete from EMP where ENAME='서은경';
         SQL> delete from EMP where EMPNO=9000;
         상동> delete from EMP
       where EMPNO=(select EMPNO from EMP where ENAME='이효준');

         SQL> insert into EMP(EMPNO, ENAME) values(1111, '이효준');
         SQL> insert into EMP(EMPNO, ENAME) values(2222, '이효준');
     SQL> delete from EMP where
      2  EMPNO=(select EMPNO from EMP where ENAME='이효준');//불가
        

         //여기서.. 실습을 해보자
     <1> TABLE 생성
       SQL> create table DMLTEST(
        2  NO number not null,
        3  NAME varchar2(20),
        4  ADDR varchar2(40));

     <2> TABLE 삭제
           SQL> drop table DMLTEST;

     <3> INSERT 테스트
       SQL> insert into DMLTEST values(10, '허인정', '경기도');
       SQL> insert into DMLTEST values(20, '박민형', '서울시');
       SQL> insert into DMLTEST values(30, '김태우', '대전시');

     <4> UPDATE 테스트
       SQL> update DMLTEST set NAME='정상화' where NO=10;

     <5> DELETE 테스트
       SQL> delete from DMLTEST where NO=20;
      
          cf1) drop table DMLTEST;
           purge recyclebin; //백업파일삭제 

          cf2) SQL> @E:\SOO\oracle\temp\dml\dmltest.sql

  (3) INSERT 문
     1) 입력시 제약사항
       <1> 한번에 하나의 행만 입력할 수 있다.
       <2> insert절에 명시된 컬럼갯수와
           values 절의 갯수는 같아야 한다.
          cf) insert절에 명시되지 않은 경우에는
         테이블의 모든 컬럼갯수가 적용
       <3> 입력될 값의 데이터타입과 컬럼타입은 같아야 한다.
          error> insert into DMLTEST(NO, ADDR) values('one', '서울');
     
       cf) 자동 converting
         - NUMBER 타입의 '숫자형태의 문자열'입려시
       자동으로 컨버팅(문자열 DATA -> NUMBER DATA) 기능
         - 문자열타입(VARCHAR2)에 '숫자'를 입력시
       자동으로 컨버팅(NUMBER DATA -> 문자열 DATA) 기능

       <4> 입력될 값의 크기는 컬럼의 크기보다 크지 않아야 한다.
         SQL> create table DMLTEST(
        NO number(3) not null,
        NAME varchar2(20),
        ADDR varchar2(40));
 
         SQL> insert into DMLTEST values(999, '다라', '인천');
     SQL> insert into DMLTEST values(-999, '가나', '서울');
         error> insert into DMLTEST values(1000, '가나', '서울');//불가
         SQL> insert into DMLTEST values(10.89, '다라', '인천');//11
         SQL> insert into DMLTEST values(10.12, '다라', '인천');//10
         SQL> insert into DMLTEST values(-10.89, '다라', '인천');//-11

     cf) VARCHAR2 : 4000 byte 저장가능
         NUMBER : +, - 38 자리수의 정수형 저장 가능
         ( 소수 입력시에는 반올림해서 정수형으로 저장)

     SQL> create DMLTEST(NO number, NAME varchar2);
         SQL> create table DMLTEST(NO number(5,2), NAME varchar2(20));
         SQL> insert into DMLTEST values(100, '길동');//가능
         error> insert into DMLTEST values(1000, '길동');//불가
         SQL> insert into DMLTEST values(12.34567, '길동');//12.35

        cf) NO number(5,2)는 총 5자리인데, 그 중에
          소수 2자리까지만 입력됨(소수 3째 자리에서 반올림)

       <5> NULL에 주의해야 한다.
         SQL> create table DMLTEST(NO number(3), NAME varchar2(20));
         SQL> insert into DMLTEST values(null, null);//가능

         SQL> create table DMLTEST(NO number(3) not null , NAME varchar2(20));
         SQL> insert into DMLTEST values(null, null);//불가

         SQL> create table DMLTEST(NO number(3) primary key, NAME varchar2(20));
         SQL> insert into DMLTEST values(null, 'aa'); //불가

         cf)create table DMLTEST(NO number(3) unique UK_TEST, NAME varchar2(20));

       <6> 문자와 날짜타입의 컬럼에는 ''을 사용하는 것이 원칙
          SQL> create table DMLTEST(NO number, NAME varchar2(20), d date);
          SQL> insert into DMLTEST values(10, '홍길동', SYSDATE);
          SQL> insert into DMLTEST values(20, '이순신', '99/11/28');
          SQL> select TO_CHAR(d, 'YYYY-MM-DD AM HH:MI:SS DAY') from DMLTEST;
          SQL> select * from DMLTEST where d>'10/05/11';
          SQL> select * from DMLTEST where d<'10/05/11';

       <7> 참조무결성을 고려해야 한다.
          SQL> insert into EMP(EMPNO, DEPTNO, ENAME)
          values(1111, 40, '이선희');//가능
          SQL> insert into EMP(EMPNO, DEPTNO, ENAME)
          values(2222, 90, '이선희');//불가

      //DEPT과 EMP은 부모간의 참조관계(FK)에 있기 때문

     2) 치환변수를 이용한 데이터 입력
       <1> 설명
          치환변수는 사용자로부터 입력받은 값을 SQL문장간에
      전달하기 위한 변수
     
       <2> 사용위치
          WHERE절, SELECT절, ORDER BY절, 테이블 이름, VALUES절

       <3> 종류
          1> & : SQL문장이 실행될 때마다 새로운 값을 요구
          (즉, 유효범위 => SQL문 )
            [ex1]
          SQL> select EMPNO, JOB from EMP where DEPTNO=&dno;
          dno의 값을 입력하십시오: 10

        [ex2]
          SQL> select EMPNO, JOB from EMP where JOB=&j;
          j의 값을 입력하십시오: 'MANAGER'    

              SQL> select EMPNO, JOB from EMP where JOB=&j;
          j의 값을 입력하십시오: 'manager'

        [ex3]
              SQL> insert into DEPT values(&no, &dn, &lo);
        no의 값을 입력하십시오: 60
        dn의 값을 입력하십시오: '영업부'
        lo의 값을 입력하십시오: '서울'

          2> && : 기실행된 치환변수값이 다음 실행시에도 유효
          (즉, 유효범위 => 하나 세션 )

         [ex]
          SQL> select EMPNO, JOB from EMP where JOB=&&j;
          j의 값을 입력하십시오: 'SALESMAN'
          SQL> insert into DMLTEST values(10, &&j, SYSDATE);
          //이미 j가 셋팅
         
          3> ACCEPT : 메세지와 함께 치환변수를 입력
         ( 유효범위 => 하나 세션 )
             SQL> accept depart prompt '부서번호를 입력하세요'
         부서번호를 입력하세요60
         SQL> accept depart prompt '부서번호가 뭐니?'
         부서번호가 뭐니?50
         SQL> insert into DEPT values(&depart, '총무부', '대전');//가능
         SQL> select * from DEPT where DEPTNO=&depart; //가능

     
     3) SUB-QUERY를 이용한 데이터 입력(*****)
        //DEPT테이블을 복사해서 DEPT2 테이블을 만드세요.
        SQL> create table DEPT2(DEPTNO number(2) not null,
       DNAME varchar2(14), LOC varchar2(13));
        SQL> insert into DEPT2 select * from DEPT;//복사
        SQL> delete from DEPT2;
        SQL> insert into DEPT2(DEPTNO, LOC)
         select DEPTNO, LOC from DEPT; //DEPTNO, LOC에만 데이터복사
    error> insert into DEPT2(DEPTNO, LOC)
         select DNAME, LOC from DEPT; //컬럼매칭이 틀림
        
        //EMP테이블을 EMP2을 만들어 복사하세요.
       
 
  (4) UPDATE 문
     1) 수정시 제약사항
        <1> where문을 항상 고려해야 한다.
    <2> where를 생략하면 테이블의 모든 데이터가 변경된다.
        SQL> update DEPT set DNAME='마케팅부';

    <3> 참조무결성을 고려해야 한다.
           SQL> create table DMLTEST(
           NO number(3) constraint UK_TEST unique, NAME varchar2(20));
           SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE
           from user_constraints;
            확인> FK_DEPTNO (R)
       error> update DEPT set DEPTNO=90 where DEPTNO=30;//불가
        //EMP사원중에서 deptno=30인 사원이 존재하므로
       SQL> update DEPT set DEPTNO=90 where DEPTNO=40;//가능
        //EMP사원중에서 deptno=40인 사원이 없으므로

           SQL> update DEPT set DNAME='회계부' where DEPTNO=30;//가능
        //deptno=30인 사원이 존재하더라도 다른 컬럼은 변경가능
       
      cf) 자식이 참조하고 있더라도 부모가 수정가능한 방법
             1> FK 삭제 ( 부자 인연 끊기 )
         2> 참조 자식 모두 삭제
         3> 참조 자식 다른 부모를 셋팅
            SQL> update EMP set DEPTNO=10 where DEPTNO=30;
      
       
     2) 데이터 수정의 2가지 방법
       <1> 일반적인 update문 사용법
       <2> SUB-QUERY를 이용한 UPDATE문 사용법
          SUB> select JOB, DEPTNO from EMP where EMPNO=7369;
          SQL> update EMP set(JOB, DEPTNO)
         =(select JOB, DEPTNO from EMP where EMPNO=7369);
         
     cf) DML을 DB에 적용하기 위해서는 TCL를 써줘야 함
            만약, TCL을 쓰지 않고 sqlplus를 종료(세션을 종료)
        하면 commit이 될 수도 있고, 안될 수도 있다.
        - commit이 되고 종료 : 안전종료 즉, exit;
        - commit이 안되고 종료 : 불안전종료 즉, 세션을 왼쪽상단X
       
  (5) DELETE 문
     1) 삭제시 주의사항
       <1> where 절을 항상 고려해줘야 한다.

       <2> 참조무결성을 고려해야 한다.
         error> delete from DEPT where DEPTNO=10; //불가 - 자식유
     SQL> delete from DEPT where DEPTNO=40; //가능 - 자식무

     참고)SQLPlus창을 청소 : SQL> $cls


6. TCL ( Transaction Control Language )
  (1) 설명
     DML문의 결과를 영구적으로 DBMS에 반영할지 여부를 결정하는 SQL
     즉, INSERT, UPDATE, DELETE 문의 실행결과를 DBMS저장하거나,
     되돌리기 위해서 실행해야 하는 SQL문

  (2) Transaction 설명
     1) 정의
        분리되어서는 안 될 (논리적)작업단위

     2) 시작
        <1> DBMS에 처음 접속했을 때
    <2> 하나 or 여러개의 DML문을 실행한 후 commit 또는
      rollback을 수행한 직후

     3) 끝
        <1> commit 이나 rollback을 실행하는 순간
      예외) rollback to a;처럼 savepoint 까지의 rollback은
        Transaction의 끝이 아님[진행중](즉, 영구저장X)
     
    <2> DDL문이나 DCL(Data Control Language)문이 실행되는 순간
       <세션1>
       SQL> insert into EMP(EMPNO, ENAME) values(4444, 'SSS');
           SQL> insert into EMP(EMPNO, ENAME) values(5555, 'DDD');
       SQL> create table AAA(NO number);
     
           <세션2>
       SQL> select * from EMP;//세션1의 insert data확인

        <3> DBMS가 (정상/비정상) 종료될 때
       <세션1> 세션 정상종료( commit됨 )
          SQL> insert into DEPT2 values(70, '국방부', '부산');
          SQL> quit; 또는 exit;
       <세션2>
              SQL> select * from DEPT2; //확인

       
       <세션1> 세션 비정상종료 ( rollback 됨 )
          SQL> insert into DEPT2 values(80, '산자부', '부산');
          SQL> quit; 또는 exit;
       <세션2>
              SQL> select * from DEPT2; //존재하지 않음

        
       <세션1>
          SQL> insert into DEPT2 values (50, '정통부', '대구');
           <세션2> DBMS 종료 ( rollback 됨 )
          sqlplus "/as sysdba"
          SQL> shutdown immediate; //DBMS process 종료
          SQL> startup //DBMS process 시작
       <세션2>
          SQL> select * from DEPT2; //존재하지 않음

   
        <4> 작업 Connection(세션)을 끊을 때
      1> 다른 세션으로 로그인 할 때 ( commit 됨 )
        <세션1>
             SQL> insert into DEPT2 values(80, '여성부', '인천');
         SQL> conn system/java6 as sysdba;
          
        <세션2>
         SQL> select * from DEPT2; //존재함
        
      2> sqlplus상에서 exit; 또는 quit; 를 쳐서 끊을 때
        ( 위와 중복 )

  (3) TCL 의 종류
     1) commit;
        DML문의 결과를 영구적으로 DBMS에 반영하는 SQL문

     2) rollback;
        DML문의 결과를(이전 Transaction 끝까지) 취소하는 SQL

     3) savepoint
        트랜젝션의 한 지점에 표시하는 임의 저장점

  (4) TCL의 관련 특성
     1) READ CONSISTENCY (읽기 일관성 - 해당 row에 대해서 )
        어떤 사용자가 변경 중인 행을 다른 사용자가 변경
    할 수 없게 하는 기술로써 변경중인 사용자에 의해
    commit 이나 rollback이 실행된 후에 변경가능한 특성

    <세션1>
     SQL> update DEPT2 set DNAME='남성부' where DEPTNO=80;
     SQL> commit; 또는 rollback;
     SQL> 1행 갱신

    <세션2>
     SQL> update DEPT2 set DNAME='중성부' where DEPTNO=80;//실행보류
     SQL> 1행 갱신

        cf) 다른 행(row)에서는 READ CONSISTENCY 적용 X

     2) LOCK ( 잠금성 )
        다른 사용자에 의해 DML문이 실행되어져 동일한 데이터를
    insert, update, delete 할 때 한 쪽 사용자는 LOCK걸리는 특성

    <1> ROW-LEVEL LOCK // 1)의 예와 동일

    <2> TABLE-LEVEL LOCK
       <세션1>
       SQL> delete from DEPT2; //테이블 전체 row에 lock걸림

       <세션2>
           SQL> update DEPT2 set DNAME='AAA' where DEPTNO=80;//보류
       SQL> update DEPT2 set DNAME='AAA' where DEPTNO=70;//보류

     
    cf) LOCK을 해제시키는 방법
      -> commit; 또는 rollback;


/////////////// 숙제 /////////////////////////////////////
  1. 게시판 테이블을 만들어서 오늘 배운부분예 대한 예제를
     적용해 오세요 ( create, insert, update, delete, drop )

  2. CASCADE 옵션에 대한 예제를 만들어 오세요.
     (1) DELETE
     (2) DROP

  3. EMP2와 DEPT2 테이블을 생성하고, 데이터를 복사해서
     CASCADE 옵션을 테스팅해 오세요..
///////////////////////////////////////////////////////////

'데이터베이스 > 오라클' 카테고리의 다른 글

[Oracle] constraint 제약조건 정리.★★★★★  (0) 2011.03.31
alter문 정리  (0) 2011.03.31
sqlplus 명령어  (0) 2010.06.02
테이블 생성시 TIP?  (0) 2010.06.02
오라클 사용자에 대해서 다뤄보자.  (0) 2010.06.02

+ Recent posts