[과목 II] 자격검정 실전문제(41~60)

게시 2024/05/16

By tonyleekorea 55 분읽는 시간

Q. 아래를 참고할 때 시간대별사용량 테이블을 기반으로 고객별 사용금액을 출력하는 SQL로 가장 적절한 것은?

③

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

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

③ 
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.고객명;

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

① 두 번째 ON 절이 ‘B.사용시간대 BETWEEN C.시작시간대 AND C.종료시간대’가 되어야 한다.
② INNER JOIN 구문 오류가 발생한다.
④ BETWEEN JOIN이란 구문은 없다. 구문 오류가 발생한다.

Q. 실행 결과가 다른 하나는?

①

  
①
SELECT T.REGION_NAME, T.TEAM_NAME, T.STADIUM_ID, S.STADIUM_NAME
FROM TEAM T 
INNER JOIN STADIUM S
USING (T.STADIUM ID = S.STADIUM_ID);

② ANSI 표준
SELECT TEAM.REGION_NAME, TEAM.TEAM_NAME, TEAM.STADIUM_ID, STADIUM.STADIUM_NAME 
FROM TEAM 
INNER JOIN STADIUM
ON (TEAM.STADIUM_ID = STADIUM.STADIUM_ID);

③ 오라클 표준
SELECT T.REGION_NAME, T.TEAM_NAME, T.STADIUM_ID, S.STADIUM NAME
FROM TEAM T, STADIUM S
WHERE T.STADIUM_ID = S.STADIUM_ID;

④ 오라클 표준
SELECT TEAM.REGION_NAME, TEAM.TEAM_NAME, TEAM.STADIUM_ID, STADIUM.STADIUM_NAME 
FROM TEAM, STADIUM
WHERE TEAM.STADIUM ID = STADIUM.STADIUM_ID;

TEAM, STADIUM 두 테이블을 조인하여 사용한다.
① USING 조건절을 이용한 EQUI JOIN에서도 NATURAL JOIN과 마찬가지로 JOIN 칼럼에 대해서 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없다. 따라서 SYNTAX 오류가 발생한다.
- USING T.STADIUM = S.STADIUM_ID → USING(STADIUM_ID)
- SELECT T.REGION_NAME, T.TEAM_NAME, T.STADIUM_ID, S.STADIUM_NAME → SELECT T.REGION_NAME, T.TEAM_NAME, STADIUM_ID, S.STADIUM_NAME
USING 절의 사용 조건
- USING 절은 양쪽 테이블에 같은 이름을 가진 컬럼이 있을 때 사용한다. 이 절을 사용할 때는 테이블 별칭이나 테이블 이름을 사용할 수 없으며, 오직 컬럼 이름만 사용해야 한다.
- 예를 들어, USING (stadium_id)라고 하면, 양쪽 테이블에 stadium_id라는 컬럼이 있어야 한다.
Natural join
- 반드시 두 테이블 간의 동일한 이름, 타입을 가진 칼럼이 필요하다.
- 조인에 이용되는 칼럼은 명시하지 않아도 자동으로 조인에 사용된다.
- 동일한 이름을 갖는 칼럼이 있지만 데이터 타입이 다르면 에러가 발생한다.
- 조인하는 테이블 간의 동일 칼럼이 SELECT 절에 기술 되도 테이블 이름을 생략해야 한다.
1 2 3 4 select department_id 부서, department_name 부서이름, location_id 지역번호, city 도시 from departments natural join locations where city＝’Seattle’
Using
- USING 절은 조인에 사용될 칼럼을 지정한다. 조인 칼럼은 괄호로 묶어서 기술해야 한다.
- NATURAL 절과 USING 절은 함께 사용할 수 없다.
- 조인에 이용되지 않은 동일 이름을 가진 컬럼은 컬럼명 앞에 테이블명을 기술한다.
1 2 3 4 select department_id 부서번호, department_name 부서, location_id 지역번호, city 도시 from departments join locations using (location_id);
공통점: 동일한 칼럼명이 있어야 한다. JOIN 칼럼에 대해서 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없다.

Q. 아래 두 SQL이 같은 결과를 출력할 때, 빈칸 ㉠에 들어갈 내용으로 가장 적절한 것은?

④ CROSS JOIN

  
[SQL(1)]
SELECT ENAME, DNAME
FROM EMP, DEPT
ORDER BY ENAME;

[SQL(2)]
SELECT ENAME, DNAME
FROM EMP ㉠ DEPT
ORDER BY ENAME;

① FULL OUTER JOIN

② SELF JOIN

③ NATURAL JOIN

④ CROSS JOIN

CROSS JOIN은 E.F.CODD 박사가 언급한 일반 집합 연산자의 PRODUCT의 개념으로, 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다. 조건절이 없거나 CROSS JOIN 키워드를 사용할 수 있다.
CROSS JOIN
- 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다. 결과는 양쪽 집합의 M*N 건의 데이터 조합이 발생한다.
관계대수 연산자의 종류에는 일반 집합 연산자와 순수 관계 연산자가 있고 연산자의 종류, 기호, 표현은 다음과 같다.

일반 집합 연산자

