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 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
List the dates of debates with number of audience bigger than 150
assistant
SELECT Date FROM debate WHERE Num_of_Audience > 150
human
Show the distinct venues of debates
assistant
SELECT DISTINCT Venue FROM debate
human
What are the date and venue of each debate?
assistant
SELECT Date , Venue FROM debate
human
List the venues of debates in ascending order of the number of audience.
assistant
SELECT Venue FROM debate ORDER BY Num_of_Audience ASC
human
How many debates are there?
assistant
SELECT COUNT( * ) FROM debate ;