노랭이 104P 부터 시작 101번 ~ 110번

문제 101. 아래와 같은 데이터 모델에서 평가대상상품에 대한 품질평가항목별 최종 평가 결과를 추출하는 SQL 문장으로 옳은 것은? 단, 평가항목에 대한 평가(평가등급)가 기대수준에 미치지 못할 경우 해당 평가항목에 대해서만 재평가를 수행한다

이 문제의 핵심은

상품별 + 평가항목별 최종 평가결과를 구하라

단, 재평가가 있을 수 있으므로 가장 최신 평가회차를 구해야 함

 

데이터 구조 이해

평가결과 테이블 (A)

▪ 상품 ID

▪ 평가항목 ID

▪ 평가회차 ◀🐤 여러번 존재 가능

▪ 평가등급

▪ 평가일자

 같은 상품 + 같은 평가항목에 대해 평가회차가 여러번 존재할 수 있음

우리는 각상품 + 각평가항목별 가장 큰 평가회차(MAX)를 찾아야 함

 

 

(SELECT MAX(평가회차) FROM 평가결과) D

①번 ❌

이건 전체 테이블에서 최대 평가회차 하나만 구함

즉, 모든상품/모든항목 중 가장 큰 회차

이게 되므로 상품별/항목별이 아님

 

A.평가회차 = (
   SELECT MAX(X.평가회차)
   FROM 평가결과 X
   WHERE X.상품ID = B.상품ID
   AND X.평가항목ID = C.평가항목ID
)

②번 ✅

상품ID별, 평가항목ID별, MAX(평가회차)

정확히 문제 요구사항 충족

➡ 상품 + 항목별 최신 회차

 

MAX(A.평가회차),
MAX(A.평가등급),
MAX(A.평가일자)
GROUP BY 상품ID, 평가항목ID

③번 ❌

MAX를 각각 구하면 같은 행이라는 보장이 없음

 

④번 ❌

FROM절에서 GROUP BY로 묶어서 MAX값 구한 후 조인

이 방식은 가능하긴 한데

제시된 SQL은 구조상 A.평가회차, A.평가등급 등을 직접 SELECT하고 있어

집계 컬럼과 불일치 가능성 있음

 

 

🔥 시험 핵심 패턴
최신 데이터 구할 때 반드시 기억
❌ 전체 MAX : SELECT MAX(컬럼) FROM 테이블
✅ 그룹별 MAX : WHERE 컬럼 = ( SELECT MAX(컬럼) FROM 테이블 WHERE 그룹조건 )

---

문제 102. 아래 부서 테이블의 담당자 변경을 위해 부서임시 테이블에 입력된 데이터를 활용하여 주기적으로 부서 테이블을 아래 결과와 같이 반영하기 위한 SQL으로 가장 적절한 것은? (단, 부서임시 테이블에서 변경일자를 기준으로 가장 최근에 변경된 데이터를 기준으로 부서 테이블에 반영되어야 한다)

문제의 조건 : 부서임시 테이블에서 각 부서코드별 가장 최근 변경일자(MAX)를 찾아 그 담당자를 부서 테이블에 반영

즉, 전체 MAX가 아닌 부서별 MAX

 

 

SELECT 부서코드, MAX(변경일자)
FROM 부서임시
GROUP BY 부서코드

①번 ❌

구조는 맞아 보이지만 SET 절에서 SET 담당자 = (SELECT C.부서코드 ...)

담당자에 부서코드를 넣고 있음 ➡  컬럼 자체가 잘못됨

 

SET 담당자 = (SELECT C.부서코드 ...)

②번 ❌

1번과 거의 동일 + EXISTS 추가

하지만 여전히 SET 담당자 = (SELECT C.부서코드 ...)

담당자에 부서코드를 넣는 오류 동일

 

UPDATE 부서 A
SET 담당자 = (
   SELECT B.담당자
   FROM 부서임시 B
   WHERE B.부서코드 = A.부서코드
   AND B.변경일자 = (
       SELECT MAX(C.변경일자)
       FROM 부서임시 C
       WHERE C.부서코드 = B.부서코드
   )
)
WHERE 부서코드 IN (SELECT 부서코드 FROM 부서임시);

③번 ✅

부서코드별 MAX(변경일자)

그 행의 담당자 가져옴

해당 부서만 업데이트

 

