SQL문

target-list: 속성 이름 (여러 개 가능)

realation-list: 검색해야할 릴레이션 (여러 개 가능)

qualification: 조건 (여러 개 가능)

DISTINCT: 중복을 허용하고 싶지 않을 때 붙음 (default는 중복 허용함)

[ SELECT절 – 관계대수의 projection 연산에 해당

FROM절 – 관계대수의 Cartesian product 연산에 해당

WHERE절 – 관계대수의 selection 연산에 해당 ]

[ 수행 순서: FROM -> WHERE -> SELECT ]

(문제) 103번 보트를 예약한 항해사의 이름을 찾으시오.

(1) FROM절이 실행되서 Cartesian Product를 한다.

(2) WHERE절이 실행되서 selection연산을 한다. (조건에 맞는 튜플을 선택)

(3) SELECT절이 실행되서 projection연산을 한다. (일부 속성을 선택)

(문제) 모든 항해사의 서로 다른 이름과 나이를 검색하시오.

이름과 나이가 둘 다 같은 경우 중복 제거 (하나라도 다르면 서로 다르다고 봄)

Horatio같은 경우 이름과 나이가 둘 다 같지는 않으므로 중복이라고 볼 수 없다.

(문제) 빨간색 보트를 예약한 선원들의 sid를 구하시오.

Boats와 Reserves를 Cartesian-product한 후 bid가 같으면서 Boats의 색(color)이 red일 때 sid를 구한다.
-> boats와 reserves를 bid로 equi-join한 것에서 boats의 color가 red일 때 sid를 구하시오.

(문제) 빨간색 보트를 예약한 선원들의 이름을 구하시오.

sailors와 reserves를 sid로 equi_join하고, reserves와 boats를 bid로 equi_join한 것에서 boat의 색이 빨간색일 때 sailors의 sname을 구하시오.

(문제) Lubber에 의해 예약된 보트의 색을 구하시오.

sailors와 reserves를 sid로 equi_join하고, reserves와 boats를 bid로 equi_join한 것에서 sailors의 이름이 ‘Lubber’일 때 Boats의 color을 구하시오.

(문제) 적어도 한 개의 보트를 예약한 항해사의 이름을 구하시오.

Reserves에 위치한 sid에 대한 항해사들은 모두 한 개 이상의 보트를 예약함
sailors릴레이션과 reserves릴레이션을 sid로 equi-join하면 이름을 구할 수 있음

LIKE 연산자

‘_’은 한 개의 문자를 의미한다. / ‘%’은 0개 이상의 문자를 의미한다.

ex) B_%B = BKB,BCAB,….
B는 양끝에 위치하며 그 사이에 한 개 이상의 문자가 와야 한다.
(한 개의 문자+0개 이상의 문자 -> 한 개 이상의 문자)

<Self-Join: 자기 자신과 Join>

(문제) 같은 날 다른 두 개의 보트를 예약한 선원의 등급을 한 단계 높이시오.

1. Reserves릴레이션과 같은 내용의 릴레이션인 Reserves1을 만든다.
2. 세 릴레이션을 곱집합을 한 뒤 살펴본다. (3x3x3)

3. sid가 같은 것들만 살펴본다.
4. Reserves와 Reserves의 day가 같고 bid가 다른 것들을 살펴본다.
5. 다 찾으면 rating을 한 단계 올리고 저장한다. (rating+1 AS rating) -> 선택

(문제) 빨간색이나 초록색 보트를 예약한 선원의 sid를 찾으시오.

boats와 reserves를 bid로 equi-join한 것에서 boats의 color가 red or green일 때 sid를 구하시오.

UNION 연산

(문제) 빨간색이나 초록색 보트를 예약한 선원의 sid를 찾으시오.

빨간 보트를 예약한 선원의 sid를 찾고 초록색 보트를 예약한 선원의 sid를 찾은 다음 UNION한다. -> 전체 값이 중복없이 나온다.

(문제) 빨간색과 초록색 보트를 예약한 선원의 sid를 찾으시오.

(잘못된 쿼리)

빨간색이면서 초록색인 보트의 색은 없다.

(교집합 연산을 이용해서 해결)

빨간 보트를 예약한 선원의 sid를 찾고 초록색 보트를 예약한 선원의 sid를 찾은 다음 INTERSECT한다. -> 공통된 값이 중복없이 나온다.

(차집합 연산 예시)

빨간 보트를 예약한 선원의 sid기준으로 초록 보트를 예약한 선원의 sid와 공통된 값을 제외한 값만 나온다.
{22,31,64} – {22,31,74} = {64}

UNION ALL을 이용해서 중복을 허용

(문제) 7등급인 항해사 이거나 104번 보트를 예약한 항해사의 sid를 구하시오. (중복 포함)

<Nested Queries: 중첩 쿼리로 문제 풀기>

(문제) 103번 보트를 예약한 항해사의 이름을 구하시오.

Reserves의 bid가 103인 sid는 58이다.
Sailors의 sid가 58인 sname은 rusty이다.

즉 Nested Query를 사용하지 않았을 때와 같은 효과를 낸다.

equi-join을 이용해서 문제를 풀 수도 있고 중첩 쿼리로도 문제를 풀 수도 있다.

