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 is the party that has the largest number of representatives?
assistant
SELECT Party , COUNT(*) FROM representative GROUP BY Party ORDER BY COUNT(*) DESC LIMIT 1
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
How many elections are there?
assistant
SELECT count(*) FROM election
human
What are the names of representatives in descending order of votes?
assistant
SELECT T2.Name FROM election AS T1 JOIN representative AS T2 ON T1.Representative_ID = T2.Representative_ID ORDER BY votes DESC
human
What are the names of representatives with more than 10000 votes in election?
assistant
SELECT T2.Name FROM election AS T1 JOIN representative AS T2 ON T1.Representative_ID = T2.Representative_ID WHERE Votes > 10000 ;