데이터의 검색(query) - 2 중급편

aggregation function

avg, min, max, sum, count등의 명령어를 통해 해당 속성들에 대한 보다 자세한 정보를 추출할 수 있다. 

 

select부에 사용한다. 

select avg(salary) from ~~~~	//연봉의 평균

select count(*) from ~~~	//총 튜플의 개수

 

group by

해당 속성이 일치하는 튜플들을 그룹으로 묶는 것

select dept_name, avg(salary)
from instructor
group by dept_name

위와 같이 학과명 별로 그룹으로 묶은 후, 해당 그룹의 평균 연봉을 계산할 수 있다. 

 

이때 주의할 것이있다.

group by에 의해 하나의 튜플로 묶인 경우, 한 튜플의 한 속성에는 하나의 값만 올 수 있다. 

가령, select 속성으로 instructor_name을 추가할 경우 한 학과의 튜플에 여러 강사의 이름이 들어갈 수 없게 됨으로 의도한 것과 다른 결과가 나올 수 있다. 

 

having

group by의 조건문으로 사용된다. 

select dept_name, avg(salary)
from instructor
group by dept_name
having avg(salary)>8000

group by에는 where대신 having이 쓰이는 것!

 

 

 

if문

select문에 사용한다.

SELECT Animal_id, if(sex_upon_intake like 'intact%','X','O') as 중성화  
from animal_ins

c의 3항연산자처럼 if부를 만족할 경우 'X'를, 만족하지 않을 경우 'O'를 중성화 colunm에 출력한다. 

 

중첩 query문

query문을 중첩해서 사용할 수 있다. 

 

select, from, where과 select문 앞부분 - 총 4 부분에 subquery가 올 수 있다.

 

where clause 내에 subquery 삽입

in, not in

some, all

exist, not exist

unique

등의 명령어를 같이 사용할 수 있다.

 

in, not in

select distinct course_id
from section
where semester='Fall' and year=2017 and
    course_id in (
        select course_id
        from section
        where semester='Spring' and year=2017)

course_id가 in 안의 sql문의 결과 relation 내에 있을 경우 해당 course_id를 추출한다. 

not in의 경우, in 안의 결과 relation 내에 없을 경우 해당 course_id를 추출한다. 

 

course_id 하나의 속성 뿐 아니라, 여러 속성의 비교 역시 가능하다. 

where (course_id,sec_id,semester,year) in (select course_id, sec_id,semester,year from ~~~~)

 

some, all

select name
from instructor
where salary > some (
    select salary
    from instructor
    where dept_name='Biology')

some 내의 결과 테이블 중 하나의 튜플 이상보다 salary가 높을 경우 해당 교수의 이름을 추출. 

all의 경우 some 내의 결과 테이블 중 모든 튜플보다 salary가 높을 경우 해당 교수의 이름을 추출

 

exist, not exist

where name='KIM' exist(
    select *
    from instructor)

exist 내의 결과 테이블 중 name=KIM인 tuple이 있을 경우, 해당 튜플을 추출한다. 

 

unique

비교 후 2개 이상이면 false를, 미만이면 true를 return

 

from clause 내에 subquery를 사용

select dept_name, avg_salary
from (select dept_name, avg(salary) as avg_salary
    from instructor
    group by dept_name)
where avg_salary>8200

from문 내의 result table을 토대로 where, select명령을 실행

 

 

select clause 내에 subquery를 사용

하나의 속성만 갖는 output table은 select부의 subquery로 올 수 있다.

select dept_name,(
    select count(*)
    from instructor
    where department.dept_name=instructor.dept_name) as number_ins
from department

 

with clause

임시 table을 정의한 후, 이 임시 table은 query문이 끝나면 소멸한다. 

with max_budget(values) as (select max(budget) from department)
select departemnt.name
from department,max_budget
where department.budget=max_budget.value

with문을 통해 max_budget이라는 임시 테이블을  as 뒤의 것으로 정의. 

 

 

 

 

join

join은 from clause에 사용된다. 

 

1) natural join

2) inner join

3) outer join

 

1) natural join

두 relation중 같은 이름을 갖는 속성을 비교해서 그 값이 일치하면 추출한다. 

select *
from student,takes
where student.id=takes.id

student와 takes에 공통되는 속성명이 id만 있을 경우, 이 query문은 다음 join문과 같다

select *
from student natural join takes

 

이 경우 공통속성 id는 한번만 표기한다. 

 

여러 개의 relation을 natural join할 수 있다.

from r1 natural join r2 natural join r3

 

주의사항!!

  natural join은 이름이 같은 속성 모두를 비교한다. 

  가령 id만 비교하고자 했지만, dept_name이라는 속성을 공통적으로 갖고 있을 경우, dept_name 값도 일치하는 튜플들만 추출한다. 

 

공통되는 속성명이 여러 개가 있고, 그 중 일부만을 사용해 join하고 싶을 경우 다음과 같은 방법을 사용한다.

 

using: 특정 속성만을 비교해서 join

from student join course using (course_id)

이 경우, student table과 course table을 course_id만 사용해서 join한다. 

 

on: 특정 조건을 사용해서 join

from student join takes on student_id=takes_id

student table과 takes table을 on 뒤의 조건을 사용해서 join

 

 

2)outer join

natural join으로부터 유실되는 정보 보호를 위한 명령

A natural right outer join B	//B에서 누락된 정보를 보호
A natural left outer join B	//A에서 누락된 정보를 보호
A natural full outer join B	//A,B 모두에서 누락된 정보를 보호

 

3) inner join

outer join과 다르게 유실되는 정보를 저장하지 않는 join이다.