lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Restaurant (
       ResID       INTEGER PRIMARY KEY,
       ResName     VARCHAR(100),
       Address     VARCHAR(100),
       Rating INTEGER
)

CREATE TABLE Type_Of_Restaurant (
       ResID       INTEGER,
       ResTypeID   INTEGER,
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID),
       FOREIGN KEY(ResTypeID) REFERENCES Restaurant_Type(ResTypeID)
)

CREATE TABLE Restaurant_Type (
       ResTypeID            INTEGER PRIMARY KEY,
       ResTypeName          VARCHAR(40),
       ResTypeDescription   VARCHAR(100)
)

CREATE TABLE Visits_Restaurant (
       StuID      INTEGER,
       ResID      INTEGER,
       Time       TIMESTAMP,
       Spent      FLOAT,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID)
)

### Question
Which Advisor has most of students? List advisor and the number of students.

### SQL
SELECT Advisor ,  count(*) FROM Student GROUP BY Advisor ORDER BY count(Advisor) DESC LIMIT 1;
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Restaurant (
       ResID       INTEGER PRIMARY KEY,
       ResName     VARCHAR(100),
       Address     VARCHAR(100),
       Rating INTEGER
)

CREATE TABLE Type_Of_Restaurant (
       ResID       INTEGER,
       ResTypeID   INTEGER,
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID),
       FOREIGN KEY(ResTypeID) REFERENCES Restaurant_Type(ResTypeID)
)

CREATE TABLE Restaurant_Type (
       ResTypeID            INTEGER PRIMARY KEY,
       ResTypeName          VARCHAR(40),
       ResTypeDescription   VARCHAR(100)
)

CREATE TABLE Visits_Restaurant (
       StuID      INTEGER,
       ResID      INTEGER,
       Time       TIMESTAMP,
       Spent      FLOAT,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID)
)

### Question
Which major has least number of students? List the major and the number of students.

### SQL
SELECT Major ,  count(*) FROM Student GROUP BY Major ORDER BY count(Major) ASC LIMIT 1;
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Restaurant (
       ResID       INTEGER PRIMARY KEY,
       ResName     VARCHAR(100),
       Address     VARCHAR(100),
       Rating INTEGER
)

CREATE TABLE Type_Of_Restaurant (
       ResID       INTEGER,
       ResTypeID   INTEGER,
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID),
       FOREIGN KEY(ResTypeID) REFERENCES Restaurant_Type(ResTypeID)
)

CREATE TABLE Restaurant_Type (
       ResTypeID            INTEGER PRIMARY KEY,
       ResTypeName          VARCHAR(40),
       ResTypeDescription   VARCHAR(100)
)

CREATE TABLE Visits_Restaurant (
       StuID      INTEGER,
       ResID      INTEGER,
       Time       TIMESTAMP,
       Spent      FLOAT,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID)
)

### Question
Which major has between 2 and 30 number of students? List major and the number of students.

### SQL
SELECT Major ,  count(*) FROM Student GROUP BY Major HAVING count(Major) BETWEEN 2 AND 30;
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Restaurant (
       ResID       INTEGER PRIMARY KEY,
       ResName     VARCHAR(100),
       Address     VARCHAR(100),
       Rating INTEGER
)

CREATE TABLE Type_Of_Restaurant (
       ResID       INTEGER,
       ResTypeID   INTEGER,
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID),
       FOREIGN KEY(ResTypeID) REFERENCES Restaurant_Type(ResTypeID)
)

CREATE TABLE Restaurant_Type (
       ResTypeID            INTEGER PRIMARY KEY,
       ResTypeName          VARCHAR(40),
       ResTypeDescription   VARCHAR(100)
)

CREATE TABLE Visits_Restaurant (
       StuID      INTEGER,
       ResID      INTEGER,
       Time       TIMESTAMP,
       Spent      FLOAT,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID)
)

### Question
Which student's age is older than 18 and is majoring in 600? List each student's first and last name.

### SQL
SELECT Fname , Lname FROM Student WHERE Age  >  18 AND Major = 600;
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Restaurant (
       ResID       INTEGER PRIMARY KEY,
       ResName     VARCHAR(100),
       Address     VARCHAR(100),
       Rating INTEGER
)

