human
Schema:
Person (name, age, city, gender, job)
PersonFriend (name, friend, year)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the female friends of Alice.
assistant
SELECT T2.friend FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name = T2.friend WHERE T2.name = 'Alice' AND T1.gender = 'female'
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
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 Alice's friends of friends.
assistant
SELECT DISTINCT T4.name FROM PersonFriend AS T1 JOIN Person AS T2 ON T1.name = T2.name JOIN PersonFriend AS T3 ON T1.friend = T3.name JOIN PersonFriend AS T4 ON T3.friend = T4.name WHERE T2.name = 'Alice' AND T4.name != 'Alice'
human
Find the name, age, and job title of persons who are friends with Alice for the longest years.
assistant
SELECT T1.name , T1.age , T1.job FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name = T2.name WHERE T2.friend = 'Alice' AND T2.year = (SELECT max(YEAR) FROM PersonFriend WHERE friend = 'Alice')
human
Find the name of persons who are friends with Alice for the shortest years.
assistant
SELECT name FROM PersonFriend WHERE friend = "Alice" AND YEAR = ( SELECT MIN( YEAR ) FROM PersonFriend WHERE friend = "Alice" ) ;