[SQLD] 데이터 모델링의 이해 문제 (41-80)

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

① (조인조건 "B.사용시간대 <= C.시작시간대 AND B.사용시간대 >= C.종료시간대" 가 잘못되었다. 논리적으로 맞지 않다.)

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

② (시간대별사용량 B와 시간대구간 C의 조인 조건에 A.고객ID = B.고객ID가 포함되어 있어, 테이블 간의 관계가 명확하지 않다.)

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

④ (BETWEEN JOIN 은 유효한 SQL 구문이 아니다.)

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

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

① (USING 절은 괄호 안에 같은 이름의 필드를 지정할 때 사용된다. ex. "USING (STADIUM_ID)"

- USING 절은 컬럼 이름만 명시한다.

- 두 테이블에서 같은 이름을 가진 컬럼에 대해 조인을 수행한다.

- USING 을 사용할 때는 컬럼 이름 앞에 테이블 별칭이나 이름을 붙이지 않는다.

- SELECT 구문에서 해당 조인 컬럼을 참조할 때는, 별칭 없이 컬럼 이름만 사용할 수 있다.)

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

②

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;

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

[SQL(1)] (조인 조건 없이 조인하고 있으므로 카티시안 곱(Cartesian Product) 또는 CROSS JOIN으로 해석한다.)

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

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

[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	강감찬	<NULL>
13000	이순신	<NULL>
14000	안중근	3000
15000	고길동	4000
16000	이대로	4000

[SQL]

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.고객번호;

① ("FROM 고객 A LEFT OUTER JOIN 단말기 B ..." 조건은 "고객" 테이블 전체를 조회한 뒤에 고객번호가 11000, 12000 번호인 고객에 대해서만 조인을 수행한다는 의미.

"고객" 테이블의 모든 행을 결과에 포함하고, 해당하는 "단말기" 테이블의 행을 옆에 연결한다.

"고객" 테이블의 어떤 행이 조인 조건을 충족하는 "단말기" 행을 찾지 못하면, 해당 "고객" 행은 여전히 결과에 포함되지만 "단말기" 테이블에서 오는 컬럼들은 모두 NULL 값을 갖게 된다.

따라서 11000, 12000 고객번호만 단말기 및 OS 정보가 결합되고 다른 고객들은 NULL로 채워진다.)

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

②

고객번호	고객명	단말기ID	단말기명	OSID	OS명
11000	홍길동	1000	A1000	100	Android
12000	강감찬	<NULL>	<NULL>	<NULL>	<NULL>

③

고객번호	고객명	단말기ID	단말기명	OSID	OS명
11000	홍길동	1000	A1000	100	Android

④

고객번호	고객명	단말기ID	단말기명	OSID	OS명
11000	홍길동	1000	A1000	100	Android
12000	강감찬	<NULL>	<NULL>	<NULL>	<NULL>
13000	이순신	<NULL>	<NULL>	<NULL>	<NULL>
14000	안중근	3000	C3000	200	iOS
15000	고길동	4000	D4000	300	Bada
16000	이대로	4000	D4000	300	Bada

(참고) LEFT OUTER JOIN

조인 수행 시 먼저 표기된 좌측 테이블에 해당하는 데이터를 읽은 후, 나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어온다.
즉, Table A와 B가 있을 때 (Table 'A'가 기준),
- A와 B를 비교해서 B의 JOIN 컬럼에서 같은 값이 있을 때 그 해당 데이터를 가져오고,
- B의 JOIN 컬럼에서 같은 값이 없는 경우에는 B 테이블에서 가져오는 컬럼들은 NULL 값으로 채운다.

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

㉮

SELECT A.ID, B.ID
FROM TBL1 A FULL OUTER JOIN TBL2 B
ON A.ID = B.ID;

㉯ (UNION은 중복 결과를 제거한다.)

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;

㉰ (UNION ALL은 중복을 제거하지 않는다.

SELECT 1은 조건을 만족하는 행이 하나라도 존재하는 지 여부만을 확인하기 위해 사용한다.

실제로 반환되는 값은 중요하지 않다.

이 경우 '1'. 즉 TBL1에서 TBL2에 없는 ID만을 찾으라는 명령을 수행하는 것과 같고,

TBL2에서 TBL1에 없는 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);

① (가), (나)
② (가), (다)
③ (나), (다)
④ (가), (나), (다)

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

[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)

SELECT EMP.A, EMP.B, EMP.C, DEPT.D, DEPT.E
FROM EMP
LEFT OUTER JOIN DEPT ON EMP.C = DEPT.C;

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

(FULL OUTER JOIN)

SELECT EMP.A, EMP.B, COALESCE(EMP.C, DEPT.C) AS C, DEPT.D, DEPT.E
FROM EMP
FULL OUTER JOIN DEPT ON EMP.C = DEPT.C;

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

(RIGHT OUTER JOIN)

SELECT EMP.A, EMP.B, DEPT.C, DEPT.D, DEPT.E
FROM EMP
RIGHT OUTER JOIN DEPT ON EMP.C = DEPT.C;

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

① 3건, 5건, 4건
② 4건, 5건, 3건
③ 3건, 4건, 4건
④ 3건, 4건, 5건

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

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

① LEFT OUTER JOIN
② RIGHT OUTER JOIN
③ FULL OUTER JOIN
④ INNER JOIN

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

[TAB1]

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

[TAB2]

C1	C2
B	2
C	3
D	4

[SQL]

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

①

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

② (JOIN 연산은 ON 절의 조건을 만족하는 행만 가져온다.)

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

③

C1	C2	C1	C2
A	1	<NULL>	<NULL>
B	2	B	2
C	3	C	3

④

C1	C2	C1	C2
A	1	<NULL>	<NULL>
B	2	B	2
C	3	C	3
D	4	D	4

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

[SQL]

SELECT A.게시판ID, A.게시판명, COUNT(B.게시글ID) AS CNT
FROM 게시판 A, 게시글 B
WHERE A.게시판ID = B.게시판(+)
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, 게시판명
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;

(참고)

(+)

Oracle 데이터베이스의 고유 문법인 (+) 연산자를 사용한 외부 조인(OUTER JOIN)이다.
LEFT OUTER JOIN과 동일한 기능을 한다.
+가 있는 부분들을 ON 절에 포함시킨다.

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

[SQL]

COL1	COL2	COL3
10	20	<NULL>
15	<NULL>	<NULL>
50	70	20

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

(90이다. [행연산]20 + NULL + 70 = 90)
② SELECT SUM(COL1 + COL2 + COL3) FROM TAB1의 결과는 185이다.

(140이다. [열연산]10 + 20 + NULL = NULL, [열연산]15 + NULL + NULL = NULL, [열연산]50 + 70 + 20 = 140)
③ SELECT SUM(COL2 + COL3) FROM TAB1의 결과는 90이다.

(90이다. [열연산]20 + NULL = NULL, [열연산]NULL + NULL = NULL, [열연산]70 + 20 = 90)
④ SELECT SUM(COL2) + SUM(COL3) FROM TAB1의 결과는 90이다.

(110이다. [행연산]20 + NULL + 70 = 90, [행연산]NULL + NULL + 20 = 20)

(참고)

ORACLE에선 컬럼(열)끼리 연산 시 NULL을 포함하면 NULL이 된다.

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

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

① 단일 행 (Single Row) 서브쿼리
② 다중 컬럼 (Multi Column) 서브쿼리
③ 다중 행 (Multi Row) 서브쿼리
④ 단일 컬럼 (Single Column) 서브쿼리

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

ex) T1 테이블 (문제에서 주어지지 않음)

COL1	COL2
A	10
A	20
B	30
B	40
C	50

①

SELECT COL1, SUM(COL2)
FROM T1
GROUP BY COL1
UNION ALL
SELECT NULL, SUM(COL2) # NULL을 첫 번째 컬럼으로 선택
FROM T1
ORDER BY 1 ASC;
# COL1 | SUM(COL2)
# NULL | 150
# A    | 30
# B    | 70
# C    | 50

②

