SQLD - Week 5 - 실전문제 Part2 - 2. SQL 활용

• <SQL 자격검정 실전문제> (일명 노랭이) 개정판 1회독 + 오답노트
  • 출제 비중이 높은 Part 2 부터 시작
• 오답노트하며 중요한 개념 정리

61번

• 계층형 질의에서 루트노드는 LEVEL 1부터 시작
  • START WITH 절에 해당하는 행은 조건절과 상관없이 결과에 출력됨
• START WITH 에서 지정한 행을 LEVEL 1로 지정
  • 홍길동, 이병헌 행이 LEVEL 1
• CONNECT BY절의 PRIOR 바로 다음에 있는 컬럼을 기준으로 ‘=’ 이후의 컬럼과 맞췄을때 같은 데이터가 있는 행이 LEVEL 2
  • 사원번호 001, 005와 같은 매니저사원번호 행이 LEVEL 2
  • 남은 6명 모두 LEVEL 2 => 매니저만 두고 모두 동일 레벨

64번

• WHERE 절은 계층형질의 전개 완료후에 필터링하기위한 조건절
  • 4번 FROM 절 안의 1)서브쿼리에서
    • START WITH 2)서브쿼리 의 ‘START WITH~’ 전개 후에 나오는 결과에서
      • WHERE 절에 해당하는 결과를 출력
      • 2)서브쿼리 결과: 100
• LEFT OUTER JOIN - ON 이랑 세트
• 한 세트(묶음끼리)를 잘 구분해서 정리

65번

• 문제에서 테이블을 직접 만드는 경우 대략적으로 결과테이블 그려놓기

SELECT *
FROM T1 A,
     T2 B

쿼리 실행 결과:

SELECT B.NO,
       A.COLA,
       B.COLB
FROM T1 A
CROSS JOIN T2 B

쿼리 실행 결과:

SELECT NO,
       A.COLA,
       B.COLB
FROM T1 A
NATURAL JOIN T2 B

쿼리 실행 결과:

• USING 절엔 ‘()’ 생략 불가!
• CROSS JOIN : 조건없이 조인할 경우 → 실행 가능한 모든 행 출력
  • A.NO만 출력했기 때문에 ‘NO = 1’ 만 출력됨
• NATURAL JOIN : 조인조건 없이 컬럼명이 같을때 사용
  • 조인조건 생략시 두 테이블에 같은 컬럼 이름이 있는 행을 연결(출력)

66번

• 셀프조인 SELF JOIN
  • 테이블의 행을 하나씩 만들어감

69번

윈도우함수

• RANK : 전체/특정 그룹 중 값의 순위
  • 값이 같으면 동일한 순위
  • 다음 순위는 동순위 개수에 따라 순위 부여
    • e.g. 2순위 3개 → 다음 순위: 5순위
• DENSE_RANK : 누적순위
  • 값이 같으면 동일한 순위
  • 다음 순위는 앞 순위와 바로 이어지는 순위 부여
    • e.g. 2순위 3개 → 다음 순위: 3순위
• CUME_DIST : 누적비율
• PERCENT_RANK : 분위수
  • 전체 COUNT 중 상대적 위치(0~1 범위 내)
• RATIO_TO_REPORT : 각 값의 비율 리턴
  • ORDER BY X
• NTILE : 행을 특정 컬럼 순서에 따라 정해진 수(N)의 그룹으로 나누기 위한 함수
  • SELECT NTILE(N) OVER ([PARTITION BY 컬럼] ORDER BY 컬럼 ASC | DESC)
  • 그룹 번호가 리턴
  • ORDER BY 필수

70번

• HAVING : GROUP BY 절의 필터링 조건절

SELECT 1

• 해당 테이블의 값을 1로 리턴
• WHERE 절에서 사용할 경우 조건을 만족하면 1 리턴
  • WHERE EXISTS (SELECT 1 ...)
• 실제값이 아닌 ‘존재 유무‘가 중요할때 사용

‘WHERE 0 <’의미

• 서브쿼리에서 리턴하는 행이 있는지를 확인하기 위함
• 리턴하는 행이 있으면 True : 해당 조건의 행 출력
• 리턴하는 행이 없으면 False : 아무행도 출력하지 X

77번

SELECT *
FROM 사원

