5장 SQL: 질의, 제약조건, 트리거
Updated:
개요
SQL 언어는 여러 가지 모습이 있는데 그 중 다음 모습들을 보자.
- 데이터 조작어(Data Manipulation Language, DML): SQL의 이 부분은 사용자가 질의하고, 행을 삽입, 삭제, 수정하는 것을 허용한다.
- 데이터 정의어(Data Definition Language, DDL): SQL의 이 부분은 테이블과 뷰에 대한 정의의 생성, 삭재, 수정을 지원한다. 무결성 제약 조건은 테이블이 생성될 때이거나 혹은 나중에 테이블에 대해 정의될 수 있다.
다음 예제를 보자.
Sailors(sid: integer, sname: string, rating: integer, age: real)
Boats(bid: integer, bname: string, color: string)
Reserves(sid: integer, bid: integer, day:date)
Sailors의 S3, Reserves의 R2, Boats의 B1 인스턴스는 다음과 같다.
기본 SQL 질의 형태
SQL 질의의 기본형은 다음과 같다.
SELECT [DISTINCT] target-list
FROM relation-list
WHERE qualification
모든 질의는 경과에 유지되는 열들을 표시하는 하나의 SELECT 절과 테이블의 크로스 프로덕트를 표시하는 하나의 FROM 절을 가져야 한다. 선택적인 WHERE 절은 FROM 절에서 언급된 테이블들에 대한 선택 조건을 표시한다. 질의는 직관적으로 선택, 추출, 크로스 프로덕트를 포함하는 관계대수식과 관계된다.
더 자세하게 기본적인 SQL 질의의 문법을 살펴보자.
- relation-list: 테이블 이름들의 리스트이다.테이블 이름은 범위 변수에 의해 표시되어 질 수 있다. 범위 변수는 같은 테이블 이름이 relation-list에 두 번 이상 나타날 때 특히 유용하다.
- target-list: relation-list에 표시된 테이블의 열 이름들의 리스트이다. 열 이름 앞에 범위 변수들을 붙일 수 있다.
- qualification: 수식 op 수식 형 조건들의 Boolean 조합(즉, 논리 접속사인 AND, OR, NOT을 사용한 수식)이고, 여기서 op는 비교연산자 {$<, <=, =, >, >=, \neq$} 중의 하나이다.
- DISTINCT: 선택적인 키워드이다. 해답으로서 계산된 테이블은 중복, 즉 같은 행의 두 사본을 포함하지 않음을 나타낸다. 디폴트는 중복들이 제거되지 않는 것이다.
한 질의의 답은 릴레이션 그 자체이고 내용은 다음의 개념적 평가 전략을 생각함으로서 이해될 수 있다.
- relation-list의 테이블들에 대한 크로스 프로덕트 계산
- 크로스 프로덕트에서 자격 조건(qualification)을 만족하지 않는 행들을 삭제
- target-list에 나타나지 않는 모든 열 삭제
- 만약에 DISTINCT가 표시되면, 중복 행 제거
이 간단한 개념적 평가 전략은 질의의 답에 표현되어야 하는 행들을 명시적이 되도록 한다. 그러나, 그것은 매우 비효율적이다. 다음 개념적 평가 전략의 예시를 보자.
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid = R.sid AND R.bid=103;
첫 번째 단계는 크로스 프로덕트 S $\times$ R을 구성하는 것이고, 다음과 같다.
두 번째 단계는 자격 조건 S.sid = R.sid AND R.bid=103을 적용하는 것이다. 이 단계는 위 인스턴스로부터 마지막 행을 제외한 모든 것을 제거한다. 세 번째 단계는 원하지 않는 열을 제거하는 것이다. SELECT 절에 단지 sname 만이 나타난다. 이 단계는 다음과 같은 한 열과 단 한 행을 가진 결과가 된다.
기본 SQL 질의의 예제
범위 변수는 FROM절에 한 릴레이션이 여러 번 나타날 때에만 명시적으로 소개될 필요가 있다. 그러나, 질의를 읽기 쉽게 하기 위하여 범위 변수와 범위 변수를 가진 모든 발생 열들의 완전한 자격 조건을 명시적으로 사용하도록 추천한다. 다음 예제를 보자.
SELECT sname
FROM Sailors S, Reserves R
WHERE S.sid = R.sid AND bid=103;
sid열이 Sailors와 Reserves 테이블에 공통적으로 나타나므로, 그 열의 존재만 제한되어야한다. 이 질의는 다음과 동일하다.
SELECT sname
FROM Sailors, Reserves
WHERE Sailors.sid = Reserves.sid AND bid=103;
이 질의는 테이블 이름들이 묵시적으로 행 변수로 사용될 수 있음을 보여준다.
적어도 한 배를 예약한 적이 있는 뱃사람의 이름을 구해보자.
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid = R.sid;
SELECT 명령어에서 수식과 문자열
select-list의 각 항은 수식 AS 열-이름 의 형식으로 될 수 있는데, 여기에서 수식은 열 이름 및 상수들로 구성된 산술식 또는 문자열 수식이며, 열-이름은 질의의 출력에 나타나는 이 열의 새로운 이름이다. 이것은 sum이나 count와 같은 집단 함수들도 포함할 수 있다. 다음 예제를 보자. 한날에 서로 다른 두 배를 운행한 사람의 등급에 대한 증가치를 계산하시오.
SELECT S.sname, S.rating+1 AS rating
FROM Sailors S, Reserves R1, Reserves R2
WHERE S.sid-R1.sid AND S.sid=R2.sid
AND R1.day=R2.day AND R1.bid <> R2.bid;
또한 자격 조건의 각 항은 수식1=수식2와 같이 일반화 될 수 있다.
SELECT S1.sname AS name1, S2.sname AS name2
FROM Sailors S1, Sailors S1
WHERE 2*S1.rating = S2.rating-1;
SQL은 LIKE 연산자를 통하여 형태 부합에 대한 지원을 제공하는데, %와 _를 함께 사용한다. 따라서 ‘_AB%’는 두 번째와 세 번째 문자가 A와 B이고, 적어도 세개의 문자로 구성되는 모든 문자열과 형태를 부합됨을 나타낸다. 다음 예제를 보자.
이름이 B로 시작해서 B로 끝나고 세 자 이상인 뱃사람의 나이를 구하시오.
SELECT S.age
FROM Sailors S
WHERE S.sname LIKE 'B_%B';
위와 같은 뱃사람은 Bob이며, 그의 나이는 63.5이다.
UNION, INTERSECT, EXCEPT
질의의 해답이 행들의 다중 집합이므로, 합집합, 교집합, 차집합과 같은 연산을 사용하고자 하는 것은 자연스러운 일이다. SQL은 이들 연산은 UNION, INTERSECT, EXCEPT라는 이름으로 지원한다. 다음 예제를 보자.
적색 혹은 녹색 배를 예약한 적이 있는 뱃사람의 이름을 구하시오.
SELECT S.sname
FROM Sailors S, Reserves R, Boat B
WHERE S.sid = R.sid AND R.bid=B.bid
AND (B.color = 'red' OR B.color = 'green');
이 질의는 WHERE절에서 OR 접속사를 사용함으로써 쉽게 표현된다. 그러나 ‘or’ 대신에 ‘and’를 사용하면 적색이면서 녹색인 배를 예약한 뱃사람들의 이름을 구하게 될 것이다. bid가 배의 키라는 무결성 제약 조건은 한 척의 배가 동시에 두 색을 가질 수 없음을 나타내고, 따라서 OR 대신에 AND로 바꾼 앞 질의의 변화는 한상 빈 해답집합을 반환한다. 다음 예제를 보자.
적색과 녹색 배를 둘 다 예약한 적이 있는 뱃사람의 이름을 구하시오.
SELECT S.sname
FROM Sailors S, Reserves R1, Boats B1, Reserves R2, Boats B2
WHERE S.sid = R1.sid AND R1.bid = B1.bid
AND S.sid = R2.sid AND R2.bid = B2.bid
AND B1.color = 'red' AND B2.color = 'green';
위 질의는 이해하기 어렵고 매우 비 효율적이다. 이 질의에 대한 더 좋은 해결 방법은 UNION과 INTERSECT를 사용하는 것이다. 위 OR 질의는 다음과 같이 다시 작성될 수 있다.
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red'
UNION
SELECT S.sname
FROM Sailors S2, Reserves R2, Boats B2
WHERE S2.sid = R2.sid AND R2.bid = B2.bid AND B2.color = 'green'
이 질의는 적색 배를 예약한 뱃사람 집합과 녹색 배를 예약한 뱃사람 집합의 합집합을 원한다는 것을 말해 준다. 마찬가지로 AND 질의도 다음과 같이 바꿀 수 있다.
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red'
INTERSECT
SELECT S.sname
FROM Sailors S2, Reserves R2, Boats B2
WHERE S2.sid = R2.sid AND R2.bid = B2.bid AND B2.color = 'green'
이 질의에는 실제로 미묘한 결함이 포함되어 있다. 만약 B1, R2, S3에 한 사람은 적색 배를 예약했고 다른 사람은 녹색 배를 예약한 Horatio라는 이름의 뱃사람이 두 명 있다면, 비록 Horatio라 불리는 어떠한 사람도 적색 배와 녹색 배를 예약하지는 않았어도, Horario라는 이름의 결과로 나온다. 즉, 이 질이는 적색배를 예약한 사람의 이름과 녹색 배를 예약한 사람의 이름이 같으면 그 이름을 실제로 계산한다. 이 문제는 뱃사람들을 식별하기 위해 sname을 사용하고, sname은 Sailor 릴레이션의 키가 아니기 때문에 발생한다. 만약 앞의 질의에서 sname 대신 sid를 사용하면, 적색 배와 녹색 배를 모두 예약한 뱃사람의 sid 집합을 얻을 수 있다. 앞의 예제에서 EXCEPT를 사용하면 다음과 같다.
적색은 예약했지만 녹색 배는 예약하지 않은 모든 뱃사람 번호를 구하시오.
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red'
EXCEPT
SELECT S.sname
FROM Sailors S2, Reserves R2, Boats B2
WHERE S2.sid = R2.sid AND R2.bid = B2.bid AND B2.color = 'green'
만약 뱃사람 22, 64, 31이 적색 배를 예약하였고 22, 74, 31이 녹색배를 예약했다면 답은 64만 포함될 것이다.
중첩 질의
SQL의 가장 강력한 특징 중 하나는 중첩 질의이다. 중첩 질의(nested query)란 그 안에 내장된 다름 질의를 가지는 질의이다. 내장된 질의는 부질의(subquery)라고 한다. 내장된 질의는 그 자체가 물론 중첩 질의일 수 있다. 부질의는 일반적으로 한 질의의 WHERE 절 내에 나타난다. 부질의들은 가끔 FROM 절 혹은 HAVING 절에도 나타날 수 있다.
중첩 질의 소개
다음 예제를 보자.
배 번호 103을 예약한 적이 있는 뱃사람의 이름을 구하시오.
SELECT S.sname
FROM Sailors S
WHERE S.sid IN ( SELECT R.sid
FROM Reserves R
WHERE R.bid = 103);
중첩 부질의는 배 103을 예약한 뱃사람들에 대한 sid의 집합을 계산해 내며, 최상층의 질의는 이 집합에 속하는 sid에 대한 뱃사람들의 이름을 검색한다. 배 103을 예약하지 않은 모든 뱃사람을 찾기 위해 이 질의를 고치는 것은 매우 쉽다. IN을 NOT IN으로 바꾸면 된다.
중첩 질의를 이해하는 가장 좋은 방법은 개념적 수행 전략 관점으로 생각해 보는 것이다. 예제에서, 전략은 Sailors 행들을 조사하고, 각 행에 대해서 Reserves에 대한 부질의를 계산한다.
상호 관련된 중첩 질의
다음 예제를 살펴보자.
배 번호 103을 예약한 적이 있는 뱃사람의 이름을 구하시오.
SELECT S.sname
FROM Sailors S
WHERE EXISTS ( SELECT *
FROM Reserves R
WHERE R.bid = 103
AND R.sid = S.sid);
EXISTS 연산자는 IN과 같은 종류의 집합 비교 연산자이다. EXISTS는 공집합과 묵시적 비교로서 집합이 공집합이 아닌지를 점검한다. 따라서 각 뱃사람 열 S에 대해서, R.bid = 103 AND S.sid = R.sid를 만족하는 Reserves 행 R의 집합이 공집합이 아닌지를 점검한다. 공집합이 아니라면, 뱃사람 S는 배 103을 예약했고, 그 이름을 검색하게 된다.
EXISTS 대신 NOT EXISTS를 사용하면, 적색 배를 예약하지 않은 뱃사람의 이름을 계산할 수 있다. EXISTS와 밀접하게 관련된 것으로 UNIQUE 술어가 있다. UNIQUE를 부질의에 적용할 떄, 부질의의 답에 아따힌 행도 두 번 이상 나타나지 않으면(중복이 없다면), 결과 조건은 true를 반환한다.
집합-비교 연산자
집합-비교 연산자 EXISTS, IN, UNIQUE와 그들의 부정 버전을 살펴보았다. SQL은 op ANY와 op ALL도 지원하며, 여기서 op는 산술 비교 연산자 {$<, <=, =, <>, >=, >$} 중 하나이다. 다음 예제를 보자
Horatio라 불리는 어떤 뱃사람들보다 등급이 더 높은 뱃사람을 구하시오.
SELECT S.sid
FROM Sailors S
WHERE S.rating > ANY ( SELECT S2.raitng
FROM Sailors S2
WHERE S2.sname = 'Horatio');
만약 Horatio라는 뱃사람이 한 명도 없으면 S.rating > ANY …는 false을 반환하기 위해 정의되고, 그 질의는 빈 해답 집합을 반환한다. ANY를 ALL로 바꾸면 다음과 같은 예제가 된다.
Horatio라 불리는 모든 뱃사람들보다 등급이 더 높은 뱃사람을 구하시오.
중첩 질의의 추가 예제
INTERSECT 연산자를 사용하여 앞에서 설명한 질의를 다시 다루어 보자.
적색과 녹색 배를 둘 다 예약한 적이 있는 뱃사람의 이름을 구하시오.
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red'
AND S.sid IN ( SELECT S2.sid
FROM Sailors S2, Boats B2, Reserves R2
WHERE S2.sid = R2.sid AND R2.bid = B2.bid
AND B2.color = 'green');
이 질의는 “적색 배를 예약하고, 동시에 녹색 배를 예약한 뱃사람들의 sid 집합에 포함된 sid를 갖는 모든 뱃사람들을 구하시오.”처럼 이해될 수 있다. EXCEPT를 사용하는 질의들은 마찬가지로 NOT IN을 사용하여 재 작성될 수 있다. 적색 배를 예약했지만 녹색배를 예약하지 않은 뱃사람들의 sid를 구하기 위해, 앞의 질의에서 단순히 IN 대신에 NOT IN을 사용할 수 있다.
모든 배를 예약한 적이 있는 뱃사람의 이름을 구해보자.
SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS (( SELECT B.bid
FROM Boats B)
EXCEPT
(SELECT R.bid
FROM Reserves R
WHERE R.sid = S.sid));
집단 연산자
집단 값을 계산하기 위한 강력한 구성들을 살펴보자. SQL은 다음과 같은 집단 연산을 제공하는데, 릴레이션의 어떤 열 A에 대해 적용될 수 있다.
- COUNT(*): 레코드의 개수
- COUNT([DISTINCT] A): A 열의 (유일한) 값들의 수
- SUM([DISTINCT] A): A 열의 모든(유일한) 값들의 합
- AVG([DISTINCT] A): A 열의 모든(유일한) 값들의 평균.
- MAX(A): A 열의 최대값
- MIN(A): A 열의 최소값
다음 예제들을 보자.
뱃사람의 수를 계산하시오
SELECT COUNT(*)
FROM Sailors S
모든 열들에 대한 단축 표현으로서 *를 생각할 수 있다.
등급 10을 가진 모든 뱃사람들의 평균 나이를 구하시오.
SELECT AVG(S.age)
FROM Sailors S
WHERE S.rating = 10
서로 다른 뱃사람 이름의 수를 계산하시오.
SELECT COUNT (DISTINCT S.sname)
FROM Sailors S
가장 나이가 많은 뱃사람의 이름과 나이를 구하시오.
SELECT S.sname, MAX(S.age)
FROM Sailors S
이 질의의 의도는 최대 나이분 아니라 그 나이를 가진 뱃사람의 이름도 반환하는 것이다. 그러나, 이 질의는 SQL에서 illegal이다. 만약 SELECT 절이 집단 연산을 사용하면, 그 질의가 GROUP BY 절을 포함하지 않는 한 SELECT 절에 오직 집단 연산만 사용해야 한다. 그러므로 SELECT 절에서 MAX(S.age)와 S.sname을 함께 사용할 수 없다. 이 질의의 적합한 답은 다음과 같다.
SELECT S.sname, S.age
FROM Sailors S
WHERE S.age = ( SELECT MAX (S2.age)
FROM Sailors S2);
다음 질의는 위와 같지만 SQL 표준에서는 합법적이지만, 많은 시스템이서 제공하지 않는다.
SELECT S.sname, S.age
FROM Sailors S
WHERE ( SELECT MAX (S2.age)
FROM Sailors S2 ) = S.age
GROUP BY와 HAVING 절
지금까지 집단 연산을 한 릴레이션의 모든 행들에 적용하였다. 릴레이션의 행들에 대한 각 그룹별로 집단 연산을 적용하고 싶은 경우도 가끔 있는데, 이때 그룹의 수는 릴레이션의 인스턴스에 따라 좌우된다. 다음 질의를 살펴보자.
각 등급 단계별 가장 어린 뱃사람의 나이를 구하시오. 만일 등급이 1에서 10까지 범위의 정수로 되어 있다는 것을 안다면, 다음과 같은 질의를 10개 작성할 수 있다.
SELECT MIN(S.age)
FROM Sailors S
WHERE S.rating = i
여기서 i=1, 2, …, 10이다. 이러한 질의를 작성하기 위해, 기본적인 SQL 질의 형식을 크게 확장할 필요가 있고, 그것이 바로 GROUP BY 절이다. 확장은 그룹에 대한 조건을 표시하기 위해 사용될 수 있는 선택적 HAVING 절을 포함한다. 이와 같은 확장을 가진 SQL 질의의 일반적인 형식은 다음과 같다.
SELECT [DISTINCT] target-list
FROM relation-list
WHERE qualification
GROUP BY grouping-list
HAVING group-qualification
GROUP BY절을 사용하여 위 예제를 다음과 같이 작성할 수 있다.
SELECT S.rating, MIN(s.age)
FROM Sailors S
GROUP BY S.rating
이 절에 대해 몇가지 중요한 점을 살펴보자.
- SELECT 절의 target-list는 (1) 열 이름 리스트 (2) aggop (열-이름) AS new-name의 형태를 가진 항들의 리스트로 구성된다. (1)에 나타나는 모든 열은 grouping-list에도 나타나야 한다. 그 이유는 질의의 결과로 나오는 각 행들은 하나의 그룹에 해당하는데, 그 그룹은 grouping-list의 열들의 값과 일치하는 행들의 모임이다. 일반적으로, 만일 한 열이 grouping-list에는 나타나지 않지만 리스트 (1)에는 나타난다면, 이 열에서 다른 값들을 가지는 한 그룹 내에 다중 행이 나타날 수 있고, 답 행의 이 열에 어떠한 값이 배정되어야 할지 불분명해진다.
- 한 열은 각 그룹 내의 모든 행에 유일한 값을 갖도록 하기 위해 주 키 정보를 사용할 수 있다. 예를 들면, grouping-list가 relation-list에 있는 테이블의 주 키를 포함한다면, 그 테이블의 모든 열은 각 그룹 내에서 유일한 값을 가진다.
- HAVING 절의 group-qualification에서 나타나는 수식들은 그룹 당 하나의 값만을 가져야한다. group-qualification에 나타나는 한 열은 집단 연산자의 매개변수 형태로 나타나거나, grouping-list에 나타나야 한다.
- GROUP BY 절이 생략되면, 전체 테이블은 하나의 그룹으로 간주된다.
다음 예제를 보자.
두 명 이상의 뱃사람을 가지는 등급에 대해 각 등급별 투표가 가능한(18세 이상) 가진 어린 뱃사람의 나이를 구하시오.
SELECT S.rating, MIN(S.age) AS minage
FROM Sailors S
WHERE S.age >= 18
GROUP BY S.rating
HAVING COUNT(*) > 1
첫 단계는 relation-list에 있는 테이블들의 크로스 프로덕트를 구성한다. 위 질의는 Sailors 릴레이션만 있으므로, 그 결과는 위와 동일하다. 두 번째 단계는 WHERE 절의 조건식 S.age>=18을 적용하는 것이다. 세 번째 단계는 필요 없는 열들을 제거하는 것이다. SELECT, GROUP BY, HAVING 절에서 언급된 열들만 필요하며, 이 예에서는 sid, sname을 제거할 수 있다. 그 결과는 다음과 같다.
위에서 rating 3와 age 25.5를 가진 두 개의 같은 행이 있다. SQL은 DISTINCT 키워드의 사용에 의한 경우를 제외하고는 중복을 제거하지 않는다. 네 번째 단계는 그룹들을 식별하기 위해서 GROUP BY절에 따라 테이블을 정렬하는 것이다. 이 단계의 결과는 다음과 같다.
다섯 번째 단계는 HAVING 절에 있는 group-qualification, 즉 COUNT(*) > 1이라는 조건을 적용하는 것이다. 이 단계는 rating이 1, 9, 10인 그룹을 제거한다. 만약 WHERE 절이 먼저 적용되지 않았다면, rating=10의 그룹이 HAVING 절의 group-qualification을 만족했을 것이다. 여섯 번째 단계는 남아 있는 각 그룹마다 하나의 답 행을 만드는 것이다. 각 답 행은 rating 열과 해당 그룹의 age 열의 값들에 MIN을 적용하여 계산된 minage 열로 구성된다. 이 단계의 결과는 다음과 같다.
질의가 SELECT 절에 DISTINCT를 포함하면, 중복은 부가적인 마지막 단계에서 제거된다.
두 새로운 집합 함수 EVERY와 ANT가 있는데 이들 함수를 나타내기 위하여, 위 예에서 HAVING 절을 다음과 같이 바꿀 수 있다.
HAVING COUNT(*) > 1 AND EVERY(S.age<=60)
위에서 다섯 번째 단계의 결과를 살펴보자. EVERY 키워드는 그룹의 모든 행이 group-qualification을 만나기 위해서는 추가된 조건을 만족해야 함을 요구한다. rating 3에 대한 그룹은 이 기준을 만나고 삭제된다. 결과는 다음과 같다.
앞의 질의를 age조건이 HAVING 절 대신에 WHERE 절에 있는 다음 질의와 대조해본다.
SELECT S.rating, MIN(S.age) AS minage
FROM Sailors S
WHERE S.age >= 18 AND S.age<=60
GROUP BY S.rating
HAVING COUNT(*) > 1
세 번째 단계 후의 결과는 age 63.5를 이제는 포함하지 않는다. 그럼에도 불구하고, 아직 두 행을 가지므로 rating 3에 대한 그룹은 COUNT(*) > 1의 조건을 만족하고, 다섯 번째 단계에서 적용된 group-qualification을 만난다. 이 질의의 최종 결과는 다음과 같다.
집단 질의의 추가 예제
Q.각 적색 배에 대해, 이 배에 대한 예약의 수를 구하시오.
SELECT B.bid, COUNT(*) AS reservationcount
FROM Boats B, Reservers R
WHERE R.bid = B.bid AND B.color = 'red'
GROUP BY B.bid
FROM절과 WHERE절을 적용하면 다음과 같다.
이후 GROUP BY절과 SELECT절을 적용하면 다음과 같다.
위 질의에서 다음 형식이 illegal한지를 알아보자.
SELECT B.bid, COUNT(*) AS reservationcount
FROM Boat B, Reservers R
WHERE R.bid = B.bid
GROUP BY B.bid
HAVING B.color = 'red'
비록 group-qualification B.color = ‘red’가 그룹 당 하나의 값을 만들어 낼지라도, 그 애트리뷰트 bid가 Boats의 키이므로 SQL은 이 질의를 허용하지 않는다. GROUP BY절에 나타나는 열들은 HAVING 절의 집단 연산자에 대한 인수로 나타나지 않으면, HAVING 절에서만 나타날 수 있다.
Q. 두 명 이상의 뱃사람이 있는 각 등급 단계에 대해 등급별 뱃사람의 평균 나이를 구하시오.
SELECT S rating, AVG(S.age) AS avgage
FROM Sailors S
GROUP BY S.rating
HAVING COUNT(*)> 1
위 질의의 다음의 다른 형태는 HAVING 절도 WHERE 절처럼 중첩 부질의를 가질 수 있음을 나타낸다. HAVING 절의 중첩 부질의 내에서 S.rating을 사용할 수 있으며 그 이유는 뱃사람의 현재 그룹에 대한 단일 값을 갖기 때문이다.
SELECT S rating, AVG(S.age) AS avgage
FROM Sailors S
GROUP BY S.rating
HAVING 1 < ( SELECT COUNT(*)
FROM Sailors S2
WHERE S.rating = S2.rating)
Q. 뱃사람의 평균 나이가 모든 등급에 대해 최소인 등급을 구하시오
집단 연산은 중첩될 수 없다는 것을 나타내기 위하여 이 질의를 사용한다.
SELECT S.rating
FROM Sailors S
WHERE AVG(S.age) = ( SELECT MIN(AVG(S2.age))
FROM Sailors S2
GROUP BY S2.rating)
이 질의는 불법인 수식 MIN(AVG(S2.age))가 비록 허용될지라고 작동하지 않는다. 중첩 부질의에서, Sailors는 등급별로 여러 그룹으로 나뉘고, 평균 나이는 각 등급 값에 대해 계산된다. 각 그룹에 대해, 그룹별 평균 나이에 MIN의 적용은 같은 값을 반환할 것이다. 이 질의에 올바른 버전은 다음과 같다. 그것은 각 등급값에 대한 평균 나이를 포함한 임시 테이블을 계산하고 그 다음에 이 평균 나이가 최소값을 가지는 등급을 찾는다.
SELECT Temp.rating, Temp.avgage
FROM ( SELECT S.rating, AVG(S.age) AS avgage
FROM Sailors S
GROUP BY S.rating) AS Temp
WHERE Temp.avgage = (SELECT MIN (Temp.avgage) FROM Temp)
위 질의의 결과는 다음과 같다.
널 값
SQL은 null이라고 부르는 특수한 열 값을 제공한다. 행 값이 unknown 또는 적용 불가능(inapplicable)일 때 null을 사용한다. null의 존재는 많은 문제들을 복잡하게 한다.
- SQL은 어떤 열 값이 null 인지를 검사하기 위해 특수한 비교 연산자 IS NULL을 제공한다.
- 예를 들어, rating IS NULL이라고 질의할 수 있고, rating IS NOT NULL이라고도 질의할 수 있다.
- 부울식에 대해서 false면 unknown이라고만 할 수 있다.
- null 값을 가질 때, 수식이 true, false, unknown을 계산해 내는 세-값 논리(three-value logic)를 사용하여 논리 연산자 AND, OR, NOT을 정의해야 한다.
- null 값들에 의지하는 조인 연산의 한 흥미 있는 변형은 외부 조인(outer join)이라고 한다.
SQL의 복잡한 무결성 제약 조건
무결성 제약 조건은 다음이 있다.
- 도메인 제약 조건(Domain constraints)
- 키 제약 조건(Primary key constraints)
- 외래키 제약 조건(Foreign key constraints)
- 일반적인 제약 조건(General constraints)
위에서 도메인 제약 조건은 릴레이션 스키마는 릴레이션 인스턴스에 있는 각 필드(열)의 도메인을 명시한다는 조건이다. 일반적인 제약 조건은 IC 명세서가 주어지면, DBMS는 이 제약조건을 위배하는 삽입이나 갱신을 거부하는 조건이다. 이는 데이터 입력 오류을 방지하는 데에 매우 유용하다.
한 테이블 위의 제약 조건
테이블 제약 조건을 사용하여 하나의 테이블에 대해 복잡한 제약 조건을 표시할 수 있으며, 이는 CHECK 조건식의 형태를 가진다. 예를 들면, rating이 1에서 10까지 범위의 정수가 되도록 하려면, 다음과 같이 사용할 수 있다.
CREATE TABLE Sailors ( sid INTEGER,
sname CHAR(10).
rating INTEGER,
age REAL,
PRIMARY KEY (sid),
CHECK (rating >= 1 AND rating <= 10))
Interlake라는 배들은 예약할 수 없다는 제약 조건을 시행하여면, 다음과 같이 사용할 수 있다.
CREATE TABLE Reserves ( sid INTEGER,
bid INTEGER,
day DATE,
FOREIGN KEY (sid) REFERENCES Sailors,
FOREIGN KEY (bid) REFERENCES Boats,
CONSTRAINT noInterlakeRes
CHECK ( 'Interlake' <>
( SELECT B.bname
FROM Boats B
WHERE B.bid = Reserves.bid)))
도메인 제약 조건과 구별 타입
사용자가 CREATE DOMAIN 문을 사용하여 새로운 도메인을 정의할 수 있으며, 이 때 CHECK 제약 조건을 사용한다.
CREATE DOMAIN ratingval INTEGER DEFAULT 1
CHECK ( VALUE >= 1 AND VALUE <= 10)
INREGER가 도메인 ratingval에 대한 기반, 혹은 소스, 타입이며, 모든 ratingval 값은 이 타입이어햐 한다. 또한 ratingval 값들은 CHECK 제약 조건을 사용함으로서 다시 한번 제한된다. 이 제약 조건의 정의에서, 도메인에 속하는 값을 언급하기 위해 키워드 VALUE를 사용한다. 선택적인 DEFAULT 키워드는 어떤 도메인에 디폴드 값을 지정하기 위해 사용될 수 있다.
여러 테이블 위의 무결성 제약 조건
비록 CHECK 절의 조건식에서 다른 테이블들을 참조할 수 있을지라도, 테이블 제약 조건은 하나의 테이블에 관한 것이다. 테이블 제약 조건은 해당 테이블이 비어 있지 않을 떄에만 유지된다. 따라서 한 제약 조건이 둘 이상의 테이블을 포함할 때, 테이블 제약 조건 메카니즘은 가끔 번거로울 뿐 아니라 요구한 대로 되지도 않는다. 이러한 상황을 위해 SQL은 단정(assertion)의 생성을 지원하는데, 어느 한 테이블에만 국한되지 않는 제약 조건이다. 예를 들면, 배의 수와 뱃사람의 수를 합한 값이 100보다 작도록 제약 조건을 집행하고 싶다고 하자. 다음과 같은 테이블 제약 조건을 시도한다.
CREATE TABLE Sailors (
sid INTEGER,
sname CHAR(10),
rating INTEGER,
age REAL,
PRIMARY KEY (sid),
CHECK ( rating >= 1 AND rating <= 10)
CHECK ( ( SELECT COUNT (S.sid) FROM Sailors S)
+ ( SELECT COUNT (B.bid) FROM Boats B)
< 100)
)
이 질의에는 문제점이 있다. 그것은 완전히 대칭적인 방법으로 Boats를 포함할지라도 Sailors와 관련되어 있다. 또한 만약에 Sailors 테이블이 비어 있으면, 비록 Boats에 100행 이상이 있더라도 이 제약 조건은 항상 받아들이는 것으로 정의된다. 가장 좋은 해결책은 다음과 같이 하나의 단정을 만드는 것이다.
CREATE ASSERTION smallClub
CHECK( ( SELECT COUNT(S.bid) FROM Sailors S)
+ ( SELECT COUNT(B.bid) FROM Boats B)
< 100)
트리거와 능동 데이터베이스
트리거란 데이터베이스에 행해진 변경에 대응해서 DBMS에 의해 자동적으로 실행되는 일종의 프로시저로서, 보통 DBA애 의해 표시된다. 관련 트리거 집합을 가진 데이터베이스를 능동 데이터베이스라고 한다. 트리거의 명세는 세 부분을 포함한다.
- 이벤트: 트리거를 구동시키는 데이터베이스에 대한 변경 사항
- 조건: 트리거가 구동될 때 수행되는 질의 또는 검사
- 동작: 트리거가 구동되고 해당 조건이 참일 때 수행되는 프로시저
트리거는 데이터베이스를 감시하는 ‘데몬(deamon)’으로 생각될 수 있으며, 데이터베이스가 이벤트 내역과 부합하는 방향으로 수정될 때 수행된다. 다음 예제를 보자.
CREATE TRIGGER youngSailorUpdate AFTER INSERT ON Sailors
REFERENCING NEW TABLE NewSailors
FOR EACH STATEMENT
INSERT
INTO YoungSailors(sid, name, age, rating)
SELECT sid, name, age, rating
FROM NewSailors N
WHERE N.age <= 18
youngSailorUpdate라는 트리거는 Sailors 릴레이션에 투플을 추가하는 INSERT 문장의 모든 수행 전에 계수기 변수를 초기화 한다. 이러한 삽입된 레코드의 수에 상관없이 INSERT 문장 당 한번씩 실행되는 트리거를 문장-수준(statement-level) 트리거라고 한다. 키워드 절 NEW TABLE은 새로 삽입된 투플들의 집합에 테이블 이름을 부여해준다. FOR EACH STATEMENT 절은 문장-수준 트리거를 표시하며 이것이 디폴트이기 때문에 생략 가능하다.
댓글남기기