[정보처리기사]실기 암기노트 데이터베이스 3장

in Documents on 정보처리기사


정보처리기사 실기 암기노트 (데이터베이스)

3장. SQL

  1. DDL(Data Definition Language)의 개념

    스키마 도메인 테이블 뷰 인덱스 정의 변경 제거 언어

    메타데이터 시스템 카탈로그

    CREATE ALTER DROP

    1. CREATE SCHEMA 스키권을 인증할 사람(AUTH ORI ZATION) 필요

      CREATE SCHEMA 스키마명 AUTHORIZATION 사용자_ID;

    2. CREATE DOMAIN 도메인은 도타(도메인명 + 데이터타입), 도타는 기본(디폴트)이 잘되있음, 인성이 쓰레기라 제약도 큼, 그래서 확인(CHECK) 해야됨

      CREATE DOMAIN 도메인명 데이터_타입
	[DEFAULT 기본값]
	[CONSTRAINT 제약조건명 CHECK (범위값)];

      CREATE DOMAIN SEX CHAR(1)
	DEFAULT '남'
	CONSTRAINT VALID-SEX CHECK (VALUE IN('남', '여'));

    3. CREATE TABLE 프라이머리 고유 포린키 옵션 두개 DELETE UPDATE 제약조건 CHECK

      CREATE TABLE 테이블명
	(속성명 데이터_타입 [NOT NULL], ...
	[, PRIMARY KEY (기본키_속성명, ...)]
	[, UNIQUE (대체키_속성명, ...)]
	[, FOREIGN KEY (외래키_속성명, ...)
		REFERENCES 참조테이블(기본키_속성명, ...)]
		[ON DELETE 옵션]
		[ON UPDATE 옵션]
    [, CONSTRAINT 제약조건명][CHECK (조건식)]);
      • 속성명에 NOT NULL 적용 가능
      • 기본키 PRIMARY KEY
      • 대체키로 사용할 속성(집합) UNIQUE 속성들이 최소성을 만족시켜야함
      • FOREIGN KEY ~ REFERENCES ~ 외래키 참조무결성 CASCADE
      • ON DELETE NO ACTION CASCADE SET NULL SET DEFAULT
      • ON UPDATE NO ACTION CASCADE SET NULL SET DEFAULT
      • CONSTRAINT 제약조건명이 없을경우 CHECK만 사용가능

    4. CREATE VIEW VIEW는 AS SELECT문이 따라온다 ASS

      CREATE VIEW 뷰명[(속성명[, 속성명, ...])]
AS SELECT문;

      SELECT문을 서브쿼리로 사용

      SELECT문의 결과로서 뷰를 생성

      UNION 이나 ORDER BY절을 사용할 수 없음

      CREATE VIEW 안산고객(성명, 전화번호)
AS SELECT 성명, 전화번호
FROM 고객
WHERE 주소 = '안산시';

    5. CREATE INDEX 검색을 빠르게 보조적인 데이터 구조 I-O-T INDEX-ON-TABLE NAME

      CREATE [UNIQUE] INDEX <인덱스명>
	ON 테이블명 ({속성명 [ASC | DESC] [,속성명 [ASC | DESC]]})
	[CUSTER];

      • UNIQUE 이 사용됬으면, 중복값이 없는 속성으로 인덱스를 생성 (없으면 중복값 허용)

      • 〈고객〉테이블에서 UNIQUE한 특성을 갖는 고객번호 속성에 대해 내림차순으로 정렬하여 ‘고객번호_idx’ 라는 이름으로 인덱스를 정의하시오.

        CREATE UNIQUE INDEX 고객번호_idx
	   ON 고객(고객번호 DESC);

    6. CREATE TRIGGER 입력 갱신 삭제 이벤트가 발생할 때마다 자동적으로 수행되는 사용자 프로시저

      무결성 제약조건 구현 관련 테이블의 데이터 일치

      트리거명 AB IUD ON 테이블명 트리거가 언제 실행되게 할것인지? 무슨 작업을 할것인지? 어떤 테이블에서?

      REFERENCING NO TABLE AS 테이블명

      FOR EACH ROW

      WHEN 조건식

      트리거 BODY(BEGIN-END)

      CREATE TRIGGER 트리거명 [동작시기 옵션][동작 옵션] ON 테이블명
REFERENCING [NEW | OLD] TABLE AS 테이블명
FOR EACH ROW
WHEN 조건식
트리거 BODY
      • 동작시기 AFTER 테이블이 변경된 후 BEFORE 테이블이 변경되기 전
      • 동작옵션 INSERT DELETE UPDATE
      • 테이블 선택옵션 NEW OLD
      • WHEN 조건
      • 트리거 BEGIN - END
      CREATE TRIGGER 학년정보_tri BEFORE INSERT ON 학생
