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 number of people whose age is greater than all engineers.
assistant
SELECT count(*) FROM Person WHERE age > (SELECT max(age) FROM person WHERE job = 'engineer')
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
What is the average age for each gender?
assistant
SELECT avg(age) , gender FROM Person GROUP BY gender
human
Find the name and age of all males in order of their age.
assistant
SELECT name FROM Person WHERE gender = 'male' ORDER BY age
human
Find the number of people who is under 40 for each gender.
assistant
SELECT COUNT( * ) , gender FROM Person WHERE age < 40 GROUP BY gender ;