SQL 3

Aggregation

  Aggreation 함수들은 데이터들을 정리해서 보여주는 기능을 한다. 다음과 같은 구조로 이뤄진다.

fig 1-1

  여기서도 이전과 동일한 데이터를 기준으로 설명이 진행된다.

fig 1-2

fig 1-3

select avg(GPA) 
from Student;

  avg를 통해 학생들 GPA의 평균을 구할 수 있다. Student 테이블의 GPA attributes의 평균을 구하는 것이므로 쉽게 이해할 수 있다.

 

 

fig 1-4

select min(GPA)
from Student, Apply
where Student.sID = Apply.sID and major = 'CS';

  min을 통해 CS 전공에 지원한 학생들의 GPA 중 가장 낮은 값을 구한다. 당연히 max는 최댓값을 구하는데 사용된다. 

 

 

fig 1-5

select count(*)
from College
where enrollment > 15000;

  count를 통해 조건을 만족하는 튜플의 개수를 구할 수 있다. 여기서는 enrollment가 15000보다 큰 대학들의 개수를 구하고 있다.

 

 

fig 1-6

select count(*)
from Apply
where cName = 'Cornell';

  다음 쿼리에서는 Apply 테이블의 cName 중 'Cornell'이라는 값을 가진 튜플의 개수를 반환한다. 같은 학생이 동일한 대학에 여러개 지원할 수 있으니 이런 경우를 제외시켜주려면 모두를 뜻하는 '*'이 아니라 'distinct sID'를 넣어주면 된다.

 

 

fig 1-7

select cName, count(*) 
from Apply
group by cName;

  group by 절은 그룹화하여 데이터를 조회할 수 있게 한다. 여기서는 cName을 기준으로 각 튜플이 속하는 그룹을 나눈 결과를 반환한다.

 

 

fig 1-8

select state, sum(enrollment) 
from College
group by state;

  이 쿼리는 state를 기준으로 각 그룹에 속한 튜플의 enrollment의 합을 나타내는 쿼리이다.

 

 

fig 1-9

select cName, major, min(GPA), max(GPA) 
from Student, Apply
where Student.sID = Apply.sID
group by cName, major;

  하나의 attibute에만 gruop by를 적용할 수 있는건 아니다. 다음 쿼리처럼 2개의 attributes를 넘겨주면 '대학-전공'을 기준으로 그룹화하여 데이터를 보여준다.

 

 

fig 1-10

select Student.sID, count(distinct cName) 
from Student, Apply
where Student.sID = Apply.sID
group by Student.sID;

  다음 쿼리는 학생의 id로 그룹화하였는데 count에 'distinct cName'이 들어있다. 즉 같은 대학의 다른 전공을 신청한 경우는 하나로 보겠다는 뜻이다. 다르게 말하면 '해당 sID의 학생이 몇 개의 대학에 지원했는가?'에 대한 쿼리이다.

 

 

fig 1-11

