More SQL

더 복잡한 SQL 질의

NULL값을 포함한 비교와 Three-Valued Logic

• 알려지지 않은 값
• 이용할 수 없거나 보류해 둔 값
• 적용할 수 없는 애트리뷰트

종종 NULL이 이 세 값중에서 어느 것을 의미하는지 결정할 수 없을 때가 있다. 따라서 SQL은 널 값의 의미를 구분하지 않는다.

SQL은 다음 세가지 논리값을 사용한다.

• TRUE, FALSE and UNKNOWN

SQL은 질의에서 애트리뷰트 값이 널인지 검사할 수 있다. 그러나 SQL에서는 애트리뷰트를 NULL과 비교하기 위해서 IS나 IS NOT을 비교 연산자로 사용한다.

예를 들어, (FALSE AND UNKNOWN)의 결과는 FALSE.

Nested Queries, 집합 비교

• Nested queries : WHERE절 내에 완전한 select-from-where 형태를 갖는다.
• 바깥에 있는 쿼리를 Outer query, IN 안에 들어있는 쿼리를 nested subquery 라고 한다.

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

위 질의에서는 비교 연산자 IN을 사용하고 있는데, 이 연산자는 하나의 값 v를 값들의 집합(혹은 다중집합) V와 비교하며, v가 V의 원소인 경우에 참이 된다. 외부 질의에서는 PROJECT 테이블에서 PNUMBER의 값이 두 중첩 질의 중에서 어느 한 중첩 질의의 결과에 포함된 튜플들을 선택하기 위해 OR 연산자를 사용한다.

위 질의는 Ssn이 123456789인 'John Smith'사원이 일하는 어떤 프로젝트의 (Pno, Hours)조합과 동일한 모든 사원의 Essn을 WORK_ON relation에서 구한다.
이 예제에서 IN 연산자는 WORK_ON 내에 있는 각 튜플의 부분 튜플인 Pno,Hours를 nested query에서 생성한 튜플과 비교한다.

• 연산자 ANY(또는 SOME)은 하나의 값 v가 값들의 집합 V 내의 어떤 값들과 같으면 TRUE를 반환(IN과 동일)
• 키워드 ALL : 예를 들어 (v > ALL V)은 하나의 값 v가 값들의 집합 V값보다 큰 경우에 참을 반환한다.
• 이 연산자들은 >, >=, <, <=, <>와 결합하여 사용이 가능하다.

다음 질의는 5번 부서에 근무하는 모든 사원보다 급여가 많은 사원들의 이름을 검색한다.

상관 중첩 질의(Correlate newted query)

• 중첩 질의의 WHERE절 안에 있는 조건에서 외부 질의의 애트리뷰트를 참조하는 경우.
• 외부 질의를 만족하는 각 투플이 구해진 구에 중첩 질의가 수행된다고 이해하자.

EMPLOYEE의 Ssn값이 이 중첩 질의의 결과 안에 속하면 그 EMPLOYEE 튜플이 선택된다.

---

JOIN된 테이블과 외부 조인

INNER JOIN

• FROM절에 조인 연산의 결과로 생성되는 테이블을 명시할 수 있는 방법

NATURAL JOIN

• 조인 조건이 지정되지 않는다.
• 두 릴레이션 R과 S에서 이름이 같은 각 애트리뷰트 쌍에 대한 암시적 EQUIJOIN조건과 동일하다.

DEPARTMENT 릴레이션은 DEPT로 재명명되고 애트리뷰트들은 Dname,Dno,Mssn,Msdate로 재명명되었다. 재명명된 이후에 같은 이름을 갖는 애트리뷰트는 Dno뿐이므로 자연 조인에 대한 묵시적인 조인 조건은 EMPLOYEE.Dno = DEPT.Dno이다.

OUTER JOIN

• INNER JOIN과 달리 둘 중 하나만 해당하는 애트리뷰트가 있어도 없는 테이블의 해당 애트리뷰트를 NULL로 채워서 JOIN 시켜준다.
• LEFT OUTER JOIN : 왼쪽 테이블에 있는 모든 튜플들이 결과에 나타남.
  • 오른쪽 테이블에 일치하는 튜플이 없을 경우 오른쪽 테이블의 모든 애트리뷰트에 NULL값으로 채움
• RIGHT OUTER JOIN : 오른쪽 테이블에 있는 모든 튜플들이 결과에 나타남.
  • 왼쪽 테이블에 일치하는 튜플이 없을 경우 왼쪽 테이블의 모든 애트리뷰트에 NULL값으로 채움
• FULL OUTER JOIN

---

Aggregate functions in SQL(집단 함수)

• COUNT, SUM, MAX, MIN, AVG과 같은 내장 집단 함수들이 있다.
• 이 함수들은 SELECT절이나 HAVING절에서 사용할 수 있다.
• 이 함수들은 숫자들의 집합이나 다중집합에 적용되어, 적용되는 개수, 합, 최대,최소,평균값을 구할 수 있다.

