SQL 자격검정 실전문제 과목 2 문제풀이 66~80

정답 3번

윈도우 함수(Window Function)는 행을 줄이지 않고(=GROUP BY처럼 한 줄로 합치지 않고), 각 행마다 순위/누적합/이전값 같은 계산 결과를 붙여주는 함수다

 

• GROUP BY: 여러 행을 묶어서 행 수가 줄어듦
  → “일자별 합계”처럼 결과가 그룹 단위로만 나옴
• 윈도우 함수: 행을 묶어 계산하긴 하지만 행 수는 그대로 유지
  → “각 행에 누적합/순위/이전값” 같은 걸 붙일 수 있음

셀프조인 (Self Join)이란 동일테이블 사이의 조인을 말한다 FROM절에 동일 테이블이 두번이상 나타나고 동일테이블 사이에 조인을 수행하면 테이블과  칼럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭을 사용해야한다

• ① 그냥 일자별 합계(=일매출)만 나온다 ->GROUP BY A.일자로 같은 날만 합치니까 누적이 아니라 그날 매출 합계만 나온다
• ② 그룹 기준이 B라서 누적이 깨진다 -> 조인은 A.일자 >= B.일자인데 GROUP BY B.일자로 묶어버리면, B 한 날짜가 여러 A에 반복 매칭된 만큼 값이 이상하게 커지거나 “누적” 형태가 아니다.
• ④ 조건 방향이 반대라서 “미래 누적(역누적)”이 된다 -> 서브쿼리 조건이 B.일자 >= A.일자면, A일자 기준으로 **그날 이후(미래)**를 합치는 형태라서 문제의 누적(과거부터 A까지)과 반대 결과가 나온다.

따라서 정답은 3번이다

정답 2번 

해당 문제는 서브쿼리에 대한 설명으로 다와 마의 설명이 틀린것인데

(다) 복수행 결과일 때 =, <, > 같은 연산자와 함께 사용할 수 있다

복수 행이면 = 같은 단일 비교 연산자를 쓰면 행이 여러 개라서 비교가 불가능해진다
그래서 복수행 서브쿼리는 보통 이렇게 써야 한다

• IN (서브쿼리)
• > ANY (서브쿼리)
• > ALL (서브쿼리)
• EXISTS (서브쿼리)

(마) “다중 컬럼 서브쿼리는 오라클 및 SQL Server 등에서 사용할 수 있다.”

• 오라클은 (col1, col2) IN (SELECT col1, col2 …) 같은 다중 컬럼(튜플) 비교 문법을 지원한다.
• 하지만 SQL Server는 이런 ‘(컬럼1, 컬럼2) IN (서브쿼리)’ 튜플 문법을 그대로 지원하지 않는다고 보는 게 일반적이라서, “오라클 및 SQL Server”라고 같이 묶어버린 (마)는 오답 처리된다.(SQL Server에서는 보통 JOIN/EXISTS로 풀어야 한다고 배운다.)

따라서 가,나,라가 묶여있는 2번이 정답이다

정답 3번

위의 SQL은 약관항목 중 단 하나라도 동의를 하지 않은 회원을 구하는 SQL이다. HAVING 절에서 동의여부가 N인 데이터가 한 건이라도 존재하는 데이터를 추출한다

 

• 1번 ->회원 A 한 명을 기준으로, 그 회원번호로 B를 찾아서 'N'이 존재하면 통과시킨다
• 2번 -> 'N'인 회원번호 목록에 A.회원번호가 포함되면 통과
• 4번 -> 애초에 'N'인 행만 남기고 조인한다. 한 회원이 'N'이 여러 개면 행이 여러 개가 되는데, GROUP BY로 회원당 1행으로 만든다

1,2,4번은 문제의 SQL문과 같은 결과를 내기 때문에 3번은 기준 SQL은 “회원별로 동의여부 'N'이 하나라도 있는 회원만”인데, ③번은 “전체 테이블에 'N'이 존재하는지만” 봐서 회원 조건이 빠진 쿼리라서 3번이 정답이다

정답 3번

이 SQL은 2014.10.01 이후 이벤트 개수와 회원별 메일발송 개수를 비교해서,메일을 전 이벤트에 대해 다 받지 못한(발송 누락이 있는) 회원을 찾는 쿼리다

① “메일 발송 기록이 있는 모든 회원”

• 이 SQL은 “있기만 하면”이 아니라 COUNT 비교로 ‘전부 받았는지/덜 받았는지’를 따지는 쿼리다
• 핵심은 < (이벤트 건수) 비교라서 ①은 의미가 부족하다

④ “GROUP BY 없고 HAVING 써서 오류”

• HAVING은 원래 그룹에 조건을 거는 절이지만
• GROUP BY가 없어도 전체를 ‘하나의 그룹’으로 보고 집계하는 형태는 가능하다
• 그래서 “실행 오류”라는 ④는 오답이다.

② ㄴ 을 제거하고 ㄱ의 EXISTS 연산자를 IN 연산자로 변경하면 회원별로 메일을 발송한 건수를 계산할 수 없으므로 원하는 결과를 추출할 수 없다

