Revising Aggregations - The Count Function

select count(*)
from city
where population > 100000

Revising Aggregations - The Sum Function

select sum(population)
from city
where district = 'California'

Revising Aggregations - Averages

select AVG(population)
from city
where district = 'California'

Average Population

select floor(avg(population))
from city

Japan Population

select sum(population)
from city
where countrycode = 'JPN';

Population Density Difference

select max(population) - min(population)
from city

The Blunder

select ceil(avg(salary) - avg(replace(salary,'0', '')))
from employees

replace 함수

Top Earners

select max(months*salary),count(months*salary) 
from employee 
where employee_id in( select employee_id 
                      from employee 
                      where months*salary in (select max(months*salary) 
                                              from employee));

Weather Observation Station 2

select round(sum(lat_n), 2), round(sum(long_w), 2)
from station

Weather Observation Station 13

select truncate(sum(lat_n), 4)
from station
where lat_n between 38.7880 and 137.2345

Weather Observation Station 14

select truncate(max(lat_n), 4)
from station
where lat_n < 137.2345

Weather Observation Station 15

select round(long_w, 4)
from station
where lat_n = (select max(lat_n)
              from station
              where lat_n < 137.2345);

잘풀었다.

Weather Observation Station 16

select round(min(lat_n),4)
from station
where lat_n > 38.7780

Weather Observation Station 17

select round(long_w, 4)
from station
where lat_n > 38.7780
order by lat_n
limit 1;

Weather Observation Station 18

select round(abs(min(lat_n) - max(lat_n)) + abs(min(long_w) - max(long_w)), 4)
from station

Weather Observation Station 19

select truncate(sqrt (((min(lat_n) - max(lat_n)) * (min(lat_n) - max(lat_n))) + ((min(long_w) - max(long_w)) * (min(long_w) - max(long_w)))),4)
from station

Weather Observation Station 20

set @rowindex := -1; /* 1) creates an index*/ 
/* 3) the outer query will select the average of the 2
(for odd no. of values)/1(for even) 
values we found in the middle of the sorted array */
select round(avg(lat_n),4)
from
/* 2) the index will increment for each new value of lat_n it finds,
and sort them by lat_n*/
(select @rowindex:=@rowindex+1 as rowindex, lat_n 
from station
order by lat_n) as l 
where l.rowindex in (floor(@rowindex/2), ceil(@rowindex/2));

median, 중앙값 찾기 문제