SELECT COL1, SUM(COL2)
FROM T1
GROUP BY GROUPING SETS(COL1) # 단순히 COL1으로 그룹화 하는 것과 같다.
ORDER BY 1 ASC; #(첫 번째, COL1을 기준으로 오름차순 정렬)
# COL1 | SUM(COL2)
# A    | 30
# B    | 70
# C    | 50

③

SELECT COL1, SUM(COL2)
FROM T1
GROUP BY ROLLUP(COL1) # (COL1), ()로 그룹화
ORDER BY 1 ASC;
# COL1 | SUM(COL2)
# NULL | 150
# A    | 30
# B    | 70
# C    | 50

④

SELECT COL1, SUM(COL2)
FROM T1
GROUP BY CUBE(COL1) # (COL1), ()로 그룹화
ORDER BY 1 ASC;
# COL1 | SUM(COL2)
# NULL | 150
# A    | 30
# B    | 70
# C    | 50

(참고)

집계 함수와 GROUP BY

집계 함수(ex. AVG, SUM, COUNT, MAX, MIN)는 대부분 GROUP BY와 같이 쓰인다.
GROUP BY가 없다면 전체 테이블에 대해 단 하나의 결과 행을 생성한다.

UNION ALL

두 개 이상의 SELECT 문의 결과를 결합하여 하나의 결과 집합으로 만든다.
중복 행을 포함한 모든 행을 결과에 포함시킨다.
중복을 제거하지 않는다.
UNION 보다 일반적으로 빠르다.

NULL 값은 ORACLE 데이터베이스에서 가장 작은 값으로 취급되어 ASC 정렬 시 가장 먼저 나온다.

GROUPING SETS

여러 그룹화 작업을 한 번에 수행할 수 있게 해주는 SQL 기능이다.
아래 SQL의 결과는 같다.

SELECT COL1, COL2, SUM(VALUE)
FROM TABLE
GROUP BY GROUPING SETS(COL1, COL2)
# COL1 | COL2 | SUM(VALUE)
# A    | NULL | xxx  (COL1 그룹화 결과)
# B    | NULL | yyy  (COL1 그룹화 결과)
# NULL | X    | zzz  (COL2 그룹화 결과)
# NULL | Y    | www  (COL2 그룹화 결과)

SELECT COL1, NULL AS COL2, SUM(VALUE)
FROM TABLE
GROUP BY COL1
UNION ALL
SELECT NULL AS COL1, COL2, SUM(VALUE)
FROM TABLE
GROUP BY COL2;

# COL1 | COL2 | SUM(VALUE)
# A    | NULL | xxx  (COL1 그룹화 결과)
# B    | NULL | yyy  (COL1 그룹화 결과)
# NULL | X    | zzz  (COL2 그룹화 결과)
# NULL | Y    | www  (COL2 그룹화 결과)

ROLLUP

ROLLUP은 지정된 컬럼들의 왼쪽에서 오른쪽 순서로 계층적 집계를 생성한다.
N개의 그룹화 컬럼에 대해 N+1개의 그룹화 집합을 만든다.
ex) ROLLUP(A, B, C)
- (A, B, C)
- (A, B)
- (A),
- () // Null

CUBE

주어진 컬럼들의 모든 가능한 조합에 대한 소계를 계산하는 SQL 연산이다.
N개의 그룹화 컬럼에 대해 2^N 개의 그룹화 집합을 만든다.
ex) CUBE(A, B, C)
- (A, B, C)
- (A, B)
- (A, C)
- (B, C)
- (A)
- (B)
- (C)
- () // Null

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

① UNION
② INTERSECT
③ MINUS
④ EXCEPT

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

① UNION 연산자는 합집합 결과에서 중복된 행을 하나의 행으로 만든다.
② UNION ALL 연산자는 집합 간의 결과가 중복되지 않는 경우, UNION 과 결과가 동일하다.
③ UNION 연산자를 사용한 SQL은 각각의 집합에 GROUP BY 절을 사용할 수 있다.
④ UNION 연산자를 사용한 SQL은 각각의 집합에 ORDER BY 절을 사용할 수 있다.

(전체 결과 집합에 대해 한 번만 사용할 수 있다. 즉, 마지막 줄에서 한 번만 가능하다.)

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

ex) 서비스 테이블 (문제에서 주어지지 않음)

서비스ID	서비스명	서비스URL
1	이메일	mail.example.com
2	클라우드	cloud.example.com
3	메신저	chat.example.com
4	지도	map.example.com

ex) 서비스이용 (문제에서 주어지지 않음)

서비스ID	사용자ID
1	user1
2	user2
3	user3
1	user4
2	user5

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

서비스ID	서비스명	서비스URL
1	이메일	mail.example.com
2	클라우드	cloud.example.com
3	메신저	chat.example.com

①

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

서비스ID	서비스명	서비스URL
1	이메일	mail.example.com
1	이메일	mail.example.com
2	클라우드	cloud.example.com
2	클라우드	cloud.example.com
3	메신저	chat.example.com

②

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

서비스ID	서비스명	서비스URL
1	이메일	mail.example.com
2	클라우드	cloud.example.com
3	메신저	chat.example.com

③

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;

(LEFT OUTER JOIN 결과)

A.서비스ID	A.서비스명	A.서비스URL	B.서비스ID	B.사용자ID
1	이메일	mail.example.com	1	user1
1	이메일	mail.example.com	1	user4
2	클라우드	cloud.example.com	2	user2
2	클라우드	cloud.example.com	2	user5
3	메신저	chat.example.com	3	user3
4	지도	map.example.com	NULL	NULL

서비스ID	서비스명	서비스URL
NULL	지도	map.example.com

④

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

(결과 없음)

서비스이용 테이블에는 있지만 서비스에는 없는 서비스ID를 찾는 SQL문

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

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

①

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

②

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

③

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

④

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

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

