Chapter 7: 복잡한 쿼리, 트리거, 뷰, 스키마 수정

더 복잡한 SQL 검색 쿼리

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

이러한 기능으로는

중첩 질의

결합된 테이블들 (자연 조인)

FROM 절에서의 외부 조인

뷰 (유도된 테이블), 주장(assertions), 트리거

집계 함수

그룹화: 온라인 분석 처리 작업을 포함

이 챕터는 이들이 무엇인지 및 SQL에서 어떻게 사용하는지 학습하는 데 중점을 둡니다.

NULL과 관련된 비교 및 세 가지 값 논리

SQL은 세 가지 값 논리를 사용합니다.

표현식을 평가한 결과는 다음 중 하나에 속합니다:

참(TRUE), 거짓(FALSE), 또는 알 수 없음(UNKNOWN)

NULL = NULL은 평가될 수 없습니다.

세 가지 값 논리에서의 논리 연결자(진리 표)

SQL은 속성 값이 NULL인지 확인하는 쿼리를 허용합니다.


SELECT Fname, Lname
FROM EMPLOYEE
WHERE Super_ssn IS NULL;

위 쿼리는 감독관이 없는 모든 직원의 이름을 찾습니다.


SELECT DISTINCT Pnumber
FROM PROJECT
WHERE Pnumber
IN (SELECT Pnumber
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE Dnum = Dnumber AND
Mgr_ssn = Ssn AND Lname = 'Smith')
OR
Pnumber IN (SELECT Pno
FROM WORKS_ON, EMPLOYEE
WHERE Essn=5s AND Lname = 'Smith');

SQL에서 (NOT) EXISTS 함수 사용하기: 상관 쿼리 상관관계 맺기

(NOT) EXISTS 함수

상관 중첩 쿼리의 결과가 '비어 있지 않음'('비어 있음')을 확인합니다.

상관 중첩 쿼리와 함께 사용될 수 있습니다.

TRUE 또는 FALSE 결과를 반환하는 부울 함수입니다.

상관 중첩 쿼리에 의해 반환된 튜플이 없으면 EXISTS는 FALSE를 반환하고, NOT EXISTS는 TRUE를 반환합니다.

NOT EXISTS의 사용

"모두에 대해" (전체 수량자 효과를 얻기 위해) 이중 부정을 이렇게 사용할 수 있습니다: ("...하지 않은 튜플들은 존재하지 않는다" -> "...한 튜플들만 존재한다.")

“Dno = 5인 모든 프로젝트에 참여하는 직원의 이름을 검색하라.”


SELECT E.Fname, E.Lname
FROM EMPLOYEE E
WHERE NOT EXISTS (
    (SELECT P.Pnumber
    FROM PROJECT P
    WHERE Dnum = 5
    MINUS
    (SELECT W.Pno
    FROM WORKS_ON W
    WHERE E.Ssn = W.Essn))
);

5번 부서에서 관리하는 프로젝트에 참여하지 않는 프로젝트가 존재하지 않는 직원의 이름을 나열합니다.

부록: NOT EXISTS 사용(계속)

이전 쿼리는 두 단계의 중첩을 사용하는 더 복잡한 방식으로 다시 작성할 수 있습니다.


SELECT E.Fname, E.Lname
FROM EMPLOYEE E
WHERE NOT EXISTS (
    SELECT Pno
    FROM WORKS_ON B
    WHERE (B.Pno IN (
        SELECT P.Pnumber
        FROM PROJECT P
        WHERE P.Dnum = 5)
        AND NOT EXISTS (
            SELECT *
            FROM WORKS_ON C
            WHERE C.Essn = E.ssn
            AND C.Pno = B.Pno))
);

예: E.Ssn = ‘123456789’ (3번 프로젝트에 참여하지 않음) 또는 ‘666884444’ (1,2번 프로젝트에 참여하지 않음) 또는 ‘453453453’ (3번 프로젝트에 참여하지 않음) 또는 ‘334455552’ (1번 프로젝트에 참여하지 않음). E.Ssn = ‘123456789’의 경우, (WORKS_ON에서) 존재하게 되므로, 선택되지 않습니다.

