CREATE TABLE perpetrator (
    Perpetrator_ID NUMBER PRIMARY KEY,
    People_ID NUMBER,
    Date TEXT,
    Year NUMBER,
    Location TEXT,
    Country TEXT,
    Killed NUMBER,
    Injured NUMBER,
    FOREIGN KEY ("People_ID") REFERENCES people("People_ID")
);
CREATE TABLE people (
    People_ID NUMBER PRIMARY KEY,
    Name TEXT,
    Height NUMBER,
    Weight NUMBER,
    "Home Town" TEXT
);

Question: How many perpetrators are there?
SQL: SELECT count(*) FROM perpetrator

Question: List the date of perpetrators in descending order of the number of people killed.
SQL: SELECT Date FROM perpetrator ORDER BY Killed DESC

Question: List the number of people injured by perpetrators in ascending order.
SQL: SELECT Injured FROM perpetrator ORDER BY Injured ASC

Question: What is the average number of people injured by all perpetrators?
SQL: SELECT avg(Injured) FROM perpetrator

Question: What is the location of the perpetrator with the largest kills.
SQL: SELECT LOCATION FROM perpetrator ORDER BY Killed DESC LIMIT 1

Question: What are the names of people in ascending order of height?
SQL: SELECT Name FROM People ORDER BY Height ASC

Question: What are the names of perpetrators?
SQL: SELECT T1.Name FROM people AS T1 JOIN perpetrator AS T2 ON T1.People_ID  =  T2.People_ID

Question: What are the names of perpetrators whose country is not "China"?
SQL: SELECT T1.Name FROM people AS T1 JOIN perpetrator AS T2 ON T1.People_ID  =  T2.People_ID WHERE T2.Country != "China"

Question: What is the name of the perpetrator with the biggest weight.
SQL: SELECT T1.Name FROM people AS T1 JOIN perpetrator AS T2 ON T1.People_ID  =  T2.People_ID ORDER BY T1.Weight DESC LIMIT 1

Question: What is the total kills of the perpetrators with height more than 1.84.
SQL: SELECT sum(T2.Killed) FROM people AS T1 JOIN perpetrator AS T2 ON T1.People_ID  =  T2.People_ID WHERE T1.Height  >  1.84

Question: What are the names of perpetrators in country "China" or "Japan"?
SQL: 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"

Question: What are the heights of perpetrators in descending order of the number of people they injured?
SQL: SELECT T1.Height FROM people AS T1 JOIN perpetrator AS T2 ON T1.People_ID  =  T2.People_ID ORDER BY T2.Injured DESC

Question: What are the countries of perpetrators? Show each country and the corresponding number of perpetrators there.
SQL: SELECT Country ,  COUNT(*) FROM perpetrator GROUP BY Country

Question: What is the country that has the most perpetrators?
SQL: SELECT Country ,  COUNT(*) FROM perpetrator GROUP BY Country ORDER BY COUNT(*) DESC LIMIT 1

Question: What are the countries that have at least two perpetrators?
SQL: SELECT Country ,  COUNT(*) FROM perpetrator GROUP BY Country HAVING COUNT(*)  >=  2

Question: List the names of perpetrators in descending order of the year.
SQL: SELECT T1.Name FROM people AS T1 JOIN perpetrator AS T2 ON T1.People_ID  =  T2.People_ID ORDER BY T2.Year DESC

Question: List the names of people that are not perpetrators.
SQL: SELECT Name FROM people WHERE People_ID NOT IN (SELECT People_ID FROM perpetrator)

Question: Show the countries that have both perpetrators with injures more than 50 and perpetrators with injures smaller than 20.
SQL: SELECT Country FROM perpetrator WHERE Injured  >  50 INTERSECT SELECT Country FROM perpetrator WHERE Injured  <  20

Question: How many distinct locations of perpetrators are there?
SQL: SELECT count(DISTINCT LOCATION) FROM perpetrator

Question: Show the date of the tallest perpetrator.
SQL: SELECT T2.Date FROM people AS T1 JOIN perpetrator AS T2 ON T1.People_ID  =  T2.People_ID ORDER BY T1.Height DESC LIMIT 1

Question: In which year did the most recent crime happen?
SQL: SELECT max(YEAR) FROM perpetrator;