lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE `Document_Types` (
`document_type_code` VARCHAR(10) PRIMARY KEY,
`document_description` VARCHAR(255) NOT NULL
)

CREATE TABLE `Documents` (
`document_id` INTEGER PRIMARY KEY,
`document_type_code` VARCHAR(10),
`grant_id` INTEGER NOT NULL,
`sent_date` DATETIME NOT NULL,
`response_received_date` DATETIME NOT NULL,
`other_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`document_type_code` ) REFERENCES `Document_Types`(`document_type_code` ),
FOREIGN KEY (`grant_id` ) REFERENCES `Grants`(`grant_id` )
)

CREATE TABLE `Grants` (
`grant_id` INTEGER PRIMARY KEY,
`organisation_id` INTEGER NOT NULL,
`grant_amount` DECIMAL(19,4) NOT NULL DEFAULT 0,
`grant_start_date` DATETIME NOT NULL,
`grant_end_date` DATETIME NOT NULL,
`other_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`organisation_id` ) REFERENCES `Organisations`(`organisation_id` )
)

CREATE TABLE `Organisation_Types` (
`organisation_type` VARCHAR(10) PRIMARY KEY,
`organisation_type_description` VARCHAR(255) NOT NULL
)

CREATE TABLE `Organisations` (
`organisation_id` INTEGER PRIMARY KEY,
`organisation_type` VARCHAR(10) NOT NULL,
`organisation_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`organisation_type` ) REFERENCES `Organisation_Types`(`organisation_type` )
)

CREATE TABLE `Project_Outcomes` (
`project_id` INTEGER NOT NULL,
`outcome_code` VARCHAR(10) NOT NULL,
`outcome_details` VARCHAR(255),
FOREIGN KEY (`project_id` ) REFERENCES `Projects`(`project_id` ),FOREIGN KEY (`outcome_code` ) REFERENCES `Research_Outcomes`(`outcome_code` )
)

CREATE TABLE `Project_Staff` (
`staff_id` DOUBLE PRIMARY KEY,
`project_id` INTEGER NOT NULL,
`role_code` VARCHAR(10) NOT NULL,
`date_from` DATETIME,
`date_to` DATETIME,
`other_details` VARCHAR(255),
FOREIGN KEY (`project_id` ) REFERENCES `Projects`(`project_id` ),FOREIGN KEY (`role_code` ) REFERENCES `Staff_Roles`(`role_code` )
)

CREATE TABLE `Projects` (
`project_id` INTEGER PRIMARY KEY,
`organisation_id` INTEGER NOT NULL,
`project_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`organisation_id` ) REFERENCES `Organisations`(`organisation_id` )
)

CREATE TABLE `Research_Outcomes` (
`outcome_code` VARCHAR(10) PRIMARY KEY,
`outcome_description` VARCHAR(255) NOT NULL
)

CREATE TABLE `Research_Staff` (
`staff_id` INTEGER PRIMARY KEY,
`employer_organisation_id` INTEGER NOT NULL,
`staff_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`employer_organisation_id` ) REFERENCES `Organisations`(`organisation_id` )
)

CREATE TABLE `Staff_Roles` (
`role_code` VARCHAR(10) PRIMARY KEY,
`role_description` VARCHAR(255) NOT NULL
)

CREATE TABLE `Tasks` (
`task_id` INTEGER PRIMARY KEY,
`project_id` INTEGER NOT NULL,
`task_details` VARCHAR(255) NOT NULL,
`eg Agree Objectives` VARCHAR(1),
FOREIGN KEY (`project_id` ) REFERENCES `Projects`(`project_id` )
)

### Question
List the description of the outcomes for all projects.

### SQL
SELECT T1.outcome_description FROM Research_outcomes AS T1 JOIN Project_outcomes AS T2 ON T1.outcome_code  =  T2.outcome_code
### Database Schema
CREATE TABLE `Document_Types` (
`document_type_code` VARCHAR(10) PRIMARY KEY,
`document_description` VARCHAR(255) NOT NULL
)

CREATE TABLE `Documents` (
`document_id` INTEGER PRIMARY KEY,
`document_type_code` VARCHAR(10),
`grant_id` INTEGER NOT NULL,
`sent_date` DATETIME NOT NULL,
`response_received_date` DATETIME NOT NULL,
`other_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`document_type_code` ) REFERENCES `Document_Types`(`document_type_code` ),
FOREIGN KEY (`grant_id` ) REFERENCES `Grants`(`grant_id` )
)