연산자	기호	표현	설명
합집합(Union)	∪	R∪S	합병 가능한 두 릴레이션 R과 S의 합집합
교집합(Intersection)	∩	R∩S	릴레이션 R과 S에 속하는 모든 튜플로 결과 릴레이션 구성
차집합(Difference)	-	R-S	R에 존재하고 S에 미 존재하는 튜플로 결과 릴레이션 구성
카티션 프로덕트(CARTESIAN Product)	X	RXS	R과 S에 속한 모든 튜플을 연결해 만들어진 새로운 튜플로 릴레이션 구성

Q. 아래를 참고할 때 SQL 실행 결과로 가장 적절한 것은?

고객번호	고객명	단말기ID	단말기명	OSID	OS명
11000	홍길동	1000	A1000	100	Android
12000	강감찬
13000	이순신
14000	안중근
15000	고길동
16000	이대로

  
SELECT A.고객번호, A.고객명, B.단말기ID, B.단말기명, C.OSID, C.OS명
FROM 고객 A 
LEFT OUTER JOIN 단말기 B
ON (A.고객번호 IN (11000, 12000) AND A.단말기ID = B.단말기ID) 
LEFT OUTER JOIN OS C
ON (B.OSID = C.OSID)
ORDER BY A.고객번호;

[OS]

OSID(PK)	OS명
100	Android
200	iOS
300	Bada

[단말기]

단말기ID(PK)	단말기명	OSID(FK)
1000	A1000	100
2000	B2000	100
3000	C3000	200
4000	D3000	300

[고객]

고객번호(PK)	고객명	단말기ID(FK)
11000	홍길동	1000
12000	강감찬
13000	이순신
14000	안중근	3000
15000	고길동	4000
16000	이대로	4000

WHERE 절에 A.고객번호 IN(11000, 12000) 조건을 넣었다면 정답은 ②가 되었을 것이나, ON 절에 A.고객번호 IN (11000, 12000) 조건을 넣었기 때문에 모든 고객에 대해서 출력을 하되 JOIN 대상 데이터가 11000과 12000으로 제한되어 ①과 같은 결과가 출력된다.
- ②
고객번호 고객명 단말기ID 단말기명 OSID OS명
11000 홍길동 1000 A1000 100 Android
12000 강감찬
해당 조건이 WHERE 절에 있었다면, 필터 조건으로 푸는 것이 맞지만, ON 절에 있으므로 이 조건은 필터조건이 아닌 조인조건이다. WHERE 절에 없으므로 조인되지 않은 데이터는 NULL로 반환되면서, A는 전부 나와야 한다. 조인이 안 된 칼럼들이기에 A 테이블의 값만 가지고 출력된다.
LEFT OUTER JOIN
- 조인 수행 시 먼저 표기된 좌측 테이블에 해당하는 데이터를 먼저 읽은 후, 나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어 온다. 즉, TABLE A와 B가 있을 때(TABLE ‘A’가 기준이 됨), A와 B를 비교해서 B의 JOIN칼럼에서 같은 값이 있을 때 그 해당 데이터를 가져오고, B의 JOIN 칼럼에서 같은 값이 없는경우에는 B 테이블에서 가져오는 칼럼들은 NULL 값으로 채운다.
ON 에 조건이 있는 경우: 모든 고객이 포함되며, 조건에 맞지 않는 고객은 단말기 정보가 NULL로 채워진다.
WHERE 절에 조건이 있는 경우: 조건에 맞는 고객만 결과에 포함되며, 나머지 고객은 결과에서 제외된다.
ON 절은 필터링을 하지 않고, WHERE 절은 필터링을 한다.

Q. 아래 SQL에서 실행 결과가 같은 것은?

(가), (나), (다)

  
(가) 
SELECT A.ID, B.ID
FROM TBL1 A 
FULL OUTER JOIN TBL2 B
ON A.ID = B.ID

(나) 
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 TBI2 B
ON A.ID = B.ID

(다) 
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 WHERE 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)

보기의 3개의 SQL은 모두 FULL OUTER JOIN과 동일한 결과를 반환한다.

Q. 아래에서 EMP 테이블과 DEPT 테이블을 LEFT, FULL, RIGHT 외부조인(OUTER JOIN)하면 생성되는 결과 건수로 가장 적절한 것은?

3건, 5건, 4건

[EMP]

A	B	C
1	b	w
3	d	w
5	y	y

[DEPT]

C	D	E
w	1	10
z	4	11
v	2	22

주키와 외래키는 영향을 미치지 않는다.
중복 제외 연결
LEFT OUTER JOIN
A B C D E
1 b w 1 10
3 d w 1 10
5 y y
FULL OUTER JOIN
A B C D E
1 b w 1 10
3 d w 1 10
5 y y
z 4 11
v 2 22
RIGHT OUTER JOIN
A B C D E
1 b w 1 10
3 d w 1 10
z 4 11
v 2 22

A	B	C	D	E
1	b	w	1	10
3	d	w	1	10
5	y	y

A	B	C	D	E
1	b	w	1	10
3	d	w	1	10
5	y	y
		z	4	11
		v	2	22

A	B	C	D	E
1	b	w	1	10
3	d	w	1	10
		z	4	11
		v	2	22

  
