Write Thinking

1. VIEW의 개념

 테이블이나 다른 VIEW을 기초로 한 논리적인 테이블이고 VIEW는 자체의 데이터는 없지만 테이블의 데이터를 보거나 변경할 수 있는 창과 같다. VIEW은 실제적으로는 질의 문장을 가진다.

1.1 VIEW의 장점

 1) VIEW은 데이터베이스의 선택적인 내용을 보여줄 수 있기 때문에 데이터베이스에 대한 액세스를 제한한다.

 2) 복잡한 질의어를 통해 얻을 수 있는 결과를 간단한 질의어를 써서 구할 수 있게 한다.

 3) 데이터 독립성을 허용한다.

 4) 동일한 데이터의 다른 VIEW를 나타낸다.

 5) 조인을 한 것처럼 여러 테이블에 대한 데이터를 VIEW을 통해볼 수 있다.

 6) 한 개의 VIEW로 여러 테이블에 대한 데이터를 검색할 수 있다.

 7) 특정 평가기준에 따른 사용자 별로 다른 데이터를 액세스 할 수 있다.

1.2 VIEW의 종류

1.2.1 Simple VIEW

 1) 오직 하나의 테이블에서만 데이터가 유래된다.

 2) 데이터 그룹 또는 함수를 포함하지 않는다.

 3) VIEW를 통해 DML 수행 가능

1.2.3 Complex VIEW

 1) 다중 테이블에서 데이터가 유래된다.

 2) 데이터 그룹 또는 함수를 포함한다.

 3) VIEW을 통한 DML을 항상 허용하지 않는다.

1.3 VIEW의 생성

 1) CREATE VIEW 문장 내에서 SUBQUERY을 내장하여 VIEW를 생성한다.

 2) SUBQUERY은 복합 SELECT 구문을 포함할 수 있고 ORDER BY 절을 포함할 수 없다.

1.3.1 Syntax

 OR REPLACE               이미 존재한다면 다시 생성한다.

 FORCE                       Base Table 유무에 관계없이 VIEW을 만든다.

 NOFORCE                  기본 테이블이 존재할 경우에만 VIEW를 생성한다.

 view_name                 VIEW의 이름

 Alias                         Subquery를 통해 선택된 값에 대한 Column명이 된다.

 Subquery                   SELECT문장을 기술한다.

 WITH CHECK OPTION   VIEW에 의해 액세스 될 수 있는 행만이 입력, 갱신될 수 있다.

 Constraint                  CHECK OPTION 제약 조건에 대해 지정된 이름이다.  

 WITH READ ONLY        이 VIEW에서 DML이 수행될 수 없게 한다.

Guidelines

 1) VIEW을 정의하는 질의어는 조인, 그룹, Subquery를 포함하는 복잡한 SELECT 문장으로 구성될 수 있다.

 2) VIEW을 정의하는 질의어에는 ORDER BY절을 쓸 수 없다.

 3) 제약 조건의 이름을 명시하지 않으면 시스템이 SYS_Cn 형태의 이름을 지정한다.

 4) VIEW을 삭제하거나 재생성하지 않고 VIEW의 정의를 변경하려면 OR REPLACE 옵션을 쓸 수 있다.

문제 1) EMP 테이블에서 20번 부서의 세부 사항을 포함하는 EMP_20 VIEW를 생성 하여라

문제 2) EMP 테이블에서 30번 부서만 EMPNO를 EMP_NO로 ENAME를 NAME로 SAL를 SALARY로 바꾸어 EMP_30 VIEW를 생성 하여라.

1.4 VIEW의 구조 및 이름 확인

 일단 VIEW가 생성되면, VIEW의 이름과 VIEW 정의를 보기 위해 USER_VIEWS 라는 데이터 사전 테이블을 질의할 수 있습니다. VIEW을 만드는 SELECT 문장의 텍스트는 LONG 열에 저장됩니다.

문제 3) 현재 SESSION를 이루고 있는 사용자가 소유한 VIEW를 조회하시오.

1.5 데이터 액세스 VIEW

 VIEW을 사용하여 데이터를 액세스할 때 ORACLE SERVER은 다음 작업을 수행합니다.

 1) USER_VIEWS 데이터 사전 테이블에서 VIEW 정의를 검색합니다.

 2) VIEW 기반 테이블에 대한 액세스 권한을 확인합니다.

 3) VIEW 질의를 기본 테이블 또는 테이블들에서의 동등한 작업으로 전환합니다.

문제 4) emp_30 의 VIEW에서 자료를 조회하여라.

1.5.1 VIEW의 수정

 OR REPLACE 옵션은 비록 이 이름이 이미 존재할지라도 VIEW가 생성될 수 있도록 해주므로 그 소유자에 대한 오래된 VIEW 버전업할 수 있다.

문제 5) 부서번호 10번만 포함하고 있는 이미 생성되어 있는 VIEW의 내용을 사원번호(employee_no),이름(employee_name),업무(job_title)의 내용으로 변경하여라.

주의

 CREATE VIEW 절에서 열 별칭을 지정할 때 별칭은 SUBQUERY 의 열과 동일한 명령으로 나열됨을 명심하십시오.

1.6 복합 VIEW 생성

 두 테이블로부터 값을 출력하는 그룹 함수를 포함하는 복잡한 VIEW를 생성합니다. VIEW의 어떤 열이 함수나 표현식에서 유래되었다면 별칭은 필수적입니다.

문제 6) 부서별로 부서명, 최소 급여, 최대 급여, 부서의 평균 급여를 포함하는 DEPT_SUM VOEW을 생성하여라.

1.7 VIEW에서 DML 연산 수행

 1) 단순 VIEW에서 DML 연산을 수행할 수 있습니다.

 2) VIEW가 다음을 포함 한다면 행을 제거할 수 없습니다.

  ① 그룹 함수

  ② GROUP By절

  ③ DISTINCT 키워드

 3) 다음을 포함한다면 VIEW에서 데이터를 수정할 수 없습니다.

  ① 그룹 함수

  ② GROUP BY 절

  ③ DISTINCT 키워드

  ④ 표현식으로 정의된 열

  ⑤ ROWNUM 의사열

 4) 다음을 포함한다면 VIEW에서 데이터를 추가할 수 없습니다.

  ① 그룹 함수

  ② GROUP BY절

  ③ DISTINCT 키워드

  ④ 표현식으로 정의된 열

  ⑤ ROWNUM의사열

  ⑥ VIEW에 의해 선택되지 않은 NOT NULL열이 기본 테이블에 있을 경우

1.8 WITH CHECK OPTION 절 사용

 VIEW을 통해 참조 무결성 체크를 수행하는 것이 가능합니다. 또한 데이터베이스 LEVEL 에서 제약 조건을 적용할 수 있습니다. VIEW은데이터 무결성을 보호하기 위해 사용될 수 있지만, 사용은 매우 제한됩니다. VIEW을 통해 수행되는 INSERT와 UPDATE는 WITH CHECK OPTION 절이 있으면 VIEW을 가지고 검색할 수 없는 행 생성을 허용하지 않음을 명시합니다. 그러므로 삽입되거나 갱신되는 데이터에 대해서 무결성 제약 조건과 데이터 검증 체크를 허용합니다. VIEW가 선택하지 않은 행에 대해 DML 작업을 수행하려고 하면, 지정된 제약 조건 명과 함께 에러가 출력됩니다.

문제 7) EMP 테이블과 동일한 EMP_20(20번 부서만)이라는 VIEW를 WITH CHECK OPTION을 사용하여 생성하여라.

참고

 부서번호가 30으로 변경된다면 VIEW은 더 이상 그 종업원들을 볼 수 없기 때문에 아무행도 갱신되지 않습니다. 그러므로 WITH CHECK OPTION절로 VIEW은 부서 20종업원만 볼 수 있고, 이 종업원들에 대한 부서번호가 VIEW을 통해 변경되는 것을 허용하지 않습니다.

1.9 DML 연산 부정

 1) WITH READ ONLY 옵션으로 VIEW을 생성하면 VIEW에서 DML 연산을 수행할 수 없습니다.

 2) VIEW에서 임의의 행에서 DML 연산을 수행하려고 하면 ORACLE SERVER 에러 ORA-01752가 발생합니다.

문제 8) EMP 테이블에서 EMP_10(10 번 부서 중 EMPNO, ENAME, JOB)이라는 VIEW를 WITH READ ONLY OPTION을 사용하여 생성하여라.

1.10.1 Syntax

문제 9) 앞에서 생성한 EMP_10, EMP_20 VIEW을 삭제하여라.

1, SEQUENCE

 SEQUENCE는 테이블의 행에 대한 SEQUENCE 번호를 자동적으로 생성하기 위해 사용될 수 있다. SEQUENCE는 사용자가 생성한 데이터베이스 객체이다. SEQUENCE에 대한 전형적인 사용은 각행에 대해 유일해야 하는 PRIMARY KEY 값을 생성하기 위해서 입니다. SEQUENCE 는 Oracle8에 의해 발생되고 증가(또는 감소) 됩니다.

1.1 SEQUENCE 특징

 1) 자동적으로 유일 번호를 생성합니다.

 2) 공유 가능한 객체

 3) 주로 기본 키 값을 생성하기 위해 사용됩니다.

 4) 어플리케이션 코드를 대체합니다.

 5) 메모리에 CACHE되면 SEQUENCE 값을 액세스 하는 효율성을 향상시킵니다.

1.2 Syntax

sequence_name          SEQUENCE의 이름입니다.

INCREMENT BY n        정수 값인 n으로 SEQUENCE 번호 사이의 간격을 지정.

                               이 절이 생략되면 SEQUENCE는 1씩 증가.

START WITH n            생성하기 위해 첫번째 SEQUENCE를 지정.

                               이 절이 생략되면 SEQUENCE는 1로 시작.

MAXVALUE n             SEQUENCE를 생성할 수 있는 최대 값을 지정.

NOMAXVALUE            오름차순용 10^27 최대값과 내림차순용-1의 최소값을 지정.

MINVALUE n              최소 SEQUENCE 값을 지정.

NOMINVALUE             오름차순용 1과 내림차순용-(10^26)의 최소값을 지정.

CYCLE | NOCYCLE        최대 또는 최소값에 도달한 후에 계속 값을 생성할 지의 여부를 지정.

                               NOCYCLE이 디폴트

CACHE | NOCACHE     얼마나 많은 값이 메모리에 오라클 서버가 미리 할당하고 유지하는가를 지정.

                              디폴트로 오라클 서버는 20을 CACHE.

문제 1) DEPT 테이블의 PRIMARY KEY에 사용되는 DEPT_DEPTNO SEQUENCE를 생성 합니다.

참고

 CYCLE OPTION을 사용해서는 안됩니다. (PRIMARY KEY으로 사용될 경우)

1.3 SEQUENCE 확인

 한번 SEQUENCE 를 생성했으면 데이터 사전에 등록 됩니다. SEQUENCE 가 데이터베이스 객체가 된 이후에 USER_OBJECTS DATA DICTIONARY에서 식별할 수 있습니다. 또한 데이터 사전의 USER_SEQUENCES 테이블을 검색함으로써 SEQUENCE의 설정 값을 확인할 수 있다.

문제 2) 현재 SESSION을 이루고 있는 사용자가 소유하고 있는 SEQUENCE를 조회하여라.

1.4 SEQUENCE 사용법

 테이블에 사용할 절차적인 번호를 생성하기 위해 SEQUENCE를 사용할 수 있다. NEXTVALUE와 CURRVALUE 의사열을 사용하여 SEQUENCE 값을 참조한다.

1.4.1 NEXTVAL 과 CURRVAL 의사열

가) 특징

 1) NEXTVAL는 다음 사용 가능한 SEQUENCE 값을 반환 한다.

 2) SEQUENCE가 참조될 때 마다, 다른 사용자에게 조차도 유일한 값을 반환한다.

 3) CURRVAL은 현재 SEQUENCE 값을 얻는다.

 4) CURRBAL이 참조되기 전에 NEXTVAL이 사용되어야 한다.

나) NEXTVAL과 CURRVAL의 사용 규칙

 1) NEXTVAL과 CURRVAL을 사용할 수 있는 경우 

  ① SUBQUERY가 아닌 SELECT문

  ② INSERT문 dml SELECT문

  ③ INSERT문의 VALUES절

  ④ UPDATE문의 SET절

 2) NEXTVAL과 CURRVAL 사용할 수 없는 경우

  ① VIEW문의 SELECT문

  ② DISTINCT 키워드를 사용한 SELECT문

  ③ GROUP BY, HAVING, ORDER BY를 이용한 SELECT문

  ④ SELECT, DELETE, UPDATE 문장에서의 SUBQUERY

  ⑤ CREATE TABLE, ALTER TABLE 명령문의 DEFAULT절

문제 3) DEPT 테이블에 부서명을 영업부, 위치를 분당구 정자동을 입력하여라.

문제 4) DEPT_DEPTNO SEQUENCE의 현재 값을 확인하시오.

1.5 SEQUENCE 값 CACHE

 SEQUENCE 값에 대해 보다 빠른 액세스를 허용하기 위해 메모리에 SEQUENCE를 CACHE 합니다. CACHE는 SEQUENCE를 처음 참조할 때 형성됩니다. 다음 SEQUENCE가 사용된 후에 SEQUENCE에 요구하면 CACHE된 SEQUENCE를 메모리에 갖다 놓습니다.

1.6 SEQUENCE에서 간격의 경계

 1) SEQUENCE 값에서 간격(gap)은 아래의상황에서 발생합니다.

  ① ROLLBACK

  ② SYSTEM CRASH

  ③ SEQUENCE가 다른 테이블에서 사용될 때

1.7 SEQUENCE 수정

 INCREMENT BY, MAXVALUE, MINVALUE, CYCLE, CACHE을 변경할 수 있습니다.

1.7.1 Syntax

1.7.2 SEQUENCE 수정 지침

 1) SEQUENCE에 대한 ALTER 권한을 가지거나 소유자여야 합니다.

 2) 이후의 SEQUENCE 번호만 영향을 받습니다.

 3) SEQUENCE는 다른 번호에서 SEQUENCE 를 다시 시작하기 위해서는 제거하고 다시 생성하여야 합니다.

 4) 유효한 검사를 수행합니다.

1.8 SEQUENCE 제거

 데이터 사전에서 SEQUENCE를 제거하기 위해 DROP SEQUENCE 문장을 사용합니다. SEQUENCE를 제거하기 위해서는 소요자이거나 DROP ANY SEQUENCE 권한을 가져야 합니다.

1.8.1 Syntax

문제 ) DEPT_DEPTNO SEQUENCE를 삭제하여라

1. 데이터(DML) 조작어

 DML(Data Manipulation Language) 명령은 데이터를 입력, 수정, 삭제하는 SQL 명령어이다. 데이터베이스에 영구적으로 반영되지 않은 데이터 조작 명령어들을 TRANSACTION 이라고 하며 오라클에서는 이를 하나의 논리적 작업 단위로 사용한다.

1,1 INSERT 문장

 테이블에 사용하여 테이블에 새로운 행을 삽입(INSERT)할 수 있다.

1.1.1 Syntax

1.1.2 사용 예

- 모든 column에 대해 값을 갖는 새로운 행을 삽입한다.

- INSERT 절의 column 은 선택적으로 기입할 수 있다. 이럴 경우 열중 NOT NULL 제약 조건이 있는 열은 반드시 포함하여야 한다.

- 문자와 날짜 값은 단일 따옴표(' ')를 둘러싼다.

Guidelines

 1) VALUES절을 가지는 INSERT 문장은 한번에 오직 하나의 행만을 추가한다.

 2) 모든 행에 값을 갖는 새로운 행을 삽입할 수 있기 때문에 column list는 INSERT 절에 필요하지 않다. 하지만 테이블에 정의된 순서에 따라 값을 나열해야 한다.

 3) 명확성을 위해 INSERT 절에 column list를 사용하면 좋다. (권장)

 4) 문자와 날짜는 단일 따옴표 안에 쓰나, 수치 값은 사용하지 않는다.

1.1.3 NULL 값을 새로운 행에 추가

 자료형에 관계없이 사용 가능하다.

가) 암시적 방법

나) 명시적 방법

