포스트

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

게시
By tonyleekorea 33 분읽는 시간

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

②

[설비]

설비ID설비명 
1설비1 
2설비2 
3설비3 

[에너지사용]

설비ID에너지코드사용량
1전기100
1용수200
1바람300
2전기200
2용수300
3전기300

[실행 결과]

설비ID에너지코드사용량합계
1바람300
1용수200
1전기100
1600
2용수300
2전기200
2500
3전기300
3300
바람300
용수500
전기600
1400
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

① 
SELECT A.설비ID, B.에너지코드, SUM(B.사용량) AS 사용량합계
FROM 설비 A 
INNER JOIN 에너지사용량 B
ON (A.설비ID = B.설비ID)
GROUP BY CUBE ((A.설비ID), (B.에너지코드), (A.설비ID, B.에너지코드))
ORDER BY A.설비ID, B.에너지코드;

② 
SELECT A.설비ID, B.에너지코드, SUM(B.사용량) AS 사용량합계 
FROM 설비 A 
INNER JOIN 에너지사용량 B 
ON (A.설비ID = B.설비ID)
GROUP BY CUBE (A.설비ID, B.에너지코드)
ORDER BY A.설비ID, B.에너지코드;

③ 
SELECT A.설비ID, B.에너지코드, SUM(B.사용량) AS 사용량합계 
FROM 설비 A 
INNER JOIN 에너지사용량 B 
ON (A.설비ID = B.설비ID)
GROUP BY GROUPING SETS((A.설비ID), (A.설비ID, B.에너지코드))
ORDER BY A.설비ID, B.에너지코드;

④
SELECT A.설비ID, B.에너지코드, SUM(B,사용량) AS 사용량합계 
FROM 설비 A 
INNER JOIN 에너지사용량 B 
ON (A.설비ID = B.설비ID)
GROUP BY GROUPING SETS((A.설비ID), (B.에너지코드), (A.설비ID, B.에너지코드))
ORDER BY A.설비ID, B.에너지코드;
  • CUBE는 결합 가능한 모든 값에 대하여 다차원집계를 생성한다. CUBE도 결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬 칼럼이 표시되어야 한다.
  • SQL의 결과를 보면 설비ID와 에너지코드의 모든 조합에 대하여 사용량합계를 추출하고 있다. CUBE 함수는 인수로 나열된 항목의 가능한 모든 조합에 대하여 GROUPING을 수행한다. 또한 GROUPING SETS은 사용자가 원하는 다양한 조합을 인수로 사용할 수 있다. 위 문제에서 ②번은 CUBE를 사용하였으므로 CUBE절에 나열된 칼럼의 모든 조합 즉, ((설비ID), (에너지코드), (설비ID, 에너지코드), ())에 대해 SUB TOTAL을 만들게 된다. ①, ③, ④의 보기별 결과는 아래와 같다.

①

설비ID에너지코드사용량
1바람300
1바람300
1바람300
1바람300
1바람300
1용수200
1용수200
1용수200
1용수200
1용수200
1전기100
1전기100
1전기100
1전기100
1전기100
1600
2용수300
2용수300
2용수300
2용수300
2용수300
2전기200
2전기200
2전기200
2전기200
2전기200
2500
3전기300
3전기300
3전기300
3전기300
3전기300
3300
바람300
용수500
전기600
1400

③ 답지 오류

  • 2가지 기준으로 그룹핑(설비ID), (설비ID, 에너지코드)하므로 (에너지 코드)로 그룹핑된 마지막 3줄을 결과에서 제외해야 한다.
설비ID에너지코드사용량
1바람300
1용수200
1전기100
1600
2용수300
2전기200
2500
3전기300
3300
바람300
용수500
전기600

④

3가지 기준으로 그룹핑(설비ID), (설비ID, 에너지코드), (에너지코드)

