Database: network_2

CREATE TABLE Person (
  name varchar(20) PRIMARY KEY,
  age INTEGER,
  city TEXT,
  gender TEXT,
  job TEXT
);
CREATE TABLE PersonFriend (
  name varchar(20),
  friend varchar(20),
  year INTEGER,
  FOREIGN KEY (name) REFERENCES Person(name),
  FOREIGN KEY (friend) REFERENCES Person(name)
);

Question: How many friends does Dan have?
SQL: SELECT count(T2.friend) FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.name WHERE T1.name  =  'Dan'

Question: How many friends does Dan have?
SQL: SELECT count(T2.friend) FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.name WHERE T1.name  =  'Dan'

Question: How many females does this network has?
SQL: SELECT count(*) FROM Person WHERE gender  =  'female'

Question: How many females are in the network?
SQL: SELECT count(*) FROM Person WHERE gender  =  'female'

Question: What is the average age for all person?
SQL: SELECT avg(age) FROM Person

Question: What is the average age for all people in the table?
SQL: SELECT avg(age) FROM Person

Question: How many different cities are they from?
SQL: SELECT count(DISTINCT city) FROM Person

Question: How many different cities do people originate from?
SQL: SELECT count(DISTINCT city) FROM Person

Question: How many type of jobs do they have?
SQL: SELECT count(DISTINCT job) FROM Person

Question: How many different jobs are listed?
SQL: SELECT count(DISTINCT job) FROM Person

Question: Who is the oldest person?
SQL: SELECT name FROM Person WHERE age  =  (SELECT max(age) FROM person)

Question: What is the name of the person who is the oldest?
SQL: SELECT name FROM Person WHERE age  =  (SELECT max(age) FROM person)

Question: Who is the oldest person whose job is student?
SQL: SELECT name FROM Person WHERE job  =  'student' AND age  =  (SELECT max(age) FROM person WHERE job  =  'student' )

Question: What is the name of the oldest student?
SQL: SELECT name FROM Person WHERE job  =  'student' AND age  =  (SELECT max(age) FROM person WHERE job  =  'student' )

Question: Who is the youngest male?
SQL: SELECT name FROM Person WHERE gender  =  'male' AND age  =  (SELECT min(age) FROM person WHERE gender  =  'male' )

Question: What is the name of the youngest male?
SQL: SELECT name FROM Person WHERE gender  =  'male' AND age  =  (SELECT min(age) FROM person WHERE gender  =  'male' )

Question: How old is the doctor named Zach?
SQL: SELECT age FROM Person WHERE job  =  'doctor' AND name  =  'Zach'

Question: What is the age of the doctor named Zach?
SQL: SELECT age FROM Person WHERE job  =  'doctor' AND name  =  'Zach'

Question: Who is the person whose age is below 30?
SQL: SELECT name FROM Person WHERE age  <  30

Question: What is the name of the person whose age is below 30?
SQL: SELECT name FROM Person WHERE age  <  30

Question: How many people whose age is greater 30 and job is engineer?
SQL: SELECT count(*) FROM Person WHERE age  >  30 AND job  =  'engineer'

Question: HOw many engineers are older than 30?
SQL: SELECT count(*) FROM Person WHERE age  >  30 AND job  =  'engineer'

Question: What is the average age for each gender?
SQL: SELECT avg(age) ,  gender FROM Person GROUP BY gender

Question: How old is each gender, on average?
SQL: SELECT avg(age) ,  gender FROM Person GROUP BY gender

Question: What is average age for different job title?
SQL: SELECT avg(age) ,  job FROM Person GROUP BY job

Question: How old is the average person for each job?
SQL: SELECT avg(age) ,  job FROM Person GROUP BY job

Question: What is average age of male for different job title?
SQL: SELECT avg(age) ,  job FROM Person WHERE gender  =  'male' GROUP BY job

Question: What is the average age for a male in each job?
SQL: SELECT avg(age) ,  job FROM Person WHERE gender  =  'male' GROUP BY job

Question: What is minimum age for different job title?
SQL: SELECT min(age) ,  job FROM Person GROUP BY job

Question: How old is the youngest person for each job?
SQL: SELECT min(age) ,  job FROM Person GROUP BY job

Question: Find the number of people who is under 40 for each gender.
SQL: SELECT count(*) ,  gender FROM Person WHERE age  <  40 GROUP BY gender