따라서 정답은 3번이된다

정답 2번

• 1번 단일 행 서브쿼리의 비교 연산자로는 =, <, <=, 〉, >=, <가 되어야 한다 IN, ALL 등의 비교 연산자는 다중 행 서브쿼리의 비교연산자이다 
• 2번 단일 행 서브쿼리의 비교연산자는 다중 행 서브쿼리의 비교 연산자로 사용할 수 없지만, 반대의 경우는 가능하다
• 3번 비 연관 서브쿼리가 주로 메인쿼리에 값을 제공하기 위한 목적으로 사용된다
• 4번 메인쿼리의 결과가 서브쿼리로 제공될 수도 있고, 서브쿼리의 결과가 메인쿼리로 제공될 수도 있으므로 실행 순서는 상황에 따라 달라진다.

따라서 정답은 2번이다

정답 3번

2014년에 입사한 사원들의 사원, 부서 정보와 부양가족수를 추출하는 SQL이다

SELECT 절에 사용된 서브쿼리는 단일행 연관 서브쿼리로 JOIN으로도 변경이 가능하며, FROM 절에 사용된 서브쿼리는 Inline View 또는 Dynamic View이고, WHERE 절에 사용된 서브쿼리는 다중행 연관 서브쿼리이다.

③번 보기의 경우 이미 FROM절에 Inline View로 사원 테이블의 입사년도 조건을 명시하였으므로 WHERE 절의 EXISTS 조건은 부서와 사원 테이블 간의 JOIN 조건에 의해 결과에 어떠한 영향도 미치지 못하므로 삭제되어도 된다

 

• 스칼라 서브쿼리(Scalar Subquery) : 값 1개를 컬럼처럼 붙인다. SELECT절(컬럼처럼 붙임), 가끔 WHERE절의 단일 비교시 사용
• 인라인 뷰: 서브쿼리를 테이블처럼 FROM에 넣는다. → “임시로 만든 가상 테이블”이라고 생각하면 된다.
• EXISTS: 결과 값이 아니라 “존재 여부”만 본다. 서브쿼리가 1행이라도 나오면 EXISTS는 참이다.

 

정답 2번

이 문제는 (상품ID, 평가항목ID)별 최종 평가행을 뽑는 문제다.

 

• ②번은 상관 서브쿼리로 상품+항목별 MAX(평가회차)를 정확히 찾는다.
• ①은 전체 MAX라서 틀리고, ③·④는 MAX값들이 같은 행에서 나온다는 보장이 없어 틀린다

 

✔️뷰 사용의 장점

• 독립성 -> 테이블 구조가 변경되어도 뷰를 사용하는 응용프로그램은 변경하지 않아도 된다
• 편리성 -> 복잡한 문제를 뷰로 만들어서 관련 문제를 단순하게 작성할수 있다 또한 해당 SQL을 자주 사용할때 뷰를 사용한다면 편리하게 사용할수있다
• 보안성-> 숨기고 싶은 정보가 있다면 뷰를 생성할때 해당 칼럼을 빼고 만들어서 사용자에게 정보를 감출수 있다

정답 2번

(NULL, 200)은 C1='B' 조건을 못 만족해서 빠지고, (B, NULL)은 NULL 비교 때문에 WHERE에서 탈락하므로, 결국 (B,200)만 남아서 200이다.

정답 2번

문제의 SQL을 해석해보자

먼저 COL1로 “각 부서 연봉 2등”만 남긴다

• 부서 100: 002(3000)=1등, 001(2500)=2등
• 부서 200: 003(4500)=1등, 004(3000)=2등, 005(2500)=3등
• 부서 300: 006(4500)=1등, 007(3000)=2등

 최종 출력 대상 사원은 001, 004, 007 이다

여기서 포인트는 ORDER BY가 “연봉”이 아니라 “사원ID” 라는 점이다.
즉, 부서별로 사원ID 오름차순으로 누적합을 만든다.

부서 100 (사원ID 순)

• 001(2500) → 누적 2500
• 002(3000) → 누적 5500

→ 우리는 001만 뽑혔으니 COL2 = 2500 이다.

부서 200 (사원ID 순)

• 003(4500) → 누적 4500
• 004(3000) → 누적 7500
• 005(2500) → 누적 10000

→ 우리는 004만 뽑혔으니 COL2 = 7500 이다.

부서 300 (사원ID 순)

• 006(4500) → 누적 4500
• 007(3000) → 누적 7500

→ 우리는 007만 뽑혔으니 COL2 = 7500 이다.

그래서 COL2는 (001=2500, 004=7500, 007=7500)로 2번 보기와 일치한다.

COL3도 COL2와 같은 값이므로 결론은 2번이 정답이다

정답 1번

서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능하다. 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야 하고, 복수 행 비교 연산자는 서브쿼리의 결과 건수와 상관없다

정답 1번

표에 부서별 소계(JOB NULL) + 전체 소계(DNAME NULL, JOB NULL) 가 모두 있으니,
가장 대표적으로 그 결과를 만드는 GROUP BY ROLLUP(DNAME, JOB)가 정답(1번)이다