[데이터 베이스] ch7.Complex Queries, Triggers, Views, and Schema Modification

pjhcsol 2023. 10. 17. 19:22

select+ [컬럼 또는 표현식] + from + [테이블 또는 뷰] ;

select:select 절을 글자 그대로 해석해보면, 테이블로 부터 컬럼을 선택한다 이다.

from:

where

컬럼 (필드와 유사하다) : 유사한 데이터 집합을 대표하여 부르는 용어, 예를 들어 우리반 학생들의 '이름', '키', '몸무게' 등이 기록된 데이터가 있다면, 여기서 '이름', '키', '몸무게' 등의 데이터들은 각각 이름 컬럼, 키 컬럼, 몸무게 컬럼에 속하는 데이터가 되는것이다.

테이블 : 데이터 집합. 컬럼의 집합이라고 해도 될것 같다. 위 예를 그대로 사용해 보면 테이블은 우리반 학생들의 데이터 정도가 될것이다.

테이블에 존재하는 모든 칼럼들을 조회하기 위해서는

select * from [테이블명] ;

테이블에서 원하는 칼럼만 조회하기 위해서는

select [조회하기를 원하는 칼럼명] from [테이블명] ;

db7장 요약

More Complex SQL Retrieval Queries

추가 기능을 통해 사용자는 데이터베이스에서 보다 복잡하고 흥미로운 검색을 지정할 수 있습니다.

Additional feature allow users to specify more complex and interesting retrievals from database.
• Such features:

Nested queries (중첩 질의) -> 외부 질의 + 내부 질의. (views를 사용하여 query 단순화 가능)
Joined tables (Natural Join)
Outer joins in the FROM clause
Views (Derived Tables), Assertions (oracle 지원 X), Triggers
Aggregate functions (집계 함수) -> Grouping이 필요
Grouping

이 장에서는 SQL의 개념과 사용 방법에 대해 중점적으로 알아봅니다

Nested queries 중첩질의 (select 안의 where 절에서 자주응용)

1.SQL에서는 Three-valued logic 을 사용함 (NULL 처리를 위해 ->t,f,unknown(null))

null 은 null 로 정의 불가능

ex)if 문은 two-value logic 이다 반환값이 t,f 임

NULL을 처리하기 위해 사용,TRUE, FALSE and UNKNOWN(NULL), 은 계산할 수 없다.,ISNULL로 판단,null 은 테이블 만들때 비워두면 기본값으로 사용됨 ->null이 아닌것은 not null로 정의

truth table 존재 ->

논리logical in three valued logic (*and ,+or, not)유의사항 (unknown *true = unknown, unknown +true = true) , (unknown *False = False, unknown +False = unknown)

2. NULL 검사 (IS NULL)attribute value가 null인지 쿼리를 checking 함 ex) select~From~Where super_ssn IS NULL; ->감독관이 없는 모든 직원의 쿼리 찾음NULL인 tuple을 listing 함만약 null이 아닌지 확인을 할려면 ->IS NOT NULL을 사용따라서 select 에서 attribute가 null인지 CHECK 할려면 IS NULL을 사용하여 NULL 여부 CHECK

3. IN을 통한 외부쿼리+내부쿼리 구조로 Nested Queries 중첩질의(SELECT 두번) ->page 7 select distinct 가 머야??외부쿼리(outer query)는 완전한 내부쿼리(inner quert == subquery)를 가진다ex)Where pnumber IN ->외부쿼리, in 뒤의 결과 집합과의 evaluate (select~Where) -> 내부쿼리1OR ->로 둘중 한개 가능?Pnumber IN ->내부쿼리 2(Select~where)번외) view 를 사용하여 query단순화 가능

4. Set과 multiset 비교연산자 : INV IN V ->true로 평가함->v가 v의 원소중 한개 올때 값의 tuple을 비교할때 사용가능tuple 체크용

5. ALL (Nested Queries)

(Single value: v 단일값) 비교를 위한 ALL(중복제거를 하지않고 모든 값이랑 비교)

ex ) Where v>ALL (select ~~서브쿼리) 서브쿼리의 모든 값:v( 여러개 각각 비교)을 단일값:v가 초과해야한다요약: single value:v 에 대해 nested query (중첩쿼리)로 얻은 모든 value 와 비교