1.1.4 특정 날짜 값 삽입

 형식 DD-MON-YY 는 항상 날짜 값을 입력할 때 사용한다. 이 형식은 현재 세기에 대한 DEFAULT 세기를 다시 호출한다. 또한 날짜가 시간 정보를 포함하므로 DEFAULT 시간은 자정(00:00:00)이다. 날짜를 다른 세기로 입력하거나 또는 특정 시간을 요구 한다면 TO_DATE 함수를 사용하여라.

예) 아래의 INSERT 문장은 날짜를 입력하는 방법이다.

1.1.5 치환 변수를 사용하여 값 입력

 SQL*Plus 의 치환 변수를 사용하여 사용자가 상호 작용으로 값을 추가할 수 있다. 날짜와 문자 값은 단일 인용 부호를 사용하여 감싸는 것이 사용하기에 편하고 SCRIPT로 작성하여 사용하면 입력시 매우 유용하다.

예) SCRIPT 안에서 치환 변수 사용하는 방법이다.

1.1.6 다른 테이블로부터 행 복사

 기존의 테이블로부터 값을 가져와 테이블에 추가하기 위해서 INSERT 문장을 사용할 수 있다. 즉 VALUES 절에서 subquery를 사용할 수 있다.

가) Syntax

문제 1) EMP 테이블에서 EMPNO, ENAME, SAL, HIREDATE의 COLUMN만 선택하여 EMP_10 테이블을 생성한 후 10번 부서만 선택하여 이에 대응하는 값을 EMP_10 테이블에 입력하여라.

참고

  INSERT 절의 열의 개수와 서브쿼리의 열의 개수가 좌측부터 1대1 대응하며 자료형과 길이가 같아야 한다.

1.1.7 INSERT 시 무결성 제약 조건의 오류

 무결성 제약 조건(5가지)이 위배되면 INSERT 시 ERROR가 발생합니다.

1.2 UPDATE 문장

 1) UPDATE문장으로 기존의 행을 갱신합니다.

 2) 일반적으로 단일 행을 식별하기 위해서 기본 키(primary key)를 사용합니다. 다른 열을 사용하면 원하지 않는 여러 행이 갱신될 수 있습니다.

 3) 특정 열이나 행은 WHERE 절을 이용하여 갱신할 수 있다.

1.2.1 Syntax

참고

 DEMO TABLE를 새로 생성하여 실습을 하자

문제 2) EMP 테이블에서 사원 번호가 7788인 사원의 부서를 10번으로 변경하여라.

문제 3) EMP 테이블에서 사원 번호가 7788인 사원의 부서를 20, 급여를 3500으로 변경하여라.

문제 4) EMP 테이블에서 부서를 모두 10으로 변경하여라.

1.2.2 다중 열 SUBQUERY로 갱신

 다중 열 SUBQUERY는 UPDATE 문장의 SET 절로 구현할 수 있다.

가) Syntax

문제 4) EMP 테이블에서 SCOTT 의 업무와 급여가 일치하도록 JONES 업무와 급여를 개인하여라

1.2.3 다른 테이블을 근거로 한 행 갱신

 다른 테이블의 값을 근거로 하는 테이블에서 행을 갱신하기 위해 UPDATE 문장에서 SUBQUERY를 사용한다.

문제 4) EMP 테이블을 근거로 EMPLOYEE 테이블을 생성한 후 7902의 업무에 해당하는 사원의 부서번호를 7902의 부서번호로 갱신하여라.

1.2.4 UPDATE시 무결성 제약 조건 ERROR

 무결성 제약 조건이 위배되는 값으로 UPDATE할 경우 ERROR가 발생한다.

문제 5)EMP 테이블에서 10번 부서의 사원을 모두 91번 부서로 갱신하여라.

문제 6) DEPT 테이블에서 부서 번호 10을 15로 갱신하여라.

1.3 DELETE 문장

 1) DELETE 문장을 사용하여 테이블로부터 기존의 자료를 삭제할 수 있다.

 2) WHERE 절을 명시하여 특정 행이나 행들을 삭제할 수 있다.

 3) WHERE 절을 생략하면 테이블의 모든 행이 삭제 된다.

1.3.1 Syntax

문제 7) EMP 테이블에서 사원번호가 7499인 사원의 정보를 삭제하여라.

문제 8) EMP 테이블에서 입사일자가 83년인 사원의 정보를 삭제하여라.

1.3.2 다른 테이블을 근거로 한 행 삭제

 다른 테이블을 값을 근거로 행을 삭제하기 위해서는 SUBQUERY를 사용하여야 한다.

문제 9) EMP 테이블의 자료 중 부서명이 'SALES'인 사원의 정보를 삭제하여라.

1.3.3 무결성 제약 조건 ERROR

 무결성 제약 조건을 위반하도록 행을 삭제하면 ERROR가 발생한다.

문제 10) EMP 테이블에서 사원 번호가 7902인 사원의 정보를 삭제하여라.

문제 11) DEPT 테이블에서 부서명이 'ACCOUNTING' 부서의 정보를 삭제하여라.

1.4. 데이터베이스 TRANSACTION

 ORACLE SERVER는 TRANSACTION을 근거로 하는 데이터의 일관성을 보증한다. TRANSACTION은 데이터 변경시에 보다 나은 융통성과 제어를 제공하며 그들은 사용자 프로세스 실패나 시스템 실패 같은 이벤트에서 데이터의 일관성을 책임집니다. TRANSACTION은 데이터를 일관되게 변경하는 DML 문장으로 구성됩니다. 예를 들면, 하나의 예금에 대한 차변과 똑같은 금액이 있는 다른 예금에 대한 대변을 포함해야 하는 두 개의 예금 사이에 예금을 전달합니다. 액션은 둘 모두 실패하거나 둘 모두 성공해야 합니다. 대변은 차변 없이는 인증될 수 없습니다.

1.4.1 TRANSACTION의 유형

1.4.2 TRANSACTION의 시작과 종료

가) TRANSACTION의 시작

 1) 실행 가능한 SQL 문장이 제일 처음 실행될 때

나) TRANSACTION의 종료

 1) COMMIT이나 ROLLBACK

 2) DDL이나 DCL 문장의 실행(자동 COMMIT)

 3) 기계 장애 또는 시스템 충돌(crash)
 4) deadlock 발생

 5) 사용자가 정상 종료

1.5.2 COMMIT 과 ROLLBACK의 장점

 1) 데이터의 일관성 제공

 2) 데이터를 영구적으로 변경하기 전에 데이터 변경을 확인하게 한다.

 3) 관련된 작업을 논리적으로 그룹화 할 수 있다.

1.4.3 TRANSACTION 제어

 COMMIT, SAVEPOINT, ROLLBACK 문장으로 TRANSACTION의 논리를 제어할 수 있다.

참고

 하나의 TRANSACTION 이 끝난 후에 다음의 실행 가능한 SQL 문장은 자동적으로 다음 TRANSACTION을 시작할 것이다. DDL과 DCL은 자동적으로 COMMIT되므로 TRANSACTION을 임시적으로 종료한다. SAVEPOINT는 ANSI 표준 SQL이 아니다.

1.4.3 암시적 TRANSACTION 처리

 1) 자동 COMMIT은 다음의 경우 발생

 ① DDL,DCL 문장이 완료시

 ② 명시적인 COMMIT이나 ROLLBACK 없이 SQL*Plus를 정상 종료

 2) 자동 ROLLBACK은 다음의 경우 발생

 ① SQL*Plus를 비정상 종료 또는 시스템 실패

1.4.4 COMMIT이나 ROLLBACK 이전의 데이터 상태

 1) 데이터 이전의 상태로 복구가 가능하다.

 2) 현재 사용자는 SELECT 문장으로 DML 작업의 결과를 확인할 수 있다.

 3) 다른 사용자는 SELECT 문장으로 현재 사용자 사용한 DML 문장의 결과를 확인할 수 없다.

 4) 변경된 행은 LOCK이 설정되어서 다른 사용자가 변경할 수 없다.

1.4.5 COMMIT 이후의 데이터 상태

 1) 데이터베이스에 데이터를 영구적으로 변경

 2) 데이터의 이전 상태는 완전히 상실

 3) 모든 사용자가 결과를 볼 수 있다.

 4) 변경된 행의 LOCK이 해제되고 다른 사용자가 변경할 수 있다.

 5) 모든 SAVEPOINT는 제거된다.

문제 12) EMP테이블에서 7788인 사원의 부서번호를 30번 부서로 갱신한 후 자료를 확정하여라.

1.4.6 ROLLBACK이후의 데이터 상태

 1) 데이터의 변경이 취소

 2) 데이터의 이전 상태로 복구

 3) 변경된 행들의 LOCK이 해제되어 다른 사용자가 변경할 수 있다.

1.4.7 SAVEPOINT로 변경을 ROLLBACK

 1) SAVEPOINT를 사용하여 현재 TRANSACTION내에 표시자를 생성한다.

 2) ROLLBACK TO SAVEPOINT 명령을 사용하여 표시자까지 ROLLBACK

1.4.8 문장 단위 ROLLBACK

 1) 실행 동안에 단일 DML 문장이 실패하면 단지 그 문장만이 ROLLBACK 한다.

 2) ORACLE SERVER은 암시적 SAVEPOINT를 구현 한다.

 3) 모든 다른 변경들은 유지된다.

 4) 사용자는 COMMIT이나 ROLLBACK을 실행하여 명시적으로 TRANSACTION을 종료한다.

1.5 읽기 일관성

 1) 읽기 일관성은 항상 데이터의 검색이 일관되게 보증한다.

 2) 사용자에 의해 행해진 변경은 다른 사용자에 의해 행해진 변경과 충돌하지 않는다.

 3) 데이터를 똑같게 보증한다.

1.6 Locking

 1) 동시 TRANSACTION 사이의 상호 작용이 파괴되지 않도록 막아 줍니다.

 2) 사용자 액션을 요구하지 않습니다.

 3) 자동적으로 낮은 LEVEL의 제약 조건을 사용합니다.

 4) TRANSACTION이 지속되도록 합니다.

 5) 두가지 기본적인 모드를 가집니다.

  ① Exclusive

  ② Share

1.6.1 Locking Mode

1. 테이블 생성

 CREATE TABLE 문장을 실행하여 테이블을 생성 합니다. 이 문장은 DDL 문장으로 Oracle8 데이터베이스 구조를 생성, 수정, 삭제하는데 사용되는 SQL 문장입니다. 이러한 문장은 데이터베이스에 즉각 영향을 미치며 데이터베이스 사전(DATA DICTIONARY)에 정보를 기록 합니다. CREATE TABLE 문장을 실행 후 SQL*Plus에서 "DESC table_name"으로 확인 할 수 있습니다. 테이블을 생성하기 위해서는 SYSTEM PRIVILEGE(다음 과정)인 CREATE TABLE 권한(SQL>SELECT * FROM role_sys_prives;)을 가지고 있어야 합니다. 또한 사용자가 테이블을 만들 수 있는 공간(SQL>SELECT * FROM user_free_space;)을 확보하여야 합니다.

참고

 SYSTEM PRIVILEGE, OBJECT PRIVILEGE, 사용자의 권한, 사용 가능한 공간을 사용자는 DATA DICTIONARY를 검색하여 알 수 있다. 물론 뒤 장에서 좀더 자세히 다루도록 하겠다.

1.1 ORACLE에서 사용하는 객체

 ORACLE 데이터베이스는 여러 개의 데이터 구조를 가지고 있습니다. 데이터베이스 설계에서 각각의 구조는 데이터베이스 개발 단계에서부터 생성할 수 있습니다.

Guildlines

 1) 데이터베이스를 사용하고 있는 동안에도 언제든지 테이블을 생성할 수 있다.

 2) 테이블의 크기는 명시할 필요가 없다.

 3) 테이블 구조는 언제든지 수정 가능하다.

문제 1) ORACLE DATE DICTIONARY에서 SYSTEM PRIVILEGE를 조회하여라.

문제 2) ORACLE DATA DICTIONARY에서 OBJECT PRIVILEGE를 조회하여라.

문제3) 현재 SESSION을 이루고 있는 사용자의 SYSTEM PRIVILEGE 중 ROLE에 관련된 사항을 ORACLE DATA DICTIONARY에서 조회하여라.

문제 4) 현재 SESSION을 이루고 있는 사용자가 사용할 수 있는 FREE SPACE가 얼마인지 ORACLE DATA DICTIONARY에서 조회하여라.

1.1.1 Syntax

 schema                테이블의 소유자

 table_name           생성하고자 하는 테이블 이름, 사용자 단위로 유일한 이름

 column                테이블에서 사용하는 열 이름, 테이블 단위로 유일한 이름

 datatype              열의 자료형

 DEFAULT expr        INSERT문장에서 값을 생략시 기본적으로 입력되는 값을 명시

 column_constraint  열정의 부분에서 무결성 제약 조건을 기술

 table_constraint     테이블 정의 부분에서 무결성 제약 조건을 기술

1.1.2 이름 지정 규칙

 객체 이름을 지정하는 표준 규칙에 따라 데이터베이스 테이블과 열의 이름을 정합니다.

 1) 문자로 시작하여야 한다.

 2) 문자의 길이는 1 ~ 30이내를 사용한다.

 3) 오직 A ~ Z, a ~ z, 0~ 9, _, $, # 만을 사용 가능하다. 단 한글 데이터베이스에서는 한글 사용 가능하다.

 4) 동일한 사용자가 소유한 객체 이름은 중복될 수 없다.

 5) 예약어는 사용할 수 없다.

1.1.3 DATA TYPE

Guidelines

 1) 테이블이나 다른 데이터베이스 객체에 대한 서술적인 이름을 사용하여라.

 2) 다른 테이블에도 일관되게 똑같은 이름을 지정하여라(예:EMP와 DEPT의 DEPTNO)

 3) 객체 이름은 대소문자를 구분하지 않는다.

1.1.4 DEFAULT OPTION

 열은 DEFAULT OPTION을 사용하여 DEFAULT 값을 부여할 수 있다. 이 OPTION은 열에 대한 값없이 어떤 행을 입력할 경우 NULL값이 입력되지 않게 해 줍니다.

 1) 삽입시 열에 대한 Default 값을 명시한다.

 2) 기술 가능한 값은 literal 값, 표현식, SQL 함수(SYSDATE,USER등) 이다.

 3) 불가능한 값은 다른 열의 이름이나 의사(NEXTVAL, CURRVAL 등) 열 입니다.

 4) DEFAULT DATA TYPE은 열의 DATA TYPE과 일치해야 한다.

1.2 제약 조건

 ORACLE SERVER은 부적절한 자료가 입력되는 것을 방지하기 위하여 constraint을 사용한다.

 1) 제약 조건은 테이블 LEVEL에서 규칙을 적용합니다.

 2) 제약 조건은 종속성이 존재할 경우 테이블 삭제를 방지 합니다.

 3) 테이블에서 행이삽입, 갱신, 삭제될 때마다 테이블에서 규칙을 적용합니다.

 4) Developer/2000 같은 ORACLE TOOL에 대한 규칙을 제공 합니다.

 5) 제약 조건의 유형은 ORACLE에서 유효합니다.

Guidelines

 모든 제약 조건은 DATA DICTIONARY에 저장 됩니다. 제약 조건의 이름을 의미 있게 부여 했다면 참조하기가 보다 쉽습니다. 제약 조건의 이름은 표준 객체 이름 규칙을 따릅니다. 제약 조건을 명명하지 않는다면 ORACLE SERVER이SYS_Cnnnnn의 형태로 이름을 생성합니다.

 제약 조건은 테이블 생성시나 테이블이 생성된 후에 정의될 수 있습니다. 또는 일시적으로 DISABLE 할 수 있고 ENABLE 할 수도 있습니다. User_constraints 의 DATA DICTIONARY VUEW을 조회하므로 지정 테이블에 대해 정의된 제약 조건을 볼 수 있다.

1.2.1 제약 조건 정의 방법

 제약 조건 정의하는 방법에는 COLUMN LEVEl 과 TABLE LEVEL 두 가지 방법이 있다.

가) 컬럼 LEVEl 제약 조건(COLUMN LEVEL CONSTRAINT)

 1) 열별로 제약 조건을 정의한다.

 2) 무결성 제약 조건 5가지를 모두 적용할 수 있다.

3) NOT NULL 제약 조건은 컬럼 LEVEL 에서만 가능 하다.

