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
How many candidates are there?
assistant
SELECT count(*) FROM candidate
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
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 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
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
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 ;