시나공 정보처리기사 실기 요약 8장 SQL 응용

SECTION 118 SQL - DDL

(3) CREATE DOMAIN

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

(4) CREATE TABLE

ex) '이름', '학번', '전공', '성별', '생년월일'로 구성된 <학생> 테이블을 정의하는 SQL문을 작성하시오, 단, 제약 조건은 다음과 같다.

'이름'은 NULL이 올 수 없고, '학번'은 기본키이다.

'전공'은 <학과> 테이블의 '학과코드'를 참조하는 외래키로 사용된다.

<학과> 테이블에서 삭제가 일어나면 관련된 튜플들의 전공 값을 NULL로 만든다.

<학과> 테이블에서 '학과코드'가 변경되면 전공 값도 같은 값으로 변경한다.

 

(5) CREATE VIEW

예제) <고객> 테이블에서 '주소'가 '안산시'인 고객들의 '성명'과 '전화번호'를 '안산고객'이라는 뷰로 정의하시오.

 

(7) ALTER TABLE

ex1) <학생> 테이블에 최대 3문자로 구성되는 '학년' 속성 추가하시오.

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

ex2) <학생> 테이블의 '학번' 필드의 데이터 타입과 크기를 VARCHAR(10)으로 하고 NULL 값이 입력되지 않도록 변경하시오.

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

(8) DROP

ex1) <학생> 테이블을 제거하되, <학생> 테이블을 참조하는 모든 데이터를 함께 제거하시오.

DROP TABLE 학생 CASCADE;

 

SECTION 119 SQL -DCL

(2) GRANT / REVOKE

ex1) 사용자 ID가 "NABI"인 사람에게 데이터베이스 및 테이블을 생성할 수 있는 권한을 부여하는 SQL문을 작성하시오.

GRANT RESOURCE TO NABI;

ex2) 사용자 ID가 "STAR"인 사람에게 단순히 데이터베이스에 있는 정보를 검색할 수 있는 권한을 부여하는 SQL문을 작성하시오.

GRANT CONNECT TO STAR;

ex3) 사용자 ID가 "NABI"인 사람에게 <고객> 테이블에 대한 모든 권한과 다른 사람에게 권한을 부여할 수 있는 권한까지 부여하는 SQL문을 작성하시오.

GRANT ALL ON 고객 TO NABI WITH GRANT OPTION;

ex4) 사용자 ID가 "STAR"인 사람에게 부여한 <고객> 테이블에 대한 권한 중 UPDATE 권한을 다른 사람에게 부여할 수 있는 권한만 취소하는 SQL문을 작성하시오.

REVOKE GRANT OPTION FOR UPDATE ON 고객 FROM STAR;

(5) SAVEPOINT

ex1) <사원>테이블에서 '사원번호'가 40인 사원의 정보를 삭제한 후 COMMIT을 수행하시오.

DELETE FROM 사원 WHERE 사원번호 =40; COMMIT;

ex2) '사원번호'가 30인 사원의 정보를 삭제하시오.

DELETE FROM 사원 WHERE 사원번호 = 30;

ex3) SAVAPOINT 'S1'을 설정하고 '사원번호'가 20인 사원의 정보를 삭제하시오.

SAVEPOINT S1; DELETE FROM 사원 WHERE 사원번호 = 20;

ex4) SAVEPOINT 'S2'를 설정하고 '사원번호'가 10인 사원의 정보를 삭제하시오.

SAVEPOINT S2; DELETE FROM 사원 WHERE 사원번호 = 10;

ex5) SAVEPOINT 'S2'까지 ROLLBACK을 수행하시오.

ROLLBACK TO S2;

 

SECTION 120 SQL - DML

(2) 삽입문(INSERT INTO~)

예제1) <사원> 테이블에 (이름 - 홍승현, 부서 - 인터넷)을 삽입하시오.

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

예제2) <사원> 테이블에 (장보고, 기획, 05/03/73, 홍제동, 90)을 삽입하시오.

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

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

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

(3) 삭제문(DELETE FROM~)

예제1) <사원> 테이블에서 "임꺽정"에 대한 튜플을 삭제하시오.

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

예제2) <사원> 테이블에서 "인터넷" 부서에 대한 모든 튜플을 삭제하시오.

DELETE FROM 사원 WHERE 부서 = '인터넷';

예제3) <사원> 테이블의 모든 레코드를 삭제하시오.

DELETE FROM 사원;

(4) 갱신문(UPDATE~ SET~)

예제1) <사원> 테이블에서 "홍길동"의 '주소'를 "수색동"으로 수정하시오.

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

예제2) <사원> 테이블에서 "황진이"의 "부서"를 "기획부"로 변경하고 '기본급'을 5만원 인상시키시오.

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

 

SECTION 121 DML - SELECT-1

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

SELECT DISTINCT 주소 FROM 사원;

예제3) <사원> 테이블에서 '기본급'에 특별수당 10을 더한 월급을 "XX부서의 XXX의 월급 XXX" 형태로 출력하시오.

SELECT 부서+'부서의' AS 부서2, 이름 +'의 월급 AS 이름2, 기본급+ 10 AS 기본급2 FROM 사원;

(4) 조건 지정 검색

예제2) <사원> 테이블에서 "기획" 부서에 근무하면서 "대흥동"에 사는 사람의 튜플을 검색하시오.

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

예제3) <사원> 테이블에서 '부서'가 "기획"이거나 "인터넷"인 튜플을 검색하시오.

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

