기본 SQL 질의 형태
- SELECT : 결과에 유지되는 열들을 표시 (추출, 열, 프로젝션 π)
- FROM : 테이블의 크로스 프로덕트를 표시 (크로스 프로덕트 R X S )
- WHERE : FROM절에서 언급된 테이블들에 대한 선택 조건을 표시 (선택, 행, 셀렉션 σ)
- FROM절의 relation-list
: 테이블 이름들의 리스트. 테이블 이름은 범위 변수에 의해 표시될 수 있음.
- SELECT절의 target-list
: relation-list에 표시된 테이블의 열 이름(애트리뷰트)들의 리스트.
열 이름 앞에 범위 변수들을 붙일 수 있음.
- WHERE절의 qualification (자격 조건)
: 수식 op 수식 형 조건들의 Boolean 조합
- DISTINCT 키워드
: 선택적임. 명시해야 중복이 제거됨. SQL에서 디폴트는 중복 제거 안하는 것임.
* 범위 변수 (range variables)
: 범위 변수는 같은 테이블 이름이 두 번 이상 나타날 때 특히 유용함.
쿼리 처리 순서 (Conceptual Evaluation Strategy)
- relation-list의 테이블들에 대한 크로스 프로덕트 계산
- 크로스 프로덕트에서 qualifications를 만족하지 않는 행들을 삭제
- target-list에 나타나지 않는 모든 열 삭제
- 만약 DISTINCT가 표시되면, 중복 행 제거
FROM -> WHERE -> SELECT -> (DISTINCT)
기본 SQL 질의의 예제
Q1) 배 번호 103을 예약한 적이 있는 뱃사람의 이름을 구하시오.
Q4) 적어도 한 배를 예약한 적이 있는 뱃사람의 이름을 구하시오.
SELECT 명령어에서 수식과 문자열
target-list의 각 항은 수식 AS 열-이름의 형식으로 될 수 있음.
여기에서 수식은 열 이름 및 상수들로 구성된 산술식 또는 문자열 수식이며,
열-이름은 질의의 출력에 나타나는 이 열의 새로운 이름임.
Q18) 이름이 B로 시작해서 B로 끝나고 세 자 이상인 뱃사람의 나이를 구하시오.
❖ AS and = are two ways to name fields in result.
❖ LIKE is used for string matching.
`_’ : 정확히 한 개의 임의의 문자를 상징
`%’ : 0개 혹은 그 이상의 임의의 문자들을 상징
UNION, INTERSECT, EXCEPT
- UNION 합집합
- INTERSECT 교집합
- EXCEPT 차집합
기본 질의 형식에서는 DISTINCT가 표시되지 않으면 중복이 제거되지 않지만,
UNION, INTERSECT, EXCEPT 질의는 디폴트가 중복 제거임.
중복 유지하려면 UNION(INTERSECT, EXCEPT) ALL을 사용.
Q5) 적색 혹은 녹색 배를 예약한 적이 있는 뱃사람의 번호를 구하시오.
Q6) 적색과 녹색 배를 둘 다 예약한 적이 있는 뱃사람의 번호를 구하시오.
Q5와 Q6에서 S.sid가 아니라 S.name을 구하는 거였으면 문제 발생.
name은 key가 아니기 때문인데,
만약 동명이인 중 한명이 적색배, 다른 한명이 녹색배를 예약했더라도
해당 이름이 결과(적색과 녹색 배를 들 다 예약한 사람)로 나옴. -> 중첩 질의 해야함.
Q19) 적색은 예약했지만 녹색 배는 예약하지 않은 모든 뱃사람의 번호를 구하시오.
Reserve 릴레이션이 sid 정보를 가지고 있으므로, Sailors 릴레이션을 조회할 필요가 없음
중첩 질의
중첩 질의 (Nested Queries)
그 안에 내장된 다른 질의를 가지는 질의.
* 부질의(subquery)
: 내장된 질의.
질의를 작성할 때, 스스로 계산되어야 하는 테이블을 참조하는 조건을 표현할 필요가 있을 때
보조 테이블을 계산하기 위해 사용된 질의임. (주로 WHERE절, 가끔 FROM 혹은 HAVING절에서 나타남)
Q1) 배 번호 103을 예약한 적이 있는 뱃사람의 이름을 구하시오.
- IN : 어떤 값이 원소들의 주어진 집합에 속하는지 검사 (<-> NOT IN)
Sailors 행들을 조사하고, 각 행에 대해서 Reserves에 대한 부질의를 계산함
상호 관련된 중첩 질의
Q1) 배 번호 103을 예약한 적이 있는 뱃사람의 이름을 구하시오.
- EXISTS : 집합이 공집합이 아닌지를 점검함. (<-> NOT EXISTS)
각 뱃사람 열 S에 대해서, R.bid=103 AND S.sid=R.sid를 만족하는 Reserves 행 R의 집합이
공집합이 아니라면, 뱃사람 S는 배 103을 예약했고, 그 이름을 검색하게 됨.
NOT EXIST를 사용하면, 적색 배를 예약하지 않은 뱃사람의 이름 등을 계산 가능.
- UNIQUE : 부질의의 답에 어떠한 행도 두 번 이상 나타나지 않으면(중복이 없으면) true 반환.
공집합이어도 ture 반환함. NOT UNIQUE도 있음.
집합-비교 연산자
Q22) Horatio라 불리는 어떤/모든 뱃사람들보다 등급이 더 높은 뱃사람을 구하시오.
- op ANY : 어떤
- op ALL : 모든
IN과 NOT IN은 각각 =ANY와 <>ALL과 동등함.
중첩 질의의 추가 예제
INTERSECT를 포함하는 질의는 IN을 이용하여 재작성 될 수 있고
EXCEPT를 사용하는 질의는 NOT IN을 사용하여 재작성 될 수 있음.
Q6) 적색과 녹색 배를 둘 다 예약한 적이 있는 뱃사람의 번호를 구하시오.
적색 배를 예약하고, 동시에
녹색 배를 예약한 뱃사람들의 sid 집합에 포함된 sid를 갖는 모든 뱃사람들을 구하기.
만약 S.sid가 아니라 S.name을 구하는 거였다면 그 부분만 바꿔주면 됨.
근데 INTERSECT로 해본다면? (뱃사람을 식별하기 위하여 sid를 사용하고 name을 반환해야함)
Q9) 모든 배를 예약한 적이 있는 뱃사람의 이름을 구하시오. (Division in SQL)
EXCEPT를 사용하지 않고 처리한다면 아래와 같음
이 뱃사람에 의해 예약되지 않은 배가 없는지 점검.
집단 연산자
Aggregate Operators
- COUNT (*)
- COUNT ( [DISTINCT] A) : A열의 (유일한) 값들의 수.
- SUM ( [DISTINCT] A) : A열의 모든 (유일한) 값들의 합.
- AVG ( [DISTINCT] A) : A열의 모든 (유일한) 값들의 평균.
- MAX (A) : A열의 최댓값.
Q27) 가장 나이 많은 뱃사람의 이름과 나이를 구하시오.
만약 SELECT 절이 집단 연산을 사용하면, 그 질의가 GROUP BY 절을 포함하지 않는 한
SELECT 절에 오직 집단 연산만 사용해야 함.
즉, SELECT절에서 MAX(S.age)와 S.name을 함께 사용할 수 없음.
이렇게 중접 질의를 사용해야 함.
- MIN (A) : A열의 최솟값.
GROUP BY와 HAVING 절
집단 연산을 한 릴레이션의 모든 (조건에 맞는) 행들에 적용하는 게 아니라
릴레이션의 행들에 대한 각 그룹별로 집단 연산을 적용하고 싶은 경우
Q31) 각 등급 단계별 가장 어린 뱃사람의 나이를 구하시오.
10개의 이와 같은 질의를 작성하는 것은 번거롭기도하고, 등급 단계가 얼마나 존재하는지 미리 알 수 없음.
* 중요한 점*
- target-list에 나타나는 모든 열은 grouping-list에도 나타나야 함. (aggregation함수에 사용하는 건 노상관)
- HAVING절의 group-qualification에서 나타나는 수식들은 그룹 당 하나의 값만을 가져야 함
Q32) 두 명 이상의 뱃사람을 가지는 등급에 대해 18세 이상인 가장 어린 뱃사람의 나이를 구하시오.
- FROM절의 target-list에 있는 테이블들의 크로스 프로덕트를 구성 (하나만 있으면 생략)
- WHERE절의 조건식을 적용
- 필요없는 열들을 제거 (SELECT절, GROUP BY절, HAVING절에서 언급된 열들만 필요함)
- GROUP BY절에 따라 테이블을 정렬
- HAVING절의 group-qualification 조건을 적용
(WHERE절이 먼저 적용되지 않았다면 rating=10의 그룹도 group-qualification 만족함. 순서 중요!) - 남아있는 각 그룹마다 하나의 답 행을 만듦
cf) EVERY : 그룹의 모든 행이 추가된 조건을 만족해야 함. (교재 p.147~148)
집단 질의의 추가 예제
Q33) 각 적색 배에 대해, 이 배에 대한 예약의 수를 구하시오.
그룹 애트리뷰트 bid가 Boats의 키(따라서 color를 결정)이므로 SQL은 이 질의를 허용하지 않음.
GROUP BY에 나타난 것만 HAVING절의 인수로 나타날 수 있음?(교재 p.149)
Q37) 뱃사람의 평균 나이가 모든 등급에 대해 최소인 등급을 구하시오. (최소의 평균 age)
각 등급 값에 대한 평균 나이를 포함한 임시 테이블을 계산하고
그 다음에 이 평균 나이가 최솟값을 가지는 등급(들)을 찾음.
널 값
널 값 (Null Values)
행 값이 미상(unknown) 또는 적용 불가능(inappliable)일 때 널을 사용함.
SQL의 복잡한 무결성 제약 조건
3장에서의 SQL의 무결성 제약 조건 기능을 보완하는 것임.
한 테이블 위의 제약 조건
- CHECK
: 테이블 제약 조건을 사용하여 하나의 테이블에 대해 복잡한 제약 조건을 표시할 수 있음.
ex) rating이 1에서 10까지의 범위의 정수가 되도록 하려면,
ex) Interlake라는 배들은 예약할 수 없다는 제약 조건을 시행하려면,
한 행이 Reserves로 삽입되거나 기존의 행이 수정될 때, CHECK 제약 조건의 조건식이 계산됨.
그것이 false로 계산되면 그 명령은 거부됨.
여러 테이블 위의 무결성 제약 조건
- ASSERTION
: 어느 한 테이블에만 국한되지 않는 제약 조건임.
한 제약 조건이 둘 이상의 테이블을 포함할 때 사용.
ex) 배의 수와 뱃사람의 수를 합한 값이 10보다 작도록 제약조건을 집행하고 싶을 때.
위와 같이 한다면, 만약에 Sailors 테이블이 비어있으면 비록 Boats에 100행 이상이 있더라도
이 제약 조건은 (테이블 제약 조건의 의미에 의하여) 항상 받아들이는 것으로 정의됨!
두 테이블에 전부 적용되도록 단정을 만들자
트리거
트리거 (Triggers)
데이터베이스에 행해진 변경에 대응해서 DBMS에 의해 자동적으로 실행되는 일종의 프로시저.
보통 DBA에 의해 표시됨.
관련 트리거 집합을 가진 데이터베이스를 능동 데이터베이스라고 함.
- 이벤트 (Event) : 트리거를 구동시키는 데이터베이스에 대한 변경 사항
- 조건 (Condition) : 트리거가 구동될 때 수행되는 질의 또는 검사
- 동작 (Action) : 트리거가 구동되고 해당 조건이 참일 때 수행되는 프로시저
'CS > 3-2 DB' 카테고리의 다른 글
| [기초데이터베이스] 04. Relational Algebra (0) | 2022.10.20 |
|---|---|
| [기초데이터베이스] 03. The Relational Model (0) | 2022.10.20 |
| [기초데이터베이스] 02. The Entity-Relationship Model (0) | 2022.10.19 |
| [기초데이터베이스] 01. Database Management Systems (0) | 2022.10.19 |
댓글