• 8주차 과제 - 서브쿼리
  • 실습 환경 : Oracle

  • 해결 날짜 : 2021-04-26

  • 과제

    • 8-1. 슬라이드 301페이지에 있는 질의문을 참고하여 EMP 테이블에서 단일행 서브쿼리를 포함하는 SQL문을 한글 질의문과 함께 작성하라. [1점]

      • 한글 질의문 : EMP 테이블에서 이름이 ‘ALLEN’인 사원의 월급 이상의 월급을 갖고 있는 사원들을 이름 순으로(오름차순) 정렬해 사원명, 역할, 월급을 출력하여라.

      • SQL문 :

      SELECT ENAME 사원명, JOB 역할, SAL 월급
FROM EMP
WHERE SAL >= (SELECT SAL
                FROM EMP
                WHERE ENAME = 'ALLEN')
ORDER BY ENAME;
      • 실행 결과 :

      image

    • 8-2. [예제3]을 참고하여 다중칼럼 서브쿼리를 사용해서 EMP, DEPT 테이블을 대상으로 부서별로 가장 최근에 입사한 사원의 사원번호, 사원이름, 입사일자 및 부서이름을 출력하는 SQL문을 작성하라. [2점]

      • SQL문 :
      SELECT E.EMPNO 사원번호, E.ENAME 사원이름, E.HIREDATE 입사일자, D.DNAME 부서이름
FROM EMP E, DEPT D
WHERE D.DEPTNO = E.DEPTNO
AND (D.DEPTNO, E.HIREDATE) IN (SELECT E2.DEPTNO, MAX(HIREDATE)
                                FROM EMP E2
                                GROUP BY E2.DEPTNO)
ORDER BY D.DEPTNO;
      • 실행 결과 :

      image

    • 8-3. [예제4]를 참고하여 EMP 테이블을 대상으로 연관 서브쿼리를 포함하는 SQL문을 한글 질의문과 함께 작성하라. [2점]

      • 한글 질의문 : EMP, DEPT 테이블에서 부서 번호가 같고 부서 별 평균 월급 보다 많은 월급을 받고 있는 사원들을 부서 번호(오름차순)로 정렬하여 사원번호, 사원이름, 월급, 부서이름을 출력하여라.(단, 연관 서브쿼리를 사용하여라)

      • SQL문 :

      SELECT E.EMPNO 사원번호, E.ENAME 사원이름, E.SAL 월급, D.DNAME 부서이름
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND E.SAL > (SELECT AVG(E2.SAL)
            FROM EMP E2
            WHERE E2.DEPTNO = D.DEPTNO
            GROUP BY E2.DEPTNO)
ORDER BY D.DEPTNO;
      • 실행 결과 :

      image image

    • 8-4. [예제9]~[예제12]를 참고하여 PLAYER, TEAM 테이블을 대상으로 90년 이후에 출생한 선수들에 대한 INLINE VIEW를 정의하고, 이로부터 각 팀별로 팀 이름, 포지션별 평균키를 출력하는 SQL문을 작성하라. (단, 평균키는 소수점 첫째 자리까지 표시하고 팀 이름을 기준으로 오름차순으로 정렬하여 출력하라.) [2점]

      • SQL문 :
      SELECT T.TEAM_ID "팀 이름", P.POSITION 포지션, P.AVG_HEIGHT "포지션별 평균 키"
FROM (SELECT TEAM_ID, POSITION, ROUND(AVG(HEIGHT), 1) AVG_HEIGHT
    FROM PLAYER
    WHERE EXTRACT(YEAR FROM BIRTH_DATE) >= 1990
    GROUP BY POSITION, TEAM_ID) P, TEAM T
WHERE T.TEAM_ID = P.TEAM_ID
ORDER BY T.TEAM_ID;
      • 실행 결과 :

      image

    • 8-5. 슬라이드 315페이지에 있는 질의문을 참고하여 EMP 테이블과 DEPT 테이블을 조인하는 뷰를 생성하고 [예제13]과 같이 생성된 뷰를 사용하여 데이터를 조회하는 SQL문을 한글 질의문과 함께 작성하라. [2점]

      • 한글 질의문 : EMP, DEPT 테이블을 레프트 조인하여 뷰를 생성하고, 생성된 뷰에서 사원명, 역할, 월급, 부서번호, 부서명을 출력하되 역할을 기준으로 오름차순으로 정렬하여 월급이 1500 이상인 사원을 출력하여라.

      • SQL문 :

      CREATE VIEW V_EMP_DEPT AS
SELECT P.ENAME 사원명, P.JOB 역할, P.SAL 월급, D.DEPTNO 부서번호, D.DNAME 부서명
FROM EMP P LEFT OUTER JOIN DEPT D
        ON P.DEPTNO = D.DEPTNO;

SELECT 사원명, 역할, 월급, 부서번호, 부서명
FROM V_EMP_DEPT
WHERE 월급 >= 1500
ORDER BY 역할;
      • 실행 결과 :

      image image

    • 8-6. 슬라이드 320페이지에 있는 질의문을 참고하여 Select List 항목으로 위치하는 스칼라 서브쿼리를 포함하는 SQL문을 한글 질의문과 함께 작성하라. [1점]

      • 한글 질의문 : PLAYER 테이블에서 TEAM 테이블을 스칼라 서브쿼리로 사용하여 TEAM 테이블에서 팀명을, PLAYER 테이블에서 선수번호, 선수명, 포지션을 출력하되, 포지션이 골키퍼인 선수들만 출력하여라.

      • SQL문 :

      SELECT PLAYER_ID 선수번호, PLAYER_NAME 선수명, POSITION 포지션,
    (SELECT TEAM_NAME FROM TEAM WHERE TEAM_ID = P.TEAM_ID) 팀명
FROM PLAYER P
WHERE POSITION = 'GK';
      • 실행 결과 :

      image