[데이터베이스] 6. More SQL: Complex queries, triggers, views, and schema modification (chapter 7)

Reference - Fundamentals of Database Systems 7th edition

 

 

More complex SQL Retrieval Queries

 

복잡한 형태의 조회 - Nested queries (중첩 질의), joined tables, outer joins

 

복잡한 형태의 조회를 알아보기 전에 NULL에 대해 더 자세히 알아보겠다.

NULL에는 3가지 종류가 있다. -> 알려지지 않은 값, 이용할 수 없는 값, 적용할 수 없는 값이다.

NULL 값이 있을 때 comparison은 AND의 경우 TRUE and NULL -> NULL, FALSE and NULL -> FALSE다.

OR의 경우 TRUE or NULL -> TRUE, FALSE or NULL -> NULL이다.

IS NULL / IS NOT NULL로 condition을 확인한다.

 

- Nested queries

Query 안에 block이 하나 더 있는 것이다. 보통 IN 연산자에서 자주 사용한다.

WHERE Pnumber IN ( SELECT ~ ) OR Pnumber IN ( SELECT ~ )의 형태로 사용한다.

이때 밖을 감싸는 query를 outer query, IN 안에 들어있는 query를 subquery라고 한다.

=ANY와 =SOME는 같은 연산자다.

이때 ambiguities를 피하기 위해 Outer join에서 사용한 별칭을 통해 condition을 사용할 수 있다. 물론 테이블명을 명시해도 된다.

 

correlated query의 경우 nested query에서 일부 attriibute를 subquery 안에서 참조하는 경우를 말하는데, 조건을 잘 바꿔서 nested query가 아닌 단일 block으로 쓸 수도 있다.

Correlated nested query

 

단일 block으로 변환

 

EXISTS command는 값이 존재하느냐 그렇지 않냐를 확인, WHERE 뒤에 사용한다.

UNIQUE command는 값이 DISTINCT한지 아닌지를 확인한다.

 

- Joined table

두 table을 연결시키는 것. Foreign key를 통해 연결하며, FK=PK를 명시해주어야 한다.

SELECT * FROM (EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber)와 같은 형태로 사용된다.

이를 INNER JOIN(=그냥 JOIN)이라 한다.

NATURAL JOIN의 경우 FK와 PK의 이름이 동일한 경우를 말한다. 하지만 모호성때문에 잘 쓰지않는 것이 좋다.

OUTER JOIN의 경우 INNER JOIN과 달리 둘 중 하나만 해당하는 attribute가 있어도 없는 테이블의 해당 attribute를 NULL로 채워서 JOIN 시켜준다.

 

- Aggregate Functions (집계 함수)

COUNT, SUM, MAX, MIN, AVG를 통해 집계 연산이 가능하다.

이를 위한 Grouping을 잘해야하는데, "WHERE _ GROUP BY _ HAVING 집계 함수;" 와 같은 형태를  통해 Grouping할 수 있다. 이때 중간에 NULL이 있으면 COUNT를 제외한 연산이 온전히 나오지 못하므로, WHERE IS NOT NULL을 통해 NULL을 제외시킬 수 있다. 이해를 돕기위한 예제를 살펴보겠다.

 

Q1. Salary가 40000불 이상이며 부서가 5명 이상인 사원의 수를 구하라.

Incorrect A.

WHERE 절이 먼저 수행돼서 사원을 셀 수 없음.

 

위처럼 Query를 하면 WHERE 절이 먼저 수행되기 때문에, Dno를 셀 때 이미 40000불 이상인 사원들로 추려진 상태이기에 '5명 이상인 부서'라는 조건을 온전히 만족시키지 못한다.

따라서 아래와 같이 nested query로 바꿔서 수행해야 한다.

Correct A.

이 경우 Dno가 5명 이상인 경우를 SELECT한 뒤 그 중에 Salary가 40000명 이상인 경우를 찾는다.

 

- CASE

CASE 문을 통해 Update에 조건을 달 수 있다.

UPDATE EMPLOYEE SET Salary = CASE WHEN Dno=5 THEN Salary+2000 과 같이 사용할 수 있다. 

 

- Semantic Constraints

: 응용프로그램에서 검사할 수 있지만, DB 안에서 ASSERTION과 TRIGGER를 통해 제약조건을 걸 수 있다.

ASSERTION의 경우 "CREATE ASSERTION _ CHECK (condition)"처럼 사용하고,

TRIGGER의 경우 event, condition, action의 단계로 이루어져 있다.

"CREATE TRIGGER _ BEFORE(혹은 AFTER) INSERT OR UPDATE _

FOR EACH ROW WHEN (condition) CALL(=자체 function) something" 의 형태로 사용할 수 있다.

 

- View

가상 테이블인 View 또한 table과 비슷하게 생성한다.

CREATE VIEW _ 의 형태로 사용한다. View는 동적으로 업데이트되는 값이 표기된다는 장점이 있어서 집계 등에 활용할 수 있다. View를 지울 땐 DROP VIEW _의 형태로 사용한다.

 

- Schema

DBA에 의해 constraint 혹은 table schema 등을 변경할 수 있다. 이때 SQL은 다른 프로그래밍 언어처럼 다시 compile할 필요없이 바로 적용된다. DROP SCHEMA COMPANY CASCADE(혹은 RESTRICT) 의 형태로 사용한다.

CASCADE = 데이터 유무와 상관없이 모두 지우겠다. RESTRICT = 안에 데이터가 없으면 지우겠다.

 

ALTER는 변경할 때 사용한다. table 안에서 column을 추가, 변경, 삭제할 때 사용한다.

ALTER TABLE COMPANY.EMPLOYEE ADD COLUMN Job VARCHAR(12); 의 형태로 사용한다.

 

Summary