[OUTER JOIN 문장 예시]

• LEFT OUTER JOIN
SELECT X.KEY1, Y.KEY2
FROM TAB1 X 
LEFT OUTER JOIN TAB2 Y
ON (X.KEY1=Y.KEY2)

• RIGHT OUTER JOIN
SELECT X.KEY1, Y.KEY2
FROM TAB1 X 
RIGHT OUTER JOIN TAB2 Y
ON (X.KEY1=Y.KEY2)

• FULL OUTER JOIN
SELECT X.KEY1, Y.KEY2
FROM TAB1 X 
FULL OUTER JOIN TAB2 Y
ON (X.KEY1=Y.KEY2)

Q. DEPT와 EMP를 조인하되 사원이 없는 부서 정보도 같이 출력하고자 할 때, 아래 SQL의 빈칸 ㉠에 들어갈 내용으로 가장 적절한 것은?

LEFT OUTER JOIN

  
SELECT E.ENAME, D.DEPTNO, D.DNAME
FROM DEPT D ㉠ D EMP E
ON D.DEPTNO = E.DEPTNO;

LEFT OUTER JOIN은 좌측 테이블이 기준이 되어 결과를 생성한다. 즉, TABLE A와 B가 있을 때(TABLE ‘A’가 기준이 된다), A와 B를 비교해서 B의 JOIN 칼럼에서 같은 값이 있을 때 B 테이블에서 해당 데이터를 가져오고, B의 JOIN 칼럼에서 같은 값이 없는 경우에는 B 테이블에서 가져오는 칼럼들을 NULL 값으로 채운다. 그리고, LEFT JOIN으로 OUTER 키워드를 생략해서 사용할 수 있다.

Q. 아래 SQL의 실행 결과로 가장 적절한 것은?

C1	C2	C1	C2
A	1
B	2	B	2
C	3	C	3
D	4
E	5

[TAB1]

C1	C2
A	1
B	2
C	3
D	4
E	5

[TAB2]

C1	C2
B	2
C	3
D	4

  
SELECT *
FROM TAB1 A 
LEFT OUTER JOIN TAB2 B
ON (A.C1 = B.C1 AND B.C2 BETWEEN 1 AND 3)

아우터 조인에서 ON절은 조인할 대상을 결정한다. 그러나 기준 테이블은 항상 표시된다. 결과 건에 대한 필터링은 WHERE 절에서 수행된다.
JOIN 대상이 아닌 값은 NULL로 채운다.

Q. 아래 오라클 SQL을 동일한 결과를 출력하는 ANSI 표준 구문으로 변경하고자 할 때 가장 적절한 SQL은?

①

  
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;

  
① 
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;

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

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

④ 
SELECT A.게시판ID, A.게시판명, COUNT(B.게시글ID) AS CNT 
FROM 게시판 A 
RIGHT OUTER JOIN 게시글 B
ON (A.게시판ID = B.게시판ID AND A.사용여부 = 'Y' AND B.삭제여부 ='N')
GROUP BY A.게시판ID, A.게시판명
ORDER BY A.게시판ID;

보기는 게시판별 게시글의 개수를 조회하는 SQL이다. 이때 게시글이 존재하지 않는 게시판도 조회되어야 한다. 오라클에서는 OUTER JOIN 구문을 (+) 기호를 사용하여 처리할 수도 있으며, 이를 ANSI 문장으로 변경하기 위해서는 Inner쪽 테이블(게시글)의 조건절을 ON절에 함께 위치시켜야 정상적인 OUTER JOIN을 수행할 수 있다.
②의 경우는 Outer 대상이 되는 테이블(게시판)의 조건절이 ON절에 위치하였으므로 원하는 결과가 출력되지 않는다. 일반조건은 ON절이 아닌 WHERE 절로 들어가야 한다.
③의 경우 삭제 여부 조건까지 일반 조건으로 처리하게 되면 전혀 다른 결과가 출력된다.

게시판과 게시글의 관계:
- 게시글은 선택적인 관계이므로, 하나의 게시판에 게시글이 달리지 않을 수도 있다. 이 경우, INNER JOIN을 사용하면 게시글이 없는 게시판은 조회되지 않는다.
삭제 여부와 OUTER JOIN:
- +가 없는 경우, 삭제 여부가 ‘N’인 행들만 출력된다. 게시글이 없는 게시판도 조회하려면 OUTER JOIN을 사용해야 한다. 이때, ANSI 표준 구문으로 변경할 때는 삭제 여부 조건을 반드시 ON 절에 포함시켜야 한다.
일반 조건과의 구분:
- 사용 여부는 일반 조건에 해당한다. 게시판은 사용 중일 수도 있고 아닐 수도 있는데, 사용하지 않는 게시판에 대해서는 카운트를 하지 않겠다는 의도로 보인다. 이는 COUNT(B.게시글ID)와 같은 조건에서 반영된다.

Q. 아래에 대한 설명으로 가장 적절한 것은? (단, 칼럼의 타입은 NUMBER이다.)

③

COL1	COL2	COL3
10	20
15
50	70	20

① SELECT SUM(COL2) FROM TAB1 의 결과는 NULL이다.

