😵‍💫

Complex Queries


더 복잡한 검색 쿼리

Three valued logic

SQL은 어떤 표현에 대한 결과로 세 가지 값, TRUE, FALSE, UNKNOWN(=NULL), 을 가질 수 있다.

NULL=NULL은 평가될 수 없다. 대신 IS 연산자로 NULL인지 아닌지 확인할 수 있다. Not NULLIS NOT으로 확인 가능하다.

Nested queries(중첩 질의)

SELECT-FROM-WHERE 절을 가진 subquery(inner query)를 다른 쿼리의 WHERE 절(outer query)에서 사용할 수 있다.

1-- 성이 'Smith'인 직원이 관리하는 부서에서 진행하는 프로젝트 Pnumber 가져오기
2SELECT DISTINCT Pnumber
3FROM PROJECT
4WHERE Pnumber IN (
5 SELECT Pnumber
6 FROM PROJECT, DEPARTMENT, EMPLOYEE
7 WHERE Dnum = Dnumber AND Mgr_ssn = Ssn AND Lname = 'Smith'
8);

Set/Multiset 비교 연산자 IN

vv가 집합 VV 안에 있으면 TRUE를 결과로 가진다.IN 말고 다른 비교 연산자도 사용가능하다.

1-- Department 5에 일하는 직원 전체보다 급여가 큰 직원의 이름을 가져오기
2SELECT Lname, Fname
3FROM EMPLOYEE
4WHERE Salary > ALL (
5 SELECT Salary
6 FROM EMPLOYEE
7 WHERE Dno = 5
8);

(NOT)EXISTS

상호 연관된 중첩 질의의 결과가 존재하는지 아닌지(true or false) 판별한다.

1-- 직원 중 부양가족이 있는 직원의 이름을 가져오기
2SELECT Fname, Lname
3FROM EMPLOYEE
4WHERE EXISTS (
5 SELECT *
6 FROM DEPENDENT
7 WHERE Ssn = Essn
8);

NOT EXISTS는 "for all"을 표현하기 위해 사용할 수 있다(2중 부정).

값들의 명시적인 집합도 사용할 수 있다.

1-- Pnumber 1 ,2, 3에서 일하는 모든 직원의 Essn 가져오기
2SELECT DISTINCT Essn
3FROM WORKS_ON
4WHERE Pno IN (1, 2 3);

Joined tables

FROM 절에서 join 연산을 할 수 있다.

1SELECT Fname, Lname, Address
2FROM (EMPLOYEE JOIN DEPARTMENT ON Dno = Dnumber)
3WHERE Dname = 'Research';

사용할 수 있는 JOIN의 타입:

  • NATURAL JOIN: 동일한 이름을 가진 attribute에 대해 join을 한다.

  • INNER JOIN: default type이다. 매칭하는 튜플이 다른 relation에 존재하면 그 튜플을 결과에 포함한다.

  • LEFT(RIGHT) OUTER JOIN: 왼쪽(오른쪽) 테이블의 모든 튜플이 결과에 나온다. 즉, 오른쪽(왼쪽) 튜플과 매칭되는 값이 없는 튜플도 결과에 나온다.

  • FULL OUTER JOIN: LEFT + RIGHT OUTER JOIN

Aggregate functions(집계 연산)

어떤 그룹에 속하는 튜플들의 정보를 요약해서 하나의 튜플로 보기 위해 사용한다.

연산자: COUNT, SUM, AVG, MAX, MIN

1SELECT SUM(Salary), MAX(Salary), MIN(Salary), AVG(Salary)
2FROM EMPLOYEE;

COUNT에서 NULL 값은 카운트하지 않는다. 하지만 튜플을 센다면, 어떤 튜플의 attribute가 NULL 값을 가져도 카운트 된다.

집계 연산은 SELECT 절이나 HAVING 절에서 사용할 수 있다. HAVING은 그룹을 선택하는 컨디션을 작성할 수 있는 절이다.

Grouping

GROUP BY 절을 사용해서 relation을 튜들들의 부분집합으로 나눌 수 있다.

Grouping attribute를 기준으로 같은 값을 가진 tuple끼리 묶는다. Grouping attribute는 GROUP BY절에 명시하고 SELECT절에 무조건 나타나야 한다.

1-- 부서별 직원의 수와 평균 급여를 가져오기
2SELECT Dno, COUNT(*), AVG(Salary)
3FROM EMPLOYEE
4GROUP BY Dno;

in-line view

WITH절을 사용해서 특정 쿼리에서 사용 가능한 가상의 테이블을 정의한다.