설비ID에너지코드사용량
1바람300
1용수200
1전기100
1600
2용수300
2전기200
2500
3전기300
3300
바람300
용수500
전기600

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

②

1
2
3

SELECT A.JOB, A.DEPTNO, ROUND(AVG(A.SAL), 2) AVG_SAL 
FROM EMP A
GROUP BY ㉠;

[실행 결과]

JOBDEPTNOAVG_SAL
CLERK101300
CLERK20950
CLERK30950
ANALYST203000
MANAGER102450
MANAGER202975
MANAGER302850
SALESMAN301400
PRESIDENT105000
2073.21

① ROLLUP (JOB, DEPTNO)

② ROLLUP ((JOB, DEPTNO))

③ ROLLUP ((JOB), (DEPTNO))

④ JOB, ROLLUP(DEPTNO)

  • (JOB, DEPTNO), ()에 대한 평균을 계산해야 하므로 ROLLUP((JOB, DEPTNO)) 표현식을 사용해야 한다.
  • 실행 결과를 보며 몇 가지 그룹이 있는지 구별해야한다. NULL 값을 보고 판단 가능하다. 문제의 예시는 두 가지 그룹이 있다. (JOB, DEPTNO), ()

  • 롤업은 단계별 소계로 오른쪽 항목을 하나씩 지워나가면서 그룹핑한다. 괄호는 하나의 묶음으로 본다.

    ① ROLLUP(A, B)

    • (A, B)
    • (A)
    • ()

    ② ROLLUP((A, B))

    • (A, B)
    • ()

    ③ ROLLUP((A), (B))

    • ((A), (B)) → (A, B)
    • ((A)) → (A)
    • ()

    ④ A, ROLLUP(B)

    • A, (B) → (A, B)
    • A, () → (A)

이미지

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

②

상품ID월매출액
P0012014.101500
P0012014.111500
P0012014.122500
P0022014.101000
P0022014.112000
P0022014.121500
P0032014.102000
P0032014.111000
P0032014.121000

[월별매출]

상품ID월매출액
P0012014.101500
P0012014.111500
P0012014.122500
P0022014.101000
P0022014.112000
P0022014.121500
P0032014.102000
P0032014.111000
P0032014.121000
1
2
3
4

SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
WHERE 월 BETWEEN '2014.10' AND '2014.12'
GROUP BY GROUPING SETS((상품ID, 월));
  • GROUPING SETS는 다양한 소계 집합을 만들 수 있는데, GROUPING SETS에 표시된 인수들에 대한 개별 집계를 구할 수 있으며, 이때 표시된 인수들 간에는 계층 구조인 ROLLUP과는 달리 평등한 관계이므로 인수의 순서가 바뀌어도 결과는 같다.
  • GROUPING SETS 함수도 결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬 칼럼이 표시되어야 한다.
  • GROUPING SETS 함수는 표시된 인수들에 대한 개별 집계를 구하는 기능을 하며, 위의 SQL은 (상품ID, 월)별 집계 데이터를 출력한다. 각 보기별 SQL은 아래와 같다.
1
2
3
4
5

① GROUPING SETS에 괄호를 사용하지 않아 월별과 상품ID별로 각각 집계되었다.
SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
WHERE 월 BETWEEN '2014.10' AND '2014.12'
GROUP BY GROUPING SETS(월, 상품ID);
상품ID월매출액
2014.104500
2014.114500
2014.125000
P0015500
P0024500
Р0034000
1
2
3
4
5
6

③ GROUPING SETS에 월별, 상품ID별과 전체가 각각 집계되었다.
SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
WHERE 월 BETWEEN '2014.10' AND '2014.12'
GROUP BY GROUPING SETS(월, 상품ID, ());
상품ID월매출액
2014.104500
2014.114500
2014.125000
P0015500
P0024500
Р0034000
1400
1
2
3
4
5