CREATE TABLE `Grants` (
`grant_id` INTEGER PRIMARY KEY,
`organisation_id` INTEGER NOT NULL,
`grant_amount` DECIMAL(19,4) NOT NULL DEFAULT 0,
`grant_start_date` DATETIME NOT NULL,
`grant_end_date` DATETIME NOT NULL,
`other_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`organisation_id` ) REFERENCES `Organisations`(`organisation_id` )
)

CREATE TABLE `Organisation_Types` (
`organisation_type` VARCHAR(10) PRIMARY KEY,
`organisation_type_description` VARCHAR(255) NOT NULL
)

CREATE TABLE `Organisations` (
`organisation_id` INTEGER PRIMARY KEY,
`organisation_type` VARCHAR(10) NOT NULL,
`organisation_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`organisation_type` ) REFERENCES `Organisation_Types`(`organisation_type` )
)

CREATE TABLE `Project_Outcomes` (
`project_id` INTEGER NOT NULL,
`outcome_code` VARCHAR(10) NOT NULL,
`outcome_details` VARCHAR(255),
FOREIGN KEY (`project_id` ) REFERENCES `Projects`(`project_id` ),FOREIGN KEY (`outcome_code` ) REFERENCES `Research_Outcomes`(`outcome_code` )
)

CREATE TABLE `Project_Staff` (
`staff_id` DOUBLE PRIMARY KEY,
`project_id` INTEGER NOT NULL,
`role_code` VARCHAR(10) NOT NULL,
`date_from` DATETIME,
`date_to` DATETIME,
`other_details` VARCHAR(255),
FOREIGN KEY (`project_id` ) REFERENCES `Projects`(`project_id` ),FOREIGN KEY (`role_code` ) REFERENCES `Staff_Roles`(`role_code` )
)

CREATE TABLE `Projects` (
`project_id` INTEGER PRIMARY KEY,
`organisation_id` INTEGER NOT NULL,
`project_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`organisation_id` ) REFERENCES `Organisations`(`organisation_id` )
)

CREATE TABLE `Research_Outcomes` (
`outcome_code` VARCHAR(10) PRIMARY KEY,
`outcome_description` VARCHAR(255) NOT NULL
)

CREATE TABLE `Research_Staff` (
`staff_id` INTEGER PRIMARY KEY,
`employer_organisation_id` INTEGER NOT NULL,
`staff_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`employer_organisation_id` ) REFERENCES `Organisations`(`organisation_id` )
)

CREATE TABLE `Staff_Roles` (
`role_code` VARCHAR(10) PRIMARY KEY,
`role_description` VARCHAR(255) NOT NULL
)

CREATE TABLE `Tasks` (
`task_id` INTEGER PRIMARY KEY,
`project_id` INTEGER NOT NULL,
`task_details` VARCHAR(255) NOT NULL,
`eg Agree Objectives` VARCHAR(1),
FOREIGN KEY (`project_id` ) REFERENCES `Projects`(`project_id` )
)

### Question
Which role is most common for the staff?

### SQL
SELECT role_code FROM Project_Staff GROUP BY role_code ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE `Document_Types` (
`document_type_code` VARCHAR(10) PRIMARY KEY,
`document_description` VARCHAR(255) NOT NULL
)

CREATE TABLE `Documents` (
`document_id` INTEGER PRIMARY KEY,
`document_type_code` VARCHAR(10),
`grant_id` INTEGER NOT NULL,
`sent_date` DATETIME NOT NULL,
`response_received_date` DATETIME NOT NULL,
`other_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`document_type_code` ) REFERENCES `Document_Types`(`document_type_code` ),
FOREIGN KEY (`grant_id` ) REFERENCES `Grants`(`grant_id` )
)

CREATE TABLE `Grants` (
`grant_id` INTEGER PRIMARY KEY,
`organisation_id` INTEGER NOT NULL,
`grant_amount` DECIMAL(19,4) NOT NULL DEFAULT 0,
`grant_start_date` DATETIME NOT NULL,
`grant_end_date` DATETIME NOT NULL,
`other_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`organisation_id` ) REFERENCES `Organisations`(`organisation_id` )
)

CREATE TABLE `Organisation_Types` (
`organisation_type` VARCHAR(10) PRIMARY KEY,
`organisation_type_description` VARCHAR(255) NOT NULL
)

