• 9주차 과제 - 그룹 함수, 윈도우 함수
  • 실습 환경 : Oracle

  • 해결 날짜 : 2021-05-01

  • 과제

    • 9-1. [예제12]를 참고하여 PLAYER 테이블에서 팀ID 별, 포지션 별 선수들 수의 합계를 구하는 SQL문을 작성하고 그 결과를 출력하라. (단, 포지션이 NULL인 데이터는 제외하고 팀ID 순으로 정렬한다.) [1점]

      • SQL문 :
      SELECT
    DECODE(GROUPING(TEAM_ID),1,'All Team', TEAM_ID) AS "팀 아이디",
    DECODE(GROUPING(POSITION),1,'All Position', POSITION) AS "포지션",
    COUNT(*) "선수 수 총합"
FROM PLAYER
WHERE POSITION IS NOT NULL
GROUP BY GROUPING SETS (TEAM_ID, POSITION)
ORDER BY TEAM_ID;
      • 실행 결과 :

      image

    • 9-2. [예제9]를 참고하여 PLAYER 테이블에서 CUBE 함수를 이용하는 한글 질의문과 SQL문을 작성하고 그 결과를 출력하라. [2점]

      • 한글 질의문 : PLAYER 테이블에서 팀ID 별, 포지션 별 선수들 수의 합을 구하여 출력하여라. (단, CUBE 함수를 사용하시오)

      • SQL문 :

      SELECT
    CASE GROUPING(TEAM_ID)
        WHEN 1 THEN 'ALL Team'
        ELSE TEAM_ID END AS "팀 아이디",
    CASE GROUPING(POSITION)
        WHEN 1 THEN 'ALL POSITION'
        ELSE POSITION END AS "포지션",
    COUNT(*) "선수 수 총합"
FROM PLAYER
WHERE POSITION IS NOT NULL
GROUP BY CUBE (TEAM_ID, POSITION);
      • 실행 결과 :

      image

    • 9-3. [예제1], [예제2]를 참고하여 PLAYER 테이블에서 팀ID, 소속선수 수, 소속 선수 수가 많은 순으로 팀 순위를 구하는 SQL문을 작성하고 그 결과를 출력하라. (단, 하나의 SQL문으로 작성되어야 하며, 팀 순위는 동일한 값에 대해서 동일한 순서를 부여하는 것과 동일한 순위를 하나의 건수로 취급하는 것 두 가지 방법을 모두 사용하여 출력한다.) [1점] (하나의 쿼리에 짜야함)

      • SQL문 :
      SELECT TEAM_ID "소속팀 ID", COUNT(TEAM_ID) "소속 선수 수",
    RANK() OVER (ORDER BY COUNT(TEAM_ID) DESC) ALL_RANK,
    DENSE_RANK() OVER (ORDER BY COUNT(TEAM_ID) DESC) DENSE_RANK
FROM PLAYER
GROUP BY TEAM_ID;
      • 실행 결과 :

      image

    • 9-4. [예제4-1], [예제5]를 참고하여 PLAYER 테이블에서 선수들의 소속팀ID, 선수이름, 선수의 키, 소속팀 선수들 중 최장신 선수의 키, 최단신 선수의 키를 출력하는 SQL문을 작성하고 그 결과를 출력하라. [1점]

      • SQL문 :
      SELECT TEAM_ID "소속팀 ID", PLAYER_NAME "선수명", HEIGHT "선수 키",
    MAX(HEIGHT) OVER (PARTITION BY TEAM_ID) AS "소속팀의 최장신",
    MIN(HEIGHT) OVER (PARTITION BY TEAM_ID) AS "소속팀의 최단신"
FROM PLAYER;
      • 실행 결과 :

      image

    • 9-5. [예제7]을 참고하여 PLAYER 테이블에서 같은 연도에 입단한 선수들의 평균 몸무게를 출력하는 SQL문을 작성하고 그 결과를 출력하라. (단, 평균 몸무게는 소수점 첫째자리까지 표시한다.) [1점]

      • SQL문 :
      SELECT PLAYER_ID "선수 ID", PLAYER_NAME "선수명",
    JOIN_YYYY "입단년도", WEIGHT "몸무게",
    ROUND(AVG(WEIGHT) OVER (PARTITION BY JOIN_YYYY ORDER BY JOIN_YYYY), 1)
                            AS "해당 선수와 입단년도가 같은 선수들의 평균 몸무게"
FROM PLAYER
WHERE JOIN_YYYY IS NOT NULL AND WEIGHT IS NOT NULL;
      • 실행 결과 :

      image

    • 9-6. [예제9], [예제10]을 참고하여 PLAYER 테이블에서 포지션 별로 선수들의 입단연도가 빠른 순으로 포지션명, 선수이름, 입단연도, 포지션 내에서 가장 입단연도가 빠른 선수와 가장 늦은 선수이름을 출력하는 SQL문을 작성하고 그 결과를 출력하라. (단, 입단연도가 같으면 이름 순으로 정렬한다.) 2점

      • SQL문 :
      SELECT POSITION "포지션", PLAYER_NAME "선수명", JOIN_YYYY "입단년도",
    FIRST_VALUE(PLAYER_NAME) OVER
        (PARTITION BY POSITION ORDER BY JOIN_YYYY, PLAYER_NAME
        ROWS UNBOUNDED PRECEDING)
        AS "포지션 내에서 입단년도가 가장 빠른 선수",
    LAST_VALUE(PLAYER_NAME) OVER
        (PARTITION BY POSITION ORDER BY JOIN_YYYY, PLAYER_NAME
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
        AS "포지션 내에서 입단년도가 가장 느린 선수"
FROM PLAYER
WHERE JOIN_YYYY IS NOT NULL;
      • 실행 결과 :

      image

    • 9-7. [예제17]을 참고하여 PLAYER 테이블에서 NTILE 함수를 이용하는 한글 질의문과 SQL문을 작성하고 그 결과를 출력하라. [2점]

      • 한글 질의문 : PLAYER 테이블에서 선수들을 포지션 별로 키가 큰 순으로 정렬하고, 포지션 별로 키를 기준으로 5개의 그룹으로 분류하여라.

      • SQL문 :

      SELECT POSITION 포지션, PLAYER_NAME 선수명, HEIGHT "선수 키",
        NTILE(5) OVER (PARTITION BY POSITION ORDER BY HEIGHT DESC) AS "포지션 별 키 그룹"
FROM PLAYER
WHERE HEIGHT IS NOT NULL;
      • 실행 결과 :

      image image