CREATE TABLE Type_Of_Restaurant (
       ResID       INTEGER,
       ResTypeID   INTEGER,
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID),
       FOREIGN KEY(ResTypeID) REFERENCES Restaurant_Type(ResTypeID)
)

CREATE TABLE Restaurant_Type (
       ResTypeID            INTEGER PRIMARY KEY,
       ResTypeName          VARCHAR(40),
       ResTypeDescription   VARCHAR(100)
)

CREATE TABLE Visits_Restaurant (
       StuID      INTEGER,
       ResID      INTEGER,
       Time       TIMESTAMP,
       Spent      FLOAT,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID)
)

### Question
List all female students age is older than 18 who is not majoring in 600. List students' first name and last name.

### SQL
SELECT Fname , Lname FROM Student WHERE Age  >  18 AND Major != 600 AND Sex = 'F';
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Restaurant (
       ResID       INTEGER PRIMARY KEY,
       ResName     VARCHAR(100),
       Address     VARCHAR(100),
       Rating INTEGER
)

CREATE TABLE Type_Of_Restaurant (
       ResID       INTEGER,
       ResTypeID   INTEGER,
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID),
       FOREIGN KEY(ResTypeID) REFERENCES Restaurant_Type(ResTypeID)
)

CREATE TABLE Restaurant_Type (
       ResTypeID            INTEGER PRIMARY KEY,
       ResTypeName          VARCHAR(40),
       ResTypeDescription   VARCHAR(100)
)

CREATE TABLE Visits_Restaurant (
       StuID      INTEGER,
       ResID      INTEGER,
       Time       TIMESTAMP,
       Spent      FLOAT,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID)
)

### Question
How many restaurant is the Sandwich type restaurant?

### SQL
SELECT count(*) FROM Restaurant JOIN Type_Of_Restaurant ON Restaurant.ResID =  Type_Of_Restaurant.ResID JOIN Restaurant_Type ON Type_Of_Restaurant.ResTypeID = Restaurant_Type.ResTypeID GROUP BY Type_Of_Restaurant.ResTypeID HAVING Restaurant_Type.ResTypeName = 'Sandwich'
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Restaurant (
       ResID       INTEGER PRIMARY KEY,
       ResName     VARCHAR(100),
       Address     VARCHAR(100),
       Rating INTEGER
)

CREATE TABLE Type_Of_Restaurant (
       ResID       INTEGER,
       ResTypeID   INTEGER,
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID),
       FOREIGN KEY(ResTypeID) REFERENCES Restaurant_Type(ResTypeID)
)

CREATE TABLE Restaurant_Type (
       ResTypeID            INTEGER PRIMARY KEY,
       ResTypeName          VARCHAR(40),
       ResTypeDescription   VARCHAR(100)
)

CREATE TABLE Visits_Restaurant (
       StuID      INTEGER,
       ResID      INTEGER,
       Time       TIMESTAMP,
       Spent      FLOAT,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID)
)

### Question
How long does student Linda Smith spend on the restaurant in total?

### SQL
SELECT sum(Spent) FROM Student JOIN Visits_Restaurant ON Student.StuID = Visits_Restaurant.StuID WHERE Student.Fname = "Linda" AND Student.Lname = "Smith";
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Restaurant (
       ResID       INTEGER PRIMARY KEY,
       ResName     VARCHAR(100),
       Address     VARCHAR(100),
       Rating INTEGER
)

CREATE TABLE Type_Of_Restaurant (
       ResID       INTEGER,
       ResTypeID   INTEGER,
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID),
       FOREIGN KEY(ResTypeID) REFERENCES Restaurant_Type(ResTypeID)
)

CREATE TABLE Restaurant_Type (
       ResTypeID            INTEGER PRIMARY KEY,
       ResTypeName          VARCHAR(40),
       ResTypeDescription   VARCHAR(100)
)

CREATE TABLE Visits_Restaurant (
       StuID      INTEGER,
       ResID      INTEGER,
       Time       TIMESTAMP,
       Spent      FLOAT,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID)
)

### Question
How many times has the student Linda Smith visited Subway?

