<과정평가형 NCS 정보처리산업기사> 과년도 필기 출제 위주 정리

---

6. SQL 활용

---

1. 기본 SQL 작성하기

339p ✍️ 데이터 사전
: 데이터 사전(Data Dictionary)에는 데이터 베이서의 데이터(사용자 데이터)를 제외한 모든 정보(DBMS가 관리 하는 데이터)가 있음

• 사용자 정보(ID, PW 및 권한 등)
• 데이터베이스 객체(테이블, 인덱스, 뷰 등)
• 무결성 제약 상태
  *함수, 프로시저 및 트리거 정보 등

345p ✍️ DDL 대상

DDL 대상	설명	비고
스키마(Schema)	- DBMS의 특지오가 구축 환경에 기반한 데이터 구조 - 하나의 데이터베이스	DBMS마다 차이
도메인(Domain)	- 속성의 데이터 타입과 제약 조건 등을 설정한 정보 - 속성이 가지는 값의 범위	예) 주소를 varchar(120)로 정의
테이블(Table)	- 데이터 저장 공간
뷰(View)	- 1개 이상의 물리 테이블을 통해서 만드는 가상의 논리 테이블
인덱스(Index)	- 빠른 검색을 위한 데이터 구조

345p ✍️ DDL 명령어

구분	DDL 명령어	내용
생성	CREATE	오브젝트 생성
변경	ALTER	오브젝트 변경
삭제	DROP TRUNCATE	오브젝트 삭제 오브젝트 내용 삭제(데이터베이스 내의 로깅 작업 건너뜀)

345p ✍️ 테이블 생성 SQL문

구분	문법
신규생성	CREATE TABLE 테이블명( 칼럼명 데이터 타입 [DEFAULT 값][NOT NULL] {,칼럼명 데이터 타입 [DEFAULT 값][NOT NULL] }* [PRIMARY KEY (칼럼 리스트),] {[FOREIGN KEY (칼럼 리스트) REFERENCES 테이블 명 [(칼럼명)] [ON DELETE 옵션] [ON DELETE 옵션] ], }* [CHECK (조건식) / UNIQUE(칼럼명)]);
다른 테이블 정보를 활용한 테이블 생성	CREATE TABLE 테이블명 AS SELECT문;

346p ✍️ 테이블 변경

구분	문법
열 추가	ALTER TABLE 테이블명 ADD 칼럼명 데이터 타입 [DEFAULT값]
열 데이터 타입 변경	ALTER TABLE 테이블명 MODIFY 칼럼명 데이터 타입 [DEFAULT 값]
열 삭제	ALTER TABLE 테이블명 DROP 칼럼명

346p ✍️ 테이블 삭제 및 이름 변경 방법

구분	문법
테이블 삭제	DROP TABLE 테이블명
테이블 내용 삭제	TRUNCATE TABLE 테이블명 (구조는 가지고 있고 내용만 삭제)
테이블명 변경	RENAME TABLE 이전 테이블명 TO 새로운 테이블명 ALTER TABLE 이전 테이블명 RENAME 새로운 테이블명

348p ✍️ 테이블 생성에 사용되는 제약 조건

제약 조건	설명
PRIMARY KEY(PK)	- 테이블의 기본 키를 정의함 - 기본적으로 NOT NULL, UNIQUE 제약 사항이 설정됨
FOREIGN KEY(FK)	- 테이블에 외래키를 정의함 - 참조 대상을 테이블명(칼럼명) 형식으로 작성해야함 - 참조 무결성이 위배되는 상황 발생 시, 다음 옵션으로 처리 가능 (CASCADE, NO ACTION, SET NULL, SET DEFAULT)
UNIQUE	- 테이블에서 해당하는 열값은 유일해야 함을 의미 - 테이블에서 모든 값이 다르게 적재되어야 하는 열에 설정
NOT NULL	- 테이블에서 해당하는 열의 값은 NULL 불가능 - 필수적으로 입력해야 하는 항목에 설정
CHECK	- 사용자가 직접 정의하는 제약 조건 - 발생 가능한 상황에 따라 여러가지 조건을 설정 가능