REFERENCING NEW TABLE AS 학생
FOR EACH ROW
WHEN new_table.학년 = ''
BEGIN
	SET new_table.학년 = '신입생';
END;

    7. ALTER TABLE DC - DROP COLUMN

      ALTER TABLE 테이블명 ADD 속성명 데이터_타입 [DEFAULT '기본값'];
ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값'];
ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE];

      〈학생〉테이블에 최대 3문자로 구성되는 학년 속성을 추가하는 SQL문
ALTER TABLE 학생 ADD 학년 VARCHAR(3);

    8. DROP

      DROP SCHEMA, DOMAIN, TABLE, VIEW, INDEX, TRIGGER [CASCADE | RESTRICT];

      DROP CONSTRAINT 제약조건명;

      RESTRICT 다른 개체가 제거할 개체를 참조중일 경우 제거가 취소

      〈학생〉테이블을 제거하는 SQL문을 작성하시오. 단, 〈학생〉 테이블을 참조하는 모든 데이터도 함께 제거한다.
DROP TABLE 학생 CASCADE;

    9. 오답노트

      내가 쓴 답
CONSTRAINT sex_ck CHECK (sex = ‘f’ OR sex = ‘m’;),
정답
CONSTRAINT sex_ck CHECK (sex = ‘f’ or sex = ‘m’),

      제약조건 CONSTRAINT ~ CHECK 안에 세미콜론(;) 이 들어가지 않는다!

      내가 쓴 답
CONSTRAINT id_fk CHECK ( FOREIGN KEY id REFERENCES doctor.doc_id; ));
정답
CONSTRAINT id_fk FOREIGN KEY (id) REFERENCES doctor(doc_id));

      외래키가 제약조건으로 들어간 경우, CHECK 생략 이후 id 는 전부 괄호() 를 사용한다.

      내가 쓴 답
FOREIGN KEY (dept) REFERENCES Department(name),
	ON DELETE SET NULL,
	ON UPDATE CASCADE );
정답
FOREIGN KEY(dept) REFERENCES Department(name)
	ON DELETE SET NULL
	ON UPDATE CASCADE );

      외래키 부분에서의 ON DELETE, ON UPDATE 로 이루어지는 부분은 콤마(,) 를 찍지 않는다.

      내가 쓴 답
AS SELECT id, Course.name, Instructor.name
정답
AS SELECT Course.id, Course.name, Instructor.name

      VIEW 를 정의할 때, 테이블이 나와있는 것이 아니면 테이블.속성 으로 표시한다.

      그리고 맨 뒤에 그냥 세미콜론(;) 넣어주면 된다.

      내가 쓴 답
CREATE UNIQUE INDEX Stud_idx
	ON Student(ssn) ASC;
정답
CREATE UNIQUE INDEX Stud_idx
	On Student(ssn, ASC);
* ASC는 생략할 수 있음

      INDEX 정의에서 I-O-T (괄호안에 모두 넣고 끝남)

      내가 쓴 답
CONSTRAINT VALID-직위 VALUE IN(‘사원’, ‘대리’, ‘과장’, ‘부장’, ‘이사’, ‘사장’);
정답
CONSTRAINT VALID-직위 CHECK VALUE IN (...);

      CONSTRAINT - CHECK 는 항상! (외래키 사용할 때만 제외)

      CREATE ( ) 월급_trg AFTER UPDATE ON 급여
	REFERENCING OLD TABLE AS old_tbl NEW TABLE AS new_tbl
	FOR EACH ROW
	WHEN old_tbl.직위 =new_tbl.직위
	BEGIN
		UPDATE 사원 SET 월급 = new_tbl.기본급 + new_tbl.성과급;
	END;

      내가 쓴 답
CREATE SCHEMA 개인정보 AUTHORIZATION id=‘장발장’;
정답
CREATE SCHEMA 개인정보 AUTHORIZATION '장발장';

  2. SQL-SELECT

    1. SELECT문의 일반 형식

      SELECT [PREDICATE] [테이블명.]속성명[AS 별칭][, [테이블명.]속성명, ...]