예제4) <사원> 테이블에서 성이 '김'인 사람의 튜플을 검색하시오.

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

예제5) <사원> 테이블에서 '생일'이 01/01/69'에서 '12/31/73' 사이인 튜플을 검색하시오.

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

예제6) <사원> 테이블에서 '주소'가 NULL인 튜플을 검색하시오.

SELECT * FROM 사원 WHERE 주소 IS NULL;

(5) 정렬 검색

예제1) <사원> 테이블에서 '주소'를 기준으로 내림차순 정렬시켜 상위 2개 튜플만 검색하시오.

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

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

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

(6) 하위 질의

예제1) '취미'가 "나이트댄스"인 사원의 '이름'과 '주소'를 검색하시오.

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

예제2) 취미활동을 하지 않는 사원들을 검색하시오.

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

예제3) 취미활동을 하는 사원들의 부서를 검색하시오.

SELECT 부서 FROM 사원 WHERE EXISTS (SELECT 이름 FROM 여가활동 WHERE 여가활동.이름 = 사원.이름);

(7) 복수 테이블 검색

예제) '경력'이 10년 이상인 사원의 '이름', '부서', '취미', '경력'을 검색하시오.

SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력 FROM 사원.여가활동 WHERE 여가활동.경력 >= 10 AND 사원.이름 = 여가활동.이름;

 

SECTION 122 DML -SELECT-2

(5) 그룹 지정 검색

GROUP BY절에 지정된 속성을 기준으로 자료를 그룹화하여 검색한다.

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

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

예제2) <상여금> 테이블에서 부서별 튜플 수를 검색하시오.

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

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

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

(6) 집합 연산자를 이용한 통합 질의

예제1) <사원> 테이블과 <직원> 테이블을 통합하는 질의문을 작성하시오. (단, 같은 레코드가 중복되어 나오지 않게 하시오.)

SELECT * FROM 사원 UNION SELECT * FROM 직원

SECTION 123 DML - JOIN

(1) JOIN

JOIN은 2개의 릴레이션에서 연관된 튜플들을 결합하여, 하나의 새로운 릴레이션을 반환한다.

JOIN은 크게 INNER JOIN과 OUTER JOIN으로 구분된다.

예제1) <학생> 테이블과 <학과> 테이블에서 '학과코드' 값이 같은 튜플을 JOIN하여 '학번', '이름', '학과코드', '학과면'을 출력하는 SQL문을 작성하시오.

SELECT 학번, 이름, 학생.학과코드, 학과명 FROM 학생, 학과 WHERE 학생.학과코드 = 학과.학과코드;

(3) OUTER JOIN

 

SECTION 124 프로시저(Procedure)

(1) 프로시저(Procedure)

SQL을 사용하여 작성한 일련의 작업을 저장해두고 호출을 통해 원할 때마다 저장된 작업을 수행하도록 하는 절차형 SQL이다.

 

SECTION 125 트리거(Trigger)

(1) 트리거(Trigger)

트리거는 데이터베이스 시스템에서 데이터의 삽입(Insert), 갱신(Update), 삭제(Delete) 등의 이벤트(Event)가 발생할 때 관련 작업이 자동으로 수행된게 하는 절차형 SQL이다.

 

SECTION 126 사용자 정의 함수

(1) 사용자 정의 함수

종료 시 처리 결과로 단일값만을 반환하는 절차형 SQL이다.

예제) 'i_성별코드'를 입력받아 1이면 "남자"를, 2면 "여자"를 반환하는 사용자 정의 함수를 'Get_S_성별'이라는 이름으로 정의하시오.

CREATE FUNCTION Get_S_성별(i_성별코드 IN INT) RETURN VARCHAR2 IS BEGIN         IF i_성별코드 = 1 THEN            RETURN'남자';         ELSE            RETURN'여자';         END IF; END;

 

SECTION 128 커서(Cursor)

(1) 커서(Cursor)

커서는 쿼리문의 처리 결과가 저장되어 있는 메모리 공간을 가리키는 포인터(Pointer)이다.

(2) 묵시적 커서(Implicit Cursor)

내부에서 자동으로 생성되어 사용되는 커서이다.

(3) 명시적 커서(Explicit Cursor)

사용자가 직접 정의해서 사용하는 커서

 

SECTION 129 DBMS 접속(Connection)

(2) DBMS 접속 기술

접속 기술	특징
JDBC(Java DataBase Connectivity)	Java 언어로 다양한 종류의 데이터베이스에 접솔할 때 사용하는 표준 API 1997년 2월 썬 마이크로 시스템에서 출시
ODBC(Open DataBase Connectivity)	개발 언어에 관계없이 데이터베이스에 접근하기 위한 표준 개발형 API 1992년 9월 마이크로소프트에서 출시

(3) 동적 SQL(Dynamic SQL)

동적 SQL은 다양한 조건에 따라 SQL 구문을 동적으로 변경하여 처리할 수 있는 SQL 처리 방식이다.

 

SECTION 131 ORM(Object-Relational Mapping)

(1) ORM(Object-Relational Mapping)

ORM은 객체지향 프로그래밍의 객체(Object)와 관계형 데이터베이스(Relational DataBase)의 데이터를 연결하는 기술을 의미한다.

 

SECTION 132 쿼리 성능 최적화

(2) 옵티마이저(Optimizer)

작성된 SQL이 가장 효율적으로 수행되도록 최적의 경로를 찾아 주는 모듈이다.