CREATE TABLE `Organisations` (
`organisation_id` INTEGER PRIMARY KEY,
`organisation_type` VARCHAR(10) NOT NULL,
`organisation_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`organisation_type` ) REFERENCES `Organisation_Types`(`organisation_type` )
)

CREATE TABLE `Project_Outcomes` (
`project_id` INTEGER NOT NULL,
`outcome_code` VARCHAR(10) NOT NULL,
`outcome_details` VARCHAR(255),
FOREIGN KEY (`project_id` ) REFERENCES `Projects`(`project_id` ),FOREIGN KEY (`outcome_code` ) REFERENCES `Research_Outcomes`(`outcome_code` )
)

CREATE TABLE `Project_Staff` (
`staff_id` DOUBLE PRIMARY KEY,
`project_id` INTEGER NOT NULL,
`role_code` VARCHAR(10) NOT NULL,
`date_from` DATETIME,
`date_to` DATETIME,
`other_details` VARCHAR(255),
FOREIGN KEY (`project_id` ) REFERENCES `Projects`(`project_id` ),FOREIGN KEY (`role_code` ) REFERENCES `Staff_Roles`(`role_code` )
)

CREATE TABLE `Projects` (
`project_id` INTEGER PRIMARY KEY,
`organisation_id` INTEGER NOT NULL,
`project_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`organisation_id` ) REFERENCES `Organisations`(`organisation_id` )
)

CREATE TABLE `Research_Outcomes` (
`outcome_code` VARCHAR(10) PRIMARY KEY,
`outcome_description` VARCHAR(255) NOT NULL
)

CREATE TABLE `Research_Staff` (
`staff_id` INTEGER PRIMARY KEY,
`employer_organisation_id` INTEGER NOT NULL,
`staff_details` VARCHAR(255) NOT NULL,
FOREIGN KEY (`employer_organisation_id` ) REFERENCES `Organisations`(`organisation_id` )
)

CREATE TABLE `Staff_Roles` (
`role_code` VARCHAR(10) PRIMARY KEY,
`role_description` VARCHAR(255) NOT NULL
)

CREATE TABLE `Tasks` (
`task_id` INTEGER PRIMARY KEY,
`project_id` INTEGER NOT NULL,
`task_details` VARCHAR(255) NOT NULL,
`eg Agree Objectives` VARCHAR(1),
FOREIGN KEY (`project_id` ) REFERENCES `Projects`(`project_id` )
)

### Question
What is the most common role for the staff?

### SQL
SELECT role_code FROM Project_Staff GROUP BY role_code ORDER BY count(*) DESC LIMIT 1
### Database Schema
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'
### Database Schema
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 females does this network has?

### SQL
SELECT count(*) FROM Person WHERE gender  =  'female'
### Database Schema
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 females are in the network?

### SQL
SELECT count(*) FROM Person WHERE gender  =  'female'
### Database Schema
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
What is the average age for all person?

### SQL
SELECT avg(age) FROM Person
### Database Schema
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
What is the average age for all people in the table?

### SQL
SELECT avg(age) FROM Person
### Database Schema
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 different cities are they from?

### SQL
SELECT count(DISTINCT city) FROM Person
### Database Schema
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 different cities do people originate from?

### SQL
SELECT count(DISTINCT city) FROM Person
### Database Schema
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 type of jobs do they have?

### SQL
SELECT count(DISTINCT job) FROM Person
### Database Schema
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 different jobs are listed?

### SQL
SELECT count(DISTINCT job) FROM Person
### Database Schema
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
Who is the oldest person?

### SQL
SELECT name FROM Person WHERE age  =  (SELECT max(age) FROM person)
### Database Schema
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
What is the name of the person who is the oldest?

### SQL
SELECT name FROM Person WHERE age  =  (SELECT max(age) FROM person)
### Database Schema
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
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' )
### Database Schema
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
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' )
### Database Schema
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
Who is the youngest male?

### SQL
SELECT name FROM Person WHERE gender  =  'male' AND age  =  (SELECT min(age) FROM person WHERE gender  =  'male' )
### Database Schema
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
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' )
### Database Schema
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 old is the doctor named Zach?

### SQL
SELECT age FROM Person WHERE job  =  'doctor' AND name  =  'Zach'
### Database Schema
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
What is the age of the doctor named Zach?

### SQL
SELECT age FROM Person WHERE job  =  'doctor' AND name  =  'Zach'
### Database Schema
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
Who is the person whose age is below 30?

### SQL
SELECT name FROM Person WHERE age  <  30
### Database Schema
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
What is the name of the person whose age is below 30?

