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
List the names of people that are not perpetrators.
assistant
SELECT Name FROM people WHERE People_ID NOT IN (SELECT People_ID FROM perpetrator)
human
What are the names of perpetrators?
assistant
SELECT T1.Name FROM people AS T1 JOIN perpetrator AS T2 ON T1.People_ID = T2.People_ID
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 names of perpetrators in country 'China' or 'Japan'?
assistant
SELECT T1.Name FROM people AS T1 JOIN perpetrator AS T2 ON T1.People_ID = T2.People_ID WHERE T2.Country = 'China' OR T2.Country = 'Japan'
human
What are the names of perpetrators whose country is not 'China'?
assistant
SELECT T1.Name FROM people AS T1 JOIN perpetrator AS T2 ON T1.People_ID = T2.People_ID WHERE T2.Country != "China" ;