11. Introduction to SQL (5) - Aggregate Functions

 

10. 1 concept

  avg: average value
  min:  minimum value
  max:  maximum value
  sum:  sum of values
  count:  number of values

 

10. 2 application

- Find the average salary of instructors in the Computer Science department

select avg (salary)
from instructor
where dept_name= ’Comp. Sci.’;

 

- Find the total number of instructors who teach a course in the Spring 2010 semester

select count (distinct ID)
from teaches
where semester = ’Spring’ and year = 2010

 

- Find the number of tuples in the course relation

select count (*)
from course;

 

10. 3 Group by

- Find the average salary of instructors in each department

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

: 따로따로 그룹을 만든다.

 

10. 4 Having 

- Find the names and average salaries of all departments whose average salary is greater than 42000

select dept_name, avg (salary)

from instructor

group by dept_name

having avg (salary) > 42000;

 

:having은 그룹을 형성한 후에 적용되는 반면, where절은 그룹이 형성되기 전에 적용된다.