ORACLE SQL JOIN과 서브쿼리 SCOTT 문제 풀이

JOIN

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

SELECT EMPNO, ENAME, EMP.DEPTNO, DNAME
FROM EMP JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO
ORDER BY ENAME ASC;

-- DEPTNO는 두 테이블에 모두 있기 때문에 SELECT DEPTNO 앞에 테이블명을 붙여야 한다.

 

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

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

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

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

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

SELECT EMPNO, ENAME, SAL, GRADE
FROM EMP, SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL AND GRADE=4
ORDER BY SAL DESC;

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

SELECT EMPNO, ENAME, DNAME, SAL, GRADE
FROM EMP, DEPT, SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL AND EMP.DEPTNO = DEPT.DEPTNO
ORDER BY GRADE DESC;

-- BETWEEN HISAL AND LOSAL로 입력하니 결과가 출력되지 않았다. BETWEEN 낮은 값 AND 큰값으로 표기해야 한다.

 

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

SELECT EN.ENAME "사원명", MN.ENAME "매니저명"
FROM EMP EN, EMP MN
WHERE EN.MGR=MN.EMPNO;

-- 관리자가 없는 사원은 출력되지 않는다. (8번 참고)


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

SELECT EN.ENAME, MN.ENAME "관리자명", MMN.ENAME "관리자의 관리자명"
FROM EMP EN, EMP MN, EMP MMN
WHERE EN.MGR=MN.EMPNO AND MN.MGR=MMN.EMPNO;

-- 관리자가 없는 사원은 출력되지 않는다. (8번 참고)

 

 

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

--기본 문법--
SELECT EN.ENAME, MN.ENAME "관리자명", MMN.ENAME "관리자의 관리자명"
FROM EMP EN, EMP MN, EMP MMN
WHERE EN.MGR=MN.EMPNO(+) AND MN.MGR=MMN.EMPNO(+);

--ANSI 조인--
SELECT EN.ENAME, MN.ENAME "관리자명", MMN.ENAME "관리자의 관리자명"
FROM EMP EN LEFT JOIN EMP MN
ON EN.MGR=MN.EMPNO
LEFT JOIN EMP MMN
ON MN.MGR=MMN.EMPNO;

-- 관리자가 없는 사원도 관리자명에 null을 출력되도록 하려면 외부조인을 이용해야한다.

1. 기존 문법 : 조인에 참여하는 테이블 중 데이터가 없는 테이블 쪽에(+) 기호를 붙인다.

2. ANSI 외부조인  : LEFT OUTER JOIN. 데이터가 있는 테이블을 기준으로 해서 왼쪽으로 외부조인 한다.

 

9. 20번 부서의 이름과 그 부서에 근무하는 사원의 이름을 출력하시오.

SELECT DNAME, ENAME, DEPT.DEPTNO
FROM EMP, DEPT
WHERE DEPT.DEPTNO=EMP.DEPTNO AND DEPT.DEPTNO=20;

10. 커미션을 받는 사원의 이름, 커미션, 부서이름 출력하시오.

SELECT ENAME, COMM, DNAME
FROM EMP, DEPT
WHERE DEPT.DEPTNO=EMP.DEPTNO AND COMM IS NOT NULL;

 

 

 

11. 이름에 'A' 가 들어가는 사원들의 이름과 부서명 출력하기.

SELECT ENAME, DNAME
FROM EMP, DEPT
WHERE DEPT.DEPTNO=EMP.DEPTNO AND ENAME LIKE '%A%';

 

12. DALLAS에 근무하는 사원 중 급여 1500 이상인 사원의 이름, 급여, 입사일 , 보너스(comm)을 출력하시오.

SELECT ENAME, SAL, HIREDATE, COMM
FROM EMP, DEPT
WHERE DEPT.DEPTNO=EMP.DEPTNO AND SAL>=1500;

13. 자신의 관리자 보다 연봉(sal)을 많이 받는 사원의 이름과 연봉을 출력하시오.

SELECT EN.ENAME, EN.SAL, MN.SAL
FROM EMP EN, EMP MN
WHERE EN.MGR=MN.EMPNO AND EN.SAL>MN.SAL;

14. 직원 중 현재시간 기준으로 근무 개월 수가 30년(12 * 30개월) 보다 많은 사람의 이름, 급여 , 입사일 , 부서명을 출력하시오

SELECT ENAME, SAL, HIREDATE, DNAME, TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE))
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO AND TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE))>360;

15. 각 부서별로 1982년 이전에 입사한 직원들의 인원수를 출력하시오.

--방법1--
SELECT DEPTNO, COUNT(ENAME), HIREDATE
FROM EMP
WHERE EXTRACT(YEAR FROM HIREDATE) <1982
GROUP BY DEPTNO, HIREDATE;

--방법2--
SELECT DEPTNO, COUNT(ENAME), HIREDATE
FROM EMP
WHERE SUBSTR(HIREDATE, 1, 2)<'82'
GROUP BY DEPTNO, HIREDATE;

--방법3--
SELECT DEPTNO, COUNT(ENAME), HIREDATE
FROM EMP
WHERE HIREDATE<'82/01/01'
GROUP BY DEPTNO, HIREDATE;

--방법4--
SELECT DEPTNO, COUNT(ENAME), HIREDATE
FROM EMP
WHERE TO_CHAR(HIREDATE, 'YY')<82
GROUP BY DEPTNO, HIREDATE;

 

 

 

서브쿼리

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

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

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

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

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

SELECT EMPNO, ENAME, SAL
FROM EMP
HAVING SAL > (SELECT AVG(SAL) FROM EMP)
GROUP BY EMPNO, ENAME, SAL;

4. 사원 테이블에서 부서별 최대 급여를 받는 사원들의 사번, 이름, 부서코드, 급여를 검색하시오.

SELECT EMPNO, ENAME, DEPTNO, SAL
FROM EMP
WHERE SAL = ANY(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

 

5. Salgrade가 2등급인 사원들의 평균 급여보다 적게 받는 사원 정보를 검색하시오.

SELECT *
FROM EMP, SALGRADE
WHERE SAL < (SELECT AVG(SAL)FROM EMP WHERE SAL BETWEEN 1201 AND 1400);