【問題1】東京都の人口

SELECT
    DISTRICT_CODE AS 地区コード
    , DISTRICT_NAME AS 地区名
    , TOTAL_AMT AS 総人口 
FROM
    POPULATION 
ORDER BY
    TOTAL_AMT DESC
    , DISTRICT_CODE DESC;

【問題2】睡眠時間帯別集計

SELECT
    STD.AGE_CODE AS 年齢コード
    , AG.AGE_NAME AS 年齢階層名
    , SUM(STD.SP_TIME_5) AS "5時間未満"
    , SUM(STD.SP_TIME_6) AS "5時間以上6時間未満"
    , SUM(STD.SP_TIME_7) AS "6時間以上7時間未満"
    , SUM(STD.SP_TIME_8) AS "7時間以上8時間未満"
    , SUM(STD.SP_TIME_9) AS "8時間以上9時間未満"
    , SUM(STD.SP_TIME_9OVER) AS "9時間以上" 
FROM
    SLEEP_TIME_DTL AS STD 
    INNER JOIN AGE_GRP AS AG 
        ON AG.AGE_CODE = STD.AGE_CODE
    INNER JOIN PREFECTURE AS PF
        ON STD.PF_CODE = PF.PF_CODE 
WHERE
   PF.PF_NAME IN ('北海道', '青森県', '岩手県', '宮城県', '福島県')
GROUP BY
    STD.AGE_CODE 
ORDER BY
    STD.AGE_CODE ASC;

(改定日：2022年７月26日)

【問題3】港入出国者分析

SELECT
    IM1.PORT_CODE AS 港コード
    , PT.PORT_NAME AS 港名
    , IM1.AMT AS 入国者数
    , IM2.AMT AS 出国者数
    , (IM1.AMT - IM2.AMT) AS 差分 
FROM
    IMMIGRATION AS IM1 
    INNER JOIN IMMIGRATION AS IM2 
        ON IM2.PORT_CODE = IM1.PORT_CODE 
        AND IM2.GROUP_CODE = IM1.GROUP_CODE 
        AND IM2.KIND_CODE = '120' 
    INNER JOIN PORT AS PT 
        ON PT.PORT_CODE = IM1.PORT_CODE 
WHERE
    IM1.GROUP_CODE = '120' 
    AND IM1.KIND_CODE = '110' 
    AND 差分 > 0 
ORDER BY
    差分 DESC
    , IM1.PORT_CODE DESC;

【問題4】外国籍分布

SELECT
    PF_CODE AS 都道府県コード
    , PF_NAME AS 都道府県名
    , MAX(CASE SUB.RANK WHEN 1 THEN NATION_NAME ELSE NULL END) AS "1位 国名"
    , MAX(CASE SUB.RANK WHEN 1 THEN AMT ELSE 0 END) AS "1位 人数"
    , MAX(CASE SUB.RANK WHEN 2 THEN NATION_NAME ELSE NULL END) AS "2位 国名"
    , MAX(CASE SUB.RANK WHEN 2 THEN AMT ELSE 0 END) AS "2位 人数"
    , MAX(CASE SUB.RANK WHEN 3 THEN NATION_NAME ELSE NULL END) AS "3位 国名"
    , MAX(CASE SUB.RANK WHEN 3 THEN AMT ELSE 0 END) AS "3位 人数"
    , SUM(AMT) AS 合計人数 
FROM
    ( 
        SELECT
            F.PF_CODE AS PF_CODE
            , P.PF_NAME AS PF_NAME
            , N.NATION_NAME AS NATION_NAME
            , F.AMT AS AMT
            , RANK() OVER ( 
                PARTITION BY
                    F.PF_CODE 
                ORDER BY
                    AMT DESC
                    , F.NATION_CODE
            ) AS RANK                           
        FROM
            FOREIGNER AS F 
            INNER JOIN NATIONALITY AS N 
                ON N.NATION_CODE = F.NATION_CODE 
            INNER JOIN PREFECTURE AS P 
                ON P.PF_CODE = F.PF_CODE 
        WHERE
            F.NATION_CODE != '113'
    ) AS SUB 
GROUP BY
    SUB.PF_CODE 
    , SUB.PF_NAME
ORDER BY
    合計人数 DESC
    , 都道府県コード ASC;

(改定日：2022年７月26日)