④ GROUPING SETS에 (월, 상품ID)별, 월별로 집계되었다.
SELECT 상품ID, 월, SUM(매출액) AS 매출액
FROM 월별매출
WHERE 월 BETWEEN '2014.10' AND '2014.12'
GROUP BY GROUPING SETS((월, 상품ID), 월);
상품ID월매출액
P0012014.101500
P0022014.101000
P0032014.102000
2014.104500
P0012014.111500
P0022014.112000
P0032014.111000
2014.114500
P0012014.122500
P0022014.121500
P0032014.121000
2014.125000

Q. 원도우 함수(Window Function)에 대한 설명으로 가장 적절하지 않은 것은?

③

① PARTITION BY 절과 GROUP BY 절은 의미적으로 유사하다.

② PARTITION BY 절이 없으면 전체 집합을 하나의 Partition으로 정의한 것과 동일하다.

③ 윈도우 함수 처리로 결과 건수가 줄어든다.

④ 윈도우 함수 적용 범위는 Partition을 넘을 수 없다.

  • 윈도우 함수는 결과에 대한 함수처리이기 때문에 결과 건수는 줄지 않는다.(집약 기능x)

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

고객번호고객명매출액순위
005이규혁7001
004이상화7001
002이순신5503
001홍길동3504
003강감찬3504

[고객]

고객번호(PK)고객명
001홍길동
002이순신
003강감찬
004이상화
005이규혁

[월별매출]

월(PK)고객번호(PK)매출액
201301001200
201301002300
201301003250
201301004300
201301005250
201302001150
201302002150
201302004200
201302005100
201303002100
201303003100
201303004200
201303005350
1
2
3
4
5
6
7
8
9

SELECT 고객번호, 고객명, 매출액, RANK() OVER(ORDER BY 매출액 DESC) AS 순위
FROM (
	SELECT A.고객번호, MAX(A.고객명) AS 고객명, SUM(B.매출액) AS 매출액
	FROM 고객 A 
	INNER JOIN 월별매출 B
	ON (A.고객번호 = B.고객번호)
	GROUP BY A.고객번호
	)
ORDER BY 순위;
  • RANK 함수는 ORDER BY를 포함한 QUERY문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수이며 동일한 값에 대해서는 동일한 순위를 부여한다.
  • 위의 SQL은 고객별 매출액과 매출 순위를 구하되 동일 순위일 경우 중간 순위를 비워둔 데이터를 추출한다. 순위를 구하는 함수로는 RANK, DENSE_RANK, ROW_NUMBER 함수가 있다. RANK WINDOW FUNCTION은 동일 값에 대해서는 동일 순위를 부여하고 중간 순위는 비워 두지만, DENSE_RANK 함수는 동일 순위를 부여하되 중간 순위를 비우지 않는다. ROW_NUMBER 함수는 동일 값에 대해서도 유일한 순위를 부여한다.

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

④ DENSE_RANK

1
2
3

SELECT JOB, ENAME, SAL, COMM, 
	㉠ OVER(ORDER BY NVL(SAL, O) + NVL(COMM, 0) DESC) RANK
FROM EMP;

[실행 결과]

JOBENAMESALCOMMRANK
PRESIDENTKING50001
ANALYSTFORD30002
ANALYSTSCOTT30002
MANAGERJONES29753
MANAGERBLAKE28504
SALESMANMARTIN125014005
MANAGERCLARK24506
SALESMANALLEN16003007
SALESMANWARD12505008
SALESMANTURNER150009
CLERKMILLER130010
CLERKADAMS110011
CLERKJAMES95012
CLERKSMITH80013

① ROWNUM

② ROW NUMBER

③ RANK

④ DENSE_RANK

  • DENSE_RANK 함수는 RANK 함수와 흡사하나, 동일한 순위를 하나의 건수로 취급하는 것이 다른 점이다.
  • 동순위를 부여하며, 이후 차순위를 나타내는 것은 DENSE_RANK의 특징이다.
  • NVL(COMM, 0)이면 NULL 값이 나올 수 없는데, COMM 칼럼에 NULL 값이 존재 - NVL은 RANK 절 안에서 처리, SELECT 절 COMM 칼럼 존재

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

