human
Schema:
county (County_Id, County_name, Population, Zip_code)
party (Party_ID, Year, Party, Governor, Lieutenant_Governor, Comptroller, Attorney_General, US_Senate)
election (Election_ID, Counties_Represented, District, Delegate, Party, First_Elected, Committee)
Please answer with SQL.
assistant
Sure, I understand.
human
What are the names of parties that do not have delegates in election?
assistant
SELECT Party FROM party WHERE Party_ID NOT IN (SELECT Party FROM election)
human
Show the delegates and the names of county they belong to.
assistant
SELECT T2.Delegate , T1.County_name FROM county AS T1 JOIN election AS T2 ON T1.County_id = T2.District
human
Show the name of each party and the corresponding number of delegates from that party.
assistant
SELECT T2.Party , COUNT(*) FROM election AS T1 JOIN party AS T2 ON T1.Party = T2.Party_ID GROUP BY T1.Party
human
Show the delegates and the names of the party they belong to.
assistant
SELECT T1.Delegate , T2.Party FROM election AS T1 JOIN party AS T2 ON T1.Party = T2.Party_ID
human
Who were the comptrollers of the parties associated with the delegates from district 1 or district 2?
assistant
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
human
Find the parties associated with the delegates from district 1. Who served as governors of the parties?
assistant
SELECT T2.Governor FROM election AS T1 JOIN party AS T2 ON T1.Party = T2.Party_ID WHERE T1.District = 1 ;