human
Schema:
Person (name, age, city, gender, job)
PersonFriend (name, friend, year)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the names of females who are friends with Zach
assistant
SELECT T1.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name = T2.name WHERE T2.friend = 'Zach' AND T1.gender = 'female'
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
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 friend of Zach with longest year relationship?
assistant
SELECT friend FROM PersonFriend WHERE name = 'Zach' AND YEAR = (SELECT max(YEAR) FROM PersonFriend WHERE name = 'Zach')
human
What is the age of the friend of Zach with longest year relationship?
assistant
SELECT T1.age FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name = T2.friend WHERE T2.name = 'Zach' AND T2.year = (SELECT max(YEAR) FROM PersonFriend WHERE name = 'Zach')
human
What is the age of the doctor named Zach?
assistant
SELECT age FROM Person WHERE job = "doctor" AND name = "Zach" ;