AND B.변경일자 = '2015.01.25'

④번 ❌

특정 날짜 하드코딩

문제 조건은 가장 최근 변경일자 ➡ 하드코딩은 오답

 

 

정답 3번

---

문제 103. 다음 중 뷰(vIEW)에 대한 설명으로 가장 부적절한 것은?

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

 

🍑 뷰 사용의 장점

🔹 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다

🔹 편리성 : 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다

                   또한 해당 형태의 SQL문을 자주 사용할 때 뷰를 이용하면 편리하게 사용할 수 있다

🔹 보안성 : 직원의 급여정보와 같이 숨기고 싶은 정보가 존재한다면,

                   뷰를 생성할 때 해당 칼럼을 빼고 생성함으로써 사용자에게 정보를 감출 수 있다

 

정답 2번

---

문제 104. 아래 테이블에 대한 [뷰 생성 스크립트]를 실행한 후, 조회 SQL의 실행결과로 맞는 것은?

 

CREATE VIEW V_TBL 
AS 
SELECT * 
FROM TBL 
WHERE C1 = 'B' OR C1 IS NULL

[뷰 생성 스크립트]

TBL 테이블에서 C1 값이 'B'이거나 NULL인 행만 조회하는 뷰(V_TBL)를 생성한다는 뜻

 

정답 2번

---

 

문제 105. 다음 중 아래의 테이블에서 SQL을 실행할 때 결과로 가장 적절한 것은?

 

SELECT 
  CASE WHEN GROUPING(A.서비스ID) = 0 
       THEN A.서비스ID 
       ELSE '합계' END AS 서비스ID,

  CASE WHEN GROUPING(B.가입일자) = 0 
       THEN NVL(B.가입일자, '-') 
       ELSE '소계' END AS 가입일자,

  COUNT(B.회원번호) AS 가입건수

FROM 서비스 A 
LEFT OUTER JOIN 서비스가입 B
  ON (A.서비스ID = B.서비스ID
      AND B.가입일자 BETWEEN '2013-01-01' AND '2013-01-31')

GROUP BY ROLLUP (A.서비스ID, B.가입일자);

[SQL문 전체]

 

 

FROM 서비스 A
LEFT OUTER JOIN 서비스가입 B

🔹 FROM + JOIN 부분 (데이터 붙이기)

서비스 기준으로 전부 다 보여줌

가입 정보가 없어도 서비스는 무조건 나옴 (LEFT JOIN이니까)

그래서 004도 결과에 반드시 나온다

 

A.서비스ID = B.서비스ID
AND B.가입일자 BETWEEN '2013-01-01' AND '2013-01-31'

🔹 ON 조건

2013년 1월 가입만 연결

즉, 1월 가입만 집계 다른 달은 제외

 

GROUP BY ROLLUP (A.서비스ID, B.가입일자)

🔹 GROUP BY ROLLUP 이해하기

ROLLUP은 단계별 합계 생성기라고 생각하세요

1. (서비스ID, 가입일자)

2. (서비스ID) ⬅ 서비스별 소개

3. 0 ⬅ 전체 합계

 

GROUPING(컬럼)

🔹 소계/합계 행인지 알려주는 함수

0 : 원래 데이터

1 : 집계로 만들어진 행

 

 

SELECT절 해석

CASE WHEN GROUPING(A.서비스ID) = 0 
     THEN A.서비스ID 
     ELSE '합계'

🔹 첫 번째 CASE

원래 데이터 : 서비스ID 그대로 출력

전체합계 행 : 합계 출력

 

CASE WHEN GROUPING(B.가입일자) = 0 
     THEN NVL(B.가입일자, '-') 
     ELSE '소계'

🔹 두 번째 CASE

원래 데이터 : 가입일자 출력

소계 행 : 소계

가입이 아예 없으면 NULL : -로 못바꿈

 

 

COUNT(B.회원번호)

🔹 COUNT

NULL은 세지 않음

가입 없는 서비스는 0

 

 

정답은 3번

---

문제 106. 아래의 데이터 모델에서 SQL을 이용하여 표와 같은 형식의 데이터를 추출하려고 할 때 올바른 SQL 문장은?

 

이 문제를 효율적으로 푸는 방법

🔥 첫번째로 결과 표를 먼저본다

1. 결과표에 (지역+월)  상세행 있음