---

Grouping: THE GROUP BY와 HAVING 절

GROUP BY

ex) 각 부서에 대해서 부서 번호, 부서에 속한 사원들의 수, 각 부서에 속한 사원들의 평균 급여를 구하라.

EMPLOYEE 튜플들을 GROUP BY 애트리뷰트인 Dno의 값이 같은 튜플들끼리 여러 그룹으로 나눈다. 따라서 각 그룹은 동일한 부서에 근무하는 사원들을 포함하고 있다.
SELECT절은 그룹화 애트리뷰트와 튜플들의 각 그룹에 적용할 집단 함수들만 포함된다.

• 만약 그룹화 애트리뷰트 내에 NULL이 존재하면
• 그 NULL값을 가진 튜플들을 위한 별도의 그룹을 만든다.

다음은 조인 조건과 함께 GROUP BY를 사용하는 예시를 보여준다.

이런 경우에 먼저 WHERE절에 있는대로 릴레이션을 JOIN한 후에 그룹화와 집단 함수가 적용된다.

HAVING

어떤 조건에 해당하는 그룹에 대해서만 집단 함수의 값을 구할때 HAVING을 사용한다.
ex) 2명 이상의 사원이 근무하는 프로젝트들만을 대상으로 질의를 변경하는 예시

HAVING절의 조건을 만족하는 그룹들만 질의 결과로 검색된다.

잘못된 예시 : HAVING문이 실행되기 전에 WHERE문이 실행됨.

중첩 질의를 사용한 올바른 예시 :

---

Assertion으로 제약 조건 및 Trigger로 동작 명시

• CREATE ASSERTION
  • 관계 모델의 기본적인 제약조건(엔티티 무결성, 기본키와 유일키, 참조 무결성)을 제외한 추가저인 제약 조건을 명시할때 사용
    ex) "사원의 급여가 자신이 근무하는 부서의 관리자의 급여보다 많으면 안된다" 라는 제약 조건 추가

• CREATE TRIGGER
  • 특정 사건이 발생하고, 특정 조건이 만족될때 취해야 할 행동의 유형을 명시
    ex) "한 사원의 급여가 그 사원을 직접적으로 감독하는 상관의 급여보다 많은가"를 항상 검사한다.
    즉, 새로운 사원 레코드를 삽입할 때, 한 사원의 급여를 변경할 때, 또는 사원의 상관을 변경할 때 취해야 할 행동은 SALARY_VIOLATION이라는 저장 함수를 호출하는 것이라고 할 때.

트리거는 보통 ECA(EVENT,CONDITION,ACTION)규칙 형태의 세 가지 요소를 가지고 있다.

• EVENT: 이벤트에 명시된 데이터베이스 갱신 연산을 이벤트 앞의 BEFORE|AFTER 키워드에 따라 갱신 연산 전에 트리거를 수행하거나 갱신 연산 전에 트리거를 수행해야 한다는 것을 의미한다.
• CONDITION: 트리거 이벤트가 발생한 경우, 선택적인 조건을 검사할 수 있다. 어떠한 조건도 명시되지 않으면, 이벤트가 발생할때 동작이 실행된다. 조건이 명시되 있으면 조건이 참일때 동작을 실행한다.
• ACTION: 동작은 보통 SQL문들로 구성되지만, 자동적으로 실행될 데이터베이스 트랜잭션이나 외부 프로그램일 수 있다. 위 예제에서는 저장 프로시저를 실행하는 것

---

Views in SQL

• 다른 테이블들에서 유도된 단일 테이블.
• 물리적으로 존재하는 기본 테이블과 달리 가상 테이블로 간주된다.
• 물리적으로 존재하지 않더라도 자주 참조할 필요가 있는 테이블을 명시하는 한가지 방법.

---

SQL에서 스키마 변경문

DBA에 의해 제약 조건 또는 테이블, 스키마 요소 등을 변경할 수 있다. 다른 프로그래밍 언어처럼 recompile할 필요없이 바로 사용할 수 있다.

• DROPcommand

  • 테이블,도메인,타입,또는 제약 조건같이 이름을 가진 스키마 요소를 제거하는데 사용
  • CASCADE and RESTRICT
  • ex: DROP SCHEMA COMPANY CASCADE;
  • CASCADE 대신에 RESTRICT 옵션을 사용한다면, 이 테이블이 다른 제약조건이나 다른 요소들에 의해 참조되지 않을때만 이 테이블이 제거된다.

• ALTERcommand

  • 기본 테이블이나 다른 이름을 가진 스키마 요소들의 정의를 변경할 수 있다.
  • ex: ALTER TABLE COMPANY.EMPLOYEE ADD COLUMN Job VARCHAR(12); - COMPANY SCHEMA에 있는 EMPLOYEE 기본 테이블에 사원들의 직업을 표히하기위한 애트리뷰트를 하나 추가하기 위한 명령문

• Dropping columns, Default values