② SELECT SUM(COL1 + COL2 + COL3) FROM TAB1 의 결과는 185이다.

③ SELECT SUM(COL2 + COL3) FROM TAB1 의 결과는 90이다.

④ SELECT SUM(COL2) + SUM(COL3) FROM TAB1 의 결과는 90이다.

칼럼끼리 연산할 때 NULL을 포함하면 결과는 NULL이다.
레코드끼리 연산할 때 NULL을 포함하면 결과가 NULL이 아니며, 이유는 NULL을 연산에서 제외하기 때문이다.

제 2장 SQL 활용

제1절 서브 쿼리

제2절 집합 연산자

제3절 그룹 함수

제4절 윈도우 함수

제5절 Top N 쿼리

제6절 계층형 질의와 셀프 조인

제7절 PIVOT 절과 UNPIVOT 절

제8절 정규 표현식

Q. 아래에서 설명하는 서브쿼리의 종류로 가장 적절한 것은?

다중 칼럼(Multi Column) 서브쿼리
서브쿼리의 실행 결과로 여러 칼럼을 반환한다. 메인쿼리의 조건절에 여러 칼럼을 동시에 비교할 수 있다. 서브쿼리와 메인쿼리에서 비교하고자 하는 칼럼 개수와 칼럼 위치가 동일해야 한다.

반환되는 데이터의 형태에 따른 서브쿼리의 분류 중 아래의 설명은 다중 칼럼(Multi Column) 서브쿼리에 대한 설명이다.

Q. SQL 실행 결과가 다른 하나는?

②

  
①
SELECT COL1, SUM(COL2) 
FROM T1 
GROUP BY COL1 
UNION ALL 
SELECT NULL, SUM(COL2) 
FROM T1
ORDER BY 1 ASC;

②
SELECT COL1, SUM(COL2) 
FROM T1
GROUP BY GROUPING SETS (COL1)
ORDER BY 1 ASC;

③
SELECT COL1, SUM(COL2) 
FROM T1
GROUP BY ROLLUP (COL1)
ORDER BY 1 ASC;

④
SELECT COL1, SUM(COL2) 
FROM T1
GROUP BY CUBE (COL1)
ORDER BY 1 ASC;

①, ③, ④번 SQL은 모두 COL1 별 합계 값과 전체 합계 값을 구하는 SQL이지만, ②번 SQL은 COL1별 합계 값만 구하는 SQL이므로 결과 건수가 달라진다. CUBE 함수와 ROLLUP 함수의 인자가 2개 이상일 경우에는 결과가 서로 달라질 수 있지만, 1개일 때의 결과는 동일하다.
ROLLUP(A), CUBE(A)는 각각 A와 전체 합계 두 가지를 나타낸다.

  
SELECT COL1, SUM(COL2) 
FROM T1 
GROUP BY COL1 

-> COL1 별로 집계한 결과(GROUP BY COL1)
-> GROUP BY ROLLUP (COL1) 했을 때 COL1로 그룹핑한 집계 결과와 동일

SELECT NULL, SUM(COL2) 
FROM T1
ORDER BY 1 ASC;

-> 전체 데이터에 대해 집계한 결과(GROUP BY가 없어서 전체 합계)
-> GROUP BY ROLLUP (COL1) 했을 때 ()로 그룹핑한 집계 결과와 동일

Q. SET OPERATOR 중에서 교집합과 같은 기능을 하는 연산자로 가장 적절한 것은?

② INTERSECT

① UNION

② INTERSECT

③ MINUS

④ EXCEPT

SET OPERATOR : 합집합은 UNION, 교집합은 INTERSECT, 차집합은 MINUS/EXCEPT
일반 집합 연산자를 SQL과 비교
- UNION 연산은 UNION 기능으로,
- INTERSECTION 연산은 INTERSECT 기능으로,
- DIFFERENCE 연산은 EXCEPT(오라클은 MINUS) 기능으로,
- PRODUCT 연산은 CROSS JOIN 기능으로 구현되었다.
집합 연산자의 종류
- UNION
  - 여러 개의 SQL문의 결과에 대한 합집합으로 결과에서 모든 중복된 행은 하나의 행으로 만든다.
- UNION ALL
  - 여러 개의 SQL문의 결과에 대한 합집합으로 중복된 행도 그대로 결과로 표시된다. 즉, 단순히 결과만 합쳐놓은 것이다.
  - 일반적으로 여러 질의 결과가 상호 배타적(Exclusive)일 때 많이 사용한다.
  - 개별 SQL문의 결과가 서로 중복되지 않는 경우, UNION과 결과가 동일하다.(결과의 정렬 순서에는 차이가 있을 수 있음)
- INTERSECT
  - 여러 개의 SQL문의 결과에 대한 교집합이다. 중복된 행은 하나의 행으로 만든다.
- EXCEPT
  - 앞의 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합이다. 중복된 행은 하나의 행으로 만든다.(일부 데이터베이스는 MINUS를 사용함)

Q. 집합 연산자에 대한 설명으로 가장 적절하지 않은 것은?

④

① UNION 연산자는 합집합 결과에서 중복된 행을 하나의 행으로 만든다.

② UNION ALL 연산자는 집합 간의 결과가 중복되지 않는 경우, UNION과 결과가 동일한다.

