select to_char(sum("2011"),'999G999G999G999G999G999G990') pop_2011,
to_char(sum("2012"),'999G999G999G999G999G999G990') pop_2012,
to_char(sum("2012") -sum("2011"),'999G999G999G999G999G999G990') "1 yr increase",
to_char(100*((sum("2012")-sum("2011"))/sum("2011")),'999G999D00') "1 yr % increase",
to_char(sum("2012")-sum("2002"),'999G999G999G999G999G999G990') "10yr increase",
to_char(100*((sum("2012")-sum("2001"))/sum("2001")),'999G999D00') "10yr % increase",
to_char(sum("2012")-sum("1962"),'999G999G999G999G999G999G990') "50 yr increase",
to_char(100*((sum("2012")-sum("1962"))/sum("1962")),'999G999D00') "50yr % increase",
count(*) countries
from world.world_population| POP_2011 | POP_2012 | 1 yr increase | 1 yr % increase | 10yr increase | 10yr % increase | 50 yr increase | 50yr % increase | COUNTRIES |
|---|---|---|---|---|---|---|---|---|
| 6,943,259,712 | 7,020,640,890 | 77,381,178 | 1.11 | 785,113,761 | 14.01 | 3,908,038,054 | 125.56 | 215 |
select * from
(
select COUNTRY, country_code, to_char("2012" ,'999G999G999G999G999G999G990') "2012"
from world.world_population
where "2012" is not null
order by "2012" desc
) c
where rownum <= 10| COUNTRY | COUNTRY_CODE | 2012 |
|---|---|---|
| China | CHN | 1,350,695,000 |
| India | IND | 1,236,686,732 |
| United States | USA | 313,914,040 |
| Indonesia | IDN | 246,864,191 |
| Brazil | BRA | 198,656,019 |
| Pakistan | PAK | 179,160,111 |
| Nigeria | NGA | 168,833,776 |
| Bangladesh | BGD | 154,695,368 |
| Russian Federation | RUS | 143,533,000 |
| Japan | JPN | 127,561,489 |
select rank, country, country_code, "2012", decode(rank,1,null,lead) lead
from
(
select rank() over (order by "2012" desc) rank,
country,
country_code,
to_char("2012",'999G999G999G999G990') "2012",
to_char(lead("2012", 1, 0) over (order by "2012") - "2012",'999G999G999G999G999G990') lead
from world.world_population
where "2012" is not null
order by "2012" desc
) c
where rownum <= 10| RANK | COUNTRY | COUNTRY_CODE | 2012 | LEAD |
|---|---|---|---|---|
| 1 | China | CHN | 1,350,695,000 | - |
| 2 | India | IND | 1,236,686,732 | 114,008,268 |
| 3 | United States | USA | 313,914,040 | 922,772,692 |
| 4 | Indonesia | IDN | 246,864,191 | 67,049,849 |
| 5 | Brazil | BRA | 198,656,019 | 48,208,172 |
| 6 | Pakistan | PAK | 179,160,111 | 19,495,908 |
| 7 | Nigeria | NGA | 168,833,776 | 10,326,335 |
| 8 | Bangladesh | BGD | 154,695,368 | 14,138,408 |
| 9 | Russian Federation | RUS | 143,533,000 | 11,162,368 |
| 10 | Japan | JPN | 127,561,489 | 15,971,511 |