SQL 데이터 조작 언어(DML), 집합연산, 외부조인, 집계함수, group by, having

<집합 연산>

관계대수의 집합 연산인 합집합, 교집합, 차집합에 해당하는 연산자

- union

- intersect

- minus

​

형식 : <select문-1> <집합 연산자> <select문-2>

조건 : <select문-1>과 <select문-2>에서 선택된 필드의 개수와 대응되는 필드의 데이터 타입이 서로 같아야함

​

UNION

예) 모든 학생의 이름과 교수의 이름을 함께 출력

select name from student
union
select name from professor

union 연산자는 연산 결과에 포함된 중복된 레코드를 제거함

✔ 중복을 제거하고 싶지 않다면 union all 연산자를 사용

​

INTERSECT

예) 컴퓨터공학과 학생들 중 최소 한 과목에서 'A+' 학점을 받은 학생들의 학번을 검색

select  s.stu_id
from    student s, department d, takes t
where   s.dept_id = d.dept_id and s.stu_id = t.stu_id and d.dept_name ='컴퓨터공학과' and t.grade = 'A+'

 

'컴퓨터공학과'에 다니는 학생들의 학번과 takes 테이블에서 학점이 'A+'인 학생들의 학번의 교집합을 구함

select  stu_id
from    student s, department d
where   s.dept_id = d.dept_id and d.dept_name = '컴퓨터공학과'
intersect
select  stu_id from takes where grade = 'A+'

 

MINUS

예) 산업공학과 학생들 중에서 한번도 'A+'를 받지 못한 학생들의 학번을 검색

select stu_id from student s, department d
where  s.dept_id = d.dept_id and dept_name = '산업공학과'
minus
select stu_id from takes
where  grade = 'A+'

 

외부 조인(outer join)

예) 모든 교과목에 대해 교과목명, 학점수, 개설년도, 개설학기를 검색

select  title, credit, year, semester
from    course, class
where   course.course_id = class.course_id

 

왼쪽 외부 조인(left outer join)

- 연산자의 왼쪽에 위치한 테이블의 각 레코드에 대해서 오른쪽 테이블에 조인 조건에 부합하는 레코드가 없을 경우에도 검색 결과에 포함

✔ 결과 레코드에서 오른쪽 테이블의 나머지 필드에는 null 이 삽입됨

​

오른쪽 외부 조인은 왼쪽 외부 조인과 반대

​

완전 외부 조인(full outer join)

- 양쪽 테이블에서 서로 일치하는 레코드가 없을 경우, 해당 레코드들도 결과 테이블에 포함시키며, 이때 나머지 필드에 대해서는 모두 null 을 삽입

예)

select  title, credit, year, semester
from    course full outer join class
using   (course_id)

 

집계 함수

 

COUNT

형식 : count(<필드 이름>) or count(distinct<필드 이름>)

- 해당 필드에 값이 몇 개 있는지 출력

- distinct 사용 시 서로 다른 값의 개수를 계산

- <필드 이름>에 '*'를 사용하면 모든 레코드의 개수를 계산

​

예) 3학년 학생이 몇 명인지 출력

select  count(*)
from    student
where   year = 3

예) 컴퓨터공학과의 학생 수를 출력

select  count(*)
from    student s, department d
where   s.dept_id = d.dept_id and d.dept_name = '컴퓨터공학과'

 

SUM

형식 : sum (<필드 이름>)

- 지정된 필드에 저장된 값의 합을 계산

​

예) 전체 교수들의 재직 연수의 합

select  sum(2019 - year_emp)
from    professor

 

AVG

형식: avg(<필드 이름>)

- 지정된 필드에 저장된 값들의 평균을 계산

​

예) 전체 교수의 평균 재직 연수를 출력

select  avg(2019 - year_emp)
from    professor

 

MIN, MAX

형식 : max(<필드 이름>) , min(<필드 이름>)

- 지정된 필드 값들 중 최대값과 최소값을 구함

​

예) 'ACCOUNTING' 부서에 속한 직원들 중 최대 급여가 얼마인지 출력

select  max(sal)
from    emp e, dept d
where   e.deptno = d.deptno and dname = 'ACCOUNTING'

 

❕ select 절에 집계 함수가 사용될 경우 다른 필드는 같이 사용할 수 없음

​

GROUP BY

형식 : group by <필드 리스트>

- group by 절은 select 문에서 where 절 다음에 위치

- group by 절에 지정된 필드의 값이 같은 레코드들끼리 그룹을 지어 각 그룹별로 집계 함수를 적용한 결과를 출력

​

예) 학과(번호)별로 소속 학생 수를 계산하여 출력

select dept_id, count(*)
from   student
group by dept_id

❕ group by 절에 포함되지 않은 필드는 select 절에도 포함될 수 없음

​

예) 학과 번호 대신 학과 이름이 출력되도록 수정

select dept_name, count(*)
from   student s, department d
where  s.dept_id = d.dept_id
group by dept_name

 

HAVING

- 그룹에 대한 조건을 명시할 때 사용

- 그룹에 대한 조건은 where 절에서 표현하지 못하므로 having 절을 이용해야함

형식 : having <집계 함수에 대한 조건>

​

예) 직원 숫자가 5명 이상인 부서에 대해 부서별 직원수, 평균 급여, 최대 급여, 최소 급여를 출력

select  dname, count(*), avg(sal), max(sal), min(sal)
from    emp e, dept d
where   e.deptno = d.deptno
group by dname
having  count(*) >= 5

 

where 절과 having 절, group by절을 모두 함께 사용할 경우

1. from절에 명시된 테이블들을 조인

2. where절에 명시된 조건을 만족하는 레코드들을 검색

3. group by절에 명시된 필드의 값이 같은 레코드들끼리 그룹을 지어 집계 함수를 적용

4. 마지막으로 그 집계 함수를 적용한 결과들 중에서 having 절을 만족하는 결과만 선택

5. select절에 지정된 필드 및 집계 함수 계산 결과 출력

​

null의 처리

형식: <필드 이름> is null, <필드 이름> is not null