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
list all female (sex is F) candidate names in the alphabetical order.
assistant
SELECT t1.name FROM people AS t1 JOIN candidate AS t2 ON t1.people_id = t2.people_id WHERE t1.sex = 'F' ORDER BY t1.name
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
Find the id of the candidate who got the lowest oppose rate.
assistant
SELECT Candidate_ID FROM candidate ORDER BY oppose_rate LIMIT 1
human
Find the name and gender of the candidate who got the highest support rate.
assistant
SELECT t1.name , t1.sex FROM people AS t1 JOIN candidate AS t2 ON t1.people_id = t2.people_id ORDER BY t2.support_rate DESC LIMIT 1
human
For each sex, what is the name and sex of the candidate with the oppose rate for their 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 ;