human
Schema:
Person (name, age, city, gender, job)
PersonFriend (name, friend, year)
Please answer with SQL.
assistant
Sure, I understand.
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
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'
human
How old is the doctor named Zach?
assistant
SELECT age FROM Person WHERE job = 'doctor' AND name = 'Zach'
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
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
Which friend of Zach has the longest-lasting friendship?
assistant
SELECT friend FROM PersonFriend WHERE name = "Zach" AND YEAR = ( SELECT MAX( YEAR ) FROM PersonFriend WHERE name = "Zach" ) ;