Humility

아무리 노력해도 최고가 되지 못할 수 있다⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀그럼에도 노력하는자가 가장 겸손한 것 아닌가

공부하는 블로그

자격증/SQL

오답노트 ( SQL 활용_2 )

새벽_글쓴이 2025. 5. 21. 00:00
반응형

1️⃣1️⃣ 뷰에 대한 설명으로 가장 적절하지 않은 것은?

  1. 뷰는 단지 정의만을 가지고 있으며, 실행 시점에 질의를 재작성하여 수행한다
  2. 뷰는 복잡한 SQL 문장을 단순화하는 장점이 있는 반면, 테이블 구조가 변경되면 응용 프로그램을 변경해 주어야 한다
  3. 뷰는 보안을 강화하기 위한 목적으로도 활용할 수 있다
  4. 실제 데이터를 저장하고 있는 뷰를 생성하는 기능을 지원하는 DBMS도 있다
더보기

2

해설

1번: 사용자는 뷰를 간단한 테이블처럼 쓰지만, DBMS는 내부적으로 그 뷰에 정의된 원래의 SELECT 문으로 다시 바꿔서 실행한다 이 과정을 바로 "질의 재작성(query rewriting)" 또는 "질의 확장(query expansion)"이라고 부른다

 

2번: 테이블 구조가 변경되면 뷰를 수정해야함. ( 응용 프로그램은 사용자가 간단하게 사용한거 )


용어 의미
뷰 (VIEW) SQL의 가상 테이블. 복잡한 SELECT를 숨기는 "DB 객체"
응용 프로그램 뷰나 테이블을 사용하는 외부 시스템 또는 사용자 코드(예: 웹사이트, 앱, 보고서, 저장 프로시저 등)

1️⃣2️⃣ 아래에서 뷰 생성 스크립트를 실행한 후, SQL을 실행한 결과로 가장 적절한 것은?

[TBL]

C1 C2
A 100
B 200
B 100
B NULL
NULL 200 
[뷰 생성 스크립트]
CREATE VIEW V_TBL
AS
SELECT *
FROM TBL
WHERE C1 = 'B' OR C1 IS NULL

[SQL]
SELECT SUM(C2) C2
FROM V_TBL
WHERE C2 >= 200 AND C1 = 'B'
  1. 0
  2. 200
  3. 300
  4. 400
더보기

2

해설

WHERE C1 = 'B' OR C1 IS NULL 이 문장으로 C1의 B 와 NULL인 행만 추출

WHERE C2 >= 200 AND C1 = 'B' 이 문장으로 C2가 200 이상인것과 C1의 B인 행만 추출

1️⃣3️⃣ 아래 SQL의 실행 결과로 가장 적절한 것은?

[사원]

