DBMS - (12) aggregation function

DBMS - (12) aggregation function

SELECT A1,A2,...,An
FROM R1,R2,...,Rm
WHERE condition
GROUP BY columns
HAVING condition

aggregation function : max, min, sum, avg, count

<그림 1: relation example>

Q1)
select avg(GPA)
from Student;

전체 학생들의 평균 GPA

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

CS에 지원한 학생들의 최소 GPA

Q3)
select avg(GPA)
from Student, Apply
where Student.sID = Apply.sID and major = 'cs';

CS에 지원한 학생들의 평균 GPA.
그러나, Q3 쿼리의 경우 duplicate issue가 존재함.

Q4)
select avg(GPA)
from Student, Apply
where sID in (select sID from Apply where major = 'cs');

Q3의 duplicate issue를 해결한 형태

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

학생수가 15000명 이상인 대학교의 숫자

Q6)
select count(*)
from Apply
where cName = 'cornell'

cornell에 지원한 applicants의 숫자

Q7)
select count(distinct sID)
from Apply
where cName = 'cornell'

cornell에 지원한 학생의 숫자

Q8)
select *
from Student S1
where (select count(*) from Student S2 where S2.sID <> S1.sID and S2.GPA = S1.GPA)
= (select count(*) from Student S2 where S2.sID <> S1.sID and S2.sizeHS = sizeHS);

where 절에서 aggregation function을 사용할 수 있음을 보여주는 예시

Q9)
select CS.avgGPA - NonCS.avgGPA
from (select avg(GPA) as avgGPA from Student where sID in (select sID from Apply where major = 'cs')) as CS,
(select avg(GPA) as avgGPA from Student where sID not in (select sID from Apply where major = 'cs')) as NonCS

GROUP BY columns? aggregation function과 함께 쓰임

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

각 학교별 지원서의 수

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

각 state별 학생수의 총합

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

각 학교, 전공별 최소 GPA, 최대 GPA

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

학생별 지원한 학교의 수

Q14)★★★
select Student.sID, count(distinct cName)
from Student, Apply
where Student.sID = Apply.sID
group by Student.sID
// 학생별 지원한 학교의 수
union
select Student.sID, 0
from Student
where sID not in (select sID from Apply);
// 지원을 하지 않은 학생은 0으로 표기

HAVING condition? group by를 결정하는 condition으로 사용됨
Q15)
select cName
from Apply
group by cName
having count(*) < 5;

학교별 받은 지원서의 수가 5미만인 학교만 반환

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

학교별 받은 학생 수가 5미만인 학교만 반환

Q17)
select major
from Student, Apply
where Student.sID = Apply.sID
group by major
having max(GPA) < (select avg(GPA) from Student);

major별로 그룹을 나누고, 해당 그룹의 최대 GPA가 학생들 평균 GPA보다 낮은 major만 반환

+ group by, having은 추가적인 표현력을 제공하지 않는다.(=즉, select from where만을 이용해서 복잡한 형태로 표현할 수 있다.)

Reference
https://www.youtube.com/watch?v=428B57dOxcE&list=PL6hGtHedy2Z4EkgY76QOcueU8lAC4o6c3&index=15