6. Aliases (Nested Queries) :다른 table의 tuple들과 값이 일치하는지(교차검증) (IN, = 등 사용함)

->outer query tuple과 inner query tuple 이 서로 연관 ->join

잠재적 오류와 모호성을 피하기 위해 IN으로 중복쿼리 비교(연결) 후 =(join)으로 join:다른테이블의 tuple끼리 비교함중첩쿼리의 서로다른 table의 tuple 끼리 매칭시켜 잠재적 오류와 모호성을 피함 potential error and ambiguities== correlated nested query라고 불림 상호연관된 중첩질의

p10

7. Nested Queries->single query

= 또는 in 을 사용하여 중첩된 쿼리(6번)를 join 조건으로 하나의 쿼리로 다시쓰기가능

join query로 rewritting 가능

IN,= -> aliases(두테이블의 tuple 교차검증(중첩쿼리))-> IN,=을 사용하는 중첩쿼리를 join 조건을 사용하는 single query로 변경가능(join query)

8. (not)exists function in SQL for Correlating Queries

Exist function == Boolean function ->T,F값만 반환

중첩쿼리의 result 가 not empty인지 empty인지 판별(check)

EXISTS : 결과 생성이 가능하면 TRUE, 그렇지 않으면 FALSE
NOT EXISTS : 결과 생성이 가능하면 FALSE, 그렇지 않으면 TRUE

9. 이중부정을 위한 not exist

~하지않은 튜플들은 존재하지않음== ~ 한튜플들만 존재함

하지않은not 하지않음 not(not not)

universal quantifier effect 위해서 SQL에서는 이중부정을 사용

10. explicit sets (명시적 집합) in sql. (where에서 IN)

명시적 값의 집합을 where 절에서 사용가능

WHERE Pno IN (1,2,3); == WHERE (Pno = 1) or (Pno = 2) or (Pno = 3);

IN: Where에서 집합으로 축소가능

11. renaming (attribute name 재정의) in sql (select에서 AS로 rename)

Select a As b == a를 b로 renaming

AS : attribute name 변경가능

Join table (select 안의 from 절에서 사용)

~~매칭되는 tuple 만 join 결과 나옴->비교결과~~

Joined Tables (-join) : from 절에서 사용 ex)FROM (EMPLOYEE JOIN DEPARTMENT ON Do = Dnumber)

Concepts: 사용자는 query의 FROM 절에서 join operation을 사용하여 얻은 table을 사용할 수 있다.

Inner join

single joined table을 포함하는데, 이러한 join은 inner join이라고도 하고, 이는 tuple matching에만 사용된다.

different types of join ->join 의 다른 타입들 (from 절)

from 절에서 join 만 쓰면 natural join 뒤에 on 이라는 조건이 붙으면 inner join(not natural join)

1. natural join (from 절 에서 사용):일치되는 이름이 같다라는 가정하에 자연스럽게 두개가 연합됨(INNER JOIN)

가장 대표적인 inner join의 subset

(테이블명과 일치되는 colum 명이 있다면 join 함)

(left table) (right table), join condition은 명시되지 않는다

두 table에서 같은 attribute name을 갖는 것에 대해 implicit EQUIJOIN condition을 생성하는 것과 동일

??? NATURAL JOIN을 사용하여 다른 관계를 조인할 수 있도록 한 관계의 attributes 이름을 변경해야 합니다.

CREATE TABLE DEPT AS : query의 결과를 table화하겠다는 의미
Dnumber as Dno : Natural join을 위한 renaming

2. INNER JOIN

joined table에서의 default type
다른 relation에 matching tuple이 존재하는 경우에만 tuple이 result에 포함된다.
- non-matching tuples을 확인하고 싶은 경우는 ?

3. LEFT (RIGHT) OUTER JOIN ()

left(right) table에 있는 모든 tuples는 무조건 result에 포함된다.

왼쪽 튜플은 전부 보여주고 오른쪽 튜플은 왼쪽에 맞춰서 정렬되고 없는값은 null로 처리

matching tuple이 없는 경우, right(left) table의 attributes의 values에 NULL 값을 삽입