Syntax

나 ) 테이블 LEVEL 제약 조건(TABLE LEVEL CONSTRAINT)

 1) 테이블의 칼럼 정의와는 개별적으로 정의한다.

 2) 하나 이상의 열을 참조할 경우에 사용

 3) NOT NULL을 제외한 나머지 제약 조건만 정의 가능하다.

Syntax

Guidelines

 constraint name 만 보고도 어떤 용도의 CONSTRAINT 인가를 식별할 수 있으면 사용자는 쉽게 데이터베이스를 운용할 것이다. 다음의 규칙에 따라 constraint name을 부여하기를 권장한다. tablename_cloumnname_constrainttype (예 : emp_empno_pk, emp_deptno_fk)

1.2.2 데이터 무결성 제약 조건의 종류

가) PRIMARY KEY(PK)

 1) 테이블에 대한 기본 키를 생성합니다.

 2) 하나의 기본 키만이 각 테이블에 대해 존재할 수 있다.

 3) PRIMARY KEY 제약 조건은 테이블에서 각행을 유일하게 식별하는 열 또는 열의 집합입니다. (UNIQUE와 NOT NULL 조건을 만족)

 4) 이 제약 조건은 열 또는 열의 집합의 유일성을 요구하고 NULL 값을 포함할 수 없음을 보증 합니다.

 5) UNIQUE INDEX가 자동 생성된다.

Syntax

문제 5) 아래의 두 문장의 차이점을 설명하여라.

나) FOREIGN KEY(FK)

 1) FOREIGN KEY는 DETAIL 쪽에서 정의한다.

 2) MASTER TABLE 의 PRIMARY KEY, UNIQUE KEY로 정의된 열을 지정할 수 있으며 열의 값과 일치하거나 NULL 값이어야 한다.

 3) FOREIGN KEY는 열 또는 열의 집합을 지정할 수 있으며 동일 테이블 또는 다른 테이블간의 관계를 지정할 수 있다.

 4) ON DELETE CASCADE 을 사용하여 DETAIL TABLE 에서 관련된 행을 삭제하고 MASTER TABLE에서 삭제를 허용할 수 있다.

Syntax

문제 6) 아래의 두 문장의 차이점을 설명하여라

주의

 FOREIGN KEY 값은 MASTER TABLE 에서 존재하는 값과 일치해야 하거나 NULL 이 되어야 한다. FOREIGN KEY 값은 데이터 값을 기초로 하여 순전히 논리적이지 분리적이거나 포인터가 아니다. MASTER TABLE(parent)은 참조 당하는 쪽(DEPT TABLE)을 테이블을 의미하고 DETAIL TABLE(chid)은 참조하는 쪽(EMP TABLE)의 테이블을 의미한다.

 Guidelines

 1) MASTER TABLE(참조 당하는 쪽)을 먼저 생성하여야 한다.

 2) MASTER TABLE 에 PRIMARY KEY 또는 UNIQUE KEY로 설정된 열을 DETAIL TABLE 에서 참조하여야 한다.

 3) MASTER TABLE 과 DETAIL TABLE의 참조하는 열과 참조 당하는 쪽의 자료형과 크기가 일치해야 한다.

다) UNIQUE key(UK)

 1) UNIQUE Key 무결성 제약 조건은 열 또는 열 집합의 모든 값들이 유일해야 한다.

 2) 중복된 값을 가지는 행이 존재할 수 없음을 의미한다.

 3) PRIMARY KEY와 유사하나 NULL을 허용한다.

 4) 열이 하나 이상 포함되어 있다면 composite unique key라 부릅니다.

 5) UNIQUE Key에 대하여 UNIQUE INDEX 가 자동 생성된다.

Syntax

문제7) 아래의 두 문장의 차이점을 설명하여라.

라) NOT NULL(IN)

 1) NOT NULL 제약 조건은 열에서 NULL을 허용하지 않도록 보증한다.

 2) NOT NULL 제약 조건이 없는 열은 DEFAULT로 NULL을 허용한다.

 3) NOT NULL 제약 조건은 COLUMN CONSTRAINT에서만 기술 가능하다.

Syntax

문제 8) 아래의 두 문장의차이점을 설명하여라.

마) CHECK(CK)

 1) CHECK 제약 조건은 행이 만족해야 하는 조건을 정의한다.

 2) 다음과 같은 표현식은 허용되지 않습니다.

  ① CURRVAL, NEXTVAL, LEVEL, ROWNUM에 대한 참조

  ② SYSDATE, UID, USER, USERENV 함수에 대한 호출

  ③ 다른 행에 있는 값을 참조하는 질의

  ④ ORACLE SERVER가 사용하는 예약어

Syntax

문제 9) 아래의 두 문장의 차이점을 설명하여라.

1.3 테이블 차트에 의한 테이블 생성

1.3.1 테이블 차트

 가) TABLE NAME : POST

나) TABLE NAME : MEMBER

1.3.2 테이블 생성 문

 가) TABLE NAME : POST

나) TABLE NAME : MEMBER

1.4 SUBQUERY을 사용한 테이블 생성

 테이블 생성시 이미 만들어져 있는 기존의 테이블을 이용하여 특정 열 또는 특정 행들만을 추출하여 사용자가 필요로 하는 새로운 테이블을 만들 수 있다.

1.4.1 SUBQUERY을 이용하여 테이블 생성 방법

 1) CREATE TABLE 문장과 AS SUBQUERY을 사용하여 테이블을 생성하고 행을 삽입합니다.

 2) SUBQUERY의 열의 개수와 명시된 열의 개수를 좌측부터 일치시킨다.

 3) 열 이름과 DEFAULT VALUE를 가진 열을 정의한다.

1.4.2 Syntax

 table_name   테이블 이름

 column1       열 이름, DEFAULT VALU, 무결성 제약 조건

 subquery      새로운 테이블에 삽입할 행의 집합을 정의한 SELECT 문장

Guidelines

 1) 테이블은 명시된 열 이름으로 생성, SQL 문장에 의해 RETURN 된 행들이 테이블에 삽입.

 2) 열 정의는 오직 열 이름과 DEFAULT VALUE만 정의 가능

 3) 열이 기술되었다면 열의 수는 SUBQUERY의 열과 좌측부터 1대1 대응

 4) 열이 기술되지 않았다면 테이블의 열 이름은 SUBQUERY의 열 이름과 동일

 5) SUBQUERY 에서 계산 식이나 함수를 사용하면 계산식과 함수를 열 이름으로 사용할 수 없기 때문에 반드시 Alias을 지정하거나 table_name 옆에 열 이름을 기술하여야 한다.

문제 10) EMP 테이블에서 30 부서에 근무하는 사원의 정보만 추출하여 EMP_30 테이블을 생성하여라. 단 열은 사원번호, 이름, 업무, 입사일자, 급여, 보너스를 포함합니다.

문제 11) EMP 테이블에서 부서별로 인원수, 평균 급여, 급여의 합, 최소 급여, 최대 급여를 포함하는 EMP_DEPTNO 테이블을 생성하여라.

문제 12) EMP 테이블에서 사원번호, 이름, 업무, 입사일자, 부서번호만 포함하는 EMP_TEMP 테이블을 생성하는데 자료는 포함하지 않고 구조만 생성하여라.

1.5 데이터 사전(DATA DICTIONARY) 질의

 사용자가 소유한 다양한 데이터베이스 객체를 보기 위해서 데이터 사전을 질의하여 알 수 있다.

 1) DDl 문장을 실행하면 그 정보는 데이터 사전(DATA DICTIONARY)에 등록

 2) 사용자가 소유한 테이블을 조회

문제 13) 현재 SESSION을 이루고 있는 사용자가 소유하고 있는 TABLE을 조화하여라.

문제 14) 현재 SESSION을이루고 있는 사용자가 소유한 모든 객체를 조회하여라.

문제 15) 현재 SESSION을 이루고 있는 사용자가 소유한 테이블, 뷰, 동의어, 시퀀스를 조회하여라

2. 테이블을 수정

 테이블을 생성한 이후에 열을 생략 되었거나, 열 정의를 변경할 필요가 있을 수 있다. 테이블의 구조를 변경할 경우 ALTER TABLE 명을 사용하여 변경 한다.

2.1 새로운 열 추가

 1) 새로운 열을 추가 할 수는 있지만 테이블에 있는 기존의 열은 DROP 할 수 없다.

 2) 열이 위치를 기술할 수 없으며 항상 테이블에서 마지막에 위치 합니다.

 3) 열을 추가할 때 테이블이 행을 포함하고 있다면 새로운 열은 이미 존재하는 열을 NULL로 초기화 한다.

2.1.1 Syntax

문제 16) BONUS 테이블에 ETC COLUMN을 추가하여라. 단 자료형은 VARCHAR2(50) 사용하여라.

문제 17) EMP_30 테이블에 DEPTNO NUMBER(2)을 추가하여라.

2.2 열 수정

 ALTER TABLE의 MODIFY 절을 사용하여 열의 정의를 수정할 수 있습니다. 열의 수정은 열의 자료형, 크기, DEFAULT VALUE 입니다.

2.2.1 Syntax

문제 18) 아래 구문들을 설명하여라.

Guidelines

 1) 숫자열의 정밀도나 폭을 증가할 수 있다.

 2) 열이 모두 NULL이거나 테이블에 자료가 없으면 열의 폭을 감소시킬 수 있다.

 3) 열이 NULL 을 포함하면 열의 자료형을변경할 수 있다.

 4) 열이 NULL을 포함하거나 크기를 변경하지 않으면 CHAR을 VARCHAR2로 변경하거나 그 반대의 겨우도 가능하다.

 5) 열의 DEFAULT VALUE를 변경하는 것은 이후의 INSERT 문장에만 영향을 미칩니다.

2.3 제약 조건 추가

 ADD절을 가지는 ALTER TABLE 문장을 사용하여 기존의 테이블에 대한 제약 조건을 추가할 수 있다.

2.3.1 Syntax

 제약 조건의 선택 사항이지만 기술하기를 권장한다. 기술하지 않을 경우는 ORACLE SERVER이 생성(SYS_Cnnnnn)하여 부여한다.

문제 19) EMP 테이블에서 이름 필드에 UNIQUE CONSTRAINT 를 설정하고 DATA DICTIONARY에서 확인 하여라. 

Guidelines

 1) 제약 조건의 추가, 삭제는 가능하지만 변경은 불가능하다.

 2) 제약 조건의 활성화 또는 비활성화가 가능하다.

 3) MODIFY 절을 사용하여 NOT NULL제약 조건을 추가한다.

주의

 데이터는 열이 추가되는 시점에서 기존의 열에 대해 명시될 수 없기 때문에 테이블에 행이 하나도 없을 경우에만 NOT NULL열을 정의할 수 있다.

2.4 제약 조건 삭제

 DROP 절을 가지는 ALTER TABLE 문장을 사용하여 기존의 테이블에 대한 제약 조건을 삭제할 수 있다.

2.4.1 Syntax

참고

 제약 조건을 삭제하면 USER_CONSTRAINTS, USER_CONS_COLUMN 데이터 사전 뷰에서 제약 조건 이름을 삭제한다. DROP의 CASCADE 문장은 모든 종속적인 제약 조건이 삭제됩니다.

문제 20) 전 문제에서 생성한 EMP 테이블에 있는 emp_ename_uk을 삭제하고 DATA DICTIONARY에서 확인하여라.

참고

 무결성 제약 조건을 삭제할 때, 그 제약 조건은 더 이상 ORACLE SERVER에 의해 적용되지 않으며 데이터 사전에서 확인할 수 없다.

2.5 제약 조건 비활성화

 DISABLE 절을 가지는 ALTER TABLE 문장을 사용하여 삭제 또는 재생성 없이 제약 조건을 비활성화할 수 있다.

Guidelines

 1) 무결성 제약 조건을 비활성화 하기 위하여 ALTER TABLE 문장을 사용하여 DISABLE 할 수 있다.

 2) 종속적인 무결성 제약 조건을 비활성화 하기 위하여 CASCADE를 사용한다.

 3) CREATE TABLE 문장과 ALTER TABLE 문장으로 DISABLE 할 수 있다.

2.5.1 Syntax

문제 21) EMP 테이블에 있는 PRIMARY KEY(EMP_PRIMARY_KEY)를 DISABLE하여라.

2.6 제약 조건 활성화

 ENABLE 절을 가지는 ALTER TABLE 문장을 사용하여 삭제 또는 재생성 없이 제약 조건을 활성화할 수 있다.

Guidelines

 1) 제약 조건이 활성화 된다면 그 제약 조건은 테이블의 모든 데이터에 적용된다. 테이블의 모든 자료는 데이터의 제약 조건과 일치해야 한다.

 2) UNIQUE key와 PRIMARY ket는 제약 조건이 활성화 된다면 UNIQUE INDEX 가 자동 생성된다.

 3) CREATE TABLE 문장과 ALTER TABLE 문장으로 ENABLE 할 수 있다.

2.6.1 Syntax

문제 22) EMP 테이블에 있는 PRIMARY KEY(EMP_PRIMARY_KEY), FOREIGN KEY(EMP_SELF_KEY)를 ENABLE 하여라.

2.7 제약 조건 조회

 테이블 소유자가 소유자 이름을 붙이지 ㅇ낳는 제약 조건은 시스템이 이름을 부여한다. 제약 조건 유형에서 C는 CHECK, P는 PRIMARY KEY, R은 REFERENCE, U는 UNIQUE를 담당하고 NOT NULL은 CHECK가 담당한다.

문제 23) EMP 테이블에 있는 각종 제약 조건을 조회하여라.

2.8 객체 이름 변경

 테이블, 뷰, 시쿼스, 동의어를 변경하기 위해 RENAME 문장을 실행 합니다. 단 객체 소유자이어야 합니다.

2.8.1) Syntax

문제 24) 이전에 생성한 EMP_30 테이블의 이름을 EMP_TEMP30으로 변경하여라.

2.9 TRUNCATE TABLE 문장

 테이블의 OWNER 이거나 DELETE TABLE 권한을가진 사용자가 테이블의 모든 행을 삭제(구조는 삭제되지 않는다)하고 사용하고 있던 기억 공간을 모두 해체할 경우에 사용합니다. 삭제된 행은 복구(ROLLBACK)할 수 없습니다.

2.9.1 Syntax

문제 25) 이전에 생성한 EMP_TEMP30 테이블의 모든 자료를 삭제하고 사용하고 있던 기억공간을 모두 해제하여라.

참고

 DELETE 문장은 테이블의 모든 행을 삭제할 수 있지만, 저장 공간을 해제할 수 없습니다.

2.10 테이블에 주석문 추가

 COMMENT 문장을 사용하여 열, 테이블, 뷰, 스냅샷에 대하여 2000Byte까지 주석을 추가할 수 있다. ㅈ석은 데이터 사전 VIEW(All_col_comments, user_col_comments. al_tab_comments, user_tab_comments)를 통하여 볼 수 있다.

2.10.1) Syntax

문제 26) EMP 테이블에 "Employee Information"라는 주석을 추가하여라.

3. 테이블 삭제

 DROP TABLE 문장은 Oracle8 테이블의 정의를 삭제 합니다. 테이블을 삭제할 때 데이터베이스는 테이블에 있는 모든 자료와 그와 연관된 모든 INDEX를 DROP 하고 사용하고 있던 공간을 돌려줍니다.

Guidelines

 1) 테이블의 모든 구조와 데이터가 삭제 됩니다.

 2) DDL 문장이기 떄문에 TRANSACTION 이 COMMIT 됩니다.

 3) 모든 인덱스가 삭제 됩니다.

 4) VIEW나 SYNONYM은 남지만 사용시 ERROR가 발생합니다.

 5) 테이블의 OWNER 나 DROP ANY TABLE 권한을 가진 사용자만이 테이블을 삭제할 수 있습니다.

3.1 Syntax

주의

 일단 실행된 DROP TABLE 문장은 복구(ROLLBACK)할 수 없습니다. ORACLE SERVER 은 DROP TABLE 문장을 실행할 때 삭제 여부를 질문하지 않습니다.

문제 27) EMP_TEMP30의 테이블을 삭제하여라.

문제 28) DEPT 테이블을 삭제하여라.

