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
How many distinct parties are there for representatives?
assistant
SELECT count(DISTINCT Party) FROM representative
human
What are the names and parties of representatives?
assistant
SELECT Name , Party FROM representative
human
What states have at least two representatives?
assistant
SELECT State FROM representative GROUP BY State HAVING COUNT(*) >= 2
human
What parties have at least three representatives?
assistant
SELECT Party FROM representative GROUP BY Party HAVING COUNT(*) >= 3
human
What are the different parties of representative? Show the party name and the number of representatives in each party.
assistant
SELECT Party , COUNT(*) FROM representative GROUP BY Party
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" ;