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 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
List the names of people that have not been on the affirmative side of debates.
assistant
SELECT Name FROM people WHERE People_id NOT IN (SELECT Affirmative FROM debate_people)
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 who have been on the negative side of debates at least twice.
assistant
SELECT T2.Name FROM debate_people AS T1 JOIN people AS T2 ON T1.Negative = T2.People_ID GROUP BY T2.Name HAVING COUNT(*) >= 2
human
Show the names of people, and dates and venues of debates they are on the affirmative side.
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.Affirmative = T3.People_ID
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 ;