human
Schema:
Person (name, age, city, gender, job)
PersonFriend (name, friend, year)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the names of females who are friends with Zach
assistant
SELECT T1.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name = T2.name WHERE T2.friend = 'Zach' AND T1.gender = 'female'
human
Who is the youngest male?
assistant
SELECT name FROM Person WHERE gender = 'male' AND age = (SELECT min(age) FROM person WHERE gender = 'male' )
human
What is the total number of people who has no friend living in the city of Austin.
assistant
SELECT count(DISTINCT name) FROM PersonFriend WHERE friend NOT IN (SELECT name FROM person WHERE city = 'Austin')
human
How many friends does Dan have?
assistant
SELECT count(T2.friend) FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name = T2.name WHERE T1.name = 'Dan'
human
What is the average age for each gender?
assistant
SELECT avg(age) , gender FROM Person GROUP BY gender
human
How many females does this network has?
assistant
SELECT COUNT( * ) FROM Person WHERE gender = "female" ;