추천경로추천인피추천인추천점수
SNS이순신강감찬80
이벤트응모홍길동강감찬88
홈페이지홍두깨심청이98

[추천내역]

추천경로추천인피추천인추천점수
SNS나한일강감찬75
SNS이순신강감찬80
이벤트응모홍길동강감찬88
이벤트응모저절로이순신78
홈페이지저절로이대로93
홈페이지홍두깨심청이98
1
2
3
4
5

SELECT 추천경로, 추천인, 피추천인, 추천점수
FROM (SELECT 추천경로, 추천인, 피추천인, 추천점수,
	 ROW_NUMBER() OVER(PARTITION BY 추천경로 ORDER BY 추천점수 DESC) AS RNUM
	 FROM 추천내역)
WHERE RNUM = 1;
추천경로추천인피추천인추천점수RNUM
SNS나한일강감찬752
SNS이순신강감찬801
이벤트응모홍길동강감찬881
이벤트응모저절로이순신782
홈페이지저절로이대로932
홈페이지홍두깨심청이981
  • ROW_NUMBER 함수는 RANK나 DENSE_RANK 함수가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해, 동일한 값이라도 고유한 순위를 부여한다.
  • ROW_NUMBER 함수는 ORDER BY절에 의해 정렬된 데이터에 동일 값이 존재하더라도 유일한 순위를 부여하는 함수로서 데이터 그룹 내에 유일한 순위를 추출할 때 사용할 수 있는 함수이다.
  • 문제의 SQL은 추천경로별(PARTITION BY 추천경로)로 추천점수가 가장 높은(ORDER BY 추천점수 DESC) 데이터를 한 건씩만 출력한다.

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

③

1
2
3
4

SELECT 상품분류코드, AVG(상품가격) AS 상품가격, 
	COUNT(*) OVER(ORDER BY AVG(상품가격) RANGE BETWEEN 10000 PRECEDING AND 10000 FOLLOWING) AS 유사개수
FROM 상품
GROUP BY 상품분류코드;

① WINDOW FUNCTION을 GROUP BY 절과 함께 사용하였으므로 위의 SQL은 오류가 발생한다.

② WINDOW FUNCTION의 ORDER BY절에 AVG 집계 함수를 사용 하였으므로 위의 SQL은 오류가 발생한다.

③ 유사개수 칼럼은 상품분류코드별 평균상품가격을 서로 비교하여 -10000~ + 10000 사이에 존재하는 상품분류코드의 개수를 구한 것이다.

④ 유사개수 칼럼은 상품전체의 평균상품가격을 서로 비교하여 -10000 ~ +10000 사이에 존재하는 상품의 개수를 구한 것이다

  • GROUP BY 절의 집합을 원본으로 하는 데이터를 WINDOW FUNCTION과 함께 사용한다면 GROUP BY 절과 함께 WINDOW FUNCTION을 사용한다고 하더라도 오류가 발생하지 않는다. 유사개수 칼럼은 상품분류코드로 GROUPING된 집합을 원본집합으로 하여 상품분류코드별 평균상품가격을 서로 비교하여 현재 읽힌 상품분류코드의 평균가격 대비 -1000 ~ + 1000 사이에 존재하는 상품분류코드의 개수를 구한 것이다.

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

사원ID부서ID사원명연봉
002100강감찬3000
003200김유신4500
006300변사포4500

[사원]

사원ID부서ID사원명연봉
001100홍길동2500
002100강감찬3000
003200김유신4500
004200김선달3000
005200유학생2500
006300변사포4500
007300박문수3000
1
2
3
4
5

