human
Schema:
Person (name, age, city, gender, job)
PersonFriend (name, friend, year)
Please answer with SQL.
assistant
Sure, I understand.
human
Who has friends that are younger than the average age?
assistant
SELECT DISTINCT T2.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name = T2.friend WHERE T1.age < (SELECT avg(age) FROM person)
human
What is average age for different job title?
assistant
SELECT avg(age) , job FROM Person GROUP BY job
human
What is average age of male for different job title?
assistant
SELECT avg(age) , job FROM Person WHERE gender = 'male' GROUP BY job
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
What is the average age for each gender?
assistant
SELECT avg(age) , gender FROM Person GROUP BY gender
human
What is the average age for all people in the table?
assistant
SELECT avg ( age ) FROM Person ;