348p ✍️ 다중 테이블 검색 방법

다중 테이블 검색 기법	내용
조인	두 개의 테이블을 결합하여 데이터를 추출하는 기법
서브쿼리	SQL문 안에 포함된 SQL문 형태의 사용 기법
집합 연산자	테이블을 집합 개념으로 조작하는 기법

348, 350p ✍️ 조인(JOIN)
: 두 테이블의 공통값을 이용하여 칼럼을 조합하는 수단

조인 유형	내용
내부조인(Inner Join)	두 테이블에 공통으로 있는 칼럼(열)을 활용하는 유형(공통 칼럼 기반)
동등조인(Equi Join) 자연조인(Natural Join) 교차조인(Cross Join)	공통으로 있는 칼럼값이 같은 경우에 레코드 추출 두 테이블에 있는 동일한 칼럼명을 기준으로 모든 칼럼값이 같은 경우에 레코드 추출 조인 조건이 없는 모든 데이터의 조합을 추출
외부조인(Outer Join)	특정한 테이블의 모든 데이터를 기준으로 다른 테이블의 정보와 비교하여 추출(단, 다른 테이블에 동일한 ㄱ밧이 없어도 특정한 테이블은 출력됨)
왼쪽 외부 조인(Left Outer Join)	왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일한 데이터를 추출(단, 오른쪽 테이블에 동일한 값이 없어도 왼쪽 테이블의 레코드는 출력됨)
오른쪽 외부 조인(Right Outer Join)	오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일한 데이터를 추출(단, 왼쪽 테이블에 동일한 값이 없어도 오른쪽 테이블의 레코드는 출력됨)
완전 외부 조인(Full Outer Join)	양쪽의 모든 데이터를 추출(단, 오른쪽과 왼쪽 테이블에 동일한 값이 없어도 오른쪽, 왼쪽 테이블의 레코드는 출력됨)

350p ✍️ 서브쿼리
: 아직 확인되지 않은 기준을 위한 검색하는 용도
가장 밖에 위치한 메인쿼리와 그 안에 포함된 서브쿼리 관계는 주종관계를 이루고 있고, 서브쿼리에 작성된 칼럼명은 메인쿼리의 칼럼명을 가져와 사용할 수 있으나 그 역은 성립하지 않음

352p ✍️ 집합 연산자

집합 연산자	설명
UNION	2개 이상 SQL문의 실행 결과에 대한 중복을 제거한 합집합
UNION ALL	2개 이상 SQL문의 실행 결과에 대한 중복을 제거하지 않은 합집합
INTERSECTION	2개 이상 SQL문의 실행 결과에 대한 중복을 제거한 교집합
EXCEPOT(MINUS)	선생 SQL문의 실행 결과와 후행 SQL문의 실행 결과 사이의 중복을 제거한 차집합(일부 DMS는 MINUS로 사용)

353p ✍️ DCL 명령어

유형	명령어	용도
DCL	GRANT REVOKE	데이터베이스 사용자 권한 부여 데이터베이스 사용자 권한 회수
TCL	COMMIT ROLLBACK CHECKPOINT	트랜잭션 확정 트랜잭션 취소 복귀지점 설정

354p ✍️ TCL 명령어

명령어	내용	비고
COMMIT	작업거래 내용 확정
ROLLBACK	작업거래 내용 취소
CHECKPOINT	작업거래의 저장 시점 설정	ROLLBACK 위치 지정

363p ✍️ 검색 SQL

SELECT
	고객.고객번호, 고객.이름, 주문.주문번호, 주문.주문일, 주문.주문가격
FROM
	고객, 주문
WHERE
	고객.고객번호 = 주문.고객번호 AND 이름 = '홍길동';

또는

SELECT
	고객.고객번호, 고객.이름, 주문.주문번호, 주문.주문일, 주문.주문가격
FROM
	고객
INNER JOIN
	주문 ON 고객.고객번호 = 주문.고객번호
WHERE
	이름 = '홍길동';

