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 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
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
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
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
What is all the information about all people?
assistant
SELECT * FROM people ;