사원ID 부서ID 연봉
001 100 2500
002 100 3000
003 200 4500
004 200 3000
005 200 2500
006 300 4500
007 300 3000 
[SQL]
SELECT 사원ID, COL2, COL3
FROM (SELECT 사원ID
					,ROW_NUMBER() OVER(PARTITION BY 부서ID
	ORDER BY 연봉 DESC) AS COL1
					,SUM(연봉) OVER(PATITION 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;

 

1.

 

사원ID COL2 COL3
001 2500 4500
004 7500 4500
007 7500 4500

 

2.

사원ID COL2 COL3
001 2500 2500
004 7500 3000
007 7500 3000

 

3.

사원ID COL2 COL3
001 5500 4500
004 5500 4500
007 3000 4500

 

4.

사원ID COL2 COL3
001 5500 2500
004 5500 3000
007 3000 3000
더보기

2

해설

윈도우 함수의 "ROWS BETWEEN"이란?

윈도우 함수에서 OVER(...) 안에 ROWS BETWEEN ... AND ... 옵션은 현재 행을 기준으로, 누적하거나 비교할 범위(윈도우 프레임)를 설정하는 거야.

📌 1. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

💡 뜻:

“현재 행까지의 모든 이전 행 포함해서 계산해라.”

  • UNBOUNDED PRECEDING → 가장 첫 번째 행부터
  • CURRENT ROW → 현재 행까지

즉, 이 옵션을 쓰면 누적합 또는 누적 평균처럼,

현재 행까지의 값을 모두 합산하는 누적 계산을 하게 돼.

🎯 예: SUM(연봉) OVER(PARTITION BY 부서ID ORDER BY 사원ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

부서ID 사원ID 연봉 누적합
100 001 2500 2500 ← 1행만
100 002 3000 2500+3000 = 5500 ← 2행까지 누적

➡️ 이렇게 부서ID별, 사원ID 순 정렬 기준으로 누적 계산됨.

📌 2. ROWS CURRENT ROW

💡 뜻:

"현재 행 하나만 기준으로 계산해라"

이건 누적이 아니라, 현재 행 1개만 보는 것이야.

예: MAX(연봉) OVER(ORDER BY 연봉 DESC ROWS CURRENT ROW)

→ 이건 그냥 연봉을 내림차순으로 정렬한 상태에서,

그 한 행에서만 MAX(연봉)을 본다는 뜻이니까 결국:

= 그냥 연봉 컬럼 값을 그대로 반환하는 것과 같다.

✅ COL1: ROW_NUMBER() OVER(PARTITION BY 부서ID ORDER BY 연봉 DESC)

  • 부서별로 나누고, 연봉이 높은 순으로 정렬해서 순번을 매긴다.
  • 즉, 각 부서에서 연봉 2등이 COL1 = 2가 됨
  • 예를 들어 부서ID 100은:
    • 1등: 연봉 3000 → 사원ID 002 → ROW_NUMBER 1
    • 2등: 연봉 2500 → 사원ID 001 → ROW_NUMBER 2 ✅

이 COL1 = 2인 사람만 WHERE절에서 남게 된다.

✅ COL2: SUM(연봉) OVER(PARTITION BY 부서ID ORDER BY 사원ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

  • 부서별로 나누고, 사원ID 오름차순으로 정렬한 뒤,
  • 해당 행까지의 누적 합계를 구한다.

예: 부서 100의 사원ID 오름차순은 001 → 002

→

  • 사원ID 001 → SUM = 2500
  • 사원ID 002 → SUM = 2500 + 3000 = 5500

✅ COL3: MAX(연봉) OVER(ORDER BY 연봉 DESC ROWS CURRENT ROW)

  • 연봉 기준으로 내림차순 정렬 후,
  • 현재 행만 고려해서 MAX(연봉)

즉, 사실상 그냥 현재 행의 연봉 값 그 자체만 출력된다.

연봉 MAX(연봉) (ROWS CURRENT ROW)
4500 4500 ✅
4500 4500 ✅
4500 4500 ✅
3000 3000 ✅
3000 3000 ✅
2500 2500 ✅
2500 2500 ✅

✅ 중간 결과 전체 테이블 보기

전체 데이터를 기반으로 COL1, COL2, COL3 구해보면 다음과 같아:

사원ID 부서ID 연봉 COL1 COL2 COL3
001 100 2500 2 2500 2500
002 100 3000 1 5500 3000
003 200 4500 1 4500 4500
004 200 3000 2 7500 3000
005 200 2500 3 10000 2500
006 300 4500 1 4500 4500
007 300 3000 2 7500 3000

✅ 마지막 조건: WHERE COL1 = 2

→ 각 부서에서 연봉 2등인 사원만 남긴다!

즉, 최종 남는 행:

사원ID COL2 COL3
001 2500 2500
004 7500 3000
007 7500 3000

✅ 정답은?

2번

사원ID COL2 COL3
001 2500 2500
004 7500 3000
007 7500 3000

1️⃣4️⃣ 아래 SQL에 대한 설명으로 가장 적절한 것은?

SELECT EMPNO, SAL
FROM EMP
WHERE SAL >= (SELECT MAX(SAL)
		FROM EMP
		GROUP BY DEPTNO);
  1. ‘단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.’ 오류가 발생한다
  2. 부서별 최고연봉보다 크거나 같은 사원을 출력한다
  3. 부서별 최고연봉 전부와 크거나 같은 연봉을 가진 사원을 출력하는 쿼리는 다음과 같이 변경할 수 있다 SELECT EMPNO, DEPTNO, SAL FROM EMP WHERE SAL ≥ ANY(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);
  4. 부서별 최고연봉 중 하나보다 크거나 같은 연봉을 가진 사원을 출력하는 쿼리는 다음과 같이 변경할 수 있다 SELECT EMPNO, DEPTNO, SAL FROM emp WHERE SAL ≥ ALL(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);
더보기

1

✅ 각 보기를 해석해보자

1번. ‘단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.’ 오류가 발생한다 ✅

✔️ 정답

지금 상황에 딱 맞는 설명이야!

2번. 부서별 최고연봉보다 크거나 같은 사원을 출력한다

❌ 틀림

오류가 발생해서 실행 자체가 안 되므로 이 설명은 성립하지 않음.

✅ 3번

부서별 최고연봉 전부와 크거나 같은 연봉을 가진 사원을 출력하는 쿼리는 다음과 같이 변경할 수 있다

SELECT EMPNO, DEPTNO, SAL
FROM EMP
WHERE SAL ≥ ANY (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

🔍 해석:

  • 서브쿼리 결과: 3000, 4000, 5000 (예시)
  • SAL ≥ ANY(...) → 하나라도 만족하면 OK
  • 즉, SAL이 3000 이상이면 출력됨

❌ 그런데 설명에서 뭐라고 했냐?

"전부와 크거나 같은 연봉을 가진 사원"

💥 이 표현은 ALL에 해당하는 설명이지, ANY 아님

→ ANY는 “하나라도 만족하면 OK”인데

→ “전부와 크거나 같은”이라는 표현은 ALL의 의미야

→ ✅ 쿼리는 맞고, 설명이 틀림

❌ 4번

부서별 최고연봉 중 하나보다 크거나 같은 연봉을 가진 사원을 출력하는 쿼리는 다음과 같이 변경할 수 있다

SELECT EMPNO, DEPTNO, SAL
FROM EMP
WHERE SAL ≥ ALL (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

🔍 해석:

  • SAL ≥ ALL (...) → 전부보다 크거나 같아야 함
  • 예: SAL ≥ 5000인 사람만 출력됨 (가장 큰 값 기준)

❌ 그런데 설명은?

“하나보다 크거나 같다” → 이건 ANY 조건이야

→ ✅ 쿼리는 맞고, 설명이 틀림

1️⃣5️⃣ 아래를 참고할 때 SQL의 빈칸 (ㄱ) 에 들어갈 내용으로 가장 적절한 것은?

SELECT A.JOB, A.DEPTNO, ROUND(AVG(A.SAL),2) AVG_SAL
	FROM EMP A
GROUP BY (ㄱ);

 

[실행결과]

JOB DEPTNO AVG_SAL
CLERK 10 1300
CLERK 20 950
CLERK 30 950
ANALYST 20 3000
MANAGER 10 2450
MANAGER 20 2975
MANAGER 30 2850
SALESMAN 30 1400
PRESIDENT 10 5000
NULL NULL 2073.21
  1. ROLLUP(JOB,DEPTNO)
  2. ROLLUP((JOB,DEPTNO))
  3. ROLLUP((JOB),(DEPTNO))
  4. JOB, ROLLUP(DEPTNO)
더보기

2

해설

🔸 1. ROLLUP(JOB, DEPTNO)

의미:

  • GROUP BY JOB, DEPTNO를 하면서
  • → DEPTNO에 대한 소계
  • → JOB에 대한 소계
  • → 전체 총계 까지 나옴

✅ 출력 예시:

JOB DEPTNO AVG_SAL
CLERK 10 ...
CLERK 20 ...
CLERK NULL ← CLERK 소계
MANAGER 10 ...
MANAGER 20 ...
MANAGER NULL ← MANAGER 소계
NULL NULL ← 전체 총계 ✅

🔸 2. ROLLUP((JOB, DEPTNO)) ← ✅ 정답

의미:

  • GROUP BY (JOB, DEPTNO) 전체를 하나의 그룹 단위로 보고
  • → (JOB, DEPTNO) 조합에 대해서만 집계
  • → 그리고 그 전체의 총계 한 줄만 더해짐

✅ 출력 예시:

JOB DEPTNO AVG_SAL
CLERK 10 ...
CLERK 20 ...
... ... ...
PRESIDENT 10 ...
NULL NULL 2073.21 ← 총계만 하나 나옴 ✅

→ ⭕ 문제의 출력 결과와 정확히 일치

→ ✔️ 정답: 2번

🔸 3. ROLLUP((JOB), (DEPTNO))

이건 문법적으로도 오류가 발생할 수 있고,

ROLLUP은 인자들을 순차적으로 묶어야지 이렇게 개별 괄호를 묶으면 잘못된 형식이야.

이건 “튜플 + 튜플” 구조인데, SQL에서 ROLLUP은 그런 식으로 2개의 튜플을 인자로 받지 못해.

→ ❌ 오답 (정상 실행 불가)

🔸 4. JOB, ROLLUP(DEPTNO)

의미:

  • 먼저 JOB, DEPTNO 조합으로 그룹핑
  • → DEPTNO에 대해서만 누적 집계 (소계 생성)
  • → 전체 총계는 없음

✅ 출력 예시:

JOB DEPTNO AVG_SAL
CLERK 10 ...
CLERK 20 ...
CLERK NULL ← DEPTNO 소계
MANAGER 10 ...
MANAGER NULL ← DEPTNO 소계
... ... ...

→ ⛔ JOB은 그대로 있고, DEPTNO에만 NULL이 생김 (중간 중간 소계)

→ NULL, NULL은 안 나옴

→ ❌ 정답 아님

1️⃣6️⃣ 아래 SQL의 실행 결과로 가장 적절한 것은?

[월별매출]

상품ID 월 매출액
P001 2014.10 1500
P001 2014.11 1500
P001 2014.12 2500
P002 2014.10 1000
P002 2014.11 2000
P002 2014.12 1500
P003 2014.10 2000
P003 2014.11 1000
P003 2014.12 1000 
SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
WHERE 월 BETWEEN '2014.10' AND '2014.12'
GROUP BY GROUPING SETS((상품ID, 월));

 

 

1.

상품ID 월 매출액
NULL 2014.10 4500
NULL 2014.11 4500
NULL 2014.12 5000
P001 NULL 5500
P002 NULL 4500
P003 NULL 4000

 

2.

상품ID 월 매출액
P001 2014.10 1500
P001 2014.11 1500
P001 2014.12 2500
P002 2014.10 1000
P002 2014.11 2000
P002 2014.12 1500
P003 2014.10 2000
P003 2014.11 1000
P003 2014.12 1000

 

3.

상품ID 월 매출액
NULL 2014.10 4500
NULL 2014.11 4500
NULL 2014.12 5000
P001 NULL 5500
P002 NULL 4500
P003 NULL 4000
NULL NULL 1400
0    

 

4.

상품ID 월 매출액
P001 2014.10 1500
P002 2014.10 1000
P003 2014.10 2000
NULL 2014.10 4500
P001 2014.11 1500
P002 2014.11 2000
P003 2014.11 1000
NULL 2014.11 4500
P001 2014.12 2500
P002 2014.12 1500
P003 2014.12 1000
NULL 2014.12 5000
더보기

2

해설

1번: GROUPING SETS(월, 상품ID)

3번: GROUPING SETS(월, 상품ID,())

4번: GROUPING SETS((월, 상품ID),월)

1️⃣7️⃣ 아래 SQL에 대한 설명으로 가장 적절한 것은?

SELECT 상품분류코드
	,AVG(상품가격) AS 상품가격
	,COUNT(*) OVER(ORDER BY AVG(상품가격)
		RANGE BETWEEN 10000 PRECEDING
		AND 10000 FOLLOWING) AS 유사개수
FROM 상품
GROUP BY 상품코드;
  1. WINDOW FUNCTION을 GROUP BY 절과 함께 사용하였으므로 위의 SQL은 오류가 발생한다
  2. WINDOW FUNCTION의 ORDER BY절에 AVG 집계 함수를 사용하였으므로 위의 SQL은 오류가 발생한다
  3. 유사개수 칼럼은 상품분류코드별 평균상품가격을 서로 비교하여 -10000 ~ +10000 사이에 존재하는 상품분류코드의 개수를 구한 것이다
  4. 유사개수 칼럼은 상품전체의 평균상품가격을 서로 비교하여 -10000 ~ +10000 사이에 존재하는 상품의 개수를 구한 것이다
더보기

3

해설

✅ 1번. "WINDOW FUNCTION을 GROUP BY 절과 함께 사용하였으므로 위의 SQL은 오류가 발생한다"

❌ 틀림

  • 윈도우 함수는 GROUP BY와 같이 사용 가능함
  • GROUP BY로 먼저 집계한 결과에 대해 윈도우 함수 적용하는 방식은 흔히 쓰임
  • 예: SELECT 부서ID, AVG(SAL), RANK() OVER (ORDER BY AVG(SAL)) ... FROM ... GROUP BY 부서ID

✅ 2번. "WINDOW FUNCTION의 ORDER BY절에 AVG 집계 함수를 사용하였으므로 위의 SQL은 오류가 발생한다"

❌ 이것도 틀림

  • AVG(상품가격)은 SELECT 절에서 이미 GROUP BY에 의해 계산된 값임
  • 윈도우 함수 내부의 ORDER BY절에서도 이 값을 사용하는 건 문제 없음

✅ 3번.

"유사개수 칼럼은 상품분류코드별 평균상품가격을 서로 비교하여 -10000 ~ +10000 사이에 존재하는 상품분류코드의 개수를 구한 것이다"

✔️ 정답

  • AVG(상품가격) → 상품코드별 평균가
  • RANGE BETWEEN 10000 PRECEDING AND 10000 FOLLOWING
  • → 중심값 ±10,000 이내인 평균가들 개수
  • 즉, 상품코드별 평균가격을 서로 비교하는 것
  • 상품분류코드 기준 아님, 전체 상품코드 간 비교임
  • 정확히 문제의 설명과 일치

✅ 4번.

"유사개수 칼럼은 상품전체의 평균상품가격을 서로 비교하여 ..."

❌ 틀림

  • 상품 전체의 평균가격 하나를 기준으로 삼는 게 아님
  • 이건 각 행의 평균가격을 중심으로 범위 비교하는 방식이므로,
  • 전반적인 평균값과는 전혀 무관해

✅ 보기 1번 관련: GROUP BY + 윈도우 함수

❌ 헷갈릴 수 있는 오류 예시 ①

SELECT 부서ID, RANK() OVER (ORDER BY AVG(SAL)) AS 순위
FROM EMP
GROUP BY 부서ID;

🧨 오류 발생!

"ORA-00979: not a GROUP BY expression"

❓ 왜 오류?

  • 윈도우 함수 안에서 AVG(SAL) 썼는데, 이건 GROUP BY가 끝나야 계산 가능한 값
  • 그런데 SELECT 절에서 윈도우 함수가 먼저 실행되는 것처럼 보이기 때문에 SQL이 혼란스러워 함

✅ 바른 방식:

SELECT 부서ID, 평균급여,
       RANK() OVER (ORDER BY 평균급여 DESC) AS 순위
FROM (
  SELECT 부서ID, AVG(SAL) AS 평균급여
  FROM EMP
  GROUP BY 부서ID
) A;

→ 집계는 먼저 GROUP BY로 끝내고, 그 결과를 가지고 윈도우 함수 사용해야 오류 없음.

✅ 보기 2번 관련: ORDER BY 안에서 집계 함수 사용

❌ 헷갈릴 수 있는 오류 예시 ②

SELECT EMPNO, SAL,
       RANK() OVER (ORDER BY AVG(SAL)) AS 급여순위
FROM EMP;

🧨 오류 발생!

"window function not allowed here" 또는 "ORA-00934: group function is not allowed here"

❓ 왜 오류?

  • AVG(SAL)은 집계 함수인데, 행 단위로 처리하는 윈도우 함수의 ORDER BY 안에서 집계 함수 사용 불가
  • RANK()는 한 행씩 처리하면서 순위를 매기는데, 그 내부에서 집계 전체 평균을 구하는 건 논리적으로 모순

✅ 바른 방식:

SELECT EMPNO, SAL,
       RANK() OVER (ORDER BY SAL DESC) AS 급여순위
FROM EMP;

→ ORDER BY에 들어가는 값은 그 행에서 바로 평가 가능한 컬럼이어야 함

✅ 마무리 요약

✅ SELECT에 집계함수 + GROUP BY 있음 → 집계 결과로 사용 가능 → 윈도우 함수에서도 사용 가능

✅ RANGE BETWEEN X PRECEDING AND X FOLLOWING

  • 이 범위는 정렬 기준 칼럼의 값 차이를 기준으로 앞뒤 행을 포함하는 범위야

즉:

해당 행의 평균가격 기준으로

±10,000원 이내에 있는 행들의 개수를 세겠다는 의미!

✅ 흐름 순서 요약

  1. GROUP BY 상품코드
  2. → 상품코드별로 AVG(상품가격) 계산됨
  3. 이 결과를 AVG(상품가격) 기준으로 정렬함
  4. 각 평균 가격을 중심으로 ±10,000 이내 값들에 대해 COUNT(*)

→ 이게 바로 유사개수

1️⃣8️⃣ 집합 연산자인 INTERSECT에 대한 설명으로 가장 적절한 것은?

  1. 여러 개의 SQL문의 결과에 대한 합집합으로, 중복된 행은 하나의 행으로 출력한다
  2. 여러 개의 SQL문의 결과에 대한 합집합으로, 중복된 행도 그대로 결과로 출력한다
  3. 여러 개의 SQL문의 결과에 대한 교집합으로, 중복된 행은 하나의 행으로 출력한다
  4. SQL문 결과간의 차집합으로, 중복된 행은 하나의 행으로 출력한다
더보기

3

 

해설

SQL 문법 설명 집합
UNION 두 개의 테이블에서 모든 중복된 행은 하나의 행으로 만듬 합집합
INTERSECT 두 테이블에 공통으로 있는 행만 추출 교집합
EXCEPT 또는 MINUS (Oracle) 한 테이블에는 있고 다른 테이블에는 없는 행만 추출 차집합
CROSS JOIN 모든 가능한 행 조합 곱집합
  • 기본적으로 ALL이 안 붙으면 중복을 제거하는 게 기본 동작
반응형

'자격증 > SQL' 카테고리의 다른 글

SQL) SQL 기본  (1) 2025.05.23
오답노트 ( 관리구문 )  (2) 2025.05.22
SQL) 데이터 모델과 SQL  (0) 2025.05.20
오답노트 ( SQL 활용_1 )  (0) 2025.05.20
오답노트 ( SQL_2 )  (0) 2025.05.19

'자격증/SQL'의 다른글

  • 현재글오답노트 ( SQL 활용_2 )

관련글

티스토리툴바