③ UNION 연산자를 사용한 SQL은 각각의 집합에 GROUP BY 절을 사용할 수 있다.

④ UNION 연산자를 사용한 SQL은 각각의 집합에 ORDER BY 절을 사용할 수 있다.

집합(SET) 연산자를 사용한 SQL의 ORDER BY 절은 최종 결과를 정렬하며, 가장 마지막 줄에 한번만 사용할 수 있다.

Q. 아래의 SQL과 실행 결과가 동일한 SQL은?

②

  
SELECT A.서비스ID, B.서비스명, B,서비스URL
FROM (
	SELECT 서비스ID 
	FROM 서비스
	INTERSECT 
	SELECT 서비스ID
	FROM 서비스이용 
	) A, 서비스 B
WHERE A.서비스ID = B.서비스ID;

  
①
SELECT B.서비스ID, A.서비스명, A.서비스URL
FROM 서비스 A, 서비스이용 B
WHERE A.서비스ID = B.서비스ID;

②
SELECT X.서비스ID, X.서비스명, X.서비스URL
FROM 서비스 X
WHERE NOT EXISTS (
		SELECT 1 
		FROM (
			SELECT 서비스ID
			FROM 서비스
			MINUS
			SELECT 서비스ID 
			FROM 서비스이용
			) Y
		WHERE X.서비스ID = Y.서비스ID
		);

③
SELECT B.서비스ID, A.서비스명, A.서비스URL 
FROM 서비스 A 
LEFT OUTER JOIN 서비스이용 B 
ON (A.서비스ID = B.서비스ID)
WHERE B.서비스ID IS NULL 
GROUP BY B.서비스ID, A.서비스명, A.서비스URL;

④
SELECT A.서비스ID, A.서비스명, A.서비스URL 
FROM 서비스 A 
WHERE 서비스ID IN (
		SELECT 서비스ID
		FROM 서비스이용
		MINUS
		SELECT 서비스ID
		FROM 서비스);

주어진 SQL은 이용된 적이 있었던 서비스를 추출하는 SQL이다.
① 이용된 적이 있었던 서비스를 추출하는 것은 동일하나 서비스와 서비스이용은 1:N 관계이므로 서비스이용건수만큼 추출되므로 전체 결과가 다르다. GROUP BY를 수행하면 동일한 결과를 출력할 수 있다.
② 전체 서비스에서 이용된 적이 있었던 서비스를 MINUS하였으므로 이용된 적이 없었던 서비스가 서브쿼리에서 추출된다. 그러므로 NOT EXISTS 구문을 적용하면 이용된 적이 있었던 서비스가 출력된다.
③ 서비스를 기준으로 OUTER JOIN을 수행하였으므로, 이용된 적이 없었던 서비스만 출력된다. B.서비스ID IS NOT NULL로 변경해야 동일한 결과가 출력된다.
④ 서비스와 서비스이용 테이블의 순서를 변경하고 IN 절을 NOT IN 절로 변경하면 동일한 결과를 출력할 수 있다.
문제의 SQL에서 이용된 적 있는 서비스를 출력하는 것은 INTERSECT에서 반영된다. 교집합을 구할 때 중복이 제거되고, 중복이 제거된 상태에서 1:1 조인을 하므로 서비스 이용건수만큼 추출되지 않고, GROUP BY된 형식으로 추출된다.
①은 1:다 조인이며 SELECT B.서비스ID 대신에 A.서비스ID를 사용한다고 해서 중복 행이 걸러지지 않는다.건수와 NULL 출력 혼동하지 않기(Ex,조인되지 않은 칼럼은 NULL로 채우고, A의 값은 그대로 나타난다.)
②는 차집합(미이용)에 해당되지 않는 것의 조건만 체크하는 것이다. FROM절에서 서비스만을 조회하므로 처음부터 중복이 없다.(서비스 중복 없음, ④도 마찬가지, ③은 GROUP BY로 중복 없음, ①은 중복 행이 발생하여 더 많은 결과 행이 나온다.)
EXISTS 서브쿼리는 존재여부를 체크하므로 SELECT 절은 의미가 없다. 간단하게 아무거나 적어준다. 1, 2, ‘x’ 등이 주로 사용된다.

SQL) 교집합(intersect) - 이용된 서비스
① 조인
- 이용된 서비스
- 1:다 조인이므로 서비스명이 중복 출력됨
② 차집합(minus)는 이용된 적 없는 서비스
- not exists 이용된 적 없는 서비스는 이용된 적 없는 서비스에 존재하지 않는 서비스
- 이중 부정은 강한 긍정
③ left join 후 null인 것을 찾는 것은
- 이용된 적 없는 서비스를 의미함
④ 서비스 이용에서 서비스를 빼주면 공집합

Q. 아래를 참고할 때 SQL 실행 결과로 가장 적절한 것은?

COL1	COL2	CNT
AA	A1	1
AB	A2	1
AC	A3	1
AD	A4	1

  
SELECT COL1, COL2, COUNT(*) AS CNT 
FROM (
	SELECT COL1, COL2 
	FROM TBL1 
	UNION ALL 
	SELECT COL1, COL2 
	FROM TBL2 
	UNION
	SELECT COL1, COL2 
	FROM TBL1
	)
