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 average height and weight for all males (sex is M).
assistant
SELECT avg(height) , avg(weight) FROM people WHERE sex = 'M'
human
Find the average and minimum weight for each gender.
assistant
SELECT avg(weight) , min(weight) , sex FROM people GROUP BY sex
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
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
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
find the names of people who are taller than 200 or lower than 190.
assistant
SELECT name FROM people WHERE height > 200 OR height < 190 ;