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 lieutenant governor and comptroller from the democratic party.
assistant
SELECT Lieutenant_Governor , Comptroller FROM party WHERE Party = 'Democratic'
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 name of the party that has the most delegates.
assistant
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
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
Show the name of the party that has at least two records.
assistant
SELECT Party FROM party GROUP BY Party HAVING COUNT( * ) >= 2 ;