[오라클] 예제03 - JOIN함수, 서브쿼리

<JOIN함수 예제>

-- 1. DEPT 테이블과 EMP 테이블에서 사번, 사원명, 부서코드, 부서명을 검색하시오. (사원명 오름차순 정렬)

-- 2. DEPT 테이블과 EMP 테이블에서 사번, 사원명, 급여, 부서명을 검색하시오. 단, 급여가 2000 이상인 사원에 대하여 급여기준으로 내림차순 정렬할 것.

-- 3. DEPT 테이블과 EMP 테이블에서 사번, 사원명, 업무, 급여, 부서명을 검색하시오. 단, 업무가 MANAGER이며 급여가 2500 이상인 사원에 대해, 사번을 기준으로 오름차순 정렬할 것.

-- 4. EMP 테이블과 SALGRADE 테이블에서 사번, 사원명, 급여, 등급을 검색하시오. 단, 등급은 급여가  LOSAL과 HISAL 범위에 포함되고 등급이 4이며 급여를 기준으로 내림차순 정렬할 것.

-- 5. DEPT 테이블, EMP 테이블, SALGRADE 테이블에서 사번, 사원명, 부서명, 급여, 등급을 검색하시오. 단, 등급은 급여가 LOSAL과 HISAL 범위에 포함되며 등급을 기준으로 내림차순 정렬할 것.

-- 6. EMP 테이블에서 사원명과 해당 사원의 관리자명을 검색하시오.

-- 7. EMP 테이블에서 사원명과 해당 사원의 관리자명, 해당 사원의 관리자의 관리자명을 검색하시오.

-- 8. 7번 결과에서 상위 관리자가 없는 모든 사원의 이름도 사원명에 출력되도록 수정하시오.

 

 

<서브쿼리 예제>

-- 1. EMP 테이블에서 BLAKE보다 급여가 많은 사원들의 사번, 이름, 급여를 검색하시오.

-- 2. EMP 테이블에서 MILLER보다 늦게 입사한 사원의 사번, 이름, 입사일을 검색하시오.

-- 3. EMP 테이블에서 사원 전체 평균 급여보다 급여가 많은 사원들의 사번, 이름, 급여를 검색하시오.

-- 4. EMP 테이블에서 CLARK와 같은 부서이며, 사번이 7698인 직원의 급여보다 많은 급여를 받는 사원들의 사번, 이름, 급여를 검색하시오.

 

<JOIN 함수 풀이>

-- 1.
SELECT EMPNO, ENAME, DEPT.DEPTNO, DNAME  -- DEPT.DEPTNO 대신 EMP.DEPNO가 들어가도 됨
FROM EMP, DEPT  
WHERE EMP.DEPTNO=DEPT.DEPTNO 
ORDER BY EMP.ENAME; 

-- 2.

SELECT EMPNO, ENAME, SAL, DNAME 
FROM DEPT JOIN EMP ON EMP.DEPTNO=DEPT.DEPTNO
WHERE SAL>=2000 
ORDER BY SAL DESC;

또는

SELECT EMPNO, ENAME, SAL, DNAME
FROM DEPT NATURAL JOIN EMP  --- 두 테이블에 서로 겹치는 컬럼이 DEPTNO 한 개 뿐이기 때문에 내츄럴 조인을 쓸 수 있음
WHERE SAL>=2000
ORDER BY SAL DESC;

-- 3.

SELECT EMPNO, ENAME, JOB, SAL, DNAME 
FROM DEPT JOIN EMP ON EMP.DEPTNO=DEPT.DEPTNO 
WHERE JOB='MANAGER' AND SAL>=2500 
ORDER BY EMPNO;

또는 

SELECT EMPNO, ENAME, JOB, SAL, DNAME
FROM DEPT NATURAL JOIN EMP
WHERE JOB='MANAGER' AND SAL>=2500
ORDER BY EMPNO;

-- 4.
SELECT EMPNO, ENAME, SAL, GRADE 
FROM EMP JOIN DEPT ON SAL BETWEEN LOSAL AND HISAL
WHERE GRADE=4 
ORDER BY SAL DESC;

-- 5.
SELECT EMPNO, ENAME, DNAME, SAL, GRADE 
FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO 

         JOIN SALGRADE ON SAL BETWEEN LOSAL AND HISAL
ORDER BY SAL DESC;

-- 6.

SELECT W.ENAME 사원명, M.ENAME 관리자명 
FROM DEPT JOIN EMP ON EMP.DEPTNO=DEPT.DEPTNO 
WHERE JOB='MANAGER' AND SAL>=2500 
ORDER BY EMPNO;

 

참고로, 카타시안 프로덕트로 풀면
SELECT WORKER.ENAME, MANAGER.ENAME
FROM EMP WORKER, EMP MANAGER
WHERE WORKER.MGR=MANAGER.EMPNO;

-- 7.

-- 셀프조인을 두 번 사용

SELECT WORKER.ENAME, MANAGER.ENAME, UPPERMANAGER.ENAME 
FROM EMP WORKER JOIN EMP MANAGER ON WORKER.MGR=MANAGER.EMPNO

        JOIN EMP UPPERMANAGER ON MANAGER.MGR=UPPERMANAGER.EMPNO ;
참고로, 카타시안 프로덕트로 풀면

SELECT WORKER.ENAME, MANAGER.ENAME, UPPERMANAGER.ENAME 
FROM EMP WORKER, EMP MANAGER, EMP UPPERMANAGER 
WHERE WORKER.MGR=MANAGER.EMPNO AND MANAGER.MGR=UPPERMANAGER.EMPNO;

 

-- 8.

-- 레프트 조인 이용

SELECT WORKER.ENAME, MANAGER.ENAME, UPPERMANAGER.ENAME 
FROM EMP WORKER LEFT JOIN EMP MANAGER ON WORKER.MGR=MANAGER.EMPNO

         LEFT JOIN EMP UPPERMANAGER ON MANAGER.MGR=UPPERMANAGER.EMPNO ;

참고로, 카타시안 프로덕트로 풀면

SELECT WORKER.ENAME, MANAGER.ENAME, UPPERMANAGER.ENAME 
FROM EMP WORKER, EMP MANAGER, EMP UPPERMANAGER 
WHERE WORKER.MGR=MANAGER.EMPNO(+) AND MANAGER.MGR=UPPERMANAGER.EMPNO(+);

 

 

<서브쿼리 풀이>

-- 1.

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL>(SELECT SAL FROM EMP WHERE ENAME='BLAKE'); 

 

-- 2.

SELECT EMPNO, ENAME, HIREDATE
FROM EMP
WHERE HIREDATE>(SELECT HIREDATE FROM EMP WHERE ENAME='MILLER'); 

-- 3.

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE SAL>(SELECT AVG(SAL) FROM EMP); 

-- 4.

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='CLARK'); 
          AND NAME<>'CLARK' AND SAL>(SELECT SAL FROM EMP WHERE EMPNO=7698);

          -- CLARK를 제외해주면 더 정확한 결과