### SQL
SELECT count(*) FROM Student JOIN Visits_Restaurant ON Student.StuID = Visits_Restaurant.StuID JOIN Restaurant ON Visits_Restaurant.ResID = Restaurant.ResID WHERE Student.Fname = "Linda" AND Student.Lname = "Smith" AND Restaurant.ResName = "Subway";
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Restaurant (
       ResID       INTEGER PRIMARY KEY,
       ResName     VARCHAR(100),
       Address     VARCHAR(100),
       Rating INTEGER
)

CREATE TABLE Type_Of_Restaurant (
       ResID       INTEGER,
       ResTypeID   INTEGER,
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID),
       FOREIGN KEY(ResTypeID) REFERENCES Restaurant_Type(ResTypeID)
)

CREATE TABLE Restaurant_Type (
       ResTypeID            INTEGER PRIMARY KEY,
       ResTypeName          VARCHAR(40),
       ResTypeDescription   VARCHAR(100)
)

CREATE TABLE Visits_Restaurant (
       StuID      INTEGER,
       ResID      INTEGER,
       Time       TIMESTAMP,
       Spent      FLOAT,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID)
)

### Question
When did Linda Smith visit Subway?

### SQL
SELECT TIME FROM Student JOIN Visits_Restaurant ON Student.StuID = Visits_Restaurant.StuID JOIN Restaurant ON Visits_Restaurant.ResID = Restaurant.ResID WHERE Student.Fname = "Linda" AND Student.Lname = "Smith" AND Restaurant.ResName = "Subway";
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Restaurant (
       ResID       INTEGER PRIMARY KEY,
       ResName     VARCHAR(100),
       Address     VARCHAR(100),
       Rating INTEGER
)

CREATE TABLE Type_Of_Restaurant (
       ResID       INTEGER,
       ResTypeID   INTEGER,
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID),
       FOREIGN KEY(ResTypeID) REFERENCES Restaurant_Type(ResTypeID)
)

CREATE TABLE Restaurant_Type (
       ResTypeID            INTEGER PRIMARY KEY,
       ResTypeName          VARCHAR(40),
       ResTypeDescription   VARCHAR(100)
)

CREATE TABLE Visits_Restaurant (
       StuID      INTEGER,
       ResID      INTEGER,
       Time       TIMESTAMP,
       Spent      FLOAT,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID)
)

### Question
At which restaurant did the students spend the least amount of time? List restaurant and the time students spent on in total.

### SQL
SELECT Restaurant.ResName ,  sum(Visits_Restaurant.Spent) FROM Visits_Restaurant JOIN Restaurant ON Visits_Restaurant.ResID = Restaurant.ResID GROUP BY Restaurant.ResID ORDER BY sum(Visits_Restaurant.Spent) ASC LIMIT 1;
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Restaurant (
       ResID       INTEGER PRIMARY KEY,
       ResName     VARCHAR(100),
       Address     VARCHAR(100),
       Rating INTEGER
)

CREATE TABLE Type_Of_Restaurant (
       ResID       INTEGER,
       ResTypeID   INTEGER,
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID),
       FOREIGN KEY(ResTypeID) REFERENCES Restaurant_Type(ResTypeID)
)

CREATE TABLE Restaurant_Type (
       ResTypeID            INTEGER PRIMARY KEY,
       ResTypeName          VARCHAR(40),
       ResTypeDescription   VARCHAR(100)
)

CREATE TABLE Visits_Restaurant (
       StuID      INTEGER,
       ResID      INTEGER,
       Time       TIMESTAMP,
       Spent      FLOAT,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID)
)

### Question
Which student visited restaurant most often? List student's first name and last name.

### SQL
SELECT Student.Fname , Student.Lname FROM Student JOIN Visits_Restaurant ON Student.StuID = Visits_Restaurant.StuID GROUP BY Student.StuID ORDER BY count(*) DESC LIMIT 1;
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
How many authors are there?

### SQL
SELECT count(*) FROM authors
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Count the number of authors.

### SQL
SELECT count(*) FROM authors
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
How many institutions are there?

### SQL
SELECT count(*) FROM inst
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Count the number of institutions.

### SQL
SELECT count(*) FROM inst
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
How many papers are published in total?