GROUP BY COL1, COL2;

[TBL1]

COL1	COL2
AA	A1
AB	A2

[TBL2]

COL1	COL2
AA	A1
AB	A2
AC	A3
AD	A4

집합 연산자는 SQL에서 위에 정의된 연산자가 먼저 수행된다. 그러므로 UNION이 나중에 수행되므로 결과적으로 중복 데이터가 모두 제거되어 ①과 같은 결과가 도출된다. 만일 UNION과 UNION ALL의 순서를 바꾼다면 다음과 같은 결과가 도출된다.
COL1 COL2 CNT
AA A1 2
AB A2 2
AC A3 1
AD A4 1

COL1	COL2	CNT
AA	A1	2
AB	A2	2
AC	A3	1
AD	A4	1

Q. 아래 SQL의 빈칸 ㉠에 들어갔을 때 그 결과가 다른 하나는?

② ROLLUP (grade, job)

[emp]

EMPNO	ENAME	JOB	SAL
7521	WARD	SALESMAN	1250
7566	JONES	MANAGER	2975
7654	MARTIN	SALESMAN	1250
7782	CLARK	MANAGER	2450
7788	SCOTT	ANALYST	3000
7844	TURNER	SALESMAN	1500
7902	FORD	ANALYST	3000
7934	MILLER	CLERK	1300

[salgrade]

GRADE	LOSAL	HISAL
1	700	1200
2	1201	1400
3	1401	2000
4	2001	3000
5	3001	9999

  
SELECT b.grade, a.job, SUM(a.sal) AS SUM_SAL, COUNT(*) AS CNT
FROM emp a, salgrade b
WHERE a.sal BETWEEN b.losal AND b.hisal
GROUP BY ㉠;

[실행 결과]

GRADE	JOB	SUM_SAL	CNT
2	CLERK	1300	1
2	SALESMAN	2500	2
2		3800	3
3	SALESMAN	1500	1
3		1500	1
4	ANALYST	6000	2
4	MANAGER	5425	2
4		11425	4

① GROUPING SETS (grade, (job, grade))

② ROLLUP (grade, job)

③ grade, ROLLUP (job)

④ grade, CUBE (job)

①, ③, ④는 두 가지 기준으로 그룹핑한다.(grade, job), (grade)
②는 세 가지 기준으로 그룹핑하며 (grade, job), (grade), () 총합 값이 반환된다.
③, ④의 grade는 ROLLUP이나 CUBE과 별개로 무조건 나오게 된다.(괄호 밖에 있는 것에 주목) 즉, ROLLUP, CUBE의 결과에 항상 기본으로 포함된다고 보면 된다.
확장 GROUP BY
- GROUPING SETS : 지정 그룹별 집계
- ROLLUP : 단계별 소계(롤업에 참여하는 항목들을 오른쪽부터 하나씩 제거해 나가면서 집계한다.)
- CUBE : 모든 조합별 소계
① (grade), (job, grade)
- GROUPING SETS(A, (B, A)) - 지정 그룹은 A, (B, A)이며, GROUP BY A, B, A, A는 결국 GROUP BY A, B와 동일하다.
  - (A)
  - (B, A) = (A, B)
② (grade, job), (grade), ()
- ROLLUP(A, B)
  - (A, B)
  - (A)
  - () - 총합계
③ 롤업의 결과 (job), ()에 grade를 기본으로 포함시키면 (grade, job), (grade)가 된다.
- A, ROLLUP(B)
  - A, (B) = (A, B)
  - A, () = (A)
④ 큐브의 결과 (job), ()에 grade를 기본으로 포함시키면 (grade, job), (grade)가 된다.
- A, CUBE(B)
  - A, (B) = (A, B)
  - A, () = (A)
괄호는 한 묶음이다.

Q. 아래 실행 결과를 출력하는 SQL로 가장 적절하지 않은 것은? (단, DBMS는 오라클로 가정)

④

[직원]

사원번호	이름	부서코드	입사일	급여
A001	홍길동	GEN	2019-01-01	1200000
A002	일지매	MKT	2019-01-12	1830000
A003	심청	MKT	2018-09-05	2340000
A004	고길동	HRD	2011-07-07	3500000
A005	이국	ACC	2007-10-13	2300000
A006	동치성	STG	2004-12-14	3560000
A007	손오공	MKT	2015-11-10	4500000
A008	사오정	HRD	2018-07-18	3200000
A009	저팔계	SYS	2011-03-03	2180000
A010	장길산	SYS	2019-01-23	1780000

[실행 결과]

부서코드	사원번호	급여	급여비중
HRD	A004	3500000	0.52
HRD	A008	3200000	0.40
MKT	A007	4500000	0.52
MKT	A003	2340000	0.27
MKT	A002	1830000	0.21

  
①
SELECT A.부서코드, A.사원번호, A.급여, 
	ROUND(RATIO_ТО_REPORT(급여) OVER (PARTITION BY 부서코드), 2) AS 급여비중
FROM ( 
	SELECT 사원번호, 이름, 부서코드, 급여, ~~SUM(급여) OVER (PARTITION BY 부서코드) AS 부서급여합~~
	FROM 직원
	WHERE 부서코드 IN ('MKT', 'HRD')
	) A