참고

 SQL > @c:\orawin95\dbs\demobld 은 DEMO SCRIPT 가 있는 절대 패스를 사용하여 데모 테이블을 다시 생성합니다.

1. SQL*Plus 명령어

 SQL*Plus 명령어는 오라클 서버(데이터베이스)와 연관되어 작용하는 명령어는 아니며 사용자가 보다 효율적으로 SQL을 활용할 수 있도록 환경을 제공하는 TOOL 이다.

1.1 SQL 과 SQL*Plus 의 차이점

1.1.1 SQL의 특징

 1) RDBMS의 표준 언어

 2) SQL Buffer 에 바로 전에 실행한 SQL 문장이 저장

 3) 명령의 끝은 ;을 사용한다

가) 명령어의 종류

1.1.2 SQL*Plus의 특징

 1) SQL 문장을 실행 한다.

 2) SQL Buffer 에 저장되지 않는다.

 3) SQL 문장을 편집한다.

 4) 환경 및 기본 조회 결과를 FORMATTING

가) 명령어의 종류

1.2 SQL 명령 편집 및 실행

1.2.1 SQL Buffer에는 SQL 명령어의 편집

 SQL Buffer에 있는 명령어를 Line 단위로 편집할 수 있다.

가) 종류

나) 사용 예

1.2.2 파일에 있는 명령어 편집과 실행

 SCRIPT를 생성하거나 LINE 단위의 편집이 아니라 SCREEN 편집을 할 경우에 유용하다. SQL*Plus 파일 명령어는 파일을 저장, 획득, 적재 그리고 실행하는데 사용할 수 있다.

가) 종류

참고

 파일명을 쓰지 않고 EDIT 명령어를 사용할 때, 기본적인 파일명은 afiedt.buf 이다. 이 파일은 버퍼를 편집할 때마다 이 파일에 겹쳐 쓰게 된다. 현재의 디렉토리에 없는 파일명을 명시하면 SQL*Plus는 파일 이름을 묻는다.

나) 사용 예

1.2.3 SQL*Plus의 환경 설정

 SQL*Plus를 사용한 환경 설정은 SET명령을 이용한다.

가) Syntax

 시스템변수   세션 환경을 제어하는 변수

 값               시스템 변수의 값

나) 종류

Guidelines

 1) SET명령어는 현재의 세션(운영 중인 SQL*Plus) 환경을 제어

 2) login.sql에는 로그온시 실행되는 표준 SET 명령과 그 외의 SQL*Plus 명령을 기술

 3) login.sql을 수정하여 부가적인 SET 명령을 사용

참고

 login.sql 파일에는 접속할 때마다 필요한표준 SET 명령과 그 외의 SQL*Plus 명령어들이 들어 있다. 접속할 때에 이 파일을 읽어서 명령어가 수행된다. 로그 아웃을 하면 사용자 정의 설정이 상실된다. 설정 값을 영구적으로 변경하려면 login.sql 파일에 추가한다.

다) 사용 예

1.2.3 SQL*Plus FORMAT 명령어

 다음의 명령어를 사용하여 리포트의 속성을 제어할 수 있다.

Guidelines

 1) 모든 포맷 명령은 SQL*Plus SESSION의 마지막이나 변경 전까지 효력을 유지한다.

 2) 모든 리포트 후에는 SQL*Plus Default 값으로 RESET 하는 것을원칙으로 한다.

 3) SQL*Plus 변수 설정을 Default 값으로 해주는 명령은 없다. 특정 값을 알거나 로그아웃한 후 로그인을 해야 한다.

 4) 열에 별칭을 사용하였다면 열 이름이 아닌 별칭을 사용하여야 한다.

1.2.5 COLUMN 명령어

 열의 출력을 제어합니다.

가) Syntax

나) COLUMN 명령의 OPTION

다) 사용 예

참고

 SQL*Plus 명령이 너무 길다면 현재 라인의 마지막에 하이폰(_)을 두어서 다음 라인에 계속적으로 작성할 수 있습니다. 오라클 서버는 형식 모델에서 제공된 숫자의 자리 수를 초과하게 되면 자리 전체에 파운드 기호(#)의 문자열을 출력합니다. 또한 포맷 모델이 알파벳이지만 실제 값은 숫자인 값의 자리에도 파운드 기호(#)를 출력합니다.

1.3 SQL*Plus 를 이용하여 보고서 작성

 SQL*Plus 명령어를 이용하여 간단한 보고서를 작성할 수 있다.

1.3.1 BREAK 명령

 행 단락을 구분 짓고 중복 값을 제거하기 위해서 VREAK 명령을 사용합니다. BREAK 명령이 효과적으로 수행되기 위해서 BREAK 되는 열에 대해서 ORDER BY 하십시오.

가) Syntax

 page        break 값이 변경될 때 새로운 PAGE로 SKIP

 skip n       break 값이 변경될 때 n 만큼 줄을 SKIP(COLUMN,ROW,PAGE,REPORT)

 duplicate   중복되는 값을 출력

나) 사용 예

다) CLEAR 명령

 CLEAR 명령은 BREAK POINT 설정을 해제한다.

1.3.2 BREAK를 이용한 SELECT 문장 기술

 SELECT 문의 ORDER BY 절로 BREAK를 제어한다.

가) Syntax

나) 사용 예

Guidelines

 1) BREAK 명령을 사용할 때 DATA의 의미 있는 SUBSET를 만들기 위해 BREAK 명령에 참조된 COLUMN을 ORDER BY 절에 기술한다.

 2) BREAK 명령은 마지막에 기술된 오직 하나의 명령어만 유효하다.

1.3.4 COMPUTE 명령

 SQL*Plus 명령어를 이용하여 요약된 계산을 더한다.

가) Syntax

 function              COUNT, NUM, MAX, MIN, SUM, AVG, STD, VAR 중 하나의 함수를 기술

 compute_column  계산에 사용되는 COLUMN이나 식

 break_column       BREAK명령으로 기술된 COLUMN

나) COMPUTE명령 취소

 현재 설정된 COMPUTE 명령을 Clear 한다.

다) 사용 예

1.3.5 TTITLE 과 BTITLE 명령

 머리말과 꼬리말을 출력합니다.

가)Syntax

나) 사용 예

1.3.6 REPORT를 실행 하기 위한 SCRIPT FILE 작성

 SQL 프롬프트에서 각각의 SQL*Plus 명령을 입력하거나 명령(또는 스크립트)파일 SELECT 문장을 포함하는 모든 명령어를 입력할 수 있습니다. 전형적인 스크립트는 최소한 하나의 SQL 문장과 여러 개의 SQL*Plus 명령어들로 구성되어 있습니다.

가) 작성 절차

참고

 SCRIPT 작성 중 SQL*Plus Window를 클릭하여 사용할 수 없다. 반드시 편집중인 Window를 종료 후 SQL*Plus 사용할 수 있다.

문제 1) 아래의 SCRIPT를 분석하여라

문제 2) 아래의 SCRIPT를 분석하여라

1.4 상호작용 리포트

 SQL*Plus를 사용하여 리턴된 자료의 범위를 제한하는데 사용자가 입력하는 값을 이용하기 위해서 사용자에게 PROPMT 라는 리포트를 생성할 수 있다. 상호 작용 리포트를 생성하기 위해 명령어 파일이나 단일 SQL 문장에 치환 변수를 내장할 수 있다.

1.4.1 치환 변수

 1) 값을 임시로 저장하기 위해서 SQL*Plus 치환 변수를 사용합니다.

  ① Single ampersand( & )

  ② Double ampersand( && )

  ③ DEFINE과 ACCEPT 명령어

 2) SQL 문장간에 변수 값을 전달 합니다.

 3) 머리말과 꼬리말을 동적으로 변경합니다.

참고

 SQL*Plus 는 사용자 입력에 대한 타당성 검사를 하지 않는다. 사용자에 대해서 만드는 PROMPT는 단순하고 모호하지 않게 하십시오.

1.4.2 치환 변수 사용할 수 있는 절

 1) WHERE 절

 2) ORDER BY

 3) COLUMN 표현식

 4) 테이블 이름

 5) 전체 SELECT 문장

1.4.3 Single ampersand( & )의 치환 변수

 리포트에서 실행할 때 사용자는 종종 리턴되는 데이터를 동적으로 제한 하기를 원한다. SQL*Plus 는 사용자 변수로써 이러한 융통성을 제공합니다. SQL 에서 각각의 변수를 인식하기 위해서 "&"를 사용합니다. 숫자는 &dept_no 와 같이 인용 부호를 사용하지 않고 사용하고 문자와 날짜에 대해서는 단일 인용 부호('&name')를 사용하면 보다 편리하게 사용할 수 있다.

문제 3) EMP 테이블에서 부서번호를 입력받아 동적 조회할 수 있는 SELECT 문장을 기술하여라.

참고) SET VERIFY ON/OFF 명령어를 이용하여 old와 new의 출력을 조절할 수 있다.

문제4) EMP 테이블에서 이름을 입력받아 동적 조회할 수 있는 SELECT 문장을 기술하여라.

문제 5) EMP 테이블에서 사원번호는 반드시 출력하고 나머지 열은 입력받아 출력하고 조건도 입력받아 출력하여라

1.4.4 Double ampersand( && )의 치환 변수

 매번 사용자에게 입력받지 않고 입력된 변수의 값을 사용하고자 할 경우에 사용한다. 사용자는 오직 한번만 입력하면 된다.

문제 6) EMP 테이블에서 사원번호, 이름, 업무는 반드시 출력하고 나머지 열은 입력받아 출력하고 입력받은 열을 정렬 조건으로 사용한다.

1.4.5 사용자 변수의 정의

 SELECT 문장을 실행하기 전에 사용자 변수를 미리 정의해 사용할 수 있습니다. SQL*Plus는 사용자 변수를 정의하고 설정하기 위해 두개의 명령어를 제공합니다.

가) ACCEPT 명령어

 1) 사용자 입력을 받을 때 사용자가 원하는 프롬프트를 생성합니다.

 2) NUMBER 또는 DATE 데이터형 변수를 명시적으로 정의합니다.

 3) 보안의 이유 때문에 사용자 입력을 숨깁니다.

 variable   값을 저장하는 변수의 이름입니다.

              존재하지 않으면 SQL*Plus가 그것을 생성하여 사용합니다.

 Datatype  NUMBER, CHAR 또는 DATE, CHAR는 최대 길이 제한이 240bytes입니다.

               DATE는 형식 모델을 다시 검사하고 데이터형은 CHAR입니다.

 format     형식 모델을 명시합니다.(예:A10, 9.999)

 text         사용자가 값을 입력하기 전에 값을 출력합니다.

 HIDE        사용자 입력을 숨긴다. (예: 패스워드)

주의

 ACCEPT 명령에서 치환 매개변수를 참조할 때 SQL*Plus 치환 매개변수 앞에 앰퍼샌드(&)를 두어서는 안됩니다.

문제 7)ACCEPT 명령으로 업무를 입력받아 사원번호, 이름, 업무, 급여를 출력하여라

나) DEFINE 과 UNDEFINE 명령어

 1) 변수는 다음의 경우까지 계속 정의 됩니다.

  ① 선언된 변수에 대해 UNDEFINE 명령을 사용

  ② SQL*Plus 종료

 2) DEFINE 명령으로 변수 내용을 검사할 수 있습니다.

 3) 모든 SESSION에 대해 변수를 정의하기 위해서는 login.sql file을 수정하면 변수는 Startup시 생성되어 사용할 수 있습니다.

문제 8) 부서이름(sales) 유지하기 위해 변수(dept_name)를 선언한 다음 이 변수를 이용하여 DEPT 테이블을 검색하여라

1. SUBQUERY

 알려지지 않은 기준에 의한 데이터 검색을 위한 NESTED SUBQUERY 와 데이터 조작 문장에 SUBQUERY 를 사용하는 방법을 배우고 SUBQUERY에 의해 검색된 데이터 정렬에 대해 다루기로 한다.

1.1 SUBQUERY의 개념

 다른 SELECT 문장의 절에 내장된 SELECT 문장 입니다. SUBQUERY는 여러 절에서 사용 가능하며 SELECT 문장 안에 기술된 SELECT 문장이다. NESTED SUBQUERY는 MAIN QUERY 이전에 한번만 수행되며 SUBQUERY의 결과를 MAIN QUERY에 의해 조건으로 사용된다. SUBQUERY를 사용하여 간단한 문장을 강력한 문장으로 만들 수 있고 테이블 자체의 데이터에 의존하는 조건으로 테이블의 행을 검색할 필요가 있을 때 아주 유용하다.

1.2 Syntax

 1) SUBQUERY는 다른 하나의 SQL 문장의 절에 NESTEDED된 SELECT 문장이다.

 2) 알려지지 않은 조건에 근거한 값들을 검색하는 SELECT 문장을 작성하는데 유용하다.

 3) SUBQUERY는 MAIN QUERY 이전에 한 번 실행한다.

 4) SUBQUERY의 결과는 MAIN OUTER QUERY에 의해 사용된다.

Guidelines

 1) SUBQUERY는 괄호로 묶어야 한다.

 2) 두 종류의 비교 연산자들이 SUBQUERY에 사용된다.

 ① 단일 행 연산자 =, >, >= <, <=, <>, !=

 ② 복수 행 연산자 IN, NOT IN, ANY, ALL, EXISTS

 3) SUBQUERY는 연산자의 오른쪽에 나타나야 한다.

 4) SUBQUERY는 많은 SQL 명령에서 사용 가능하다.

 5) SUBQUERY는 ORDER BY절을 포함할 수 없다.

1.3 SUBQUERY를 사용할 수 있는 절

 1) WHERE, HAVING, UPDATE

 2) INSERT 구문의 INT

 3) UPDATE 구문의 SET

 4) SELECT나 DELETE의 FROM 절

1.4 SUBQUERY의 유형

 1) 단일 행 SUBQUERY : SELECT 문장으로부터 오직 하나의 행만을 검색하는 질의입니다.

 2) 다중 행 SUBQUERY : SELECT 문장으로부터 하나 이상의 행을 검색하는 질의 입니다.

 3) 다중 열 SUBQUERY : SELECT 문장으로부터 하나 이상의 열을 검색하는 질의 입니다.

1.5 단일 행 SUBQUERY

 단일 행 SUBQUERY는 내부 SELECT 문장으로부터 하나의 행을 검색하는 질의입니다. 이런 유형의 SUBQUERY는 단일 행 연산자를 사용합니다. 이때 WHERE절에 기술된 열의 개수와 데이터 타입은 SELECT 절에 기술된 열과 좌측부터 1 대 1 대응되며 데이터 타입이 일치해야한다.

문제 ) EMP 테이블에서 SCOTT의 급여보다 많은 사원의 정보를 사원번호, 이름, 담당업무, 급여를 출력하여라.

문제 1) EMP 테이블에서 사원번호가 7521의 업무와 같고 급여가 7934 보다 많은 사원의 정보를 사원번호, 이름, 담당업무, 입사일자, 급여를 출력하여라.

1.5.1) SUBQUERY에서 그룹 함수 사용

 담일 행을 RETURN하는 SUBQUERY에 그룹 함수를 사용하여 MAIN QUERY로부터 데이터를 출력할 수 있다.

문제 2) EMP 테이블에서 급여의 평균보다 적은 사원의 정보를 사원번호, 이름, 담당업무, 급여, 부서번호를 출력하여라.

1.5.2) SUBQUERY 가진 HAVING절

 SUBQUERY를 WHERE 절 뿐만 아니라 HAVING절에서도 사용 가능하다. 오라클 서버는 SUBQUERY를 실행하고 MAIN QUERY의 HAVING절에 RETURN한다.

문제3) EMP 테이블에서 20번 부서의 최소 급여보다 많은 모든 부서를 출력하여라.

문제 4) EMP 테이블에서 업무별로 가장 적은 급여를 출력하여라.

1.6 다중 행 SUBQUERY

 하나 이상의 행을 RETURN 하는 SUBQUERY를 다중 행 SUBQUERY 라고 부릅니다. 다중 행 SUBQUERY는 단일 행 연산자 대신에 다중 행 연산자를 사용합니다. 다중 행 연산자는 하나 이상의 값을 요구합니다.

 위 SELECT 문의 문제점은 SUBQUERY에서 RETURN 되는 ROW가 1개 이상이다. 이런 경우는 다중 행 SUBQUERY 연산자를 이용하여야 한다