### SQL
SELECT count(*) FROM papers
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Count the number of total papers.

### SQL
SELECT count(*) FROM papers
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
What are the titles of papers published by "Jeremy Gibbons"?

### SQL
SELECT t3.title FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE  t1.fname  =  "Jeremy" AND t1.lname  =  "Gibbons"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Find the titles of all the papers written by "Jeremy Gibbons"

### SQL
SELECT t3.title FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE  t1.fname  =  "Jeremy" AND t1.lname  =  "Gibbons"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Find all the papers published by "Aaron Turon".

### SQL
SELECT t3.title FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE  t1.fname  =  "Aaron" AND t1.lname  =  "Turon"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Find the titles of all the papers written by "Aaron Turon".

### SQL
SELECT t3.title FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE  t1.fname  =  "Aaron" AND t1.lname  =  "Turon"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
How many papers have "Atsushi Ohori" published?

### SQL
SELECT count(*) FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE  t1.fname  =  "Atsushi" AND t1.lname  =  "Ohori"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
How many papers are "Atsushi Ohori" the author of?

### SQL
SELECT count(*) FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE  t1.fname  =  "Atsushi" AND t1.lname  =  "Ohori"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
What is the name of the institution that "Matthias Blume" belongs to?

### SQL
SELECT DISTINCT t3.name FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN inst AS t3 ON t2.instid  =  t3.instid WHERE t1.fname  =  "Matthias" AND t1.lname  =  "Blume"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Which institution is the author "Matthias Blume" belong to? Give me the name of the institution.

### SQL
SELECT DISTINCT t3.name FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN inst AS t3 ON t2.instid  =  t3.instid WHERE t1.fname  =  "Matthias" AND t1.lname  =  "Blume"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Which institution does "Katsuhiro Ueno" belong to?

### SQL
SELECT DISTINCT t3.name FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN inst AS t3 ON t2.instid  =  t3.instid WHERE t1.fname  =  "Katsuhiro" AND t1.lname  =  "Ueno"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
What is the name of the institution the author "Katsuhiro Ueno" belongs to?

### SQL
SELECT DISTINCT t3.name FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN inst AS t3 ON t2.instid  =  t3.instid WHERE t1.fname  =  "Katsuhiro" AND t1.lname  =  "Ueno"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Who belong to the institution "University of Oxford"? Show the first names and last names.

### SQL
SELECT DISTINCT t1.fname ,  t1.lname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN inst AS t3 ON t2.instid  =  t3.instid WHERE t3.name  =  "University of Oxford"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Find the first names and last names of the authors whose institution affiliation is "University of Oxford".

### SQL
SELECT DISTINCT t1.fname ,  t1.lname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN inst AS t3 ON t2.instid  =  t3.instid WHERE t3.name  =  "University of Oxford"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Which authors belong to the institution "Google"? Show the first names and last names.

### SQL
SELECT DISTINCT t1.fname ,  t1.lname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN inst AS t3 ON t2.instid  =  t3.instid WHERE t3.name  =  "Google"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Find the first names and last names of the authors whose institution affiliation is "Google".

### SQL
SELECT DISTINCT t1.fname ,  t1.lname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN inst AS t3 ON t2.instid  =  t3.instid WHERE t3.name  =  "Google"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
What are the last names of the author of the paper titled "Binders Unbound"?

### SQL
SELECT t1.lname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE t3.title  =  "Binders Unbound"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Who is the author of the paper titled "Binders Unbound"? Give me the last name.

### SQL
SELECT t1.lname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE t3.title  =  "Binders Unbound"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Find the first and last name of the author(s) who wrote the paper "Nameless, Painless".

### SQL
SELECT t1.fname ,  t1.lname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE t3.title  =  "Nameless ,  Painless"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
What are the first and last name of the author who published the paper titled "Nameless, Painless"?

### SQL
SELECT t1.fname ,  t1.lname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE t3.title  =  "Nameless ,  Painless"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
What are the papers published under the institution "Indiana University"?

### SQL
SELECT DISTINCT t1.title FROM papers AS t1 JOIN authorship AS t2 ON t1.paperid  =  t2.paperid JOIN inst AS t3 ON t2.instid  =  t3.instid WHERE t3.name  =  "Indiana University"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
List the titles of the papers whose authors are from the institution "Indiana University".

