SQL 자격검정 실전문제 과목2 문제풀이81~98

정답 1번

해당 문제는 (설비ID, 에너지코드) 상세 + 설비별 소계 + 에너지코드별 소계 + 전체 총계까지 전부 보여주고 있다

따라서 결과표에 나와야하는 행의 종류는 아래의 총 4가지다

• 상세 합계: (설비ID, 에너지코드) 별 합계
• 설비 소계: (설비ID) 별 합계 → 에너지코드 = NULL
• 에너지 소계: (에너지코드) 별 합계 → 설비ID = NULL
• 총계: () 전체 합계 → 둘 다 NULL

GROUP BY CUBE(A, B) 는 A별 합계, B별 합계, A+B별 합계, 전체 합계를 한 번에 만들어준다

기준 컬럼이 2개면 조합이 4개(2²)고, 기준 컬럼이 3개면 조합이 8개(2³)로 늘어난다

• CUBE(A, B) → 2² = 4개 집계
• CUBE(A, B, C) → 2³ = 8개 집계
  • (A,B,C), (A,B), (A,C), (B,C), (A), (B), (C), ()

즉 기준이 늘수록 “모든 조합”의 소계를 뽑아준다

따라서 CUBE(설비ID, 에너지코드) = (설비ID, 에너지코드) + (설비ID) + (에너지코드) + ( ) 를 가지고 있는 2번이 정답이다

1번도 CUBE를 사용하긴하나 (설비ID, 에너지코드)값이 중복으로 들어가있어 중복값이나 추가집계가 생길수있다

3번은 GROUPING SETS(설비ID, (설비ID, 에너지코드))로 에너지코드 소계, 총계가 없고

4번은 GROUPING SETS(설비ID, 에너지코드, (설비ID, 에너지코드))로 총계()가 없어서 맨 아래 전체합계 행이 안 나온다

정답 2번

GROUP BY ROLLUP(A, B) 이란 (A,B)별 결과를 만들고, 그 위에 A별 소계, 마지막에 전체 총계까지 만들어준다

왼쪽부터 단계적으로 접어 올리고 ROLLUP((A, B))처럼 괄호로 묶으면 (A,B)를 한 덩어리로 취급한다 따라서 소계없이 총계만 생긴다

ROLLUP(A, B)는 단계적으로 말아올리며 소계가 추가된다 A 소계가 생기며 A, NULL 같은 행이 추가된다

따라서 답은 2번이다

✔️위 문제에 나온 CUBE랑 차이

• ROLLUP(A, B): 단계적으로 올라감 → (A,B), (A), ()
• CUBE(A, B): 가능한 조합 다 만듦 → (A,B), (A), (B), ()
  

정답 2번

이문제의 핵심은 이것이다

• “상품ID와 월을 같이 묶어서 집계해라”
• 다른 소계(월만 / 상품만 / 전체총계)는 만들지 마라

따라서 이미 원본 데이터가 상품ID와 월이 같이 묶여있기 때문에 여기서 (상품ID, 월)로 그룹을 묶고 SUM(매출액)을 해도,
각 그룹이 1건짜리라서 그대로 같은 값이 나온다

따라서 원본과 같은 2번이 정답이다

1번이 의미하는 건 ‘소계’로

• (NULL, 2014.10, 4500) → 월(2014.10) 전체 합계
• (P001, NULL, 5500) → 상품(P001) 전체 합계

이런 건 (상품ID, 월)로만 그룹핑하면 절대 안 나온다 왜냐면 저 행들은 각각

• GROUP BY 월
• GROUP BY 상품ID

를 추가로 해야 나오는 행이기 때문이다 따라서 GROUPING SETS(상품ID, 월)의 형태가 되기때문에 월별과 상품아이디별로 집계되었다

3번의 경우 GROUPING SETS에 월별 ,상품아이디별과 전체가 각각 집계되었다

4번의 경우 GROUPING SETS에 (상품ID, 월)별,월별로 집계되었다

정답 3번

윈도우 함수는 OVER 키워드와 함께 사용되고 집계값(누적합, 순위, 평균 등)을 각 행에 붙여주는 방식이다 그래서 원래 조회되던 행 수가 그대로 유지된다

 GROUP BY

• 같은 그룹을 1행으로 합침
• 결과 건수가 줄어듦

 

 PARTITION BY

• 같은 그룹을 나누긴 하지만 행은 그대로 유지
• 계산값(평균/합계/순위 등)을 각 행에 붙여줌

따라서 정답은 3번이다

정답 4번

RANK vs DENSE_RANK 차이

RANK()

