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
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
List all people names in the order of their date of birth from old to young.
assistant
SELECT name FROM people ORDER BY date_of_birth
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
How many candidates are there?
assistant
SELECT count(*) FROM candidate
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
What are the names of all female candidates in alphabetical order (sex is F)?
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 ;