### SQL
SELECT name FROM Person WHERE age  <  30
### Database Schema
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 people whose age is greater 30 and job is engineer?

### SQL
SELECT count(*) FROM Person WHERE age  >  30 AND job  =  'engineer'
### Database Schema
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 engineers are older than 30?

### SQL
SELECT count(*) FROM Person WHERE age  >  30 AND job  =  'engineer'
### Database Schema
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
What is the average age for each gender?

### SQL
SELECT avg(age) ,  gender FROM Person GROUP BY gender
### Database Schema
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 old is each gender, on average?

### SQL
SELECT avg(age) ,  gender FROM Person GROUP BY gender
### Database Schema
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
What is average age for different job title?

### SQL
SELECT avg(age) ,  job FROM Person GROUP BY job
### Database Schema
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 old is the average person for each job?

### SQL
SELECT avg(age) ,  job FROM Person GROUP BY job
### Database Schema
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
What is average age of male for different job title?

### SQL
SELECT avg(age) ,  job FROM Person WHERE gender  =  'male' GROUP BY job
### Database Schema
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
What is the average age for a male in each job?

### SQL
SELECT avg(age) ,  job FROM Person WHERE gender  =  'male' GROUP BY job
### Database Schema
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
What is minimum age for different job title?

### SQL
SELECT min(age) ,  job FROM Person GROUP BY job
### Database Schema
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 old is the youngest person for each job?

### SQL
SELECT min(age) ,  job FROM Person GROUP BY job
### Database Schema
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
Find the number of people who is under 40 for each gender.

### SQL
SELECT count(*) ,  gender FROM Person WHERE age  <  40 GROUP BY gender
### Database Schema
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 people are under 40 for each gender?

### SQL
SELECT count(*) ,  gender FROM Person WHERE age  <  40 GROUP BY gender
### Database Schema
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
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
### Database Schema
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
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
### Database Schema
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
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')
### Database Schema
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 people are older than every engineer?

### SQL
SELECT count(*) FROM Person WHERE age  >  (SELECT max(age) FROM person WHERE job  =  'engineer')
### Database Schema
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
list the name, job title of all people ordered by their names.

### SQL
SELECT name ,  job FROM Person ORDER BY name
### Database Schema
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
What are the names and job titles of every person ordered alphabetically by name?

### SQL
SELECT name ,  job FROM Person ORDER BY name
### Database Schema
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
Find the names of all person sorted in the descending order using age.

### SQL
SELECT name FROM Person ORDER BY age DESC
### Database Schema
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
What are the names of everybody sorted by age in descending order?

### SQL
SELECT name FROM Person ORDER BY age DESC
### Database Schema
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
Find the name and age of all males in order of their age.

### SQL
SELECT name FROM Person WHERE gender  =  'male' ORDER BY age
### Database Schema
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
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
### Database Schema
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
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'
### Database Schema
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
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'
### Database Schema
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
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'
### Database Schema
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
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'
### Database Schema
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
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)
### Database Schema
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
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)
### Database Schema
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
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)
### Database Schema
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
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)
### Database Schema
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
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'
### Database Schema
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
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'
### Database Schema
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
Find the person who has exactly one friend.

### SQL
SELECT name FROM PersonFriend GROUP BY name HAVING count(*)  =  1
### Database Schema
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
What are the names of everybody who has exactly one friend?

### SQL
SELECT name FROM PersonFriend GROUP BY name HAVING count(*)  =  1
### Database Schema
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
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'
### Database Schema
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
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'
### Database Schema
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
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'
### Database Schema
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
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'
### Database Schema
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
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'
### Database Schema
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
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'
### Database Schema
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
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'
### Database Schema
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
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'
### Database Schema
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
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'
### Database Schema
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
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'
### Database Schema
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
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'
### Database Schema
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
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'
### Database Schema
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
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)
### Database Schema
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
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)
### Database Schema
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
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)
### Database Schema
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
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)
### Database Schema
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
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')
### Database Schema
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
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')
### Database Schema
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
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')
### Database Schema
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
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')
### Database Schema
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
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')
### Database Schema
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
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')
### Database Schema
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
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')
### Database Schema
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
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')
### Database Schema
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
Who is the person that has no friend?

### SQL
SELECT name FROM person EXCEPT SELECT name FROM PersonFriend
### Database Schema
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
What are the names of all people who do not have friends?

### SQL
SELECT name FROM person EXCEPT SELECT name FROM PersonFriend
### Database Schema
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
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