[데이터베이스] 12. SQL 기본 쿼리

SQL을 다루기에 앞서 다음과 같은 테이블을 사용한다.

 

sailors 테이블

 

reserves 테이블

 

Boats 테이블

 

 

---

기본 문법

 

SQL 기본 문법의 형태는 위와 같다.

이때 실행 순서는 FROM - WHERE - SELECT 순서이다.

From 절로 릴레이션 리스트를 가져오고, 가져온 릴레이션에서 WHERE 절로 조건에 맞는 행을 고른 뒤, 그 안에서 보고 싶은 컬럼을 select 하면 된다.

만약 DISTINCT 키워드가 있다면 select 결과에 대해 중복값을 제거한다.

 

FROM 절에 들어가는 relation-list 에 여러개의 릴레이션을 넣으면 각 릴레이션에 대해 cross product 한 결과를 만들어낸다.

WHERE 절은 관계 대수에서 selection 에 해당하는 부분이며, qualification 다양한 비교 연산자와 논리 연산자가 들어간다.

SELECT 절은 target-list 로 relation의 어트리뷰트를 지정한다. 관계 대수와 다르게, 중복 데이터를 제거할 때는 DISTINCT 키워드를 사용해야 한다.

 

 

만약 위와 같은 SQL을 실행한다면

 

1. Saillors, Reserves 테이블을 크로스 프로덕트한 결과에 대해서

2. S.sid = R.sid AND R.bid = 103 인 행들만 남긴 뒤

3. S.sname 을 조회한다.

 

 

그래서 그림상으로는 이렇게 크로스 프로덕트한 결과에 대해서 조건에 맞는 (색칠된) 행을 찾는 것과 같다.

 

 

이 SQL을 자연어로 풀어쓰면, 103번 보트를 예약한 모든 선원의 이름을 출력하는 SQL이다.

실제 SQL의 실행 결과는 위와 같다.

 

103번보트는 22, 31, 74번 선원이 예약하였고 각각의 이름은 위와 같다.

 

연습 문제

1. 모든 선원의 이름과 나이 구하기

 

 

이때 (Horatio, 35) 조합이 두번 나타난다.

만약 관계 대수와 동일하게 중복을 제거하고 싶다면

 

 

이렇게 DISTINCT 를 붙여주면 된다.

 

 

2. 7보다 큰 등급을 갖는 선원 조회하기

 

 

3. 103번 보트를 예약한 적이 있는 선원의 이름 구하기

 

 

4. 적어도 하나의 보트를 예약한 선원의 sid 구하기

 

이때 이렇게 하면 여러 sid 가 중복으로 나오므로, DISTINCT 키워드를 사용해서 중복된 sid 를 없애줄 수 있다.

하지만 만약 S.sid 대신 S.sname을 사용하면 이때는 DISTINCT 키워드를 사용해서 중복을 제거할 수 없다.

(애초에 이렇게 조회해서도 알 수 없긴 하지만) 중복을 제거하면 동명이인의 예약 정보가 제거될 수도 있기 때문이다.

 

5. 적색 배를 예약한 적 있는 선원의 sid 구하기

 

이 문제의 경우에는 DISTINCT를 사용해도 좋을 것 같다.

(책에는 DISTINCT 를 사용하지 않았으며, sid 정보는 사실 Reserve 테이블에 들어있기 때문에 굳이 Sailors 테이블을 가져올 필요는 없었다.)

 

6. 적색 배를 예약한 적이 있는 선원의 이름 구하기

 

선원의 이름을 가져온다면 이렇게 반드시 Sailor 테이블을 조인할 수 밖에 없다.

또한 이때는 DISTINCT 를 사용할 수 없다. 동명이인이 있을 수 있기 때문이다.

 

 

7. Lubber 가 예약한 배의 색상들

선원의 이름, 배의 색상, 예약 정보가 필요하므로 역시 3개의 테이블을 조인해야한다.

Lubber 가 동명이인일 수 있으니 1사람의 예약정보는 아닐 수 있다.

 

 

---

SELECT 문에서 수식 작성 & 문자열 다루기

select 문에는 수식 AS 컬럼명 형식으로 데이터를 조회할 수 있다.

 

AS 대신에 컬럼명 = 수식 의 형태도 쓸 수 있다는데, Postgres 디비는 지원하지 않았다.

 

문자열을 다룰 때는 LIKE 라는 키워드와 _  % 라는 와일드카드를 사용할 수 있다.

_ 는 그 위치 하나에 임의의 문자가 와도 된다는 뜻이고, % 는 그 위치에 빈 문자열을 포함하여 임의의 문자열이 와도 된다는 뜻이다.

 

연습 문제

1. 같은 날에 서로 다른 두 배를 예약한 선원이 이름과, 그 등급 + 1의 값을 조회

그룹바이를 아직 배우지 않았으므로, 서로 다른 배를 다루었다는 것을 확인하려면 예약 테이블을 2번 조인하면 된다.

 

SELECT DISTINCT S.sname, S.rating + 1
FROM sailors S, Reserves R1, Reserves R2
WHERE S.sid = R1.sid AND S.sid = R2.sid AND R1.bid <> R2.bid AND R1.day = R2.day;

 

 

 

2. 이름이 B로 시작해서 B로 끝나고 세글자 이상인 선원의 나이

 

postgres 에서는 이상하게 조회가 되지 않았다.

 

이렇게 하니까 성공했다.

 

---

UNION, INTERSECT, EXCEPT

SQL 연산의 결과는 결국 행들의 집합이므로, 스키마가 같다면 (union-compatible) 두 SQL 결과는 합집합, 교집합, 차집합의 대상이 될 수 있다.

 

1. 빨간 배 혹은 녹색 배를 예약한 적이 있는 선원 이름

 

이렇게 할 수도 있고, 집합 연산을 사용하면

 

 

이렇게 할 수도 있다.

히자만 이 SQL은 너무 중복되는 게 많아서 위와 같이 쓰는 것이 더 간단할 것이다.

 

 

2. 빨간배와 녹색배를 모두 예약한 적이 있는 선원의 이름 구하기

관계대수를 정리할 때 봤었듯, 이때는 WHERE 절에서 and 조건을 걸 수 없다.

 

이때는 이렇게 교집합 연산을 사용해서 구해야 한다.

(같은 테이블을 여러번 써서도 구할 수 있겠지만, 배의 색상에 대해 조건이 들어가기 때문에 5개의 테이블을 나열해야 한다.)

 

 

3. 빨간색 배는 예약했지만, 녹색 배는 예약하지 않은 선원의 번호

(SELECT R.sid
FROM  Reserves R, Boats B
WHERE R.bid = B.bid AND B.color = 'red')
EXCEPT
(SELECT R.sid
FROM  Reserves R, Boats B
WHERE R.bid = B.bid AND B.color = 'green')

 

 

 

4. 등급이 10이거나, 104번 배를 예약한 선원의 id

SELECT S.sid
FROM   sailors S
WHERE  S.rating = 10
UNION
SELECT R.sid
FROM   Reserves R
WHERE R.bid = 104

 

 

 

UNION 연산은 두 집합을 합치면서 중복이 제거된다.

만약 중복을 제거하고 싶지 않다면 UNION ALL 연산을 사용하자.