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
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 heights of perpetrators in descending order of the number of people they injured?
assistant
SELECT T1.Height FROM people AS T1 JOIN perpetrator AS T2 ON T1.People_ID = T2.People_ID ORDER BY T2.Injured DESC
human
What are the countries that have at least two perpetrators?
assistant
SELECT Country , COUNT(*) FROM perpetrator GROUP BY Country HAVING COUNT(*) >= 2
human
List the number of people injured by perpetrators in ascending order.
assistant
SELECT Injured FROM perpetrator ORDER BY Injured ASC
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
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 ;