[database] [COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING]

 

--count
select count(*) as total_classroom
from classroom

select * from instructor

select count(dept_name) as x
from instructor

select * from classroom

select count(distinct building) as x
from classroom

 

--sum
select sum(budget) as total_budget
from department

select sum(capacity) as total_capacity
from classroom
where building = 'Waston'

select dept_name, sum(salary) as total_salary
from instructor
where salary is null
group by dept_name

 

--AVG
select round(cast(avg(budget) as decimal(10, 2)), 2) as avg_budget
from department

select avg(capacity) as avg_capacity
from classroom

 

설명

AVG(budget)	budget 컬럼의 평균값을 구함
CAST(... AS DECIMAL(10, 2))	평균값을 소수점 둘째 자리까지 표현 가능한 10자리 숫자로 변환 (예: 12345678.90)
ROUND(..., 2)	위의 값을 소수점 둘째 자리까지 반올림
AS avg_budget	결과 컬럼 이름을 avg_budget으로 지정
FROM department	department 테이블에서 데이터를 가져옴

 

 

--MIN
select min(capacity) as min_capacity
from classroom

select min(start_time) as x
from time_slot

select dept_name, min(salary) as min_salary
from instructor
group by dept_name

 

--MAX
select max(capacity) as min_capacity
from classroom

select max(start_time) as x
from time_slot

select building, max(budget) as max_budget
from department
where building is not null
group by building

예시
| dept\_id | dept\_name  | budget | building |
| -------- | ----------- | ------ | -------- |
| 1        | Engineering | 500000 | Main     |
| 2        | Math        | 300000 | North    |
| 3        | CS          | 700000 | Main     |
| 4        | History     | 250000 | South    |
| 5        | Philosophy  | 200000 | South    |
| 6        | Art         | 150000 | NULL     |
| 7        | Physics     | 350000 | North    |

실행 결과
| building | max\_budget |
| -------- | ----------- |
| Main     | 700000      |
| North    | 350000      |
| South    | 250000      |

 

--MAX
select building, count(*) as c_c
from classroom
group by building

select dept_name, round(cast(avg(salary) as decimal(10, 2)), 2) as avg_salary
from instructor
where dept_name is not null
group by dept_name
order by avg_salary desc

select building,
	count(*) as c_c,
	sum(capacity) as t_c,
	avg(capacity) as a_c
from classroom
group by building

 

 

--Having
select building, count(*) as c_c
from classroom
group by building
having count(*) > 7

select dept_name, sum(credits) as t_c
from course
where dept_name is not null
group by dept_name
having sum(credits) > 10

select building, sum(capacity) as t_c
from classroom
group by building
having sum(capacity) > 500
order by t_c desc

select dept_name,
	count(*)  as i_c,
	round(cast(avg(salary) as decimal(10, 2)), 2) as average_salary
	from instructor
	where dept_name is not null
	group by dept_name
	having count(*) > 1 and avg(salary) > 50000
	order by average_salary desc

select building,
	count(*) as c_c,
	max(capacity) as m_c,
	sum(capacity) as t_c
from classroom
group by building
having sum(capacity) > 300 and max(capacity) > 50
order by t_c desc

 

SELECT ...
FROM ...
[WHERE ...]
[GROUP BY ...]
[HAVING ...]
[ORDER BY ...]
순서 지켜야 함