367p ✍️ 트랜잭션 특징
(1) 원자성 - 트랜잭션 내에서 모두 실행되거나 모두 실행되지 않음 (All or Nothing)
(2) 일관성 - 트랜잭션의 실행 전/후 결과가 동일해야함 (무결성)
(3) 고립성 - 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받지 않음 (독립성)
(4) 지속성 - 트랜잭션의 결과는 지속적으로 저장되어 보존됨 (장애 대응성)

---

2. 고급 SQL 작성하기

378p ✍️ 인덱스
: 저장된 데이터를 빠르게 검색할 수 있는 수단이자 테이블에 대한 조회 속도를 높여주는 자료 구조

380p ✍️ 뷰
: 기본 테이블로부터 유도된 가상 테이블

382p ✍️ 뷰 생성방법

상황	뷰 생성 쿼리문
테이블 A 그대로	CREATE VIEW 뷰A SELECT * FROM 테이블 A
테이블 A 일부 칼럼	CREATE VIEW 뷰X AS SELECT 칼럼1, 칼럼2, 칼럼3 FROM 테이블 A
테이블 A와 테이블 B 조인 결과	CREATE VIEW 뷰Y AS SELECT * FROM 테이블 A a, 테이블 B b WHERE a.칼럼1 = b.COL1;

393p ✍️ 뷰 업데이트가 불가능한 상황

UPDATE가 불가능한 상황	내용
뷰 칼럼이 산술식, 집계 함수, 상수로부터 유도된 경우	sum, count, avg 등 포함된 뷰
뷰 정의에서 Group by 절이 포함된 경우	물리적으로 존재하는 레코드를 특정할 수 없음
다수 테이블로 뷰가 정의된 경우	대부분 불가능, 뷰 생성 조건이 엄격하다면 UPDATE가 가능할 수 있음

394p ✍️ 뷰 변경이 가능.불가능한 경우

경우	변경 여부
뷰가 하나의 테이블에서 정의된 경우	가능
뷰 생성에 사용된 테이블의 PK를 포함하는 경우	가능
뷰 정의에서 집계 함수로 정의된 칼럼이 있는 경우	불가능
뷰 정의에서 DISTINCT가 포함된 경우	불가능
뷰 정의에서 GROUP BY 또는 HAVING이 포함된 경우	불가능
뷰 정의에서 서브쿼리가 포함된 경우	불가능
뷰 정의에 상수, 문자열 등이 포함된 경우	불가능

395p ✍️ <수행 tip>
* 인덱스는 사용자가 직접 사용하지 않으므로 간접적으로 인덱스의 효용을 느낄 수밖에 없다.
본 수행 내용 과정을 통해 생성한 인덱스를 통해 검색 속도가 빨라졌는지는 상황에 따라 다르다.
그 이유는 축적된 데이터와 이를 사용하는 검색 조건에 인덱스는 의존적이기 때문이다.
이를 위해  '인덱스 설계'에 대한 필요와 이해가 가장 중요하며 또한 인덱스를 둘러싼 관려 주제들 역시 중요하다.
* 스무고개 놀이를 통해 어떤 숫자를 찾아가는 과정을 생각해 보자.
가장 효과적인 방법은 중간값을 선택하는 것이다.
컴퓨터에서 이와 같은 방식의 해결 기법을 '이진 검색'이라고 한다.
데이터 베이스에서 정보를 찾아가는 방법 역시 이전 검색응 응용한 것이다.
B트리라는 자료 구조를 통해 분기 횟수를 최소화하여 찾아간다.
B트리에는 분기를 할 수 있는 정보가 있으며 그 정보가 바로 인덱스이다.
* 앞서 "뷰 생성" 과정에서 사용한 다음과 같은 쿼리문 (SELECT SUM(주문가격) FROM 주문가격_VIEW WHERE 고객번소 = 'XXXX')의 사용은 SW 보안취약점 관점에서 문제가 많다. 하지만 본 학습에서는 보안보다는 사용의 개념을 학습하기 위해 사용된 쿼리문으로 단순 이해의 용도로 사용되었다.