SQL : Queries, Constraints, Triggers
SQL : 질의, 제약조건, 트리거
Basic SQL Query Syntax
SELECT [DISTINCT] targer_list
FROM relation_list
WHERE qualification1) \(\texttt{target_list}\)
- Relation의 Attribute의 리스트이다.
- Attribute의 리스트는 Projection 연산의 결과이다.
\(\texttt{SELECT} \iff \pi\) (Projection)
* \(\texttt{DISTINCT}\) Option
- Query 결과 중 중복되는 값들에 대한 제거 여부를 결정하는 부분이다.
- \(\texttt{DISTINCT}\)라 명시하면, 중복되는 값들이 하나로 표현된다.
- Projection 연산 중 \(\texttt{DISTINCT}\) 옵션은 Cost를 높이는 원인이 되기도 한다.
(중복성을 제거하는 과정에서의 시간 소모)
- Key 속성을 \(\texttt{SELECT}\)하는 경우에 \(\texttt{DISTINCT}\) 옵션을 추가하면, 결과는 달라지지 않고, 성능만 나빠지니 유의하자.
(Key 속성은 이미 중복되지 않는 값이기 때문이다.)
2) \(\texttt{relation_list}\)
- 질의의 대상이 되는 테이블이다. (1개 이상)
- Relation의 이름 뒤에 Range-Variable을 추가할 수 있다.
3) \(\texttt{qualification}\)
- 조건식이다.
- \(\texttt{qualification}\)은 두 가지 형태의 Term으로 표현될 수 있다.
a) attr OP const → Selection Operation
b) attr1 OP attr2 → Join Operation
\(\texttt{WHERE} \iff \sigma\)(Selection)
- 질의 대상 테이블이 하나인 경우, Selection 연산이 수행될 수 있다.
\(\texttt{WHERE} \iff \sigma\)(Selection), ▷◁(Join)
- 질의 대상 테이블이 다수인 경우, Selection 연산 혹은 Join 연산이 수행될 수 있다.
Conceptual Evaluation Strategy (이론적인 SQL 질의 처리 전략)
1) FROM절의 relation_list에 있는 Relation들을 Cross-Product한다. (Cross-Product)
2) WHERE절의 qualifications를 적용해서 조건에 맞는 Tuple들만 결과에 남기고, 그렇지 않은 Tuple들은 제거한다. (Selection)
3) Projection을 통해 target_list에서 필요한 Attribute만 저장한다. (Projection)
4) 만약, DISTINCT 옵션이 명시되었다면, 중복되는 Rows들을 제거한다.
- 위 프로세스는 효율적인 방법이 아니며, 보다 최적화 될 여지가 있다.
- SQL만으로는 Query Optimizing이 불가능하며, Relational Algebra로의 변환이 이루어져야 최적화가 가능해진다.
Example. Conceptual Evaluation
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid = R.sid AND R.bid = 103
-- 'rusty'가 리턴된다.
-- 위 SQL에서 S, R은 Range-Variable이다.
| (sid) | sname | rating | age | (sid) | bid | day |
| 22 | dustin | 7 | 45.0 | 22 | 101 | 10/10/96 |
| 22 | dustin | 7 | 45.0 | 58 | 103 | 11/12/96 |
| 31 | lubber | 8 | 55.5 | 22 | 101 | 10/10/96 |
| 31 | lubber | 8 | 55.5 | 58 | 103 | 11/12/96 |
| 58 | rusty | 10 | 35.0 | 22 | 101 | 10/10/96 |
| 58 | rusty | 10 | 35.0 | 58 | 103 | 11/12/96 |
* 위 Table은 \(S \times R\)의 결과이다.
Conceptual Strategy:
\(\pi_{sname} (\sigma_{S.sid=R.sid \; \land \; R.bid=103} \; (S \times R))\)
- 비효율적이다.
Optimized Way:
\(\pi_{sname}((\sigma_{bid=103} \; Reserves)▷◁Sailors)\)
- 효율적이다.
Range-Variables (범위 변수)
- FROM 절에서 같은 Relation이 두 번 이상 사용될 때, 범위 변수를 통해 각각을 구분지을 수 있다.
- 다수의 테이블에서 질의를 할 때, 범위 변수를 사용하지 않으면 착오가 생길 수 있다.
- Correlation Query(다수의 같은 타입의 테이블을 조합하는 쿼리)시에 필수적이다.
- 같은 두 Table을 Self-Join하는 경우에도 범위 변수는 용이하게 사용된다.
- 가독성 등의 이유로, 범위 변수의 사용이 권장된다.
Example. Range-Variables를 사용한 경우와 그렇지 않은 경우
-- Range-Variables를 사용한 경우 (Good Style)
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid=R.sid AND bid=103
-- Range-Variables를 사용하지 않은 경우 (Bad Style)
SELECT sname
FROM Sailors, Reserves
WHERE Sailors.sid=Reserves.sid AND bid=103Example. Find sailors who've reserved at least one boat.
※ Primary Key를 SELECT할 때, DISTINCT 옵션을 추가하면 결과는 달라지지 않고,
부가적인 연산(Sorting, Hashing 등)만 더 수행하여 Performance를 저해한다.
※ 반대로, Foreign Key를 SELECT할 때, DISTINCT 옵션을 추가하면 결과는 달라질 수 있다.
※ 또한, 중복이 허용된 Field 값을 SELECT할 때, DISTINCT 옵션을 추가하는 것에는 신중해야 한다.
Given Tables:
| Reserves R1 | ||
| sid (FK) | bid | day |
| 22 | 101 | 10/10/96 |
| 58 | 103 | 11/12/96 |
| 22 | 103 | 20/10/97 |
| Sailors S1 | |||
| sid (PK) | sname | rating | age |
| 22 | dustin | 7 | 45.0 |
| 28 | yuppy | 9 | 35.0 |
| 31 | lubber | 8 | 55.5 |
| 44 | guppy | 5 | 35.0 |
| 58 | rusty | 10 | 35.0 |
SELECT S1.name
FROM Sailors S1, Reserves R1
WHERE S1.sid=R1.sidOutput:
| dustin |
| rusty |
| dustin |
SELECT DISTINCT S1.name
FROM Sailors S1, Reserves R1
WHERE S1.sid=R1.sidOutput:
| dustin |
| rusty |
SELECT R1.sid
FROM Reserves R1
-- 만약 최소 하나의 배를 예약한 선원의 sid를 요구하는 경우,
-- 굳이 Sailors 테이블과 Join할 필요가 없다.Output:
| 22 |
| 58 |
| 22 |
SELECT DISTINCT R1.sid
FROM Reserves R1
-- DISTINCT 옵션이 추가되어 중복성이 제거된다.
-- R1.sid는 Foreign Key이다.Output:
| 22 |
| 58 |
SELECT DISTINCT S1.sid
FROM Sailors S1, Reserves R1
WHERE S1.sid=R1.sidOutput:
| 22 |
| 58 |
SELECT DISTINCT S1.sid
FROM Sailors S1, Reserves R1
WHERE S1.sid=R1.sid
-- sid는 S1에서의 PK이므로, DISTINCT 옵션을 부여해도 결과는 달라지지 않고,
-- 필요없는 연산(Sorting, Hashing)만 더 수행하게 된다.Output:
| 22 |
| 58 |
Expressions and Strings
- SQL은 Expression(수식)과 String(문자열)을 처리할 수 있는 기능을 제공한다.
※ Variable은 =의 우변에 위치할 수 없다. (우변에는 값이 정해진 상수만 위치할 수 있다.)
Example.
SELECT S.age, age1 = S.age-5, 2*S.age AS age2
FROM Sailors S
WHERE S.sname LIKE 'B_%B' SELECT Clause
- age1, age2는 Variable이다. (S의 Range Variable이 아니다.)
(변수를 이용하여 수식으로 나타낼 수 있다.)
- AS는 :=(Assignment)를 의미한다.
※ age1은 Field가 아닌, Variable이므로 아래와 같은 표현은 DBMS가 처리할 수 없다.
age2 = age1 - 5
WHERE Clause
- LIKE는 ==(Equal)를 의미한다.
- SQL에서 문자열은 작은 따옴표(')로 둘러싸인다.
- _는 임의의 한 문자를 의미한다.
- %는 임의의 0개 이상의 문자를 의미한다.
- 즉, 'B_%B'는 B로 시작해서 B로 끝나되, 그 사이에 1개 이상의 아무 문자나 올 수 있는, 길이가 3이상인 문자열을 의미한다.
(BoB, BooaB, BxyB 모두 위 문자열 기준에 부합한다. BB, BulC는 부합하지 않는다.)
※ 경우에 따라, Case Sensitive한 경우 대소문자를 구분해야 하고, 그렇지 않은 경우 대소문자를 구분짓지 않아도 될 수 있다.
ex) \(\texttt{S.sname} \leq \texttt{C%}\)
- S.sname값들 중, A, B, C로 시작하는 모든 문자열들을 지칭하는 조건이다.
- ASCII Code에 의한 Alpha Numeric Representation으로 인해 가능한 표현이다.
Output:
| S.age | age1 | age2 |
| \(\vdots\) | ||
Example. Find sid's of sailors who've reserved a red or a green boat
Given Tables:
| Reserves R1 | ||
| sid (FK) | bid | day |
| \(\vdots\) | ||
| Sailors S1 | |||
| sid (PK) | sname | rating | age |
| \(\vdots\) | |||
| Boats B1 | ||
| bid (PK) |
bname | color |
| \(\vdots\) | ||
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid AND (B.color='red' OR B.color='green')- 위 SQL과 아래 SQL은 동일한 작업을 수행한다.
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color='red'
UNION -- UNION : 합집합, INTERSECT : 교집합
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color='green'Example. Find sid's of sailors who've reserved a red and a green boat
SELECT S.sid
FROM Sailors S, Boats B1, Reserves R1, Boats B2, Reserves R2
WHERE S.sid=R1.sid AND R1.bid=B1.bid
AND S.sid=R2.sid AND R1.bid=B1.bid
AND (B1.color='red' AND B2.color='green')- 위 SQL과 아래 SQL은 동일한 작업을 수행한다.
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color='red'
INTERSECT
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color='green'- 또한, 위 SQL을 Nested Query를 이용하여 아래와 같이 나타낼 수도 있다.
SELECT S.sid
FROM Sailors S, Boats B, Reserves R
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')Nested Query (중첩 질의)
- 질의가 포함된 질의를 의미한다.
- DBMS는 Sub-Query(내부의 질의)를 먼저 처리한 다음, 외부의 질의를 처리한다.
Example. Nested Query
SELECT S.sname
FROM Sailors S
WHERE S.sid IN (SELECT R.sid
FROM Reserves R
WHERE R.bid=103)IN
- S.sid가 Sub-Query의 결과에 포함된 경우를 표현할 때 사용한다.
NOT IN
- S.sid가 Sub-Query의 결과에 포함되지 않는 경우를 표현할 때 사용한다.
Example. Nested Query with ANY
SELECT *
FROM Sailors S
WHERE S.rating > ANY (SELECT S2.rating
FROM Sailors S2
WHERE S2.sname='Horatio')- Sub-Query의 결과들 중 적어도 하나보다는 큰 S.rating을 갖는 Tuple을 질의한다.
- Sub-Query의 결과가 (2, 5, 7)이라면 S.rating이 2보다 큰 모든 Tuple들이 리턴된다.
Example. Nested Query with ALL
SELECT *
FROM Sailors S
WHERE S.rating > ALL (SELECT S2.rating
FROM Sailors S2
WHERE S2.sname='Horatio')- Sub-Query의 어떠한 결과보다도 큰 S.rating을 갖는 Tuple을 질의한다.
- Sub-Query의 결과가 (2, 5, 7)이라면 S.rating이 7보다 큰 모든 Tuple들이 리턴된다.
* "= ALL"의 경우, S.rating이 2이면서 5이면서 7인 Tuple을 질의하는 것이 되기 때문에, 만족하는 결과가 없다.
* "= ANY"의 경우, S.rating이 2, 5, 7 셋 중 하나인 Tuple을 질의하는 것이 된다. 질의에 문제가 없다.
※ Comparison Operator
>, <, =, ≥, ≤, ≠
Nested Query with Correlation
- Sub-Query에서 외부 Query의 Range Variable을 사용하는 형태의 Nested Query를 의미한다.
- 외부 Query의 Range Variable을 Sub-Query가 사용하기 때문에, 일반적인 Nested Query와 달리,
DBMS는 Sub-Query를 먼저 처리할 수 없다.
- Nested Loop를 처리할 때와 같이, 외부 Range Variable에 해당되는 한 Tuple마다의 Sub-Query를 처리하는 방식으로 진행한다.
Example. Nested Query with Correlation
SELECT S.sname
FROM Sailors S
WHERE EXISTS (SELECT *
FROM Reserves R
WHERE R.bid=103 AND S.sid=R.sid)- 첫 번째 S.sid에 대한 Sub-Query를 처리하고,
두 번째 S.sid에 대한 Sub-Query를 처리하고, ...와 같은 방식으로 처리된다.
- 일반적인 Nested Query와 달리, Sub-Query가 한 번에 처리되지 않는다.
EXISTS
- Sub-Query의 결과에 존재하는 경우를 표현할 때 사용한다.
- IN과 같은 개념이다.
NOT EXISTS
- Sub-Query의 결과에 존재하지 않는 경우를 표현할 때 사용한다.
- NOT IN과 같은 개념이다.
- EXCEPT와 같은 개념이다.
UNIQUE
- Duplicate Tuple을 처리할지에 대한 여부를 표현할 때 사용한다.
Division in SQL
Example. SQL with Division
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) )- 모든 배에서 모든 예약된 배를 제외시킨 집합에 포함되지 않는 배를 가진 선원의 이름을 질의한다.
- 즉, 모든 예약된 배 중 하나 이상을 예약한 선원의 이름을 질의한다.
SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS (SELECT B.bid
FROM Boats B
WHERE NOT EXISTS (SELECT R.bid
FROM Reserves R
WHERE R.bid=B.bid
AND R.sid=S.sid) )Aggregate Operator (집계 연산자)
* 집계 연산의 종류
COUNT
- Query에서 도출되는 Record들의 개수를 리턴한다.
- DISTINCT 옵션을 설정할 수 있다.
SUM
- Query에서 도출되는 Field 값들의 합을 리턴한다.
- DISTINCT 옵션을 설정할 수 있다.
AVG
- Query에서 도출되는 Field 값들의 평균을 리턴한다.
- DISTINCT 옵션을 설정할 수 있다.
MAX
- Query에서 도출되는 Field 값들 중 최댓값을 리턴한다.
MIN
- Query에서 도출되는 Field 값들 중 최솟값을 리턴한다.
※ 집계 연산자 간에는 중첩이 허용되지 않는다. (SQL/92 기준)
- 즉, MIN( AVG( ~) )와 같은 표현은 허용되지 않는다.
Example. Aggregate Operator
SELECT COUNT(*)
FROM Sailors S
-- S 테이블에 존재하는 Record의 개수를 질의한다.
SELECT AVG(S.age)
FROM Sailors S
WHERE S.rating=10
-- rating 값이 10인 Record들의 age 평균값을 질의한다.
SELECT S.sname
FROM Sailors.S
WHERE S.rating=(SELECT MAX(S2.rating)
FROM Sailors S2)
-- S2 테이블에서의 최대 rating값과 동일한 값을 가진 Record를 S 테이블에서 찾고, 그 Record의 sname을 질의한다.
SELECT COUNT(DISTINCT S.rating)
FROM Sailors S
WHERE S.sname='Bob'
-- S 테이블에서 sname값이 'Bob'인 Record의 개수를 질의한다. 이 때, rating값이 같은 Record들에 대한 중복성은 제거한다.
SELECT AVG(DISTINCT S.age)
FROM Sailors S
WHERE S.rating=10
-- S 테이블에서 rating값이 10인 Record들의 age 평균값을 질의한다. 이 때, age값이 같은 Record들에 대한 중복성은 제거한다.
-- 즉, age 값이 같은 Record들이 여럿 있을 경우, 평균값에 한 번만 반영된다.Example. Find name and age of the oldest sailor(s)
1) Illegal Solution
SELECT S.sname, MAX(S.age)
FROM Sailors S- 나이가 가장 많은 Sailor의 sname과 age만 출력되는것이 아닌,
SELECT S.sname, S.age FROM Sailors S 와 같은 결과가 출력된다.
- 즉, 테이블 S 전체의 sname과 age가 출력되므로, 질의의 의도와 맞지 않는 결과가 초래된다.
- Grouping(GROUP BY)을 통해 해결할 수 있다.
2) Solution
SELECT S.sname, S.age
FROM Sailors S
WHERE S.age = (SELECT MAX(S2.age)
FROM Sailors S2)- 나이가 가장 많은 Sailor의 sname과 age가 정상적으로 출력된다.
3) Solution
SELECT S.sname, S.age
FROM Sailors S
WHERE (SELECT MAX(S2.age)
FROM Sailors S2)
= S.age- 2)번 Solution과 동치이다.
- 즉, 3)번 Query 또한 나이가 가장 많은 Sailor의 sname과 age가 정상적으로 출력된다.
- 3)번 Solution은 SQL/92 표준에서는 지원되나, 다른 몇몇 시스템에서는 지원되지 않을 수 있다.
Grouping
SELECT [DISTINCT] target-list
FROM relation-list
WHERE qualification
GROUP BY grouping-list
HAVING group-qualificationgruoping-list
- Attribute List의 Subset이다.
group-qualification
- Group을 구성하기 위한 조건이다.
* Conceptual Evaluation Process with GROUP BY and HAVING
1) relation-list들을 Cross-Product한다. (FROM 절)
2) qualification을 만족시키지 못하는 Tuple들을 제거한다. (WHERE 절)
- WHERE절을 비워둔채로 GROUP BY 절에서만 qualification을 명시할 수 있다.
3) target_list에 명시한 Field만 남기고, 나머지 Column들은 제거한다. (집계 함수를 제외한 SELECT 절)
4) grouping-list에 명시한 Field들을 기준으로 Grouping한다. (GROUP BY 절)
- GROUP BY 절에 있는 Field는 반드시 SELECT 절에도 위치해 있어야 한다.
5) gruop-qualification을 만족시키지 못하는 Group을 제거한다. (HAVING 절)
6) 마지막으로, SELECT절에 집계 함수가 위치해있는 경우, 그 집계 함수 값을 계산한다. (SELECT 절의 집계 함수)
- 집계 함수는 Groping이 다 완수되고 나서야 계산될 수 있다.
Example. Find age of the youngest sailor with age ≥ 18, for each rating with at least 2 such sailors.
Sailors Instance:
| sid (PK) | sname | rating | age |
| 22 | dustin | 7 | 45.0 |
| 29 | brutus | 1 | 33.0 |
| 31 | lubber | 8 | 55.5 |
| 32 | andy | 8 | 25.5 |
| 58 | rusty | 10 | 35.0 |
| 64 | horatio | 7 | 35.0 |
| 71 | zorba | 10 | 16.0 |
| 74 | horatio | 9 | 35.0 |
| 85 | art | 3 | 25.5 |
| 95 | bob | 3 | 63.5 |
| 96 | frodo | 3 | 25.5 |
Query:
SELECT S.rating, MIN(S.age) AS minage
FROM Sailors S
WHERE S.age >= 18
GROUP BY S.rating -- 같은 rating값을 갖는 Tuple들은 한 그룹에 속한다.
HAVING COUNT(*) > 1
Grouping Process:
- GROUP BY 절을 수행하고, HAVING 절을 수행하여 Output을 도출해내는 과정이다.
Output:
| rating | minage |
| 3 | 25.5 |
| 7 | 35.0 |
| 8 | 25.5 |
Example. Find age of the youngest sailor with age ≥ 18, for each rating with at least 2 such sailors and with every sailor under 60.
Sailors Instance:
| sid (PK) | sname | rating | age |
| 22 | dustin | 7 | 45.0 |
| 29 | brutus | 1 | 33.0 |
| 31 | lubber | 8 | 55.5 |
| 32 | andy | 8 | 25.5 |
| 58 | rusty | 10 | 35.0 |
| 64 | horatio | 7 | 35.0 |
| 71 | zorba | 10 | 16.0 |
| 74 | horatio | 9 | 35.0 |
| 85 | art | 3 | 25.5 |
| 95 | bob | 3 | 63.5 |
| 96 | frodo | 3 | 25.5 |
Query:
SELECT S.rating, MIN(S.age) AS minage
FROM Sailors S
WHERE S.age >= 18
GROUP BY S.rating
HAVING COUNT(*) > 1 AND EVERY(S.age <= 60)
Grouping Process:
* EVERY 조건이 아닌, ANY 조건인 경우에는 rating=3인 Group도 Output에 포함된다.
Output:
| rating | minage |
| 7 | 35.0 |
| 8 | 25.5 |
Example. Find age of the youngest sailor with age ≥ 18, for each rating with at least 2 sailors between 18 and 60.
Sailors Instance:
| sid (PK) | sname | rating | age |
| 22 | dustin | 7 | 45.0 |
| 29 | brutus | 1 | 33.0 |
| 31 | lubber | 8 | 55.5 |
| 32 | andy | 8 | 25.5 |
| 58 | rusty | 10 | 35.0 |
| 64 | horatio | 7 | 35.0 |
| 71 | zorba | 10 | 16.0 |
| 74 | horatio | 9 | 35.0 |
| 85 | art | 3 | 25.5 |
| 95 | bob | 3 | 63.5 |
| 96 | frodo | 3 | 25.5 |
Query:
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- Grouping 이전에 WHERE 절에서 qualification이 모든 Tuple에 적용됨에 유의하자.
- 이전 예제에서들과 달리, 이번 예제에서는 rating=3인 Group에서 Tuple의 개수는 3이 아닌, 2이다.
Output:
| rating | minage |
| 3 | 25.5 |
| 7 | 35.0 |
| 8 | 25.5 |
* GROUP BY 절에 조건이 위치하는 경우의 Query와 Output
SELECT S.rating, MIN(S.age) AS minage
FROM Sailors S
WHERE S.age >= 18
GROUP BY S.rating
HAVING COUNT(*) > 1 AND (S.age >= 18 AND S.age <= 60)
Output:
| rating | minage |
| 3 | 25.5 |
| 7 | 35.0 |
| 8 | 25.5 |
Example. For each red boat, find the number of reservations for this boat.
(red 색상 배 각각에 대한 예약 건수 질의)
SELECT B.bid, COUNT(*) AS scount
FROM Sailors S, Boats B, Reserves R
WHERE S.sid=R.sid AND R.bid=B.bid AND B.color='red'
GROUP By B.bidExample. Find age of the youngest sailor with age > 18, for each rating with at least 2 sailors (of any age)
SELECT S.rating, MIN(S.age)
FROM Sailors S
WHERE S.age > 18
GROUP BY S.rating
HAVING 1 < (SELECT COUNT(*)
FROM Sailors S2
WHERE S.rating=S2.rating)
-- HAVING 절을 아래로 대치해도, 결과는 같다.
-- HAVING COUNT(*) > 1Example. Find those ratings for which the average age is the minimum over all ratings.
1) Wrong Solution
SELECT S.rating
FROM Sailors S
WHERE S.age = (SELECT MIN( AVG(S2.age) ) FROM Sailots S2)- 집계 연산자는 중첩이 불가하므로, 위와 같은 SQL은 허용되지 않는다.
2) Correct Solution (SQL/92)
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.abgage = (SELECT MIN(Temp.avgage)
FROM Temp)Null Values
- 한 Record의 특정 Field 값이 Unknown(알 수 없음)하거나, Inapplicable(적용 불가)한 경우에,
SQL에서는 해당 Field에 Null값을 부여할 수 있다.
- 대소 비교 연산 시, 해당 Field에 Null 값이 있다면, 3-Valued Logic*이 필요하다.
* 3-Valued Logic
- 3개의 값(Ture, False, Unknown)을 갖는 Logic이다.
- "5 > 8"의 결과값은 False이다.
- "Null > 8"의 결과값은 Unknown이다.
General Constraints in SQL
- 테이블에 General Constraint를 부여하는 방법으로는 아래와 같은 4가지 방법이 있다.
1) CHECK문
2) CONSTRAINT문
3) CREATE ASSERTION문
4) CREATE TRIGGER문
Example. 일반적인 제약 조건
CREATE TABLE Sailors
(sid INTEGER,
sname CHAR(10),
rating INTEGER,
age REAL,
PRIMARY KEY (sid),
CHECK (rating >= 1 AND rating <= 10) )- CHECK문을 통해 rating 값의 범위에 제한(General Constraint)을 둔다.
Example. 이름이 부여된 제약 조건
CREATE TABLE Reserves
(sname CHAR(10),
bid INTEGER,
day DATE,
PRIMARY KEY (bid, day),
CONSTRAINT noInterlakeRes -- Constraint Name
CHECK ('Interlake' <>
(SELECT B.bname
FROM Boats B
WHERE B.bid=bid) ) )
-- 'Interlake'라는 이름의 배는 Reserves 테이블에 허용되지 않는다.- Constraint에 이름을 부여하고, CHECK문에 질의를 삽입하여 General Constraint를 부여할 수 있다.
- <> 는 \(\neq\)를 의미한다.
Example. 다수의 Relation들에 대한 제약조건
FAILED Query
CREATE TABLE Sailors
( sid INTEGER,
sname CHAR(10),
rating INTEGER,
age REAL,
PRIMARY KEY (sid),
CHECK
( (SELECT COUNT (S.sid) FROM Sailors S)
+ (SELECT COUNT(B.bid) FROM Boats B) < 100)
)- 위 DDL은 Sailors 테이블을 정의하는 SQL이기 때문에, Boats 테이블을 CHECK문에 추가할 수 없다.
- 즉, 다수의 테이블에 대한 Constraint 정의문은 Coverage를 고려해야 한다.
Correct Query
- CREATE ASSERTION 문 이용
CREATE TABLE Sailors
( sid INTEGER,
sname CHAR(10),
rating INTEGER,
age REAL,
PRIMARY KEY (sid),
CREATE ASSERTION smallClub
CHECK
( (SELECT COUNT (S.sid) FROM Sailors S)
+ (SELECT COUNT(B.bid) FROM Boats B) < 100)
)- 'smallClub' 이름을 가진 Constraint를 정의하고, 그 내부에 Boatst 테이블의 Field 값을 이용한다.
- CREATE ASSERTION문을 통해 특정 테이블에 귀속시키지 않고, 독립적으로 Constraint를 정의하는 방법이다.
Triggers
- General Constrain를 정의하는 방법 중 하나이다.
- Procedure의 형태를 갖고 있다.
- Trigger의 구성 요소로는 Event, Condition, Action이 있다. (E, C, A)
- DBMS에 어떠한 Event가 발생했을 때, Activate된다. (Trigger가 실행되는 것은 아니다. 단지 활성화되는 것이다.)
(Event : Insert, Delete, Update)
- Condition에 부합되는 경우, Trigger가 Action된다. (Trigger 코드가 실행된다.)
- Condition은 있을수도 있고 없을수도 있는, 선택사항이다. (즉, Event가 발생되면 바로 Trigger를 실행시킬 수도 있다.)
Example. Trigger (SQL:1999)
CREATE TRIGGER youngSailorUpdate
AFTER INSERT ON SAILORS -- 새 Relation이 Insert되면, Trigger가 Activate된다.
REFERENCING NEW TABLE NewSailors -- NewSailors 테이블을 참조한다.
FOR EACH STATEMENT
INSERT
INTO YoungSailors(sid, name, age, rating) -- Action
SELECT sid, name, age, rating -- Action
FROM NewSailors N -- Action
WHERE N.age <= 18 -- Action- CREATE TRIGGER 문 뒤에 Trigger의 이름을 명시한다.
- AFTER문 뒤에 어떤 Event가 발생되어야 Trigger를 Activate 할지를 명시한다.
- Trigger가 생성하는 레코드는 물론 SAILORS 테이블에도 저장되며,
\(\texttt{NEW TABLE}\) 구문의 NewSailors 테이블에도 저장된다.
Reference: Database Management Systems 3E (Raghu Ramakrishnan, Johannes Gehrke 저, McGrawHill, 2003)
