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으로 쓸 수도 있다.
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.
위처럼 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
'CS > Database' 카테고리의 다른 글
[데이터베이스] 8. Functional Dependencies and Normalization for Relational Databases (Ch. 14) (0) | 2023.12.09 |
---|---|
[데이터베이스] 7. Indexing (Ch. 17) (0) | 2023.12.09 |
[데이터베이스] 5. Basic SQL (chapter 6) (1) | 2023.12.02 |
[데이터베이스] 4. Relational Data Model and Relationship Database Constraints (Chapter 5) (0) | 2023.12.02 |
[데이터베이스] 3. Data Modeling Using ER Model (1) | 2023.12.01 |