[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

[SQL]

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	<NULL>	3800	3
3	SALESMAN	1500	1
3	<NULL>	1500	1
4	ANALYST	6000	2
4	MANAGER	5425	2
4	<NULL>	11425	4

① GROUPING SETS (grade, (job, grade))
② ROLLUP (grade, job)
③ grade, ROLLUP (job)
④ grade, CUBE (job)

58. 아래 실행 결과를 출력하는 SQL로 가장 적절하지 않은 것은?

[직원]

사원번호	이름	부서코드	입사일	급여
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.48
MKT	A007	4500000	0.52
MKT	A003	2340000	0.27
MKT	A002	1830000	0.21

①

(OVER (PARTITION BY ...) 부분 중간 테이블

사원번호	이름	부서코드	급여	부서급여합
A002	일지매	MKT	1830000	8670000
A003	심청	MKT	2340000	8670000
A007	손오공	MKT	4500000	8670000
A004	고길동	HRD	3500000	6700000
A008	사오정	HRD	3200000	6700000

SELECT A.부서코드, A.사원번호, A.급여, ROUND(RATIO_TO_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.부서코드 IN('MKT', 'HRD')
ORDER BY A.부서코드, A.급여 DESC, A.사원번호;

④

(OVER (PARTITION BY ... ) 절 내의 ORDER BY는 윈도우 함수의 연산 방식을 변경한다.)

1. ORDER BY 가 없는 경우: 전체 파티션에 대해 계산한다.

2. ORDER BY 가 있는 경우: 정렬된 순서에 따라 누적 계산을 수행한다. (정렬을 하면서 누적 합계(또는 집계 함수의 연산)를 계산한다.)

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.사원번호;

(참고)

RATIO_TO_REPORT

분석 함수이다.
특정 그룹 내에서 개별 행의 값이 차지하는 비율을 계산한다.
결과는 항상 0에서 1 사이의 값이다.
그룹 내 모든 행의 RATIO_TO_REPORT 값을 합하면 1이 된다.
NULL 값은 계산에서 무시된다.

SELECT 부서, 직원, 급여
	RATIO_TO_REPORT(급여) OVER (PARTITION BY 부서) AS 급여비율
FROM 직원테이블;

OVER (PARTITION BY ...)

OVER 절은 윈도우 함수를 정의할 때 사용된다.
PARTITION BY는 OVER 절 내에서 데이터를 그룹화하는 방법을 지정한다.

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

(설명)

1:1 관계 다이어그램이다.
각 회원기본정보 레코드는 정확히 하나의 회원상세정보 레코드와 연결되어 있고, 그 반대도 마찬가지이다.
즉, 모든 회원은 기본 정보와 상세 정보를 각각 하나씩 가지고 있다.

① 회원ID 칼럼을 대상으로 (회원기본정보 EXCEPT 회원상세정보) 연산을 수행하면 회원 상세정보가 등록되지 않은 회원ID가 추출된다. (존재하지 않는다.)
② 회원ID 칼럼을 대상으로 (회원기본정보 UNION ALL 회원상세정보) 연산을 수행한 결과의 건수는 회원기본정보의 전체건수와 동일하다. (2배이다.)
③ 회원ID 칼럼을 대상으로 (회원기본정보 INTERSECT 회원상세정보) 연산을 수행한 결과의 건수와 두 테이블을 회원ID로 JOIN 연산을 수행한 결과의 건수는 동일하다.
④ 회원ID 칼럼을 대상으로 (회원기본정보 INTERSECT 회원상세정보) 연산을 수행한 결과와 (회원기본정보 UNION 회원상세정보) 연산을 수행한 결과는 다르다. (같다.)

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

[TAB1]

C1	C2	C3
1	<NULL>	A
2	1	B
3	1	C
4	2	D

[SQL]

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

① A
② B
③ C
④ D

(결과)

(참고)

START WITH

Oracle의 계층적 쿼리에 사용되는 절이다.
START WITH 절은 계층 구조에서 최상위 노드(루트)를 정의한다.
- 이 조건을 만족하는 행들이 계층 구조의 시작점이 된다.
일반적으로 CONNECT BY 절과 함께 사용되어 전체 계층 구조를 정의한다.
CONNECT BY 절에는 부등호 및 다른 비교 연산자(ex. >, =, <, <=, >=)를 사용할 수 있다.
- ex) START WITH condition CONNECT BY PRIOR parent_id = child_id
START WITH 뒤에는 조건이 온다. 이 조건을 만족하는 행들이 루트 노드가 된다.
즉, 조건에 따라 여러 행이 루트가 될 수 있다.
- ex) START WITH parent_id IS NULL

PRIOR

Oracle의 계층적 쿼리에 사용되는 키워드이다.
현재 처리중인 행의 부모 행을 참조할 때 사용된다.
CONNECT BY 절에서 주로 사용된다.
현재 행과 이전(부모) 행 사이의 관계를 정의하는 데 도움을 준다.
ex) CONNECT BY PRIOR C1 = C2
- 이는 "자식 행의 C2값이 부모 행의 C1 값과 같다" 는 의미이다.
ex) CONNECT BY C1 = PRIOR C2
- 이는 "자식 행의 C1 값이 부모 행의 C2 값과 같다" 는 의미이다.

SIBLINGS

Oracle의 계층적 쿼리에서 ORDER BY 절과 함께 사용되는 키워드이다.
같은 부모를 가진 형제 노드들 간의 정렬을 지정할 때 사용된다.
계층 구조를 유지하면서 각 레벨 내에서 원하는 순서로 결과를 정렬할 수 있게 해준다.
ex) ORDER SIBLINGS BY C3 DESC
- 이는 "각 부모 노드 아래의 자식 노드들을 C3 열을 기준으로 내림차순으로 정렬한다"는 의미이다.

61.오라클 계층형 질의에 대한 설명으로 가장 적절하지 않은 것은?

① START WITH 절은 계층 구조의 시작점을 지정하는 구문이다.
② ORDER SIBLINGS BY 절은 형제 노드 사이에서 정렬을 지정하는 구문이다.
③ 순방향전개란 부모 노드로부터 자식 노드 방향으로 전개하는 것을 말한다.
④ 루트 노드의 LEVEL 값은 0이다. (Oracle 계층형 질의에서 루트 노드의 LEVEL 값은 1이다.)

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

[사원]

사원번호	사원명	입사일자	매니저사원번호
001	홍길동	2012-01-01	<NULL>
002	강감찬	2012-01-01	001
003	이순신	2013-01-01	001
004	이민정	2013-01-01	001
005	이병헌	2013-01-01	<NULL>
006	안성기	2014-01-01	005
007	이수근	2014-01-01	005
008	김병만	2014-01-01	005

[SQL]

SELECT 사원번호, 사원명, 입사일자, 매니저사원번호
FROM 사원
START WITH 매니저사원번호 IS NULL
CONNECT BY PRIOR 사원번호 = 매니저사원번호
AND 입사일자 BETWEEN '2013-01-01' AND '2013-12-31'
ORDER SIBLINGS BY 사원번호;

① (매니저사원번호가 NULL인 행은 루트, 사원번호를 부모로 해서 매니저사원번호가 사원번호와 같은 행들을 선택한다.)

사원번호	사원명	입사일자	매니저사원번호
001	홍길동	2012-01-01	<NULL>
003	이순신	2013-01-01	001
004	이민정	2013-01-01	001
005	이병헌	2013-01-01	<NULL>

②

사원번호	사원명	입사일자	매니저사원번호
003	이순신	2013-01-01	001
004	이민정	2013-01-01	001
005	이병헌	2013-01-01	<NULL>

③

사원번호	사원명	입사일자	매니저사원번호
001	홍길동	2012-01-01	<NULL>

④

사원번호	사원명	입사일자	매니저사원번호
001	홍길동	2012-01-01	<NULL>
005	이병헌	2013-01-01	<NULL>
006	안성기	2014-01-01	005
007	이수근	2014-01-01	005
008	김병만	2014-01-01	005

63. 계층형 질의문에 대한 설명으로 가장 적절하지 않은 것은?

① SQL Server에서의 계층형 질의문은 CTE(Common Table Expression)를 재귀 호출함으로써 계층 구조를 전개한다.
② SQL Server에서의 계층형 질의문은 앵커 멤버를 실행하여 기본 결과 집합을 만들고 이후 재귀 멤버를 지속적으로 실행한다.
③ 오라클의 계층형 질의문에서 WHERE 절은 모든 전개를 진행한 이후 필터 조건으로서 조건을 만족하는 데이터만을 추출하는 데 활용된다.
④ 오라클의 계층형 질의문에서 PRIOR 키워드는 CONNECT BY 절에만 사용할 수 있으며 PRIOR 자식 = 부모 형태로 사용하면 순방향 전개로 수행된다.

(PRIOR 키워드는 SELECT, WHERE 절에서도 사용할 수 있다.

PRIOR 부모 = 자식은 순방향 전개고, PRIOR 자식 = 부모는 역방향 전개이다.)

64. 아래 실행 결과를 출력하는 SQL로 가장 적절한 것은?

[부서]

부서코드	부서명	상위부서코드
100	아시아지부	<NULL>
110	한국지사	100
111	서울지점	110
112	부산지점	110
120	일본지사	100
121	도쿄지점	120
122	오사카지점	120
130	중국지사	100
131	베이징지점	130
132	상하이지점	130
200	남유럽지부	<NULL>
210	스페인지사	200
211	마드리드지점	210
212	그라나다지점	210
220	포르투갈지사	200
221	리스본지점	220
222	포르투지점	220

[매출]

부서코드	매출액
111	1000
112	2000
121	1500
122	1000
131	1500
132	2000
211	2000
212	1500
221	1000
222	2000

[실행 결과]

부서코드	부서명	상위부서코드	매출액	LVL
100	아시아지부	<NULL>	<NULL>	2
120	일본지사	100	<NULL>	1
121	도쿄지점	120	1500	2
122	오사카지점	120	1000	2

①

(UNION 위의 부분에서, 일본지사를 START WITH로 역방향 전개 -> 아시아 지부 행 선택한다.

UNION 아래 부분에서 일본지사를 START WITH로 순방향 전개 -> 도쿄 지점, 오사카 지점 행 선택한다.

UNION을 하여 공통된 부분을 제거한다. -> 일본지사는 하나만 남는다.

Oracle에선 LVL이 1부터 시작한다.

이렇게 생성된 A 테이블과 매출, B 테이블을 LEFT OUTER JOIN 하면서 A.부서코드와 B.부서코드가 같은 행들만 가져온다.

A 테이블은 아시아지부, 일본지사, 도쿄지점, 오사카지점만 존재하므로 4개의 행만 존재한다.

매출액은 지점만 존재한다.)

