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

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"