Database: election

CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
);
CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
);
CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
);

Question: How many counties are there in total?
SQL: SELECT count(*) FROM county

Question: Count the total number of counties.
SQL: SELECT count(*) FROM county

Question: Show the county name and population of all counties.
SQL: SELECT County_name ,  Population FROM county

Question: What are the name and population of each county?
SQL: SELECT County_name ,  Population FROM county

Question: Show the average population of all counties.
SQL: SELECT avg(Population) FROM county

Question: On average how large is the population of the counties?
SQL: SELECT avg(Population) FROM county

Question: Return the maximum and minimum population among all counties.
SQL: SELECT max(Population) ,  min(Population) FROM county

Question: What are the maximum and minimum population of the counties?
SQL: SELECT max(Population) ,  min(Population) FROM county

Question: Show all the distinct districts for elections.
SQL: SELECT DISTINCT District FROM election

Question: What are the distinct districts for elections?
SQL: SELECT DISTINCT District FROM election

Question: Show the zip code of the county with name "Howard".
SQL: SELECT Zip_code FROM county WHERE County_name  =  "Howard"

Question: What is the zip code the county named "Howard" is located in?
SQL: SELECT Zip_code FROM county WHERE County_name  =  "Howard"

Question: Show the delegate from district 1 in election.
SQL: SELECT Delegate FROM election WHERE District  =  1

Question: Who is the delegate of district 1 in the elections?
SQL: SELECT Delegate FROM election WHERE District  =  1

Question: Show the delegate and committee information of elections.
SQL: SELECT Delegate ,  Committee FROM election

Question: What are the delegate and committee information for each election record?
SQL: SELECT Delegate ,  Committee FROM election

Question: How many distinct governors are there?
SQL: SELECT count(DISTINCT Governor) FROM party

Question: Count the number of distinct governors.
SQL: SELECT count(DISTINCT Governor) FROM party

Question: Show the lieutenant governor and comptroller from the democratic party.
SQL: SELECT Lieutenant_Governor ,  Comptroller FROM party WHERE Party  =  "Democratic"

Question: Who are the lieutenant governor and comptroller from the democratic party?
SQL: SELECT Lieutenant_Governor ,  Comptroller FROM party WHERE Party  =  "Democratic"

Question: In which distinct years was the governor "Eliot Spitzer"?
SQL: SELECT DISTINCT YEAR FROM party WHERE Governor  =  "Eliot Spitzer"

Question: Find the distinct years when the governor was named "Eliot Spitzer".
SQL: SELECT DISTINCT YEAR FROM party WHERE Governor  =  "Eliot Spitzer"

Question: Show all the information about election.
SQL: SELECT * FROM election

Question: Return all the information for each election record.
SQL: SELECT * FROM election

Question: Show the delegates and the names of county they belong to.
SQL: SELECT T2.Delegate ,  T1.County_name FROM county AS T1 JOIN election AS T2 ON T1.County_id  =  T2.District

Question: What are the delegate and name of the county they belong to, for each county?
SQL: SELECT T2.Delegate ,  T1.County_name FROM county AS T1 JOIN election AS T2 ON T1.County_id  =  T2.District

Question: Which delegates are from counties with population smaller than 100000?
SQL: SELECT T2.Delegate FROM county AS T1 JOIN election AS T2 ON T1.County_id  =  T2.District WHERE T1.Population  <  100000

Question: Find the delegates who are from counties with population below 100000.
SQL: SELECT T2.Delegate FROM county AS T1 JOIN election AS T2 ON T1.County_id  =  T2.District WHERE T1.Population  <  100000

Question: How many distinct delegates are from counties with population larger than 50000?
SQL: SELECT count(DISTINCT T2.Delegate) FROM county AS T1 JOIN election AS T2 ON T1.County_id  =  T2.District WHERE T1.Population  >  50000

Question: Count the number of distinct delegates who are from counties with population above 50000.
SQL: SELECT count(DISTINCT T2.Delegate) FROM county AS T1 JOIN election AS T2 ON T1.County_id  =  T2.District WHERE T1.Population  >  50000

Question: What are the names of the county that the delegates on "Appropriations" committee belong to?
SQL: SELECT T1.County_name FROM county AS T1 JOIN election AS T2 ON T1.County_id  =  T2.District WHERE T2.Committee  =  "Appropriations"

Question: Which county do the delegates on "Appropriations" committee belong to? Give me the county names.
SQL: SELECT T1.County_name FROM county AS T1 JOIN election AS T2 ON T1.County_id  =  T2.District WHERE T2.Committee  =  "Appropriations"

Question: Show the delegates and the names of the party they belong to.
SQL: SELECT T1.Delegate ,  T2.Party FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID

Question: For each delegate, find the names of the party they are part of.
SQL: SELECT T1.Delegate ,  T2.Party FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID

Question: Who were the governors of the parties associated with delegates from district 1?
SQL: SELECT T2.Governor FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID WHERE T1.District  =  1

Question: Find the parties associated with the delegates from district 1. Who served as governors of the parties?
SQL: SELECT T2.Governor FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID WHERE T1.District  =  1

Question: Who were the comptrollers of the parties associated with the delegates from district 1 or district 2?
SQL: SELECT T2.Comptroller FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID WHERE T1.District  =  1 OR T1.District  =  2

Question: Find the parties associated with the delegates from district 1 or 2. Who served as comptrollers of the parties?
SQL: SELECT T2.Comptroller FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID WHERE T1.District  =  1 OR T1.District  =  2

