【SQLD】 2과목 SQL 기본 및 활용 연습문제 정리(4)

---------------------------------------------------------------------------------------------------------------------------------------------

- 다음의 SQL문과 그 결과를 보고 효과적인 인덱스를 생성하시오

SELECT SAL FROM EMP ORDER BY SAL DESC;

SAL

500

300

300

280

100

1. CREATE INDEX IND_SAL ON EMP(SAL ASC);

2. CREAT INDEX IND_SAL ON EMP(SAL);

3. CREATE INDEX IND_SAL ON EMP(SAL DESC);

4. CREATE INDEX IND_SAL ON EMP;

 

답 : 3번

SAL 값이 큰 순(내림차순)으로 조회되었으므로 내림차순 인덱스를 생성해야함

 

---------------------------------------------------------------------------------------------------------------------------------------------

 

- EMP 테이블에서 1부터 5개의 행만 조회하는 SQL문을 작성할 것, 이때 ROWNUM을 사용할 것

 

답 : 

SELECT *

FROM (SELECT ROW NUM AS LIST, EMPNO FROM EMP)

WHERE LIST <=5;

매력적 오답 :

SELECT * 

FROM(SELECT ROWNUM AS LIST, EMPNO FROM EMP WHERE ROWNUM<=5);

 

---------------------------------------------------------------------------------------------------------------------------------------------

 

- 테이블 R과 S가 다음과 같을때 SQL의 실행 결과로 옳은 것은?

[R]

EID	ENAME	PHONE
12	A	7676
23	B	5656
43	C	4545
55	D	3434

[S]

DID	EE
100	A
200	B
030	C
400	D
500	E

SELECT COUNT(*) FROM R, S ;

답 : 20

SQL문에서 COUNT() 함수는 행 수를 확인하는 집계함수, 그런데 R과 S테이블에 조인 조건 없어서 카테시안 곱이 발생함

* 카테시안 곱 : - From절에 2개 이상의 Table이 있을때 두 Table 사이에 유효 join 조건을 적지 않았을때 해당 테이블에 대한 모든 데이터를 전부 결합하여 Table에 존재하는 행 갯수를 곱한 만큼의 결과값이 반환되는 것

---------------------------------------------------------------------------------------------------------------------------------------------

 

- 다음의 SQL문에 대한 설명으로 올바르지 않은 것은?

SELECT ENAME, SAL, NTILE(4) OVER (ORDER BY SAL DESC) AS DATA

FROM EMP;

 

1. DATA 필드가 가질 수 있는 값의 범위는 0~3임

2. SAL이 큰 순으로 조회됨

3. SAL의 값에 따라서 데이터를 4등분으로 분류해서 DATA필드로 조회됨

4. SAL의 마지막 행은 급여가 가장 작은 사람임

 

답 : 1번

NTILE(ARGUMENT) 함수는 데이터를 ARGUMENT 값으로 N등분 하는 함수 1~4까지 4등분 함

---------------------------------------------------------------------------------------------------------------------------------------------

 

- 다음의 PREV_SAL은 어떤 윈도우 함수를 사용해야 하는가?

ENAME	HREDATE	SAL	PREV_SAL
A	20-81	160
B	22-81	1250	1600
C	08-81	1500	1250
D	28-81	1250	1500

 

1. LEAD

2. LAG

3. NTILE

4. LAST_VALUE

 

답 : 2번

LAG() 함수 : 이전 행의 몇 번째 행 값을 가지고 옴

LEAD() 함수 : 다음 행의 값을 리턴

NTILE() 함수 : 데이터를 N등분 함수

LAST_VALUE : 마지막 레코드 값을 조회하는 함수

---------------------------------------------------------------------------------------------------------------------------------------------

 

★★- 부서와 사원 테이블을 생성하는 SQL문장을 수행 후 튜플 삽입으로 두 테이블의 상태가 다음과 같을 때, 테이블 연산 수행에 대한 설명으로 올바르지 않은 것?

 

CRATE TABLE 부서(

부서번호 INT NOT NULL,

부서명 VARCHAR(20)

PRIMARY KEY(부서번호));

 

CREATE TABLE 사원(

사번 INT NOT NULL,

이름 VARCHAR(20)

부서번호 INT,

PRIMARY KEY(사번),

FOREIGN KEY(부서번호)

REFERENCES 부서(부서번호));

[부서]

부서번호	부서명
1	자채부
2	영업부