EMPLOYEE E 와 EMPLOYEE S 의 LEFT OUTER JOIN이므로, EMPLOYEE E의 tuples 중 join에 참여하지 않는 tuples도 result에 보여진다.

4. FULL OUTER JOIN

LEFT & RIGHT OUTER JOIN의 result 결합

왼쪽 오른쪽 테이블의 튜플 둘다 전부 보여주고 없는 값은 null처리

5. Multiway JOIN (in the FROM clause(FROM 절)) join ~table on ~

"multiway" join은 JOIN specificaitons를 nesting함으로써 기술할 수 있다.

ex) from ((project join Department on Dnum=Dnumber) JoIN Employee on mgr_ssn=ssn

Aggregate Functions 집계연산(group by 절로 grouping, select,having 절 사용가능)

multiple tuples로부터 요약한 정보(통계값, 요약 정보)들을 single tuple에 group 단위로 가져오고 싶을 때 사용

일반적으로 GROUP BY 절로 grouping을 수행

Aggregate functions(집계연산)는 SELECT 절이나 HAVING 절에 사용될 수 있다.

group 내에서 구분(조건 적용을)위해 HAVING 절 사용

Built-in aggregate functions:

: NULL이 포함된 tuple은 count하지 않는다.

: Round() 함수가 없으면 반올림해주지 않는다.

Entire Tuples 전체작업에 Aggregations Applied

group by: 1개의 tuple 만듬 multiple tuples로부터 요약한 정보(통계값, 요약 정보)행만듬

중요 포인트: COUNT 는 NULL 값에 대한 counting을 하지 않기에 마지막 예시에서의 결과값이 다르게 나온다.

1. grouping (group by 절)(grouping attributes는 무조건 SELECT 절에 명시되어야 한다.)

tuples의 하위 집합으로의 Partition relation

-grouping attributes를 기반으로, 같은 값을 가진 것을 선택

-각 group에 독립적으로 function 적용

group by 절 (GROUP BY Clause)

grouping attributes를 명시

ex)Dno 값이 같은 tuple들로 grouping 수행 ( Dno가 select 절에 포함되어야 함)

GROUP BY 는 JOIN의 result에도 적용할 수 있다.

GROUP BY 사용 시, attributes의 기술 순서 중요
ORDER BY의 default는 오름차순

2. grouping (having 절(GROUP BY Clause with))

전체 group을 select하거나 reject하는 조건을 제공:전체그룹을 선택할지말지(join의 결과에서 최소몇개를 포함하고있는 것을 추출할때 having)
HAVING 절의 위치는 GROUP BY 뒤 (+ ORDER BY는 제일 마지막)

ex)각부서에서 각 2명이상일때 부서본호와 직원수 표시

3. grouping :GROUP BY 절과 함께 롤업(rollup), 큐브(cube) 및 TOP-N 절

어떤 관점으로 축약

cube : 다차원 어느방향으로 보는지에 따라 결과가 달라짐

rollup: 중간 결과를 다볼수있음->다차원 분석가능

ex)

SELECT DNUMBER, DNAME, e.sex, COUNT (*) as dept_emp_cnt
FROM DEPARTMENT, EMPLOYEE e WHERE DNUMBER=DNO
AND DNO IN (SELECT DNO
            FROM EMPLOYEE
            GROUP BY DNO
            HAVING AVG (SALARY) < 40000)
GROUP BY ROLLUP (DNUMBER, DNAME, e.sex)

DNUMBER, DNAME, e.sex 다볼수있음

대표적인 OLAP(On-line Analytic Processing) 작업:
롤업(rollup), 큐브(cube) 및 TOP-N 절

rollup 절: 중간 결과를 다볼수있음->다차원 분석가능

cube 절: 모든 combination 결합에서 최종으로 올라감

차원 그룹의 "모든 가능한 조합"에 대한 부분 합계를 계산한다.

top-n 절

ask for the n latgest or smallest values of a column.

4.WITH Clause view (in line view)

특정 query에서 사용될 임시의 table을 사용자가 정의하는데 사용한다.

편의성을 위해 임시 "Views"를 만들고, query에서 즉시 사용하도록 한다. 그리고 이를 in-line view라고 한다.

위에서 작성한 query는 WITH 절을 사용해 아래와 같이 재작성할 수 있다.

5. CASE Clause (조건마다 값이다름)

