복잡한 Query, Triggers, Views, Schema Modification Complex Queries, Triggers, Views, and Schema Modification (데이터베이스)

본 장에서 알아볼 항목은 아래와 같다.

1. 복잡한 SQL Queries

2. Views in SQL

3. Schema Modification in SQL

4. Triggers와 Constraints As Assertions

1. 복잡한 SQ Queries

• Nested Queries (중첩 질의)

• Joined Tables (일반적인 Join)

• Outer joins in the FROM clause

• Views (derived Tables), Assertions, Triggers

• Aggregate functions (집계 함수)

• Grouping

삼치논리 : SQL은 3개의 논리 상태를 가진다.

각 연산에 대한 값은 위와 같다.

또한 NULL에 대해선 IS 를 통해 NULL 여부를 판단한다.

Nested Queries (중첩 질의)

2중 for문에 비유해서 생각해보자

이때 바깥쪽에 위치한 For을 Outer Query라고 하고, 안에 위치한 Query를 Inner Query라고 한다.

Where 절 안에 IN에 의해서 묶여진다.

Pnumber가 Inner Query1 이나 Inner Query2 둘 중 하나라도 만족한다면 추출한다.

위와 같이 복수 개의 Multiset에 대해서도 IN을 적용할 수 있다.

ALL : SELECT문을 만족하는 모든 요소들 -> MAX 와 비슷한 의미를 지닌다.

Dno이 5인 모든 Employee의 Salary보다 높은 Salary를 가진 Employee의 Lname과 Fname을 추출

(NOT)EXISTS : 상호 연관된 중첩 질의가 'Not Empty' 혹은 'Empty'인지 판별하는 함수.

중첩 질의에서 No Tuple을 Return 하면 EXISTS는 FALSE, NOT EXISTS는 TRUE를 Return 한다.

5번 부서에 의해서 관리되는 프로젝트에 모두 참가하는 EMPLOYEE를 추출하시오

WHERE 절

• 첫 번째 SELECT 문 -> 5번 부서가 관리하는 전체 PROJECT의 Pnumber를 return

→ {1,2,3}

• 두 번째 SELECT 문 -> EMPLOYEE가 참여하는 PROJECT의 Pno를 return

→ {1,2}

• {1,2,3} MINUS {1,2} = {3}

• NOT EXISTS {3} => 3이 존재함으로 FALSE

• 어떠한 tuple도 선택되지 않는다.

이는 아래와 같이도 표현이 된다.

NOT EXISTS 절

• 첫번째 NOT EXISTS
  - Poject.Dnum = 5 인 Project의 Pnum이 SELECT문에서 추출
  - WORKS_ON의 Pno이 위 Pnum인 모든 요소를 추출

• 두번째 NOT EXISTS
  - WORKS_ON과 EPLOYEE join

IN에는 특정 값을 넣어줄 수 도 있다.

FROM절에 JOIN과 ON을 통해서 직접 join을 해줄 수 있다.

OUTER JOIN : ON문에 성립되지 않는 값은 NULL로 비워놓고 출력에 포함 시키는 것.

위 예시는 LEFT OUTER JOIN이므로, LEFT의 E 테이블의 모든 값을 출력하고

성립되지못한 Borg의 우측 값은 NULL로 나타낸 것이다.

FULL OUTER JOIN : LEFT와 RIGHT OUTER를 합친 것.

사진에서 보듯이 왼쪽과 오른쪽 모두 공백(NULL)이 존재함을 확인할 수 있다.

Aggregate Functions (집계 함수)

여러 튜플의 정보를 그룹별로 하나의 튜플을 생성하기 위한 함수이다

COUNT, SUM, MAX, MIN, AVG ...

GROUP BY 절을 사용해서 엮는다.

HAVING 은 Condition 설정에 쓰인다.

GROUP BY절

GROUP BY

Dno를 기준으로 묶인 것을 확인할 수 있다.