Question: How many people are under 40 for each gender?
SQL: SELECT count(*) ,  gender FROM Person WHERE age  <  40 GROUP BY gender

Question: Find the name of people whose age is greater than any engineer sorted by their age.
SQL: SELECT name FROM Person WHERE age  >  (SELECT min(age) FROM person WHERE job  =  'engineer') ORDER BY age

Question: What is the name of all the people who are older than at least one engineer? Order them by age.
SQL: SELECT name FROM Person WHERE age  >  (SELECT min(age) FROM person WHERE job  =  'engineer') ORDER BY age

Question: Find the number of people whose age is greater than all engineers.
SQL: SELECT count(*) FROM Person WHERE age  >  (SELECT max(age) FROM person WHERE job  =  'engineer')

Question: How many people are older than every engineer?
SQL: SELECT count(*) FROM Person WHERE age  >  (SELECT max(age) FROM person WHERE job  =  'engineer')

Question: list the name, job title of all people ordered by their names.
SQL: SELECT name ,  job FROM Person ORDER BY name

Question: What are the names and job titles of every person ordered alphabetically by name?
SQL: SELECT name ,  job FROM Person ORDER BY name

Question: Find the names of all person sorted in the descending order using age.
SQL: SELECT name FROM Person ORDER BY age DESC

Question: What are the names of everybody sorted by age in descending order?
SQL: SELECT name FROM Person ORDER BY age DESC

Question: Find the name and age of all males in order of their age.
SQL: SELECT name FROM Person WHERE gender  =  'male' ORDER BY age

Question: What is the name and age of every male? Order the results by age.
SQL: SELECT name FROM Person WHERE gender  =  'male' ORDER BY age

Question: Find the name and age of the person who is a friend of both Dan and Alice.
SQL: 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'

Question: What are the names and ages of every person who is a friend of both Dan and Alice?
SQL: 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'

Question: Find the name and age of the person who is a friend of Dan or Alice.
SQL: SELECT DISTINCT T1.name ,  T1.age FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.name WHERE T2.friend  =  'Dan' OR T2.friend  =  'Alice'

Question: What are the different names and ages of every friend of either Dan or alice?
SQL: SELECT DISTINCT T1.name ,  T1.age FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.name WHERE T2.friend  =  'Dan' OR T2.friend  =  'Alice'

Question: Find the name of the person who has friends with age above 40 and under age 30?
SQL: 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)

Question: What are the names of every person who has a friend over 40 and under 30?
SQL: 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)

Question: Find the name of the person who has friends with age above 40 but not under age 30?
SQL: 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)

Question: What are the names of the people who are older 40 but no friends under age 30?
SQL: 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)

Question: Find the name of the person who has no student friends.
SQL: SELECT name FROM person EXCEPT SELECT T2.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend WHERE T1.job  =  'student'

Question: What are the names of the people who have no friends who are students?
SQL: SELECT name FROM person EXCEPT SELECT T2.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend WHERE T1.job  =  'student'

Question: Find the person who has exactly one friend.
SQL: SELECT name FROM PersonFriend GROUP BY name HAVING count(*)  =  1

Question: What are the names of everybody who has exactly one friend?
SQL: SELECT name FROM PersonFriend GROUP BY name HAVING count(*)  =  1

Question: Who are the friends of Bob?
SQL: SELECT T2.friend FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.name WHERE T1.name  =  'Bob'

Question: Who are Bob's friends?
SQL: SELECT T2.friend FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.name WHERE T1.name  =  'Bob'

Question: Find the name of persons who are friends with Bob.
SQL: SELECT T1.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.name WHERE T2.friend  =  'Bob'

Question: What are the names of all of Bob's friends?
SQL: SELECT T1.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.name WHERE T2.friend  =  'Bob'

Question: Find the names of females who are friends with Zach
SQL: SELECT T1.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.name WHERE T2.friend  =  'Zach' AND T1.gender  =  'female'

Question: What are the names of all females who are friends with Zach?
SQL: SELECT T1.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.name WHERE T2.friend  =  'Zach' AND T1.gender  =  'female'

Question: Find the female friends of Alice.
SQL: SELECT T2.friend FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend WHERE T2.name  =  'Alice' AND T1.gender  =  'female'

