human
Schema:
election (Election_ID, Representative_ID, Date, Votes, Vote_Percent, Seats, Place)
representative (Representative_ID, Name, State, Party, Lifespan)
Please answer with SQL.
assistant
Sure, I understand.
human
What are the names and parties of representatives?
assistant
SELECT Name , Party FROM representative
human
What are the names of representatives and the dates of elections they participated in.
assistant
SELECT T2.Name , T1.Date FROM election AS T1 JOIN representative AS T2 ON T1.Representative_ID = T2.Representative_ID
human
Show the parties that have both representatives in New York state and representatives in Pennsylvania state.
assistant
SELECT Party FROM representative WHERE State = 'New York' INTERSECT SELECT Party FROM representative WHERE State = 'Pennsylvania'
human
What states have at least two representatives?
assistant
SELECT State FROM representative GROUP BY State HAVING COUNT(*) >= 2
human
What are the lifespans of representatives in descending order of vote percent?
assistant
SELECT T2.Lifespan FROM election AS T1 JOIN representative AS T2 ON T1.Representative_ID = T2.Representative_ID ORDER BY Vote_Percent DESC
human
What are the life spans of representatives from New York state or Indiana state?
assistant
SELECT Lifespan FROM representative WHERE State = "New York" OR State = "Indiana" ;