[sqld] SQL 자격검정 실전문제 핵심정리 (3장 SQL 기본) (6.10 시험 대비용 정리)

참고자료 : SQL 자격검정 실전문제

제3장 SQL 기본

제1절 관계형 데이터베이스 개요

제2절 DDL

제3절 DML

제4절 TCL

제5절 WHERE절

제6절 함수

제7절 Group By, Having 절

제8절 Order By절

제9절 조인(JOIN)

 

DB : 특정한 기업이나 조직 또는 개인이 필요에 의해 데이터를 일정한 형태로 저장해 놓은 것

DBMS : 효율적인 데이터 관리뿐만 아니라 예기치 못한 사건으로 인한 데이터 손상을 피하고, 필요시 데이터를 복구하기 위한 강력한 기능의 SW 

 

SQL 문장들의 종류

1. DML : SELECT, INSERT, UPDATE, DELETE (COMMIT)
2. DDL : CREATE, ALTER, DROP, RENAME (AUTO COMMIT)
3. DCL : GRANT, REVOKE 
4. TCL : COMMIT, ROLLBACK

비절차적 데이터 조작어 

• 사용자가 무슨(what) 데이터를 원하는지만 명세

절차적 데이터 조작어

• 사용자가 무슨(what) 데이터를 어떻게 (how) 접근해야 하는지 명세 (ex) PL/SQL(Oracle), T-SQL(SQL Server)

정규화

• 데이터의 정합성 확보에 데이터의 입력/수정/삭제 시 발생할 수 있는 이상현상을 방지하기 위해 중복 제거

제약조건을 걸어두는 이유 : 데이터의 무결성 유지를 위함

1. PRIMARY KEY : 중복 X, NULL X, 값 하나
2. UNIQUE KEY : 중복 X, NULL 가능
3. NOT NULL
4. CHECK
5. FOREIGN KEY : 값 여러 개 가능

 

제약조건 추가하는 법 (ex) 테이블 생성 시, 기본키 제약 조건 추가)

 

테이블 생성시 기본키 제약조건 지정

1. CONSTRAINT [조건명] PRIMARY KEY ([칼럼명])
2. 해당 칼럼을 생성할 때 바로 기본키 지정

 

테이블의 구조를 변경하여 제약조건 지정

1. ALTER TABLE [테이블명] ADD CONSTRAINT PRIMARY KEY [조건명] ON [칼럼명]

 

테이블 구조 변경

[Oracle] 

ALTER TABLE [테이블명] MODIFY (칼럼명1 데이터유형 [DEFAULT] [NOT NULL]) ..

[SQL Server]

ALTER TABLE [테이블명] ALTER (칼럼명1 데이터유형 [DEFAULT] [NOT NULL]) ..(테이블 구조 변경)

RENAME [기존테이블명] TO [바꿀테이블명] (테이블명 변경)

 

 

NULL값 

• 숫자 0이 아니라, "아직 정의되지 않은 미지의 값"이거나 "현재 데이터를 입력하지 못하는 경우"이다.

 

테이블 삭제 옵션

1. ON DELETE CASCADE : Master 삭제 -> Child 삭제
2. ON DELETE SET NULL : Master 삭제 -> Child NULL
3. ON DELETE SET DEFAULT : Master 삭제 -> Child DEFAULT
4. ON DELETE RESTRICT : Child 테이블에 PK값 없는 경우 -> Master 삭제 허용
5. ON DELETE NO ACTION : 참조무결성을 위반하는 삭제/수정 액션 X

 

테이블 삽입 옵션

1. AUTOMATIC : Master PK X -> Master PK 생성 후 Child 입력
2. SET NULL : Master PK X -> Child 외부 키 NULL값으로 처리
3. SET DEFAULT : Master PK X-> Child 외부 키를 DEFAULT로 처리
4. DEPENDENT : Master PK O -> Child 입력 허용
5. NO ACTION : 참조무결성을 위반하는 삭제/수정 액션 X

데이터 조회 옵션 

1. ALL : Default 옵션이므로 별도 표시 X, 중복된 데이터가 있어도 모두 표시
2. DISTINCT : 중복된 데이터가 있는 경우 1건으로 처리해서 출력

테이블 생성 시 주의사항

1. 테이블명은 객체를 의미할 수 있는 적절한 이름 사용 + 단수형
2. 다른 테이블의 이름과 중복 X
3. 칼럼명 반드시 문자로 시작, 벤더에서 사전에 정의한 예약어(Reserved Word) X 
4. A-Z, a-z, 0-9, _, $, # 문자만 허용