Question: Return all the committees that have delegates from Democratic party.
SQL: SELECT T1.Committee FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID WHERE T2.Party  =  "Democratic"

Question: Which committees have delegates from the Democratic party?
SQL: SELECT T1.Committee FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID WHERE T2.Party  =  "Democratic"

Question: Show the name of each county along with the corresponding number of delegates from that county.
SQL: SELECT T1.County_name ,  COUNT(*) FROM county AS T1 JOIN election AS T2 ON T1.County_id  =  T2.District GROUP BY T1.County_id

Question: For each county, find the name of the county and the number of delegates from that county.
SQL: SELECT T1.County_name ,  COUNT(*) FROM county AS T1 JOIN election AS T2 ON T1.County_id  =  T2.District GROUP BY T1.County_id

Question: Show the name of each party and the corresponding number of delegates from that party.
SQL: SELECT T2.Party ,  COUNT(*) FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID GROUP BY T1.Party

Question: For each party, return the name of the party and the number of delegates from that party.
SQL: SELECT T2.Party ,  COUNT(*) FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID GROUP BY T1.Party

Question: Return the names of all counties sorted by population in ascending order.
SQL: SELECT County_name FROM county ORDER BY Population ASC

Question: Sort the names of all counties in ascending order of population.
SQL: SELECT County_name FROM county ORDER BY Population ASC

Question: Return the names of all counties sorted by county name in descending alphabetical order.
SQL: SELECT County_name FROM county ORDER BY County_name DESC

Question: Sort the names of all counties in descending alphabetical order.
SQL: SELECT County_name FROM county ORDER BY County_name DESC

Question: Show the name of the county with the biggest population.
SQL: SELECT County_name FROM county ORDER BY Population DESC LIMIT 1

Question: Which county has the largest population? Give me the name of the county.
SQL: SELECT County_name FROM county ORDER BY Population DESC LIMIT 1

Question: Show the 3 counties with the smallest population.
SQL: SELECT County_name FROM county ORDER BY Population ASC LIMIT 3

Question: What are the 3 counties that have the smallest population? Give me the county names.
SQL: SELECT County_name FROM county ORDER BY Population ASC LIMIT 3

Question: Show the names of counties that have at least two delegates.
SQL: SELECT T1.County_name FROM county AS T1 JOIN election AS T2 ON T1.County_id  =  T2.District GROUP BY T1.County_id HAVING COUNT(*)  >=  2

Question: Which counties have two or more delegates? Give me the county names.
SQL: SELECT T1.County_name FROM county AS T1 JOIN election AS T2 ON T1.County_id  =  T2.District GROUP BY T1.County_id HAVING COUNT(*)  >=  2

Question: Show the name of the party that has at least two records.
SQL: SELECT Party FROM party GROUP BY Party HAVING COUNT(*)  >=  2

Question: Which party has two or more records?
SQL: SELECT Party FROM party GROUP BY Party HAVING COUNT(*)  >=  2

Question: Show the name of the party that has the most delegates.
SQL: SELECT T2.Party FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID GROUP BY T1.Party ORDER BY COUNT(*) DESC LIMIT 1

Question: Which party has the largest number of delegates?
SQL: SELECT T2.Party FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID GROUP BY T1.Party ORDER BY COUNT(*) DESC LIMIT 1

Question: Show the people that have been governor the most times.
SQL: SELECT Governor FROM party GROUP BY Governor ORDER BY COUNT(*) DESC LIMIT 1

Question: Which people severed as governor most frequently?
SQL: SELECT Governor FROM party GROUP BY Governor ORDER BY COUNT(*) DESC LIMIT 1

Question: Show the people that have been comptroller the most times and the corresponding number of times.
SQL: SELECT Comptroller ,  COUNT(*) FROM party GROUP BY Comptroller ORDER BY COUNT(*) DESC LIMIT 1

Question: Which people severed as comptroller most frequently? Give me the name of the person and the frequency count.
SQL: SELECT Comptroller ,  COUNT(*) FROM party GROUP BY Comptroller ORDER BY COUNT(*) DESC LIMIT 1

Question: What are the names of parties that do not have delegates in election?
SQL: SELECT Party FROM party WHERE Party_ID NOT IN (SELECT Party FROM election)

Question: Which parties did not have any delegates in elections?
SQL: SELECT Party FROM party WHERE Party_ID NOT IN (SELECT Party FROM election)

Question: What are the names of parties that have both delegates on "Appropriations" committee and
SQL: SELECT T2.Party FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID WHERE T1.Committee  =  "Appropriations" INTERSECT SELECT T2.Party FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID WHERE T1.Committee  =  "Economic Matters"

Question: Which parties have delegates in both the "Appropriations" committee and the "Economic Matters" committee?
SQL: SELECT T2.Party FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID WHERE T1.Committee  =  "Appropriations" INTERSECT SELECT T2.Party FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID WHERE T1.Committee  =  "Economic Matters"

Question: Which committees have delegates from both democratic party and liberal party?
SQL: SELECT T1.Committee FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID WHERE T2.Party  =  "Democratic" INTERSECT SELECT T1.Committee FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID WHERE T2.Party  =  "Liberal"

Question: Find the committees that have delegates both from from the democratic party and the liberal party.
SQL: SELECT T1.Committee FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID WHERE T2.Party  =  "Democratic" INTERSECT SELECT T1.Committee FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID WHERE T2.Party  =  "Liberal"