SELECT A.부서코드, A.부서명, A.상위부서코드, B.매출액, LVL
FROM (SELECT 부서코드, 부서명, 상위부서코드, LEVEL AS LVL
		FROM 부서
        START WITH 부서코드 = '120'
        CONNECT BY PRIOR 상위부서코드 = 부서코드
        UNION
        SELECT 부서코드, 부서명, 상위부서코드, LEVEL AS LVL
        FROM 부서
        START WITH 부서코드 = '120'
        CONNECT BY 상위부서코드 = PRIOR 부서코드) A 
        LEFT OUTER JOIN 매출 B
ON (A.부서코드 = B.부서코드)
ORDER BY A.부서코드;

②

(START WITH를 부서코드 = '100'인 아시아지부에서 시작하고, PRIOR 부모 = 자식 구조이므로 순방향 전개를 한다.

아시아 지부가 루트이므로 아시아 지부 하위의 모든 행을 다 가져온다.)

SELECT A.부서코드, A.부서명, A.상위부서코드, B.매출액, LVL
FROM (SELECT 부서코드, 부서명, 상위부서코드, LEVEL AS LVL
		FROM 부서
        START WITH 부서코드 = '100'
        CONNECT BY 상위부서코드 = PRIOR 부서코드) A LEFT
        		OUTER JOIN 매출 B
ON (A.부서코드 = B.부서코드)
ORDER BY A.부서코드;

③

(START WITH를 부서코드 = '121'인 도쿄지점에서 시작하고, PRIOR 자식 = 부모 구조이므로 역방향 전개를 한다.

도쿄 지점, 일본 지사, 아시아 지부 행의 결과만 존재한다.)

SELECT A.부서코드, A.부서명, A.상위부서코드, B.매출액 LVL
FROM (SELECT 부서코드, 부서명, 상위부서코드, LEVEL AS LVL
		FROM 부서
        START WITH 부서코드 = '121'
        CONNECT BY PRIOR 상위부서코드 = 부서코드) A LEFT
                OUTER JOIN 매출 B
ON (A.부서코드 = B.부서코드)
ORDER BY A.부서코드;

④

(START WITH 부서코드 = '120'로 PRIOR 자식 = 부모인 역방향 전개를 한다. 아시아 지부 행을 선택한다.

부서코드만 남기므로 START WITH 부서코드 = '100'이 된다.

상위 SELECT 문은 PRIOR 부모 = 자식 구조이므로 순방향 전개를 한다. 아시아 지부는 루트이므로 아시아 지부 하위의 모든 행을 선택한다.

A.부서코드 = B.부서코드를 ON 절로 LEFT OUTER JOIN 하여 매출액이 존재하는 지점 행들만 매출액 데이터가 존재하게 된다. 매출액이 없는 지사, 지부는 데이터가 NULL로 채워진다.)

SELECT A.부서코드, A.부서명, A.상위부서코드, B.매출액, LVL
FROM (SELECT 부서코드, 부서명, 상위부서코드, LEVEL AS LVL
		FROM 부서
        START WITH 부서코드 = (
        	SELECT 부서코드
           	FROM 부서
            WHERE 상위부서코드 IS NULL
            START WITH 부서코드 = '120'
            CONNECT BY PRIOR 상위부서코드 = 부서코드)
        CONNECT BY 상위부서코드 = PRIOR 부서코드) A LEFT
                OUTER JOIN 매출 B
ON (A.부서코드 = B.부서코드)
ORDER BY A.부서코드;

(참고)

START WITH 절이 WHERE 절 보다 먼저 연산된다.

(참고)

LEFT OUTER JOIN을 할 때 ON 절을 만족하지 않더라도, 왼쪽 테이블의 행들은 무조건 다 가져온다.

대신 없는 데이터는 NULL이 된다.

65. 아래의 SQL과 실행 결과가 다른 하나는?

[테이블 생성]

CREATE TABLE T1 (NO NUMBER, COLA VARCHAR2(10));

INSERT INTO T1 VALUES(1, 'AAA');

CREATE TABLE T2 (NO NUMBER, COLB VARCHAR2(10));

INSERT INTO T2 VALUES(1, 'BBB');
INSERT INTO T2 VALUES(3, 'CCC');

COMMIT;

[SQL]

SELECT A.NO, A.COLA, B.COLB
FROM T1 A, T2 B
WHERE B.NO = A.NO;

①

SELECT A.NO, A.COLA, B.COLB
FROM T1 A INNER JOIN T2 B
ON B.NO = A.NO;

②

SELECT NO, A.COLA, B.COLB
FROM T1 A JOIN T2 B
USING (NO);

③

SELECT A.NO, A.COLA, B.COLB
FROM T1 A CROSS JOIN T2 B;

④

SELECT NO, A.COLA, B.COLB
FROM T1 A NATURAL JOIN T2 B;

(참고)

USING

두 테이블 간의 조인 조건을 간단하게 지정한다.
동일한 이름의 열을 자동으로 매칭시킨다.
결과 집합에서 조인에 사용된 열(NO)은 한 번만 표시된다.
ex) USING (NO)

INNER JOIN

두 테이블 간 일치하는 행만 반환한다.
JOIN 조건은 ON 절을 사용하여 지정한다.
NULL 값을 가진 행은 결과에 포함되지 않는다.

JOIN

Oracle에서 JOIN은 기본적으로 INNER JOIN과 동일하다.

CROSS JOIN

두 테이블의 카티션 곱(Cartesian product)을 반환한다.
첫 번째 테이블의 모든 행과 두 번째 테이블의 모든 행을 조합한다.

NATURAL JOIN

두 테이블에서 이름과 데이터 타입이 같은 모든 열을 기준으로 자동으로 조인한다.
명시적인 조인 조건(ON절)이 필요없다.
동일한 이름의 열이 여러 개 있을 경우 예상치 못한 결과가 나올 수 있다.

66. WINDOW FUNCTION을 사용하지 않고 아래의 실행 결과를 출력하는 SQL로 가장 적절한 것은?

[일자별매출]

일자	매출액
2015.11.01	1000
2015.11.02	1000
2015.11.03	1000
2015.11.04	1000
2015.11.05	1000
2015.11.06	1000
2015.11.07	1000
2015.11.08	1000
2015.11.09	1000
2015.11.10	1000

[실행 결과]

일자	누적매출액
2015.11.01	1000
2015.11.02	2000
2015.11.03	3000
2015.11.04	4000
2015.11.05	5000
2015.11.06	6000
2015.11.07	7000
2015.11.08	8000
2015.11.09	9000
2015.11.10	10000

① (원래 테이블의 결과와 동일하다.)

SELECT A.일자, SUM(A.매출액) AS 누적매출액
FROM 일자별매출 A
GROUP BY A.일자
ORDER BY A.일자;

② (ON 절을 보면 (A.일자 >= B.일자)로 JOIN을 하고 있다.

그리고 SELECT 절에서는 B.일자를 선택하고 있다.

B.일자가 11.01이면 A.일자는 11.01부터 11.10까지 모두 매칭된다.

B.일자가 11.10이면 A.일자는 11.10만 매칭된다.

그래서 날짜순의 역방향으로 합산이 이뤄진다.)

SELECT B.일자, SUM(B.매출액) AS 누적매출액
FROM 일자별매출 A JOIN 일자별매출 B ON (A.일자 >= B.일자)
GROUP BY B.일자
ORDER BY B.일자;

③ (2번의 설명과 동일하지만, SELECT 절을 보면 A.일자를 하고 있다.

A.일자가 11.01이면 B.일자도 11.01로 매칭되고, A.일자가 11.10이면 B.일자도 11.10으로 매칭된다.

그래서 날짜순으로 합산이 이뤄진다.)

SELECT A.일자, SUM(B.매출액) AS 누적매출액
FROM 일자별매출 A JOIN 일자별매출 B ON (A.일자 >= B.일자)
GROUP BY A.일자
ORDER BY A.일자;