ORDER BY A.부서코드, A. 급여 DESC, A.사원번호;

②
SELECT A.부서코드, A.사원번호, A.급여, ROUND(급여/부서급여합, 2) AS 급여비중
FROM ( 
	SELECT 사원번호, 이름, 부서코드, 급여, SUM(급여) OVER (PARTITION BY 부서코드) AS 부서급여합
	FROM 직원
	WHERE 부서코드 IN ('MKT', 'HRD') 
	) A
ORDER BY A.부서코드, A.급여 DESC, A.사원번호;

③ 
SELECT A.부서코드, A.사원번호, A.급여, ROUND(급여/B.부서급여합, 2) AS 급여비중
FROM 직원 A, ( 
		SELECT 부서코드, SUM(급여) AS 부서급여합
		FROM 직원
		WHERE 부서코드 IN ('MKT', 'HRD')
		GROUP BY 부서코드 ) B
WHERE A.부서코드 = B.부서코드
AND A.부서코드 IN ('MKT', 'HRD')
ORDER BY A.부서코드, A.급여 DESC, A.사원번호;

④
SELECT A.부서코드, A.사원번호, A.급여, ROUND(급여/부서급여합, 2) AS 급여비중
FROM ( 
	SELECT 사원번호, 이름, 부서코드, 급여, SUM(급여) OVER (PARTITION BY 부서코드 ORDER BY 사원번호) AS 부서급여합
	FROM 직원
	WHERE 부서코드 IN ('MKT', 'HRD') 
	) A
ORDER BY A. 부서코드, A.급여 DESC, A.사원번호;

①, ②, ③번의 경우는 결괏값으로 동일한 값이 추출되는 반면 ④번의 경우는 부서급여합의 결과가 다른 보기와 다르게 집계되기 때문에 결괏값이 다르게 출력된다.
급여비중을 합해서 1이 되므로 부서 코드별 급여비중을 출력하는 SQL이다. 두 개의 부서에 대해서 선택하는 WHERE절이 있어야 한다.
① RATIO_TO_REPORT 함수 사용하였으므로 SUM 함수를 사용하여 부서별 급여 총합을 구할 필요가 없다. 각각의 급여 정보와 부서코드가 서브쿼리에 아직 남아 있으므로, 서브쿼리의 결과를 올려서 메인 쿼리에서 부서코드별로 급여에 대한 비율을 계산할 수가 있다.
② 부서코드별 급여의 총합을 급여와 함께 서브쿼리에서 출력을 해두고, WHERE절에서 부서코드 제한한다. 메인쿼리에서 이 둘을 연산한다.
③ ②는 SUM OVER로 부서별 급여 총합을 구했지만, GROUP BY를 사용하여 부서코드별로 묶였기 때문에 ②처럼 급여를 출력할 수 없다.(GROUP BY 칼럼이 아닌 칼럼을 단독으로 올릴 수 없기 때문)
급여와 부서코드별 급여의 합을 한 라인에 출력을 해야 연산이 가능하므로 조인이 들어가야 된다. A라는 원래 의 직원 정보와 GROUP BY를 통해 요약해 놓은 정보를 WHERE절에서 부서코드별로 조인을 걸고, 결과적으로 둘을 같은 라인에 출력한다.
④ ORDER BY를 쓰면 안 된다. SUM OVER를 사용한 목적은 전체 총합을 부서별로 구하기 위함인데, 누적합을 구하게 되면 전체 비중이 마지막 행에만 나오게 된다. 즉, ORDER BY를 빼서 전체 급여 총합이 각각의 부서가 같을 때 같은 값으로 출력되어야 한다.

부서 코드별 급여 비중 계산 방법

RATIO_TO_REPORT 함수 사용

이 함수는 파티션 내에서 각 행의 값이 차지하는 비중을 계산한다. 부서 코드별로 급여 비중을 쉽게 구할 수 있다.

  
 SELECT 부서코드, 급여, RATIO_TO_REPORT(급여) OVER (PARTITION BY 부서코드) AS 급여비중
 FROM 직원
 WHERE 부서코드 IN ('부서1', '부서2');

SUM 함수를 사용하여 비중 계산

부서 코드별 급여의 총합을 구한 후, 각 급여를 그 총합으로 나누어 비중을 계산한다.
서브쿼리와 메인쿼리를 사용하여 결과를 얻을 수 있다. 서브쿼리에서 부서별 급여 총합을 구한 후, 메인 쿼리에서 각 급여와 총합을 나누어 비중을 계산한다.

  
 SELECT 부서코드, 급여, 급여 / 부서급여합 AS 급여비중
 FROM (
     SELECT 부서코드, 급여, SUM(급여) OVER (PARTITION BY 부서코드) AS 부서급여합
     FROM 직원
     WHERE 부서코드 IN ('부서1', '부서2')
 ) 서브쿼리;

주의 사항 - ORDER BY 절 사용
- SUM OVER에서 ORDER BY를 사용하면 누적합이 구해져서 전체 비중 계산이 잘못될 수 있다.
- ORDER BY 없이 SUM 함수를 사용해야 부서별 급여 총합이 제대로 계산된다.

