human
Schema:
candidate (Candidate_ID, People_ID, Poll_Source, Date, Support_rate, Consider_rate, Oppose_rate, Unsure_rate)
people (People_ID, Sex, Name, Date_of_Birth, Height, Weight)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the name of the candidates whose oppose percentage is the lowest for each sex.
assistant
SELECT t1.name , t1.sex , min(oppose_rate) FROM people AS t1 JOIN candidate AS t2 ON t1.people_id = t2.people_id GROUP BY t1.sex
human
which poll source does the highest oppose rate come from?
assistant
SELECT poll_source FROM candidate ORDER BY oppose_rate DESC LIMIT 1
human
find the highest support percentage, lowest consider rate and oppose rate of all candidates.
assistant
SELECT max(support_rate) , min(consider_rate) , min(oppose_rate) FROM candidate
human
Find the names of the candidates whose support percentage is lower than their oppose rate.
assistant
SELECT t1.name FROM people AS t1 JOIN candidate AS t2 ON t1.people_id = t2.people_id WHERE t2.support_rate < t2.oppose_rate
human
Please list support, consider, and oppose rates for each candidate in ascending order by unsure rate.
assistant
SELECT Support_rate , Consider_rate , Oppose_rate FROM candidate ORDER BY unsure_rate
human
What is the id of the candidate with the lowest oppose rate?
assistant
SELECT Candidate_ID FROM candidate ORDER BY oppose_rate LIMIT 1 ;