1.6.1) 다중 행 SUBQUERY 연산자

 1) IN 연산자

 2) ANY 연산자

 3) ALL 연산자

 4) EXISTS 연산자

1.6.2) IN 연산자

 2개 이상의 값을 RETURN 하는 SUBQUERY에 대하여 비교 연산자(=, !=, <, <=, >, >-)를 기술하면 ERROR가 발생한다. 이런 경우 SUBQUERY 에서 RETURN 된 목록의 각각과 비교하여 QUERY 를 수행하는 연산자가 IN이다.

문제 5) EMP 테이블에서 업무별로 최소 급여를 받는 사원의 정보를 사원번호, 이름, 업무, 입사일자, 급여, 부서번호를 출력하여라

1.6.3) ANY 연산자

 2개 이상의 값을 RETURN하는 SUBQUERY에 대하여는 그런 값들을 어떻게 사용하는가를 지정해 두어야 한다. 비교 연산자(=, !=, <, <=, >, >-)와 SUBQUERY 사이에 ANY 연산자를 기술하여 RETURN된 목록의 각각의 값과 비교한다.

문제 6) EMP 테이블에서 30번 부서의 최소 급여를 받는 사원보다 많은 급여를 받는 사원의 정보를 사원번호, 이름, 업무, 입사일자, 급여, 부서번호를 출력하여라. 단 30번은 제외

1.6.4) ALL 연산자

 2개 이상의 값을 RETURN 하는 SUBQUERY에 대하여는 그런 값들을 어떻게 사용하는가를 지정해 두어야 한다. 비교 연산자(=, !=, <, <=, >, >-)와 SUBQUERY 사이에 ALL 연산자를 기술하여 RETURN 된 목록의 모든 값과 비교한다.

문제 7) EMP 테이블에서 30번 부서의 최고 급여를 받는 사원 보다 많은 급여를 받는 사원의 정보를 사원번호,이름,업무,입사일자,급여,부서번호를 출력하여라. 단 30번은 제외

1.6.5) EXISTS 연산자

 SUBQUERY에서 적어도 1개의 행을 RETURN하면 논리식은 참이고 그렇지 않으면 거짓이다.

문제 8) EMP 테이블에서 적어도 한명의 사원으로부터 보고를받을 수 있는 사원의 정보를 사원번호, 이름, 업무, 입사일자, 급여를 출력하여라, 단 사원번호 순으로 정렬하여라.

1.7 다중 열 SUBQUERY

 SUBQUERY의 구문을 작성할 때 WHERE 절에 비교되는 열이 하나가 아니라 여러 개의 열을 동시에 비교하는 경우가 있다. 이런 경우 다중 열 SUBQUERY 라하여 Pairewise 되었다고 한다.

1.7.1) Syntax

 SUBQUERY로 작성된 구문을 실행하면 의외의 결과가 검색되는 경우가 있을 것이다. 이런 경우는 반드시 다중 열 SUBQUERY를 사용하여 조회하여야 올바른 자료를 검색할 수 있다. 아래의 예를 보고 정확한 개념을 이해하기 바란다.

문제 9) EMP 테이블에서 급여와 보너스가 부서 30에 있는 어떤 사원의 보너스와 급여에 일치하는 사원의 이름, 부서번호, 급여, 보너스를 출력하여라.

중요

 위 결과는 30번 부서에 급여가 1500이고 보너스가 300인 사원이 없는데도 출력되었다. 이는 조건을 각각 별도로 조회할 경우에 발생되는 문제점이다. 이를 해결하기 위해서는 Pairwise SUBQUERY를 이용하여야한다.

문제 10) 업무별로 최소 급여를 받는 사원의 정보를 사원번호, 이름, 업무, 부서번호를 출력하여라. 단 업무별로 정렬하여라.

중요

 위 결과를 사번에 7900인 사원은 업무가 CLERK 이다. CLERK 의 업무의 최소 급여는 800에도 불구하고 출력되었다. 이는 업무별 최소 급여만 RETURN 되지 어느 업무가 어떤 최소값을 가지는지를 알 수 없다.

1.7.2) Pairwise SUBQUERY

 앞의 결과를 보면 알 수 있듯이 어떤 업무의 급여가 최소인지 업무와 최소 급여를 동시에 비교하여야 한다.

문제 11) EMP 테이블에서 급여와 보너스가 부서 30에 있는 어떤 사원의 보너스와 급여에 일치하는 사원의 이름, 부서번호, 급여, 보너스를 출력하여라.

문제 12) 업무별로 최소 급여를 받는 사원의 정보를 사원번호, 이름, 업무, 부서번호를 출력하여라. 단 업무별로 정렬하여라.

1.8 SUBQUERY 에서의 NULL 값

 NULL 값을 비교하는 모든 조건은 NULL이다.

 위 문장의 SELECT 문장은 1명 이상으로부터 보고를 받을 수 있는 사원의 정보를 출력한 것이다. 그러면 말단 직원을 출력할 경우에는 SELECT 문장을 어떻게 기술해야 할까, 일반 사용자가 실수하기 쉬운 부분이다. 우선 IN 대신 NOT IN을 사용하면 쉽게 해결될 것이라 생각한다. 그러나 여기에는 다음과 같은 문제점이 있다. 우선 다음의 SELECT 문장을 보기 바란다.

 위의 SELECT 문장의 결과 SUBQUERY 에서 RETURN 되는 값 중에는 NULL(KING은 MGR이 NULL이다)이 있다. NULL 에 어떠한 연산을 하여도 모든 조건은 NULL 이므로 전체 값이 존재하지 않는다고 RETURN 한다. SUBQUERY의 결과 집합의 일부분으로서 NULL값은 IN 연산자를 사용할 경우는 문제가 되지 않지만 NOT IN 연산자를 사용하면 안된다.

Guidelines

 SUBQUERY 의 결과 집합의 일부분으로서 NULL 값은 IN(= ANY) 연산자를 사용할 수 있다. 그러나 NOT IN(!=ALL)연산자를 사용할 수 없다.

1.9 FROM 절에서의 SUBQUERY

 SUBQUERY는 FROM 절에서도 사용 가능하다. 하나의 테이블에서 자료의 양이 많은 경우 FROM 절에 테이블 전체를 기술하여 사용하면 효율이 떨어질 수 있다. 이런 경우 필요한 행과열만 선택하여 FROM 절에 기술하면 오라클 서버가 최적화 단계에서 효율적인 검색을 할 수 있다. 이처럼 FROM 절에 기술한 SUBQUERY는 마치 VIEW와 같은 역할을 한다. 이런 VIEW를 INLINE VIEW라 한다.

문제 13) EMP과 DEPT 테이블에서 업무가 MANAGER 인 사원의 정보를 이름, 업무, 부서명, 근무지를 출력하여라

1. Join

 하나 이상의 테이블로부터 자료를 검색하기 위하여 조인을 사용합니다. 일반적으로 Primary Key(이후 PK로 사용)와 Foreign Key(이후 FK로 사용)을 사용하여 Join하는 경우가 대부분이지만 때로는 논리적인 값들의 연관으로 Join하는 경우도 있습니다.

1.1 Syntax

Guidelines

 1) WHERE 절에 조인 조건을 기술한다.

 2) 테이블을 조인하는 SELECT 문장을 작성할 경우 명확성을 위하여 또는 데이터베이스의 Performance 향상을 위하여 열 이름 앞에 테이블 명을 붙인다.

 3) 똑 같은 열 이름이 존재하는 테이블이 있을 경우는 반드시 열 이름 앞에 테이블 명을 붙인다.

 4) n개의 ㅌ이블을 조인하려면 최소한 n-1번의 조인 조건 문이 필요하다.

1.2 Join의 종류

1.3 Cartesian Product

 모든 가능한 행들의 Join으로 다음과 같은 경우에 발생한다.

 1) 조인 조건이 생략된 경우

 2) 조인 조건이 잘못된 경우

 3) 첫번째 테이블의 모든 행이 두번째 테이블의 모든 행과 두번째 테이블의 모든 행이 첫번째 테이블의 모든 행과 조인되는 경우,

 4) 양쪽 ROW의 개수를 곱한 결과

참고

 Cartesian Product는 만은 수의 행을 생성하는 경향이 있고 결과도 거의 유용하지 못하다. 그러므로 모든 테이블로부터 모든 행을 조합할 필요가 없을 경우 WHERE 절에 조인 조건을 명확히 기술하여야 한다.

문제 1) EMP테이블과 DEPT 테이블을 Cartesian Product 하여 사원번호, 이름, 업무, 부서번호, 부서명, 근무지를 출력하여라.

1.4 Equijoin

 Equijoin이란 조인 조건에서 "="을 사용하여 값들이 정확하게 일치하는 경우에 사용하는 조인을 말합니다. 대부분 PK와 FK의 관계를 이용하여 조인 합니다. Equijoin은 다른 말로 단순 조인 또는 내부 조인 이라고도 합니다.

1.4.1) Syntax

 table1.column1                       조회할 자료가 있는 테이블과 열 이름을 기술

 table1.column1=table2,column2 두 테이블들간에 논리적으로 연결하는 조인 조건 기술

1.4.2)Equijoin으로 자료 검색

 1) SELECT절은 검색할 열 이름을 명시

 2) FROM절은 데이터베이스가 Access해야 하는 두개의 테이블을 명시

 3) WHERE절은 테이블의 조인 조건을 명시

 4) 양쪽 테이블에 공통으로 존재하는 열 이름은 모호함을 피하기 위하여 열 이름 앞에 테이블명을 기술함

1.4.3) Equijoin의 방법

 종업원의 부서 이름을 결정하기 위해 EMP Table의 DEPTNO와 DEPT Table 의 DEPTNO와 값을 비교하여야 합니다. EMP Table 과 DEPT Table 사이의 관계는 양쪽 테이블의 DEPTNO열이 같아야 합니다. 이들이 PK와 FK로 연결되어 있습니다.

문제 2) EMP 테이블에서 사원번호, 이름, 업무, EMP 테이블의 부서번호, DEPT 테이블의 부서번호, 부서명, 근무지를 출력하여라

1.4.4) Table에 Alias 사용

 1) 테이블 별칭을 사용하여 긴 테이블 명을 간단하게 사용한다.

 2) 테이블 이름 대신에 Alias를 사용한다.

 3) SQL 코드를 적게 사용하여 코딩 시간이 절약되고 메모이를 보다 적게 사용한다.

Guidelines

 1) 테이블 Alias는 30자까지 사용 가능하지만 짧을수록 더 좋다.

 2) FROM절에서 Alias가 사용되면 SELECT문 전체에서 사용 가능하다.

 3) 테이블의 Alias에 가급적 의미를 부여

 4) 테이블은 현재 SELECT문장에서만 유용

1.4.5) AND연산자를 사용하여 추가적인 검색 조건

 조인 이외의 WHERE절에 추가적인 조건을 가질 수 있다.

문제3) SALESMAN 사원만 사원번호, 이름, 급여, 부서명, 근무지를 출력하여라

1.4.6) 두개 이상의 테이블 조인

 때로는 두개 이상의 테이블을 조인 할 경우가 있다.

문제 4) 고객의 TKB SPORT SHOP 의 이름, 주문처, 항목수, 각 항목의 합계, 각 주문의 합계를 출력하여라.

1.5 Non-Equijoin

 EMP와 SALGRADE 사이의 관련성은 EMP 테이블의 어떠한 column 도 직접적으로 SALGRADE테이블의 한 column에 상응하지 않기 때문에 Non-Equijoin이다. 두 테이블 사이의 관련성은 EMP 테이블의 SAL 열이 SALGRADE 테이블의 LOSAL과 HISAL 열 사이에 있다는 것이다. 조인 조건은 등등(=) 이외의 연산자(BETWEEN ~ AND ~ )를 갖는다.

문제 5) EMP 테이블에서 사원번호, 이름, 업무, 급여, 급여의 등급, 하한값, 상한값을 출력하여라.

참고

 위 질의가 실행될 때 한번만 조인되는 것을 알 수 있다. 이에 대한 두가지 이유가 있다.

 1) SALGRADE 테이블에서 중복되는 등급을 포함하는 행이 없다.

 2) EMP 테이블에 있는 SAL의 값은 SALGRADE 테이블에서 제공하는 값 범위에 있다.

Guidelines

 <= 및 >= 같은 다른 연산자를 사용 가능하나 BETWEEN 이 가장 단순하다. 또한 테이블에 Alias를 사용하였는데 이는 모호성 때문이 아니라 성능 때문에 사용하였다. BETWEEN 사용시 하한값을 먼저 명시하고 상한값을 나중에 명시한다는 것을 명심하라.

1.6 Outer Join

 행이 조인 조건을 만족하지 않으면, 행은 질의 결과에 나타나지 않을 것입니다. 예를 들어 EMP와 DEPT 테이블의 equijoin조건에서 부서 OPERATIONS(40번 부서)는 해당 부서에 아무도 없기 때문에 나타나지 않습니다. 이런 경우 모든 행을 전부 출력하고자 할 경우 Outer Join을 사용한다. 즉 정상적으로 조인 조건을 만족하지 못하는 행들을 보기 위해 Outer join을 사용한다.

Guidelines

 1) 행인 조건을 만족하지 않을 시 해당 행은 질의 결과에 나타나지 않는다.

 2) Outer join 연산자를 조인 조건에 사용시 조인 조건을 만족하지 않는 행들도 결과에 나타날 수 있다.

 3) 연산자는 괄호로 묶인 플러스 기호(+)이며 조인 시킬 값이 없는 조인 측에 "(+)"를 위치 시킨다.

 4) (+)연산자는 한 개 이상의 NULL 행을 생성하고 정보가 충분한 테이블의 한 개 이상의 행들이 이런 NULL행에 조인된다.

 5) Outer join 연산자는 표현식의 한 편에만 올 수 있다.

 6) Outer join 을 포함하는 조건은 IN 연산자, OR 연산자를 사용하여 다른 하나의 조건에 연결될 수 없다.

1.6.1) Suntax

 table1.column 테이블을 함께 조인(또는 관련)시키는 조건입니다.

 table2.column (+)는 outer join 기호입니다. WHERE절 조건의 양쪽이 아니라 어느 한쪽에 둘 수 있습니다. 즉 양측 모두에는 올 수 없습니다. 일치하는 행이 없는 테이블의 열 이름 뒤에 outer join연산자를 사용합니다.

1.6.2) Outer Join제약 사항

 1) Outer Join 연산자는 정보가 부재하는 쪽의 표현식 한 쪽에만 둡니다. 다른 테이블의 어떠한 열과도 직접적으로 일치하는 것이 없는 한 테이블의 행을 리턴합니다.

 2) Outer Join을 포함하는 조건은 IN 연산자를 사용할 수 없고, OR 연산자에 의해 다른 조건과 연결될 수 없습니다.

문제 6) EMP 테이블과 DEPT 테이블에서 DEPT 테이블에 있는 모든 자료를 사원번호, 이름, 업무, EMP 테이블의 부서번호, DEPT 테이블의 부서번호, 부서명, 근무지를 출력하여라

1.7 Self Join

 때때로 자체적으로 테이블을 조인할 필요가 있습니다. 각 종업원의 관리자 명을 알기 위해서 자체적으로 EMP 테이블을 조인하는 것이 필요합니다.

 1) ENAME열을 검사하여 EMP 테이블에서 Blake를 검색한다.

 2) MGR열을 검사하여 Blake에 대한 관리자 번호를 검색한다. (Blake 관리자 번호:7839)

 3) ENAME열을 검사하여 EMPNO가 7839인 관리자를 검색한다. 7839는 King 이므로 Blake의 관리자는 King이다.

Guidelines

 1) Self join을 사용하여 한 테이블의 행들을 같은 테이블의 행들과 조인한다.

 2) 같은 테이블에 대해 두 개의 alias 를 작성(테이블 구분) 함으로 FROM 절에 두개의 테이블을 사용하는 것과 같이 한다.

 3) Column에 대해서도 어떤 테이블에서 왔는지 반드시 Alias명을 기술하여야 한다.

 4) 테이블 하나를 두개 또는 그 이상으로 Self join할 수 있다.

문제7) EMP 테이블에서 Self join하여 관리자를 출력하여라.

