-- 港毎に出入国者の人数を集計した出入国集計データより、外国人の入国者が出国者より多い港を抽出しなさい。 種別コードは、'110'(入国者) 、'120'(出国者)となる。

-- 表示項目は以下とする。(エイリアスを使用し→の項目名とする)

-- PORT_CODE → 港コード
-- PORT_NAME → 港名
-- 外国人入国者のAMT → 入国者数
-- 外国人出国者のAMT → 出国者数
-- 外国人入国者のAMT - 外国人出国者のAMTの計算結果 → 差分
-- 表示順

-- 差分の降順
-- 港コードの降順
WITH foreigns AS (
    SELECT
        p.PORT_CODE,
        p.PORT_NAME,
        i.AMT
    FROM
        IMMIGRATION AS i
        INNER JOIN GRP AS g
            ON g.GROUP_CODE = i.GROUP_CODE
        INNER JOIN PORT AS p
            ON p.PORT_CODE = i.PORT_CODE
    WHERE
        i.GROUP_CODE = "120" --外国人
)

SELECT
    p.PORT_CODE,
    p.PORT_NAME,
    SUM(i.AMT) AS out_amt
FROM
    foreigns f
    LEFT OUTER JOIN 
WHERE
    f.KIND_CODE = "120" --入国

-- (
-- SELECT
--     p.PORT_CODE,
--     p.PORT_NAME,
--     SUM(i.AMT) AS in_amt
-- FROM
--     IMMIGRATION AS i
--     INNER JOIN GRP AS g
--         ON g.GROUP_CODE = i.GROUP_CODE
--     INNER JOIN PORT AS p
--         ON p.PORT_CODE = i.PORT_CODE
-- WHERE
--     i.KIND_CODE = "110" --入国
--     AND i.GROUP_CODE = "120" --外国人
-- )