create view popu2015
select
POPU.PF_CODE,
PRE.PF_NAME,
TOTAL_AMT as TOTAL_2015
FROM POPU_TRANSITION AS POPU
INNER JOIN PREFECTURE AS PRE
ON POPU.PF_CODE = PRE.PF_CODE
where SURVEY_YEAR = 2015;

create view popu2020
select
POPU.PF_CODE,
PRE.PF_NAME,
TOTAL_AMT as TOTAL_2020
FROM POPU_TRANSITION AS POPU
INNER JOIN PREFECTURE AS PRE
ON POPU.PF_CODE = PRE.PF_CODE
where SURVEY_YEAR = 2020;


select
p15.PF_CODE as '都道府県コード',
p15.PF_NAME as '都道府県名',
TOTAL_2015 as '総人口2015年',
TOTAL_2020 as '総人口2020年',
round(cast((TOTAL_2020-TOTAL_2015) as real) / cast(TOTAL_2015 as real) * 100) as '人口増加率'
from popu2015 as p15
inner join popu2020 as p20
on p15.PF_CODE = p20.PF_CODE
order by '人口増加率' DESC, PF_CODE ASC