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
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
List the date of perpetrators in descending order of the number of people killed.
assistant
SELECT Date FROM perpetrator ORDER BY Killed DESC
human
How many perpetrators are there?
assistant
SELECT count(*) FROM perpetrator
human
List the names of perpetrators in descending order of the year.
assistant
SELECT T1.Name FROM people AS T1 JOIN perpetrator AS T2 ON T1.People_ID = T2.People_ID ORDER BY T2.Year DESC
human
In which year did the most recent crime happen?
assistant
SELECT MAX( YEAR ) FROM perpetrator ;