FROM 테이블명[, 테이블명, ...]
[WHERE 조건]
[GROUP BY 속성명[, 속성명, ...]]
[HAVING 조건]
[ORDER BY 속성명 [ASC|DESC][, 속성명 [ASC|DESC], ...]];
      • PREDICATE 검색할 튜플을 제한할 목적으로 사용되는 조건
        • ALL - 모든 튜플 검색 (기본값)
        • DISTANCT - 중복된 튜플 제거
        • DISTANCTROW - 튜플 전체값을 대상으로 중복된 튜플 제거
      • 2개 이상의 테이블을 검색할 때 테이블명.속성명 으로 사용
      • AS 다른 제목으로 표현
      • FROM WHERE
      • GROUP BY : 특정 속성을 기준으로 그룹화하여 검색할 때 사용, 그룹함수와 함께 사용
      • HAVING : 그룹에 대한 조건
      • ORDER BY
      • WHERE 조건문에 IS NULL 또는 IS NOT NULL 을 사용할 수 있다
      • GROUP BY 는 해당 속성의 중복을 전부 제거하는 것인가? 그룹으로 묶는다 이런느낌
      • IN, NOT IN 의 차이 및 복수테이블과 하위 질의에 대한 차이점 인식하기
      • 하위질의 괄호 안에 세미콜론(;) 넣지 않음
        • 2개의 테이블의 속성들이 SELECT에 들어있을 때, 복수 테이블
        • 1개씩 SELECT에 들어있을 때, 하위질의

    2. 기출 따라잡기

        • 괄호가 항상 중요함! 중복되는 레코드는 한번만 표시하고 = DISTINCT

          내답 : SELECT name (...)
정답 : SELECT DISTINCT name (...)

        • 결과 테이블에 네이밍이 다른것이 아니면 AS 절은 빼는식으로 작성하자

          내답 : SELECT SUM(psale) AS 합계
정답 : SELECT SUM(psale)

        • ORDER BY 구문을 정확히 알 것

          내답 : ORDER BY ASC;
정답 : ORDER BY name [ASC];

        • HAVING 조건에 대해서 조금 더 명확하게

          내답 : GROUP BY 결제여부
HAVING COUNT(결제여부);
          
정답 : GROUP BY 결제여부;

  3. SQL - JOIN

    1. JOIN 개념

      2개 테이블 튜플 결합 하나의 새로운 릴레이션

      INNER JOIN OUTER JOIN

      일반적으로 FROM 절에 기술

      한 테이블에만 있는 속성은 생략가능

      두 테이블에 모두 속해있는 속성은 테이블명과 함께 표시

    2. INNER JOIN

      • EQUI JOIN 공통속성 =(equal) 행을 연결

        • NATURAL JOIN 중복제거모드
          • 이름이 같고, 도메인이 같은 속성이 반드시 존재해야됨
        • JOIN 속성 연결고리 공통속성
        /* WHERE 절을 이용한 EQUI JOIN */
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2, ...
WHERE 테이블명1.속성명 = 테이블명2.속성명;
         
/* NATURAL JOIN을 이용한 EQUI JOIN*/
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 NATURAL JOIN 테이블명2;
         
/* JOIN ~ USING절을 이용한 EQUI JOIN의 표기형식*/
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 JOIN 테이블명2 USING(속성명);


        SELECT 학번, 이름, 학과.학과코드, 학과.학과명
FROM 학생, 학과
WHERE 학생.학과코드 = 학과.학과코드;
         
SELECT 학번, 이름, 학과.학과코드, 학과.학과명
FROM 학생 NATURAL JOIN 학과;
/* 학과코드가 학생, 학과 테이블에 둘다 존재하므로 NATURAL JOIN이 가능한 것 */
         
SELECT 학번, 이름, 학과.학과코드, 학과.학과명
FROM 학생 JOIN 학과 USING 학과코드;

      • NON-EQUI JOIN

        • 비교연산자 사용 (= 이 아닌 >, <, <>, >=, <=)
        SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2, ...
WHERE (NON-EQUI JOIN 조건);
        • 테이블명.속성을 BETWEEN 하면, 학생.성적에 맞게 BETWEEN 되어 등급이 결정됨 (JOIN)

    3. OUTER JOIN OUTER 니까 ON 이 필요

      JOIN 조건에 만족하지 않는 튜플도 결과로 출력

      • LEFT OUTER JOIN

        • 우측 항 릴레이션의 어떤 튜플과도 맞지 않는
        • 좌측 항 릴레이션에 있는 튜플들에 NULL 값을 붙임
        SELECT[테이블명1.]속성명, [테이블명2.]속성명, •••
FROM 테이블명1 LEFT OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;
                                