1.8 Set Operators

 하나 이상의 테이블로부터 자료를 검색하는 또 다른 방법은 SET 연산자를 이용하는 방법이 있다. 즉 SET 연산자를 이용하여 여러 개의 SELECT 문장을 연결하여 작성할 수 있다.

1.8.1) Syntax

Guidelines

 1) 첫번째 SELECT 구문에서 기술된 열과 두번째 SELECT 구문에서 기술된 열들은 좌측부터 1대1 대응하며 그 개수와 타입이 일치해야 한다.

 2) FROM절 뒤에 기술되는 테이블은 같을 수도 있고 다를 수도 있다.

 3) 출력되는 HARDING을 첫번쨰 SELECT 구문에서 기술된 열이 출력된다.

 4) ORDER BY는 단 한번만 기술 가능하고 SELECT 구문의 마지막에 기술한다.

 5) SELECT 문장은 위에서 아래로 수행되고 이를 변경하고자 할 경우는 괄호를 사용한다.

1.8.2) SET 연산자의 종류

1.8.3) UNION과 UNION ALL의 차이

 양쪽에서 검색된 결과를 모두 출력한다. 아래의 두개의 SELECT 문장을 참조하여라.

1.8.4) INTERSECT 연산자

 양쪽에서 검색된 자료만 출력한다. 아래의 SELECT 문장을 참조하여라.

1.8.5) MINUS 연산자

 두번째 SELECT 문장에서 검색되지 않았던 값을 첫번째 SELECT 문장에서 출력한다. 즉 첫번째 SELECT 문장에서 두번째 SELECT 문장에의 값을 뺀것을 출력한다. 아래의 SELECT 문장을 참조하여라.

1. 그룹 함수

 단일 행 함수와는 달리 그룹 함수는 여러 행 또는 테이블 전체에 대해 함수가 적용되어 하나의 결과를 가져오는 함수를 말한다. 그룹 당 하나의 결과가 주어지도록 행의 집합에 대해 연산할 경우 GROUP BY 절을 이용하여 그룹화 할 수 있고 HAVING를 이용하여 그룹에 대한 조건을 제한하는 방법을 배우기로 한다.

1.1 그룹 함수의 종류

Guidelines

 1) DISTINCT는 해당 함수로 하여금 오직 중복되지 않는 값만 RETURN 하게 해준다. 그러나 ALL(Default)은 해당 함수로 하여금 모든 값을 고려하게 한다.

 2) Expr이 있는 인수들의 자료 형태는 CHAR, VARCHAR2, NUMBER, DATE형이 될 수 있다.

 3) COUNT(*)를 제외한 모든 그룹 함수들은 NULL값을 무시한다. NULL 값을 하나의 값으로 치환하기 위해서는 NVL함수를 사용하라

 4) 모든 자료형에 대하여 MAX와 MIN를 사용할 수 있다. 그러나 AVG, SUM, VARIANCE, STDDEV는 NUMBER만 사용 가능하다.

1.2 그룹 함수 사용

1,2,1 Syntax

문제1) EMP 테이블에서 모든 SALESMAN에 대하여 급여의 평균, 최고액, 최저액, 합계를 구하여 출력하여라.

문제2) 아래의 SELECT 문장을 분석하여라.

문제3) EMP 테이블에 등록되어 있는 인원수, 보너스에NULL이 아닌 인원수, 보너스의 평균, 등록되어 있는 부서의 수를 구하여 출력하여라.

1.3 데이터 그룹 생성

 지금까지 모든 그룹 함수는 테이블을 하나의 큰 그룹으로 다루었다. 여기서는 테이블의 자료를 보다 작은 그룹으로 나누어 사용할 수 있는 GROUP BY 절을 배워보자

1.3.1 Syntax

 table_name       테이블명 질의 대상 테이블 이름

 WHERE            condition을 만족하는 행들만 검색

 ORDER BY        질의 결과 정렬을 위한 옵션(ASC:오름차순(Default),DESC내림차순)

 group_function  group Function 이 SELECT 절 뒤에서 Column과 같이 기술되면 반드시 GROUP BY절이

기술되어야 한다.

 GROUP BY group_by_expresstion 행을 그룹하기 위한 기준이 될 Column을 기술

 HAVING condition 그룹에 대한 조건을 기술

1.3.2 SELECT 절

 SELECT 절에 GROUP 함수와 Column 이 같이 기술되면 반드시 GROUP BY 절이 기술되어야 한다. 그러나 SELECT 절에 GROUP 함수만 기술되고 Column은 기술되지 않으면 반드시 GROUP BY절을 기술할 필요는 없다.

문제 4) EMP 테이블에서 부서별로 인원수, 평균, 급여, 최저급여, 최고 급여, 급여의 합을 구하여 출력하여라.

문제 5) 각 부서별로 인원수, 급여의 평균, 최저 급여, 최고 급여, 급여의 합을 구하여 급여의 합이 많은 순으로 출력하여라.

문제 6) 각 부서별로 인원수, 급여의 평균, 최저 급여, 최고 급여, 급여의 합을 구하여 급여의 합이 많은 순으로 출력하여라.

참고

 위 결과는 어느 행이 어떤 부서의 결과인지 알 수 없다. 즉 GROUP BY 절에 기술된 Column은 SELECT 절에 반드시 기술될 필요는 없다. 그러나 결과를 구분하기가 어렵다.

Guidelunes

 1) SELECT 절에 GROUP FUnction 이 포함된다면 GROUP BY절에 각각의 열이 명시되어야 함

 2) WHERE 절을 사용하여 행을 그룹으로 나누기 전에 행을 제외

 3) 그룹에 대한 조건은 HAVING 절을 사용(그룹에 대한 조건을 WHERE절에서 기술 불가)

 4) GROUP BY절에 열을 포함(열의 별칭은 사용할 수 없다)

 5) Default는 GROUP BY절 다음에 기술된 순서로 오름차순으로 정렬되지만 ORDER BY 절을 이용하여 변경 가능

1.3.3 하나 이상의 Column으로 그룹화

 때로는 그룹 내의 그룹에 대한 결과를 검색할 필요가 있다. 이러한 경우 대그룹, 중그룹, 소그룹으로 나누고자 하는 순서대로 GROUP BY 절 뒤에 기술하면 순서대로 오름차순으로 정렬된다.

가) Syntax

 GROUP BY group_by_expr1[, group_by_expr2, . . . .] 행을 그룹하기 위한 기준이 될 Column을 기술한다.'

문제 7) 부서별, 업무별 그룹하여 결과를 부서번호, 업무, 인원수, 급여의 평균, 급여의 합을 구하여 출력하여라.

문제 8) 업무별, 부서별 그룹하여 결과를 부서번호, 업무, 인원수, 급여의 평균, 급여의 합을 구하여 출력하여라.

1.3.4 WHERE 절

 WHERE절을 이용하여 조회하고자 하는 ROW를 선별할 수 있다. 그러나 그룹에 대한 제한 조건은 WHERE절에서 기술할 수 없다. 즉 GROUP FUNCTION는 문법상 WHERE 절에 기술할 수 없다. 그러므로 그룹에 대한 제한 조건은 HAVING 절에서 기술한다.

문제 9) EMP 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 구하여 출력하여라.

1.3.5 HAVING 절

 1) WHERE 절에 GROUP Function을 사용할 수 없다.

 2) HAVING 절을 사용하여 출력될 그룹을 명시

 3) 오라클 서버는 HAVING절을 사용할 때 다음의 단계를 수행

  ① 행을 그룹화

  ② 그룹 함수를 적용

  ③ HAVING절과 일치하는 그룹을 출력

문제 10) EMP 테이블에서 급여가 최대 2900 이상인 부서에 대해서 부서번호, 평균 급여, 급여의 합을 구하여 출력하여라.

문제 11) EMP 테이블에서 업무별 급여의 평균이 3000 이상인 업무에 대해서 업무명, 평균 급여, 급여의 합을 구하여 출력하여라.

문제 12) EMP 테이블에서 전체 월급이 5000을 초과하는 각 업무에 대해서 업무와 월급여 합계를 출력하여라. 단 판매원은 제외하고 월 급여 합계로 정렬(내림차순)하여라.

Guidelines

 1) HAVING절은 GROUP BY절 앞에 기술 가능하지만 GROUP BY절 다음에 기술하는 것이 논리적으로 권장 됩니다. HAVING절이 SELECT 절에 있는 그룹에 적용되기 전에 그룹은 구성되고 그룹 함수는 구성됩니다.

 2) SELECT 절에 그룹 함수를 사용하지 않고 GROUP BY 절만 사용 가능하다. 그룹 함수의 결과로 행이 제한 된다면 HAVING절 뿐만 아니라 GROUP BY절을 사용해야 된다.

1.3.6 중첩 그룹 함수

 그룹 함수는 어떤 LEVEL 까지도 중첩할 수 있습니다.

문제 13) 부서별 평균 중 최대 평균 급여, 부서별 급여의 합 중 최대 급여, 부서별 급여의 최소 급여, 부서별 급여의 최대 급여를 출력하여라.

1. SQL 함수

 제공되는 함수들은 기본적인 Query 문을 더욱 강력하게 해주고 데이터 값을 조작하는데 사용된다. 여러분은 단일 행 함수를 이용하여 문자, 숫자, 날짜 함수에 대해 살펴볼 뿐만 아니라 형을 전환하는 함수들에 대해서도 살펴본다.

1.1 SQL 함수의 특징 및 이점

 1) 데이터에 계산을 수행할 수 있다.

 2) 개별적인 데이터 항목을 수정할 수 있다.

 3) 행의 그룹에 대해 결과를 조작할 수 있다.

 4) 출력을 위한 날짜와 숫자 형식을 조절할 수 있다.

 5) 열의 자료형을 변환할 수 있다.

1.2 단일 행 함수(Single Row Function)

 이 함수는 단일 행에 대해서만 적용 가능하고 행별로 하나의 결과를 RETURN한다..

function_name   함수 명

column             데이터 베이스의 Column Name

expression         어떤 문자 스트링이거나 계산된 표현식

arg1, arg2         함수에 의해 사용될 수 있는 인수

1.2.1 단일 행 함수가 이용되는 곳

 1) 데이터에 대해 계산을 수행할 경우

 2) 각각의 데이터 항목을 변경할 경우

 3) 출력할 날짜 형식을 변경할 경우

 4) Column Data Type 을 변경할 경우

1.2.2 단일 행 함수의 종류

 1) 문자형 함수 : 문자를 입력 받고 문자와 숫자 값 모두를 RETURN 할 수 있다.

 2) 숫자형 함수 : 숫자를 입력 받고 문자를 RETURN 한다.

 3) 날짜형 함수 : 날짜형에 대해 수행하고 숫자를 RETURN 하는 MONTHS_BETWEEN 함수를 제외하고 모두 날짜 데이터형의 값을 RETURN한다.

 4) 변환형 함수 : 어떤 데이터형의 값을 다른 데이터형으로 변환한다.

 5) 일반적인 함수 : NVL, DECODE

1.2.3 단일 행 함수의 특징

 1) 질의에서 RETURN 되는 각각의 행에 대해 수행

 2) 행별로 하나의 결과를 RETURN

 3) 참조 시 사용한 데이터 형과 다른 데이터 형으로 결과를 RETURN 할 수 있다.

 4) 하나 이상의 인수를 필요로 한다.

 5) SELECT, WHERE, ORDER BY 절에서 사용할 수 있습니다.

 6) 함수를 중첩할 수 있습니다.

  ① 단일 행 함수들은 여러 LEVEL에 걸쳐 중첩 사용이 가능하다.

  ② 중첩된 함수들은 가장 하위 LEVEL에서 가장 상위 LEVEL 순으로 진행된다.

1.3 문자형 함수 (Character Function)

1.3.1 LOWER함수

 대소문자가 혼합되어 있거나 대문자인 문자열을 소문자로 변환 합니다.

문제 2) EMP 테이블에서 scott의 정보를 사원번호, 성명, 담당업무, 부서번호를 출력하여라.

1.3.3 INITCAP 함수

 각 단어의 첫번째 문자를대문자로 나머지 문자는 소문자로 변경합니다.

문제 3) DEPT 테이블에서 첫 글자만 대문자로 변환하여 모든 정보를 출력하여라.

1.3.4 CONCAT 함수

 두 개의 문자열을 합성합니다. CONCAT는 두개의 매개변수만 사용 가능합니다.

문제4) 두개의 SELECT 문이 있다. 결과의 차이점을 설명하여라

참고

 Column의 데이터 타입이 varchar2, number, char의 차이로 varchar2와 number는 가변길이, char는 고정 길이입니다.

1.3.5 SUBSTR 함수

 지정된 길이만큼의 문자열을 추출합니다.

문제5) EMP 테이블에서 이름의 첫글자가 K'보다 크고 'Y'보다 적은 사원의 정보를 사원번호, 이름, 업무, 급여, 부서번호를 출력하여라, 단 이름순으로 정렬하여라.

1,3,6 LENGTH 함수

 문자열의 길이를 숫자 값으로 RETURN 한다.

문제 6) EMP 테이블에서 20번 부서 중 이름의 길이 및 급여의 자릿수를 사원번호, 이름, 이름의 자릿수, 급여, 급여의 자릿수를 출력하여라.

1.3.7 INSTER 함수

 명명된 문자의 위치를 숫자 값으로 RETURN한다.

문제 7) EMP 테이블에서 이름 중 'L'자의 위치를 출력하여라.

문제 8) 파일명을 입력을 입력받아 확장자가 없으면 .SQL을 붙여 출력하여라.

1.3.8 LPAD함수

 문자값을 우측부터 채웁니다.

문제 9) 아래 두 문장의 결과를 보고 차이점을 설명하여라.

1.3.9 RPAD 함수

 문자값을 좌측부터 채웁니다.

문제 10) 아래 두 문장의 결과를 보고 차이점을 설명하여라.

1,3,19 LTRIM 함수

 왼쪽 문자를 지우는 함수 입니다.

문제11) EMP 테이블에서 10번 부서에 대하여 담당 업무 중 좌측에 'A'를 삭제하고 급여 중 좌측의 1을 삭제하여 출력하여라.

1.3.11 RTRIM 함수

 오른쪽 문자를 지우는 함수 입니다.

문제 12) EMP 테이블에서 10번 부서에 대하여 담당 업무 중 우측에 'T'를 삭제하고 급여 중 우측의 0을 삭제하여 출력하여라.

1.3.11 TRANSLATE 함수

 특정 문자열을 대체하는 함수 입니다. 즉 str1을 str2 문자로 대체하는 함수이다.

문자 13) EMP 테이블에서 성명을 소문자로 바꾸어 출력하여라

1,3,11 REPLACE 함수

 특정 문자열을 대신하는 함수 입니다.

문제 14) EMP 테이블에서 JOB에 'A'를 '$'로 바꾸어 출력하여라

1.4 숫자형 함수

1.4.1 ROUND 함수

 명시된 소수점으로 반올림하는 함수입니다. 숫자를 n 자리까지 반올림한다. n이 양수이면 소수 자리를, 음수이면 정수 자리를 사사오입합니다. 생략할 수 있으며 Default는 0입니다.

문제 15) 다음의 결과를 분석하여라

참고

 DUAL 테이블은 SYS User 가 Owner 이며 모든 사용자가 사용할 수 있도록 권한을 부여하였다. Dummy 라는 하나의 Column 과 X 값을 가지는 하나의 행을 포함합니다. DUAL 테이블은 오직 하나의 값을 출력하고자 할 때 유용합니다. 예를 들어 데이터를 가진 테이블에서 파생되지 않은 상수, 의사열, 표현식의 값인 경우 입니다. 즉 임의의 값을 알고자 할 경우 유용하게 사용할 수 있다. 위 SELECT 문장에서 dual이 아닌 dept를 사용하면 결과는 어떻게 될까?

1.4.2 TRUNC 함수

 명시된 숫자를 절삭하는 함수입니다. 숫자를 n자리까지 절삭한다. n이 양수이면 소수 자리를, 음수이면 정수 자리를 절삭합니다. 생략할 수 있으며 Default는 0입니다.

문제 16) 다음의 결과를 분석하여라.

1.4.3 MOD 함수

 숫자의 나머지를 구하는 함수입니다.

문제 17) EMP 테이블에서 급여를 30으로 나눈 나머지를 구하여 출력하여라.