[사원]

사번	이름	부서번호
11	홍길동	1
12	이순신	2

 

1. 부서 테이블에서 (2,'영업부') 튜플을 삭제한다면 참조 무결성 제약조건을 위배

2. 사원 테이블에 (13,'강감찬','A1') 튜플을 삽입한다면 도메인 무결성 제약조건 위배

3. 사원 테이블에 (14,'김유신',0) 튜플을 삽입한다면 참조 무결성 제약조건을 위배

4. 부서 테이블에 (1,'연구부') 튜플을 삽입한다면 참조 무결성 제약조건을 위배

 

답 : 4번

부서 테이블은 마스터 테이블이므로 사원 테이블에 없는 부서번호 등을 자유롭게 입력 가능하여 이는 참조 무결성 위배가 아님

하지만 부서테이블에 부서번호 칼럼은 PRIMARY KEY이므로 중복값이 입력되면 안되고 이는 무결성 제약 조건(PK중복)에 위배됨

3번: A1이 속성 값인 INT가 아니므로 도메일 무결성 제약조건에 위배됨

* 도메인 무결성 제약조건  :  도메인 무결성 제약 조건이란 각 속성의 값은 반드시 도메인에 지정된 값만을 가져야 한다는 조건

1번 : 튜플 삭제시 [사원] 테이블의 참조 무결성 제약조건이 위배됨

3번 : 튜플 삽입시 부서번호 0(FOREIGN KEY) 값이 부모 릴레이션의 기본 키와 다르므로 참조 무결성 제약조건 위배됨

* 참조 무결성 제약조건 : 자식 릴레이션의 외래 키의 값은 참조된 부모 릴레이션의 기본 키 값과 같아야 하며, 자식 릴레이션의 값이 변경될 때 부모 릴레이션의 제약을 받는다는 조건

---------------------------------------------------------------------------------------------------------------------------------------------

 

- 도서(도서번호, 도서제목, 출판사명, 발행연도) 테이블에서, 2000년 이후에 10권 이상의 책을 발행한 출판사의 이름을 중복 없이 출력하는 SQL문으로 옳은 것은? (단, 출판사명이 동일한 출판사는 존재하지 않고 도서번호는 도서 테이블의 기본키임)

 

답 : 

SELECT 출판사명

FROM 도서

WHERE 출판연도>=2000

GROUP BY 출판사명

HAVING COUNT(도서번호) >=10;

 

1) Start with 절 

Start with num2 IS NULL <= 2건 (num1= 1,2)

2) connect by 절

connect by num1=prior num2

(Prior 컬럼은 Start with 에서 선택한 row 값 . )

=> connect by num1 = NULL 인 건은 없음

결국 Start with 절 조건에 일치한 2건만 출력

---------------------------------------------------------------------------------------------------------------------------------------------

 

- 다음의 NOT EXISTS 구문을 동일한 결과가 출력되게  SQL문을 변경하시오

SELECT ... FROM 급여이력 S

WHERE NOT EXISTS

(SELECT 'X' FROM 사원 P

WHERE P.사원번호 = S.사원번호)

 

[SQL문]

SELECT ... FROM 급여이력 S

LEFT OUTER JOIN 사원 P

ON ( S.사원번호 = P.사원번호) WHERE (   );

 

답 : P.사원번호 IS NULL

NOT EXISTS는 OUTER JOIN으로 변경할 경우 IS NULL로 검사하여 구현함

* 급여 이력에서 사원의 차집합을 구한는 것임

---------------------------------------------------------------------------------------------------------------------------------------------

 

★★- 서브쿼리에 대한 설명으로 올바르지 않은 것은?

 

1. 서브쿼리는 괄호를 사용해서 SELECT문을 감싸서 사용하는 것

2. 서브쿼리는 비교 연산자와 함께 사용 가능함

3. 메인쿼리는 스칼라 서브쿼리의 카럼을 쓸 수 없음

4. 서브쿼리는 SELECT, FROM, WHERE 등에서 사용 가능함

 

답 : 3번

서브쿼리의 한 종류인 인라인뷰(FROM절에 사용됨)의 칼럼을 메인 쿼리에서 사용 가능함

서브쿼리는 메인쿼리의 칼럼을 쓸 수 있지만, 메인쿼리는 서브쿼리의 칼럼을 쓸 수 없으므로 이를 표시하려면 스칼라 서브쿼리를 이용해야함