SELECT[테이블명1.]속성명, [테이블명2.]속성명, •••
FROM 테이블명1, 테이블명2
WHERE 테이블명1.속성명 = 테이블명2.속성명(+);

      • RIGHT OUTER JOIN

        • 좌측 항 릴레이션의 어떤 튜플과도 맞지 않는
        • 우측 항 릴레이션에 있는 튜플들에 NULL 값을 붙임
        SELECT[테이블명1.]속성명, [테이블명2.]속성명, •••
FROM 테이블명1 RIGHT OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;
                                
SELECT[테이블명1.]속성명, [테이블명2.]속성명, •••
FROM 테이블명1, 테이블명2
WHERE 테이블명1.속성명(+) = 테이블명2.속성명;

      • INNER JOIN 에서 LEFT 일경우 우측에 (+) RIGHT 일 경우 좌측에 (+) 랑 동일

      • FULL OUTER JOIN (LEFT + RIGHT) OUTER JOIN

        • INNER JOIN RIGHT LEFT 순으로 진행
        SELECT [테이블명1.]속성명, [테이블명2.]속성명，•••
FROM 테이블명1 FULL OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;

      • 답

        SELECT 학번, 이름, 학생.학과코드, 학과명
FROM 학생 LEFT OUTER JOIN 학과
ON 학생.학과코드 = 학과.학과코드;
         
SELECT 학번, 이름, 학생.학과코드, 학과명
FROM 학생, 학과
ON 학생.학과코드 = 학과.학과코드(+);

    4. SELF JOIN

      같은 테이블 2개의 속성을 연결 EQUI JOIN

       SELECT [별칭1.]속성명, [별칭1.]속성명. •••
 FROM 테이블명1 [AS] 별칭1 JOIN 테이블명1 [AS] 별칭2
 ON 별칭1.속성명 = 별칭2.속성명;
       
 SELECT [별칭1.]속성명, [별칭1.]속성명, •••
 FROM 테이블명1 [AS] 별칭1, 테이블명1 [AS] 별칭2
 WHERE 별칭1.속성명 = 별칭2.속성명;

    5. 기출 따라잡기

      • 학생 LEFT OUTER JOIN 학과
      • 학생.학과코드 = 학과.학과코드(+);
      • 학생에만 NULL 값이 존재하므로, LEFT OUTER JOIN 이다

  4. SQL - DML

    1. DML(Data Manipulation Language) 의 개념

      데이터베이스 사용자 응용프로그램 OR 질의어 저장된 데이터 관리 언어

      데이터베이스 사용자 - 데이터베이스 관리 시스템 (DBMS) 인터페이스 제공

      INSERT DELETE UPDATE

    2. INSERT문 INSERT INTO VALUES

      INSERT INTO 테이블명[(속성명1, 속성명2, •••)]
VALUES (데이터1, 데이터2, •••);

      • 대응하는 속성, 데이터 개수, 데이터타입 일치

      • 속성명 생략 가능 단, 기술된 속성 순으로

        INSERT INTO 사원 VALUES (‘장보고’, ‘기획’, ’05/03/73’, ‘구의동’, 90);

      • SELECT 문 검색 결과 삽입 가능

      • 〈사원〉테이블에 있는 편집 부서의 모든 튜플을 〈편집부원(이름, 생일, 주소, 기본급)〉 테이블에 삽입하는 SQL문을 작성하시오. SELECT 결과를 이용해 삽입 VALUES 생략

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

    3. DELETE문 DELETE FROM [WHERE]

      DELETE FROM 테이블명 WHERE 조건;

      DELETE FROM 사원 WHERE 이름 = '임꺽정';
DELETE FROM 사원; /* 모든 튜플을 삭제할 때 WHERE 절 생략 */

      단, DML DELETE문은 테이블 구조나 테이블 자체는 남겨두고 테이블 내의 튜플만 삭제하는 것임

      전부 삭제하려면 DDL의 DROP문을 사용해야 함

    4. UPDATE문 : UPDATE SET WHERE

      특정 튜플 내용 갱신

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

      • 〈사원〉테이블에서 홍길동의 주소를 ‘퇴계동’ 으로 갱신하는 SQL문을 작성하시오.

        UPDATE 사원 SET 주소='퇴계동 WHERE 이름='홍길동';

      • 〈사원〉테이블에서 황진이의 부서를 ‘기획’으로 변경하고 기본급을 5 인상하는 SQL문을 작성하시오.

        UPDATE 사원 SET 부서='기획', 기본급 = 기본급+5 WHERE 이름='황진이';

    5. 기출 따라잡기 SECTION 072

      /* 〈사원〉테이블에 있는 자료 중에서 ‘부서’가 “기획”인 자료를 검색하여 〈기획부(성명, 경력，주소, 기본급)〉테이블에 삽입하는 SQL문을 작성하시오.*/
      