1-- 2명 이상이 일하는 부서 중 급여가 40000 이상인 직원의 수를 가져오기
2WITH SOMEDEPTS AS (
3 SELECT Dno
4 FROM EMPLOYEE
5 GROUP BY Dno
6 HAVING COUNT (*) >= 2
7)
8SELECT e.Dno, COUNT(*)
9FROM EMPLOYEE e, SOMEDEPTS b
10WHERE Salary > 40000 AND e.Dno = b.Dno
11GROUP BY e.Dno;

CASE

값이 특정 condition에 따라 달라질 수 있을 때 사용한다. SQL 쿼리 중 값을 기대하는 어느 부분에서도 사용할 수 있다.

1UPDATE EMPLOYEE
2SET Salary = CASE WHEN Dno = 5 THEN Salary + 2000
3 WHEN Dno = 4 THEN Salary + 1500
4 WHEN Dno = 1 THEN Salary + 3000

Recursive query

같은 타입의 relation에서 튜플 간의 관계(계층구조 등)를 파악할 때 유용하게 사용할 수 있다.

1-- 계층구조 탐색
2SELECT Super_ssn
3FROM EMPLOYEE
4START WITH Ssn = '123456789'
5CONNECT BY PRIOR Super_ssn = Ssn;

Views in SQL

SQL에서 view는 다른 테이블로부터 파생된 테이블이다. 실제 물리적으로 적재되지 않은 가상 테이블이다. View는 실제로 참조하게 되는 순간 실제화/구체화된다.

View는 자주 참조되는 테이블을 명시하기 위한 방법으로 생각할 수 있다. 또한, 자주 사용되는 joined table의 캐쉬처럼 사용할 수 있다.

CREATE VIEW를 통해 view의 이름과 attribute, 정의를 명시한다.

1CREATE VIEW WORKS_ON1 AS
2SELECT Fname, Lname, Pname, Hours
3FROM EMPLOYEE, PROJECT, WORKS_ON
4WHERE Ssn = Essn AND Pno = Pnumber;

View의 장점

  • 쿼리를 단순화 할 수 있다.

  • 보안 및 권한 부여 메커니즘을 제공한다.

  • 값비싼 join cost를 아낄 수 있다.

View 구현의 여러 방법

Query modification

필요할 때만 만들어낸다. View가 참조될 때마다 새로 연산을 수행하는 방식이다.

이 방식은 복잡한 쿼리들로 이루어진 view의 경우 참조할 때마다 연산을 수행해야하기 때문에 비효율적이다.

View materialization

View가 처음 참조될 때 임시 테이블을 물리적으로 만든다. 이 테이블에 대해 쿼리가 계속 이루어지면 남아있다가 일정기간동안 사용되지 않으면 삭제된다.

이 방식에서는 테이블을 자동으로 update하기 위한 효율적인 방법이 필요하다.

  • Incremental update: 변화에 영향받은 tuple만 DBMS가 판별해서 갱신한다.

  • Immediate update: View의 base table이 변경되면 갱신한다.

  • Lazy update: View가 참조될 때 갱신한다.

  • Periodic update: 일정 주기로 갱신한다.

일반적인 경우 view 테이블을 INSERT/DELETE/UPDATE로 직접 변경하는 것은 불가능하다. 왜냐하면 view에는 많은 table이 참여하고 있기 때문에 명령어가 중의적인 의미를 가질 수도 있기 때문이다.

Schema change statements in SQL

  • DROP
    스키마 요소(tables, domains, constraints)들을 삭제할 때 사용한다.
    Option: CASCADE, RESTRICT

  • ALTER TABLE
    attribute 추가/제거/재정의, 테이블 제약조건 추가/삭제, default 값 변경 등에 사용할 수 있다.

Assertion and Triggers

CREATE ASSERTION: 일반 제약조건을 명시할 수 있다. 오라클에서는 지원되지 않는다.

Trigger

특정 이벤트가 발생하고 condition이 만족됐을 때 취할 행동을 명시할 수 있다.

Events, Condition, Action(ECA)로 정의된다.

1CREATE OR REPLACE
2TRIGGER SALARY_VIOLATION
3BEFORE INSERT OR UPDATE OF Salary ON EMPLOYEE -- Event
4FOR EACH ROW
5 WHEN (NEW.SALARY > 100000) -- Condition
6 BEGIN -- Action
7 INFORM_SALARY_VIOLATION(:NEW.SALARY, :OLD.Salary); -- Stored procedure
8 END;
9/