count(*), count(칼럼명) 

• *은 NULL값을 포함한 카운트, 그 외에는 NULL값 미포함

INDEX 생성

• CREATE INDEX [인덱스명] ON [테이블명] (칼럼명)

테이블 삭제 명령어 

DROP	TRUNCATE	DELETE
DDL	DDL(일부 DML)	DML
ROLLBACK X	ROLLBACK X	COMMIT 이전 ROLLBACK O
AUTO COMMIT	AUTO COMMIT	사용자 COMMT
테이블이 사용했던 storage를 모두 release	테이블이 사용했떤 storage중 최초 테이블 생성시 할당된 storage만 남김	데이터를 모두 delete, release X
테이블 정의 자체를 삭제	테이블 생성 초기 상태로 만듦	데이터만 삭제
로그 X	로그 X	로그 O

 

트랜잭션(transaction) 

• DBMS에서 분리될 수 없는 논리적인 작업 단위 

 

커밋(commit) & 롤백(rollback)

1. 커밋 : 데이터에 대한 변경사항을 영구적으로 반영
2. 롤백 : 데이터에 대한 변경사항을 모두 폐기하고 변경 전의 상태로 되돌림, commit 되지 않은 상위의 모든 트랜잭션을 ROLLBACK, savepoint(저장점)을 정의해하면 롤백할 때, savepoint지점까지 일부만 롤백 가능

[Oracle]

SAVEPOINT SVPT1;

...

ROLLBACK TO SVPT1;

 

[SQL Server]

SAVE TRANSACTION SVPT1;

...

ROLLBACK TRANSACTION SVTR1;

 

트랜잭션의 특성

1. 원자성(atomicity) : 트랜잭션에서 정의된 연산들은 모두 성공적으로 실행되던지, 전혀 실행되지 않거나 둘 중 하나(all or nothing)
2. 일관성(consistency) : 트랜잭션이 실행되기 전의 데이터베이스 내용이 잘못되어 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 잘못이 있으면 안 됨
3. 고립성(isolation) : 트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안 됨
4. 지속성(durability) : 트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 데이터베이스의 내용은 영구적으로 저장 

 

트랜잭션의 격리성이 낮을 경우 발생할 수 있는 문제 

1. Non-Repeatable Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리가 다르게 나타나는 현상
2. Phantom Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫 번째 쿼리에서 없던 유령 레코드가 두 번째 쿼리에서 나타나는 현상
3. Dirty Read : 다른 트랜잭션에 의해 수행, 커밋되지 않은 데이터 읽기

 

WHERE절