④ (FROM 일자별매출 A에서 메인 쿼리가 시작한다.

GROUP BY A.일자로 A 테이블의 일자로 그룹화하고, ORDER BY로 A 테이블의 일자로 오름차순 정렬한다.

서브 쿼리를 연산하면, FROM 일자별매출 B 로 B 테이블을 선택한다.

WHERE B.일자 >= A.일자 조건은 현재 처리중인 A.일자 컬럼보다 크거나 같은 경우를 선택한다.

A.일자를 기준으로 조건처리를 한다.

A.일자는 오름차순 정렬이므로, 11.01부터 11.10순으로 조회하게 되고, B.일자는 A.일자의 가장 작은 날짜(11.01)보다 큰 일자들의 합이므로 역방향 합산을 하게 된다.

최종적인 결과는 2번과 같게된다.)

SELECT A.일자, (
	SELECT SUM(B.매출액)
    FROM 일자별매출 B
    WHERE B.일자 >= A.일자) AS 누적매출액
FROM 일자별매출 A
GROUP BY A.일자
ORDER BY A.일자;

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

[EMP]

A	B	C	D
1	a	1	x
2	a	1	x
3	b	2	y

[DEPT]

D	E	F
x	i	5
y	m	6

[SQL]

SELECT COUNT(DISTINCT A||B)
FROM EMP
WHERE D = (SELECT D FROM DEPT WHERE E = 'i');

(설명)

EMP 테이블을 선택한다.

WHERE 조건을 해석하기 위해 서브쿼리를 연산한다.

DEPT 테이블에서 E열의 값이 'i'인 행의 D 열을 선택한다. WHERE D = x

A || B는 두 열을 연결한다.

COUNT(DISTINCT A || B) A와 B 열을 연결한 결과 중 중복을 제거하고 수를 센다.

'1a', '2a'

① 0
② 1
③ 2
④ 3

(참고)

DISTINCT

쿼리에서 중복된 행을 제거하는 데 사용되는 키워드이다.
여러 열에 DISTINCT를 적용할 수도 있다.

Oracle에서 문자열을 연결하는 데 사용되는 연산자이다.
ex) SELECT 'Employee ID: ' || employee_id AS employee_info FROM employees;
- 이 쿼리는 Employee ID: 라는 문자열과 employee_id 열의 값을 연결한다.

68. 아래에서 서브쿼리에 대한 설명으로 적절한 것을 모두 고른 것은?

(가)	서브쿼리는 단일 행(Single Row) 또는 복수 행(Multi Row) 비교 연산자와 함께 사용할 수 있다. (참)
(나)	서브쿼리는 SELECT 절, FROM 절, HAVING 절, ORDER BY 절 등에서 사용이 가능하다. (참. WHERE 절에서도 사용 가능하다.)
(다)	서브쿼리의 결과가 복수 행(Multi Row) 결과를 반환하는 경우에는 '=', '<=', '=>' 등의 연산자와 함께 사용할 수 있다. (거짓. 복수행 결과를 반환하는 서브쿼리는 IN, ALL, ANY, SOME, EXISTS 등의 복수 행 연산자를 사용해야한다. '=', '<=', '>='등은 단일 행 비교 연산자이다.)
(라)	연관(Correlated) 서브쿼리는 서브쿼리가 메인쿼리 칼럼을 포함하고 있는 형태의 서브쿼리이다. (참. 연관 서브쿼리는 서브쿼리 내에서 메인 쿼리의 칼럼을 참조하는 형태이다.)
(마)	다중 칼럼 서브쿼리는 서브쿼리의 결과로 여러 개의 칼럼이 반환되어 메인쿼리의 조건과 동시에 비교되는 것을 의미하며 오라클 및 SQL Server 등의 DBMS에서 사용될 수 있다. (거짓. SQL Server에서는 다중 칼럼 서브쿼리를 직접적으로 지원하지 않는다.)

(가), (나), (라)

69. 아래 실행 결과를 출력하는 SQL로 가장 적절한 것은?

[SQL]

KEY	GRP	VAL1	VAL2
1	A	10	100
2	A	10	200
3	A	10	300
4	A	20	400
5	A	50	500
6	B	40	600
7	B	10	700
8	B	20	800
9	B	20	900
10	B	10	1000

[실행 결과]

KEY	VAL1	VAL2
1	0.1	1
2	0.1	1
3	0.1	2
4	0.2	2
5	0.5	3
6	0.4	3
7	0.1	4
8	0.2	4
9	0.2	5
10	0.1	5

①

(결과)

KEY	VAL1	VAL2
1	0	1
2	0	2
3	0	3
4	0.75	4
5	1	5
6	1	6
7	0	7
8	0.5	8
9	0.5	9
10	0	10

SELECT KEY, 
    PERCENT_RANK() OVER(PARTITION BY GRP ORDER BY VAL1) AS VAL1,
    DENSE_RANK() OVER(ORDER BY VAL2) AS VAL2
FROM TBL
ORDER BY KEY;

②

(결과)

KEY	VAL1	VAL2
1	0.5	1
2	0.5	2
3	0.5	3
4	0.8	4
5	1	5
6	0.9	6
7	0.5	7
8	0.8	8
9	0.8	9
10	0.5	10

SELECT KEY,
	CUME_DIST() OVER(ORDER BY VAL1) AS VAL1,
    RANK() OVER (ORDER BY VAL2) AS VAL2
FROM TBL
ORDER BY KEY;

③

(결과)

KEY	VAL1	VAL2
1	0	1
2	0	2
3	0	3
4	0.75	4
5	1	5
6	1	6
7	0	7
8	0.5	8
9	0.5	9
10	0	10

SELECT KEY,
	PERCENT_RANK() OVER(PARTITION BY GRP ORDER BY VAL1) AS VAL1,
    RANK() OVER(ORDER BY VAL2) AS VAL2
FROM TBL
ORDER BY KEY;

④

(결과)

KEY	VAL1	VAL2
1	0.1	1
2	0.1	1
3	0.1	2
4	0.2	2
5	0.5	3
6	0.4	3
7	0.1	4
8	0.2	4
9	0.2	5
10	0.1	5

SELECT KEY,
	RATIO_TO_REPORT(VAL1) OVER(PARTITION BY GRP) AS VAL1,
    NTILE(5) OVER(ORDER BY VAL2) AS VAL2
FROM TBL
ORDER BY KEY;

(참고)

분석함수(윈도우함수) 정리

PERCENT_RANK()

백분율 순위를 계산한다.
0과 1 사이의 값을 반환한다. (0 <= 결과 < 1)
계산식: (rank - 1) / (총 행 수 - 1)
첫 번째 행의 PERCENT_RANK는 항상 0이다.

DENSE_RANK()

중복 값에 대해 같은 순위를 부여하지만, 다음 순위는 연속된 정수로 매긴다.
ex) 1, 2, 2, 3, 4, 5, 5, 6 (중복된 값이 있어도 순위를 건너뛰지 않는다.)

CUME_DIST()

누적 분포 값을 계산한다.
0보다 크고 1보다 작거나 같은 값을 반환한다. (0 < 결과 <=1)
계산식: (해당 값보다 작거나 같은 값의 개수) / (총 행 수)

RANK()

중복 값에 대해 같은 순위를 부여하고, 다음 순위는 중복된 개수만큼 건너뛴다.
ex) 1, 2, 2, 4, 5, 5, 7

NTILE(n)

데이터를 n개의 그룹으로 균등하게 나눈다.
각 행에 1부터 n까지의 버킷 번호를 할당한다.
n은 양의 정수여야 한다.

70.아래 SQL과 동일한 결과를 출력하는 SQL로 가장 적절하지 않은 것은?

[SQL]

(동의여부가 'N'인 항목이 하나 이상 있는 회원을 선택한다.)

SELECT A.회원번호, A.회원명
FROM 회원 A, 동의항목 B
WHERE A.회원번호 = B.회원번호
GROUP BY A.회원번호, A.회원명
HAVING COUNT(CASE WHEN B.동의여부 'N' THEN 0 ELSE NULL END) >= 1
ORDER BY A.회원번호

①

(각 회원에 대해 '동의항목' 테이블에서 동의여부가 'N'인 항목이 존재하는지를 확인한다.)

