(SQLD)SQL자격검정 실전문제(SQL기본 및 활용)

SQL기본 및 활용

2-1. SQL기본

오답

7번

• SQL server에서는 여러개의 컬럼을 동시에 수정하는 구문은 지원하지 않음
• 분류명 수정시 NOT NULL구문 지정 필수, 지정하지 않을시 자동 NULL

40번

내장함수

• 1:M 조인이라 하더라도 M쪽에서 출력된 행이 하나씩 단일행 함수의 입력값으로 사용
• 다중행 함수도 단일행 함수와 동일하게 단일 값만을 반환

  헷갈리는 문제

  16번 18번

  테이블에서 컬럼 삭제시
  ALTER TABLE 테이블명 DROP COLUMN 컬럼명
  테이블 이름 변경시
  RENAME 기존테이블명 TO 새테이블명

29번 37번

ORACLE과 SQL Server에서의 Commit, Rollback,Insert,Select
ORACLE

• DDL문장 수행 후 자동 COMMIT수행
• 공백("")으로 데이터 입력시 IS NULL로 조회
  SQL Server
• DDL문장 수행 후 자동 COMMIT수행하지 않음
• CREATE TABLE도 TRANSACTION의 범주에 포함
• 공백("")으로 데이터 입력시 ""로 조회

19번

참조관계를 정의하기 위해 외래시 선언시 부모(delete)와 자식(insert)제약조건 종류

Delete(/Modify) Action : 부모테이블에서 삭제시
CASCADE	부모 행 삭제시 자식 행 같이 삭제
SET NULL	부모 행 삭제시 자식 행 NULL
SET DEFAULT	부모 행 삭제시 자식 행 Default값
RESTRICT	자식 테이블에 PK값 없는 경우만 부모 삭제 허용
NO ACTION	참조무결성을 위반하는 삭제/수정 액션취하지 않음
Insert Action: 자식테이블에서 입력시
AUTOMATIC	부모테이블에 PK가 없는 경우 부모 PK생성 후 자식 입력
SET NULL	부모테이블에 PK가 없는 경우 자식외부키를 NULL값처리
SET DEFAULT	부모테이블에 PK가 없는 경우 자식 외부키를 지정된 기본값으로 입력
DEPENDENT	부모테이블에 PK존재할 때만 자식 입력허용
NO ACTION	참조무결성을 위반하는 삭제/수정 액션취하지 않음

26번

DROP : 테이블 정의 자체를 삭제
TRUNCATE : UNDO를 위한 데이터를 생성하지 않기 때문에 동일 데이터량 삭제시 DELETE보다 빠르다

53번

SELECT SUM(num) FROM test HAVING AVG(num)>1;

위와 같이 group by 절이 없어도 having을 사용하면 오류가 나지 않음

SELECT 메뉴ID,사용유형코드,AVG(COUNT(*)) FROM test GROUP BY 메뉴id,사용유형코드;

AVG(COUNT(*))경우는 최종 결과 값이 1건 밖에 될수 없음

56번

SELECT 지역, SUM(매출금액)
FROM 지역별매출
GROUP BY 지역
ORDER BY 년 DESC;

위와 같이 GROUP BY에 관련되지 않은 컬럼은 ORDER BY절에서 사용할 수 없음

63번

JOIN에 대한 설명

• 일반적으로 JOIN은 PK와 FK값의 연관성에 의해 성립

• 대부분 NON EQUE JOIN을 수행할 수 있지만 때로는 설계상의 이유로 수행이 불가능한 경우도 있음

  65번

  순수 관계 연산자

• SELECT : WHERE 절로 구현

• PROJECT : SELECT 절로 구현

• JOIN

• DIVIDE

  82번

  UNION ALL사용하는 경우 칼럼 ALIAS는 첫번째 SQL기준

90번

계층형 질의문

• 오라클의 계층형 질의문에서 WHERE절은 모든 전개를 진행한 이후 필터 조건으로서 조건을 만족하는 데이터만을 추출하는데 사용
• 오라클의 계층형 질의문에서 PRIOR키워드는 CONNECT BY 절, SELECT절, WHERE절에서 사용

92번

SELF JOIN은 하나의 테이블에서 두 개의 칼럼이 연관관계를 가지고 있는 경우에 사용

95번

서브쿼리

• 연관 서브쿼리는 서브쿼리가 메인쿼리 컬럼을 포함하고 있는 형태의 서브쿼리
• 다중컬럼 서브쿼리는 서브쿼리의 결과로 여러 개의 컬럼이 반환되어 메인쿼리의 조건과 동시에 비교되는 것을 의미하며 ORACLE에서만 지원

97번

서브쿼리문제

• 문제 SQL문 해석 후 선지 읽으면서 오류코드 먼저 소거