SQL에서 명시적 집합과 이름 변경하기

명시적 집합

WHERE 절에서 값의 명시적 집합을 사용할 수 있습니다.


SELECT DISTINCT Essn
FROM WORKS_ON
WHERE Pno IN (1, 2, 3);

속성 이름 변경

"AS"를 사용하고 이어서 법적으로 인정되는 이름을 사용하여 속성의 이름을 변경할 수 있습니다. (지난 번에 논의함)


SELECT E.Lname AS Employee_name,
S.Lname AS Supervisor_name
FROM EMPLOYEE E, EMPLOYEE S
WHERE E.Super_ssn = S.Ssn;

SQL에서 결합된 테이블과 내부 조인

결합된 테이블

개념: 사용자는 쿼리의 FROM 절에서 조인 작업의 결과로 나오는 테이블을 지정할 수 있습니다.


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

단일 결합 테이블을 포함합니다. 이러한 조인은 또한 내부 조인이라고도 불릴 수 있습니다(일치하는 튜플에만 적용됨).

SQL에서 다양한 타입의 JOINed 테이블

사용자는 다양한 타입의 조인을 지정할 수 있습니다:

NATURAL JOIN (내부 조인의 가장 대표적인 형태)

R(왼쪽 테이블)과 S(오른쪽 테이블)에 대해, 조인 조건이 명시되지 않습니다.
R과 S에서 같은 이름을 가진 각 속성 쌍에 대해 암시적 EQUIJOIN 조건을 생성하는 것과 동일합니다.


CREATE TABLE DEPT AS
SELECT Dname, Dnumber as Dno, Mgr_ssn, Mgr_start_date
FROM DEPARTMENT;

SELECT Fname, Lname, Address
FROM EMPLOYEE NATURAL JOIN DEPT
WHERE Dname = 'Research';

사용자는 다양한 타입의 조인을 지정할 수 있습니다(계속):

INNER JOIN (vs. OUTER JOIN)

결합된 테이블에서 기본적인 조인 유형입니다.
다른 관계에 일치하는 튜플이 있을 때만 튜플이 결과에 포함됩니다. 일치하지 않는 튜플까지 결과에 포함시키고 싶다면 어떻게 해야 할까요?

LEFT (RIGHT) OUTER JOIN

왼쪽(오른쪽) 테이블의 모든 튜플이 결과에 나타나야 합니다.
일치하는 튜플이 없는 경우, 오른쪽(왼쪽) 테이블의 속성에 대해 NULL 값으로 채워집니다.
예시


SELECT E.Lname AS Supervisee_Name,
S.Lname AS Supervisor_NAME
FROM Employee E LEFT OUTER JOIN EMPLOYEE S ON E.Super_ssn = S.Ssn

FULL OUTER JOIN: LEFT와 RIGHT OUTER JOIN의 결과를 결합합니다.


SELECT E.Lname AS Supervisee_Name, S.Lname AS Supervisor_Name
FROM EMPLOYEE E FULL OUTER JOIN EMPLOYEE S ON E.Super_ssn = S.Ssn

FROM 절에서의 다중 조인(Multiway JOIN)

"다중" 조인은 JOIN 사양을 중첩함으로써 지정할 수 있습니다.

예시)


SELECT Pnumber, Dnum, Lname, Address, Bdate
FROM ((PROJECT JOIN DEPARTMENT ON Dnum=Dnumber) JOIN EMPLOYEE ON Mgr_ssn = Ssn)
WHERE Plocation='Stafford';

위 내용은 SQL에서 명시적 집합을 어떻게 사용하는지, 속성의 이름을 어떻게 변경하는지, 그리고 다양한 조인 유형과 이들이 쿼리 내에서 어떻게 작동하는지에 대해 설명합니다.

SQL에서 집계 함수

왜 사용하는가?

여러 튜플로부터 정보를 요약하여 그룹 당 단일 튜플 요약으로 제공하기 위해 사용합니다.

