• 7주차 과제 - 집합 연산자, 계층
  • 실습 환경 : Oracle

  • 해결 날짜 : 2021-04-17

  • 과제

    • 7-1) [예제1]을 참고하여 EMP 테이블에서 UNION 연산을 실행하는 한글 질의문과 SQL문을 작성하라. [1점]

      • 한글 질의문 :사원 중 JOB이 CLERK인 사원들의 집합과 사원 중 JOB이 SALESMAN인 사원들의 집합의 합집합을 구하여라.

      • SQL문 :

      SELECT *
FROM EMP
WHERE JOB = 'CLERK'
UNION
SELECT *
FROM EMP
WHERE JOB = 'SALESMAN';
      • 실행 결과 :

      image

    • 7-2) [예제5]를 참고하여 EMP 테이블에서 MINUS 연산을 실행하는 한글 질의문과 SQL문을 작성하라. [1점]

      • 한글 질의문 : 사원 중 JOB이 SALESMAN인 사원들의 집합에서 사원 중 월급이 1300 미만이 아닌 사원들의 집합을 구하여라.

      • SQL문 :

      SELECT *
FROM EMP
WHERE JOB = 'SALESMAN'
MINUS
SELECT *
FROM EMP
WHERE SAL < 1300;
      • 실행 결과 :

      image

    • 7-3) [예제1]을 참고하여 순방향 전개를 나타내는 계층형 SQL문을 한글 질의문과 함께 작성하라. (EMP 테이블을 사용할 경우 JOB을 출력하도록 함) [2점]

      • 한글 질의문 : EMP 테이블에서 상위 사원과 하위 사원의 관계를 순방향 전개하여 표시하되, 월급이 1000 이상인 사원만 출력하여라.

      • SQL문 :

      SELECT JOB, LEVEL, LPAD(' ', 4 * (LEVEL-1)) || EMPNO 사원, MGR 관리자, SAL 월급,
    CONNECT_BY_ISLEAF ISLEAF
FROM EMP
WHERE SAL >= 1000
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;
      • 실행 결과 :

      image

    • 7-4) [예제3]을 참고하여 역방향 전개를 나타내는 계층형 SQL문을 한글 질의문과 함께 작성하라. (EMP 테이블을 사용할 경우 JOB을 출력하도록 함) [2점]

      • 한글 질의문 : EMP 테이블에서 상위 사원과 하위 사원의 관계를 역방향 전개하여 표시하되, 월급이 1200 초과인 사원만 출력하여라.

      • SQL문 :

      SELECT JOB, LEVEL, LPAD(' ', 4*(LEVEL-1)) || EMPNO 사원, MGR 관리자,
    CONNECT_BY_ISLEAF ISLEAF
FROM EMP
WHERE SAL > 1200
START WITH JOB = 'CLERK'
CONNECT BY PRIOR MGR = EMPNO;
      • 실행 결과 :

      image

    • 7-5) [예제5]를 참고하여 셀프 조인을 수행하는 SQL문을 한글 질의문과 함께 작성하라. [2점]

      • 한글 질의문 : EMP 테이블에서 셀프 조인을 통해 연봉이 15000 이상인 사원의 직속 관리자와 차상위 관리자를 구하여 출력하여라.

      • SQL문 :

      SELECT E1.ENAME 사원명, E1.JOB 직책, E1.EMPNO "사원 번호", (E1.SAL*12) + NVL(E1.COMM,0) 연봉,
        E1.MGR "직속 관리자 번호", E2.MGR "차상위 관리자 번호"
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO
    AND (E1.SAL*12) + NVL(E1.COMM,0) >= 15000
      • 실행 결과 :

      image

    • 7-6) [예제6]를 참고하여 [과제7-5]에서 만든 질의문을 최상위 레벨도 출력되도록 변경하라. [2점]

      • 한글 질의문 : EMP 테이블에서 셀프 조인을 통해 연봉이 15000 이상인 사원의 직속 관리자와 차상위 관리자를 구하되, 직속 관리자가 없는 최상위 관리자도 나오게 출력하여라.

      • SQL문 :

      SELECT E1.ENAME 사원명, E1.JOB 직책, E1.EMPNO "사원 번호", (E1.SAL*12) + NVL(E1.COMM,0) 연봉,
        E1.MGR "직속 관리자 번호", E2.MGR "차상위 관리자 번호"
FROM EMP E1 LEFT OUTER JOIN EMP E2
ON (E1.MGR = E2.EMPNO)
WHERE (E1.SAL*12) + NVL(E1.COMM,0) >= 15000;
      • 실행 결과 :

      image