정보처리기사 SQL 응용 (TCL,DDL,DML 보충)

1. 트랜잭션 (A.C.I.D) ex) 은행 업무를 예시로 들어 봅시다

원자성(Atomicity) : 하나라도 실패시 모두 실패 

ex) A거래처에 1억 입금하는 도중에 서버 에러로 1억이 입금이 안되면 취소 되야겠죠??

지속성(Consistency) : 트랜잭션 성공 후 일관된 DB 상태 보존

ex) A거래처에 1억을 입금하면 내 계좌에는 1억이 없어지고 상대편 계좌에는 1억이 생성되야하는것

고립성(Isolation) : 작업도중 다른 트랜잭션이 들어와 업무 방해 못함

ex)A거래처에 1억 먼저 입금하고 B거래처에 2억 입금하면 3억이 합계가되서 보여지는 경우가 생기면 안되겠죠?

영속성(Durability) : 트랜잭션이 성공하면 영구적으로 DB에 반영

ex)A거래처에 1억 입금한 내역은 절대 사라지면 안됩니다.

 

2. TCL 언어

Commit : 트랜잭션을 메모리에 영구 저장 / Rollback : 트랜잭션 내역 무효화

Checkpoint : Rollback을 위한 시점 지정

※ 예를들어서 insert 업무를 진행하고 commit을 하면 rollback해도 insert 전으로 돌아가지 않습니다.

 

3. DDL (create, alter, truncate, drop)

● CREATE TABLE 문법

CREATE TABLE 사원 (

사번 VARCHAR(10) PRIMARY KEY, // 기본키 생성 (사번은 유일값만족, null값이 못들어옴)

업무 VARCHAR(20) Foregin key References 부서(부서코드), // 부서 테이블의 부서코드를 참조하는 외래키 생성

이름 VARCAHR(20) UNIQUE, //테이블내 유일값을 가진다

생년월일 CHAR(8) NOT NULL, //NULL값이 들어가면 안된다.

성별 CHAR(1) CHECK(성별='M' OR 성별 = 'F'), //M과 F만 들어가게 체크인 성질 부여

입사일 DATE DEFAULT SYSDATE //기본값을 서버날짜로 설정

);  

 

● CREATE VIEW 문법

사원 테이블의 이름과, 사번을 조회하는 VIEW를 생성하되 뷰 이름은 '사원뷰'로 할것

CREATE VIEW 사원뷰 AS SELECT 이름,사번 FROM 사원;

 

● CREATE INDEX 문법

인덱스명 : 사원인덱스 , 사원테이블의 사번컬럼 참조

CREATE INDEX 사원인덱스 ON 사원(사번)

 

● ALTER TABLE 문법

사원 테이블에 전화번호 열 추가  (크기 : VARCHAR(10), 조건 : 테이블내 유일값)

ALTER TABLE 사원 ADD 전화번호 VARCHAR(10) UNIQUE;

사원 테이블에 전화번호 열 변경 (크기 : VARCHAR(20), 조건 : NULL값 들어가면 안됨)

ALTER TABLE 사원 MODIFY 전화번호 VARCHAR2(20) NOT NULL;

사원 테이블에 전화번호 열 삭제 

ALTER TABLE 사원 DROP 전화번호;

 

● DROP 문법

DROP TABLE 사원 : 사원 TABLE 삭제

DROP TABLE 사원 CASCADE : 사원 테이블을 참조하는 테이블도 모두 삭제

DROP TABLE 사원 RESTRICT : 삭제시 다른 테이블은 참조하면서 제거하지 않는 옵션

 

● TRUNCATE 문법

TRUNCATE TABLE 사원 = 사원안에 있는 모든 데이터 삭제, 사원테이블이 없어지는건 아님

 

4. DML(SELECT, INSERT, UPDATE, DELETE)

 

● SELECT GROUP BY 예제

직책, 부서별 기준으로 급여합계가 900만원 이상인 직원을 조회

SELECT 직책, 부서, SUM(급여) AS 급여합계 FROM 급여 