내장 집계 함수:

COUNT, SUM, MAX, MIN, MEDIAN, AVG 등이 있습니다.

그룹화

일반적으로 GROUP BY 절을 통해 그룹화합니다.

요약하기 전에 튜플의 하위 그룹을 생성합니다.

전체 그룹을 선택하거나 조건을 적용하기 위해 HAVING 절이 사용됩니다.

집계 함수는 SELECT 절과 HAVING 절에서 사용할 수 있습니다.

전체 튜플에 적용되는 집계


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


SELECT COUNT(*) as NumEmps, SUM(Salary) AS Total_Sal, MAX(Salary) AS Highest_Sal, MIN(Salary) AS Lowest_Sal, AVG(Salary) AS Average_Sal
FROM EMPLOYEE, DEPARTMENT
WHERE Dno = Dnumber AND Dname = 'Research';


SELECT COUNT(E.Lname)
FROM Employee E LEFT OUTER JOIN EMPLOYEE S
ON E.Super_ssn = S.Ssn;


SELECT COUNT(S.Lname)
FROM Employee E LEFT OUTER JOIN EMPLOYEE S
ON E.Super_ssn = S.Ssn;


SELECT COUNT (*)
FROM Employee E LEFT OUTER JOIN EMPLOYEE S
ON E.Super_ssn = S.Ssn;

값의 개수를 세는 경우 NULL은 제외되지만, 튜플에 대해서는 예외입니다.

그룹화: GROUP BY 절

관계를 튜플의 하위 집합으로 분할합니다.

그룹화 속성을 기반으로: 동일한 값을 가지고 있어야 합니다.

각 그룹에 독립적으로 함수를 적용합니다.

GROUP BY 절

그룹화 속성을 명시합니다.

그룹화 속성은 SELECT 절에 반드시 나타나야 합니다.


SELECT Dno, COUNT(*) as nEmps, AVG(Salary) as avgSal
FROM EMPLOYEE
GROUP BY Dno;

GROUP BY 절 (계속)

JOIN의 결과에 적용될 수 있습니다.


SELECT Pnumber, Pname, COUNT(*) as numEmps
FROM PROJECT, WORKS_ON
WHERE Pnumber=Pno
GROUP BY Pnumber, Pname
ORDER BY Pnumber, Pname;

GROUP BY는 JOIN의 결과에 적용된 후 결과가 정렬됩니다.

HAVING 절과 함께 사용하는 GROUP BY 절

HAVING 절

전체 그룹을 선택하거나 거부하는 조건을 제공합니다.


SELECT Pnumber, Pname, COUNT(*) as numEmps
FROM PROJECT, WORKS_ON
WHERE Pnumber=Pno
GROUP BY Pnumber, Pname
HAVING COUNT(*) > 2;

전체 그룹을 선택하거나 거부하는 조건을 제공합니다. “$40,000 이상 벌고 있는 직원이 2명 이상인 각 부서에 대해 부서 번호와 직원 수를 검색합니다.”


SELECT Dnumber, COUNT(*) as nEmps
FROM DEPARTMENT, EMPLOYEE
WHERE Dnumber = Dno AND Salary > 40000
AND Dnumber IN
(SELECT Dno
FROM EMPLOYEE
GROUP BY Dno
HAVING COUNT(*) >= 2)
GROUP BY Dnumber;

SQL에서의 뷰 (가상 테이블)

뷰를 사용하는 이유? 뷰를 정의하는 장점:

특정 쿼리의 명세 간소화

보안 및 권한 메커니즘의 제공

실질화된 경우 공간으로 비싼 조인 비용(다수) 절약

DROP VIEW

뷰를 제거함.

뷰 구현

뷰는 항상 최신 상태여야 합니다. 왜냐하면?

뷰가 정의된 기본 관계의 튜플을 수정하면, 뷰는 이러한 변경 사항을 “자동으로” 반영해야 함.
뷰는 뷰에 대한 쿼리를 지정할 때 실현되거나 실질화되어야 하지만, 뷰를 정의할 때는 그렇지 않아야 함.
DBMS는 뷰를 최신 상태로 유지하는 책임이 있음
사용자가 뷰가 최신임을 확인하는 것이 아님.