SELECT Y.사원ID, Y.부서ID, Y.사원명, Y.연봉
FROM (
	SELECT 사원ID, MAX(연봉) OVER(PARTITION BY 부서ID) AS 최고연봉
	FROM 사원) X, 사원 Y
WHERE X.사원ID = Y.사원D AND X.최고연봉 = Y.연봉;

  • 안쪽 IN-LINE-VIEW에 의해 아래와 같이 사원ID와 부서별 최고연봉이 결과로 생성되며

    사원ID최고연봉
    0013000
    0023000
    0034500
    0044500
    0054500
    0064500
    0074500

    이를 다시 사원 테이블과 사원ID = 사원ID AND 최고연봉 = 연봉으로 JOIN을 하면 부서별 최고연봉의 사원이 출력된다. 아래의 SQL로도 동일한 결과를 얻을 수 있다.

    1
2
3
4
5
6

      SELECT 사원ID, 사원명, 부서ID, 연봉
  FROM (
  	SELECT 사원ID, 사원명, 부서ID, 연봉, MAX(연봉) OVER(PARTITION BY 부서ID) AS 최고연봉
  	FROM 사원
  	)
  WHERE 연봉 = 최고연봉

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

④ LAG

1
2
3

SELECT EMPNO, HIREDATE, SAL, ㉠(SAL) OVER (ORDER BY HIREDATE) AS SAL2
FROM EMP
WHERE JOB = 'SALESMAN';

[실행결과]

EMPNOHIREDATESALSAL 2
74992018-02-201600
75212018-02-2212501600
78442018-09-0815001250
76542018-09-2812501500

① LEAD

② ROW_NUMBER

③ RANK

④ LAG

  • LAG(가져올 값, 이전 N번째 행(생략 시 1), 대체값(생략 시 NULL))
    • LAG 함수를 이용해 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다. 해당 문제에서 사용된 LAG 함수는 인자 값이 하나이므로, 이전 1번째 행(default)의 값을 가져온다.

  • LAG 함수를 이용해 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다. 이후 몇 번째 행의 값을 가져오는 것은 LEAD 함수이며, SQL Server에서는 지원하지 않는 함수이다.

    이미지

    이미지

Q. GRANT와 REVOKE에 대한 설명으로 가장 적절하지 않은 것은?

③

① 어떤 사용자가 WITH GRANT OPTION과 함께 권한을 허가받았으면 그 사용자는 해당 권한을 WITH GRANT OPTION 유무와 관계없이 다른 사용자에게 허가할 수 있다.

② PUBLIC을 사용하면 자신에게 허가된 권한을 모든 사용자들에게 허가할 수 있다.

③ REVOKE 문을 사용하여 권한을 취소하더라도 권한을 취소당한 사용자가 WITH GRANT OPTION을 통해서 다른 사용자에게 허가했던 권한들까지 연쇄적으로 모두 취소되는 것은 아니다.

④ REVOKE 문을 사용하여 권한을 취소할 때 그 권한을 허가한 사용자가 권한을 취소할 수 있다.

  • REVOKE 문을 사용하여 권한을 취소하면 권한을 취소당한 사용자가 WITH GRANT OPTION을 통해서 다른 사용자에게 허가했던 권한들도 모두 연쇄적으로 취소된다.

Q. B_User가 아래의 작업을 수행할 수 있도록 권한을 부여하는 DCL로 가장 적절한 것은?

④ GRANT SELECT, UPDATE ON A_User.TB_A TO B_User;

1

UPDATE A_User.TB_A SET coll='AAA' WHERE col2=3

① GRANT SELECT, UPDATE TO B_User;

② REVOKE SELECT ON A_User.TB_A FROM B_User;

③ DENY UPDATE ON A_User.TB_A TO B_User;

④ GRANT SELECT, UPDATE ON A_User.TB_A TO B_User;

  • 권한을 부여하는 명령어는 GRANT이며, WHERE 조건의 데이터를 찾기 위한 SELECT 권한과 데이터 변경을 위한 UPDATE 권한이 필요하다.