• 동점이면 같은 등수
• 다음 등수는 동점 개수만큼 건너뜀(빈 번호 생김)

예) 점수: 100, 90, 90, 80

• RANK: 1, 2, 2, 4 ← 3등이 비어버림

DENSE_RANK()

• 동점이면 같은 등수
• 다음 등수는 바로 다음 번호(빈 번호 없음)

예) 점수: 100, 90, 90, 80

• DENSE_RANK: 1, 2, 2, 3

현재 문제에서는 공동 2등 다음에 3등이 존재하므로 이것은 DENSE_RANK()에 해당한다 따라서 정답은 4번이다

정답 3번

이문제는 추천경로(그룹)마다 추천점수가 가장 높은 1건만 뽑는 쿼리다

• 1번은 “원본 전체”라서 RNUM=1 조건을 반영하지 못한다
• 2번은 “전체에서 1등 1건만” 뽑은 형태인데, 이 쿼리는 경로별 1등을 뽑는다
• 4번은 각 경로의 최저점(혹은 정렬 방향을 반대로 이해한) 느낌이라서 쿼리와 반대다

따라서 경로별 1등을 선별한 3번이 정답이다

정답 1번

이 문제는 “부서별 최고연봉 사원”을 뽑는 쿼리라서 부서 100/200/300에서 각각 1명씩 나와야 한다 따라서 각 부서별 최고연봉이 있는 1번이 정답이고 3번의 경우 부서200과 300에 최고연봉자가 있지만 부서100의 최고 연봉자가 없기때문에 틀렸다

✔️LEG 함수란 현재 행을 기준으로 이전 행의 데이터를 가져오는 윈도우 함수다

현재행 기준 이후의 행을 가져오는것을 LEAD함수라고 한다 SQL 서버에서는 지원하지 않는 함수다

정답 3번

WITH GRANT OPTION이란 어떤 권한을 WITH GRANT OPTION으로 받으면,나도 그 권한을 쓸 수 있고 다른 사람에게 그 권한을 줄 수도 있다(GRANT 가능)

REVOKE의 핵심은 연쇄 취소(CASCADE)다 

• A가 B에게 권한을 줬고,
• B가 그 권한을 C에게 다시 줬는데,
• 나중에 A가 B의 권한을 REVOKE로 취소하면,

B가 C에게 준 권한도 같이 취소되는 게 원칙이다
(단, C가 같은 권한을 다른 경로(다른 사용자/롤)로도 이미 받고 있었다면 그 “다른 경로” 때문에 권한이 남을 수는 있다.

즉 WITH GRANT OPTION = 권한을 ‘재배포’할 수 있는 권리이고 REVOKE = 재배포된 권한까지 ‘연쇄 회수’가 기본이다

따라서 3번이 정답이 된다

정답 4번

문제에서 B_User가 해야 하는 작업이 다른 사용자(A_User)가 소유한 테이블 A_User.TB_A에 대해 UPDATE를 수행하는 거라서, 권한을 줄 때 객체(테이블)를 정확히 지정해서 UPDATE 권한을 부여해야 하기 때문이다

1번은 어느 테이블에 대한 권한인지(ON ...)가 없다 따라서 문법/의미 모두 불완전하다

2번은 REVOKE ...권한을 주는 게 아니라 빼는 문장이다

3번 DENY ... Oracle DCL에는 일반적으로 DENY 문이 없다(주로 SQL Server 계열에서 쓰는 표현)

4번은 객체를 정확히 지정했고(ON A_User.TB_A) 필요한 권한(UPDATE)을 포함해서 B_User에게 권한 부여가 맞게 작성됨

그래서 정답이 4번이다

정답 1번

1번 Lee → Kim 권한 부여 (WITH GRANT OPTION)

• Kim은 R에 대해 SELECT/INSERT/DELETE 가능
• 그리고 다른 사람에게도 줄 수 있음(GRANT OPTION)

2번 Kim → Park 권한 부여

• Park는 Kim에게서 SELECT/INSERT/DELETE 권한을 받음
  (단, Park는 보통 다시 남에게 줄 권한까지는 없음)

3번 Lee가 Kim의 DELETE 회수 (CASCADE)

• Kim의 DELETE 권한 삭제
• CASCADE 때문에 Kim이 Park에게 줬던 DELETE도 같이 회수됨

4번 Lee가 Kim의 INSERT 회수 (CASCADE)

• Kim의 INSERT 권한 삭제
• CASCADE 때문에 Park의 INSERT도 같이 회수됨

따라서 현재 남은 권한이 1번 park이 사용할 SELECT... 이기때문에 1번이 정답이다