11주차 - 관계 대수의 한계, 집단 함수(Aggregate Function), 외부 조인(Outer Join), SELECT문, DISTINCT, LIKE, NULL 값에 관하여, ORDER BY, COUNT, GROUP BY, HAVING, 집합 연산, 중첩 질의, 상관 중첩 질의, 트리거(Trigger)

관계 대수의 한계

1. 산술 연산이 불가능함
2. 집단 함수(Aggregate Function)를 지원하지 않음
3. 정렬을 나타낼 수 없음
4. 중복된 투플을 결과로 명시 불가

관계 대수는 위와 같은 한계점이 있다. 그에 따라 SQL은 이러한 한계점을 보완하는 방식으로 발전해왔다.

 

집단 함수(Aggregate Function)

집단 함수란, 여러 개의 값을 불러와 하나의 값으로 처리하여 반환하는 함수를 만한다. Maximum, Minimum, Average등이 그 예이다.

 

 

외부 조인(Outer Join)

외부 조인이란, 두 릴레이션에서 대응되는 투플들을 결합하면서 대응되는 투플을 Null로 표시하도록 하는 방식이다.

 

이러한 외부 조인에는 어떤 테이블의 내용을 남길 것이냐에 따라 왼쪽 외부 조인, 오른쪽 외부 조인, 완전 외부 조인으로 나뉜다.

• 왼쪽 외부 조인: 왼쪽 테이블 데이터 다 남김
• 오른쪽 외부 조인: 오른쪽 테이블 데이터 다 남김
• 완전 외부 조인: 모든 테이블 데이터 다 남김

 

---

SQL

SELECT [DISTINCT] 애트리뷰트(들)
FROM 릴레이션(들)
[WHERE 조건 [중첩질의]]
[GROUP BY 애트리뷰트(들)]
[HAVING 조건]
[ORDER BY 애트리뷰트(들) [ASC/DESC]];

 지금까지는 관계 대수에 대한 이야기를 주로 다루었다. 지금부터는 SQL문에 대한 이야기로 다시 돌아와 작성해보려 한다.

 

SELECT문

RDBMS에서 검색할 때 사용하는 SQL문이다. 관계 대수에서의 실렉션 연산자와 비슷하다고 생각되지만 전혀 그렇지 않다.

 

SELECT문은 관계 대수에서의 실렉션, 프로젝션, 조인, 카티션 곱 등을 결합한 개념이다.

 

DISTINCT

 SELECT절에서 추가할 수 있는 조건이다. 이는 검색 결과 중 중복을 허용하지 않겠다는 의미의 조건을 명시하는 SQL문이다.

 

LIKE

 WHERE절에서 활용할 수 있는 기능이다. 

SELECT EMPNAME, TITLE, DNO
FROM EMPLOYEE
WHERE EMPNAME LIKE '이%';

위와 같이 작성함으로 검색된 정보들 중 '이'로 시작되는 정보만을 가져오라는 명령을 명시한 것이다.

 

부정 검색조건 '<>'

 WHERE절에서 활용할 수 있는 기능이다.

SELECT EMPNAME, TITLE, DNO
FROM EMPLOYEE
WHERE DNO <> 1;

위 코드는 검색 도중, DNO가 1이 아닌 정보들만 가져오라는 명령을 명시한 것이다. 파이썬 코드로 따지자면 '!='와 같은 의미로 사용될 수 있다.

 

NULL 값에 관하여

A = NULL
NULL > 100
NULL = 100
NULL <> 100
NULL = NULL
NUL <> NULL

 NULL을 활용한 연산들 중, 위와 같은 연산들은 모두 거짓이다.

 

DNO IS NULL

NULL 값에 대한 비교는 위와 같이 'IS', 혹은 'IS NOT' 구문을 사용한다.

 

ORDER BY

SELECT EMPNAME, TITLE, DNO
FROM EMPLOYEE
ORDER BY DNO DESC

 ORDER BY는 검색을 마치고 최종적으로 결과를 보일 때, 어떠한 순서로 보일지를 결정하는 구문이다.

 

위와 같이 어떠한 애트리뷰트를 기준으로, 오름차순 혹은 내림차순으로 표시할지를 명시하는 과정을 나타낸다.

*참고: 오름차순(1, 2, 3, 4), 내림차순(4, 3, 2, 1)

 

COUNT

• COUNT(*): NULL 값을 포함한 값들의 개수
• COUNT(애트리뷰트): NULL 값을 제외한 값들의 개수

 COUNT는 SQL에서 지원하는 집단 함수 중 하나이다. 이러한 COUNT 함수는 어떻게 작성하느냐에 따라 위와 같이 다른 결과를 가져올 수 있다.

 

GROUP BY

