2020 정보처리기사 필기 - 3.3 SQL 응용(2)

▶ 2020 정보처리기사 필기 정리 ◀

▶ 104 DML

---

DML(Data Manipulation Language, 데이터 조작어)

데이터베이스 사용자가 응용 프로그램이나 질의어를 통해 저장된 데이터를 실질적으로 관리하는데 사용되는 언어

1. 삽입문(INSERT INTO~)

INSERT INTO 테이블명([속성명1, 속성명2, .....])

VALUES(데이터1, 데이터2, ...);

ex1) <사원>테이블에 (이름-홍길동, 부서-인터넷)을 삽입

INSERT INTO 사원(이름, 부서) VALUES('홍길동', '인터넷');

ex2) <사원>테이블에 (장보고, 기획, 05/03/73, 옥계동, 90)을 삽입

INSERT INTO 사원 VALUES('장보고', '기획', #05/03/73#, '옥계동', 90);

ex3) <사원>테이블에 있는 편집부의 모든 튜플을 <편집부원(이름, 생일, 주소, 기본급)> 테이블에 삽입

INSERT INTO 편집부원(이름, 생일, 주소, 기본급) 

SELECT 이름, 생일, 주소, 기본급 FROM 사원 WHERE 부서 = '편집';

2. 삭제문(DELETE FROM~)

DELETE FROM 테이블명 [WHERE조건]; 

- 모든 레코드 삭제 시 WHERE절 생략

☞ DELETE문은 테이블 내의 튜플들만 삭제, DROP문은 테이블 자체를 삭제

ex1) <사원>테이블에서 "황진이"에 대한 튜플을 삭제

DELETE FROM 사원 WHERE 이름 = '황진이';

ex2) <사원>테이블의 모든 레코드 삭제

DELETE FROM 사원;

3. 갱신문(UPDATE~ SET~)

UPDATE 테이블명 SET 속성명 = 데이터, [속성명=데이터, .....] [WHERE 조건];

ex1) <사원>테이블에서 "홍길동"의 '주소'를 "수색동"으로 수정

UPDATE 사원 SET 주소='수색동' WHERE 이름='홍길동';

ex2) <사원>테이블에서 "홍길동"의 '부서'를 "기획부"로 변경하고 '기본급'을 5만원으로 인상시키시오

UPDATE 사원 SET 부서='기획', 기본급=기본급+5 WHERE 이름='홍길동';

☞ 데이터 조작어 4가지 유형

SELECT~ FROM~ WHERE~

INSERT INTO~ VALUES~

DELETE~ FROM~ WHERE~

UPDATE~ SET~ WHERE~

▶ 105 DML - SELECT(1)

---

검색문(SELECT~ FROM~)

SELECT  [PREDICATE] [테이블명.]속성명1, [테이블명.]속성명2, ......,

[그룹함수(속성명)],

[WINDOW함수 OVER(PARTITION BY 속성명1, 속성명2, .... ORDER BY 속성명 3, 속성명4,...)]

FROM 테이블명1, 테이블명2, ...

[WHERE 조건]

[GROUP BY 속성명1, 속성명2, ...]

[HAVING 조건]

[ORDER BY 속성명 [ASC | DESC]];

- PREDICATE : 불러올 튜플 수를 제한할 명령어 기술

> ALL : 모든 튜플 검색, 주로 생략

> DISTINCT : 중복된 튜플이 있으면 그중 첫 번째 한 개만 검색

☞ LIKE 연산자

대표문자	%	_	#
의미	모든 문자를 대표	문자 하나를 대표	숫자 하나를 대표

1. 기본 검색

ex1) <사원>테이블의 모든 튜플 검색

SELECT * FROM 사원;

SELECT 사원.* FROM 사원;

ex2) <사원>테이블에서 '주소'만 검색하되 같은 '주소'는 한 번만 출력하시오.

SELECT DISTINCT 주소 FROM 사원;

ex3) <사원>테이블의 모든 튜플 검색

SELECT * FROM 사원;

SELECT 사원.* FROM 사원;

2. 조건 지정 검색

ex1) <사원>테이블에서 "기획"부서의 모든 튜플 검색

SELECT * FROM 사원 WHERE 부서='기획';

ex2) <사원>테이블에서 "기획"부서에 근무하면서 "대흥동"에 사는 사람의 튜플 검색

SELECT * FROM 사원 WHERE 부서='기획' AND 주소='대흥동';

ex3) <사원>테이블에서 '부서'가 "기획"이거나 "인터넷"인 튜플 검색

SELECT * FROM 사원 WHERE 부서='기획' OR 부서='인터넷';

ex4) <사원>테이블에서 성이 "김"인 사람의 튜플 검색.

SELECT * FROM 사원 WHERE 이름 LIKE "김%";

ex5) <사원>테이블에서 '생일'이 '01/01/69'에서 '12/31/73' 사이인 튜플 검색

SELECT * FROM 사원 WHERE 생일 BETWEEN #01/01/69# AND #12/31/73#;

ex6) <사원>테이블에서 '주소'가 NULL인 튜플 검색

SELECT * FROM 사원 WHERE 주소 IS NULL;

3. 정렬 검색