### SQL
SELECT DISTINCT t1.title FROM papers AS t1 JOIN authorship AS t2 ON t1.paperid  =  t2.paperid JOIN inst AS t3 ON t2.instid  =  t3.instid WHERE t3.name  =  "Indiana University"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Find all the papers published by the institution "Google".

### SQL
SELECT DISTINCT t1.title FROM papers AS t1 JOIN authorship AS t2 ON t1.paperid  =  t2.paperid JOIN inst AS t3 ON t2.instid  =  t3.instid WHERE t3.name  =  "Google"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Which papers were written by authors from the institution "Google"?

### SQL
SELECT DISTINCT t1.title FROM papers AS t1 JOIN authorship AS t2 ON t1.paperid  =  t2.paperid JOIN inst AS t3 ON t2.instid  =  t3.instid WHERE t3.name  =  "Google"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
How many papers are published by the institution "Tokohu University"?

### SQL
SELECT count(DISTINCT t1.title) FROM papers AS t1 JOIN authorship AS t2 ON t1.paperid  =  t2.paperid JOIN inst AS t3 ON t2.instid  =  t3.instid WHERE t3.name  =  "Tokohu University"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Find the number of papers published by authors from the institution "Tokohu University".

### SQL
SELECT count(DISTINCT t1.title) FROM papers AS t1 JOIN authorship AS t2 ON t1.paperid  =  t2.paperid JOIN inst AS t3 ON t2.instid  =  t3.instid WHERE t3.name  =  "Tokohu University"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Find the number of papers published by the institution "University of Pennsylvania".

### SQL
SELECT count(DISTINCT t1.title) FROM papers AS t1 JOIN authorship AS t2 ON t1.paperid  =  t2.paperid JOIN inst AS t3 ON t2.instid  =  t3.instid WHERE t3.name  =  "University of Pennsylvania"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
How many papers are written by authors from the institution "University of Pennsylvania"?

### SQL
SELECT count(DISTINCT t1.title) FROM papers AS t1 JOIN authorship AS t2 ON t1.paperid  =  t2.paperid JOIN inst AS t3 ON t2.instid  =  t3.instid WHERE t3.name  =  "University of Pennsylvania"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Find the papers which have "Olin Shivers" as an author.

### SQL
SELECT t3.title FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE t1.fname  =  "Olin" AND t1.lname  =  "Shivers"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Which papers did the author "Olin Shivers" write? Give me the paper titles.

### SQL
SELECT t3.title FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE t1.fname  =  "Olin" AND t1.lname  =  "Shivers"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Which papers have "Stephanie Weirich" as an author?

### SQL
SELECT t3.title FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE t1.fname  =  "Stephanie" AND t1.lname  =  "Weirich"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Find the titles of the papers the author "Stephanie Weirich" wrote.

### SQL
SELECT t3.title FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE t1.fname  =  "Stephanie" AND t1.lname  =  "Weirich"
### Database Schema
CREATE TABLE Inst (
  instID INTEGER,
  name TEXT,
  country TEXT, -- the home country of the institution (this is obviously an impoverished model)
  PRIMARY KEY (instID)
)

CREATE TABLE Authors (
  authID INTEGER,
  lname TEXT,
  fname TEXT,
  PRIMARY KEY (authID)
)

CREATE TABLE Papers (
  paperID INTEGER,
  title TEXT,
  PRIMARY KEY (paperID)
)

CREATE TABLE Authorship (
  authID INTEGER,
  instID INTEGER,
  paperID INTEGER,
  authOrder INTEGER,
  PRIMARY KEY (authID, instID, paperID),
  FOREIGN KEY (authID) REFERENCES Authors (authID),
  FOREIGN KEY (instID) REFERENCES Inst (instID),
  FOREIGN KEY (paperID) REFERENCES Papers (paperID)
)

### Question
Which paper is published in an institution in "USA" and have "Turon" as its second author?

### SQL
SELECT t3.title FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid JOIN inst AS t4 ON t2.instid  =  t4.instid WHERE t4.country  =  "USA" AND t2.authorder  =  2 AND t1.lname  =  "Turon"