단원 모의 문제

② — 서브쿼리는 김민준이 관리하는 강도윤 470과 최유나 450, 두 행을 돌려줍니다. > ANY는 그 가운데 어느 하나보다 크면 참이라서, 가장 작은 450보다 많이 받는 김민준과 강도윤이 나옵니다.

• ① > ANY를 > ALL처럼 봤습니다. 두 값 모두보다 커야 하는 것은 > ALL이고, 그러면 470보다 많은 김민준 1명뿐입니다.
• ③ >를 >=처럼 봐서 최유나도 남는다고 봤습니다. 최유나의 450은 450보다 크지 않고 470보다도 작아서, 최유나 줄에서는 조건이 참이 되지 않습니다.
• ④ ANY를 EXISTS처럼, 서브쿼리에 행이 있기만 하면 참이라고 봤습니다. ANY는 돌려받은 값과 하나씩 견주므로, 450·470과 견줘 2명만 남습니다.

다시 볼 레슨: 서브쿼리

① — (가)는 HAVING 절에 서브쿼리를 둔 쿼리입니다. 서브쿼리가 전체 평균 한 값을 돌려주므로 >와 쓸 수 있고, 부서 평균이 전체 평균(2530 ÷ 6)보다 높은 10번과 30번이 나옵니다.

• ② 서브쿼리가 한 행만 돌려준다고 봤습니다. 20번 부서 사원은 세 명이라 월급 세 행이 오고, 값 하나만 받는 = 자리라서 ORA-01427 오류입니다.
• ③ 메인 쿼리에서도 서브쿼리 쪽 테이블의 열을 쓸 수 있다고 봤습니다. DNAME은 WHERE 절 서브쿼리에만 쓴 DEPT의 열이라 ORA-00904 오류이고, 부서 이름이 필요하면 조인으로 씁니다.
• ④ 서브쿼리 안에서도 정렬할 수 있다고 봤습니다. WHERE 절 서브쿼리 안에는 ORDER BY를 쓸 수 없어 오류(ORA-00907)이고, 안에서 정렬할 수 있는 것은 인라인 뷰입니다.

SELECT DEPTNO,
  AVG(SAL) AS AVG_SAL
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL) > (
  SELECT AVG(SAL)
  FROM EMP
)
ORDER BY DEPTNO;

20번 부서의 평균 420은 전체 평균보다 조금 낮아서 빠집니다.

다시 볼 레슨: 서브쿼리

② — Oracle은 집합 연산자를 모두 같은 우선순위로 보고 왼쪽부터 처리합니다. 먼저 UNION이 서울의 10·40과 월급 400 미만인 이서연의 20·정하늘의 NULL을 합칩니다. 이어서 INTERSECT가 관리자가 있는 사원들의 부서 20·30·NULL에도 있는 20과 NULL만 남깁니다.

• ① NULL끼리는 같지 않다고 봐서 INTERSECT에서 NULL을 뺐습니다. 집합 연산자는 중복을 가릴 때 NULL끼리도 같은 값으로 봐서, 두 결과에 다 있는 NULL이 남습니다.
• ③ 두 오해가 겹쳤습니다. INTERSECT를 먼저 처리했고, NULL끼리는 다르다고 봐서 10·20·40이 됩니다.
• ④ SQL Server처럼 INTERSECT를 먼저 처리했습니다. 뒤의 두 SELECT가 먼저 20·NULL을 내고 서울의 10·40과 합쳐 4행이 되지만, Oracle은 왼쪽의 UNION부터 처리합니다.

섞어 쓸 때는 괄호로 처리 순서를 밝힙니다.

다시 볼 레슨: 집합 연산자

③ — ROLLUP 밖에 쓴 MGR은 모든 줄에서 묶이고, ROLLUP(DEPTNO)는 (DEPTNO)와 ()를 냅니다. 둘을 합치면 묶음은 (MGR, DEPTNO)와 (MGR)이라, 상세 줄 3개와 관리자별 소계 2개가 나오고 전체 합계 줄은 없습니다.

• ① ROLLUP(DEPTNO)가 DEPTNO별 소계를 낸다고 봤습니다. ROLLUP은 괄호 안 열을 오른쪽부터 비워 가므로, 여기서 비는 쪽이 DEPTNO이고 소계는 MGR별로 나옵니다.
• ② 두 오해가 겹쳤습니다. 소계는 DEPTNO가 아니라 MGR별이고, MGR이 늘 묶여 있어 전체 합계 묶음 ()도 생기지 않습니다.
• ④ ROLLUP이 늘 전체 합계 줄을 붙인다고 봤습니다. ROLLUP 밖의 MGR은 비워지지 않아서, 모든 열을 비운 () 묶음이 생기지 않습니다.