내답 : INSERT INTO 기획부
	  SELECT 성명, 경력, 주소, 기본급 FROM 사원 WHERE 부서='기획';
      	  
정답 : INSERT INTO 기획부(성명, 경력, 주소, 기본급)
	  SELECT 성명, 경력, 주소, 기본급
	  FROM 사원
	  WHERE 부서 = '기획';

      /* "장발장”과 “황진이” 사원의 ‘부서’를 “편집”으로 갱신하는 SQL문을 작성하시오. */
      
내답 : UPDATE 사원 SET 부서='편집' WHERE 성명=‘장발장’ OR 성명=‘황진이’;
정답 : UPDATE 사원 
	  SET 부서='편집'
	  WHERE 성명 IN ('장발장', '황진이');

  5. SQL - DCL

    1. DCL(Data Control Language)

      보안 무결성 회복 병행제어

      보병은 권한이 없으니 우회(무회)해서 돌아가세요

      COMMIT ROLLBACK GRANT REVOKE

    2. COMMIT / ROLLBACK

      • COMMIT : 트랜잭션 데이터베이스 반영 일관성(Consistency) 데이터베이스에 반영완료(Commit)
      • ROLLBACK : 변경 내용 취소 비일관성(Incosistency) 일부만 반영 롤백(Rollback)

    3. GRANT / REVOKE

      • GRANT 권한부여
      • REVOKE 권한해제
      GRANT 사용자등급 TO 사용자_ID_리스트[IDENTIFIED BY 암호];
REVOKE 사용자등급 FROM 사용자_ID_리스트;

      • 사용자 등급 DBA(데이터베이스 관리자) RESOURCE(데이터베이스 및 테이블 생성 가능자) CONECT(일반 사용자)

      • GRANT RESOURCE TO NABI;

      • GRANT CONNECT TO STAR;

      • 테이블 속성에 대한 권한 부여 및 취소 GRANTOT, ROF

        GRANT 권한_리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];
REVOKE [GRANT OPTION FOR] 권한_리스트 ON 개체 FROM 사용자 [CASCADE];
        • 권한 종류 ALL SELECT INSERT DELETE UPDATE ALTER
        • WITH GRANT OPTION
        • GRANT OPTION FOR
        • CASCADE
        • GRANT ALL ON 고객 TO NABI WITH GRANT OPTION;
        • REVOKE GRANT OPTION FOR UPDATE ON 고객 FROM STAR;
        • 작은따옴표로 구분하지 않는다

    4. SECTION 073

      • 김하늘에게〈강좌〉테이블에 대해 삭제하는 권한을 부여하고, <강좌>테이블에 대해 삭제하는 권한을 다른 사람에게부여할 수 있는 권한을 부여하는 SQL문을 작성하시오.

        내답 : GRANT DELETE ON 학생 TO 김하늘 GRANT OPTION;
정답 : GRANT DELETE ON 강좌 TO 김하늘 WITH GRANT OPTION;

  6. 예상 문제은행

    • 문제 항상 잘 읽을 것! 조건! 조건! 조건!

      CHECK (기본급 >= 1000000);

    • 2개의 테이블이 있을 때, 해당 속성이 어느 테이블에 속한지 확인하고, FROM 구문 틀리지 말것!

    • ~가 없는 이라는 조건이 붙었을 때, IS NULL 보다는 NOT IN을 먼저 생각해본다

      SELECT 이름, 재직년도, 기본급
FROM 사원
내가쓴답 : WHERE 사원 IN ( SELECT 이름 FROM 사원 WHERE 자격증 IS NULL);
정답 : WHERE 이름 NOT IN ( SELECT 이름 FROM 자격증);

      SELECT 이름
FROM 자격증
GROUP BY 이름
HAVING COUNT(*) >= 2;

    • WITH CHECK OPTION

      CREATE VIEW 3학년학생
SELECT * FROM 학생 WHERE 학년=3 WITH CHECK OPTION;

    • 검색 결과가 오른차순으로 되있는지, 내림차순으로 되어있는지 확인 (문제에서 제시한 것 말고)

      <검색 결과>대로 표시하는 SQL문을 작성하시오.

    • SELECT 상호, 총액
FROM 거래내역
WHERE 총액 IN (SELECT MAX(총액) FROM 거래내역);

      • iff 는…




© 2018. by HYEON

Powered by HYEON