select Student.sID, sName, count(distinct cName), cName 
from Student, Apply
where Student.sID = Apply.sID 
group by Student.sID;

  다음 쿼리는 해석해보면 'Student 테이블의 sID를 기준으로 학생의 id와 이름, 지원한 대학 수, 대학 이름을 받아와라' 정도로 해석할 수 있다. 하지만 여기서 한가지 이상한 점을 눈치채야 한다. 한 학생이 여러 대학에 지원한 경우 지원한 대학 수(count(distinct(cName))야 하나의 값으로 나타내서 문제는 없지만 대학 이름(cName)은 여러 개의 값이 들어가야 하기 때문이다. 하지만 관계형 DB에서는 한 셀에 리스트 형태처럼 여러개의 값이 들어가는 것은 불가능하다.

  이에 대한 처리는 sql 시스템마다 다른데, 예를 들어 SQLite에서는 에러 처리하지 않고 여러개의 값 중 하나만 보여주고, PostgreSQL에서는 아예 에러로 처리를 해버린다.

 

 

fig 1-12

fig 1-13

select cName
from Apply
group by cName
having count(*) < 5;

select cName
from Apply
group by cName
having count(distinct sID) < 5;

  having은 group by와 같이 쓰이는 절인데 그룹에 대한 조건을 설정한다. 해당 쿼리에서는 그룹화 된 결과 중 튜플의 수가 5보다 작은 그룹에 대한 결과만 반환하고 있다. 그리고 어떤 차이가 있길래 두 개의 쿼리가 fig 1-12와 fig 1-13 처럼 다른 결과가 반환될까?

  두번째 쿼리의 경우에는 한 학생이 동일한 대학에 여러번 지원한 경우를 따로 보지않고 하나로 처리하기 때문에 조건을 만족하는 그룹이 하나 더 생긴 것이다.

 

NULL

  SQL에서는 NULL이라는 값이 있다. 0이나 공백의 의미가 아니라 아직 정의되지 않은(undefined), 알 수 없는(unknown)의 의미로 사용된다. 

fig 2-1

insert into Student values (432, 'Kevin', null, 1500);
insert into Student values (321, 'Lori', null, 2500);
select * from Student;

  위 처럼 GPA 정보가 없는(null) 학생 정보를 넣어준뒤 Student 테이블 전체를 조회하면 위 처럼 빈칸으로 표시가 된다.

 

 

fig 2-2

select sID, sName, GPA
from Student
where GPA > 3.5 or GPA <= 3.5 or GPA is null;

fig 2-3

select count(distinct GPA) 
from Student
where GPA is not null;

  이러한 null값은 위와 같이 조건으로도 사용될 수 있다. 하지만 null값은 비교연산은 불가능하고 null인지 아닌지 여부만 판단가능하다.

 

Data Modification Statements

  이제 마지막으로 데이터를 조회하는 것 이외에 추가/삭제/수정하는 쿼리를 알아보자. 뒤의 내용들을 보면 알겠지만 여기의 쿼리문들은 한번에 하나의 테이블에만 적용가능하다.

Insert

fig 3-1

  INSERT는 데이터를 추가할 때 사용되고 위와 같은 형태를 가진다. 첫 번째 처럼 값을 설정하여 테이블에 넣어줄 수도 있고, SELECT문의 결과를 테이블에 넣어줄 수 도 있다.

Delete

fig 3-2

  DELETE는 테이블을 선택하고 특정 조건을 만족하는 튜플(row)들을 삭제할 수 있다.

Update

fig 3-3

  UPDATE는 기존의 정보를 새롭게 갱신한다. 테이블을 설정하고 갱신할 튜플들의 새 attribute값을 설정한 뒤, 어떤 조건의 튜플들을 선택할 것인지 조건을 정해준다.

 

예제

fig 3-4

insert into College values ('Carnegie Mellon', 'PA', 11500); 
select * from College;

  하나의 대학 정보를 더 추가하였다.

 

 

fig 3-5

select *
from Student
where sID not in (select sID from Apply);

  다음 쿼리를 통해 아직 대학에 지원하지 않은 학생들의 정보를 가져올 수 있다.

 

 

fig 3-6

insert into Apply
    select sID, 'Carnegie Mellon', 'CS', null 
    from Student
    where sID not in (
	    select sID from Apply);

select * from Apply;

  위의 쿼리를 이용하여 아직 지원을 하지 않은 학생들은 새롭게 추가한 'Carnegie Mellon' 대학에 지원되게끔 처리할 수 있다.

 

 

fig 3-7

delete from Student
where sID in
  (select sID
   from Apply
   group by sID
   having count(distinct major) > 2);
   
select * from Student;

  다음 쿼리문은 두개 보다 많은 전공에 지원한 학생들의 정보를 Student 테이블에서 삭제하는 쿼리이다.

 

 

fig 3-8

update Apply
set decision = 'Y', major = 'economics' 
where cName = 'Carnegie Mellon'
    and sID in (select sID from Student where GPA < 3.6); 
    
select * from Apply;

  다음은 Apply 테이블에서 cName이 Carnegie Mellon이고, GPA가 3.6미만인 학생의 지원 정보를 decision은 'Y', major는 'economics'로 수정하는 쿼리이다.