SELECT A.회원번호, A.회원명
FROM 회원 A
WHERE EXISTS (SELECT 1
        	FROM 동의항목 B
                WHERE A.회원번호 = B.회원번호
                AND B.동의여부 = 'N')
ORDER BY A.회원번호;

②

(동의여부가 'N'인 모든 회원번호를 서브쿼리로 가져와서 IN 조건으로 비교한다.

IN은 전체 목록을 생성한 후 비교하므로, 데이터의 양이 많을 경우 EXISTS 보다 느릴 수 있다.)

SELECT A.회원번호, A.회원명
FROM 회원 A
WHERE A.회원번호 IN (SELECT B.회원번호
	FROM 동의항목 B
    WHERE B.동의여부 = 'N')
ORDER BY A.회원번호;

③

(현재의 서브쿼리에서는 동의여부가 'N'인 항목이 하나라도 있으면 모든 회원을 반환하거나, 없다면 아무것도 반환하지 않는다.)

SELECT A.회원번호, A.회원명
FROM 회원 A
WHERE 0 < (SELECT COUNT(*)
	FROM 동의항목 B
    WHERE B.동의여부 = 'N')
ORDER BY A.회원번호;

④

(회원 테이블과 동의항목 테이블을 조인한 후 동의여부가 'N'인 항목만 필터링한다.

GROUP BY를 사용하여 중복을 제거한다. 한 회원이 여러 'N' 항목을 가질 수 있기 때문이다.)

SELECT A.회원번호, A.회원명
FROM 회원 A, 동의항목 B
WHERE A.회원번호 = B.회원번호 AND B.동의여부 = 'N'
GROUP BY A.회원번호, A.회원명
ORDER BY A.회원번호;

(참고)

HAVING 절

GROUP BY로 그룹화된 결과에 대해 조건을 설정한다.
집계 함수의 결과를 필터링할 때 사용된다.
WHERE는 개별 행에 대한 필터링을 수행한다.
HAVING은 그룹화된 결과에 대한 필터링을 수행한다.
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY 순으로 작성한다.

CASE WHEN 구문

각 행에 대해 조건부 로직을 적용한다.

GROUP BY를 여러개의 컬럼으로 하면 컬럼의 조합에 대해 하나의 그룹이 생성된다.

71. 아래 SQL에 대한 설명으로 가장 적절한 것은?

[SQL]

SELECT A.회원ID, A.회원명, A.이메일
FROM 회원 A
㉠ WHERE EXISTS (SELECT 'X'
		FROM 이벤트 B, 메일발송 C
    	WHERE B.시작일자 >= '2014.10.01'
   	 AND B.이벤트ID = C.이벤트ID
㉡    AND A.회원ID = C.회원ID
㉢   HAVING COUNT(*) < (SELECT COUNT(*)
    		FROM 이벤트
            WHERE 시작일자
            >= '2014.10.01'));

① 이벤트 시작일자가 '2014.10.01'과 같거나 큰 이벤트를 대상으로 이메일이 발송된 기록이 있는 모든 회원을 추출하는 SQL이다. (HAVING 절에 대한 설명이 없다.)
② ㉡을 제거하고 ㉠의 EXISTS 연산자를 IN 연산자로 변경해도 결과는 동일하다.

(㉡을 제거하면 상관 서브쿼리가 아니게 된다.)
③ ㉢은 이벤트 시작일자가 '2014.10.01'과 같거나 큰 이벤트건수와 그 이벤트들을 기준으로 회원별 이메일 발송건수를 비교하는 것이다. (HAVING 절은 회원별 이메일 발송 건수와 전체 이벤트 건수를 비교한다.)
④ GROUP BY 및 집계 함수를 사용하지 않고 HAVING 절을 사용하였으므로 SQL이 실행되지 못하고 오류가 발생한다. (Oracle은 GROUP BY 없이도 HAVING 절을 사용할 수 있다.)

(참고)

상관 / 연관 서브쿼리(Correlated Subquery)

외부 쿼리의 컬럼을 참조하는 서브쿼리이다.
메인 쿼리의 테이블에서 하나의 행을 읽을 때마다 서브쿼리가 실행된다.
서브쿼리가 메인 쿼리의 컬럼을 참조하므로, 메인 쿼리에 종속적이다.
서브쿼리의 결과는 외부 쿼리의 HAVING 절이나 WHERE 절의 조건으로 사용된다.

WHERE EXISTS (SELECT 'X'
FROM 이벤트 B, 메일발송 C
WHERE B.시작일자 >= '2014.10.01'
AND B.이벤트ID = C.이벤트ID
AND A.회원ID = C.회원ID
...)

72. 서브쿼리에 대한 설명으로 가장 적절한 것은?

① 단일 행 서브쿼리는 서브쿼리의 실행 결과가 항상 한 건 이하인 서브쿼리로 IN, ALL 등의 비교 연산자를 사용하여야 한다.

(단일 행 서브쿼리는 =, <, >, <= 등의 표준 비교 연산자를 사용해야 한다. IN, ALL 등은 다중 행 서브쿼리에서 사용된다.)
② 다중 행 서브쿼리 비교 연산자는 단일 행 서브쿼리의 비교 연산자로도 사용할 수 있다.
③ 연관 서브쿼리는 주로 메인 쿼리에 값을 제공하기 위한 목적으로 사용한다.

(연관 서브쿼리는 주로 메인 쿼리의 각 행에 대해 조건을 확인하거나 필터링하는 데 사용된다. 값을 제공하는 것은 주로 비연관 서브쿼리의 역할이다.)
④ 서브쿼리는 항상 메인쿼리에서 읽힌 데이터에 대해 서브쿼리에서 해당 조건이 만족하는지를 확인하는 방식으로 수행된다.

(이는 연관 서브쿼리의 작동 방식을 설명하는 것이다. 비연관 서브쿼리는 메인 쿼리와 독립적으로 실행될 수 있으며, 때로는 메인 쿼리 실행 전에 한 번만 실행될 수도 있다.)

73. 아래 SQL에 대한 설명으로 가장 적절하지 않은 것은?

[SQL]

SELECT B.사원번호, B.사원명, A.부서번호, A.부서명,
	(SELECT COUNT(*)
    	FROM 부양가족 Y
        WHERE Y.사원번호 = B.사원번호) AS 부양가족수
FROM 부서 A, (SELECT *
		FROM 사원
        WHERE 입사년도 = '2014') B
WHERE A.부서번호 = B.부서번호
AND EXISTS (SELECT 1
	FROM 사원 X
    WHERE X.부서번호 = A.부서번호);

① 위 SQL에는 다중 행 연관 서브쿼리, 단일 행 연관 서브쿼리, 인라인 뷰(Inline View)가 사용되었다.

(SELECT 절의 부양가족수 계산은 다중 행 연관 서브쿼리,

EXISTS 조건의 서브쿼리는 단일 행 연관 서브쿼리,

FROM 절의 서브쿼리는 인라인 뷰이다.)
② SELECT 절에 사용된 서브쿼리는 스칼라 서브쿼리라고도 하며, 이러한 형태의 서브쿼리는 JOIN으로 동일한 결과를 추출할 수도 있다.

(SELECT 절의 서브쿼리는 스칼라 서브쿼리이고, LEFT JOIN과 GROUP BY를 사용하여 동일한 결과를 얻을 수 있다.)
③ WHERE 절의 서브쿼리에 사원 테이블 검색 조건으로 입사년도 조건을 FROM 절의 서브쿼리와 동일하게 추가해야 원하는 결과를 추출할 수 있다.

(WHERE 절의 EXISTS 서브쿼리는 부서에 사원이 존재하는지만 확인한다. 입사년도 조건을 추가할 필요가 없고 추가하면 결과가 달라질 수 있다.)
④ FROM 절의 서브쿼리는 동적 뷰(Dynamic View) 라고도 하며, SQL 문장 중 테이블 명이 올 수 있는 곳에서 사용할 수 있다. (FROM 절의 서브쿼리는 인라인 뷰 또는 동적 뷰라고 불리며, 테이블 이름이 올 수 있는 곳에서 사용할 수 있다.)

(참고)

다중 행 연관 서브쿼리