• 단일행 연산자의 경우 where절에서 비교하려는 대상이 서브쿼리의 결과 값과 매칭이 되는지 확인

• 다중행 서브쿼리 비교 연산자는 단일행 서브쿼리의 비교 연산자로도 사용가능

• 비연관 서브쿼리는 주로 메인쿼리에 값을 제공하기 위한 목적

  103번

  뷰

• 뷰의 장점 중 독립성은 테이블 구조가 변경되어도 뷰를 사용하는 응용프로그램은 변경하지 않아도 된다

• 실제 데이터를 저장하고 있는 뷰를 생성하는 기능을 지원하는 DBMS도 있다

  106번

• GROUPING(column) 함수는 ROLLUP 또는 CUBE와 함께 사용될 때, 특정 컬럼의 값을 기준으로 그룹화한 결과에서 해당 컬럼이 NULL인지 아닌지를 판단할 때 사용

• GROUPING(column) 함수는 column이 NULL이면 1을 반환하고, NULL이 아니면 0을 반환합니다.

111번

grouping sets에서 괄호의 개수 파악 중요

2-3. SQL 최적화 기본 원리

오답

132번

SQL 처리 흐름도

• 인덱스 스캔, 테이블전체 스캔 등과 같은 액세스 기법이 표현
• SQL의 내부적인 처리 절차를 시각적으로 표현
• 실행 시간은 알수 없다

140번

인덱스를 구성하는 컬럼 이외의 데이터가 update될 때는 인덱스로 인한 부하 발생하지 않음

헷갈리는 문제

128번

비용기반 옵티마이저
테이블 및 인덱스 등의 통계정보를 활용하여 SQL문을 실행하는데 소요될 처리시간 및 CPU, I/O 자원량 등을 계산하여 가장 효율적일 것으로 예상되는 실행계획을 선택하는 옵티마이저

129번 131번

실행계획을 통해 알수 있는 정보(예상정보)

• 실행계획 즉, 실행방법이 달라진다고 결과가 달라지지 않는다

1. 액세스 기법
2. 질의 처리 예상 비용
3. 조인 순서

133번

규칙기반 옵티마이저

• 제일 낮은 우선순위는 전체 테이블 스캔이고, 제일 높은 우선순위는 ROWID를 활용하여 테이블 액세스
• 인덱스 범위 스캔은 결과 건수만큼 반환하지만, 결과가 없으면 한 건도 반환하지 않을 수 있음

  134번

  기본 인덱스(Primary key) : UNIQUE & NOT NULL
  보조 인덱스 : UNIQUE 인덱스가 아니라면 중복 데이터 입력가능

135번

• 테이블 전체 데이터를 읽는 경우는 인덱스를 사용하지 않는 FTS사용
• 인덱스가 존재하는 상황이라면 입력 처리가 모두 끝나고 인덱스를 다시 생성하는 것이 좋음

136번

인덱스 종류

1. B-TREE 인덱스 : 브랜치블록과 리프블록으로 구성, 브랜치블록은 분기를 목적, 리프블록은 인덱스를 구성하는 컬럼의 값으로 정렬
2. CLUSTERED인덱스 : 인덱스의 리프페이지가 데이터 페이지, 리프페이지의 모든 데이터는 인덱스 키 컬럼 순으로 물리적으로 정렬
3. BITMAP인덱스 : 시스템에서 사용될 질의를 시스템 구현시에 모두 알 수 없는 경우인 DW 및 AD-HOC 질의 환경을 위해서 설계, 하나의 인덱스키 엔트리가 많은 행에 대한 포인터를 저장하는 구조

137번

인덱스, 옵티마이저

• 비용기반 옵티마이저는 SQL을 수행하는데 있어 소요되는 비용을 계산하여 실행계획을 생성하므로 인덱스가 존재하더라도 전체 테이블 스캔이 유리하다고 판단할 수도 있다
• 규칙기반 옵티마이저의 규칙에 따라 적절한 인덱스가 존재하면 전체 테이블 스캔보다는 항상 인덱스를 사용하려고 한다
• 인덱스 범위 스캔은 결과 건수만큼 반환하지만, 결과가 없으면 한 건도 반환하지 않을 수 있음

138번

연산자 우선순위를 통해 인덱스의 순서를 결정해서 성능을 높일 수 있다

139번

인덱스를 스캔하여 테이블로 데이터를 찾아가는 방식이 랜덤 액세스인데 부하가 크기 때문에 많은 양의 데이터를 읽을 때는 테이블 전체 스캔이 유리할 수도 있다

141번

NL JOIN : OLTP의 목록처리 업무
HASH JOIN , SORT MERGE JOIN : DW 등의 데이터 집계업무