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
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')
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
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
What are the names of all of Bob's friends?
assistant
SELECT T1.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name = T2.name WHERE T2.friend = "Bob" ;