주로 SELECT, WHERE, HAVING 절에 위치한다.
외부 쿼리의 컬럼을 참조한다.
여러 행을 반환할 수 있다.
사용 연산자: IN, ANY, ALL, EXISTS
ex)

SELECT COUNT(*)
    	FROM 부양가족 Y
        WHERE Y.사원번호 = B.사원번호) AS 부양가족수

단일 행 연관 서브쿼리

주로 SELECT, WHERE, HAVING 절에 위치한다.
외부 쿼리의 컬럼을 참조한다.
항상 단일 행(또는 값)을 반환한다.
사용 연산자: =, <, >, <=, >=, <>
ex)

EXISTS (SELECT 1
	FROM 사원 X
    WHERE X.부서번호 = A.부서번호)

인라인 뷰

FROM절에 위치한다.
서브쿼리가 마치 테이블처럼 사용된다.
주로 외부 쿼리와 독립적이다.

(SELECT *
 FROM 사원
 WHERE 입사년도 = '2014') B

74. 평가대상상품에 대한 품질평가항목별 최종 평가결과를 출력하는 SQL로 가장 적절한 것은?

(단, 평가항목에 대한 평가등급이 기대수준에 미치지 못할 경우 해당 평가항목에 대해서만 재평가를 수행한다.)

①

(전체 평가결과 중 가장 최근(최대) 평가회차의 결과만 보여준다.

모든 상품과 평가항목에 대해 동일한 평가회차의 결과를 출력한다.

그러나 항목별 재평가를 고려하지 않는다.)

SELECT B.상품ID, B.상품명, C.평가항목ID, C.평가항목명, A.평가회차, A.평가등급, A.평가일자
FROM 평가결과 A, 평가대상상품 B, 품질평가항목 C,
	(SELECT MAX(평가회차) AS 평가회차 FROM 평가결과) D
WHERE A.상품ID = B.상품ID
AND A.평가항목ID = C.평가항목ID
AND A.평가회차 = D.평가회차;

②

(상품ID와 평가항목ID별로 가장 최근(최대) 평가회차를 선택한다.

이는 서브쿼리를 통해 구현된다.

각 상품의 각 평가항목에 대해 개별적으로 최신 평가결과를 가져온다.

이는 재평가가 항목별로 이뤄질 수 있다는 조건을 만족시킨다.

평가대상상품(B)과 품질평가항목(C) 테이블과 조인하여 상품명과 평가항목명을 함께 출력한다.

평가항목에 대한 재평가가 이루어질 수 있다는 걸 보여주는 부분:

WHERE X.상품ID = B.상품ID AND X.평가항목ID = C.평가항목ID 조건으로, 각 상품과 평가항목 조합에 대해 독립적으로 최대 평가회차를 갖는다.

이렇게 함으로써, 한 상품의 특정 평가항목이 재평가되었을 때, 다른 평가항목의 평가 결과에는 영향을 주지 않는다.)

SELECT B.상품ID, B.상품명, C.평가항목ID, C.평가항목명, A.평가회차, A.평가등급, A.평가일자
FROM 평가결과 A, 평가대상상품 B, 품질평가항목 C
WHERE A.상품ID = B.상품ID
AND A.평가항목ID = C.평가항목ID
AND A.평가회차 = (SELECT MAX(X.평가회차)
    	FROM 평가결과 X
        WHERE X.상품ID = B.상품ID
        AND X.평가항목ID = C.평가항목ID);

③

(각 상품ID와 평가항목ID 조합에 대해 최대 평가회차, 최대 평가등급, 최대 평가일자를 보여준다.

평가회차, 평가등급, 평가일자가 서로 다른 행에서 올 수 있어서 부정확할 수 있다.

ex. 최대 평가회차의 평가등급이 아닌 다른 회차의 최대 평가등급이 선택될 수 있다.)

SELECT B.상품ID, B.상품명, C.평가항목ID, C.평가항목명, MAX(A.평가회차) AS 평가회차,
	MAX(A.평가등급) AS 평가등급,
    MAX(A.평가일자) AS 평가일자
FROM 평가결과 A, 평가대상상품 B, 품질평가항목 C
WHERE A.상품ID = B.상품ID
AND A.평가항목ID = C.평가항목ID
GROUP BY B.상품ID, B.상품명, C.평가항목ID, C.평가항목명;

④

(서브쿼리에서 각 상품ID와 평가항목ID 조합에 대해 최대 평가회차, 최대 평가등급, 최대 평가일자를 선택한다.

GROUP BY를 사용하여 상품ID와 평가항목ID 별로 그룹화한다.

메인 쿼리에서는 이 결과를 평가대상상품과 품질평가항목 테이블과 조인하여 상품명과 평가항목명을 가져온다.

결과적으로 각 상품의 각 평가항목에 대해 가장 최근의 평가 결과를 보여준다.

MAX 함수를 사용하여 평가회차, 평가등급, 평가일자를 선택하기 때문에 세 값이 항상 같은 행에서 오는 것을 보장하지 않는다.)

SELECT B.상품ID, B.상품명, C.평가항목ID, C.평가항목명, A.평가회차, A.평가등급, A.평가일자
FROM (SELECT 상품ID, 평가항목ID, MAX(평가회차) AS 평가회차,
		MAX(평가등급) AS 평가등급,
        MAX(평가일자) AS 평가일자
		FROM 평가결과
        GROUP BY 상품ID, 평가항목ID) A, 평가대상상품 B, 품질평가항목 C
WHERE A.상품ID = B.상품ID
AND A.평가항목ID = C.평가항목ID;

75. 뷰에 대한 설명으로 가장 적절하지 않은 것은?

① 뷰는 단지 정의만을 가지고 있으며, 실행 시점에 질의를 재작성하여 수행한다.

(뷰는 실제 데이터를 저장하지 않고 쿼리 정의만을 저장한다. 뷰가 호출될 때 해당 쿼리가 실행되어 결과를 반환한다.)
② 뷰는 복잡한 SQL 문장을 단순화하는 장점이 있는 반면, 테이블 구조가 변경되면 응용 프로그램을 변경해주어야 한다.

(뷰는 복잡한 SQL 문장을 단순화하는 장점이 있으며, 기본 테이블의 구조가 변경되어도 뷰를 통해 접근하는 응용 프로그램은 변경할 필요가 없다.)
③ 뷰는 보안을 강화하기 위한 목적으로도 활용할 수 있다.

(특정 열이나 행에 대한 접근을 제한하는 뷰를 만들어서 민감한 데이터를 보호할 수 있다.)
④ 실제 데이터를 저장하고 있는 뷰를 생성하는 기능을 지원하는 DBMS도 있다.

(일부 DBMS에서는 물리적으로 데이터를 저장하는 '구체화된 뷰'(Materialized View)를 지원한다. 이는 쿼리 성능을 향상시키는 데 사용될 수 있다.)

(참고)

뷰 사용의 장점

이름	설명
독립성	테이블의 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.
편리성	복잡한 쿼리를 뷰로 생성하여 관련 쿼리를 단순하게 작성할 수 있다.
보안성	숨기고 싶은 컬럼을 빼고 뷰를 생성하여 민감한 데이터를 보호할 수 있다.

76. 아래에서 뷰 생성 스크립트를 실행한 후, 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'

① 0
② 200
③ 300
④ 400

