Database: election_representative

CREATE TABLE "election" (
"Election_ID" int,
"Representative_ID" int,
"Date" text,
"Votes" real,
"Vote_Percent" real,
"Seats" real,
"Place" real,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY ("Representative_ID") REFERENCES `representative`("Representative_ID")
);
CREATE TABLE "representative" (
"Representative_ID" int,
"Name" text,
"State" text,
"Party" text,
"Lifespan" text,
PRIMARY KEY ("Representative_ID")
);

Question: How many elections are there?
SQL: SELECT count(*) FROM election

Question: List the votes of elections in descending order.
SQL: SELECT Votes FROM election ORDER BY Votes DESC

Question: List the dates and vote percents of elections.
SQL: SELECT Date ,  Vote_Percent FROM election

Question: What are the minimum and maximum vote percents of elections?
SQL: SELECT min(Vote_Percent) ,  max(Vote_Percent) FROM election

Question: What are the names and parties of representatives?
SQL: SELECT Name ,  Party FROM representative

Question: What are the names of representatives whose party is not "Republican"?
SQL: SELECT Name FROM Representative WHERE Party != "Republican"

Question: What are the life spans of representatives from New York state or Indiana state?
SQL: SELECT Lifespan FROM representative WHERE State  =  "New York" OR State  =  "Indiana"

Question: What are the names of representatives and the dates of elections they participated in.
SQL: SELECT T2.Name ,  T1.Date FROM election AS T1 JOIN representative AS T2 ON T1.Representative_ID  =  T2.Representative_ID

Question: What are the names of representatives with more than 10000 votes in election?
SQL: SELECT T2.Name FROM election AS T1 JOIN representative AS T2 ON T1.Representative_ID  =  T2.Representative_ID WHERE Votes  >  10000

Question: What are the names of representatives in descending order of votes?
SQL: SELECT T2.Name FROM election AS T1 JOIN representative AS T2 ON T1.Representative_ID  =  T2.Representative_ID ORDER BY votes DESC

Question: What is the party of the representative that has the smallest number of votes.
SQL: SELECT T2.Party FROM election AS T1 JOIN representative AS T2 ON T1.Representative_ID  =  T2.Representative_ID ORDER BY votes ASC LIMIT 1

Question: What are the lifespans of representatives in descending order of vote percent?
SQL: SELECT T2.Lifespan FROM election AS T1 JOIN representative AS T2 ON T1.Representative_ID  =  T2.Representative_ID ORDER BY Vote_Percent DESC

Question: What is the average number of votes of representatives from party "Republican"?
SQL: SELECT avg(T1.Votes) FROM election AS T1 JOIN representative AS T2 ON T1.Representative_ID  =  T2.Representative_ID WHERE T2.Party  =  "Republican"

Question: What are the different parties of representative? Show the party name and the number of representatives in each party.
SQL: SELECT Party ,  COUNT(*) FROM representative GROUP BY Party

Question: What is the party that has the largest number of representatives?
SQL: SELECT Party ,  COUNT(*) FROM representative GROUP BY Party ORDER BY COUNT(*) DESC LIMIT 1

Question: What parties have at least three representatives?
SQL: SELECT Party FROM representative GROUP BY Party HAVING COUNT(*)  >=  3

Question: What states have at least two representatives?
SQL: SELECT State FROM representative GROUP BY State HAVING COUNT(*)  >=  2

Question: List the names of representatives that have not participated in elections listed here.
SQL: SELECT Name FROM representative WHERE Representative_ID NOT IN (SELECT Representative_ID FROM election)

Question: Show the parties that have both representatives in New York state and representatives in Pennsylvania state.
SQL: SELECT Party FROM representative WHERE State  =  "New York" INTERSECT SELECT Party FROM representative WHERE State  =  "Pennsylvania"

Question: How many distinct parties are there for representatives?
SQL: SELECT count(DISTINCT Party) FROM representative