human
Schema:
people (People_ID, District, Name, Party, Age)
debate (Debate_ID, Date, Venue, Num_of_Audience)
debate_people (Debate_ID, Affirmative, Negative, If_Affirmative_Win)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the party that has the most people.
assistant
SELECT Party FROM people GROUP BY Party ORDER BY COUNT(*) DESC LIMIT 1
human
Show the names of people that are on affirmative side of debates with number of audience bigger than 200.
assistant
SELECT T3.Name FROM debate_people AS T1 JOIN debate AS T2 ON T1.Debate_ID = T2.Debate_ID JOIN people AS T3 ON T1.Affirmative = T3.People_ID WHERE T2.Num_of_Audience > 200
human
Show the names of people and the number of times they have been on the affirmative side of debates.
assistant
SELECT T2.Name , COUNT(*) FROM debate_people AS T1 JOIN people AS T2 ON T1.Affirmative = T2.People_ID GROUP BY T2.Name
human
Show the names of people, and dates and venues of debates they are on the negative side, ordered in ascending alphabetical order of name.
assistant
SELECT T3.Name , T2.Date , T2.Venue FROM debate_people AS T1 JOIN debate AS T2 ON T1.Debate_ID = T2.Debate_ID JOIN people AS T3 ON T1.Negative = T3.People_ID ORDER BY T3.Name ASC
human
Show different parties of people along with the number of people in each party.
assistant
SELECT Party , COUNT(*) FROM people GROUP BY Party
human
Show the names of people aged either 35 or 36.
assistant
SELECT Name FROM people WHERE Age = 35 OR Age = 36 ;