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
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 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 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
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
How many candidates are there?
assistant
SELECT count(*) FROM candidate
human
Give the names of people who did not participate in the candidate election.
assistant
SELECT name FROM people WHERE people_id NOT IN ( SELECT people_id FROM candidate ) ;