특정 조건마다 value가 다른 경우에 사용한다.
value가 존재할 것으로 예상되는 SQL query의 어느 부분에서든 사용 가능하다.
tuple에 대한 querying, inserting, or updating에 적용 가능하다.

6. Recursive Queries in SQL

동일한 table에 대해 반복적인 연산을 수행

Ex) EMPLOYEE의 Super_ssn (FK)가 이러한 relationship을 가진다.

result tuples가 계속 population된다.

START WITH : reculsive query를 시작할 attribute value
CONNECT BY PRIOR : 시작 tuple에서부터 해당 조건을 만족하는 tuple을 탐색

VIEWS(VIRTUAL TABLES) IN SQL

1. views

SQL에서 view의 Concepts

다른 tables로부터 파생된 single table 따라서 "필수적으로 채워지지 않는" virtual table로 간주된다.

view는 자주 참조해야 하는 table을 지정하는 방법

ex) 자주 요청되는 joins의 result를 caching할 목적으로 사용,join cost가 space보다 더욱 비용이 들때

join cost와 space의 tradeoff

Retrieve:검색

ex)해당 query가 호출될 때마다 EMPLOYEE, WORKS_ON & PROJECT를 매번 join하는 비용이 크다.

이러한 joins에 대한 view를 정의했다면, 이후로는 single-table에 대한 retrieval로 간주된다.

2.CREATE VIEW(특정 뷰보기 명령어)

view의 name, attribute names & view definition을 정의하는 query

존재하는 Table의 attribute를 가져오기 때문에, data types도 기존의 table attribute의 data types와 동일

CREATE VIEW A AS. A: view name
SELECT ~ FROM ~ WHERE : view definition

CREATE VIEW DEPT_INFO (Dept_name, No_of_emps, Total_sal) AS : 새로운 attribute name 지정

View가 한 번 정의되면, SQL queries는 FROM 절에서 view relation을 사용할 수 있다.
DROP VIEW 전까지 사용 가능 (뷰삭제)

3. view의 정의 장점

특정 queries를 기술하는 데 용이,보안과 권한 부여 메커니즘의 제공,Ex) 특정 부서의 사람이 다른 부서의 정보를 볼 수 없음

,구현 시, 공간(join에 참여하는 table 수?)에 따른 join cost를 절감할 수 있음

4.View Implementation

View는 항상 up-to-date되어야 한다.

view가 정의한 base relation의 tuples를 수정하면, 해당 수정사항이 view에도 자동적으로 반영되어야 한다.

일종의 Cache이기 때문에 이전의 tuples 정보를 사용할 수도 있다.

view는 정의될 때가 아닌, view에 대한 query로 참조할 때 실제 결과를 저장한다.(실시간 반영)

DBMS는 view를 up-to-date해야하는 책임(view를 사용자가 최신화x)

DBMS가 view에게 up-to-date를 지시하는가 ?querying를 위한 view를 효율적으로 구현하는 것은 쉽지 않다

Strategy 1) Query modification approach 쿼리입장에서 view 접근 수정법

필요할 때 뷰를 계산,뷰를 영구적으로 저장하지 맙시다,view query를 기본 table에 대한 query로 수정

problem :Join과 같은 효과,Join의 cost가 높은 경우, 비효율적이다.("복잡한" 쿼리를 통해 정의된 뷰를 실행하는 데 시간이 오래 걸리거나 비효율적임)

Strategy 2) View materialization approach 뷰 구체화 접근

view query가 처음 호출되었을 때, 일시적인 view를 물리적으로 생성
view에 대한 또다른 queries가 곧 호출될 거라는 가정 하에, 해당 view table을 유지
base tables가 update되었을 때, 자동적으로 view table을 update하는 효율적인 전략을 필요로 한다.
Incremental update : 새로운 tuple의 결과만 update(query의 영향을 받는 부분에 대해서만 update)
물리적으로 구현된 table은 query가 계속되는 한 유지된다.
일정 기간동안 query가 없다면, 자동적으로 table을 삭제

5. Multiple ways to implement materialization 구체화 구현을 위한 다양한 방법

view update의 시점에 따라 구분

Immediate update (c.f., "write-through"):base tables가 변경되자마자 바로 view update

