Database: perpetrator

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

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;