human
Schema:
perpetrator (Perpetrator_ID, People_ID, Date, Year, Location, Country, Killed, Injured)
people (People_ID, Name, Height, Weight, Home Town)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the countries that have both perpetrators with injures more than 50 and perpetrators with injures smaller than 20.
assistant
SELECT Country FROM perpetrator WHERE Injured > 50 INTERSECT SELECT Country FROM perpetrator WHERE Injured < 20
human
How many distinct locations of perpetrators are there?
assistant
SELECT count(DISTINCT LOCATION) FROM perpetrator
human
How many perpetrators are there?
assistant
SELECT count(*) FROM perpetrator
human
What is the country that has the most perpetrators?
assistant
SELECT Country , COUNT(*) FROM perpetrator GROUP BY Country ORDER BY COUNT(*) DESC LIMIT 1
human
What are the countries that have at least two perpetrators?
assistant
SELECT Country , COUNT(*) FROM perpetrator GROUP BY Country HAVING COUNT(*) >= 2
human
What are the countries of perpetrators? Show each country and the corresponding number of perpetrators there.
assistant
SELECT Country , COUNT( * ) FROM perpetrator GROUP BY Country ;