검색 결과를 어떤 애트리뷰트를 기준으로 묶어서 보일지를 명시하는 구문이다.

EMPNO	EMPNAME	DNO	SALARY
1	홍길동	5	150
2	김철수	4	200
3	박수지	5	170
4	최규남	4	180

이해를 돕기 위해 테이블을 가지고 설명하겠다. 'SELECT * FROM EMPLOYEE'의 결과가 위와 같다고 하자.

 

SELECT DNO, AGV(SALARY) AS AVGSAL
FROM EMPLOYEE
GROUP BY DNO

이러한 상황에서 위와 같이 SQL문을 작성한다면 다음과 같은 결과가 나올 것이다.

 

DNO	AVGSAL
4	190
5	160

 

HAVING

 HAVING절을 사용하면 조건을 만족하는 그룹들에 대해서만 집단 함수를 적용할 수 있다.

 

SELECT DNO, AVG(SALARY) AS AVGSAL
FROM EMPLOYEE
GROUP BY DNO
HAVING AVG(SALARY) >= 2500000

위 구문은 EMPLOYEE 테이블에 속한 정보들을 부서 별로 묶고, 부서 별 평균 임금이 250만원 이상인 부서의 번호와 평균 임금을 보이라는 요구사항을 구현한 것이다.

 

위와 같이 HAVING절에 나타나는 애트리뷰트는 반드시 GROUP BY절이나 집단 함수에 포함되어야 한다.

 

집합 연산

SQL문은 합집합, 차집합, 교집합과 같은 집합 연산 기능을 지원한다.

 

(SELECT DNO
FROM EMPLOYEE
WHERE EMPNAME = '김창섭')

UNION

(SELECT DEPTNO
FROM DEPARTMENT
WHERE DEPTNAME = '개발')

위 질의는 '김창섭이 속한 부서이거나, 개발 부서의 부서번호를 검색하라'는 요구사항을 구현한 것이다. 위 구문의 결과는 다음과 같다.

 

DNO

2

3

 

 

중첩 질의

SELECT EMPNAME, TITLE
FROM EMPLOYEE
WHERE TITLE 
	= (SELECT TITLE
	FROM EMPLOYEE
	WHERE EMPNAME = '박영권')

 위 질의는 '박영권과 같은 직급을 가진 모든 사원의 이름과 직급을 검색하라'는 요구사항을 구현한 것이다.

 

이 경우는 한 개의 애트리뷰트로 이루어진 릴레이션이 반환되는 경우를 나타내고 있다.

 

TABLE = [1, 2, 3]

1 IN TABLE: True
4 IN TABLE: FALSE
4 NOT IN TABLE: True
0 < ANY TABLE: TRUE
4 < ANY TABLE: FALSE
0 < ALL TABLE: TRUE
4 < ALL TABLE: FALSE

한 개의 애트리뷰트로 이루어진 릴레이션이 반환되는 경우에는 위와 같은 연산을 수행하곤 한다.

 

상관 중첩 질의

SELECT EMPNAME, DNO, SALARY
FROM EMPLOYEE E
WHERE SALARY >
	(SELECT AVG(SALARY)
	FROM EMPLOYEE
	WHERE DNO = E.DNO);

 상관 중첩 질의란, 위 코드 속 'E.DNO'와 같이 중첩 질의 내부의 WHERE절에서 외부 질의에서 선언된 애트리뷰트를 참조하는 질의를 말한다. 

 

외부 질의를 만족시키는 투플이 구해진 다음, 내부 질의가 실행되기 때문에 외부 질의를 만족하는 투플의 수만큼 내부 질의가 여러 번 실행될 수 있다.

 

트리거(Trigger)

 트리거란, 데이터베이스의 갱신과 같이 '명시된 이벤트가 발생할 때마다' DBMS가 자동적으로 수행하는 사용자 정의문을 말한다.

 

이는 데이터베이스의 무결성을 유지하기 위한 일반적이고도 강력한 도구로 알려져있다.

 

또한 트리거는 테이블 정의 시 표현할 수 없는 기업만의 비즈니스 규칙 등을 시행하는 역할을 한다.

 

CREATE TRIGGER <트리거이름>
AFTER <트리거를 유발하는 이벤트들이 OR로 연결된 리스트> ON <릴레이션>
[WHEN <조건>] 
BEGIN <SQL문(들)> END

트리거를 명시하기 위해서는 위와 같이 이벤트, 조건, 동작(ECA) 규칙을 표현해야 한다.

 

이벤트로 표현할 수 있는 행위에는 '테이블에 투플 삽입', '테이블로부터 투플 삭제', '테이블의 투플 수정' 등이 있다.

 

그리고 트리거는 연쇄적으로 활성화되도록 설계되는 경우도 있다고 한다.