Lazy update (c.f., "write-back"):view query가 요청될 때 view update

Periodic update:주기적으로 view update

View Update

INSERT/DELETE/UPDATE command로 view table을 수정하는 것은 불가능

여러 가지 의미로 해석될 수 있기에 reject됨, view table은 read-only!
aggregate function 없이 single table을 기반으로 정의된 view에 대한 update는 base table에 대한 update로 변환될 수 있다.->view 집계함수없이 하나의 테이블 == 의존관계가 한개 밖에 없을때 --> 뷰한개일때 view 업데이트 허용 이를 제외하고 view를 통한 테이블 update 불가

Views as Authorization Mechanism 권한 부여 메커니즘view

위 query로 Dno = 5 인 EMPLOYEE table의 정보만 생성하고, Dno = 5인 Employees에게만 해당 view에 대한 접근권한을 부여함으로써, EMPLOYEE table에 대한 접근을 방지할 수 있다.

SCHEMA CHANGE STATEMENTS 스키마 상태 변경문

tables/views, attributes, constraints, & other schema constructs를 더하거나 제거함으로써, schema를 수정하는데 사용된다.
하지만, 전체 database schema를 recompile하는 것이 아니라 변경된 부분에 대해서만 전체 database의 일관성을 무너뜨리지 않도록 recompile 수행

1.drop command

tables, domains, or constraints와 같은 schema elements를 제거하는데 사용된다.
DROP options: CASCADE & RESTRICT
Ex1) DROP SCHEMA COMPANY CASCADE;
- CASCADE: schema를 제거하고, 그 안의 모든 elements(tables, views, constraints, 등) 또한 제거
- RESTRICT: schema 내에 아무 element도 들어있지 않은 경우에만 제거
Ex2) DROP TABLE DEPENDENT CASCADE;
- CASCADE : relation(table)을 제거하고 catalog로부터 해당 relation(table)의 definition 또한 제거
- RESTRICT : 어떠한 참조도 존재하지 않는 경우에만 제거

2. ALTER TABLE Command

포함하는 actions:

column(attribute) 추가 또는 제거 Alter table ~ add/drop ~;
column definition 변경
table constraints 추가 또는 제거
CASCADE : 모든 제약조건과 column(Address)를 참조하는 views 제거
RESTRICT : column을 참조하는 어떠한 views/constraints가 없는 경우 제거

Default Values:Oracle에서는 지원되지 않는 SET DEFAULT

on delete [set default, set null, cascade]
on update[set null]

현재 오라클에서는 on delete cascade만 지원

제약 조건을 트리거로서 동작으로 지정(SPECIFYING CONSTRAINTS AS ASSERTIONS AND ACTIONS AS TRIGGERS)

1.CREATE ASSERTION(create ASSERTION문 ~)

declarative assertions을 통해 일반적인 제약조건을 사용자가 기술할 수 있도록 한다.
- 해당 제약조건은 key, (or unique), entity, not-null, referential integrity constraints에 포함되지 않는다.
개별 attributes와 domains에 적용되는 CHECK 절로는 기술되지 않는 경우에만 사용한다.
Oracle에서는 지원하지 않는다.

2.Triggers<-ASSERTION이 포함됨

유효성검증도구

특정 이벤트가 발생하거나, 특정 조건이 만족되는 경우에 취해야 할 action의 type을 기술하기 위해 사용한다.
- Ex) “If an employee exceeds a travel expense limit, notify his manager.”
일반적인 trigger는 3가지 components를 가진다:
- Event(s), Condition, Action (ECA)

트리거 명령어

BEFORE (사전) / AFTER (사후)
NEW : 새로 들어오는 값 / OLD : 기존의 값
Oracle에서는 다른 형태로 사용됨

CREATE TRIGGER SALARY_VIOLATION
BEFORE INSERT OR UPDATE ON EMPLOYEE
FOR EACH ROW
BEGIN
IF :new.Salary > 100000 THEN
DBMS_OUTPUT.PUT_LINE('Old Salary: || :old.Salary);
DBMS_OUTPUT.PUT_LINE('New Salary: [| :new.Salary);
DBMS_OUTPUT.PUT_LINE('Salary difference: ' || (:new.Salary - :old.Salary));
END IF;
END;