1.4.4 POWER 함수

 거듭제곱을 구하는 함수 입니다.

1.4.5 SQRT 함수

 제곱근을 구하는 함수 입니다.

1.4.6 SIGN 함수

 주어진 숫자가 양수인지 음수인지 또는 0인지를 구하는 함수 입니다.

1.4.7 CHR 함수

 ASCII Code 값에 해당하는 문자를 구하는 함수 입니다.

문제 18) EMP 테이블에서 20번 부서 중 이름과 담당 업무를 연결하여 출력하여라.단 담당업무를 한 줄 아래로 출력하여라

1.5 날짜형 함수

1.5.1 오라클 날짜 형식

 1) 오라클은 세기, 년, 월, 일, 시, 분, 초를 내부 숫자(7Byte) 형식으로 날짜를 저장 합니다.

 2) Default Date Type 은 DD-MON-YY(변경 가능)입니다.

 3) 오라클 날짜의 범위는 B.C 4712년 1월 1일부터 A.D 9999년 12월 31일 사이입니다.

 4) SYSDATE 는 오라클이 설치되어 있는 서버의 현재 날짜와 시간을 RETURN 하는 함수 입니다.

1.5.2 날짜 연산

 1) 날짜에서 숫자를 더하기나 빼어 날짜 결과를 출력

 2) 날짜 사이의 일수를 알기 위해서 두개의 날짜를 뺍니다.

 3) 시간을 24로 나누어서 시간을 날짜에 더합니다.

문제 19) EMP 테이블에서 현재까지 근무일 수가 몇주 몇일 인가를 출력하여라. 단 근무 일수가 많은 사람 순으로 출력하여라.

1.5.3 날짜 함수

 날짜 함수는 오라클 날짜에 대해 연산을 합니다. 모든 날짜 함수는 숫자값을 RETURN하는데 MONTHS_BETWEEN을 제외하고는 DATE형을 RETURN합니다.

1.5.4 MONTHS_BETWEEN 함수

 1) 날짜와 날짜 사이의 월수를 계산합니다.

 2) 결과는 음수 또는 양수가 될 수 있습니다.

 3) 결과의 비정수 부분을 월의 부분을 나타냅니다.

위 예에서 212는 월을 나타내고 .04794는 월의 일부분을 나타냅니다.

문제 20) EMP 테이블에서 10번 부서 중 현재까지의 근무 월수를 계산하여 출력하여라.

1.5.5 ADD_MONTHS 함수

 1) 날짜에 월을 더합니다(ADD_MONTHS(hiredate, 10))

 2) 날짜에 월을 뺍니다(ADD_MONTHS(hiredate,-10))

 3) 결과의 날짜형입니다.

문제 21) EMP 테이블에서 10번 부서 중 입사 일자로부터 5개월이 지난 후 날짜를 계산하여 출력하여라.

1.5.6 NEXT_DAY 함수

 1) 명시된 요일의 돌아오는 날짜를 계산 합니다.

 2) 요일이 아니라 숫자도 기술 가능(SUNDAY:1, MONDAY:2, , , , , ,)

 3) NLS_LANG이 KOREAN_KOREA.K016KSC5601로 되어 있으면 한글도 사용 가능(일요일, 월요일, 화요일, , , , , ,)

문제 22) EMP 테이블에서 10번 부서 중 입사 일자로부터 돌아오는 금요일을 계산하여 출력하여라

1.5.7 LAST_DAY 함수

 1) 월의 마지막 날짜를 계산

 2) 윤년, 평년은 자동 계산

문제 23) EMP 테이블에서 입사한 달의 근무 일수를 계산하여 출력하여라. 단 토요일과 일요일도 근무 일수에 포함한다.

1.5.8 ROUND 함수

 1) 명시된 형식으로 반올림 합니다.

2) 날짜를 가장 가까운 년도 또는 월로 반올림할 수 있습니다.

  ① fmt에 명시된 단위에 대해 반올림한 날짜를 계산

  ② fmt가 생략되면 날짜를 가장 가까운 날짜로 반올림한다.

1.5.9 TRUNC 함수

 1) 명시된 형식으로 절삭 합니다.

 2) 날짜를 가장 가까운 년도 또는 월로 절삭할 수 있습니다.

 ① fmt에 명시된 단위에 대해 절삭한 날짜를 계산

 ② fmt가 생략되면 날짜를 가장 가까운 날짜로 절삭한다.

문제24) EMP 테이블에서 10번 부서 중 입사한 달의 ROUND과 TRUNC 함수를 비교합니다.

1.6 변환 함수

1.6.1 데이터의 형 변환

 오라클 서버는 어떤 일정한 데이터형의 데이터를 사용해야 하는 곳에, 그것과 다른 데이터형의 데이터를 사용할 수 있게 합니다. 이것은 오라클 서버가 자동적으로 데이터형을 변환할 수 있을 때 허용됩니다. 이 데이터형 변환은 오라클 서버에 의해서 암시적으로 행해지거나 또는 사용자에 의해서 명시적으로 행해질 수 있습니다.

가) 암시적인 데이터형 변환

 값 할당(assignment)시, 오라클 서버는 다음을 자동으로 변환할 수 있습니다.

참고

 오라클 서버가 값 할당(assignment) 문장에서 사용된 값의 데이터형을 목표(target)값의 데이터형으로 변환할 수 있을 경우에 할당(assignment) 문장은 올바로 수행됩니다. 또한 CHAR가 NUMBER로의 변환은 문자열이 적절한 숫자로 나타낼수 있을 경우에만 가능하고 CHAR 가 DATE로의 변환은 문자열이 Default Date Type와 같을 경우에만 성공합니다.

Guidelines

 비록 암시적 데이터형 변환을 이용할 수 있더라도, SQL 문장의 안정성을 위해서 명시적 데이터형 변환을 할 것을 권장합니다.

나) 명시적인 데이터형 변환

 SQL은 변환 함수를 통하여 어떤 데이터형의 값을 다른 데이터형의 값으로 변환하기 위하여 아래의 함수를 제공 합니다.

1.6.2 TO_CHAR 함수

 숫자, 날짜, 문자열을 지정한 형식의 VARCHAR2 문자열로 변환하는 함수입니다.

1) 날짜 형식을 변환

가) 특정 형식으로 날짜를 출력

 이전의 모든 날짜 형식은 DD-MON-YY 형식이었다. TO_CHAR 함수는 이러한 형식의 날짜를 명시한 날짜 형식으로 변환하여 출력할 수 있다.

Guidelines

 1) 포맷(fmt) 모델은 단일 인용 부호로 둘러 싸여 있어야 하고 대소문자를 구분한다.

 2) 포맷(fmt) 모델은 어떤 타당한 날짜 형식도 포함 가능하다.

 3) 추가된 공백을 제거하거나 앞부분의 0을 없애기 위해서 "fm"요소를 사용한다.

 4) SQL*Plus COLUMN 명령어로 문자 필드 결과의 출력 폭의 크기를 조절할 수 있다. (DEFAULT는 80)

나) 날짜 형식 모델

다) 시간 형식

 1) 시간 요소는 날짜의 시간 부분을 형식화(HH24:MI:SS AM → 15:34:32 PM)

 2) 문자열에 이중 인용 부호를 사용하여 문자열을 추가(DD "of" MONTH → 10 of OCTOBER)

 3) 숫자 접미사는 숫자를 문자로 변환(ddspth → four teenth) 

 4) 시간 형식의 종류

라) 기타 형식

마) 숫자에 영향을 주는 접미사

문제25) EMP테이블에서 10번 부서 중 입사 일자를 '1 May 1981'와 '1998년 1월 1일'의 형태로 출력하여라

2) 숫자 형식을 변환

 TO_CHAR함수를 사용하여 숫자 값을 문자로 출력하기 위해 사용한다.

가) 숫자를 가진 TO_CHAR 함수

 1) 숫자 값을 문자로 변환할 때 즉 NUMBER형을 VARCHAR2로 전환할 때

 2) 이 기법은 연결(Concatenation) 시에 유용

Guidelines

 1) 형식에 의해 제공되는 자릿수를 초과하는 숫자에 대해서는 "#"을 출력

 2) 지정된 소수 값을 형식에서 제공하는 소수점 자리로 반올림 한다.

나) 숫자 형식 모델

문제26) EMP 테이블에서 부서 20중 급여 앞에 $를 삽입하고 3자리마다 ,를 출력하여라

1.6.3 TO_NUMBER 함수

 숫자를 포함하는 문자열을 숫자로 변환 합니다.

1.6.4 TO_DATE 함수

 날짜를 나타내는 문자열을 명시된 날짜로 변환 합니다.

문제27) February 22, 1981에 입사한 사원의 정보를 이름, 업무, 입사일자를 출력하여라.

1.7 기타 함수

1.7.1 DECODE 함수

 CASE 나 IF-THEN-ELSE-END IF 문장의 조건적 조회를 가능하게 함

문제 28) EMP 테이블에서 JOB이 ANALYST이면 급여 증가는 10%이고 JOB이 CLERK이면 급여 증가는 15%이고 JOB이 MANAGER이면 급여 증가는 20%입니다. 다른 업무에 대해서는 급여증가가 없습니다. 사원번호, 이름, 업무, 급여, 증가된 급여를 출력하여라.

1.8 중첩 함수

 1) 단일행 함수는 여러 LEVEl에 걸쳐 중첩 가능

 2) 중첩 함수는 가장 하위 LEVEL에서 상위 LEVEL 순으로 진행

문제 28) 다음의 결과를 분석하여 보아라.

1. 아래의 SELECT 문장이 성공적으로 수행 될까요? (참/거짓)

답 : 참

2. 아래의 SELECT 문장이 성공적으로 수행 될까요? (참/거짓)

답 : 참

3.이 문장에 에러가 있습니다. 올바르게 작성하시오.

SELECT empno, ename, sal*12 "년 봉" 로 고치면 된다.

4. EMP 테이블의 구조와 내용을 조회하여라.

SELECT *

  FROM emp;

5. EMP 테이블에서 중복되지 않는 부서번호를 출력하시오

SELECT DISTINCT

          deptno

 FROM emp;

6. EMP 테이블의 이름과 업무를 연결하여 출력하여라

SELECT ename || ' ' || job AS "Employees"

  FROM emp;

7. DEPT 테이블의 부서명과 위치를 연결하여 출력하여라

SELECT dname || ' ' || loc AS "dept"

  FROM emp;

8. EMP 테이블의 업무와 급여를 연결하여 출력하여라.

SELECT job || ' ' || sal AS "ex eight"

 FROM emp;

1. WHERE절에 HIREDATE의 비교를 '01-JAN-82'가 아닌 '01-jan-82'로 기술하면 결과는?

ORA-01858: 숫자가 있어야 하는 위치에서 숫자가 아닌 문자가 발견되었습니다.
01858. 00000 -  "a non-numeric character was found where a numeric was expected"
*Cause:    The input data to be converted using a date format model was
           incorrect.  The input data did not contain a number where a number was
           required by the format model.
*Action:   Fix the input data or the date format model to make sure the
           elements match in number and type.  Then retry the operation.

2. EMP 데이블에서 급여가 3000이상인 사원의 정보를 사원번호, 이름, 담당업무 급여를 출력하는 SELECT 문장을 작성하시오.

3. EMP 테이블에서 사원번호가 7788인 사원의 이름과 부서번호를 출력하는 SELECT 문장을 작성하시오.

SELECT ename,empno
  FROM emp
 WHERE empno=7788;

4. EMP 테이블에서 입사일이 February 20, 1981과 May 1, 1981 사이에 입사한 사원의 이름, 업무, 입사일을 출력하는 SELECT 문장을 작성하시오. 단 입사일 순으로 출력하시오.

SELECT ename,job,hiredate
  FROM emp
 WHERE hiredate BETWEEN '81/02/01' AND '81-05-01'
 ORDER BY hiredate;

5. EMP 테이블에서 부서번호가 10, 20인 사원의 모든 정보를 출력하는 SELECT 문장을 작성하시오. 단 이름순으로 정렬하여라.

SELECT *
  FROM emp
 WHERE deptno IN (10,20);

6. EMP 테이블에서 급여가 1500이상이고 부서번호가 10,30인 사원의 이름과 급여를 출력하는 SELECT 문장을 작성하여라, 단 HEADING을 Employee과 Monthly Salary로 출력하여라.

SELECT

 ename Employee
  ,sal "Monthly Salary"
  FROM emp
 WHERE sal >=1500 AND deptno IN (10,30)

7. EMP 테이블에서 1982년에 입사한 사원의 모든 정보를 출력하는 SELECT 문을 작성하여라.

8.EMP 테이블에서 COMM에 NULL이 아닌 사원의 모든 정보를 출력하는 SELECT 문을 작성하여라.

SELECT *
  FROM emp
 WHERE COMM is NOT NULL;

9. EMP 테이블에서 보너스가 급여보다 10%가 많은 모든 종업원에 대하여 이름, 급여, 보너스

SELECT ename,sal,comm
  FROM emp
 WHERE comm > sal*1.1;

10. EMP 테이블에서 업무가 Clerk이거나 Analyst이고 급여가 1000,3000,5000이 아닌 모든 사원의 정보를 출력하는 SELECT 문을 작성하여라.

11. EMP 테이블에서 이름에 L이 두 자가 있고 부서가 30이거나 또는 관리자가 7782인 사원의 모든 정보를 출력하는 SELECT 문을 작성하여라. 

SELECT *
  FROM emp
 WHERE ename LIKE '%LL%' OR DEPTNO IN (30) AND empno IN (7782);

1. 현재 날짜를 출력하고 열 레이블은 Current Date로 출력하는 SELECT 문장을 기술하시오.

2. EMP 테이블에서 현재 급여에 15%가 증가된 급여를 사원번호, 이름, 업무, 급여, 증가된 급여(New Salary), 증가액(Increase)를 출력하는 SELECT 문장을 기술하시오.

SELECT empno,ename,job,sal,sal*1.15 "New Salary",sal*0.15 "Increase"
  FROM emp;

3. EMP 테이블에서 이름, 입사일, 입사일로부터 6개월 후 돌아오는 월요일을 구하여 출력하는 SELECT 문장을 기술하시오.

SELECT ename,hiredate,NEXT_DAY(ADD_MONTHS(hiredate,6),'월요일')
  FROM emp;

4. EMP 테이블에서 이름, 입사일, 입사일로부터 현재까지의 월수, 급여, 입사일로부터 현재까지의 급여의 총계를 출력하는 SELECT 문장을 기술하시오.

5. EMP 테이블에서 다음의 결과가 출력되도록 작성하시오.

SELECT ename
      ,TO_CHAR(sal,'$99,999.00') "earns"
      ,TO_CHAR(sal*3,'$99,999.00') "wants"
  FROM emp;

SELECT
      RPAD(ename, 20, ' ') || ' earns ' || TO_CHAR(sal,'$99,999.00')
      || ' monthly but wants ' || TO_CHAR(sal*3,'$99,999.00')
  FROM emp;

6. EMP 테이블에서 모든 사원의 이름과 급여(15자리로 출력 좌측의 빈곳은 "*"로 대치)를 출력하는 SELECT 문장을 기술하시오.

SELECT LPAD(ename,15,'*') "이름"
      ,LPAD(sal,15,'*') "급여"
  FROM emp;

7. EMP 테이블에서 모든 사원의 정보를 이름, 업무, 입사일, 입사한 요일을 출력하는 SELECT 문장을 기술하시오.

SELECT ename
      ,job
      ,hiredate "입사일"
      ,TO_CHAR(hiredate,'DAY') "입사한 요일"
  FROM emp;
     

8. EMP 테이블에서 이름의 길이가 6자 이상인 사원의 정보를 이름, 이름의 글자수, 업무를 출력하는 SELECT 문장을 기술하시오.

9. EMP 테이블에서 모든 사원의 정보를 이름, 업무, 급여, 보너스, 급여+보너스를 출력하는 SELECT 문장을 기술하시오.

SELECT ename,job,sal,comm
      ,sal+NVL(comm,0) "급여+보너스"
  FROM emp;

SELECT ename
      ,job
      ,NVL(sal,0) "급여"
      ,NVL(comm,0) "보너스"
      ,sal+NVL(comm,0) "급여+보너스"
  FROM emp;

