정보처리기사 필기 - 데이터베이스 구축 : SQL 응용

정보처리기사 필기 - 데이터베이스 구축 : SQL 응용

 

SQL의 개념

 

ⓞ SQL(Structured Query Language)의 개요

 

• 1974년 IBM 연구소에서 개발
• 관계형 데이터베이스 지원
• 관계대수, 관계해석을 기초로 한 혼합언어

ⓞ SQL의 분류

⑴ DDL(정의어)

 

• 스키마/도메인/테이블/뷰/인덱스 정의, 변경, 삭제
• DB관리자, DB설계자가 사용하는 언어
• Create, Alter, Drop

 

⑵ DML(조작어)

 

• 검색, 삽입, 삭제, 수정
• Select, Insert, Delete, Update

 

⑶ DCL(제어어)

 

• 데이터보안 유지, 회복
• Commit, RollBack, Grant, Reboke

ⓞ DDL(Data Define Language, 데이터 정의어)

 

• CREATE SCHEMA : 스키마 정의
• CREATE DOMAIN : 도메인 정의
• CREATE TABLE : 테이블 정의
• CREATE VIEW : 뷰 정의
• CREATE INDEX : 인덱스 정의
• ALTER TABLE : 테이블 변경
• DROP : 삭제

 

ⓞ DCL(Data Control Language, 데이터 제어어)

 

• GRANT / REVOKE : 권한부여 / 권한취소
• COMMIT : 트랜잭션의 성공을 알리는 명령어
• ROLLBACK : 원래 상태로 복구
• SAVEPOINT : RollBack할 저장점 저장

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

 

• 삽입문(INSERT INTO ~)
• 삭제문(DELETE FROM ~)
• 갱신문(UPDATE ~ SET ~)
• 검색 (SELECT ~ FROM ~)

 

⑴ INSERT

 

• 새로운 튜플을 삽입하는 명령

INSERT INTO 테이블명(속성1, 속성2...) VALUES(데이터1, 데이터2..);
INSERT INTO 학생(번호, 이름, 학년, 학과) VALUES('010', '정수현', 2, '사이버안보');
INSERT INTO 학생(번호, 이름) SELECT 번호, 이름 FROM 컴퓨터학과;

⑵ DELETE

 

• 테이블의 특정 튜플을 삭제하는 명령

DELETE FROM 테이블명 WHERE 조건;
DELETE FROM 학생;
DELETE FROM 학생 WHERE 과목='컴퓨터';

⑶ UPDATE

 

• 테이블의 특정 튜플의 내용을 변경하는 명령

UPDATE 테이블명 SET 속성명=데이터 WHERE 조건; 
UPDATE 학생 SET 이름='홍길동' WHERE 번호='101';
UPDATE 학생 SET 수강료=수강료+10000 WHERE 수강과목='컴퓨터';

 

⑷ SELECT

 

• 테이블의 특정 튜플의 내용을 검색하는 명령

SELECT [DISTINCT] 속성명 FROM 테이블명;
SELECT [DISTINCT] 속성명 FROM 테이블명 WHERE 조건 ORDER BY 정렬할 속성명 [ASC] 또는 [DESC] GROUP BY 그룹화 속성명 HAVING 그룹조건; 

EX1. <사원>테이블의 모든 튜플을 검색

select * from 사원;

EX2. <사원>테이블에서 주소속성만 나타내는데 같은 주소는 한번만 출력

select distinct 주소 from 사원;

EX3. <사원>테이블에서 급여에 10을 더한 값을 ‘○○부서의 ○○○의 급여는 ○○○입니다.’ 형태로 출력

select 부서, '부서의 ', 이름, '의 급여는 ', 급여+10, '입니다.' from 사원;

EX4. <사원>테이블에서 부서가 ‘편집’부인 모든 튜플을 검색

select * from 사원 where 부서='편집';

EX5. <사원>테이블에서 부서가 ‘편집’부이면서 주소가 ‘후월동’인 튜플을 검색

select * from 사원 where 부서='편집' and 주소='후월동';

EX6. <사원>테이블에서 부서가 ‘편집’이거나 ‘기획’인 튜플을 검색

select * from 사원 where 부서='편집' or 부서='기획';
select * from 사원 where 부서 in('편집', '기획');

EX7. <사원>테이블에서 성이 ‘홍’인 사람의 튜플을 검색

select * from 사원 whrer 이름 like '홍%';

EX8. <사원>테이블에서 생일이 ‘1970-01-01’~‘1980-12-31’사이인 튜플을 검색

select * from 사원 where 생일 between #1970-01-01# and #1980-12-31#;
select * from 사원 where 생일>=#1970-01-01# and 생일<=#1980-12-31#;

EX9. <사원>테이블에서 급여가 210~220인 튜플을 검색

select * from 사원 where 급여 between 210 and 220;
select * from 사원 where 급여>=210 and 급여<=220;

EX10. <사원>테이블에서 주소가 NULL인 튜플을 검색

select * from 사원 where 주소 is null;

EX11. <사원>테이블에서 주소를 기준으로 오름차순 정렬시켜서 상위 2개의 튜플만 표시

select top 2 * from 사원 order by 주소;

EX12. <사원>테이블에서 부서를 기준으로 오름차순 정렬하고 같은 부서인 경우에는 ‘이름’을 기준으로 내림차순 정렬

select * from 사원 order by 부서, 이름 desc; 

EX13. <사원>테이블에서 부서별 급여의 평균

select 부서, avg(급여) as 평균 from 사원 group by 부서;

EX14. <사원>테이블에서 부서별 튜플의 개수

select 부서, count(*) as 사원수 from 사원 group by 부서;

EX15. <사원>테이블에서 급여가 200 이상이고 사원이 2명 이상인 부서의 튜플의 수

select 부서, count(*) as 사원수 from 사원 where 급여>=200 group by 부서 having count(*)>=2; 

EX16. 경력이 8년 초과인 사원의 이름, 부서, 특기, 경력을 검색

select 사원.이름, 부서, 특기, 경력 from 사원, 활동 where 경력>8 and 사원.이름=활동.이름;

 

ⓞ JOIN

 

- 1개 이상의 테이블에 연관된 튜플을 결합하여 1개의 새로운 릴레이션을 반환

⑴ INNER JOIN

 

• Equi join : 등호를 이용한 조인
• cross join : 조건 없이 조인

⑵ OUTER JOIN 

 

• 조인 조건에 맞지 않은 튜플도 결과로 출력하기 위한 조인
• left : 좌측에 있는 튜플들에 널값을 붙여가며 inner join 결과에 추가하는 방식
• right : 우측에 있는 튜플들에 널값을 붙여가며 inner join 결과에 추가하는 방식
• full : left join과 right join을 합한 것

⑶ SELF JOIN

 

• 같은 테이블에서 2개의 속성을 연결하여 Equi join하는 방식