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 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 name of people whose height is lower than the average.
assistant
SELECT name FROM people WHERE height < (SELECT avg(height) FROM people)
human
which gender got the highest average uncertain ratio.
assistant
SELECT t1.sex FROM people AS t1 JOIN candidate AS t2 ON t1.people_id = t2.people_id GROUP BY t1.sex ORDER BY avg(t2.unsure_rate) DESC LIMIT 1
human
Find the average height and weight for all males (sex is M).
assistant
SELECT avg(height) , avg(weight) FROM people WHERE sex = 'M'
human
how many people are there whose weight is higher than 85 for each gender?
assistant
SELECT count(*) , sex FROM people WHERE weight > 85 GROUP BY sex
human
What are the average and minimum weights for people of each sex?
assistant
SELECT avg ( weight ) , MIN( weight ) , sex FROM people GROUP BY sex ;