SELECT 사원ID,
       ROW_NUMBER() OVER (PARTITION BY 부서ID
                          ORDER BY 연봉 DESC) AS COL1,
       SUM(연봉) OVER (PARTITION BY 부서ID
                     ORDER BY 사원ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS COL2,
       MAX(연봉) OVER(
                    ORDER BY 연봉 DESC ROWS CURRENT ROW) AS COL3
FROM 사원

SELECT 사원ID,
       COL2,
       COL3
FROM
  (SELECT 사원ID,
          ROW_NUMBER() OVER (PARTITION BY 부서ID
                             ORDER BY 연봉 DESC) AS COL1,
          SUM(연봉) OVER (PARTITION BY 부서ID
                        ORDER BY 사원ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS COL2,
          MAX(연봉) OVER(
                       ORDER BY 연봉 DESC ROWS CURRENT ROW) AS COL3
   FROM 사원)
WHERE COL1=2
ORDER BY 1

ROWS BETWEEN A AND B 구문 파헤쳐보기

• UNBOUNDED PRECEDING, CURRENT ROW를 어떻게 구분하지? 현재 시점과 처음 시점은 어디를 기준으로 잡을까?

SELECT 사원ID,
       부서ID,
       연봉,
       SUM(연봉) OVER (PARTITION BY 부서ID
                     ORDER BY 사원ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS col2,
       SUM(연봉) OVER (PARTITION BY 부서ID
                     ORDER BY 사원ID ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS col2_2
FROM 사원

• PARTITION된 영역 기준!
  • UNBOUNDED PRECEDING은 입력할 행의 위치와 상관없이 파티션된 영역의 첫번째 행을 의미
  • CURRENT ROW는 데이터를 채워야할/입력할 행의 위치! : 계속 이동한다
• COL2는 파티션영역내 첫행부터 현재행(이동위치)에 따라 합을 구하기떄문에 아래로 누적합 형태가 나오고,
• COL2-2는 현재행(이동위치)부터 파티션영역내 마지막 행까지의 합을 구하기때문에 합계가 점차 줄어드는 형태가 된다.

SELECT 사원ID,
       연봉
FROM 사원
ORDER BY 연봉 DESC

SELECT 사원ID,
       col3
FROM
  (SELECT 사원ID,
          max(연봉) over(
                       ORDER BY 연봉 DESC ROWS CURRENT ROW) AS col3
   FROM 사원)

• ROWS CURRENT ROW : BETWEEN+ AND CURRENT ROW 가 생략된것
  • = ROWS BETWEEN CURRENT ROW AND CURRENT ROW
• 결국 현재 행 내에서 MAX(연봉)을 구하기때문에 해당 행의 연봉이 그대로 출력된다

78번

GROUPING 함수

• Microsoft Learn의 SQL Server 페이지 설명은 다음과 같음

  Indicates whether a specified column expression in a GROUP BY list is aggregated or not.
  GROUPING returns 1 for aggregated or 0 for not aggregated in the result set.
  GROUPING can be used only in the SELECT <select> list, HAVING, and ORDER BY clauses when GROUP BY is specified.

• 공식 정의에 따르면 ‘GROUP BY 절에서 지정한 컬럼에 대한 집계생성 여부를 반환한다’는 의미
  • 해당 컬럼이 집계되었다면 0, 그 외 1
• ROLLUP, CUBE, GROUPING SETS 함수가 리턴하는 NULL과 일반 NULL을 구분하기 위해 사용
  • 위 함수들이 리턴하는 NULL은 column placeholder 역할을 함
• 하지만 이 설명은 뭔가 복잡함. 그래서 대부분 아래처럼 정의하는 듯함. 직관적으로 이해는 되지만 어쨌든 GROUPING 함수의 용도를 잘 기억하자!
  
• GROUP BY 결과로 “NULL이 생성된 경우” 1을 리턴하는 함수
  • GROUP BY 결과에 NULL이 있으면 : 1
• GROUP BY 절 필수!
  • 위 조건을 충족하면, SELECT 절 / HAVING 절 / ORDER BY 절에서만 사용가능
• MIN(B.지역명)은 아직 이해가 안됨…😣

79번

SELECT empno,
       sal
FROM emp
WHERE sal >=
    (SELECT max(sal)
     FROM emp
     GROUP BY deptno)

쿼리 실행 중 오류 발생:
ORA-01427: single-row subquery returns more than one row
Help: https://docs.oracle.com/error-help/db/ora-01427/

80번

• GROUP BY FUNCTION 종류별로 정리 꼼꼼히!!
  • 여러 GROUP BY 결과를 동시에 출력(합집합)

GROUPING SETS(A, B, …)

• A, B별 그룹 연산 결과 출력
  • GROUPING SETS에 나열한 대상에 대해 각 GROUP BY 결과를 출력
• 나열 순서 중요하지X
• 전체 총계는 출력X
  • 전체 총계 출력하려면 : NULL 혹은 () 사용
• ’= UNION ALL’ : 빈 컬럼 NULL로 맞춰줘야함

ROLLUP(A, B)

• A별, (A, B)별, 전체 그룹 연산 결과 출력
• 나열 대상의 순서 중요!
  • 나열된 컬럼의 계층 구조로 집계 출력
• UNION ALL로 대체 가능 : 빈 컬럼 NULL로 맞춰줘야함

CUBE(A,B)

• A별, (A, B)별, 전체 그룹 연산 결과 출력
• 모든 조합의 경우의 수 모두 출력
• 나열 대상의 순서 중요X
• UNION ALL, GROUPING SETS로 대체 가능

82번

• 원래 ROLLUP (JOB, DEPTNO)를 하면 다음처럼 주어진 컬럼명 나열순을 기준으로 레벨별 집계가 반환된다

SELECT A.JOB,
       A.DEPTNO,
       SUM(SAL)
FROM EMP A
GROUP BY ROLLUP (JOB,
                 DEPTNO)

• 하지만 ROLLUP ((A, B)) 이런식으로 괄호를 두 번 감싸면 컬럼별 집계와 맨 마지막 전체 집계만 출력가능

SELECT A.JOB,
       A.DEPTNO,
       SUM(SAL)
FROM EMP A
GROUP BY ROLLUP ((JOB,
                  DEPTNO))

• 문제에서는 평균값을 출력하는 행 하나만 있기때문에 괄호 2개로 전체 총합 소계만 출력하는게 필요

83번

• GROUPING SETS ((A, B)) : A, B 두개의 컬럼을 그룹으로 만들어 소계를 집계
  • GROUP BY A, B 와 동일한 결과
• 참고 : 블로그

SELECT job,
       deptno,
       count(*) cnt
FROM emp
GROUP BY GROUPING
SETS ((job,
       deptno))

SELECT job,
       deptno,
       count(*) cnt
FROM emp
GROUP BY job,
         deptno

• GROUPING SETS (A, B) 는 명시된 컬럼별 소계를 출력함

SELECT job,
       deptno,
       count(*) cnt
FROM emp
GROUP BY GROUPING
SETS (Job,
      deptno)

85번

• RANK : 동순위 데이터 수에 따라 다음 순위 결정됨
• DENSE_RANK : 누적순위 - 동순위 다음 순위가 바로 이어짐
• ROW_NUMBER : 연속된 행번호 - 동순위 인정X, 나열 순서대로 순서 값 출력

90번

LAG

• 바로 이전 행 결과 출력

  LEAD

• 바로 다음 행 결과 출력

91번

권한 부여 옵션 - 중간관리자 권한

• WITH GRANT OPTION : 오브젝트 권한을 다른 사용자에게 부여 -> 이 사용자는 중간관리자가 됨
  • 중간관리자가 부여한 권한은 중간관리자만 회수 가능
  • 중간관리자에게 부여된 권한 회수 시 제 3 자에게 부여된 권한도 함께 회수됨
• WITH ADMIN OPTION : 시스템 권한/롤 권한을 다른 사용자에게 부여 가능 -> 이 사용자는 중간관리자가 됨
  • 중간관리자를 거치지 않고 직접 회수 O
  • 중간관리자 권한 회수 : 제3자에게 부여된 권한 함께 회수 X(남아있음)

96번

• INTERSECT : SQL 결과에 대한 교집합 출력
  • 중복된 행은 하나만 출력

97번

LAG

• LAG (scalar_expression [, offset [, default ]]) OVER ( [ partition_by_clause ] order_by_clause)
  • offset : The number of rows back from the current row from which to obtain a value. If not specified, the default is 1.
• 출처 : Geeksforgeeks