Q. 아래는 EMPLOYEE 스키마뿐만 아니라 연관된 객체들도 모두 삭제하는 SQL 명령어이다. 빈칸 ㉠에 들어갈 내용으로 가장 적절한 것은?

③ CASCADE

1

DROP SCHEMA EMPLOYEE ㉠;

① NULL

② NOT NULL

③ CASCADE

④ RESTRICT

  • RESTRICT는 스키마가 공백인 경우에만 삭제된다.

Q. 사용자 Lee가 테이블 R을 생성한 후 아래의 SQL을 실행하였다. 그 이후에 실행 가능한 SQL로 가장 적절한 것은? (단, A, B의 데이터 타입은 정수형이다.)

① Park: SELECT * FROM R WHERE A = 400;

1
2
3
4
5
6
7
8
9
10
11

Lee:
GRANT SELECT, INSERT, DELETE ON R TO Kim WITH GRANT OPTION;

Kim: 
GRANT SELECT, INSERT, DELETE ON R TO Park;

Lee: 
REVOKE DELETE ON R FROM Kim CASCADE;

Lee: 
REVOKE INSERT ON R FROM Kim CASCADE;

① Park: SELECT * FROM R WHERE A = 400;

② Park: INSERT INTO R VALUES(400, 600);

③ Park: DELETE FROM R WHERE B = 800;

④ Kim: INSERT INTO R VALUES(500, 600);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Lee:
GRANT SELECT, INSERT, DELETE ON R TO Kim WITH GRANT OPTION;
-- Kim에게 테이블 R에 SELECT, INSERT, DELETE 권한을 주면서, 
-- Kim이 다른 유저에게 테이블 R에 동일한 권한을 줄 수 있다.

Kim: 
GRANT SELECT, INSERT, DELETE ON R TO Park;
-- Kim이 테이블 R에 Lee에게 받은 권한을 Park에게 준다. 

Lee: 
REVOKE DELETE ON R FROM Kim CASCADE;
-- Kim과 Park에서 DELETE 권한을 취소한다. 
-- WITH GRANT OPTION으로 Kim으로부터 받은 Park의 권한은 CASCADE 명령어로 받은 권한을 취소할 수 잇다.

Lee: 
REVOKE INSERT ON R FROM Kim CASCADE;
-- Kim과 Park에서 INSERT 권한을 취소한다. 
-- WITH GRANT OPTION으로 Kim으로부터 받은 Park의 권한은 CASCADE 명령어로 받은 권한을 취소할 수 있다.

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

②

1
2
3
4
5

SELECT X.keyb, Y.col1, SUM(X.col1) AS sumcol1
FROM Table_B X, Table_A Y
WHERE X.keya = Y.keya
GROUP BY CUBE(X.keyb, Y.col1)
ORDER BY keyb, col1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

①
SELECT X.keyb, Y.col1, SUM(X.col1) AS sumcol1
FROM Table_B X, Table_A Y
WHERE X.keya = Y.keya
GROUP BY ROLLUP(X.keyb, Y.col1)
ORDER BY keyb, col1;

②
SELECT X.keyb, Y.col1, SUM(X.col1) AS sumcol1
FROM Table_B X, Table_A Y
WHERE X.keya = Y.keya
GROUP BY GROUPING SETS((X.keyb, Y.col1), (X.keyb), (Y.coll), ())
ORDER BY keyb, col1;

③
SELECT X.keyb, MAX(Y.col1) AS col1, SUM(X.col1) AS sumcol1
FROM Table_B X, Table_A Y
WHERE X.keya = Y.keya
GROUP BY X.keyb
UNION ALL
SELECT MAX(X.keyb) AS keyb, Y.col1, SUM(X.col1) AS sumcol1
FROM Table_B X, Table_A Y
WHERE X.keya = Y.keya
GROUP BY Y.col1
ORDER BY keyb, coll1;

