-- 1
select cname
from city
where population > 2000000

-- 2
select country
from city as c
where exists (
    select cname
    from disaster
    where dyear >= 1980 and
          dyear <= 1990 and
          casualties < 50 and
          cname = c.cname
    )

(select cname, etype, dyear
        from disaster
        where dyear<(date_part('year', current_date) - 100))
-- 3
select *
from disaster a
where
    a.dyear<(date_part('year', current_date) - 100) and
    not exists (
        select *
        from disaster b
        where a.dyear + 100 >= b.dyear and
        a.cname = b.cname and
        a.dyear < b.dyear
      )

-- 4
select a.cname, count(a.cname)
    from disaster a
    where a.dyear > (date_part('year', current_date) - 50)
    group by a.cname
    having count(a.cname) = (
        select max(a.max)
        from    (select a.cname, count(a.cname) as max
            from disaster a
            where a.dyear > (date_part('year', current_date) - 50)
            group by a.cname) a
        )

-- 5
select a.country
    from (select *, sum::float / population as min
            from (select cname, sum(casualties)
            from prediction
            group by cname) x natural join city) a
    where a.min = (
        select min(b.min)
        from    (select min(sum::float / population) as min
            from (select cname, sum(casualties)
            from prediction
            group by cname) x natural join city) b
        )

-- 6
select a.cname
from city natural join disaster as a
group by a.cname
having count(distinct etype) =
    (select count(distinct etype)
    from event)

-- 7
select *
from disaster as d natural join
    (select *
    from (select a.etype, a.provider, b.provider, (a.percent + b.percent)::float / 200 as sum
        from measures a, measures b
        where a.mcost + b.mcost < 1000000 and a.provider < b.provider) a
    where a.sum =
            (select max((a.percent + b.percent)::float / 200) as sum
            from measures a, measures b
            where a.mcost + b.mcost < 1000000 and a.provider < b.provider)) as c