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 round(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;
Top Earners
select * from (
select salary * months, count(*) from Employee
group by salary * months
order by 1 desc)
where rownum = 1;
Weather Observation Station 2
select round(sum(LAT_N),2), round(sum(LONG_W),2) from STATION;
Weather Observation Station 13
select round(sum(LAT_N), 4) from STATION
where LAT_N > 38.7880 and LAT_N < 137.2345;
Weather Observation Station 14
select trunc(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 * from (
select round(LONG_W, 4) from STATION
where LAT_N > 38.7780
order by LAT_N asc)
where rownum = 1;
Weather Observation Station 18
select round(((max(LAT_N) - min(LAT_N)) + (max(LONG_W) - min(LONG_W))),4)
from STATION;
Weather Observation Station 19
select round(sqrt(
power((max(LAT_N) - min(LAT_N)),2) +
power((max(LONG_W) - min(LONG_W)),2)
),4)
from STATION;
Weather Observation Station 20
select round(median(LAT_N),4) from STATION;
