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 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
Which poll resource provided the most number of candidate information?
assistant
SELECT poll_source FROM candidate GROUP BY poll_source ORDER BY count(*) 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
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
Return the poll source corresponding to the candidate who has the oppose rate.
assistant
SELECT poll_source FROM candidate ORDER BY oppose_rate DESC LIMIT 1 ;