human
Schema:
Person (name, age, city, gender, job)
PersonFriend (name, friend, year)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the name of the person who has friends with age above 40 and under age 30?
assistant
SELECT T1.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name = T2.name WHERE T2.friend IN (SELECT name FROM Person WHERE age > 40) INTERSECT SELECT T1.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name = T2.name WHERE T2.friend IN (SELECT name FROM Person WHERE age < 30)
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
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
How old is the doctor named Zach?
assistant
SELECT age FROM Person WHERE job = 'doctor' AND name = 'Zach'
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" ) ;