[데이터베이스] SQL - (2)

SQL에서의 연산

---

 

- 자연 조인(Natural Join)

 

자연 조인(Natural Join)은 공통으로 가지는 속성의 값이 일치하는 tuple들을 연결하여 만들어진 tuple을 포함하는 테이블을 생성한다.

 

예시)

select * 

from instructor natural join teaches;

 

자연 조인(Natural Join)을 사용할 때 속성을 잘못 동일시 하지 않도록 주의해야 한다.

 

예시)

학생의 이름과 그 학생이 수강했던 과목을 나열하라.

 

incorrect version:

 

select name, title
from student natural join takes natural join course;

이유:

student와 takes의 자연 조인은 (ID, name, dept_name, tot_cred, course_id, sec_id) 속성을 가지고 있고, course는 (course_id, title, dept_name, credits) 속성을 가지고 있다.

 

결과적으로, 이 두 테이블의 자연 조인을 수행하게 되면 두 테이블이 course_id 뿐만 아니라 dept_name 또한 동일한 tuple들을 출력하게 된다.

 

따라서, 학생이 자신이 소속한 학과가 아닌 다른 학과에서 수강한 과목에 대한 모든 쌍을 생략되기 때문에 의도한 것과 다른 결과를 도출하게 되므로 incorrect하다.

 

 

correct version:

 

select name, title
from student natural join takes, course
where takes.course_id = course.course_id;

 

or

 

select name, title
from (student natural join takes) join course using(course_id);

(이때, "A join B using(k)"대신 "A join B on A.S  = B.T"를 사용하면 attribute의 이름이 다를 때도 사용가능하다.)

 

 

 

- 외부 조인(Outer Join)

 

외부 조인은 정보의 손실을 피하기 위해서 조인 연산을 확장한 것이다.

 

외부 조인은 조인 연산을 수행한 뒤 조인의 결과에서 빠질 수 있는 튜플을 null 값을 이용해 추가해서 보존하는 연산이다. 

 

1. 왼쪽 외부 조인(left outer join)

: 연산의 왼쪽에 나타난 table의 tuple만 보존한다.

 

예시) instructor natural left outer join teaches

 

 

2. 오른쪽 외부 조인(right outer join)

: 연산의 오른쪽에 나타난 table의 tuple만 보존한다.

 

예시) instructor natural right outer join teaches

 

 

3. 전체 외부 조인(full outer join)

: 두 table의 모든 tuple을 보존한다.

 

예시) instructor natural full outer join teaches

 

 

 

- 재명명(Rename) 연산

 

재명명 연산은 다음과 같은 형태를 가진다.

old-names as new-names

 

as 절은 select 절과 from 절 모두에서 나타날 수 있다.

 

예시)

select ID, name, salary/12 as monthly_salary

from instructor

 

select distinct T.name

from instructor as T, instructor as S

where T.salary > S.salary and S.dept_name = 'Comp.Sci.'

 

keyword 'as'는 생략될 수 있다.

 

예시)

select distinct T.name

from instructor T, instructor S

where T.salary > S.salary and S.dept_name = 'Comp.Sci.'

 

 

 

- 문자열(String) 연산

 

문자열에서 like 연산자를 사용해서 패턴 일치를 수행할 수 있다.

 

이때, 패턴은 다음의 두 개의 특수문자를 사용해서 나타낼 수 있다.

 

1. 퍼센트(%): %는 어떤 substring과도 일치한다.

2. 밑줄(_): _문자는 어떤 문자와도 일치한다.

 

예시) 

select name

from instructor

where name like '%dar%'

-> %dar%은 dar을 포함하는 어떤 문자열과도 일치한다.

 

%, _, \과 같은 특수 문자를 포함하는 패턴 일치를 위해서 이스케이프 문자를 사용한다.

 

예시)

like 'ab\%cd%' escape '\': "ab%cd"로 시작하는 모든 문자열과 일치한다.

like 'ab\\cd%' escape '\': "ab\cd"로 시작하는 모든 문자열과 일치한다.

 

 

 

- tuple 출력 순서 제어

 

query의 결과 튜플이 정렬된 순서로 출력하기 위해서 order by 절을 사용한다.

 

예시)

select distinct name

from  instructor

order by name

 

정렬 순서를 명시하기 위해서 내림차순일 때는 desc를, 오름차순일 때는 asc를 사용한다. 오름차순이 default이다.

 

예시) order by name desc

 

 

 

- where 절의 술어(predicate)

 

SQL은 where 절에서 어떤 값보다는 작거나 같고 어떤 값보다는 크거나 같은 값을 간단히 명시하기 위해 between 비교 연산자를 제공한다.

 

예시)

select name

from instructor

where salary between 90000 and 100000

 

 

 

- 집합 연산

 

union, intersect, except는 각각 합집합, 교집합, 차집합 연산을 제공한다.

 

예시)(select course_id from section where sem = 'Fall' and year =2009)union(select course_id from section where sem = 'Spring' and year = 2010)

 

 

 

- 집계 함수(Aggregate Functions)

 

1. 기본 집계

 

집계 함수는 입력으로 값의 모음(collection, 즉 set 혹은 multiset)을 가지며, 결과 값으로 단일 값을 반환하는 함수이다.

 

SQL에서 제공하는 내장 집계 함수1. avg: average value2. min: mininum value3. max: maximum value4. sum: sum of values5. count: number of values

 

예시)select avg(salary)from instructorwhere dept_name = 'Comp. Sci.';

 

집계 함수를 구하기 전에 중복을 제거해야 하는 경우도 있다. 중복을 제거하기 위해서 distinct를 집계 함수 표현에 사용한다.

 

예시)select count(distinct ID)from teacheswhere semester = 'Spring' and year = 2010;

 

테이블 내의 tuple의 개수를 알기 위해서 집계 함수 count를 사용한다.

 

예시)select count(*)from course;

 

 

2. 그룹단위 집계

 

tuple들을 단일 집합이 아닌 복수의 tuple 집합에 대해 집계 함수를 적용하고 싶을 때, group by 절을 사용한다.

 

group by 절에서 모든 속성이 같은 값을 가지는 tuple들은 하나의 group으로 묶인다.

 

예시)select dept_name, avg(salary)from instructorgroup by dept_name;

 

group by를 사용할 때, select 절에서 나타날 수 있는 속성은 집계 함수를 제외하고는 group by 절에 나타나는 속성 뿐이다.

 

예시)select dept_name, ID, avg(salary)from instructorgroup by dept_name;-> select 절에 나타난 ID 속성은 aggreate function 형태도 아니고, group by 절에도 나타나지 않는 속성이므로 이 query는 잘못된 query이다.

 

 

3. Having 절

 

group이 형성된 다음에 조건을 적용하기 위해서 having 절을 사용한다.

 

예시)

select dept_name, avg(salary)

from instructor

group by dept_name

having avg(salary) > 42000;

 

집계 함수, group by, having 절을 포함하는 query의 연산 순서는 다음과 같다.

from 절 -> where 절 -> group by 절 -> having 절 -> select 절

 

 

4. null 값의 집계

 

count(*)을 제외한 모든 집계 함수는 null 값을 무시한다.

 

만약 collection에 null 값만 존재한다면, count는 0을 반환하고, 다른 집계 함수는 null을 반환한다.