• FROM절 다음에 위치 (조회되는 데이터의 조건을 설정하여 데이터 제한

 

연산자의 종류 

1. BETWEEN a AND b 
2. IN (list) (Oracle에서는 VARCHAR2 빈 문자열을 NULL로 판단)
3. IS NULL
4. NOT IN(list)
5. LIKE '비교 문자열'

※ NULL값과의 비교(+,-,*,/) 연산은 무조건 NULL값 반환

※ NULL을 조건절에서 사용할 경우 IS NULL, IS NOT NULL 두 개만 사용 가능 

 

 

연산자 우선순위

• () -> NOT -> 비교 연산자 -> AND -> OR

 

함수 

• 벤더에서 제공하는 함수인 내장 함수(Built-in Function)와 사용자가 정의할 수 있는 함수(User Defined Function)로 나눌 수 있음
• 내장 함수는 단일행 함수(Single-Row Function), 다중행 함수(Multi-Row Function), (집계 함수(Aggregate Function), 그룹 함수(Group Function), 윈도우 함수(Window Function)로 구분
• 다중행 함수, 단일행 함수는 모두 단일 값 반환, 1:M 관계 테이블을 조인하는 경우 두 개의 함수 모두 사용 가능

 

단일행 함수 종류

종류	내용	예시
문자형 함수	문자를 입력하면 문자나 숫자 값 반환	LOWER, UPPER, SUBSTR, SUBSTRING, LENGTH/LEN, LTRIM, RTRIM, TRIM, ASCII
숫자형 함수	숫자를 입력하면 숫자 값 반환	ABS(절댓값), MOD, ROUND(해당 자리 반올림), TRUNC(소수점 버림), SIGN(양수 : 1 ,0 : 0, 음수 : -1), CEIL/CEILING(정수 올림), FLOOR(정수 내림), EXP, LOG
날짜형 함수	DATE 타입의 값 연산	SYSDATE/GETDATE(현재 날짜와 시간 출력), EXTRACT(날짜에서 데이터 출력), TO_NUMBER(TO_CHAR(d, 'YYYY' | 'MM' | 'DD'), 1=하루, 1/24=1시간 1/24/60=1분
변환형 함수	문자, 숫자, 날짜형 값의 데이터 타입 반환	TO_NUMBER, TO_CHAR, TO_DATE
NULL 관련 함수	NULL 처리하기 위한 함수	NVL/ISNULL, NULLIF, COALESCE

 

단일행 NULL 관련 함수 종류

1. NVL(표현식1, 표현식2) / ISNULL(표현식1, 표현식2) : 표현식1 값 NULL-> 표현식2, NULL 아니면 표현식1로 표현
2. NULLIF(표현식1, 표현식2) : 표현식1과 표현식2가 같으면 NULL, 같지 않으면 표현식1 리턴
3. COALESCE(표현식1, 표현식2, 표현식3 ...) : NULL값 아닌 최초 표현식 리턴, 모두 NULL이면 NULL 리턴

 

GROUP BY절, HAVING절의 특징

• GROUP BY 절은 행동을 소그룹화 한후, SELECT 절에 집계함수 적용
• 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행
• GROUP BY 절에서는 ALIAS 사용 불가
• 집계 함수는 WHERE 절에 올 수 없다
• HAVING 절에는 집계함수를 이용하여 조건 o
• HAVING 절은 일반적으로 GROUP BY 뒤에 위치

 

CASE WHEN LOC = 'NEW YORK' THEN 'EAST'

=

CASE LOC WHEN 'NEW YORK' THEN 'EAST'

 

ORDER BY 특징

• SQL 문장으로 조회된 데이터들을 다양한 목적에 맞게 특정한 칼럼을 기준으로 정렬
• 칼럼명 대신, ALIAS, 정수 대체 가능
• DEFAULT : ASC, DESC 
• SQL 문장에 가장 마지막에 위치
• SELECT 절에서 정의하지 않은 컬럼 사용 가능

Oracle에서 NULL값을 가장 큰 값으로 취급, SQL Server에서는 NULL 값을 가장 작은 값으로 취급

 

SELECT 문장 실행 순서(FWGHSO)

1. FROM : 발췌 대상 테이블 참조
2. WHERE : 발췌 대상 데이터가 아닌 것 제거
3. GROUP BY : 행들을 소그룹화 한다
4. HAVING : 그룹핑된 값의 조건에 맞는 조건만 출력
5. SELECT : 데이터의 값 출력/계산
6. ORDER BY : 데이터를 정렬한다 

[TOP ()예제] 사원

테이블에서 급여가 높은 2명을 내림차순으로 출력하는데, 같은 급여를 받는 사원이 있으면 같이 출력

•  SELECT TOP(2) WITH TIES ENAME, SAL FROM EMP ORDER BY SAL DESC;

JOIN

• 두 개 이상의 테이블들을 연결 또는 결합하여 데이터를 출력하는 것
• PK, FK 값의 연관에 의해 JOIN 성립 
• 어떤 경우에는 PK, FK가 없어도 논리적인 값들의 연관만으로도 JOIN 성립

N개의 테이블 조회 -> 최소 N-1의 JOIN 필요

 

EQUI JOIN : 2개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하는 경우 사용, PK, FK 관계 기반(=)

 

SELECT 테이블1.칼럼명, 테이블2.칼럼명 

FROM 테이블1, 테이블2

WHERE 테이블1.칼럼명1 = 테이블2.칼럼명2

-> WHERE절에 JOIN 조건을 넣는다.

 

ANSI/ISO SQL 표준 EQUI JOIN 문장

SELECT 테이블1.칼럼명, 테이블2.칼럼명

FROM 테이블1 INNER JOIN 테이블2

ON 테이블1.칼럼명1 = 테이블2.칼럼명2

->ON절에 JOIN절을 넣는다.

 

NON EQUI JOIN : 2개의 테이블 간에 칼럼 값들이 서로 정확하게 일치하지 않는 경우 사용, PK, FK 관계 기반(BETWEEN, <=, >= 연산자)

ex)

SELECT COUNT(*) CNT 

FROM EMP_TBL A, RULE_TBL B

WHERE A.ENAME LIKE B.RULE