ex1) <사원>테이블에서 '주소'를 기준으로 내림차순 정렬시켜 검색

SELECT * FROM 사원 ORDER BY 주소 DESC;

ex2) <사원>테이블에서 '부서'를 기준으로 오름차순 정렬, 같은 '부서'에 대해서는 '이름'을 기준으로 내림차순 정렬시켜 검색

SELECT * FROM 사원 ORDER BY 부서 ASC, 이름 DESC;

4. 하위 질의

ex1) '취미'가 "나이트댄스"인 사원의 '이름'과 '주소' 검색

SELECT 이름, 주소 FROM 사원 WHERE 이름=(SELECT 이름 FROM 여가활동 WHERE 취미='나이트댄스'); 

ex2) 취미활동을 하지 않는 사원들 검색

SELECT * FROM 사원 WHERE 이름 NOT IN(SELECT 이름 FROM 여가활동); 

5. 복수 테이블 검색

ex1) '경력'이 10년 이상인 사원의 '이름', '부서', '취미', '경력' 검색

SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력 FROM 사원, 여가활동 

WHERE 여가활동.경력>=10 AND 사원.이름=여가활동.이름; 

▶ 107 DML - SELECT(2)

---

검색문(SELECT~ FROM~)

SELECT [PREDICATE] [테이블명.]속성명1, [테이블명.]속성명2, ......,

[그룹함수(속성명)],

[WINDOW함수 OVER(PARTITION BY 속성명1, 속성명2, .... ORDER BY 속성명 3, 속성명4,...)]

FROM 테이블명1, 테이블명2, ...

[WHERE 조건]

[GROUP BY 속성명1, 속성명2, ...]

[HAVING 조건]

[ORDER BY 속성명 [ASC | DESC]];

- 그룹함수 : GROUP BY절에 지정된 그룹별로 속성의 값 집계

> COUNT(속성명) : 그룹별 튜플 수

> AVG(속성명) : 그룹별 평균

> SUM(속성명) : 그룹별 합계

- WINDOW 함수 : GROUP BY절을 이용하지 않고 속성의 값 집계

> ROW_NUMBER( ) : 윈도우별로 각 레코드에 대한 일련 번호 반환

> RANK( ) : 윈도우별로 순위 반환, 공동 순위 반영

> DENSE_RANK( ) : 윈도우별로 순위 반환, 공동 순위 무시하고 순위 부여

- GROUP BY절 : 특정 속성을 기준으로 그룹화하여 검색할 때 허용. 그룹합수와 함께 사용

- HAVING절 : GROUP BY와 함께 사용, 그룹에 대한 조건 지정

1. WINDOW 함수 이용 검색

ex1) <상여금>테이블에서 '상여내역'별로 '상여금'에 대한 일련 번호를 구하시오. (단, 순서는 내림차순, 속성명은 'NO'로 할것)

SELECT 상여내역, 상여금

ROW_NUMBER( ) OVER(PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS NO

 FROM 상여금 

ex2) <상여금>테이블에서 '상여내역'별로 '상여금'에 대한 순위를 구하시오. (단, 순서는 내림차순, 속성명은 '순위'로 할것)

SELECT 상여내역, 상여금 

RANK( ) OVER(PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS 순위

FROM 사원 WHERE 부서='기획' AND 주소='대흥동';

2. 그룹 지정 검색

ex1) <상여금>테이블에서 '부서'별 '상여금'의 평균을 구하시오.

SELECT 부서, AVG(상여금) AS 평균 FROM 상여금 

GROUP BY 부서;

ex2) <상여금>테이블에서 부서별 튜플 수 검색

SELECT 부서, COUNT(*) AS 사원수 FROM 상여금 

GROUP BY 부서;

ex3) <상여금>테이블에서 '상여금'이 100이상인 사원이 2명 이상인 '부서'의 튜플 수를 구하시오

SELECT 부서, COUNT(*) AS 사원수 FROM 상여금 

WHERE 상여금>=100

GROUP BY 부서

HAVING COUNT(*)>=2;

검색문(SELECT~ FROM~)

SELECT 속성명1, 속성명2, ......,

FROM 테이블명1, 테이블명2, ...

UNION | UNION ALL | INTERSECT | EXCEPT

SELECT 속성명1, 속성명2, ......

FROM 테이블명1, 테이블명2, ...

[ORDER BY 속성명 [ASC | DESC]];

- 집합 연산자의 종류

> UNION : 두 SELECT문의 조회 결과를 통합하여 모두 출력, 중복된 행 한번만 출력

> UNION ALL :  두 SELECT문의 조회 결과를 통합하여 모두 출력, 중복된 행도 그대로 출력

> INTERSECT : 두 SELECT문의 조회 결과 중 공통된 행만 출력

> EXCEPT : 첫 번째 SELECT문의 조회 결과에서 두 번째 SELECT문의 조회 결과를 제외한 행 출력

3. 집합 연산자를 이용한 통합 질의

ex1) <사원>테이블과 <직원>테이블을 통합하는 질의문을 작성하시오 (단, 같은 레코드 중복 X)

SELECT * FROM 사원 

UNION

SELECT * FROM 직원;

▶ 2020 정보처리기사 필기 정리 ◀