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
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
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
Find the name, age, and job title of persons who are friends with Alice for the longest years.
assistant
SELECT T1.name , T1.age , T1.job FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name = T2.name WHERE T2.friend = 'Alice' AND T2.year = (SELECT max(YEAR) FROM PersonFriend WHERE friend = 'Alice')
human
Find the names of all person sorted in the descending order using age.
assistant
SELECT name FROM Person ORDER BY age DESC
human
list the name, job title of all people ordered by their names.
assistant
SELECT name , job FROM Person ORDER BY name ;