GROUPING 되는 속성은 SELECT에 필수적으로 포함되어 있어야 한다.

HAVING 절

HAVING

HAVING은 그룹의 Condition을 설정한다.

사원수가 2명 이상인 Departmentem들에 대해서 Salary가 40000보다 큰 근로자의 수는?

WITH 절

특정 질의 안에서만 자주 사용될 질의를 임시 VIEW로 만드는 것

CASE

CASE는 특정 상황으로 분류될때 활용한다.

2. VIEWS In SQL

Schema 와 Catalog 개념 in SQL

VIEWS 란?

한 테이블에 의해서 유도되는 Table

물리적으로 존재하지 않는 가상의 Table이다.

자주 참조될 Table을 임의로 지정하는 것

VIEWS 의 생성과 예시

VIEWS 생성시 Attribute 이름을 설정

생성된 VIEWS를 사용

생성된 VIEWS는 FROM절에서 호출하여 사용이 가능하다.

VIEWS의 쓰임

특정 질의를 간략화한다

시간과 Cost의 절약

보안성 제공

VIEWS의 삭제

VIEWS의 갱신

VIEWS는 UP-TO-DATE 해야한다.

즉, VIEW가 정의된 relation에 변화가 생기면 자동적으로 변화를 적용해야한다.

DBMS는 VIEW가 UP-TO-DATE 하는 것에 책임이 있다.

VIEW의 UPDATE

INSERT/DELETE/UPDATE command로 VIEW TABLE 수정하는 것은 불가하다

여러 가지 의미로 해석될 수 있기에 Reject 됨.

VIEW TABLE은 Only READ만.

3. SCHEMA modification in SQL

DROP command

Table, domain, constraint 같은 Schema element 제거에 이용됨

CASCADE, RESTRICT와 함꼐 사용

1) DROP SCHEMA COMPANY CASCADE;

CASCADE : Schema와 그 안의 모든 Elements 함께 제거

RESTRICT : Schema 내에 아무 Elements 도 들어있지 않은 경우에만 제거

2) DROP TABLE DEPENDENT CASCADE;

CASCADE : Relation(Table)을 제거하고 해당 Relation(table)의 Definition 또한 제거

RESTRICT : 어떠한 참조도 존재하지 않는 경우만 제거

The ALTER TABLE Command

포함하는 actions

column(attribute) 추가 또는 제거

column definition 변경

table constraints 추가 또는 제거

CASCADE : 모든 제약조건과 참조하는 VIEWS를 제거

RESTRICT : Column을 참조하는 어떠한 VIEWS/Constraints가 없는 경우 제거

4. Triggers와 Constraints As Assertion

Assertion : 주장하다.

CREATE ASSERTION

유저로 하여금 Declarative Assertions(선언 주장)으로 일반적인 제약조건을 기술할 수 있게 한다.

유저에 의해 설정된 특정 Condition을 위반하는 Tuple을 찾아내는 Query를 명시합니다.

Triggers (트리거)

특정 사건이 발생했거나, 특정 Condition(조건)이 충족 되었을때,

Type of Action을 수행하도록 한다.

TRIGGER를 설정하는 문구이다.

EMPLOYEE절에 INSERT나 UPDATE가 있기 전 (Before)

:NEW - 바인드 변수, 새롭게 들어오는 튜플의 샐러리가 크다면 일단 에러메세지를 띄우는 Trigger이다.

하지만, 위 Query는 Oracle에서 수행되지 않음으로 아래와 같이 바꿔준다.

CREATE OR REPLACE TRIGGER SALARY_VIOLATION
   BEFORE INSERT OR UPDATE ON EMPLOYEE
   FOR EACH ROW
   BEGIN
        IF :new.salary > 80000 THEN
        DBMS_OUTPUT.PUT_LINE('no');
        END IF;
    END;

BEGIN - 액션

IF - Condition 판별

END IF;

보완해야할 내용 - NOT EXISTS 부분