Can you list the person names and their friends?
select name, friend from personfriend
I want to know the ages of the friends first, please.
select t2.friend, t1.age from person as t1 join personfriend as t2 on t1.name = t2.friend
Please tell me the names of the person who has friends with age above 40 and under age 30.
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 )
How about the names of different persons who have friends with age above 40 and under age 30?
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 ) union 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 )