human
Schema:
Person (name, age, city, gender, job)
PersonFriend (name, friend, year)
Please answer with SQL.
assistant
Sure, I understand.
human
What is the average age for all person?
assistant
SELECT avg(age) FROM Person
human
Find the name of the person who has friends with age above 40 but not 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) EXCEPT 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 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
Which person whose friends have the oldest average age?
assistant
SELECT T2.name , avg(T1.age) FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name = T2.friend GROUP BY T2.name ORDER BY avg(T1.age) DESC LIMIT 1
human
Who has friends that are younger than the average age?
assistant
SELECT DISTINCT T2.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name = T2.friend WHERE T1.age < (SELECT avg(age) FROM person)
human
Who has friends that are older than the average age? Print their friends and their ages as well
assistant
SELECT DISTINCT T2.name , T2.friend , T1.age FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name = T2.friend WHERE T1.age > ( SELECT avg ( age ) FROM person ) ;