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

-- 2011. 9. 19
      JOIN : table 2개 이상 연결
           INNER JOIN, OUTER JOIN 차이점
           INNER JOIN : NULL 값일 경우에 수행하지 못한다.
           OUTER JOIN : NULL 값까지 값을 가지고 올 수 있다.
                        (관리자) NULL까지 포함한다

     JOIN
          inner join
              1. CROSS JOIN
                  조건문 없이 이중 for을 사용하는 것과 동일하다
                        SELECT emp.column, dept.column..... from emp,dept
                        SELECT emp.column, dept.column..... from emp CROSS JOIN dept
                         column명이 중복
                               emp : deptno
                               dept : deptno
              2. EQUI JOIN
                  1) SELECT column명,...FROM emp,dept WHERE emp.deptno=dept.deptno
                  2) SLECT column명...FROM emp NATURAL JOIN dept
                  3) SELECT column명...FROM emp JOIN dept USING(deptno)
                        자연조인, JOIN~USING
              3. NON-EQUI JOIN
                    조건 '<', BETWEEN~AND (=가 사용되지 않는 조건)
                      참조되지 않는 테이블이 있을 경우
          outer join
                LEFT JOIN
                RIGHT JOIN
                FULL JOIN
          self join
                자신 테이블 조인
 SUBQUERY
       메인 (서브쿼리) : 서브쿼리가 수행하고 결과값을 메인 쿼리에 전송
         == 단일서브쿼리 : 서브쿼리의 결과값이 1개일 경우
         == 다중서브쿼리 : 서브쿼리의 결과값이 여러개 일 경우
              (IN, ANY, ALL)


CROSS JOIN 사용법
   일반적으로 많이 사용되는 방법
     SELECT empno,ename,job,hiredate,sal,deptno,dname,loc FROM emp,dept; 오류
   table의 별칭을 부여
     SELECT empno, ename,job,hiredate,sal,e.deptno,dname,loc FROM emp e,dept d;
   CROSS JOIN
     SELECT empno, ename, job, hiredate, sal, emp.deptno, dname, loc FROM emp CROSS JOIN dept;

EQUI-JOIN 사용법
      SELECT empno,ename,emp.deptno,dname,loc FROM emp,dept WHERE emp.deptno=dept.deptno;

      SELECT empno,ename,deptno,dname,loc FROM emp NATURAL JOIN dept;

      SElECT empno,ename,deptno,dname,loc FROM emp JOIN dept USING(deptno);

EQUI-JOIN 조건을 수행
    SELECT empno,ename,dname,loc FROM emp,dept WHERE emp.deptno=dept.deptno AND empno=30;

    SELECT empno,ename,dname,loc FROM emp NATURAL JOIN dept WHERE deptno=30;

    SELECT empno,ename,dname,loc FROM emp JOIN dept USING(deptno) WHERE deptno=30;

     1)사원 이름, 부서명, 근무지를 출력,조건 == S자로 시작하는 모든 사람
           1.EQUI-JOIN
                SELECT ename,dname,loc FROM emp,dept WHERE emp.deptno=dept.deptno AND ename like 'A%';
           2.NATURAL JOIN
                SELECT ename,dname,loc FROM emp NATURAL JOIN dept WHERE ename like 'A%';
           3.JOIN~USING
                SELECT ename,dname,loc FROM emp JOIN dept USING(deptno) WHERE ename like 'A%';

     SELECT empno,ename,sal,grade FROM emp e,salgrade s WHERE sal BETWEEN s.losal AND e.hisal AND deptno=10;

 숙제
 1.EUQI JOIN 3가지 방법을 이용
     사번(empno),이름,입사일,급여,직위,부서명,근무지 출력 정렬은 내림차순으로 출력(deptno)
        SELECT empno,ename,hiredate,sal,mgr,dname,loc FROM emp,dept
                       WHERE emp.deptno=dept.deptno order by emp.deptno asc;
        SELECT empno,ename,hiredate,sal,mgr,dname,loc FROM emp NATURAL JOIN dept order by deptno asc;
        SELECT empno,ename,hiredate,sal,mgr,dname,loc FROM emp JOIN dept USING(deptno) order by deptno asc;

 2. 모든 사원의 이름,관리자의 이름,부서명을 출력
  emp     emp      dept
       select e1.ename, e2.ename, d.dname from emp e1, emp e2, dept d
               WHERE e1.mgr = e2.empno and e1.deptno = d.deptno;
 3. 관리자별 관리인원의 총수
    7788 3 ==> GROUP BY
        SELECT e2.ename , count(e1.mgr) from emp e1, emp e2 WHERE e1.mgr = e2.empno group by e2.ename;