PostgreSQL query issue 2

Question

The relations are:

Event (etype, description)                     primary key - etype

City (cname, country, population)              primary key - cname

Disaster (cname, dyear, etype, casualties)     primary key - cname, dyear

I need to write a query which tells which country faced all types (etype in relation) of disasters.

How do i do that?

I have this so far:

select country
from city natural join disaster, (select count(etype) as a
                                  from event) as A
group by country, etype
having count(country) = max(A.a)

It gives back all countries that faced any disaster that > disaster types in event relation — Arthur
– Arthur, Commented Nov 17, 2018 at 22:56
For your tables, you need to add country into disaster table against the common named cities among the countries. — Barbaros Özhan
– Barbaros Özhan, Commented Nov 17, 2018 at 23:25
Consider this db-fiddle.com/f/tgfz1oA9KFPho8ezcQTEDL/0 interesting case for common named cities. Certianly there should be USA but at least one of the other countries shouldn't appear in the list. — Barbaros Özhan
– Barbaros Özhan, Commented Nov 17, 2018 at 23:41

Gordon Linoff · Accepted Answer · 2018-11-17 22:58:37Z

2

You can always count:

select c.country
from disasters d join
     cities c
     on d.cname = c.cname
group by c.country
having count(distinct d.etype) = (select count(*) from events);

answered Nov 17, 2018 at 22:58

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Arthur Over a year ago

I cant understand this goup by. You combined both tables together but you group only by table c?

Gordon Linoff Over a year ago

Well, the country and event are not in the same table, so something has to be joined together.

Collectives™ on Stack Overflow

PostgreSQL query issue 2

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related