질문: 그렇다면 DBMS는 어떻게 뷰를 최신 상태로 유지하게 합니까?

문제: 쿼리를 위한 뷰를 “효율적으로” 구현하는 것은 쉽지 않음.

뷰 구현 전략

전략 1) 쿼리 수정 접근법

필요할 때 뷰를 계산합니다.

뷰를 영구적으로 저장하지 않습니다!

뷰 쿼리를 기본 테이블에 대한 쿼리로 수정합니다. 변환 예제:


SELECT Fname, Lname
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE Ssn = Essn AND Pno = 'ProductX';

문제점이 있나요??

“복잡한” 쿼리를 통해 정의된 뷰에 대해서는 비효율적이며, 실행하는 데 오랜 시간이 걸리거나 시간이 많이 소요됩니다.

전략 2) 뷰 실질화 접근법

뷰가 처음 쿼리될 때 물리적으로 임시 뷰 테이블을 생성합니다.

그리고 나중에 뷰에 대한 다른 쿼리가 올 것이라고 가정하고 해당 테이블을 유지합니다.

기본 테이블이 업데이트될 때 뷰 테이블을 자동으로 업데이트하는 효율적인 전략이 필요합니다.

실질화된 뷰에 대한 증분 업데이트 전략

DBMS는 데이터베이스 업데이트가 (정의하는) 기본 테이블 중 하나에 적용될 때 실질화된 뷰 테이블에 삽입, 삭제 또는 수정해야 할 새 튜플을 결정할 수 있습니다.

쿼리되는 동안 실질화된 테이블이 유지됩니다.

일정 기간 동안 뷰에 대한 쿼리가 없으면 테이블이 자동으로 제거됩니다. 나중에 다시 접근하면 처음부터 다시 계산됩니다.

실질화 구현 방법:

즉시 업데이트 (즉, “쓰기 전달”)

기본 테이블이 변경되자마자 뷰를 업데이트합니다.

지연 업데이트 (즉, “쓰기 지연”)

뷰 쿼리가 요청할 때마다 뷰를 업데이트합니다.

주기적 업데이트

주기적으로 뷰를 업데이트합니다.

후자의 전략에서는 뷰 쿼리가 최신 결과를 얻지 못할 수도 있습니다.

이는 일반적으로 인구 조사, 월간 판매 기록 검색, 은행, 소매점 운영 등에서 사용됩니다.

SQL에서의 스키마 수정

스키마 진화 명령어

스키마 요소(테이블, 뷰, 속성, 제약 조건 등)를 추가하거나 삭제하여 스키마를 변경하는 데 사용됩니다.

이유? 데이터베이스 관리자(DBA)는 데이터베이스가 작동 중인 동안 스키마를 변경할 수 있습니다.

데이터베이스 스키마를 다시 컴파일할 필요가 없습니다.

간편하고 빠릅니다.

그러나 변경사항이 데이터베이스의 나머지 부분에 영향을 미치지 않고 일관성을 유지할 수 있도록 합니다.

DROP 명령어

이름이 지정된 스키마 요소(테이블, 도메인, 제약 조건)를 제거하는 데 사용됩니다.

DROP 동작 옵션: CASCADE와 RESTRICT

예시:

DROP SCHEMA COMPANY CASCADE; -- 이것은 절대 확신이 있을 때만 실행해야 합니다.

스키마와 모든 속한 요소(테이블, 뷰, 제약 조건 등)를 제거합니다.
RESTRICT: 요소가 없으면 제거를 계속합니다.

DROP TABLE DEPENDENT CASCADE;

관계 및 카탈로그에서의 정의를 제거합니다.
RESTRICT: 참조가 없는 경우에만 제거를 진행합니다.

ALTER TABLE 명령어

포함하는 작업들:

컬럼(속성) 추가 또는 삭제
컬럼 정의 변경
테이블 제약 조건 추가 또는 삭제

