human
Schema:
Person (name, age, city, gender, job)
PersonFriend (name, friend, year)
Please answer with SQL.
assistant
Sure, I understand.
human
How many friends does Zach have?
assistant
SELECT COUNT( friend ) FROM PersonFriend WHERE name = 'Zach';
human
How long has zach been friends with each of them?
assistant
SELECT name , friend , year FROM PersonFriend WHERE name = 'Zach';
human
What are the ages of Zach's friends?
assistant
SELECT t1.name , friend , year , age FROM PersonFriend AS t1 JOIN Person AS t2 ON t1.friend = t2.name WHERE t1.name = 'Zach';
human
Which person has zach been friends with the longest?
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" ) ;