③의 GROUPING SETS로 바꿔 돌려도 이 다섯 줄이 그대로 나옵니다.

다시 볼 레슨: 그룹 함수

① — 관리자가 없는 김민준은 MGR이 NULL인 그룹 하나로 묶이고, 이 줄은 합계 줄이 아니라서 GROUPING(MGR)이 0입니다. 그래서 DECODE가 '전체'가 아닌 MGR 값 NULL을 내고, TOTAL은 김민준의 520입니다.

• ② GROUPING의 1과 0을 거꾸로 봤습니다. 모든 행을 더한 2530은 MGR을 비운 전체 합계 줄이라 GROUPING(MGR)이 1이고, LABEL이 '전체'입니다.
• ③ 묶는 열이 NULL이면 합계도 NULL이라고 봤습니다. MGR이 NULL인 김민준도 한 그룹으로 묶이고, 그 그룹의 SAL 520이 그대로 더해집니다.
• ④ DECODE가 MGR이 NULL인 줄을 모두 '전체'로 바꾼다고 봤습니다. '전체'가 붙는 것은 GROUPING(MGR)이 1인 합계 줄뿐이고, 데이터에 원래 있던 김민준의 NULL은 0이라 NULL 그대로 나옵니다.

다시 볼 레슨: 그룹 함수

④ — 윈도우 함수는 행을 줄이지 않아 6행이 그대로 나옵니다. PARTITION BY DEPTNO는 10·20·30번과 DEPTNO가 NULL인 정하늘까지 네 칸을 만들고, 칸마다 순위를 1부터 다시 매겨 1위가 넷입니다.

• ① 두 오해가 겹쳤습니다. 칸마다 한 줄로 접힌다고 봤고, NULL은 칸이 되지 않는다고 봤습니다. 행 수는 그대로 6행이고, NULL끼리도 한 칸이 됩니다.
• ② PARTITION BY가 GROUP BY처럼 칸마다 한 줄로 접는다고 봤습니다. 윈도우 함수는 행 수를 그대로 두고 행마다 순위를 붙입니다.
• ③ DEPTNO가 NULL인 정하늘은 칸에 들지 못한다고 봤습니다. PARTITION BY에서는 NULL끼리도 한 칸이 되어, 혼자인 칸에서 정하늘이 1위입니다.

다시 볼 레슨: 윈도우 함수

③ — OVER에 ORDER BY만 쓰면 창은 첫 행부터 현재 행까지라서, 동점이 없는 최유나 줄의 A는 창의 마지막 행인 자신의 450입니다. B는 창을 칸의 끝까지 넓혀, 월급 내림차순의 마지막 행인 정하늘의 300을 가져옵니다.

• ① LAST_VALUE가 범위와 상관없이 늘 칸의 끝 값을 낸다고 봤습니다. 범위를 적지 않은 A의 창은 현재 행에서 끝나서, 창의 끝 값은 최유나 자신의 450입니다.
• ② 두 창을 바꿔 봤습니다. 범위를 적지 않은 A가 현재 행까지이고, UNBOUNDED FOLLOWING까지 넓힌 B가 칸 전체입니다.
• ④ LAST_VALUE가 늘 현재 행의 값을 낸다고 봤습니다. B는 창이 칸의 끝까지라서 내림차순의 마지막 값 300을 냅니다.

다시 볼 레슨: 윈도우 함수

④ — Oracle은 NULL을 가장 큰 값으로 봐서 내림차순에서는 DEPTNO가 NULL인 정하늘이 맨 앞에 섭니다. 정하늘과 최유나(30) 다음 셋째 행이 20번 부서 사원이고, WITH TIES가 셋째 행과 DEPTNO가 같은 20번의 나머지 두 명까지 붙여 5행이 됩니다.

• ① 두 오해가 겹쳤습니다. 내림차순에서도 NULL이 맨 뒤라고 봤고, WITH TIES를 ONLY처럼 3행에서 끊었습니다.
• ② WITH TIES를 ONLY처럼 봤습니다. WITH TIES는 마지막 행과 정렬 값이 같은 행까지 붙여서 20번 부서 세 명이 모두 나옵니다.
• ③ 내림차순에서도 NULL이 맨 뒤라고 봤습니다. NULL을 가장 작게 보는 SQL Server라면 그렇지만, Oracle은 내림차순 맨 앞에 정하늘을 둡니다.

20번 세 명끼리의 순서는 보장되지 않습니다.

다시 볼 레슨: Top N 쿼리 · ORDER BY 절

④ — Oracle은 START WITH·CONNECT BY로 트리를 다 만든 뒤에 남은 WHERE 조건으로 행을 거릅니다. 그래서 박지호 행만 남아도 LEVEL과 경로는 트리 안의 자리 그대로, 뿌리 김민준에서 3단계와 김민준부터 박지호까지의 경로입니다.