2. 지역별 소계 있음 ➡ 월별합계

3. 전체 총계 있음 ➡ 지역전체 + 월별합계

 

🔥 두번째로는 ROLLUP 구조

이런 구조는 공식이 있다 

상세 + 중간소계 + 전체합계 ➡ ROLLUP

CUBE는 모든 조합 다 만듦 ➡ 필요 없는 월 전체 합계도 생김

GROUPING SETS는 직접 다 써줘야 한다0

ROLLUP은 딱 계층형 소계용

 

🔥 GROUPING() 값 해석

GROUPING(컬럼)

0 ➡ 실제 값 (상세)

1 ➡ 집계로 인해 NULL (소계/총계)

 

🔥 MIN(지역명) 쓰는 이유

GROUP BY에 지역ID만 있고 지역명은 없으면 

그냥 B.지역명 쓰면 오류 가능성이 있다

그래서 시험에서는 보통 MIN(지역명) 안전하게 감쌈

 

✅ 정리

결과표에 소계 + 총계 : ROLLUP

전체 같은 글자 나오면 GROUPING

GROUPING = 1일 때 라벨 붙여야 함

 

---

문제 107. 아래 결과를 얻기 위한 SQL문에서 (ㄱ)에 들어갈 함수를 작성하시오

정답 ROLLUP

---

문제 108. 다음 설명 중 가장 적절한 것은?

1. 일반 그룹 함수를 사용하여 CUBE, GROUPING SETS와 같은 그룹 함수와 동일한 결과를 출할 수 있으나,
   ROLLUP 그룹 함수와 동일한 결과는 추출할 수 없다
2. GROUPING SETS 함수의 경우에는 함수의 인자로 주어진 컬럼의 순서에 따라 결과가 달라지므로 컬럼의 순서가 중요하다
3. CUBE, ROLLUP, GROUPING SETS 함수들의 대상 컬럼 중 집계된 컬럼 이외의 대상 컬럼 값은
   해당 컬럼의 데이터 중 가장 작은 값을 반환한다
4. CUBE 그룹 함수는 인자로 주어진 컬럼의 결합 가능한 모든 조합에 대해서 집계를 수행하므로 다른 그룹 함수에 비해
   시스템에 대한 부하가 크다

문제 풀이

①번 일반 그룹 함수를 사용하여 CUBE, GROUPING SETS와 같은 결과를 낼 수 있으나 ROLLUP과 동일한 결과는 낼 수 없다 ❌

왜냐하면 일반 GROUP BY + UNION ALL을 여러번 쓰면 CUBE/ROLLUP/GROUPING SETS 전부 흉내낼 수 있음

즉, ROLLUP도 충분히 만들 수 있음

 

②번 GROUPING SETS는 컬럼 순서에 따라 결과가 달라진다 ❌

GROUPING SETS는 GROUPING SETS ((A,B), (A), ()) 처럼 직접 집합을 지정함

순서가 아니라, 어떤 집합을 쓰느냐가 중요함

📛 ROLLUP은 순서 중요

 

③번 집계되지 않은 컬럼은 가장 작은 값을 반환한다 ❌

집계되지 않은 컬럼은 GROUPING 때문에  NULL이 됨

최소값 반환 이런 개념이 아니다

 

④번 CUBE는 가능한 모든 조합을 집계하므로 다른 그룹 함수보다 시스템 부하가 크다 ✅

 

정답 4번

---

문제 109. 아래와 같이 설비와 에너지사용 테이블을 이용하여 결과를 나타내려 할 때 SQL으로 가장 적절한 것을 2개 고르시오

 

구조를 보고 문제를 풀어보자

결과를 보면

🔹 설비ID만 NULL인 줄이 있음

🔹 에너지코드만 NULL인 줄이 있음

🔹 둘 다 NULL인 줄이 있음

결과에 있는 NULL	패턴써야 할 함수
한쪽 NULL만 있음	ROLLUP
양쪽 NULL 각각 있음	CUBE
내가 직접 조합 지정	GROUPING SETS

 

---

문제 110. 자재발주 테이블에 SQL을 수행하여 아래와 같은 결과를 얻었다

다음 중 (ㄱ)에 들어갈 문장으로 옳은 것은?

 

CUBE, GROUPING SET 에 대해 알면 풀 수 있다

정답 4번