④
SELECT X.keyb, MAX(Y.col1) AS col1, SUM(X.col1) AS sumcol1
FROM Table_B X, Table_A Y
WHERE X.keya = Y.keya
GROUP BY X.keyb
UNION ALL
SELECT MAX(X.keyb) AS keyb, Y.col1, SUM(X.col1) AS sumcol1
FROM Table_B X, Table_A Y
WHERE X.keya = Y.keya
GROUP BY Y.col1
UNION ALL
SELECT X.keyb, Y.col1, SUM(X.col1) AS sumcol1
FROM Table_B X, Table_A Y
WHERE X.keya = Y.keya
GROUP BY X.keyb, Y.col1
ORDER BY keyb, col1;
  • CUBE 그룹함수는 인수에 나열된 모든 칼럼의 가능한 조합에 대해서 소계를 함께 도출한다. CUBE는 GROUPING SETS 그룹함수를 이용하여 동일한 결과를 도출할 수 있다. 위의 SQL과 동일한 결과를 도출하기 위해서는 GROUPING SETS((X.keyb, Y.col1), (X.keyb), (Y.col1), ())를 이용하면 된다.
  • ④번의 경우 전체 합계 하나만 더 추가되면 동일한 결과를 도출할 수 있다. CUBE의 4가지 기준 중 3가지를 따로 구해 UNION ALL 적용
  • 보기의 SQL 결과와 동일한 결과를 출력하기 위해 MAX() 사용

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

③ 여러 개의 SQL문의 결과에 대한 교집합으로, 중복된 행은 하나의 행으로 출력한다.

① 여러 개의 SQL문의 결과에 대한 합집합으로, 중복된 행은 하나의 행으로 출력한다.

② 여러 개의 SQL문의 결과에 대한 합집합으로, 중복된 행도 그대로 결과로 출력한다.

③ 여러 개의 SQL문의 결과에 대한 교집합으로, 중복된 행은 하나의 행으로 출력한다.

④ SQL문 결과간의 차집합으로, 중복된 행은 하나의 행으로 출력한다

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

③ 2

[emp]

EMPNOENAMEJOBSAL
7698BLAKEMANAGER2850
7782CLARKMANAGER2450
7566JONESMANAGER2975
7499ALLENSALESMAN1600
7654MARTINSALESMAN1250
7844TURNERSALESMAN1500
7521WARDSALESMAN1250
1
2
3

SELECT a.empno, a.ename , a.job, 
	LAG(a.sal, ㉠) OVER (PARTITION BY a.job ORDER BY a.ename) AS prev_sal
FROM emp a;

[실행 결과]

EMPNOENAMEJOBPREV_SAL
7698BLAKEMANAGER
7782CLARKMANAGER
7506JONESMANAGER2850
7499ALLENSALESMAN
7654MARTINSALESMAN
7844TURNERSALESMAN1600
7521WARDSALESMAN1250

① 0

② 1

③ 2

④ 4

  • LAG 함수는 3개인 인자까지 사용할 수 있는데, 두 번째 인자는 몇 번째 앞의 행을 가져올지를 지정한다. 생략할 경우, 기본 값은 1이다. 이 예제에서는 2번째 앞의 행을 가져오므로 생략할 수 없고 반드시 2라고 적어야 한다.

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

RANK

[TBL]

COL1COL2
1100
2100
3200
4300
5300
6300
7400
8500
1
2

SELECT COL1, COL2, ㉠() OVER (ORDER BY COL2) COL3
FROM TBL;

[실행 결과]

COL1COL2COL3
11001
21001
32003
43004
63004
53004
74007
85008
  • RANK 함수는 동일한 순위를 별개의 건수로 취급한다.


참고 자료

Certificate, [SQLD] 개념 및 문제풀이
이 기사는 저작권자의 CC BY 4.0 라이센스를 따릅니다.