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
what are 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)
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
list all female (sex is F) candidate names in the alphabetical order.
assistant
SELECT t1.name FROM people AS t1 JOIN candidate AS t2 ON t1.people_id = t2.people_id WHERE t1.sex = 'F' ORDER BY t1.name
human
List all info about all people.
assistant
SELECT * FROM people
human
What are the names of all people, ordered by their date of birth?
assistant
SELECT name FROM people ORDER BY date_of_birth ;