[20110708] Oracle Join 문

-- 1) DECODE()

-- IF ~ ELSE IF ~ ELSE IF ~ ELSE

SELECT ename, job, sal, DECODE(job, 'SALESMAN', sal * 1.1,

  'ANALYST', sal * 1.2,

                                              'MANAGER', sal * 1.3, sal) 

                                               AS "Salary" FROM EMP;

-- 상반기에 해당하는 월까지에 해당 하는 인원을 출력하자

SELECT COUNT(DECODE(TO_CHAR(hiredate, 'MM'), '01', 1)) "1월",

       COUNT(DECODE(TO_CHAR(hiredate, 'MM'), '02', 2)) "2월",

       COUNT(DECODE(TO_CHAR(hiredate, 'MM'), '03', 3)) "3월",

       COUNT(DECODE(TO_CHAR(hiredate, 'MM'), '04', 4)) "4월",

       COUNT(DECODE(TO_CHAR(hiredate, 'MM'), '05', 5)) "5월",

       COUNT(DECODE(TO_CHAR(hiredate, 'MM'), '06', 6)) "6월"

FROM EMP

WHERE TO_CHAR(hiredate, 'MM') <= '06';

-- CASE ~ WHEN (Oracle 에서만 사용함)

SELECT ename, job, sal,

CASE

     WHEN job = 'SALESMAN' THEN sal * 1.1

        WHEN job = 'ANALYST' THEN sal * 1.2

        WHEN job = 'MANAGER' THEN sal * 1.3

        ELSE sal

    END "Salary"

FROM EMP;

SELECT

    COUNT(CASE WHEN TO_CHAR(hiredate, 'MM') = '01' THEN COUNT(*) END) AS "1월",

    COUNT(CASE WHEN TO_CHAR(hiredate, 'MM') = '02' THEN COUNT(*) END) AS "2월",

    COUNT(CASE WHEN TO_CHAR(hiredate, 'MM') = '03' THEN COUNT(*) END) AS "3월",

    COUNT(CASE WHEN TO_CHAR(hiredate, 'MM') = '04' THEN COUNT(*) END) AS "4월",

    COUNT(CASE WHEN TO_CHAR(hiredate, 'MM') = '05' THEN COUNT(*) END) AS "5월",

    COUNT(CASE WHEN TO_CHAR(hiredate, 'MM') = '06' THEN COUNT(*) END) AS "6월",

    COUNT(*) "총인원"

FROM EMP

GROUP BY hiredate

HAVING to_char(hiredate,'MM') <= '06';

-- GROP BY 에 대한 조건은 where 가 아닌 HAVING 를 사용해야 한다.

-- GROUP BY 는 해당 그룹에 대한 조건을 출력 하는것이다.

select MAX(sal), MIN(sal) FROM EMP GROUP BY deptno;

SELECT TRUNC(AVG(sal)) FROM EMP GROUP BY job

SELECT * FROM EMP ORDER BY job ASC

SELECT * FROM EMP ORDER BY job ASC, ename DESC

-- JOIN (전체를 여러그룹으로 본다, 테이블 하나가 아니라 여러개의 테이블이다)

-- EQUI JOIN (등가 조인, INNER JOIN)

-- FOREIGN KEY 를 이용해서 두개이상의 테이블 연결

-- EMP 테이블과 dept 테이블의 연관조건을 알아보고 싶다

-- emp 테이블의 제약조건(constraints)을 알면된다

SELECT e.EMPNO, e.DEPTNO, e.JOB, d.DNAME

FROM EMP e, DEPT d --

WHERE e.DEPTNO = d.DEPTNO   -- EQUI JOIN

SELECT e.EMPNO, E.ENAME, e.DEPTNO, e.JOB, d.DNAME, d.LOC

FROM EMP e, DEPT d

WHERE e.DEPTNO = d.DEPTNO AND e.ENAME = 'SCOTT';

DESC EMP -- EMP 구성 조회

desc user_tables;

SELECT table_name FROM user_tables;

DESC user_constraints

SELECT owner, constraint_name, constraint_type, table_name,

r_owner, r_constraint_name

FROM user_constraints

WHERE table_name = 'EMP'

SELECT owner, constraint_name, constraint_type, table_name,

r_owner, r_constraint_name

FROM user_constraints

WHERE table_name = 'DEPT'

-- Non-EQUI JON (비등가 조인)

-- 두 테이블간의 직접 대응하는 열이 없다.

SELECT e.Ename, e.SAL, s.GRADE

FROM EMP e, SALGRADE s

WHERE e.SAL BETWEEN s.LOSAL AND s.HISAL AND e.Ename = UPPER('scott');

SELECT ename, mgr FROM EMP WHERE ename = UPPER('scott');

SELECT e1.ENAME, e1.MGR, e2.EMPNO, e2.ENAME

FROM EMP e1, EMP e2

WHERE e1.MGR = e2.EMPNO AND e1.ename = UPPER('scott');

SELECT e1.ENAME, e1.MGR, e2.EMPNO, e2.ENAME

FROM EMP e1, EMP e2

WHERE e1.MGR = e2.EMPNO AND e1.ename = UPPER('scott');

-- 사번, 이름, 매니저번호, 매니저이름, 매니저 위치 출력하라

SELECT e1.empno "사원 번호", e1.ename "사원 이름", e2.EMPNO "매니저 번호",

       e2.ENAME "매니저 이름", d.LOC "매니저 근무 위치"

FROM EMP e1, emp e2 , DEPT d

WHERE e1.mgr = e2.empno AND e2.DEPTNO = d.DEPTNO

-- OUTER JOIN (포괄조인)

-- 정보가 부족한 쪽에 ' + ' 기호를 붙인다.

SELECT e.ename, e.deptno, d.dname

FROM EMP e, DEPT d

WHERE e.DEPTNO(+) = d.DEPTNO;