오라클 강의자료 2011.09.20 (쌍용교육센터)

--2011.09.20

   INNER JOIN : 
       1) NULL을 포함할 수 없다.
       2) 일반적으로 가장 많이 사용되는 조인
       3) 같은 값을 가진 있는 테이블을 연결
            EQUI-JOIN (=)
            NON-EQUI-JOIN (=이외의 연산자 사용)
            BETWEEN~AND 
       emp.deptno = dept.deptno
            1) FROM emp, dept WHERE emp.deptno=dept.deptno
            2) FROM emp NATURAL JOIN dept(자연조인)
                    : 테이블에 같은 필드명이 존재
            3) FROM emp JOIN dept USING(deptno)
        emp-sal      salgrade-losal, hisal

   테이블 3개 연결 emp, dept, salgrade
       출력 : 이름(emp-ename), 부서명(dept-dname), 등급(slagrade-grade)
          SELECT ename, dname FROM emp, dept WHERE emp.deptno=dept.deptno;
          SELECT ename, dname, grade FROM emp e, dept d, salgrade s
                     WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal;

    SELF JOIN : 같은 테이블에 2개 이상을 가지고 연결
         각사원의 관리자 이름
              출력) 사원(emp), 관리자(emp)
                    SELECT e.ename "사원이름", e.mgr "관리자번호", m.ename "관리자이름" FROM emp e, emp m
                                 WHERE e.mgr=m.empno; 

    OUTER JOIN : 관리자 모드
          NULL일 경우에도 포함을 한다.
              LEFT OUTER JOIN
              RIGT OUTER JOIN
              FULL OUTER JOIN

emp :  사번, 이름, 직급, 관리자번호, 입사일, 급여, 평가급, 부서번호
dept : 부서번호, 부서명, 부서위치
salgrade : losal, hisal, grade

   SELECT * FROM tab; 현재 저장되어 있는 테이블
   DESC emp
   DESC dept
   DESC salgrade : 테이블의 구조

SELECT e.ename, e.mgr, m.ename FROM emp e, emp m WHERE e.mgr=m.empno(+);
SELECT ename, dname FROM emp, dept WHERE emp.deptno(+)=dept.deptno;

1. 사원들의 이름, 부서번호(emp), 부서이름, 근무지(dept) 출력 EQUI-JOIN
SELECT ename,emp.deptno,dname,loc from emp, dept WHERE emp.deptno = dept.deptno;
2. 부서번호가 30번인 사원들의 이름,직급,부서번호(emp),부서위치(dept) 출력
SELECT ename,job,emp.deptno,loc FROM emp,dept WHERE emp.deptno = dept.deptno AND emp.deptno = 30;
3. 커미션을 받는 사원의 이름, 커미션, 부서이름, 부서위치 출력
SELECT ename,comm,dname,loc FROM emp,dept
          WHERE emp.deptno = dept.deptno AND comm IS NOT NULL AND comm NOT IN(0);
4. DALLAS에서 근무하는 사원의 이름, 직급, 부서번호, 부서이름 출력
SELECT ename, job, emp.deptno, dname FROM emp, dept
          WHERE emp.deptno = dept.deptno AND loc='DALLAS';
5. 이름이 A가 들어가는 사원들의 이름과 부서 번호출력
SELECT ename,emp.deptno,dname FROM emp,dept WHERE emp.deptno = dept.deptno AND ename like '%A%'
6. 사원이름과 직급,급여,급여등급 출력 NON-EQUI
SELECT ename, job, sal, grade FROM emp,salgrade WHERE emp.sal BTEWEEN salgrade.losal AND salgrade.hisal;
7. 사원이름,부서번호와 해당 사원과 같은 부서에서 근무하는 사원출력 SELF-JOIN
SELECT e1.ename, e1.deptno, e2.ename FROM emp e1, emp e2
      WHERE e1.deptno = e2.deptno AND e1.ename<>e2.ename;


 JOIN : 하나 이상의 테이블에서 원하는 데이터를 찾기 위한 기능
 SUBQUERY : 하나의 테이블에서 검색한 결과를 다른 테이블로 전달하여 새로운 결과를 검색하는 기능
         형식 ) 메인쿼리 = (서브쿼리) : 쿼리 두번 수행을 한번 수행으로 종료
         사원의 이름만 알고 있따. 부서명, 근무지(dept)  SCOTT를 이용해서 부서번호를 가지고 온다
         가지고 온 부서번호를 이용해서 부서명, 근무지를 확인

 서브쿼리에서 출력한 결과값 1, 여러개
     단일행 서브쿼리
     다중행 서브쿼리 IN(select deptno from dept),ANY, ALL
                            IN(10,20,30,40)
                            ANY(SELECT deptno from dept) 가장작은값
                            ALL(SLECT deptno from dept) 가장큰값
                            IN : 서브쿼리의 결과값 전체를 출력
                            ANY : 서브쿼리의 결과값 중에 최소값을 출력
                            ALL : 서브쿼리의 결과값중에 최대값을 출력
                            EXIST : 서브쿼리의 결과값이 한개라도 존재하면 출력
  서브쿼리 : select, insert, update, delete 사용이 가능

          사원의 이름, 부서번호, 직위, 급여를 출력
 단 emp의 전체급여의 평균보다 큰사란만 출력 
          사원의 이름 ,직위, 입사일, 급여를 출력
            SELECT deptno FROM emp WHERE ename = 'SCOTT';
            SELECT dname,loc FROM dept WHERE deptno = 20;
      ★ SUBQUERY
            SELECT dname,loc FROM dept WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SCOTT');
 단 부서번호가 20번인 사람중에 가장 작은 연봉을 받는 사람보다
           연봉이 많은 사람을 출력