1. EMP 테이블에서 인원수, 최대 급여, 최소 급여, 급여의 합을 계산하여 출력하는 SELECT 문장을 작성하여라.

SELECT COUNT(*)   -- 가급적이면 *보다는 인원수를 지칭하는 empno를 사용하는 것을 추천 이유는 *는 모든 것을
         ,MAX(sal)        계산하기 떄문임.
         ,MIN(sal)
         ,TO_CHAR(SUM(sal),'$999,999.00')

 FROM emp;

2. EMP 테이블에서 각 업무별로 최대 급여, 최소 급여, 급여의 합을 출력하는 SELECT 문장을 작성하여라.

SELECT MAX(sal)
         ,MIN(sal)
         ,SUM(sal)
 FROM emp
GROUP BY job;

3. EMP 테이블에서 업무별 인원수를 구하여 출력하는 SELECT 문장을 작성하여라.

SELECT COUNT(*)
  FROM emp
 GROUP BY job;

SELECT job "업무"
      ,COUNT(empno) "인원수"
  FROM emp
 GROUP BY job;

4. EMP 테이블에서 최고 급여와 최소 급여의 차이는 얼마인가 출력하는 SELECT 문장을 작성하여라.

SELECT MAX(sal) - MIN(sal)
  FROM emp;

5. EMP 테이블에서 아래의 결과를 출력하는 SELECT 문장을 작성하여라.

6. EMP 테이블에서 아래의 결과를 출력하는 SELECT 문장을 작성하여라.

7. EMP 테이블에서 아래의 결과를 출력하는 SELECT 문장을 작성하여라.

1. EMP 테이블에서 모든 사원에 대한 이름, 부서번호, 부서명을 출력하는 SELECT 문장을 작성하여라

SELECT ename "이름"
      ,a.deptno "부서번호"
      ,b.dname "부서명"
  FROM emp a
      ,dept b
 WHERE a.deptno=b.deptno
      ;

2. EMP 테이블에서 NEW YORK에서 근무하고 있는 사원에 대하여이름, 업무, 급여, 부서명을 출력하는 SELECT 문장을 작성하여라

3. EMP 테이블에서 보너스를 받는 사원에 대하여 이름, 부서명, 위치를 출력하는 SELECT 문장을 작성하여라

SELECT ename "이름"
      ,b.dname "부서명"
      ,b.loc "위치"
  FROM emp a
      ,dept b
 WHERE a.deptno=b.deptno
   AND (a.comm>0 OR a.comm IS NOT NULL)

      ;

4. EMP 테이블에서 이름 중 L자가 있는 사원에 대하여 이름, 업무, 부서명, 위치를 출력하는 SELECT 문장을 작성하여라.

5. 아래의 결과를 출력하는 SELECT 문장을 작성하여라. (관리자가 없는 King을 포함하여 모든 사원을 출력)

SELECT a.ename "Employee"
      ,a.empno "EMP#"
      ,b.ename "Manager"
      ,a.mgr "Mgr#"
  FROM emp a
      ,emp b
  WHERE a.mgr=b.empno(+)
  ORDER BY b.mgr DESC
      ;

6. EMP 테이블에서 그들의 관리자 보다 먼저 입사한 사원에 대하여 이름, 입사일, 관리자 이름, 관리자 입사일을 출력하는 SELECT 문장을 작성하여라.

SELECT a.ename "이름"
      ,a.hiredate "입사일"
      ,b.ename "관리자 이름"
      ,b.hiredate "관리자 입사일"
  FROM emp a
      ,emp b
 WHERE a.mgr=b.empno
   AND a.hiredate < b.hiredate
      ;

7. EMP 테이블에서 사원의 급여와 사원의 급여 양만큼 "*"를 출력하는 SELECT 문장을 작성하여라. 단 "*"는 100을 의미한다.

SELECT ename
      ,star
      ,lpad(' ', star+1,'*') --R/LPAD(기본값, 자리수, 자리수를 채울값)
  FROM emp a,
      (SELECT empno
      ,TRUNC(sal/100) star
       FROM emp
      ) b
 WHERE a.empno=b.empno
      ;

1. EMP 테이블에서 이름, 부서번호, 부서명을 출력한다. 단, 최저 급여인 사원정보를 출력

SELECT ename "이름"
      ,deptno "부서번호"
      ,(SELECT dname FROM dept WHERE deptno=a.deptno) "부서명"
  FROM emp a
 WHERE sal=(SELECT MIN(sal)
              FROM emp)
      ;

2. EMP 테이블에서 업무별 최고 급여인 사원의 이름, 업무, 급여를 출력한다.

SELECT ename "이름"
      ,job "업무"
      ,sal "급여"
  FROM emp
 WHERE sal IN (SELECT MAX(sal)
                FROM emp
                GROUP BY deptno)
      ;
     

3. EMP 테이블에서 부서별 가장먼저 입사한 사원의 이름, 부서명, 입사일을 출력한다.

4. EMP 테이블에서 입사년도가 가장늦은(최근) 사원의 사번은 어느 부서인가?

SELECT a.ename "이름"
      ,a.empno "사번"
      ,b.dname "부서명"
      ,a.hiredate "입사일"
  FROM emp a
      ,dept b
 WHERE hiredate IN (SELECT MAX(hiredate)
                      FROM emp)
   AND a.deptno=b.deptno  
      ;

5. EMP 테이블에서 부서별 사원의 이름, 부서명, 입사일을 출력하되 위치가 'NEW YORK'인 사람만 출력한다.

SELECT a.ename "이름"
      ,b.dname "부서명" --(SELECT dname FROM dept WHERE deptno=a.deptno) "부서명"
      ,a.hiredate "입사일"
      ,b.loc "위치" --(SELECT loc FROM dept WHERE deptno=a.deptno) "위치"
      ,a.deptno
  FROM emp a
      ,dept b
 WHERE b.dname IN (SELECT dname
                      FROM emp
                     GROUP BY dname)
   AND b.loc='NEW YORK'
   AND a.deptno=b.deptno
      ;

SELECT a.ename
      ,a.dname
      ,a.hiredate
  FROM(SELECT b.deptno
             ,b.ename
             ,c.dname
             ,b.hiredate
         FROM emp b
             ,dept c
        WHERE b.deptno=c.deptno
          AND c.loc='NEW YORK'
      ) a
 ORDER BY a.deptno
      ;

1. EMP 테이블에 있는 모든 CONSTRAINT를 조회하는 SELECT 문을 작성하여라.

2. EMP 테이블에 SAL, COMM을 제외한 모든 COLUMN과 행을 포함하는 EMP_DEMO 테이블을 생성하는 SQL 문을 작성하여라.

CREATE TABLE EMP_DEMO
    AS SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, DEPTNO
  FROM emp
      ;

3. EMP 테이블과 DEPT 테이블을 이용하여 아래의 내용을 포함하는 테이블(EMP_DEPT)을 생성하여라.

CREATE TABLE EMP_DEPT
    AS SELECT a.EMPNO, a.ENAME, a.JOB, b.DNAME, b.LOC
  FROM emp a
      ,dept b
 WHERE a.deptno=b.deptno
      ;
SELECT * FROM EMP_DEPT;

4. EMP 테이블과 SALGRADE 테이블을 이용하여 아래의 내용을 포함하는 테이블 (EMP_GRADE)을 생성하여라.

5. DEPT 테이블의 PRIMARY KEY를 DISABLE하는 SQL 문을 작성하여라.
ALTER TABLE dept
DISABLE CONSTRAINT dept_primary_key CASCADE;

6. 3번에서 생성한 테이블에 EMPNO를 PRIMARY KEY로 설정하는 SQL문을 작성하여라.

7. 4번에서 생성한 테이블에 SAL의 정밀도를 정수 부분을 12자리 소수 이하 4자리를 기억할 수 있도록 변경하는 SQL문을 작성하여라.
desc emp_grade;
ALTER TABLE emp_grade
MODIFY sal number(16,4);

8. 3번에서 작성한 테이블의 내용과 기억 장소를 모두 해제하는 SQL문을 작성하여라.
TRUNCATE TABLE EMP_DEPT;

9. 3번과 4번 생성한 테이블을 모두 삭제하는 SQL문을 작성하여라.
DROP TABLE EMP_DEPT;
DROP TABLE EMP_GRADE;

1.아래의 구조를 만족하는 MY_DATA 테이블을 생성하시오. 단 ID가 PRIMARY KEY이다.

2. 1번에 의해 생성된 테이블에 아래의 값을 입력하여라.

3. 2번에서 입력한 자료를 확인 하여라.

SELECT ID
      ,name
      ,userid
      ,TO_CHAR(salary,'999,999.00') "salary"
  FROM my_data;

4. 2번에서 입력한 자료를 모두 삭제하고 insert하기 위한 script를 작성하여 대화식으로 입력하여라. 단 내용은 2번과 동일하다.
TRUNCATE TABLE MY_DATA;
INSERT INTO my_date VALUES(&id,'&name','&userid',&salary);

5. 4번에서 바꾼 자료를 영구적으로 데이터베이스에 등록하여라.

6. ID가 3번인 사람의 급여를 65,000.00으로 생신하고 영구적으로 데이터베이스에 반영하여라.

7. 이름이 Ford인 사원을 영구 제명하여라.

DELETE FROM my_data
 WHERE name='Ford';
commit;

8. 급여가 15,000이하인 사람의 급여를 15,000로 변경하여라.

9. 1번에서 생성한 테이블을 삭제하여라.

DROP TABLE my_data;

1. 초기값1부터 최대값 999,999까지 1씩 증가하는 TEST_SEQ SEQUENCE를 생성하여라.

2. 현재 SESSION을 이루고 있는 사용자가 사용할 수 있는 SEQUENCE를 조회하여라.

SELECT *

 FROM USER_SEQUENCES

         ;

3.1번에서 작성한 SEQUENCE의 현재 값을 조회하여라.

4. CURRVAL과 NEXTVAL을 설명하여라.

SELECT TEST_SEQ_SEQUENCE.NEXTval
  FROM DUAL
      ;

5. CACHE와 NOCACHE의 차이점을 설명하여라.

얼마나 많은 값이 메모리에 오라클 서버가 미리 할당하고 유지하는가를 지정.

6. CYCLE와 NOCYCLE의 차이점을 설명하여라.

최대 또는 최소값에 도달한 후에 계속 값을 생성할 지의 여부를 지정

7. 1번에서 생성한 SEQUENCE를 삭제하여라.
DROP SEQUENCE TEST_SEQ_SEQUENCE;

1. EMP테이블에서 사원 번호, 이름, 업무,부서를 포함하는 EMP_VIEW VIEW를 생성하여라.

2. 1번에서 생성한 VIEW를 이용하여 10번 부서의 자료만 조회하여라.

3. 번에서 생성한 VIEW를 DATA DICTIONARY에서 조회하여라.

desc EMP_VIEW
    ;

4. EMP 테이블과 DEPT 테이블을 이용하여 이름, 업무, 급여, 부서명, 위치를 포함하는 EMP_DEPT_NAME이라는 VIEW를 생성하여라.

5. VIEW생성시 WITH READ ONLY OPTION에 대하여 설명하여라.

이 VIEW에서 DML이 수행될 수 없게 한다.

6. VIEW생성시 WITH CHECK OPTION에 대하여 설명하여라.

VIEW에 의해 액세스 될 수 있는 행만이 입력, 갱신될 수 있다.

7. VIEW를 이용하여 자료를 수정할 수 있는 경우와 없는 경우에 대하여 설명하여라.

 1)단순 VIEW에서 DML 연산을 수행할 수 있습니다.

 2) VIEW가 다음을 포함 한다면 행을 제거할 수 없습니다.

  ① 그룹 함수  ② GROUP By절  ③ DISTINCT 키워드

 3) 다음을 포함한다면 VIEW에서 데이터를 수정할 수 없습니다.

  ① 그룹 함수  ② GROUP BY 절  ③ DISTINCT 키워드  ④ 표현식으로 정의된 열  ⑤ ROWNUM 의사열

 4) 다음을 포함한다면 VIEW에서 데이터를 추가할 수 없습니다.

  ① 그룹 함수  ② GROUP BY절  ③ DISTINCT 키워드  ④ 표현식으로 정의된 열  ⑤ ROWNUM의사열

  ⑥ VIEW에 의해 선택되지 않은 NOT NULL열이 기본 테이블에 있을 경우

1.EMP 테이블에서 이름을 가지고 INDEX(emp_ename_indx)를 생성하여라

CREATE INDEX emp_ename_indx
          ON emp(ename)
             ;

2. INDEX의 장단점을 기술하여라.

 장점 : 검색속도를 향상시킨다.

- 인덱스는 테이블의 값을 빠르게 액세스 하도록 하는 데이터베이스 객체이다.

- 데이터를 빠르게 찾기 위한 B*TREE을 써서 디스크 입출력 횟수를 줄인다.

- Oracle8 Server가 인덱스를 자동적으로 사용하고 유지 보수 한다.

- 인덱스를 만들면 사용자가 직접 조작할 필요가 없게 된다.

- 인덱스는 논리적으로도 물리적으로도 테이블과는 독립적이다.

- 언제든지 생성하거나 삭제할 수 있으며 이는 테이블이나 다른 인덱스에 영향을 주지 않는다는 의미이다

3. 테이블 생성시 자동적으로 생성되는 INDEX가 있다. 어떤 제약 조건을 기술하면 생성되는가?

PRIMARY KEY,      UNIQUE KEY

4. INDEX 생성 지침을 설명하여라.

가) 많은 것이 항상 더 좋은 것은 아니다.

 테이블의 많은 인덱스가 질의의 스피드 향상을 꼭 의미하는 것은 아닙니다. 인덱스를 가지고 있는 테이블에 대한 각 DML 작업은 인덱스도 갱신되어야 함을 의미합니다. 많은 인덱스가 테이블과 관련되어 있으며, ORACLE SERVER은 DML 후에 모든 인덱스를 갱신하기 위해 더 많은 노력이 필요하게 됩니다.

나) 언제 인덱스를 생성하는가?

 1) 열은 WHERE 절 또는 조인 조건에서 자주 사용됩니다.

 2) 열은 광범위한 값을 포함합니다.

 3) 열은 많은 수의 null 값을 포함합니다.

 4) 둘 또는 이상의 열은 WHERE 절 또는 조인 조건에서 자주 함께 사용됩니다.

 5) 테이블은 대형이고 대부분의 질의들은 행의 2~4%보다 적게 읽어 들일 것으로 예상됩니다.

다) 언제 인덱스를 생성해서는 안되는가

 1) 테이블이 작다.

 2) 열의 질의의 조건으로 자주 사용되지 않는다.

 3) 대부분의 질의들은 행의 2~4%이상을 읽어 들일 것으로 예상된다.

 4) 테이블은 자주 갱신됩니다. 테이블에 하나 이상 인덱스를 가지고 있다면 테이블을 엑세스하는 DML 문장은 인덱스의 유지 때문에 상대적으로 더 많은 시간이 걸리게 됩니다.

5. 여러 개의 COLUMN으로 인덱스를 사용하는 경우가 있다. 몇개의 COLUMN까지 가능한다.

많이 할 수 있으나 안함

6. 동의어는 어떤 경우에 사용하는가.

7. EMP 테이블을 EMPLOYEE라는 동의어를 생성하여라

1. Oracle8에 로그온하기 위한 필요한 권한은 무엇인가?

2. 테이블을 생성하기 위해 필요한 권한은 무엇인가

3. SYSTEM PRIVILEGE와 OBJECT PRIVILEGE란?

4. 테이블을 생성한 OWNER는 어떠한 권한을 가지는가?

5. 사용자가 비밀 번호를 갱신하려면 어떤 문장을 기술하여야 하는가?

6. 사용자가 액세스할 수 있는 테이블을 조회하여라?

7. CONNECT ROLE와 RESOURCE ROLE에 대하여 설명하여라

8. 현재 SESSION을 이루고 있는 사용자가 가지고 있는 OBJECT권한을 조회하여라.

9. 사용자는 KSH이고 패스워드는 KIM인 사용자를 생성하여라.
 
10. 9번에서 생성된 사용자에게 CONNECT와 RESOURCE권한을 부여하여라.
 
11. 10번에서 부여한 권한을 취소하고 KSH사용자를 삭제하여라.