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

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

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

-- 差分の降順
-- 港コードの降順

-- g.groupcode 110 日本人
-- g.groupcode 120 外国人


select
    tmp.port_code as '港コード'
    ,tmp.port_name as '港名'
    ,tmp.in_amt as '入国者数'
    ,tmp.out_amt as '出国者数'
    ,tmp.sabun as '差分'
from (
    select
        i.port_code
        ,p.port_name
        ,sum(case when i.kind_code = 110 then i.amt else 0 end) as in_amt
        ,sum(case when i.kind_code = 120 then i.amt else 0 end) as out_amt
        ,sum(case when i.kind_code = 110 then i.amt else 0 end) - sum(case when i.kind_code = 120 then i.amt else 0 end) as sabun
    from IMMIGRATION as i
    join GRP as g
        on i.GROUP_CODE = g.GROUP_CODE
        and g.group_code = 120
    join PORT as p
        on i.PORT_CODE = p.PORT_CODE
    group by
        i.port_code
    having
        sum(case when i.kind_code = 110 then i.amt else 0 end) - sum(case when i.kind_code = 120 then i.amt else 0 end) > 0
) as tmp
order by 
    tmp.sabun desc
    ,tmp.port_code desc