** 문제 및 정답 수정사항
https://www.dataq.or.kr/www/board/files/list.do
데이터자격검정
데이터자격검정, 빅데이터분석기사, DAP, DAsP, SQLP, SQLD, ADP, ADsP
www.dataq.or.kr
1과목
39번 - 정답 3번.
(동영상 풀이 강의 2:37)
[학생] 테이블에서
[학과] 정보 빼기,
[LAB실 이용]과 관련 정보도 빼기,
LAB실 이용에 보면 '교수번호'와 '교수명'도 겹치는 것을 통해 [교수] 테이블도 분리하기.
[도서 대출]에서
[대출자]로 대출자와 관련된 내용 빼기 (대출자 번호, 대출자 명, 대출자 신분 구분),
[도서]와 관련된 내용도 빼기 (대출도서번호, 대출도서명, 출판사명, 출판년월, 대표저자명, ISBN)
47번 - 정답 1번.
**문제 정정하기
(단, (A)에서 주식별자는 [사번, 부서]이며, 함수 종속성 부서→사무실이 존재한다고 가정)
2번은 맞는 말이다.
그림 (A)는 제2 정규화가 이루어지지 않고 있다.
일반 속성인 '사무실'이 PK 전체인 [사번, 부서]에 종속되지 않기 때문이다.
그래서 제2 정규화를 수행했다.
3번은 맞는 말이다.
그림 (B)는 제2 정규형를 만족하면서, 제3 정규형도 만족하게 되었다.
4번도 맞는 말이다.
정규화릉 통해 사번으로 사무실을 찾으려면 두 테이블을 조인해야 하니, 성능이 저하된다.
2과목 - 1장
9번 - 정답 4번.
집계 연산 시 NULL 무시.
일반 연산 시 NULL이 포함되어 있으면, 결과는 NULL.
- 60 + 30 = 90
- NULL + 20 = NULL
- 10 + 20 = 30
- 50 + 10 = 60
15번 - 정답 3번.
REPLACE(칼럼명, 찾을문자, 치환문자) 에서 '치환문자' 값이 없으면 '찾을문자'를 삭제한다. 즉, 줄 바꿈을 삭제한다.
그래서 원래는 줄 바꿈까지 포함된 식에서
첫 번째 줄은 LENGTH가 3,
두 번째 줄은 LENGRLTH가 5이다.
REPLACE를 통해 줄 바꿈이 삭제된 식에 수는
첫 번째 줄은 LENGTH가 2,
두 번째 줄은 LENGTH가 3이다.
SELECT ( LENGTH(C1)
- LENGTH( REPLACE(C1, CHR(10)) )
+ 1 ) 이를 CC라 한다.
이것을 계산해 보면,
첫 번째 줄은 ( 원래 3 - 변경된 2 + 1 ) = 2
두 번째 줄은 ( 원래 5 - 변경된 3 + 1 ) = 3
최종적으로 SUM(CC)는 2+3=5
16번 - 정답 3번.
(동영상 풀이 강의 12:06)
1/24/(60/10)을
1일 / 24시간 / 6분으로 읽을 수 있다.
먼저 1일 / 24시간 = 1시간 = 60분이다.
그다음 60분 / 6분 = 10분이다.
그래서 문제에서 2023년 1월 10일 10시에 + 10분을 하면, 2023년 1월 10일 10시 10분이 된다.
18번 - 정답 4번.
(동영상 풀이 강의 23:22)
1번, 2번, 3번의 SUM() 안의 식은 동일한 결과를 보여준다.
CASE WHEN POSITION '변수' 나
CASE POSITION WHEN='변수'는 동일한 식이다.
1번, 2번, 3번의 SUM() 식을 보면
각 POSITION에
만족하면 THEN 1
만족 안 하면 END 이므로
(참고로 CASE에서 ELSE 값에 대해 안 써주면, 만족 안 하는 것들은 NULL이 된다.)
SUM()을 통해 '각 POSITION 만족한 값의 합'이 들어간다.
(만약 만족한 것이 하나도 없으면 SUM()은 NULL이 출력된다.)
NVL(입력값 1, 입력값 2)
ISNULL(입력값 1, 입력값 2)
입력값 1의 결과가 NULL이면 입력값 2가 출력된다.
입력값 1의 결과가 NULL이 아닌 값이라면, 해당 값이 출력된다.
이후 ISNULL()이나 NVL()식을 보면
ISNULL(각 POSITION 합, 0)
NVL(각 POSITION 합, 0) 이므로
'각 POSITION 합' 값이 NULL이 아니면
'각 POSITION 합'이 출력된다.
(만약 만족한 것이 하나도 없어 NULL이면, 두 번째 인수인 0이 출력된다.)
그런데 4번은
SUM()식에서
각 POSITION에
만족하면 THEN 1
만족 안 해도 ELSE 1
END 이므로
항상 값이 1만 나오기에
SUM()을 하면 1이 전체 개수만큼 더해져 나온다.
각 POSITION에 따른 값이 아니다.
28번 - 정답 2번.
각 그룹별로
HAVING COUNT(*) = 2 인 그룹은
100과 999이다.
그다음 ORDER BY를 통해
999를 정렬할 때는 0으로 취급한다.
나머지 ID들은 그대로 자신의 ID를 사용한다.
그럼
999 (=정렬할 때는 0으로 취급)
100
이런 형태가 나오게 된다.
38번 - 정답 4번.
(동영상 풀이 강의 32:14)
45번 - 정답 4번.
49번 - 정답 1번.
(동영상 풀이 강의 29:02)
2과목 - 2장
55번 - 정답 2번.
(개정 전에는 80번이었으므로, 인터넷에 sqld 80번 검색하면 다양한 해설을 얻을 수 있다.)
문제에서는 INTERSECT를 사용하였기에, '서비스' 중에서 '서비스에 이용된' 것들의 정보가 나온다.
1번
서비스와 서비스 이용은 1:N 관계이므로, 모든 서비스 이용 내역들이 다 나온다.
예를 들어 1번 서비스를 ID가 123인 사람이 이용하고, 1번 서비스를 ID가 456인 사람도 이용했다면,
SQL 결과로 둘 다 나오게 된다. 하지만 우리는 이용된 서비스가 무엇인지, 즉 1번 서비스가 한 번만 나오는 것을 원한다.
2번
MINUS를 통해 '서비스가 이용 안 한 것'만 도출되는 것이 Y이다.
이후 NOT EXIST 'Y'이므로, '서비스가 이용한 것'만 도출된다.
3번
서비스 A를 기준으로 LEFT OUTER JOIN 되었기에, 이용된 적이 없는 서비스들만 도출된다.
(WHERE 절을 통해 B.서비스ID IS NULL 이기에, SELECT에서 B.서비스ID를 추출하면 NULL로 나온다.)
4번
서비스 이용에서 MINUS 서비스를 함으로써, 아무것도 남지 않게 된다. (서비스에서 가져온 값을 FK로 쓰고 있는데, 서비스 내용을 다 빼면 없는 셈이다.)
그럼 WHERE 서비스ID IN (아무것도 없는 공집합)으로 결과 데이터는 아무것도 나오지 않게 된다.
57번 - 정답 2번.
1번. grade별, (job, grade)별
3번.
먼저, 1. DNAME 고정 그룹화
결과는 DNAME을 기준으로 각 부서를 분리해서 출력.
이후에, 2. ROLLUP(JOB) 계층적 집계
각 부서별 JOB 그룹에 대해 합계를 계산한 후, 해당 부서의 전체 합계를 추가.
4번. 3번과 마찬가지로 grade별 나오고, (grade, job)별로 나온다.
그런데, 2번은 grade별, (gradeb job)별, '전체 합'이 나온다.
78번 - 정답 2번.
1번
GROUPING(column) WHEN 0: 해당 컬럼이 일반적인 데이터일 때 0을 반환합니다. 즉, 소계나 총계가 아닌, 개별 행의 데이터를 의미합니다.
2번
GROUPING(column) WHEN 1: 해당 컬럼이 소계나 총계와 같은 그룹화된 결과일 때 1을 반환합니다. 이때는 해당 컬럼이 NULL로 표시되는 그룹화된 데이터로 간주됩니다.
따라서 '지역 전체' 컬럼은 총계를 나타내는 WHEN 1 이어야 한다. 정답은 2번이다.
3번
CUBE는 가능한 모든 결합을 나타내므로 '지역명, 이용월, (지역명,이용월), 전체'에 대한 결과가 출력된다. 이용월 별 정보는 필요없다.
4번
GROUPING SETS은 '지역ID, 이용월'에 대한 결과가 출력된다. 우리가 원하는 전체에 대한 결과가 없다.
88번 - 정답 3번
각 그룹별로 현재 그룹의 평균 가격을 기준으로 ±10,000 범위 내에 있는 그룹들을 찾고, 그 개수를 세어줍니다.
예를 들어 아래와 같이, GROUP BY 상품분류코드 별로, AVG(상품 가격)이 있다고 해보면.
이 윈도우 함수는 각 그룹(상품분류코드)을 중심으로 -10,000부터 +10,000 사이의 평균 상품 가격을 가진 그룹들이 몇 개인지를 세어줍니다.
1. 상품분류코드 A (평균 가격 50,000)
50,000 ± 10,000 범위 안에 포함되는 평균 가격: 40,000 ~ 60,000
해당 범위 안에 있는 그룹: A (50,000), B (52,000), C (55,000), D (60,000)
따라서, COUNT(*)는 4
2. 상품분류코드 B (평균 가격 52,000)
52,000 ± 10,000 범위 안에 포함되는 평균 가격: 42,000 ~ 62,000
해당 범위 안에 있는 그룹: A (50,000), B (52,000), C (55,000), D (60,000)
따라서, COUNT(*)는 4
3. 상품분류코드 C (평균 가격 55,000)
55,000 ± 10,000 범위 안에 포함되는 평균 가격: 45,000 ~ 65,000
해당 범위 안에 있는 그룹: A (50,000), B (52,000), C (55,000), D (60,000)
따라서, COUNT(*)는 4
4. 상품분류코드 D (평균 가격 60,000)
60,000 ± 10,000 범위 안에 포함되는 평균 가격: 50,000 ~ 70,000
해당 범위 안에 있는 그룹: A (50,000), B (52,000), C (55,000), D (60,000), E (70,000)
따라서, COUNT(*)는 5
5. 상품분류코드 E (평균 가격 70,000)
70,000 ± 10,000 범위 안에 포함되는 평균 가격: 60,000 ~ 80,000
해당 범위 안에 있는 그룹: D (60,000), E (70,000), F (75,000)
따라서, COUNT(*)는 3
6. 상품분류코드 F (평균 가격 75,000)
75,000 ± 10,000 범위 안에 포함되는 평균 가격: 65,000 ~ 85,000
해당 범위 안에 있는 그룹: E (70,000), F (75,000)
따라서, COUNT(*)는 2
< 윈도우 함수에서 ORDER BY의 역할 >
1. 누적 합계 등 누적 계산이 필요한 경우
만약 SUM, AVG, MIN, MAX, COUNT 등의 집계 함수를 누적해서 계산하려고 한다면 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW와 같은 범위를 함께 사용합니다. ROW처럼 범위를 누적 형태로 지정해야 합니다.
예를 들어 SUM(값) OVER (ORDER BY 날짜 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)는 첫 행부터 현재 행까지의 누적 합계를 계산합니다. 이런 경우 ORDER BY가 누적 계산을 위한 기준이 됩니다.
2. 범위 기반 윈도우 함수 사용 시 (RANGE)
반면, RANGE 절을 사용할 때는 특정 값의 범위를 지정하게 됩니다. 예제에서 사용한 RANGE BETWEEN 10000 PRECEDING AND 10000 FOLLOWING처럼 범위 기반의 윈도우 함수를 설정하면, 누적 합이 아닌 특정 값 범위 내에서 집계된 값을 계산합니다.
이때 ORDER BY AVG(상품가격)처럼 집계 함수 자체가 ORDER BY에 직접 들어가는 경우는, 평균 값을 기준으로 오름차순 정렬하여 윈도우 범위를 설정하는 역할만 하게 됩니다. 누적 값을 의미하지 않습니다.
ORDER BY가 있어야 기준 값(평균 상품 가격)이 생기고, 이 기준을 중심으로 ±10,000 범위를 계산할 수 있습니다.
ORDER BY 없이 RANGE만 쓰면 어떤 값을 기준으로 앞뒤 10,000 범위를 찾아야 할지 알 수 없어서 SQL이 범위를 정의할 수 없습니다.
97번 - 정답 3번.
LAG() 함수
1. 첫 번째 인자: 이전 행에서 값을 가져올 컬럼명.
2. 두 번째 인자: 몇 번째 앞의 행 값을 가져올지 지정 (기본값 1).
3. 세 번째 인자: 이전 행이 없을 때 반환할 기본값 (기본값은 NULL).
2과목 - 3장
106번 - 정답 2번.
**문제 정정하기
[T2] 테이블에
(7369, SMITH, CLERK, 800) 추가해야 한다!
'MERGE INTO 테이블1 USING 테이블2 ON 조건'
식에서는 USING 되는 테이블 2를 참조하여, 테이블 1을 수정한다.
**문제 정정하고 나면,
T1의 첫 행(7369)가 T2에 존재하므로, WHEN MATCHED THEN, 그리고 WHERE 절의 (A.JOB이 CLERK이 아니고) A.SAL이 800으로 2000보다 작기에, DELETE 된다.
T1의 두 번째 행(7566)가 T2에 존재하므로, WHEN MATCHED THEN, 그리고 WHERE 절의 A.JOB이 CLERK이기에, UPDATE 된다. (UPDATE 된 것은 A.SAL이 2975-500=2475이다.)
https://m.youtube.com/watch?v=5fTVqNUE5sg&list=PLbflMVhwy2jOoDIm7kVgt1_sYDJm_oBaD&index=11
동영상 풀이 강의
1과목
2:37 39번
2과목 - 1장
12:06 16번
23:22 18번
32:14 38번
29:02 49번
'진로(취업)' 카테고리의 다른 글
| 자격증 시험 일정 (24년도 하반기) (0) | 2024.09.01 |
|---|---|
| 면접 대비 질문 모음 (3) | 2024.09.01 |