77. 아래 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(PARTITION 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;

①

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

②

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

③

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

④

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

(참고)

ROW_NUMBER

결과 집합의 각 행에 고유한 순차적 번호를 할당한다.
OVER 절과 함께 사용되며, ORDER BY 를 통해 번호 부여 순서를 지정할 수 있다.
PARTITION BY를 사용하여 데이터를 그룹화하고 각 그룹 내에서 번호를 새로 시작할 수 있다.

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

윈도우 함수에서 프레임 범위를 지정할 때 사용되는 키워드이다.
현재 행을 기준으로 파티션의 첫 번째 행까지의 모든 행을 포함한다.
주로 누적 계산이나 이동 평균등을 구할 때 사용된다.
UNBOUNDED PRECEDING: 파티션의 첫 번째 행
CURRENT ROW 현재 처리중인 행

ROWS CURRENT ROW

현재 행만을 포함하는 윈도우 프레임을 정의한다.
보통 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW의 축약형으로 사용된다.

WINDOW 함수는 WHERE 절 조건을 적용하고 나서 계산된다.

OVER 절 내의 각 윈도우 함수는 독립적으로 동작한다. (다른 열의 연산에 영향 받지 않는다.)

78. 아래 실행 결과를 출력하는 SQL로 가장 적절한 것은?

[실행 결과]

지역명	이용월	이용량
서울	2014.01	1000
서울	2014.02	1000
서울	월별합계	2000
경기	2014.01	1000
경기	2014.03	2000
경기	월별합계	3000
대전	2014.05	1500
대전	2014.06	1000
대전	월별합계	2500
지역전체	월별합계	7500

①

SELECT (CASE GROUPING(B.지역명) WHEN 0 THEN '지역전체' ELSE B.지역명 END) AS 지역명,
	(CASE GROUPING(TO_CHAR(A.이용일시, 'YYYY.MM'))
    WHEN 0 THEN '월별합계'
    ELSE TO_CHAR(A.이용일시, 'YYYY.MM') END) AS 이용월,
    SUM(A.이용량) AS 이용량
FROM 이용내역 A INNER JOIN 지역 B ON (A.지역ID = B.지역ID)
GROUP BY ROLLUP(B.지역명, TO_CHAR(A.이용일시, 'YYYY.MM'));

② (MIN(B.지역명)을 사용하여 GROUP BY 절에서는 B.지역ID를 사용하고 있지만 결과에는 지역명을 사용한다.

각 지역ID에 대응하는 하나의 지역명을 선택하는 방법이다.)

SELECT (CASE GROUPING(B.지역ID) WHEN 1 THEN '지역전체' ELSE MIN(B.지역명) END) AS 지역명,
	(CASE GROUPING(TO_CHAR(A.이용일시, 'YYYY.MM'))
    WHEN 1 THEN '월별합계'
    ELSE TO_CHAR(A.이용일시, 'YYYY.MM') END) AS 이용월,
    SUM(A.이용량) AS 이용량
FROM 이용내역 A INNER JOIN 지역 B ON (A.지역ID = B.지역ID)
GROUP BY ROLLUP(B.지역ID, TO_CHAR(A.이용일시, 'YYYY.MM'));

③

SELECT (CASE GROUPING(B.지역명) WHEN 1 THEN '지역전체' ELSE B.지역명 END) AS 지역명,
	(CASE GROUPING(TO_CHAR(A.이용일시, 'YYYY.MM'))
    WHEN 1 THEN '월별합계'
    ELSE TO_CHAR(A.이용일시, 'YYYY.MM') END) AS 이용월,
    SUM(A.이용량) AS 이용량
FROM 이용내역 A INNER JOIN 지역 B ON (A.지역ID = B.지역ID)
GROUP BY CUBE(B.지역명, TO_CHAR(A.이용일시, 'YYYY.MM'));

④

SELECT (CASE GROUPING(B.지역명) WHEN 1 THEN '지역전체' ELSE MIN(B.지역명) END) AS 지역명,
	(CASE GROUPING(TO_CHAR(A.이용일시, 'YYYY.MM'))
    WHEN 1 THEN '월별합계'
    ELSE TO_CHAR(A.이용일시, 'YYYY.MM') END) AS 이용월,
    SUM(A.이용량) AS 이용량
FROM 이용내역 A INNER JOIN 지역 B ON (A.지역ID = B.지역ID)
GROUP BY GROUPING SETS(B.지역ID, TO_CHAR(A.이용일시, 'YYYY.MM'));

(참고)

GROUPING(컬럼)

GROUP BY 절에서 사용된다.
해당 컬럼이 그룹화에 사용되었는지 여부를 나타내는 지표를 반환한다.
그룹화에 사용된다는 것은 SQL의 GROUP BY 절에서 해당 컬럼이 데이터를 분류하는 기준으로 사용된다는 것을 의미한다.
- 1: 해당 컬럼이 그룹화에 사용되었을 때
- 0: 해당 컬럼이 그룹화에 사용되지 않았을 때 (NULL로 집계되었을 때)
ex) ROLLUP(지역명, 이용월)을 사용한 경우
- 일반 행:GROUPING(지역명) = 0, GROUPING(이용월) = 0
- 지역별 소계 행: GROUPING(지역명) = 0, GROUPING(이용월) = 1
- 전체 합계 행: GROUPING(지역명) = 1, GROUPING(이용월) = 1

79. 아래 SQL에 대한 설명으로 가장 적절한 것은?

[SQL]

SELECT EMPNO, SAL
FROM EMP
WHERE SAL >= (SELECT MAX(SAL)
			FROM EMP
            GROUP BY DEPTNO);

(설명)

위 쿼리에는 문제가 있다.

1. 메인 쿼리의 WHERE 절에서 서브쿼리를 사용하고 있다.

2. 이 서브쿼리는 GROUP BY DEPTNO를 사용하고 있어서, 각 부서별로 최대 급여를 계산한다.

3. 그 결과, 서브쿼리는 여러 개의 행(각 부서별 최대 급여)를 반환한다.

4. 그러나 메인 쿼리의 WHERE 절에서는 단일 값과의 비교를 시도하고 있다.

이로 인해 "단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다." 라는 오류가 발생하게 된다.

아래처럼 쓰면 쿼리를 수정할 수 있다.

ALL 연산자를 사용하면 다중 행 결과와의 비교가 가능해진다.

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

① '단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.' 오류가 발생한다.

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

(부서별 최고연봉 중 하나보다 크거나 같은 연봉을 가진 사원을 출력하는 쿼리이다.)

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

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

(부서별 최고연봉 전부보다 크거나 같은 연봉을 가진 사원을 출력하는 쿼리이다.)

SELECT EMPNO, DEPTNO, SAL
FROM emp
WHERE SAL >= ALL(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

(참고)

ANY

서브쿼리에서 반환된 값들 중 "하나라도" 조건을 만족하면 TRUE

ALL

서브쿼리에서 반환된 "모든" 값이 조건을 만족해야 TRUE

80. 아래를 참고할 때 SQL의 빈칸 ㉠에 들어갈 내용으로 가장 적절한 것은?

[SQL]

SELECT DNAME, JOB, COUNT(EMPNO) TOTAL_EMP, SUM(SAL) TOTAL_SAL
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ㉠ (DNAME, JOB);

[실행 결과]

DNAM	JOB	TOTAL_EMP	TOTAL_SAL
SALES	CLERK	1	950
SALES	MANAGER	1	2850
SALES	SALESMAN	4	5600
SALES	NULL	6	9400
RESEARCH	CLERK	2	1900
RESEARCH	ANALYST	2	6000
RESEARCH	MANAGER	1	2975
RESEARCH	NULL	5	10875
ACCOUNTING	CLERK	1	1300
ACCOUNTING	MANAGER	1	2450
ACCOUNTING	PRESIDENT	1	5000
ACCOUNTING	NULL	3	8750
NULL	NULL	14	29025

① ROLLUP (DNAME, JOB)

((DNAME, JOB), (DNAME, NULL), (NULL, NULL))
② CUBE (DNAME, JOB)

((DNAME, JOB), (DNAME, NULL), (NULL, JOB), (NULL, NULL))
③ GROUPING SETS ((DNAME, JOB), DNAME)

((DNAME, JOB), (DNAME, NULL))
④ DNAME, ROLLUP (JOB)

((DNAME, JOB), (DNAME, NULL))

'Archive > SQLD' 카테고리의 다른 글

[SQLD] 데이터 모델링의 이해 문제 (1-50) (2)	2024.10.28
[SQLD] 데이터 모델링의 이해 문제 (81-126) (2)	2024.10.19
[SQLD] SQL 기본 (0)	2024.02.24
[SQLD] 데이터 모델과 성능 (0)	2024.02.21
[SQLD] 데이터 모델링의 이해 (0)	2024.02.19

기초부터 차곡차곡

[SQLD] 데이터 모델링의 이해 문제 (41-80)

'Archive > SQLD' 카테고리의 다른 글

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

[SQLD] 데이터 모델링의 이해 문제 (41-80)

'Archive > SQLD' 카테고리의 다른 글

관련글

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역