(문제) 103번 보트를 예약하지 않은 항해사의 이름을 찾으시오.

Reserves의 bid가 103인 sid는 58이다.
Sailors의 sid가 58이 아닌 sname은 dustin, lubber이다.

(문제) 빨간색 보트를 예약한 선원들의 이름을 구하시오.

Boats의 color가 ‘red’인 bid는 102,104이다.
Reserves의 bid가 102,104인 sid는 22,31,64이다.
sailor의 sid가 22,31,64인 sname은 ‘Dustin’, ‘Lubber’, ‘Horatio’이다.

(문제) 빨간색이 아닌 배를 예약한 선원들의 이름을 구하시오. (다른 색 배 예약)

빨간색 보트인 bid를 구하고 Reserves릴레이션에서 해당 bid가 아닌 sid를 구하고, 해당 sid로 Sailors릴레이션에서 sname을 구한다.

(문제) 빨간 배를 예약하지 않은 선원들의 이름을 구하시오. (다른 색 배 예약 + x 예약)

빨간색 보트인 bid를 구하고 해당 bid로 Reserves릴레이션에서 sid를 구하고, Sailors릴레이션에서 해당 sid가 아닌 sname을 구한다.

(문제) 빨간색이 아닌 배를 예약하지 않은 선원들의 이름을 구하시오. (빨간 배 예약 + x 예약)

빨간색 보트인 bid를 구하고 Reserves릴레이션에서 해당 bid가 아닌 sid를 구하고, Sailors릴레이션에서 해당 sid가 아닌 sname을 구한다.

EXISTS 연산

(문제) 103번 보트를 예약한 항해사의 이름을 구하시오.

Sailors릴레이션의 sid속성을 안쪽 쿼리로 가져와서 조건을 제시할 수 있다.
밖에 있는 속성을 사용하는 것을 correlation이라고 한다.
Exists연산자는 correlation을 가능하도록 한다.

Division in SQL

(문제) 모든 배를 예약한 선원의 이름을 구하시오.

예약을 받지 않은 보트가 존재하지 않을 때 항해사를 구하시오.

Any 연산

한 조건이라도 만족하면 True이다.

All 연산

모든 조건을 만족하면 True이다.

(문제) Horatio라고 불리는 일부 선원보다 더 높은 등급을 받은 선원을 찾으시오.

Horatio가 두 명인데, 그 둘 중 한 사람의 등급보다 높은 사람을 구하시오.

(문제) Horatio라고 불리는 전체 선원보다 더 높은 등급을 받은 선원을 찾으시오.

Horatio가 두 명인데, 그 둘의 등급보다 더 높은 사람을 구하시오.

(문제) 가장 높은 등급을 지닌 선원을 구하시오.

모든 선원의 등급을 구하고 그 등급 전체보다 크거나 같은 등급을 구하시오.

Aggregate Operators

셈, 합, 평균, 최대/최소를 구하는 연산자

(문제) 10등급을 지닌 모든 선원들의 평균 나이를 구하시오.

(문제) 가장 나이가 많은 선원의 이름을 구하시오

(문제) 서로 다른 이름을 가진 선원의 수를 구하시오

(문제) 10등급인 가장 나이 많은 선원들보다 더 나이 많은 선원들의 이름을 구하시오

MAX 연산자 혹은 ALL 연산자로 구할 수 있음

MAX: 10등급인 선원들 중 가장 높은 나이를 구하고 그것보다 높은 나이를 지닌 선원들의 이름 찾기

All: 10등급인 선원들의 나이 전체보다 높은 나이를 구하고 그것보다 높은 나이를 지닌 선원들의 이름 찾기

GROUP BY and HAVING

(문제) 각 등급별로 가장 어린 선원의 나이를 구하시오.

중요: 값(나이)만 나와서는 안 되고 그룹(등급)도 나와서 값을 구분 지을 수 있어야 한다.

where절은 grouping하기 전에 진행 됨, having절은 grouping한 이후 진행됨

(where -> group by -> having)

(문제) 각각의 빨간 보트에 대해서 예약한 사람의 수를 구하시오.

Boats의 색이 빨간 색인 튜플을 찾고 Boats와 Reserves를 bid로 equi-join한다. 이후 Boats의 bid그룹별로 카운트를 한다.

(문제) 나이가 18살보다 큰 선원들을 등급별로 그룹핑했을 때 각 그룹 당 2명 이상의 선원이 있어야 한다. 이 때 그룹별 가장 나이 어린 사람을 구하시오.

1. where절 – 나이가 18살보다 큰 튜플들을 찾음

2. group by절 – rating별로 sorting한 후, 각 rating별로 grouping

3. having절 – 각 그룹별로 counting했을 때 1보다 큰 그룹만 남긴다. (그룹 조정)

(where로 조건에 맞는 튜플만 남김 -> grouping함 -> having절로 group조정함)

4. 그룹별로 가장 나이가 적은 선원을 구함 -> 그룹(rating), 값(MIN(age))

ORDER BY (정렬)

default값은 ascending(오름차순)

descending(내림차순)을 원하면 쿼리 맨 뒤에 ‘DESC’를 붙이면 됨