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
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
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
Which committees have delegates from both democratic party and liberal party?
assistant
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'
human
Who were the governors of the parties associated with delegates from district 1?
assistant
SELECT T2.Governor FROM election AS T1 JOIN party AS T2 ON T1.Party = T2.Party_ID WHERE T1.District = 1
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
Who are the lieutenant governor and comptroller from the democratic party?
assistant
SELECT Lieutenant_Governor , Comptroller FROM party WHERE Party = "Democratic" ;