human
Schema:
Person (name, age, city, gender, job)
PersonFriend (name, friend, year)
Please answer with SQL.
assistant
Sure, I understand.
human
What is the average age for each gender?
assistant
SELECT avg(age) , gender FROM Person GROUP BY gender
human
Which person whose friends have the oldest average age?
assistant
SELECT T2.name , avg(T1.age) FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name = T2.friend GROUP BY T2.name ORDER BY avg(T1.age) DESC LIMIT 1
human
Find the name and age of all males in order of their age.
assistant
SELECT name FROM Person WHERE gender = 'male' ORDER BY age
human
Who is the oldest person?
assistant
SELECT name FROM Person WHERE age = (SELECT max(age) FROM person)
human
Who is the person whose age is below 30?
assistant
SELECT name FROM Person WHERE age < 30
human
What is the name of the youngest male?
assistant
SELECT name FROM Person WHERE gender = "male" AND age = ( SELECT MIN( age ) FROM person WHERE gender = "male" ) ;