human
Schema:
Person (name, age, city, gender, job)
PersonFriend (name, friend, year)
Please answer with SQL.
assistant
Sure, I understand.
human
Who is the oldest person whose job is student?
assistant
SELECT name FROM Person WHERE job = 'student' AND age = (SELECT max(age) FROM person WHERE job = 'student' )
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 all person?
assistant
SELECT avg(age) FROM Person
human
Find the name of people whose age is greater than any engineer sorted by their age.
assistant
SELECT name FROM Person WHERE age > (SELECT min(age) FROM person WHERE job = 'engineer') ORDER BY age
human
How many people whose age is greater 30 and job is engineer?
assistant
SELECT count(*) FROM Person WHERE age > 30 AND job = 'engineer'
human
How many people are older than every engineer?
assistant
SELECT COUNT( * ) FROM Person WHERE age > ( SELECT MAX( age ) FROM person WHERE job = "engineer" ) ;