[정보처리기사 실기] 8. SQL 응용

 

 

 

 

 

도서 내용 중 중요하다 여겨지는 것만 작성합니다

영리목적이 아닌 공유목적입니다

출처 : 2023 시나공 정보처리기사 실기 (저자: 김정준, 강윤석, 김용갑, 김우경 | 출판사: 길벗)

'기출문제는 시나공 저자(알앤디)가 복원한 것임'

 

제가 이미 잘 알고 있는 것은 적지 않습니다

해당 포스팅을 참고하여 학습하셔도 좋지만

교재를 구매하여 문제를 풀며 학습하시는 것을 추천드립니다

 

---

SQL - DDL

• CREATE, ALTER, DROP

 

CREATE TAELE

제약조건명이 있으면 CONSTRAINT문으로 작성

// ex1)
CREATE TABLE 학생 (
    이름 VARCHAR(15) PRIMARY KEY,
    학번 CHAR(8) NOT NULL,
    전공 CHAR(5),
    성별 SEX,
    생년월일 DATE, 
    FOREIGN KEY(전공) REFERENCES 학과(학과코드)
    	ON DELETE SET NULL
        ON UPDATE CASCADE,
    CONSTRAINT 생년월일제약 CHECK(생년월일>='1980-01-01')
);

// ex2)
CREATE TABLE patient(
    id CHAR(5) PRIMARY KEY,
    name CHAR(10),
    sex CHAR(1),
    phone CHAR(20),
    CONSTRAINT sex_ck CHECK (sex='f' or sex='m'),
    CONSTRAINT id_fk FOREIGNKEY(id) REFERENCES doctor(doc_id)
);

// ex3)
CREATE TABLE 사원(
    사원번호 NUMBER(4) PRIMARY KEY,
    사원명 VARCHAR(10),
    근무지번호 NUMBER(2) FOREIG NKEY REFERENCES 근무지
    ON DELETE CASCADE
);

 

CREATE VIEW

• with ckeck option : 갱신이나 삽입 연산이 수행될 때 뷰 정의 조건을 따라야 함

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

 

CREATE INDEX

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

 

CREATE DOMAIN

CREATE DOMAIN 직위 VARCHAR(10)
DEFAULT '사원'
CONSTRAINT VALID-직위 CHECK(
           VALUES IN('사원', '대리', '과장', '부장', '이사', '사장')
);

 

ALTER TABLE

ALTER TABLE 학생 ADD 학년 VARCHAR(3);

ALTER TABLE 학생 ALTER 학번 VARCHAR(10) NOT NULL;

ALTER TABLE 학생 DROP COLUMN 성적 CASCADE;

 

 

---

SQL - DCL

• COMMIT, ROLLBACK, GRANT, REVOKE

 

GRANT / REVOKE

• DBA : 데이터베이스 관리자
• RESOURCE : 데이터베이스 및 테이블 생성 가능자
• CONNECT : 단순 사용자

 

• WITH GRANT OPTION : 다른 사용자에게 다시 부여할 수 있는 권한
• GRANT OPTION FOR : 다른 사용자에게 권한을 부여할  수 있는 권한을 취소
• CASCADE : 연쇄적 취소

GRANT RESOURCE TO NABI;

GRANT ALL ON 고객 TO NABI WITH GRANT OPTION;

REVOKE GRANT OPTION FOR UPDATE ON 고객 FROM STAR;

REVOKE SELECT ON 수강 FROM 박문수 CASCADE;

 

COMMIT

트랜잭션이 수행한 내용을 데이터베이스에 반영하는 명령

 

ROLLBACK

변경되었으나 아직 COMMIT되지 않은 모든 내용들을 취소하고 데이터베이스를 이전 상태로 되돌리는 명령

ROLLBACK TO SAVEPOINT명;

 

 

---

SQL - DML

• SELECT, INSERT, DELETE, UPDATE

 

삽입문 (INSERT INTO~)

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

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

 

삭제문 (DELETE FROM~)

DELETE FROM 사원
WHERE 이름 = '임꺽정';

 

갱신문 (UPDATE~ SET~)

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

 

검색문 (SELECT FROM)

ex 1) 사원 테이블에서 '주소'를 내림차순 정렬시켜 상위 2개 튜플만 검색

SELECT TOP 2 *
FROM 사원
ORDER BY 주소 DESC;

 

ex2) "망원동"에 거주하는 사원들의 '기본급'보다 적은 '기본급'을 받는 사원의 정보 검색

ALL() : 하위 질의로 검색된 범위를 기본 질의의 조건으로 사용

SELECT 이름, 기본급, 주소
FROM 사원
WHERE 기본급 < ALL (SELECT 기본급 FROM 사원 WHERER 주소 = '망원동');

 

ex3) '상여금'이 100 이상인 사원이 2명 이상인 '부서'의 튜플수

SELECT 부서, COUNT(*) AS 사원수
FROM 상여금
WHERE 상여금 >= 100
GROUP BY 부서
HAVING COUNT(*) >= 2;

 

ex4) 취미활동을 하지 않는 사원들

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

 

ex5)

SELECT SUM(psale)
FROM Sale
WHERE pin IN(
            SELECT id
            FROM Product
            WHERE name LIKE 'USB%');