3-2. 노랭이 SQL 풀이(65번~127번)

위 문제는 한국데이터산업진흥원의 [SQL 자격검정 실전문제]를 참조하였습니다.
해설은 https://cafe.naver.com/sqlpd 의 글과 참조하여 개인적인 생각을 적은 글입니다.

 

(1) p.66 69번

아래는 어느 회사의 정산 데이터 모델의 일부이며 고객이 서비스를 사용한 시간 대에 따라 차등 단가를 적용하려고 한다. 다음 중 시간대별사용량 테이블을 기반으로 고객별 사용금액을 추출하는 SQL으로 가장 적절한 것은?

SELECT A.고객ID, A.고객명, SUM(B.사용량 * C.단가) AS 사용금액
FROM 고객 A.INNER JOIN 시간대별사용량 B
ON (A.고객ID = B.고객ID) INNER JOIN 시간대구간 C
ON B.사용시간대 BETWEEN C.시작시간대 AND C.종료시간대
GROUP BY A.고객ID, A.고객명
ORDER BY A.고객ID, A.고객명;

/* 사례1 */
SELECT A.ID, B.ID
FROM TBL1 A FULL OUTER JOIN TBL2 B
ON A.ID = B.ID
 
/* 사례2 */
SELECT A.ID, B.ID
FROM TBL1 A LEFT OUTER JOIN TBL2 B
ON A.ID = B.ID
UNION
SELECT A.ID, B.ID
FROM TBL1 A RIGHT OUTER JOIN TBL2 B
ON A.ID = B.ID
 
/* 사례3 */
SELECT A.ID, B.ID
FROM TBL1 A, TBL2 B
WHERE A.ID = B.ID
UNION ALL
SELECT A.ID, NULL
FROM TBL1 A
WHERE NOT EXISTS (SELECT 1 FROM TBL2 B WHRER A.ID = B.ID)
UNION ALL
SELECT NULL, B.ID
FROM TBL2 B
WHERE NOT EXISTS (SELECT 1 FROM TBL1 A WHERE B.ID = A.ID)

• 주키와 외래키는 영향을 미치지 않는다.

74번 답

 

(5) p.87 77번

아래와 같은 데이터 모델에서 ORACLE을 기준으로 SQL을 작성하였다. 그러나 SQL Server에서도 동일한 결과를 보장할 수 있도록 ANSI 구문으로 SQL을 변경하려고 한다. 다음 중 아래의 SQL을 ANSI 표준 구문으로 변경한 것으로 가장 적절한 것은? 

/* ORACLE */
SELECT A.게시판ID, A.게시판명, COUNT(B.게시글ID) AS CNT
FROM 게시판 A, 게시글 B
WHERE A.게시판ID = B.게시판ID(+)
AND B.삭제여부(+) = 'N'
AND A.사용여부 = 'Y'
GROUP BY A.게시판ID, A.게시판명
ORDER BY A.게시판ID;
 
/* SQL Server */
SELECT A.게시판ID, A.게시판명, COUNT(B.게시글ID) AS CNT
FROM 게시판 A LEFT OUTER JOIN 게시글 B
ON (A.게시판ID = B.게시판ID AND B.삭제여부 = 'N')
WHERE A.사용여부 = 'Y'
GROUP BY A.게시판ID, A.게시판명
ORDER BY A.게시판ID;

(6) p.91 82번

집합 연산자의 종류

집합 연산자	연산자의 의미
UNION	여러 개의 SQL문의 결과에 대한 합집합으로 결과에서 모든 중복된 행은 하나의 행으로 만듦
UNION ALL	여러 개의 SQL문의 결과에 대한 합집합으로 중복된 행도 그대로 결과로 표시된다.
INTERSECT	여러 개의 SQL문의 결과에 대한 교집합으로 중복된 행은 하나의 행으로 만든다.
EXCEPT(MINUS)	앞의 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합으로 중복된 행은 하나의 행으로 만든다.

 

(7) p.94 87번

아래와 같은 데이터 상황에서 아래의 SQL을 수행할 경우 정렬 순서상 2번째 표시될 값을 적으시오.

• START WITH C2 IS NULL : NULL 값을 가진 C2를 부모로 지정 / A
• CONNECT BY PRIOR C1= C2 : 이전 C1의 값이 다음번 C2와 같은 것으로 연결 / A B C
• ORDER SIBLINGS BY C3 DESC : 계층 구조 안에서 C3 기준 내림차순 정렬 / A C B
• CONNECT BY PRIOR C1= C2 : A C B D
• SELECT C3 : A C B D

정답

 

(8) p.95 88번, p.96 90번

Oracle 계층형 질의

• START WITH절은 계층 구조의 시작점을 지정하는 구문이다.
• ORDER SIBLINGS BY절은 형제 노드 사이에서 정렬을 지정하는 구문이다.
• 순방향전개란 부모 노드로부터 자식 노드 방향으로 전개하는 것을 말한다.
• 루트 노드의 LEVEL 값은 1이다.
• WHERE 절은 모든 전개를 진행한 이후 필터 조건으로서 조건을 만족하는 데이터만을 추출하는데 활용된다.
• PRIOR 키워드는 CONNECT BY 절을 포함하여 SELECT, WHERE 절에도 사용할 수 있으며 'PRIOR 자식 = 부모' 형태로 사용하면 순방향 전개로 수행된다.

 

SQL Server 계층형 질의

• CTE(Common Table Expression)를 재귀 호출함으로써 계층 구조를 전개한다.
• 앵커 멤버를 실행하여 기본 결과 집합을 만들고 이후 재귀 멤버를 지속적으로 실행한다.

 

(9) p.98 92번

SELF JOIN이란?

• 한 테이블에서 두 개의 칼럼이 연관 관계를 가지고 있는 경우에 사용한다.
• 동일 테이블 사이의 조인, 반드시 테이블 별칭(Alias)를 사용해야 한다.

 

(10) p.99 94번

• DISTINCT A || B는 A컬럼과 B컬럼을 합친 상태로 DISTINCT를 한다는 뜻
• 정답은 EMP 테이블의 (A=1, B=a), (A=2, B=a)인 2건이 출력된다.

 

(11) p.100 95번

서브쿼리

• 서브쿼리(Subquery)란 하나의 SQL문안에 포함되어 있는 또 다른 SQL문을 말한다.
• 서브쿼리는 알려지지 않은 기준을 이용한 검색을 위해 사용한다.
• 서브쿼리를 괄호로 감싸서 사용한다. 단일행 또는 복수행 비교연산자와 함께 사용가능하다. 

 

서브쿼리 종류

서브쿼리 종류	설명
단일 행 서브쿼리 (Single row subquery)	- 서브쿼리를 실행하면 그 결과는 반드시 한 행만 조회된다. - 비교 연산자인 =, <, <=, >, >=, <>를 사용한다.
다중 행 서브쿼리 (Multi row subquery)	- 서브쿼리를 실행하면 그 결과는 여러 개의 행이 조회된다. - 다중 행 비교 연산자인 IN, ANY, ALL, EXISTS를 사용한다.

 

(12) p.107 103번

뷰(View)

• 테이블은 실제로 데이터를 가지고 있는 반면, 뷰는 실제 데이터를 가지고 있지 않다.
• 뷰의 장점 : 독립성 / 편리성 / 보안성