• ① WHERE가 트리를 만들기 전에 행을 거른다고 봤습니다. 그랬다면 박지호 한 행에는 MGR이 NULL인 뿌리가 없어 행이 나오지 않겠지만, WHERE는 트리를 다 만든 뒤에 걸립니다.
• ② WHERE로 남긴 박지호가 새 뿌리가 된다고 봤습니다. 뿌리는 START WITH가 고른 김민준이고, LEVEL은 그 뿌리에서 센 단계입니다.
• ③ 경로가 박지호에서 위로 쌓인다고 봤습니다. PRIOR EMPNO = MGR은 아래로 내려가는 순방향이라 경로는 뿌리 김민준부터 쌓입니다.

다시 볼 레슨: 계층형 질의와 셀프 조인

③ — 사원 쪽 E의 MGR을 관리자 쪽 M의 EMPNO와 잇고, 짝이 없는 김민준도 남도록 E 쪽을 살리는 LEFT OUTER JOIN을 씁니다.

• ① 내부 조인으로도 모든 사원이 나온다고 봤습니다. MGR이 NULL인 김민준은 짝이 없어 빠지고 5행만 나옵니다.
• ② 살릴 쪽을 거꾸로 골랐습니다. RIGHT OUTER JOIN은 관리자 쪽 M을 모두 살려 부하가 없는 세 사람에게도 줄이 생기고, 8행 가운데 김민준은 ENAME 쪽에 나오지 않습니다.
• ④ 잇는 조건의 방향을 거꾸로 썼습니다. E.EMPNO = M.MGR이면 M이 관리자가 아니라 부하 쪽이 되어, 사원마다 부하 이름이 붙은 8행이 나옵니다.

SELECT E.ENAME,
  M.ENAME AS MGR_NAME
FROM EMP E
  LEFT OUTER JOIN EMP M
  ON E.MGR = M.EMPNO
ORDER BY E.EMPNO;

다시 볼 레슨: 계층형 질의와 셀프 조인 · 조인

① — PIVOT은 PIVOT 절에 쓰지 않은 열 MGR로 묶어 관리자마다 한 줄을 내서, MGR 1001·1004·1006의 3행입니다. 1006 아래 두 사람은 모두 20번이라 D30 칸에는 들어온 행이 없고, SUM이 빈 그룹에 내는 NULL이 들어갑니다.

• ② 들어온 행이 없는 칸은 0이라고 봤습니다. 빈 칸에는 집계 함수가 빈 그룹에 내는 값이 들어가서, COUNT라면 0이지만 SUM은 NULL입니다.
• ③ PIVOT이 행을 묶지 않고 재료 5행을 그대로 둔다고 봤습니다. GROUP BY가 없어도 PIVOT 절에 쓰지 않은 MGR로 묶여 관리자마다 한 줄이 됩니다.
• ④ 두 오해가 겹쳤습니다. 재료는 MGR로 묶여 3행이 되고, 빈 칸은 SUM이 내는 NULL입니다.

1004 줄은 그 아래 정하늘의 DEPTNO가 NULL이라 IN 목록에 들지 못해서 값 칸이 모두 NULL입니다.

다시 볼 레슨: PIVOT과 UNPIVOT

② — {3,4}는 바로 앞의 \d가 3번 이상 4번 이하라는 뜻이라, 왼쪽부터 훑다 처음 만나는 숫자 세 개 305와 맞습니다. 나머지 셋은 모두 1120입니다.

• ① SUBSTR처럼 첫 글자부터 2글자를 자른다고 봤습니다. REGEXP_SUBSTR의 셋째 인자는 시작 위치, 넷째 인자는 몇 번째 일치라서 두 번째 숫자 덩어리 1120이 나옵니다.
• ③ \d{4}가 처음 만나는 숫자 덩어리 305에서 멈춘다고 봤습니다. 숫자가 네 개 이어져야 맞아서, 세 개뿐인 305를 건너뛰고 1120과 맞습니다.
• ④ $를 빼고 읽어 첫 덩어리 305가 나온다고 봤습니다. $는 문자열의 끝 자리라서, 끝에 붙은 1120만 맞습니다.

SELECT REGEXP_SUBSTR(
    'BOX-305-1120',
    '\d+', 1, 2) AS R1,
  REGEXP_SUBSTR(
    'BOX-305-1120',
    '\d{3,4}') AS R2,
  REGEXP_SUBSTR(
    'BOX-305-1120',
    '\d{4}') AS R3,
  REGEXP_SUBSTR(
    'BOX-305-1120',
    '\d+$') AS R4
FROM DUAL;

다시 볼 레슨: 정규 표현식