human
Schema:
Person (name, age, city, gender, job)
PersonFriend (name, friend, year)
Please answer with SQL.
assistant
Sure, I understand.
human
Who is the person whose age is below 30?
assistant
SELECT name FROM Person WHERE age < 30
human
Who is the youngest male?
assistant
SELECT name FROM Person WHERE gender = 'male' AND age = (SELECT min(age) FROM person WHERE gender = 'male' )
human
Find the name of the person who has no student friends.
assistant
SELECT name FROM person EXCEPT SELECT T2.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name = T2.friend WHERE T1.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
Who is the oldest person?
assistant
SELECT name FROM Person WHERE age = (SELECT max(age) FROM person)
human
What is the name of the oldest student?
assistant
SELECT name FROM Person WHERE job = "student" AND age = ( SELECT MAX( age ) FROM person WHERE job = "student" ) ;