SELECT AVG(sal) FROM emp;
SELECT ename, deptno, job, sal FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);
SELECT ename, job, hiredate, sal FROM emp WHERE sal > (SELECT MIN(sal) FROM emp WHERE deptno = 20 );
SELECT ename, job, hiredate, sal FROM emp WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 20 );
SELECT empno, ename,hiredate,sal,deptno FROM emp WHERE deptno IN(SELECT deptno FROM emp WHERE sal > 3000);

1. SCOTT의 급여와 동일하거나 더 많이 받는 사원의 이름, 급여 출력
     1)SCOTT의 급여 (서브쿼리)
     2)SCOTT의 급여보다 크거나 같은 사람들 출력(메인쿼리)
         SELECT ename,sal from emp WHERE sal >= (SELECT sal from emp WHERE ename='ALLEN');
2. 직급이 CLERK인 사람의 부서번호를 검색
         SELECT deptno from emp WHERE job = 'CLERK';
3. 직급이 CLERK인 사람의 부서번호(emp)와 부서명과 지역(dept)을 출력
         SELECT dname,loc FROM dept WHERE deptno IN(SELECT deptno FROM emp WHERE job='CLERK');
4. 이름이 T를 포함한 사원의 부서에서 같이 근무하는 사원의 이름, 사원번호 출력
     SELECT distinct deptno FROM emp WHERE ename like '%T%';
     SELECT ename,empno FROM emp
          WHERE deptno IN(SELECT distinct deptno FROM emp WHERE ename like '%T%');
5. 부서위치가 달라사인 모든 사원의 이름과 부서번호 출력
      SELECT ename,deptno FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE loc = 'DALLAS');
6. SALES 부서의 모든 사원과 급여출력
      SELECT ename,sal FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE dname = 'SALES');

7. KING에게 보고하는 모든 사원의 이름과 급여 출력
      SELECT ename, sal FROM emp WHERE mgr IN(SELECT empno FROM emp WHERE ename ='KING');
8. 자신의 급여가 평균 급여보다 많고 이름에 S자가 들어가 있는 동일한 부서에서 모든 사람의 이름, 사번, 급여를 출력
     SELECT ename, empno, sal FROM emp 
         WHERE deptno IN(SELECT deptno FROM emp
             WHERE sal>(SELECT AVG(sal) FROM emp) AND ename like '%S%');

테이블 생성
REATE TABLE dept01 AS SELECT * FROM dept;

치환변수
    SELECT * FROM & table_name;
    SELECT * FROM emp WHERE sal > &sal_value;
    SELECT &&column_name FROM &&table_name;
    DELETE FROM dept01;

다음 할 내용
     테이블 제약조건 수정, 삭제,추가
     테이블 생성 서브쿼리 이용해서 데이터 수정, 삭제, 추가
     트랜잭션, 뷰

     인덱스 MARGE(2개이상 테이블을 합치는 과정)
     PS/SQL, 프로시져

    트리거, 데이터베이스 모델링

 1.데이터형
     NUMBER(value, value) : 숫자
     CHAR(1~2000) : 문자열 (정적)
     VARCHAR2(1~4000) : 문자열 (가변)
     CLOB(4GB) : 문자열
     BLOB(4GB) : 동영상, 그림
     BFILE(4GB) : 파일(B ==> binary)
     DATE, TIMESTAMP : 날짜

 2.테이블 생성 CREATE
     컬럼제약조건
     PRIMARY KEY, NOT NULL, CHECK, DEFAULT
     테이블 삭제 DROP
     테이블 수정 ALTER
     ADD, MODIFY ,DROP
 3. 데이터 관리
    1. SELECT : 검색
        WHERE == 조건
        GROUP BY == 그룹별 처리
        HAVING == 그룹 조건
        ORDER BY (ASC|DESC)
          1)JOIN
              INNER (EQUI, NONEQUI)
                   == (=),NATURAL JOIN, JOIN~ USING
              OUTER
                   LEFT OUTER JOIN,RIGHT OUTER JOIN
                        (+)는 어디에 붙는다 (NULL)
                    WHERE e.deptno(+) = m.deptno : RIGHT
                    WHERE e.deptno = m.deptno(+) : LEFT
              SELF
          2)SUBQUERY
              단일행
              다중행 : IN, ANY, ALL, EXIST
              내장 함수
              단일행 : NVL, DECODE, ROUND, TO_CHAR
              다중행 : COUNT, MAX, AVG, SUM, MIN
       2. INSERT : 데이터 추가
       3. UPDATE : 데이터 수정
       4. DELETE : 데이터 삭제
             SQL : DML, DDL, DCL(제어), TCL(COMMIT, ROLLBACK,SAVE-POINT)