Question: What are all the friends of Alice who are female?
SQL: SELECT T2.friend FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend WHERE T2.name  =  'Alice' AND T1.gender  =  'female'

Question: Find the male friend of Alice whose job is a doctor?
SQL: SELECT T2.friend FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend WHERE T2.name  =  'Alice' AND T1.gender  =  'male' AND T1.job  =  'doctor'

Question: Who are the friends of Alice that are doctors?
SQL: SELECT T2.friend FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend WHERE T2.name  =  'Alice' AND T1.gender  =  'male' AND T1.job  =  'doctor'

Question: Who has a friend that is from new york city?
SQL: SELECT T2.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend WHERE T1.city  =  'new york city'

Question: What are the names of all friends who are from New York?
SQL: SELECT T2.name FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend WHERE T1.city  =  'new york city'

Question: Who has friends that are younger than the average age?
SQL: 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)

Question: What are the different names of friends who are younger than the average age for a friend?
SQL: 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)

Question: Who has friends that are older than the average age? Print their friends and their ages as well
SQL: 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)

Question: Whare the names, friends, and ages of all people who are older than the average age of a person?
SQL: 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)

Question: Who is the friend of Zach with longest year relationship?
SQL: SELECT friend FROM PersonFriend WHERE name =  'Zach' AND YEAR  =  (SELECT max(YEAR) FROM PersonFriend WHERE name =  'Zach')

Question: Which friend of Zach has the longest-lasting friendship?
SQL: SELECT friend FROM PersonFriend WHERE name =  'Zach' AND YEAR  =  (SELECT max(YEAR) FROM PersonFriend WHERE name =  'Zach')

Question: What is the age of the friend of Zach with longest year relationship?
SQL: SELECT T1.age FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend WHERE T2.name =  'Zach' AND T2.year  =  (SELECT max(YEAR) FROM PersonFriend WHERE name =  'Zach')

Question: What are the ages of all of Zach's friends who are in the longest relationship?
SQL: SELECT T1.age FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.friend WHERE T2.name =  'Zach' AND T2.year  =  (SELECT max(YEAR) FROM PersonFriend WHERE name =  'Zach')

Question: Find the name of persons who are friends with Alice for the shortest years.
SQL: SELECT name FROM PersonFriend WHERE friend =  'Alice' AND YEAR  =  (SELECT min(YEAR) FROM PersonFriend WHERE friend =  'Alice')

Question: What are the names of all people who are friends with Alice for the shortest amount of time?
SQL: SELECT name FROM PersonFriend WHERE friend =  'Alice' AND YEAR  =  (SELECT min(YEAR) FROM PersonFriend WHERE friend =  'Alice')

Question: Find the name, age, and job title of persons who are friends with Alice for the longest years.
SQL: SELECT T1.name ,  T1.age ,  T1.job FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.name WHERE T2.friend =  'Alice' AND T2.year  =  (SELECT max(YEAR) FROM PersonFriend WHERE friend =  'Alice')

Question: What are the names, ages, and jobs of all people who are friends with Alice for the longest amount of time?
SQL: SELECT T1.name ,  T1.age ,  T1.job FROM Person AS T1 JOIN PersonFriend AS T2 ON T1.name  =  T2.name WHERE T2.friend =  'Alice' AND T2.year  =  (SELECT max(YEAR) FROM PersonFriend WHERE friend =  'Alice')

Question: Who is the person that has no friend?
SQL: SELECT name FROM person EXCEPT SELECT name FROM PersonFriend

Question: What are the names of all people who do not have friends?
SQL: SELECT name FROM person EXCEPT SELECT name FROM PersonFriend

Question: Which person whose friends have the oldest average age?
SQL: 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

Question: What is the name of the person who has the oldest average age for their friends, and what is that average age?
SQL: 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

Question: What is the total number of people who has no friend living in the city of Austin.
SQL: SELECT count(DISTINCT name) FROM PersonFriend WHERE friend NOT IN (SELECT name FROM person WHERE city  =  'Austin')

Question: What is the total number of people who have no friends living in Austin?
SQL: SELECT count(DISTINCT name) FROM PersonFriend WHERE friend NOT IN (SELECT name FROM person WHERE city  =  'Austin')

Question: Find Alice's friends of friends.
SQL: 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'

Question: What are the names of all of Alice's friends of friends?
SQL: 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'