Q. 아래에 대한 설명으로 가장 적절한 것은? (단, 시스템적으로 회원기본정보와 회원상세정보는 1:1, 양쪽 필수 관계임을 보장한다.)

③

① 회원ID 칼럼을 대상으로 (회원기본정보 EXCEPT 회원상세정보) 연산을 수행하면 회원상세정보가 등록되지 않은 회원ID가 추출된다.

② 회원ID 칼럼을 대상으로 (회원기본정보 UNION ALL 회원상세정보) 연산을 수행한 결과의 건수는 회원기본정보의 전체건수와 동일하다.

③ 회원ID 칼럼을 대상으로 (회원기본정보 INTERSECT 회원상세정보) 연산을 수행한 결과의 건수와 두 테이블을 회원ID로 JOIN 연산을 수행한 결과의 건수는 동일하다.

④ 회원ID 칼럼을 대상으로 (회원기본정보 INTERSECT 회원상세정보) 연산을 수행한 결과와 (회원기본정보 UNION 회원상세정보) 연산을 수행한 결과는 다르다.

① 1:1 양쪽 필수 관계를 시스템적으로 보장하므로 두 엔터티 간의 EXCEPT 결과는 항상 공집합이다.
② 1:1 양쪽 필수 관계를 시스템적으로 보장하므로 UNION을 수행한 결과는 회원기본정보의 전체건수와 동일하지만, UNION ALL을 수행하였으므로 결과건수는 회원기본정보의 전체건수에 2배가 된다.
④ 1:1 양쪽 필수 관계를 시스템적으로 보장하므로 연산 수행결과는 같다.

Q. 아래 SQL을 수행할 경우 정렬 순서상 3번째 표시될 값은?

B

[TAB 1]

C1	C2	C3
1		A
2	1	B
3	1	C
4	2	D

  
SELECT C3
FROM TAB1
START WITH C2 IS NULL
CONNECT BY PRIOR C1 = C2
ORDER SIBLINGS BY C3 DESC

SQL 실행 결과는 다음과 같다. A부터 START WITH 해서, 같은 레벨 내에서 내림차순이므로 레벨 1인 A, 레벨 2인 C, B, 레벨 3인 D 순으로 정렬된다.
C3
A
C
B
D

① C2가 NULL인 행 먼저 출력

C1	C2	C3
1		A

② 이전(①에서 출력된) 행의 C1 값과 다음으로 출력될 C2 값이 같은 행 출력

C1	C2	C3
2	1	B
3	1	C

③ 두 개 이상의 행이 동시에 뽑혔으면 C3 기준 내림차순 정렬

C1	C2	C3
3	1	C
2	1	B

④ 다시 ② 수행

C1	C2	C3
4	2	D

⑤ 최종 결과는

C1	C2	C3
1		A
3	1	C
2	1	B
4	2	D

⑥ SELECT 절에서 C3만 본다고 하였으므로 C3만 확인

C3
A
C
B
D

PRIOR
- CONNECT BY 절에서 사용되며, 현재 읽은 칼럼을 지정한다. PRIOR 자식 = 부모 형태를 사용하면 계층 구조에서 부모 데이터에서 자식 데이터(부모 → 자식) 방향으로 전개하는 순방향 전개를 한다. 그리고 PRIOR 부모 = 자식 형태로 사용하면 반대로 자식 데이터에서 부모 데이터(자식 → 부모) 방향으로 전개하는 역방향 전개를 한다.
- PRIOR이 붙은 건 부모의 칼럼이고 안 붙은 건 자식의 칼럼이다. PRIOR이 붙은 곳에서부터 출발한다. 부모행의 C1과 자식행의 C2가 같으면 연결한다. 계층형 쿼리의 원래의 목적인 자식의 자식을 찾고, 또 그 자식의 자식을 찾고..
- 계층 구조 쿼리에서의 정렬 구문 ORDER SIBLINGS BY는 부모자식간의 계층구조는 유지하면서 동일 부모 아래 형제들끼리 정렬한다. 형제노드, 즉 같은 레밸 내에서 정렬한다는 의미이다. 같은 레벨에서 내림차순
- 루트 노드는 레벨 1이다. 루트 노드로부터 전개되면 레벨 2, 레벨 2로부터 전개되면 레벨 3이다.
CONNECT BY PRIOR 자식 = 부모
- CONNECT BY는 계층 연결을 뜻한다.
- CONNECT BY PRIOR C1 = C2에서 PRIOR이 있는 부분이 부모로 C1 → C2로 진행되는 순방향이다. CONNECT BY C1 = PRIOR C2에서 PRIOR이 있는 부분이 부모로 C2 → C1으로 가는 역방향이다.
- 계층구조 테이블은 2개의 칼럼으로 구성되는데 하나는 PK 항목이다.(UNIQUE, NOT NULL) → C1 다른 하나는 부모항목이다.(NULL, 중복 가능) → C2
- PRIOR가 PK에 붙으면 순방향, PRIOR가 부모항목에 붙으면 역방향

Certificate, [SQLD] 개념 및 문제풀이

제 2장 SQL 활용

인기 태그