human
Schema:
Person (name, age, city, gender, job)
PersonFriend (name, friend, year)
Please answer with SQL.
assistant
Sure, I understand.
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
Who are the friends of Bob?
assistant
SELECT T2.friend FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name = T2.name WHERE T1.name = 'Bob'
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
Find the name and age of the person who is a friend of both Dan and Alice.
assistant
SELECT T1.name , T1.age FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name = T2.name WHERE T2.friend = 'Dan' INTERSECT SELECT T1.name , T1.age FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name = T2.name WHERE T2.friend = 'Alice'
human
Find the name and age of the person who is a friend of Dan or Alice.
assistant
SELECT DISTINCT T1.name , T1.age FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name = T2.name WHERE T2.friend = 'Dan' OR T2.friend = 'Alice'
human
How many friends does Dan have?
assistant
SELECT COUNT( T2.friend ) FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name = T2.name WHERE T1.name = "Dan" ;