GROUP BY 직책, 부서 // 직책, 부서 기준 그룹핑

HAVING SUM(급여) >= 900 // GROUP BY 절에 조건식

ORDER BY 직책 DESC, SUM(급여합계) OR 급여합계 ASC // 직책기준 DESC(내림차순) 급여합계기준 ASC(오름차순)

 

● JOIN문 예제

A(테이블명 : 도서, 튜플수 : 3개) / B(테이블명 : 가격, 튜플수 : 5개)  / 도서코드가 같은 개수 : 2개

SELECT A.책번호, A.책이름, B.도서가격 FROM 도서 A

(INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN) 가격 B

ON A.도서코드 = B.도서코드 // 조건식

INNER JOIN 튜플 수 : 2

LEFT JOIN 튜플 수  : 3

RIGHT JOIN 튜플 수 : 5

FULL JOIN 튜플 수 : ( 3 + 5 ) - 2 = 6

CROSS JOIN 튜플 수  : 3 X 5 = 15

 

● SUBQUERY 예제 (스칼라뷰, 인라인뷰)

 

단일행 리턴 (스칼라뷰 1개의 컬럼에 1개의 행만 리턴)

SELECT (SELECT MAX(도서가격) FROM 도서 A WHERE A.책번호 = B.책번호 AND A.책이름 = '정보처리기사')

FROM 가격B

 

인라인뷰 (여러개 리턴 가능)

SELECT MAX(도서가격) FROM 가격 A, (SELECT 책번호 FROM 도서 WHERE 책이람 = '정보처리기사') B

WHERE A.책번호 = B.책번호

 

중첩서브 쿼리

SELECT MAX(가격) FROM 가격 WHERE 책번호 IN (SELECT 책번호 FROM 도서 WHERE 책번호 = '정보처리기사')

 

● 집합연산자 (UNION, UNION ALL, INTERSECT, MINUS)

EX) A 테이블 튜플수 10개 / B 테이블 튜플수 8개 / 중첩데이터수 = 3개

SELECT * FROM A UNION SELECT * FROM B = 튜플수 : (10+8) - 3 = 15 (중첩제외)

SELECT * FROM A UNION ALL SELECT * FROM B = 튜플수 : 10+8 = 18 (중첩포함)

SELECT * FROM A INTERSECT SELECT * FROM B = 튜플수 : 3  (중첩수)

SELECT * FROM A MUINUS SELECT * FROM B = 튜플수  : 10-3  = 7 (왼쪽 튜플수 - 중첩수)

 

● COUNT(*)와 COUNT(열값) 차이

사원 테이블의 사번 튜플수 = 5개 중에 3개는 값이 있고 2개는 NULL값이 있을때

 

SELECT COUNT(*) FROM 사원 = 튜플수 : 5개 (NULL 포함 카운트)

SELECT COUNT(사번) FROM 사원 = 튜플수 : 3개 (NULL 제외 카운트)

 

● INSERT 예제

EX) 학생 테이블에 학번:123, 성명:김길동, 학년:1, 수강과목:정보처리기사 데이터를 삽입하시오.

INSERT INTO 학생(학번, 성명, 학년, 과목) VALUES (123, '김길동', 1, '정보처리기사);

 

● UPADATE 예제

EX) 학번이 123 이고 이름이 김길동인 학년을 2로 고치시오

UPDATE 학생 SET 학년 = 2 WHERE 학번 = 123 AND 이름 = '김길동'

 

● ROWNUM(), DENS_RANK, RANK 차이

학생점수

이름	점수
일길동	80
이길동	90
삼길동	100
사길동	100

SELECT 이름, 점수, 

RANK() OVER (ORDER BY  DESC) A,

DENSE_RANK() OVER (ORDER BY 점수 DESC) B,

ROW_NUMBER() OVER (ORDER BY 점수 DESC) C

FROM 학생점수

 

이름	점수	A	B	C
사길동	100	1	1	1
삼길동	100	1	1	2
이길동	90	3	2	3
일길동	80	4	3	4