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 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 all the information about election.
assistant
SELECT * 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
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
What are the delegate and committee information for each election record?
assistant
SELECT Delegate , Committee FROM election ;