예시:

ALTER TABLE COMPANY. EMPLOYEE ADD COLUMN Job VARCHAR(12);
ALTER TABLE COMPANY. DEPARTMENT DROP COLUMN Address CASCADE;

CASCADE: 컬럼(Address)을 참조하는 모든 제약 조건과 뷰가 함께 삭제됩니다.
RESTRICT: 뷰/제약 조건이 컬럼을 참조하지 않는 경우에만 제거합니다.

ALTER TABLE COMPANY. EMPLOYEE DROP CONSTRAINT EMPSUPERFK CASCADE;

기본 값

제거되거나 변경될 수 있습니다:

ALTER TABLE COMPANY.DEPARTMENT ALTER COLUMN Mgr_ssn DROP DEFAULT;
ALTER TABLE COMPANY.DEPARTMENT ALTER COLUMN Mgr_ssn SET DEFAULT '333445555';

의미론적 제약조건을 주장(assertions)으로서 지정하고 트리거로서의 동작

CREATE ASSERTION

사용자가 선언적 주장(assertion)을 통해 “일반적인” 제약 조건을 지정할 수 있습니다.

이러한 제약 조건들은 키(또는 유니크), 엔티티, 널이 아닌, 참조 무결성 제약 조건의 카테고리에 속하지 않습니다.

사용자가 원하는 조건을 위반하는 튜플을 선택하는 쿼리를 명시합니다.

단순한 CHECK 제약 조건으로 명시할 수 없는 경우에만 사용됩니다.


CREATE ASSERTION SALARY_CONSTRAINT
CHECK (NOT EXISTS (
    SELECT * FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D
    WHERE E.Salary > M.Salary AND E.Dno = D.Dnumber AND D.Mgr_ssn = M.Ssn -- (이 기능은 Oracle에서는 구현되지 않았습니다)
));

트리거 (Triggers)

특정 이벤트가 발생하고 특정 조건이 충족될 때 수행할 작업의 유형을 명시하는 데 유용합니다.

“직원이 여행 경비 한도를 초과하면 매니저에게 알림을 보냅니다.”

“부서의 직원 수가 한도를 초과하면 새 직원은 받아들여지지 않습니다.”

데이터베이스를 모니터링하는데 사용됩니다.

트리거는 보통 세 부분으로 구성됩니다:

이벤트(Event), 조건(Condition), 행동(Action) - 이를 ECA 규칙이라고 합니다.

이러한 규칙은 이 수업에서 다루지 않는 “활성” 데이터베이스의 규칙을 만듭니다.

자세히 알고 싶은 사람은 섹션 26.1을 참조하십시오.

트리거 사용 방법?


CREATE OR REPLACE -- 새로운 직원 레코드 삽입
TRIGGER SALARY_VIOLATION -- 직원의 급여 변경
-- 이벤트(Event) -- 직원의 상사 변경
BEFORE INSERT OR UPDATE OF Salary, Supervisor_ssn ON EMPLOYEE FOR EACH ROW
	-- 조건(Condition): 규칙 액션을 실행할지 결정
	WHEN (NEW.SALARY > (SELECT Salary 
											FROM EMPLOYEE 
											WHERE Ssn = NEW.Supervisor_Ssn))
	-- 액션(Action): 일반적으로 SQL 명령문, 트랜잭션, 또는 PSM으로 구성
	INFORM_SUPERVISOR (NEW.Supervisor.Ssn, NEW.Ssn);

트리거는 "BEFORE" 즉, 트리거 작업(여기서는 삽입 또는 업데이트)이 수행되기 "전 실행되어야 함을 의미합니다.

저장 프로시저라 불리는 것은 DBMS에 의해 데이터베이스 서버에 저장되는 프로그램 모듈로, SQL 표준에서는 영속 저장 모듈(Persistent Stored Modules, PSM)이라고 합니다.

그러나 Oracle에서는 이것이 그대로 실행되지 않습니다. 트리거와 관련된 실습을 진행할 것입니다.