---------------------------------------------------------------------------------------------------------------------------------------------

 

★★- "TEST" 테이블에 있는 NUM2 칼럼의 총 행은 10개이고, 2개의 NULL 값이 존재하는데 이때 SQL문의 결괏값은?

ㄱ : SELECT COUNT(*) FROM TEST;

ㄴ : SELECT COUNT(NUM2) FROM TEST;

 

답 : ㄱ-10, ㄴ-8

COUNT(*)은 행의 총 갯수를 의미하고 COUNT(칼럼명)의 경우 NULL 값은 제외하고 나옴

---------------------------------------------------------------------------------------------------------------------------------------------

 

- 다음의 SQL문에 대한 설명으로 올바르지 않은 것은?

ㄱ : SELECT SUM(SAL) FROM EMP GROUP BY DEPTNO;

ㄴ : SELECT SUM(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO);

 

1. ㄱ은 부서별 합계를 출력

2. ㄱ과 ㄴ은 결과 행 수가 동일

3. ㄴ은 부서별 합계와 전체합계가 출력됨

4. ㄱ의 SQL문은 전체합계는 출력되지 않음

 

답 : 2번

ROLLUP은 전체합계가 추가적으로 출력됨

# 집계함수와 GROUP BY 형식(각 컬럼별 로 집계함)

SELECT column, 집계함수(column) 

FROM TABLE 

(WHERE column = data)

GROUP BY column; 

---------------------------------------------------------------------------------------------------------------------------------------------

 

- 다음의 SQL문에 대한 설명으로 올바른 것은?

SELECT 'A',1 FROM DUAL

UNION ALL

SELECT 1,'A' FROM DUAL;

 

1. 위의 실행 결과는 A,1,1,A임

2. UNION ALL을 사용해 합집합을 만들고 중복을 제거함

3. 위의 SQL문은 실행되지 않음

4. 실행 결과로 아무것도 출력되지 않음

 

답 : 3번

데이터 타입이 각각 문자 숫자, 숫자 문자로 다르므로 SQL문이 실행되지 않음

2번은 UNION에 관한 설명

---------------------------------------------------------------------------------------------------------------------------------------------

 

- 학생(STUDENT) 테이블에 영문학과 학생 50, 법학과 100, 수학과 50명의 정보가 있음, 다음 SQL문의 실행 결과 튜플 수는 각각 얼마인가? (단, DEPT필드는 학과면, NAME필드는 이름을 의미함)

ㄱ : SELECT DEPT FROM STUDENT;

ㄴ : SELECT DISTINCT DEPT FROM STUDENT;

ㄷ : SELECT NAME FROM STUDENT WHERE DEPT='영문학과'

 

답 : ㄱ-200, ㄴ-3, ㄷ-50

ㄱ은 아무 조건 없이 조회 하였으므로 총 학생수인 200, ㄴ은 DSITINCT로 중복을 제거했으므로 3, ㄷ은 영문학과 학생인 50이 출력됨

---------------------------------------------------------------------------------------------------------------------------------------------

 

- 다음의 SQL문은 파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대해 누적백분율은 구하는 SQL문임 빈칸에 올바른 윈도우 함수는?

SELECT DEPTNO, ENAME, SAL, (    ) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS PCT

FROM EMP;

 

답 : CUME_DIST()

CUME_DIST 함수는 주어진 그룹에 대한 상대적 누적분포도 값을 반환하는 것으로 분포도 값은 0초과 1이하 사이의 값임

* PERCENT_RANK : 인수로 지정한 값의 그룹 내의 위치를 백분율로 나타냄

---------------------------------------------------------------------------------------------------------------------------------------------

 

- 다음 설명 중 올바르지 않은 것은?

1. SQL SERVER는 NULL 값을 인덱스 맨 뒤에 저장함

2. ORACLE에서 인덱스 구성 칼럼 중 하나라도 NULL이 아닌 레코드는 인덱스에 저장함

3. SQL SERVER는 인덱스 구성 칼럼이 모두 NULL인 레코드도 인덱스에 저장함

4. ORACLE에서 인덱스 구성 칼럼이 모두 NULL인 레코드는 인덱스에 저장하지 않음

 

답 : 1번

SQL SERVER는 NULL값을 맨 앞에, ORACLE은 NULL 값은 맨 뒤에 저장함

---------------------------------------------------------------------------------------------------------------------------------------------